Database Management System: Relational Database Concepts, Keys, and SQL

Data is one of the most vital assets of modern civilization. From national identification systems and online school portals to global banking networks, millions of transactions occur every second. Managing this overwhelming volume of data efficiently, securely, and without redundancy requires specialized software systems known as Database Management Systems (DBMS).

In a traditional file-processing system (such as storing data in separate spreadsheets or paper registers), data is vulnerable to duplication (redundancy), inconsistency, lack of security, and structural rigidity. A Relational Database Management System (RDBMS) overcomes these limitations by organizing data into interconnected tables based on well-defined mathematical principles of set theory. Understanding relational database concepts, primary and foreign key constraints, table design, and Structured Query Language (SQL) is essential for mastering computing curriculum and building real-world digital applications.

1. In-Depth Conceptual Breakdown

1.1 Fundamental Database Terminology

To understand relational databases, we must first distinguish between raw data, information, and the relational structures that organize them.

• Data: Raw, unorganized facts, figures, symbols, or observations (e.g., 101, "Aarav", 95.5).
• Information: Processed, structured data presented in a meaningful context (e.g., "Student Aarav, Roll No. 101, scored 95.5% in Mathematics").
• Database: An organized collection of logically related data stored electronically for easy access, management, and updating.
• RDBMS (Relational Database Management System): A DBMS based on the relational model introduced by E.F. Codd in 1970. In an RDBMS, data is represented in two-dimensional tables called relations. Popular RDBMS tools include MySQL, PostgreSQL, SQLite, Oracle, and LibreOffice Base.

Components of a Relation (Table)

A table (or relation) consists of horizontal rows and vertical columns.

+---------+--------------+-------+-------------+
| Roll_No | Student_Name | Class | Total_Marks |  <-- Column Headers (Attributes)
+---------+--------------+-------+-------------+
|   101   | Aarav Sharma |  10A  |     485     |  <-- Row 1 (Tuple)
|   102   | Priya Verma  |  10A  |     492     |  <-- Row 2 (Tuple)
|   103   | Rohan Mehta  |  10B  |     440     |  <-- Row 3 (Tuple)
+---------+--------------+-------+-------------+

1. Attribute (Column / Field): A named column in a table representing a specific characteristic or property of an entity. For example, Roll_No, Student_Name, Class, and Total_Marks are attributes.
2. Tuple (Row / Record): A single horizontal row in a table representing a complete set of attribute values for a single entity.
3. Domain: The set of all permissible atomic (indivisible) values for a given attribute. For example, the domain of Class might be restricted to {'10A', '10B', '10C'}.
4. Degree: The total number of attributes (columns) present in a table. $\text{Degree } (D) = \text{Total Number of Columns}$ In the table above, Degree $D = 4$.
5. Cardinality: The total number of tuples (rows) present in a table (excluding the header row). $\text{Cardinality } (C) = \text{Total Number of Rows}$ In the table above, Cardinality $C = 3$.

1.2 Keys in Relational Database Systems

Keys are individual columns or combinations of columns used to uniquely identify records, enforce data integrity, and establish relationships between different tables.

       [ All Available Column Combinations ]
                       │
                       ▼
               [ CANDIDATE KEYS ]  (All keys capable of unique identification)
             /                   \
            /                     \
           ▼                       ▼
    [ PRIMARY KEY ]         [ ALTERNATE KEYS ]
 (Chosen as main identifier)   (Remaining candidate keys)

Types of Keys

1. Candidate Key: Any attribute or minimal set of attributes that can uniquely identify every tuple in a table. A table can have multiple candidate keys.
2. Primary Key: A specific candidate key selected by the database designer to uniquely identify each tuple in a table.
   • Rule 1: It must contain unique values for every row.
   • Rule 2: It cannot contain NULL values (unspecified or missing values).
   • Rule 3: A table can have only one Primary Key.
3. Alternate Key: Any candidate key that was not chosen as the primary key. $\text{Alternate Keys} = \text{Candidate Keys} \setminus \{\text{Primary Key}\}$
4. Composite Primary Key: A primary key that consists of two or more attributes combined together to uniquely identify a record when no single attribute is sufficient.
5. Foreign Key: An attribute or combination of attributes in a table whose values are derived from and reference the Primary Key of another table (or the same table).
   • The table containing the foreign key is called the Child Table (or Referencing Table).
   • The table containing the referenced primary key is called the Parent Table (or Referenced Table).
   • Referential Integrity: A foreign key value must either match an existing primary key value in the parent table or be NULL.

