Structuring Data 📝

What is a Relational Database?

• It’s a collection of data items with predefined relationships between them.

• Data is organized into tables, which consist of columns (attributes) and rows (records or tuples).

• Each row in a table is uniquely identified by a primary key.

• Relationships between different tables are established using foreign keys, which are primary keys from one table used in another.

Anatomy of a Table

The video shows a sample customer table to illustrate the concepts:

• Table: The entire structure (e.g., a “Customers” table).

• Column (Attribute): A vertical category of data (e.g., Firstname, Birthdate).

• Row (Tuple): A horizontal record representing a single entity (e.g., all information for one customer).

• Primary Key: A column with a unique value for each row, used for identification (e.g., CustomerID).

• Data Value: The actual information in a single cell (e.g., “John”).

Key Features of Relational Databases

The video highlights four main features:

• SQL (Structured Query Language): The standard language used to interact with relational databases for tasks like adding, updating, deleting, and retrieving data.

• Data Integrity: The assurance of accuracy and consistency of data over its entire lifecycle. Relational databases enforce this through constraints.

• Transactions: A sequence of operations performed as a single logical unit of work. A transaction is “all-or-nothing”—it must either complete successfully (commit) or be entirely undone (rollback).

• ACID Compliance: A set of properties that guarantee transaction reliability:

  • Atomicity: Transactions are indivisible; they either happen completely or not at all.

  • Consistency: A transaction brings the database from one valid state to another.

  • Isolation: Concurrent transactions do not interfere with each other.

  • Durability: Once a transaction is committed, its changes are permanent, even in the event of a system failure.

What is Normalization? Normalization is a database design technique used to organize data into multiple, smaller, related tables. Its primary goal is to minimize data redundancy.

The Problem: Data Redundancy Data redundancy is the unnecessary repetition of data in a database. The video illustrates this with an example of a single STUDENTS TABLE with the following columns:

• rollno

• name

• branch

• hod (Head of Department)

• office_tel

In this table, if there are multiple students from the same branch (e.g., Computer Science - CSE), the information for branch, hod, and office_tel is repeated for every single student record. This redundancy leads to several problems, known as anomalies:

• Insertion Anomaly: When adding a new student to the CSE branch, you must re-enter all the branch details (HOD name, office telephone number) again. This is inefficient and prone to error.

• Deletion Anomaly: If you were to delete the records of all students from a particular branch, you would also lose all information about that branch itself (who the HOD is, the office phone number), because that data only existed within the student records.

• Updation (or Modification) Anomaly: If the Head of Department (HOD) for the CSE branch changes, you would have to find and update every single student record for that branch. If even one record is missed, the database becomes inconsistent, with conflicting information about who the HOD is.

The Solution: Applying Normalization Normalization solves these problems by following a “Divide and Rule” approach. It breaks down the large, problematic table into smaller, more manageable tables.

In the example, the single STUDENTS TABLE is split into two separate tables:

1. New Student Table:

   • rollno, name, branch

   • This table only stores information specific to each student.

2. New Branch Table:

   • branch, hod, office_tel

   • This table stores information specific to each branch. The branch details (like for CSE) are now stored only once.

   These two tables are related by the common branch column, which acts as a link between them.

   How This Solves the Problems:

• Insertion Solved: To add a new student, you only add a row to the Student Table. The branch information already exists in the Branch Table and does not need to be repeated.

• Deletion Solved: You can delete all student records from the Student Table without losing the branch information, which is safely stored in the Branch Table.

• Updation Solved: If the HOD changes, you only need to update it in one single place—the corresponding row in the Branch Table. This change is automatically reflected for all students in that branch because of the relationship between the tables.

• Introduction to 1NF: The First Normal Form is the first and most fundamental step in the normalization process. A table that is not in 1NF is considered to have a poor database design. Achieving 1NF is a prerequisite for moving to higher normal forms like 2NF and 3NF.

• The Four Rules of 1NF: To be in First Normal Form, a table must adhere to four basic rules:

  1. Atomicity: Each column must contain only atomic (single, indivisible) values. A single cell cannot hold multiple values.

  2. Homogeneity: All values within a single column must be of the same data type.

  3. Unique Column Names: Every column in the table must have a unique name.

  4. Order Independence: The order in which data (rows) is stored does not matter.

