Database Management System - Relational database concepts, primary keys, foreign keys, creating tables, and basic SQL queries

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

NOT NULL

NOT NULL

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

INT

INTEGER

101

-45

VARCHAR(n)

'Aarav Sharma'

CHAR(n)

'M'

'STD10'

DECIMAL(p, s)

95.50

DECIMAL(4,2)

DATE

YYYY-MM-DD

'2008-05-14'

1.4 Detailed SQL Syntax & Operations

1. Creating Tables (

CREATE TABLE

)

The

CREATE TABLE

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

-- 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

$\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

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

5. Deleting Records (

DELETE

)

The

DELETE

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

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

• 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

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

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

WHERE

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$.