Comparison Matrix: Primary Key vs. Foreign Key vs. Candidate Key

1.3 Introduction to SQL (Structured Query Language)

SQL is the standard language used to communicate with Relational Database Management Systems. SQL commands are categorized into functional sub-languages based on their operations:

• DDL (Data Definition Language): Used to define, alter, or delete database structures and schemas.
  • Commands: CREATE, ALTER, DROP, TRUNCATE.
• DML (Data Manipulation Language): Used to manipulate and modify data contained within relations.
  • Commands: INSERT, UPDATE, DELETE.
• DQL (Data Query Language): Used to retrieve data from relations.
  • Commands: SELECT.

Essential SQL Data Types

1.4 Detailed SQL Syntax & Operations

1. Creating Tables (CREATE TABLE)

The CREATE TABLE statement defines the table structure, column data types, and integrity constraints.

CREATE TABLE Student (
    Roll_No INT PRIMARY KEY,
    Student_Name VARCHAR(50) NOT NULL,
    Class VARCHAR(5),
    DOB DATE,
    Marks DECIMAL(5,2)
);

2. Inserting Data (INSERT INTO)

The INSERT INTO statement adds new rows of data into a relation.

-- Syntax 1: Inserting values for all columns in order
INSERT INTO Student 
VALUES (101, 'Aarav Sharma', '10A', '2008-05-14', 485.50);

-- Syntax 2: Inserting values for specific columns
INSERT INTO Student (Roll_No, Student_Name, Class) 
VALUES (102, 'Priya Verma', '10A');

3. Querying Data (SELECT)

The SELECT statement retrieves specific data from one or more tables.

$\text{Syntax: } \mathbf{SELECT} \text{ attributes } \mathbf{FROM} \text{ table } [\mathbf{WHERE} \text{ condition}] [\mathbf{ORDER\ BY} \text{ column } \mathbf{ASC}|\mathbf{DESC}];$

• Select All Columns:

  SELECT * FROM Student;
  

• Select Specific Columns:

  SELECT Roll_No, Student_Name FROM Student;
  

• Filtering Rows (WHERE Clause):

  SELECT * FROM Student 
  WHERE Marks >= 400.00 AND Class = '10A';
  

• Sorting Output (ORDER BY Clause):

  SELECT * FROM Student 
  ORDER BY Marks DESC;
  

• Pattern Matching Operators (LIKE):
  • % represents zero or more characters.
  • _ represents exactly one character.

  -- Names starting with 'A'
  SELECT * FROM Student WHERE Student_Name LIKE 'A%';
  
  -- Names having 'a' as the second letter
  SELECT * FROM Student WHERE Student_Name LIKE '_a%';
  

4. Updating Records (UPDATE)

The UPDATE statement modifies existing values in a table based on a condition.

UPDATE Student 
SET Marks = 490.00, Class = '10B' 
WHERE Roll_No = 101;

5. Deleting Records (DELETE)

The DELETE statement removes specific rows from a table based on a condition.

DELETE FROM Student 
WHERE Roll_No = 102;

2. Real-World Applications & Analogies

Application 1: School Information System

Consider a school management system that handles thousands of students, teachers, and examination records.

+------------------------------------+
|           STUDENT TABLE            |
+------------------------------------+
| PK: Roll_No                        |
| Name, Class, Contact_No            |
+------------------------------------+
                 │
                 │ 1-to-Many Relationship
                 ▼
+------------------------------------+
|            MARKS TABLE             |
+------------------------------------+
| PK: Mark_ID                        |
| FK: Roll_No (References STUDENT)  |
| Subject, Marks_Obtained            |
+------------------------------------+

• Primary Key Role: Roll_No uniquely identifies each student. Even if two students share the name "Rahul Sharma", their unique Roll_No prevents administrative errors.
• Foreign Key Role: In the MARKS table, Roll_No acts as a Foreign Key referencing the STUDENT table. This ensures that marks cannot be entered for a non-existent student roll number (referential integrity).

Application 2: E-Commerce Order Management Analogy

Imagine buying items on an e-commerce website like Amazon or Flipkart:

• Customer Table: Contains Customer_ID (Primary Key), Name, Address, Phone.
• Order Table: Contains Order_ID (Primary Key), Customer_ID (Foreign Key referencing Customer Table), Order_Date, Total_Amount.

Why split this into two tables? If customer details were repeated in every order line, updating an address would require changing thousands of records (Update Anomaly). Splitting data into relations linked by Customer_ID ensures that address updates occur in exactly one place.

