DB

8.1 Database Concepts

Understanding database concepts, relational databases, normalization, and entity-relationship modelling

Learning Objectives

By the end of this lesson, you will be able to:

  • Explain the limitations of using a file-based approach for data storage and retrieval
  • Describe the features of a relational database that address limitations of file-based approach
  • Understand and use terminology associated with relational database model
  • Use an entity-relationship (E-R) diagram to document a database design
  • Show understanding of the normalization process (1NF, 2NF, 3NF)
  • Explain why given set of database tables are, or are not, in 3NF
  • Produce normalized database design from description of database or given set of data

Key Terms

Database

Structured collection of related data stored in an efficient and compact manner

Entity

Anything that can have data stored about it (person, place, event)

Table

Group of similar data in a database with rows and columns

Record/Tuple

Row in a table representing one instance of an entity

Field/Attribute

Column in a table representing a property of the entity

Primary Key

Unique identifier for each record in a table

Candidate Key

Attribute(s) that could be chosen as primary key

Secondary Key

Candidate key not selected as primary key

Foreign Key

Attribute in one table that refers to primary key in another table

Referential Integrity

Ensures consistency and accuracy of relationships between tables

Normalization

Process to construct relational database with integrity and reduced data redundancy

Composite Primary Key

Primary key consisting of two or more attributes

One-to-Many Relationship

Each record in one table associated with multiple records in another table

Many-to-Many Relationship

Multiple records in one table associated with multiple records in another table

E-R Diagram

Entity-Relationship diagram showing visual representation of database design

Database Fundamentals

A database is a structured collection of related data stored in an efficient and compact manner that can be accessed by different applications programs.

What Does This Mean?

  • Efficient: Stored data can be accessed very easily and quickly
  • Compact: Stored data takes up as little space as possible
  • Related Data: Database contains data about particular topic
Examples of Databases:
  • Database of employees in an organization
  • Database of students in a college/university
  • Database of products in an online store
  • Database of patients in a hospital

Database Components

Entity

Anything that can have data stored about it (person, place, event)

Table

Group of similar data with rows and columns. Entity is implemented as table.

Record/Tuple

Row in a table representing one instance of an entity

Field/Attribute

Column in a table representing a property of the entity

Note:

When there is only a single table in database, this is called a 'Flat File Database'.

Real-Life Example: School Database

Imagine a school needs to store information about students. In a simple flat-file database:

StudentID FirstName LastName DateOfBirth Grade Address
S001 John Smith 2005-03-15 10 123 Main St
S002 Sarah Johnson 2006-07-22 9 456 Oak Ave
S003 Michael Brown 2005-11-30 10 789 Pine Rd