• Example of 1NF Violation and Solution: The video demonstrates these rules using a STUDENTS TABLE with columns for rollno, name, and subject:

  • Violation: The table initially violates 1NF because the subject column contains multiple subjects in a single cell (e.g., “OS, CN”).

  • Solution: To bring the table into 1NF, the multi-valued subject column is broken down. This is done by creating a separate row for each subject a student has taken, which results in the student’s rollno and name being repeated but ensures that every cell contains only a single value.

Understanding the Rules of Second Normal Form (2NF)

For a table to be considered in Second Normal Form, it must satisfy two essential conditions:

1. It must already be in First Normal Form (1NF). This means the table has a primary key, and all columns contain atomic (indivisible) values, with no repeating groups.

2. It must not have any Partial Dependencies. This is the core rule of 2NF.

   Explaining Dependency and Primary Keys To understand partial dependency, the video first explains the concept of functional dependency using a simple STUDENTS table.

• Example 1: The STUDENTS Table

  • This table contains columns like student_id, name, branch, and address.

  • The student_id is the Primary Key, as it uniquely identifies each student record.

  • All other columns in the table (like name and branch) are functionally dependent on the student_id. This means if you know the student_id, you can uniquely determine the student’s name, their branch, and their address.

Explaining Partial Dependency with a Composite Key Partial dependency occurs when a table has a composite primary key (a primary key made up of two or more columns), and a non-key column depends on only a part of that composite key, not the whole thing.

To illustrate this, the video introduces a more complex scenario with three tables: STUDENT, SUBJECT, and a new SCORE table.

• Example 2: The SCORE Table

  • This table is designed to store the marks students get in different subjects.

  • Its columns are: score_id, student_id, subject_id, marks, and teacher.

  • In this table, no single column can uniquely identify a row. A student can have multiple scores (for different subjects), and a subject is taken by multiple students.

  • Therefore, the primary key must be a composite key formed by (student_id + subject_id). This combination uniquely identifies a specific student’s score in a specific subject.

  • The problem arises with the teacher column. The teacher of a subject (e.g., Mr. J teaches Java) depends only on the subject_id, not on which student (student_id) is taking the course.

  • This is a partial dependency: The teacher column depends on only one part (subject_id) of the two-part composite primary key.

How to Achieve 2NF by Removing Partial Dependency A table with partial dependency is not in 2NF. To fix this, the dependency must be removed by decomposing the table.

1. Identify the Partial Dependency: Recognize that the teacher column depends on subject_id but not student_id.

2. Decompose the Table: The solution is to move the column causing the issue (teacher) along with the part of the key it depends on (subject_id) into a separate, more appropriate table.

3. Create a New Structure:

   • The SCORE table is simplified to contain only score_id, student_id, subject_id, and marks. Its primary key remains (student_id + subject_id).

   • The teacher information is moved to the SUBJECT table. The SUBJECT table now contains subject_id, subject_name, and teacher.

   After this change, the SCORE table has no more partial dependencies, and the entire database structure is now in Second Normal Form. The video also notes that an even better design would be to create a separate TEACHER table to avoid redundancy if one teacher teaches multiple subjects.

• Recap of 2NF: The video starts by recapping the 2NF state, where the database consists of three tables: Student, Subject, and Score. This was done to eliminate partial dependencies.

• Introducing a New Problem: To demonstrate 3NF, two new columns, exam_name and total_marks, are added to the Score table.

• Defining 3NF: A table is in 3rd Normal Form if:

  1. It is already in 2nd Normal Form (2NF).

  2. It has no transitive dependencies.

• Explaining Transitive Dependency:

  • A transitive dependency exists when a non-primary key attribute depends on another non-primary key attribute.

  • In the example, the primary key for the Score table is a composite key of (student_id + subject_id).

  • The video shows that the total_marks column does not depend on the primary key. Instead, total_marks is dependent on the exam_name column (e.g., a “Practical” exam has different total marks than a “Theory” exam).

  • Since total_marks depends on exam_name (a non-key attribute), this is a transitive dependency.

• The Solution for 3NF:

  • To eliminate the transitive dependency, the problematic attributes (exam_name and total_marks) are moved out of the Score table.

  • A new table is created. This new table contains exam_name (as its primary key) and total_marks.

  • The Score table is then modified to simply hold a reference to the exam_name.

By the end, the database is in 3NF, now consisting of four tables (Student, Subject, Score, and Exam), effectively removing the transitive dependency and making the database more robust and less redundant.