3. Step-by-Step Solved Textbook Examples

Example 1: Determining Table Attributes, Degree, Cardinality, and Keys

Given the following relation EMPLOYEE:

Questions:

1. State the Degree and Cardinality of the relation.
2. Identify all Candidate Keys.
3. If Emp_ID is designated as the Primary Key, name the Alternate Key.

Solution:

• Step 1: Calculate Degree

  • Count total columns: Emp_ID, Aadhaar_No, Emp_Name, Department, Salary.
  • $\text{Degree } (D) = 5$

• Step 2: Calculate Cardinality

  • Count total rows (excluding header): 4 data rows.
  • $\text{Cardinality } (C) = 4$

• Step 3: Identify Candidate Keys

  • Emp_ID is unique for every employee.
  • Aadhaar_No is unique for every citizen/employee.
  • Candidate Keys = {Emp_ID, Aadhaar_No}

• Step 4: Identify Alternate Key

  • If Primary Key = Emp_ID, then:
  • $\text{Alternate Key} = \text{Candidate Keys} \setminus \{\text{Primary Key}\} = \{\text{Aadhaar\_No}\}$

Example 2: DDL Table Creation with Constraints

Problem: Create a table named BOOK with the following requirements:

• Book_ID: Integer, must be the Primary Key.
• Title: Text string up to 100 characters, cannot be empty.
• Author: Text string up to 50 characters.
• Price: Floating-point decimal with 2 decimal places.
• Pub_Date: Date format.

Solution:

To construct the query, match each requirement to its SQL keyword:

1. Primary Key constraint $\rightarrow$ PRIMARY KEY
2. Cannot be empty $\rightarrow$ NOT NULL
3. Text up to $N$ chars $\rightarrow$ VARCHAR(N)
4. Decimal $\rightarrow$ DECIMAL(precision, scale)

CREATE TABLE BOOK (
    Book_ID INT PRIMARY KEY,
    Title VARCHAR(100) NOT NULL,
    Author VARCHAR(50),
    Price DECIMAL(7,2),
    Pub_Date DATE
);

Example 3: Complex SQL Data Filtering & Manipulation Queries

Given the table PRODUCT:

Write SQL statements for the following operational queries:

1. Display P_Name and Price of all products in the 'Electronics' category priced below 2000.00.
2. Increase the Price of all 'Furniture' items by $10\%$.
3. Delete all products whose Quantity is less than 10.
4. Display all products arranged by Price from highest to lowest.

Solution:

• Query 1: Data Selection with Multiple Conditions

  SELECT P_Name, Price 
  FROM PRODUCT 
  WHERE Category = 'Electronics' AND Price < 2000.00;
  

• Query 2: Updating Numerical Values with Calculations $\text{New Price} = \text{Price} + (0.10 \times \text{Price}) = \text{Price} \times 1.10$

  UPDATE PRODUCT 
  SET Price = Price * 1.10 
  WHERE Category = 'Furniture';
  

• Query 3: Deleting Rows Based on Numeric Condition

  DELETE FROM PRODUCT 
  WHERE Quantity < 10;
  

• Query 4: Sorting Output

  SELECT * FROM PRODUCT 
  ORDER BY Price DESC;
  

4. Common Student Mistakes to Avoid

Mistake 1: Confusing CHAR and VARCHAR Data Types

• The Error: Using CHAR(20) for names, thinking it dynamic saves memory.
• The Fact: CHAR(20) is fixed-length. Storing "Raj" (3 characters) in CHAR(20) forces the system to pad 17 blank space characters, wasting memory. VARCHAR(20) allocates only 3 characters dynamically. Use CHAR for fixed-length codes (e.g., Gender 'M'/'F', Country_Code 'IND') and VARCHAR for varying names/addresses.

Mistake 2: Using Equality (=) Instead of IS NULL for Missing Values

• Incorrect SQL: SELECT * FROM Student WHERE Marks = NULL;
• Correct SQL: SELECT * FROM Student WHERE Marks IS NULL;
• Reason: In SQL logic, NULL represents an unknown value. Standard relational comparison operators (=, <>, <) with NULL evaluate to UNKNOWN (false), returning zero rows.

Mistake 3: Confusing DELETE, DROP, and TRUNCATE

• DELETE FROM TableName: Removes rows/records based on conditions. Table structure remains intact. (DML Command)
• DROP TABLE TableName: Destroys the entire table structure along with all data permanently. (DDL Command)
• TRUNCATE TABLE TableName: Removes all rows instantly, preserving the structure for future inserts. (DDL Command)

