RDBMS Notes: Concepts, SQL, Normalization and ER Modelling

1. DBMS and RDBMS Basics

For example, a college database may store student details in one table, course details in another, and enrolment details in a third table. Instead of repeating the complete course information for every student, the tables are linked using keys.

DBMS vs RDBMS

Data Hierarchy

1. Bit: The smallest unit of digital data, either 0 or 1.
2. Byte: Commonly a group of eight bits.
3. Field / Attribute / Column: One data item, such as StudentName.
4. Record / Tuple / Row: A complete set of related fields for one item.
5. Table / Relation: A collection of rows with the same structure.
6. Database: An organized collection of related tables and other database objects.

Important Relational Terms

• Relation: A table in the relational model.
• Tuple: A row in a relation.
• Attribute: A column in a relation.
• Degree: Number of attributes (columns) in a relation.
• Cardinality: Number of tuples (rows) in a relation.
• Schema: The structure of the database, including tables, columns, constraints, and relationships.
• Instance: The actual data stored in the database at a particular time.

2. Three-Level Database Architecture

The ANSI/SPARC three-schema architecture separates user views, logical design, and physical storage. Its main purpose is to reduce complexity and support data independence.

1. External Level (View Level): The user-specific view of data. A teacher may see student marks, while an accounts employee sees fee details.
2. Conceptual Level (Logical Level): The complete logical structure of the database, including entities, relationships, and constraints.
3. Internal Level (Physical Level): How data is physically stored, indexed, and organized on storage devices.

3. Keys and Integrity Constraints

Constraints are rules that protect data quality. They stop invalid, duplicate, or inconsistent values from entering the database.

Types of Keys

• Super key: One or more attributes that uniquely identify a row. It may contain unnecessary attributes.
• Candidate key: A minimal super key. No attribute can be removed without losing uniqueness.
• Primary key: The candidate key chosen to identify each row. It must be unique and cannot be NULL.
• Alternate key: A candidate key that was not selected as the primary key.
• Composite key: A key made from two or more columns.
• Foreign key: A column or set of columns that refers to a key in another table.

Example