In this table:

  • Entity: Student
  • Table: The entire structure above
  • Record/Tuple: Each row (e.g., John Smith's information)
  • Field/Attribute: Each column (StudentID, FirstName, etc.)
  • Primary Key: StudentID (unique for each student)

Check Your Understanding: Database Fundamentals

Answer
  • [1 mark] An entity is anything that can have data stored about it (person, place, event)
  • [1 mark] A table is the implementation of an entity in a database with rows and columns
  • [Additional] For each entity in a database design, one table is created to store its data
Answer
  • [1 mark] The database contains data about a particular topic or theme
  • [1 mark] Examples include employee data for an organization, student data for a school, or product data for a store
  • [Additional] Related data means all information in the database is connected and relevant to the same subject
Answer
  • [1 mark] Efficient: Stored data can be accessed very easily and quickly
  • [1 mark] Compact: Stored data takes up as little space as possible
  • [Additional] Efficiency ensures quick retrieval, compactness minimizes storage requirements
Answer
  • [1 mark] A database that contains only a single table
  • [Additional] Simple structure with all data in one table, often leading to data redundancy

File-Based Approach vs Relational Database

In the file-based approach, data is stored in one or more flat files. Each department in an organization would have its own set of application programs to process data in these files.

Limitations of File-Based Approach

Key Problems

Data Inconsistency

Data can be altered by one application and not by another; becomes inconsistent

Data Redundancy

Data is duplicated across different files, wasting storage space and time

Data Dependency

Enquiries depend on structure of data and software used

Poor Security

No proper security system against illegal access of data

Difficult Updates

Hard to update records as same information stored in different places

Real Example: Company Departments

A company has two departments with separate file systems:

  • HR Department: Holds details on name, address, qualifications of each employee
  • Payroll Department: Holds details of name, address and salary of each employee
Problem:

Employee data is duplicated. If an employee changes address, it must be updated in two places. If only updated in one file, data becomes inconsistent.

Advantages of Relational Database

Reduced Redundancy

Storage space is not wasted as data items are only stored once, meaning little or no redundant data.

Data Consistency

Data altered in one application is available in another application, so data is consistent.

Data Independence

Enquiries available are not dependent on structure of data and software used, so data is independent.

File-Based vs Relational Database Comparison

File-Based System
HR_Department.txt
EmpID: E001
Name: John Smith
Address: 123 Main St
Qualification: BSc
Payroll_Department.txt
EmpID: E001
Name: John Smith
Address: 123 Main St
Salary: $5000
Same data stored in multiple files = Data Redundancy
Relational Database
EMPLOYEE
EmpID: E001
Name: John Smith
Address: 123 Main St
Qualification: BSc
links to
PAYROLL
EmpID: E001
Salary: $5000
Single source of truth = No Data Redundancy

Key Insight: In the relational database, employee address is stored only once in the EMPLOYEE table. The PAYROLL table references this through the EmpID foreign key. If John Smith moves, we only update his address in one place!

Real-Life Example: Hospital System

Imagine a hospital with different departments:

File-Based Approach (Problem)
  • Patient records in Admissions
  • Same patient data in Billing
  • Duplicate data in Medical Records
  • If patient address changes, must update in 3 places
  • If update missed, inconsistent data
Relational Database (Solution)
  • Single PATIENT table
  • ADMISSIONS table links via PatientID
  • BILLING table links via PatientID
  • Address change in one place only
  • All departments see updated address
Benefits
  • No data duplication
  • Data consistency maintained
  • Easier updates
  • Better security controls
  • Complex queries possible

Activity 1: Identifying File-Based Problems

A library uses a file-based system with separate files for different functions:

  1. Members.txt - contains member ID, name, address, phone
  2. Loans.txt - contains loan ID, member ID, book ID, due date
  3. Fines.txt - contains fine ID, member ID, amount, date
  4. Catalog.txt - contains book ID, title, author, ISBN

Identify three problems with this file-based approach and explain how a relational database would solve each problem.

Solution:
  1. Problem 1: Data Redundancy
    Member details (name, address, phone) might be duplicated in Members.txt, Loans.txt, and Fines.txt.
    Relational Solution: Store member details once in MEMBER table, reference via MemberID in other tables.
  2. Problem 2: Data Inconsistency
    If a member changes address, need to update in multiple files. If missed, inconsistent data.
    Relational Solution: Update address once in MEMBER table, all references automatically correct.
  3. Problem 3: Difficult Complex Queries
    Hard to query "Which members have overdue books and unpaid fines?" across multiple files.
    Relational Solution: Use SQL JOIN operations to easily combine data from MEMBER, LOAN, and FINE tables.

Check Your Understanding: File vs Relational

Answer
  • [1 mark] Data redundancy occurs when the same data is stored in multiple places
  • [1 mark] It is wasteful as it costs time and money, takes up storage space, and can lead to data inconsistency
  • [Additional] Example: Employee address stored in HR file and Payroll file separately
Answer
  • [1 mark] By storing each piece of data only once in the database
  • [1 mark] Other tables reference this single source through foreign keys, so updates in one place are reflected everywhere
  • [Additional] Example: Employee address stored once in EMPLOYEE table, referenced by PAYROLL table via EmployeeID
Answer
  • [1 mark] Data formats are defined in application programs
  • [1 mark] If there is a need to change data formats, whole programs have to be changed
  • [Additional] Different applications may hold data in different forms, causing problems when integrating data
Answer
  • [1 mark] File-based systems do not provide proper security system against illegal access of data
  • [1 mark] Anyone can easily change or delete data stored in files without proper access controls
  • [Additional] Relational databases have built-in security features like user authentication and access permissions

Relational Database Concepts

A relational database is a collection of relational tables. In the relational database model, each item of data is stored in a relation which is a special type of table.

Keys in Relational Databases

Primary Key

May be a single attribute or a combination of attributes. If primary key consists of more than one field, it's called a composite primary key.

Key Points:
  • Every table must have a primary key
  • Each tuple must have a value for primary key
  • Value must be unique
  • Primary key ensures integrity within table

Candidate Key

In some cases there may be more than one attribute for which unique values are guaranteed. Each one is a candidate key.

Example: Student Table

StudentID and Email could both be candidate keys as both are unique for each student.

Foreign Key

An attribute in one table that refers to primary key in another table.

Purpose:

Creates relationships between tables and maintains referential integrity.

Example: Theatrical Agency Database

Design for a database for Theatrical Agency might contain table definitions as:

Member Table
MemberID MemberGivenName MemberFamilyName BandName
0005 Xiangfei Jha ComputerKidz
0009 Mahesh Ravuru ITWizz
0001 Dylan Stoddart ComputerKidz
Band Table
BandName AgentID
ComputerKidz 01
ITWizz 07

Key Observations:

  • Primary Key in Band table: BandName (unique for each band)
  • Primary Key in Member table: MemberID (unique for each member)
  • Foreign Key in Member table: BandName references Band table
  • Relationship: One band can have many members (One-to-Many)

Referential Integrity

What is Referential Integrity?

Referential Integrity is a concept in relational database design that ensures consistency and accuracy of relationships between tables.

How it Works:

Use of a foreign key to ensure that a value can only be entered in one table when same value already exists in referenced table.

Why Important?
  • Makes sure data is consistent
  • Ensures all data is up-to-date
  • Prevents incorrect record operations
  • Ensures accurate query results

Example: School Database with Referential Integrity

Student Table
Student ID First Name Second Name Date Of Birth Class ID
S1276 Noor Baig 09/22/2010 7A
S1277 Ahmed Sayed 06/11/2010 7B
S2199 Tahir Hassan 01/30/2011 7A
Class Table
Class ID Teacher Name Location
7A Mr Khan Floor 2 Room 3
7B Miss Malik Floor 2 Room 4
7C Miss Gill Floor 2 Room 5
Referential Integrity in Action:

The database must not contain any values of a foreign key (Class ID in Student table) that are not matched to corresponding primary key (Class ID in Class table).

  • Student can only be assigned to Class ID that exists in Class table (7A, 7B, or 7C)
  • Cannot assign student to Class ID "7D" because it doesn't exist in Class table
  • If Class 7C is deleted from Class table, students cannot have Class ID 7C

Database Relationships Simulator

Select a relationship type to see how it works:

Select a relationship type to visualize

Relationship Types:

  • One-to-One: Each record in Table A links to exactly one record in Table B (e.g., Country - Capital City)
  • One-to-Many: One record in Table A links to multiple records in Table B (e.g., Class - Students)
  • Many-to-Many: Multiple records in Table A link to multiple records in Table B (e.g., Students - Courses)

Implementing Many-to-Many Relationships

A many-to-many relationship cannot be implemented directly with foreign keys. Solution: Create a junction table (linking table).

Student Table
STUDENT(StudentID, Name, Grade)
PK: StudentID
Course Table
COURSE(CourseID, Title, Teacher)
PK: CourseID
Junction Table
ENROLLMENT(StudentID, CourseID)
Composite PK: (StudentID, CourseID)
FK: StudentID → STUDENT
FK: CourseID → COURSE

How it works: The ENROLLMENT table links students to courses. One student can enroll in many courses, and one course can have many students.

Activity 2: Identifying Keys and Relationships

Examine the following database tables for a library system:

BOOK Table
ISBNTitleAuthorPublisherID
978-123456Database BasicsJohn SmithP001
978-654321Python GuideJane DoeP002
PUBLISHER Table
PublisherIDNameAddress
P001Tech Press123 Tech St
P002Code Books456 Code Ave
  1. Identify the primary key in each table
  2. Identify any foreign keys
  3. What type of relationship exists between BOOK and PUBLISHER?
  4. Explain how referential integrity is maintained between these tables
Solution:
  1. Primary Keys:
    • BOOK table: ISBN (unique identifier for each book)
    • PUBLISHER table: PublisherID (unique identifier for each publisher)
  2. Foreign Key:
    • PublisherID in BOOK table is a foreign key that references PublisherID in PUBLISHER table
  3. Relationship Type:
    • One-to-Many relationship (One publisher can publish many books, but each book has only one publisher)
  4. Referential Integrity:
    • A book cannot have a PublisherID that doesn't exist in the PUBLISHER table
    • If a publisher is deleted from PUBLISHER table, books from that publisher must either be deleted or reassigned
    • This ensures data consistency between the two tables

Check Your Understanding: Relational Concepts

Answer
  • [1 mark] A primary key uniquely identifies each record in its own table
  • [1 mark] A foreign key is an attribute in one table that refers to the primary key in another table
  • [Additional] Primary key ensures uniqueness within a table, foreign key creates relationships between tables
Answer
  • [1 mark] Referential integrity ensures consistency and accuracy of relationships between tables
  • [1 mark] It uses foreign keys to ensure values in one table exist in the referenced table
  • [1 mark] Example: In a school database, a student can only be assigned to a ClassID that exists in the Class table
  • [Additional] Prevents "orphaned" records that reference non-existent data
Answer
  • [1 mark] Create a junction table (also called linking table or associative table)
  • [1 mark] The junction table contains foreign keys that reference the primary keys of both tables
  • [1 mark] Example: For Students and Courses, create ENROLLMENT table with StudentID and CourseID
  • [Additional] The junction table often has a composite primary key made of the two foreign keys
Answer
  • [1 mark] A composite primary key consists of two or more attributes that together form the primary key
  • [1 mark] Used when no single attribute uniquely identifies each record, but the combination does
  • [Additional] Example: In an enrollment table, (StudentID, CourseID) together uniquely identify each enrollment
Answer
  • [1 mark] Candidate keys are attributes that could be chosen as primary key (guarantee unique values)
  • [1 mark] Secondary key is a candidate key that was not selected as the primary key
  • [Additional] Example: In Student table, both StudentID and Email might be candidate keys; if StudentID is chosen as primary key, Email becomes a secondary key

Entity-Relationship Modelling

An E-R diagram can be used to document the design of a database. This provides an understandable visual representation of how entities in a database are related.

E-R Diagram Components

Entities and Attributes

Entity

Represented as a rectangle in E-R diagram

Contains the entity name and its attributes
Attributes

Properties of the entity, listed inside the rectangle

Primary key is usually underlined or marked with (PK)
Example: STUDENT Entity
STUDENT
StudentID (PK)
FirstName
LastName
DateOfBirth
ClassID (FK)

Relationships

Relationship Lines

Connect entities to show how they relate

Cardinality

Shows how many entities participate in the relationship

e.g., one-to-one, one-to-many, many-to-many
Optional/Mandatory

Whether participation in relationship is required

Cardinality Notation:
| (one and only one)
O (zero or one)
< (one or many)
<|> (zero or many)

Example: School Database E-R Diagram

STUDENT
StudentID (PK)
FirstName
LastName
DateOfBirth
ClassID (FK)
|<
one class has many students
CLASS
ClassID (PK)
TeacherName
Location

Interpretation: This E-R diagram shows that one CLASS can have many STUDENTS (one-to-many relationship). The ClassID in STUDENT table is a foreign key that references ClassID in CLASS table.

Example: Photographer Database (P11 Nov 22)

A photographer creates a relational database to store data about photographs taken at birthday parties. Database, PHOTOGRAPHS, stores details of customer, the party, the photographs taken and the cameras used.

CUSTOMER (CustomerID, FirstName, LastName, Telephone)
PARTY (PartyID, CustomerID, PartyDate, StartTime)
PHOTO_DATA (PhotoID, PartyID, TimeTaken, CameraID)
CAMERA_DATA (CameraID, LensType, LightingType)
CUSTOMER
CustomerID (PK)
FirstName
LastName
Telephone
|
1:M
<
PARTY
PartyID (PK)
CustomerID (FK)
PartyDate
StartTime
|
1:M
<
PHOTO_DATA
PhotoID (PK)
PartyID (FK)
TimeTaken
CameraID (FK)
|
1:M
<
CAMERA_DATA
CameraID (PK)
LensType
LightingType

Relationships: 1:M between CUSTOMER and PARTY, 1:M between PARTY and PHOTO_DATA, 1:M between CAMERA_DATA and PHOTO_DATA.

Example: Films Database with Many-to-Many

Database, FILMS, stores information about films and actors:

ACTOR (ActorID, FirstName, LastName, DateOfBirth)
FILM_FACT (FilmID, FilmTitle, ReleaseDate, Category)
FILM_ACTOR (ActorID, FilmID)
ACTOR
ActorID (PK)
FirstName
LastName
DateOfBirth
<|
M:M
|>
FILM_FACT
FilmID (PK)
FilmTitle
ReleaseDate
Category
Implemented via junction table:
FILM_ACTOR
ActorID (FK)
FilmID (FK)
(Composite PK)

Note: Table FILM_ACTOR has a composite primary key (ActorID, FilmID) because neither key uniquely identifies each tuple by itself. One actor cannot appear in same film twice so together, they are unique.

E-R Diagram Builder

Select entities to build a simple E-R diagram:

Attributes: StudentID (PK), Name, Grade
Attributes: CourseID (PK), Title, Teacher
Attributes: StudentID (FK), CourseID (FK)
STUDENT
StudentID (PK)
Name
Grade
<|
M:M
|>
COURSE
CourseID (PK)
Title
Teacher
With junction table:
ENROLLMENT
StudentID (FK)
CourseID (FK)
(Composite PK)

Activity 3: Creating E-R Diagrams

A relational database, TECHNOLOGY, stores data about staff in a company and computer devices used by staff. Database has following tables:

STAFF (StaffID, FirstName, LastName, DateOfBirth, JobTitle)
DEVICE (DeviceID, Type, DatePurchased, StaffID)
  1. Identify the primary key in each table
  2. Identify any foreign keys
  3. Describe the relationship between the two tables
  4. Draw an E-R diagram for this database
Solution:
  1. Primary Keys:
    • STAFF table: StaffID (unique identifier for each staff member)
    • DEVICE table: DeviceID (unique identifier for each device)
  2. Foreign Key:
    • StaffID in DEVICE table is a foreign key that references StaffID in STAFF table
  3. Relationship:
    • Primary key StaffID in STAFF links to foreign key StaffID in DEVICE
    • One staff member can have many devices
    • Each device can only be assigned to one member of staff
    • This is a One-to-Many relationship (1:M)
  4. E-R Diagram:
    STAFF
    StaffID (PK)
    FirstName
    LastName
    DateOfBirth
    JobTitle
    |
    1:M
    <
    DEVICE
    DeviceID (PK)
    Type
    DatePurchased
    StaffID (FK)

Check Your Understanding: E-R Modelling

Answer
  • [1 mark] To document the design of a database
  • [1 mark] Provides an understandable visual representation of how entities in a database are related
  • [Additional] Helps database designers and developers understand the database structure before implementation
Answer
  • [1 mark] Entities are represented as rectangles in the diagram
  • [1 mark] Attributes are listed inside the rectangle, usually with the primary key underlined or marked (PK)
  • [Additional] Foreign keys are often marked (FK) to show relationships between entities
Answer
  • [1 mark] Cardinality determines how many records relate to each other in a relationship
  • [1 mark] It shows the numerical relationship between entities (e.g., one-to-one, one-to-many, many-to-many)
  • [Additional] Examples: | (one and only one), O (zero or one), < (one or many), <|> (zero or many)
Answer
  • [1 mark] One customer can have many parties (birthday parties photographed over time)
  • [1 mark] Each party belongs to only one customer
  • [Additional] This is represented in the database by CustomerID foreign key in PARTY table referencing CUSTOMER table
Answer
  • [1 mark] Show the two entities connected with a line labeled as many-to-many (M:M)
  • [1 mark] Indicate that a junction table will be needed to implement the relationship
  • [Additional] Example: ACTOR <|> FILM with a note about FILM_ACTOR junction table

Normalization

Normalization is used to construct a Relational Database that has integrity and in which Data Redundancy is reduced. Tables that are not normalized will be larger, harder to update, and more difficult to query.

Normal Forms

1

First Normal Form (1NF)

To achieve First Normal Form (1NF), these rules must be met:

Rules:
  • No repeating groups or repeating attributes
  • Atomic values (data cannot be further divided)
  • Unique rows (with a primary key)
  • Unique field names within table
Examples of Atomic Data:
  • NY344599 (Student ID)
  • ISBN # e.g. 1-931841-62-4
  • First name: 'John'
  • Telephone number
  • Complete description
2

Second Normal Form (2NF)

Rules for Second Normal Form (2NF):

Requirements:
  • Already in First Normal Form (1NF)
  • No partial dependencies
  • All non-key attributes must be fully dependent on every part of the primary key
Key Terms:
  • Non-key attributes: Attributes not part of primary key
  • Partial dependency: When non-key attribute depends only on part of composite primary key
  • Composite primary key: Primary key made of multiple attributes
3

Third Normal Form (3NF)

Rules for Third Normal Form (3NF):

Requirements:
  • Already in Second Normal Form (2NF)
  • No transitive dependencies
  • No non-key attributes that depend on another non-key attribute
Transitive Dependency:

When a non-key attribute depends on another non-key attribute, which itself depends on the primary key.

Worked Example: School Database Normalization

Un-normalized School database held in a single table:

Student ID First Name Second Name Date Of Birth Subject Name Subject Teacher Class ID Location Teacher Name Licence Number Address Teacher Date Of Birth
S1276 Noor Baig 09/22/2010 Maths, History, Geography Mr Yee, Miss Wu, Mr Khan 7A Floor 2 Room 3 Mr Khan 37952 School House 1 03/27/1985
Problems with Un-normalized Database:
  • Repeating groups (Subject Name, Subject Teacher repeated for each subject)
  • Data redundancy (Teacher details repeated for each student in same class)
  • Update anomalies (If Mr Khan leaves, need to update all records containing his details)
  • Insertion anomalies (Can't add a new class without students)
  • Deletion anomalies (If all students from Class 7B leave, class details lost)

Normalization Process Simulator

Follow the normalization process step by step:

Un-normalized School Database (0NF)

Single table with repeating groups:

STUDENT(StudentID, FirstName, SecondName, DateOfBirth,
  SubjectName, SubjectTeacher, SubjectName, SubjectTeacher,
  SubjectName, SubjectTeacher, ClassID, Location,
  TeacherName, LicenceNumber, Address, TeacherDateOfBirth)

Problems: Repeating attributes, data redundancy, update anomalies

Normalization Steps:

  • 0NF → 1NF: Remove repeating groups to separate table
  • 1NF → 2NF: Remove partial dependencies (non-key attributes dependent on part of composite key)
  • 2NF → 3NF: Remove transitive dependencies (non-key attributes dependent on other non-key attributes)

Complete Normalization Walkthrough

Step 1: Convert to First Normal Form (1NF)

Remove repeating attributes (subjects and subject teachers) to separate table:

STUDENT(StudentID, FirstName, SecondName, DateOfBirth,
  ClassID, Location, TeacherName, LicenceNumber,
  Address, TeacherDateOfBirth)
STUDENTSUBJECT(StudentID, SubjectName, SubjectTeacher)
Step 2: Convert to Second Normal Form (2NF)

In STUDENTSUBJECT table, SubjectTeacher depends only on SubjectName (partial dependency). Remove by creating SUBJECT table:

STUDENT(StudentID, FirstName, SecondName, DateOfBirth,
  ClassID, Location, TeacherName, LicenceNumber,
  Address, TeacherDateOfBirth)
STUDENTSUBJECT(StudentID, SubjectName)
SUBJECT(SubjectName, SubjectTeacher)
Step 3: Convert to Third Normal Form (3NF)

In STUDENT table, transitive dependencies exist. Location depends on ClassID, and teacher details depend on TeacherName. Create CLASS and TEACHER tables:

STUDENT(StudentID, FirstName, SecondName, DateOfBirth, ClassID)
CLASS(ClassID, Location, LicenceNumber)
TEACHER(LicenceNumber, TeacherName, Address, TeacherDateOfBirth)
STUDENTSUBJECT(StudentID, SubjectName)
SUBJECT(SubjectName, LicenceNumber)
Advantages of Normalized Database:
  • Data consistency: Each piece of information stored in one place
  • Data integrity: Enforces rules and constraints
  • Efficient storage: Reduces redundant information
  • Simplified updates: Updates made more efficiently
  • Easier querying: Data organized logically

Example: Not in 2NF

StudentName Subject Level SubjectTeacher
Tom Physics A SAN
Tom Chemistry A MEB

Problem: Primary key is (StudentName, Subject). SubjectTeacher depends only on Subject (partial dependency). Not in 2NF.

Example: Not in 3NF

StaffID StaffName City Country
56 ALI Lahore Pakistan
78 Ahmed Islamabad Pakistan

Problem: Country depends on City (non-key depends on non-key). If we know City, we can find Country. Not in 3NF.

Activity 4: Normalization Practice

Examine the following table and answer the questions:

OrderID CustomerID CustomerName CustomerAddress ProductID ProductName Quantity Price SupplierID SupplierName
O001 C100 John Smith 123 Main St P001 Laptop 2 $800 S01 Tech Supplies
O001 C100 John Smith 123 Main St P002 Mouse 1 $25 S02 Peripherals Inc
O002 C101 Sarah Jones 456 Oak Ave P001 Laptop 1 $800 S01 Tech Supplies
  1. What normal form is this table in? Explain your answer.
  2. Identify any repeating groups or partial dependencies.
  3. Identify any transitive dependencies.
  4. Normalize this table to 3NF, showing the resulting tables.
Solution:
  1. Current Normal Form:
    • The table is in 1NF (no repeating groups, atomic values)
    • Not in 2NF because of partial dependencies (if composite PK is (OrderID, ProductID))
  2. Problems Identified:
    • Customer details (CustomerName, CustomerAddress) repeated for each order line
    • Product details (ProductName, Price, SupplierID, SupplierName) repeated
    • Partial dependency: Customer details depend only on CustomerID, not on ProductID
    • Partial dependency: Product details depend only on ProductID, not on OrderID
  3. Transitive Dependencies:
    • SupplierName depends on SupplierID (non-key depends on non-key if SupplierID is non-key)
    • This would be a transitive dependency if the table was in 2NF
  4. Normalized to 3NF:
    ORDER(OrderID, CustomerID, OrderDate)
    CUSTOMER(CustomerID, CustomerName, CustomerAddress)
    PRODUCT(ProductID, ProductName, Price, SupplierID)
    SUPPLIER(SupplierID, SupplierName)
    ORDER_DETAIL(OrderID, ProductID, Quantity)

    Primary keys: OrderID, CustomerID, ProductID, SupplierID, (OrderID, ProductID) for ORDER_DETAIL

Check Your Understanding: Normalization

Answer
  • [1 mark] Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity
  • [1 mark] It involves dividing large tables into smaller, related tables and defining relationships between them
  • [1 mark] Important because it minimizes data duplication, reduces update anomalies, and ensures data consistency
  • [Additional] Normalized databases are more efficient, easier to maintain, and less prone to errors
Answer
  • [1 mark] A partial dependency occurs when a non-key attribute depends on only part of a composite primary key
  • [1 mark] This violates 2NF because all non-key attributes should be fully dependent on every part of the primary key
  • [1 mark] Example: In table (StudentID, Subject, Teacher), if Teacher depends only on Subject (not on StudentID), it's a partial dependency
  • [Additional] To fix, remove the partially dependent attribute to a separate table
Answer
  • [1 mark] A transitive dependency occurs when a non-key attribute depends on another non-key attribute
  • [1 mark] This violates 3NF because non-key attributes should depend only on the primary key
  • [1 mark] Example: In table (StaffID, StaffName, City, Country), Country depends on City, which depends on StaffID
  • [Additional] To fix, remove the transitively dependent attribute to a separate table
Answer
  • [1 mark] Data consistency: Each piece of information stored in one place
  • [1 mark] Data integrity: Enforces rules and constraints to maintain accurate data
  • [1 mark] Efficient storage: Reduces redundant information, saving disk space
  • [Additional] Simplified updates, easier querying, reduced risk of update anomalies
Answer
Task Normalization Stage
Remove any partial key dependencies 1NF to 2NF
Remove any repeating groups of attributes 0NF to 1NF
Remove any non-key dependencies 2NF to 3NF

Key Takeaways

  • A database is a structured collection of related data stored efficiently and compactly
  • File-based systems suffer from data redundancy, inconsistency, dependency, and poor security
  • Relational databases solve file-based problems by storing data once and linking tables
  • A primary key uniquely identifies each record; a foreign key links tables
  • Referential integrity ensures consistency between related tables
  • Relationships can be one-to-one, one-to-many, or many-to-many
  • Many-to-many relationships require a junction table with composite primary key
  • E-R diagrams visually represent database structure with entities and relationships
  • Normalization organizes data to reduce redundancy and improve integrity
  • 1NF eliminates repeating groups and ensures atomic values
  • 2NF eliminates partial dependencies (non-key fully dependent on PK)
  • 3NF eliminates transitive dependencies (non-key depends only on PK)
  • Normalization advantages include data consistency, integrity, efficient storage, and easier updates
  • Database design involves identifying entities, attributes, keys, and relationships

Question Bank

Marking Scheme & Answer
  • [2 marks] File-based approach: Data stored in separate files for each department/application, leading to data duplication and separate processing programs
  • [1 mark] Limitation 1: Data Redundancy - Same data stored in multiple files, wasting storage space and requiring multiple updates
  • [1 mark] Limitation 2: Data Inconsistency - Data altered in one file may not be updated in others, leading to inconsistent data
  • [1 mark] Limitation 3: Data Dependency - Programs depend on specific data formats; changes require program modifications
  • [1 mark] Database solution: Stores data once, links through relationships, ensures consistency through referential integrity
Marking Scheme & Answer
  • [2 marks] Primary Key: Unique identifier for each record in a table (e.g., StudentID in STUDENT table). Ensures no duplicate records.
  • [2 marks] Foreign Key: Attribute in one table that references primary key in another table (e.g., ClassID in STUDENT table references ClassID in CLASS table). Creates relationships.
  • [2 marks] Referential Integrity: Ensures consistency between related tables. Foreign key values must exist as primary key values in referenced table. Prevents orphaned records.
  • [Additional] Example: In school database, student can only have ClassID that exists in CLASS table. Database enforces this constraint.
Marking Scheme & Answer
  • [1 mark] Many-to-many relationship: One student can enroll in many courses, one course can have many students
  • [1 mark] Cannot be implemented directly with foreign keys (foreign key can only reference one record)
  • [1 mark] Solution: Create a junction table (also called linking or associative table)
  • [1 mark] Junction table contains foreign keys referencing both tables (e.g., ENROLLMENT with StudentID and CourseID)
  • [1 mark] Junction table often has composite primary key (StudentID, CourseID) to ensure unique enrollment records
  • [Additional] Example tables: STUDENT(StudentID, Name), COURSE(CourseID, Title), ENROLLMENT(StudentID, CourseID, EnrollmentDate)
Marking Scheme & Answer
MEMBER
MemberID (PK)
Name
Address
JoinDate
<|
M:M via LOAN
|>
BOOK
ISBN (PK)
Title
Author
YearPublished
With junction table:
LOAN
LoanID (PK)
MemberID (FK)
ISBN (FK)
LoanDate
DueDate

Explanation: M:M relationship between MEMBER and BOOK implemented via LOAN junction table. LOAN records each borrowing transaction with dates.

Marking Scheme & Answer

Step 1: Convert to 1NF

Table is already in 1NF (no repeating groups, atomic values). Composite PK: (EmployeeID, ProjectID)

Step 2: Convert to 2NF (remove partial dependencies)

EMPLOYEE(EmployeeID, EmployeeName, Department, DepartmentManager)
PROJECT(ProjectID, ProjectName)
EMPLOYEE_PROJECT(EmployeeID, ProjectID, HoursWorked)

Employee details depend only on EmployeeID (partial dependency). Project details depend only on ProjectID.

Step 3: Convert to 3NF (remove transitive dependencies)

EMPLOYEE(EmployeeID, EmployeeName, DepartmentID)
DEPARTMENT(DepartmentID, DepartmentName, DepartmentManager)
PROJECT(ProjectID, ProjectName)
EMPLOYEE_PROJECT(EmployeeID, ProjectID, HoursWorked)

DepartmentManager depends on Department (transitive dependency). Create separate DEPARTMENT table.

Final 3NF Tables:

  • EMPLOYEE(EmployeeID, EmployeeName, DepartmentID)
  • DEPARTMENT(DepartmentID, DepartmentName, DepartmentManager)
  • PROJECT(ProjectID, ProjectName)
  • EMPLOYEE_PROJECT(EmployeeID, ProjectID, HoursWorked)
Marking Scheme & Answer

Why not in 3NF:

  • Assuming composite PK: (OrderID, ProductID)
  • CustomerCountry depends on CustomerCity (transitive dependency - non-key depends on non-key)
  • ProductName depends only on ProductID (partial dependency if in 1NF)
  • Customer details repeated for each product in order

Normalized to 3NF:

ORDER(OrderID, CustomerID, OrderDate)
CUSTOMER(CustomerID, CustomerName, CityID)
CITY(CityID, CityName, Country)
PRODUCT(ProductID, ProductName)
ORDER_DETAIL(OrderID, ProductID, Quantity)

Explanation: Removed transitive dependency (Country depends on City) by creating CITY table. Removed partial dependencies by separating customer and product details.

Marking Scheme & Answer
  • [1 mark] Reduced data redundancy: Data items stored only once, saving storage space
  • [1 mark] Data consistency: Updates in one place reflected throughout database
  • [1 mark] Data independence: Data separate from applications, easier to modify
  • [1 mark] Improved security: Better access controls and security features
  • [Additional] Complex queries easier, referential integrity maintained, reduced program-data dependency
Marking Scheme & Answer

0NF to 1NF:

Remove repeating groups. Example: Student subjects moved to separate table. Ensure atomic values, unique rows with PK.

1NF to 2NF:

Remove partial dependencies. All non-key attributes must depend on entire PK. Example: SubjectTeacher depends only on Subject (part of composite PK), so move to SUBJECT table.

2NF to 3NF:

Remove transitive dependencies. No non-key attribute should depend on another non-key attribute. Example: Country depends on City, so create separate CITY/COUNTRY tables.

Example: School Database

  • 0NF: Single table with repeating subjects
  • 1NF: STUDENT and STUDENTSUBJECT tables
  • 2NF: Add SUBJECT table for SubjectTeacher
  • 3NF: Add CLASS and TEACHER tables for location and teacher details
Marking Scheme & Answer
  • [1 mark] Definition: Referential integrity ensures consistency and accuracy of relationships between tables
  • [1 mark] Mechanism: Uses foreign keys to ensure values in one table exist in referenced table
  • [1 mark] Importance: Prevents orphaned records (records referencing non-existent data)
  • [1 mark] Importance: Ensures data consistency when updates/deletes occur
  • [Additional] Example: Cannot assign student to ClassID that doesn't exist in CLASS table. Database enforces this automatically.
Marking Scheme & Answer

Tables with Attributes:

CUSTOMER(CustomerID, FirstName, LastName, LicenseNumber, Phone)
CAR(RegistrationNumber, Make, Model, Year, DailyRate)
RENTAL(RentalID, CustomerID, RegistrationNumber, StartDate, EndDate, TotalCost)

Keys:

  • Primary Keys: CustomerID, RegistrationNumber, RentalID
  • Foreign Keys: CustomerID in RENTAL references CUSTOMER, RegistrationNumber in RENTAL references CAR

Relationships:

  • CUSTOMER to RENTAL: One-to-Many (One customer can have many rentals over time)
  • CAR to RENTAL: One-to-Many (One car can be rented many times over time)
  • RENTAL is junction table implementing M:M between CUSTOMER and CAR over time

E-R Diagram would show: CUSTOMER --< RENTAL >-- CAR (with Rental as junction table recording each rental transaction)