Mistake 4: Omitting Quotes for Strings and Dates in Queries

• Incorrect SQL: SELECT * FROM Student WHERE Class = 10A AND DOB = 2008-05-14;
• Correct SQL: SELECT * FROM Student WHERE Class = '10A' AND DOB = '2008-05-14';
• Reason: Numeric values (INT, DECIMAL) are written without quotes. Non-numeric literals (VARCHAR, CHAR, DATE) must always be enclosed in single or double quote marks.

5. Practice Questions for Self-Assessment

Question 1

Consider the relation DOCTOR given below:

1. Write the SQL command to create the above table with Doc_ID as the primary key.
2. What is the degree and cardinality of this table?
3. Write an SQL query to display the names and salaries of doctors who joined before '2016-01-01' and earn more than 100000.

Solution:

1. Table Creation Query:

   CREATE TABLE DOCTOR (
       Doc_ID VARCHAR(10) PRIMARY KEY,
       Doc_Name VARCHAR(50) NOT NULL,
       Specialization VARCHAR(30),
       Salary INT,
       Joining_Date DATE
   );
   

2. Degree and Cardinality:
   • Degree $D = 5$ (columns)
   • Cardinality $C = 4$ (rows)
3. Data Retrieval Query:

   SELECT Doc_Name, Salary 
   FROM DOCTOR 
   WHERE Joining_Date < '2016-01-01' AND Salary > 100000;
   

Question 2

Explain the concept of Foreign Key with an example. What is Referential Integrity?

Solution:

• Foreign Key Definition: A Foreign Key is a non-key attribute (or combination of attributes) in a child table whose values must match the Primary Key of a parent table.
• Example:
  • Parent Table: DEPARTMENT (Dept_ID [PK], Dept_Name)
  • Child Table: EMPLOYEE (Emp_ID [PK], Emp_Name, Dept_ID [FK referencing DEPARTMENT])
• Referential Integrity: A database rule ensuring that relationships between tables remain consistent. It dictates that any value in a Foreign Key column must exist in the Primary Key column of the referenced Parent table. You cannot insert an employee with a Dept_ID of D99 if D99 does not exist in the DEPARTMENT table.

Question 3

Write SQL statements for the STUDENT table (Roll_No, Name, Stream, Marks, City):

1. Display records of all students living in either 'Delhi' or 'Mumbai'.
2. Display all unique Stream values available in the table.
3. Increase Marks by 5 points for all students in the 'Science' stream.

Solution:

1. Using the IN Operator:

   SELECT * FROM STUDENT 
   WHERE City IN ('Delhi', 'Mumbai');
   

2. Using the DISTINCT Keyword:

   SELECT DISTINCT Stream FROM STUDENT;
   

3. Updating Data:

   UPDATE STUDENT 
   SET Marks = Marks + 5 
   WHERE Stream = 'Science';
   

6. Exam Revision & FAQs

Q1: What is the primary difference between DDL and DML commands?

Answer:

• DDL (Data Definition Language) commands define, alter, or drop the structure/schema of database objects (tables, indexes). They do not directly alter stored data records. Examples: CREATE, ALTER, DROP.
• DML (Data Manipulation Language) commands operate directly on the data records/tuples stored within tables to insert, modify, or delete contents without changing table structure. Examples: INSERT, UPDATE, DELETE.

Q2: Can a table have multiple Primary Keys? Explain.

Answer: No. A table can have only one Primary Key. However, a single Primary Key can be composed of multiple combined columns. When two or more columns together form a single primary key, it is called a Composite Primary Key.

Q3: What is the purpose of the LIKE operator in SQL? Name its two wildcard characters.

Answer: The LIKE operator is used in a WHERE clause to perform pattern matching on string columns.

1. % (Percent sign): Represents zero, one, or multiple characters.
2. _ (Underscore sign): Represents a single character.

Q4: If a table initially has $5$ columns and $10$ rows, and a user inserts $2$ new rows and adds $1$ new column, what will be the new Degree and Cardinality?

Answer:

• Initial state: $D_1 = 5$, $C_1 = 10$.
• Adding $1$ column increases the Degree: $D_2 = 5 + 1 = 6$.
• Inserting $2$ rows increases the Cardinality: $C_2 = 10 + 2 = 12$.
• Final Answer: New Degree = $6$, New Cardinality = $12$.