CREATE TABLE Department (
  DepartmentID INT PRIMARY KEY,
  DepartmentName VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE Student (
  StudentID INT PRIMARY KEY,
  StudentName VARCHAR(100) NOT NULL,
  DepartmentID INT,
  CONSTRAINT fk_student_department
    FOREIGN KEY (DepartmentID)
    REFERENCES Department(DepartmentID)
);

In this example, StudentID is the primary key of the Student table. DepartmentID in the Student table is a foreign key, ensuring that a student refers only to an existing department.

Common Constraints

• NOT NULL: A value is required.
• UNIQUE: Duplicate values are not allowed.
• PRIMARY KEY: Uniquely identifies each row.
• FOREIGN KEY: Maintains valid relationships between tables.
• CHECK: Ensures values meet a stated condition.
• DEFAULT: Supplies a value when one is not provided.

4. SQL and Its Categories

SQL stands for Structured Query Language. It is the standard language used to work with relational databases. SQL syntax differs slightly between database systems, but its main concepts remain the same.

DELETE, TRUNCATE and DROP

SELECT Query Example

SELECT DepartmentID, COUNT(*) AS TotalStudents
FROM Student
WHERE DepartmentID IS NOT NULL
GROUP BY DepartmentID
HAVING COUNT(*) >= 10
ORDER BY TotalStudents DESC;

This query finds departments with at least ten students and displays them from the largest count to the smallest.

Logical Processing Order of a SELECT Query

Although we write SELECT first, a simplified logical processing order is:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

• WHERE: Filters individual rows before grouping.
• GROUP BY: Creates groups for aggregate calculations.
• HAVING: Filters groups after aggregation.
• ORDER BY: Sorts the final result.

5. Transactions and ACID Properties

A transaction is a logical unit of work that should either complete correctly or leave the database unchanged. Transferring money between two accounts is a common example: the debit and credit operations must both succeed, or neither should take effect.

1. Atomicity: All operations in a transaction succeed together, or all are undone.
2. Consistency: A completed transaction moves the database from one valid state to another valid state.
3. Isolation: Concurrent transactions should not interfere in a way that produces incorrect results.
4. Durability: After COMMIT, successful changes survive system failures.

6. Normalization

Normalization is a database-design process that organizes data to reduce unnecessary repetition and avoid update problems. The goal is not to create as many tables as possible; it is to store facts in the right place while keeping the design practical.

Common Data Anomalies

• Insertion anomaly: A fact cannot be recorded unless another unrelated fact is also available.
• Update anomaly: The same fact appears in many rows and must be changed repeatedly.
• Deletion anomaly: Deleting one row accidentally removes useful information about another entity.

Normal Forms

• First Normal Form (1NF): Each field contains atomic values, and there are no repeating groups or multi-valued columns.
• Second Normal Form (2NF): The table is in 1NF, and every non-key attribute depends on the whole candidate key, not only part of a composite key.
• Third Normal Form (3NF): The table is in 2NF, and non-key attributes do not depend on other non-key attributes.
• Boyce-Codd Normal Form (BCNF): For every functional dependency X → Y, X must be a super key. BCNF is stricter than 3NF.
• 4NF and 5NF: Advanced forms that address multivalued dependencies and certain join dependencies.

Simple 3NF Example

Suppose a table stores StudentID, StudentName, DepartmentID, DepartmentName. DepartmentName depends on DepartmentID, not directly on StudentID. To avoid repeated department names, separate the design into:

Student(StudentID, StudentName, DepartmentID)
Department(DepartmentID, DepartmentName)

7. ER Model and Relationships

The Entity-Relationship (ER) model is used before implementation to represent real-world data requirements visually. It identifies entities, their attributes, and the relationships between them.

Entities and Attributes

• Entity: A distinguishable object about which data is stored, such as Student, Course, Employee, or Book.
• Attribute: A property of an entity, such as StudentName or DateOfBirth.
• Strong entity: Has its own primary key and exists independently.
• Weak entity: Depends on another entity for identification and existence.
• Composite attribute: Can be divided into parts, such as Address into HouseNo, Street, City, and PIN.
• Multivalued attribute: May have multiple values, such as multiple phone numbers.
• Derived attribute: Calculated from another value, such as Age from DateOfBirth.

Relationship Cardinality

• One-to-One (1:1): One person has one passport, and one passport belongs to one person.
• One-to-Many (1:N): One department can have many students, but each student belongs to one department.
• Many-to-Many (M:N): A student can enrol in many courses, and a course can have many students.

A many-to-many relationship is usually implemented with a junction table. For example:

Student(StudentID, StudentName)
Course(CourseID, CourseName)
Enrollment(StudentID, CourseID, EnrolledOn)

8. SQL Joins

Joins combine related data from two or more tables. They are essential because normalized databases store related facts in separate tables.

INNER JOIN Example

SELECT s.StudentName, d.DepartmentName
FROM Student AS s
INNER JOIN Department AS d
  ON s.DepartmentID = d.DepartmentID;

9. RDBMS Exam Preparation Tips

• Learn the difference between a primary key, candidate key, super key, and foreign key with examples.
• Remember that degree means columns, while cardinality means rows.
• Practise CREATE TABLE statements using PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, and CHECK.
• Understand the difference between WHERE and HAVING.
• Write ACID properties with a banking transaction example.
• For normalization questions, identify the dependency before deciding the normal form.
• Draw ER diagrams using meaningful entities, attributes, keys, and relationship cardinality.
• Do not confuse DELETE, TRUNCATE, and DROP.

Frequently Asked Questions

What is the difference between a schema and an instance?

A schema is the database blueprint, while an instance is the actual data stored at a particular point in time.

Can a table have more than one primary key?

A table has one primary-key constraint, but that primary key may be composite and contain multiple columns.

Why is normalization important?

Normalization reduces duplicate data and helps prevent insertion, update, and deletion anomalies.

What is the purpose of a foreign key?

A foreign key connects related tables and helps maintain referential integrity by preventing invalid references.