Unit 3.3
The Database Approach and Relational Model
Learning Objectives 🎯
By the end of this chapter, you will be able to:
- ✅ Define the database approach and the role of a Database Management System (DBMS).
- ✅ Explain the key advantages of the database approach, like minimizing redundancy.
- ✅ Describe the basic components of the relational database model.
- ✅ Understand the purpose of a data model and an Entity-Relationship Diagram (ERD).
The Old Way: File-Based Chaos
Before databases, every application stored its own data in separate files.
Problems 🌪️
- Data Redundancy: Same data (e.g., customer address) stored in multiple files.
- Data Inconsistency: Updating one file didn't update others.
- Data Isolation: Difficult to share data between departments.
- Poor Security: Hard to control who sees what data.
⚡ Sort It: File System vs Database (1/2)
Click a statement to select it, then click the correct zone to classify it.
🌪️ File-Based Problem
✅ Database Advantage
⚡ Sort It: File System vs Database (2/2)
🌪️ File-Based Problem
✅ Database Advantage
The Solution: The Database Approach
A modern approach that creates a centralized, shared repository of data.
The goal is to create a "single source of truth" for the entire organization.
Database 🗃️
A logically coherent collection of related data.
DBMS ⚙️
The software that manages and controls access to the database.
Examples of DBMS: MySQL, Oracle, Microsoft SQL Server, PostgreSQL
How it Works: The DBMS as Gatekeeper
Applications and users don't touch the data directly. They go through the DBMS.
🎬 Watch: What a DBMS Does
🧩 Normalization: One Fact, One Place
Normalization is the process of splitting data into well-designed tables so each fact is stored only once.
❌ One big table
Library loans: MemberName, MemberPhone, BookTitle, Author, LoanDate
A member who borrows 20 books has their phone number stored 20 times.
✅ Normalized
Members(MemberID, Name, Phone)Books(BookID, Title, Author)Loans(LoanID, MemberID, BookID, LoanDate)
Change the phone number once, and every loan sees it.
Normalization is how the database approach actually minimizes redundancy.
Key Advantages of the Database Approach ⚡
1. Minimizes Redundancy & Inconsistency
Data is stored once. An update in one place is an update for everyone.
This creates a "Single Source of Truth", ensuring all departments use the same, correct data.
2. Improves Data Sharing & Integration
Centralized data is easy to share across departments, providing a holistic view of the business.
More Advantages...
3. Increases Data Security 🔒
The DBMS provides a sophisticated security framework.
- Define precise user access controls.
- Grant specific permissions (Read, Create, Update, Delete).
- Centralized security is easier to manage.
4. Enforces Data Standards & Integrity
The DBMS can enforce rules to ensure data quality.
Example Rule: An `order_quantity` field must be a positive number. The DBMS will reject any attempt to enter a negative value.
⚡ DBMS Integrity Guard: eSewa Transaction
Fill in the form. The DBMS will enforce data integrity rules before accepting the transaction.
The Relational Model: Organizing the Data 📊
The most popular database model. Data is organized into tables (relations).
Table (Relation)
A collection of rows and columns.
Row (Record)
Represents a single item or entity (e.g., one customer).
Column (Field)
Represents a characteristic of that entity (e.g., customer name).
The Relational Model: Organizing the Data 📊 (cont.)
Example: A `Customers` Table
CustomerID | FirstName | LastName | City
-----------|-----------|-----------|-----------
101 | Ram | Thapa | Kathmandu
102 | Sita | Sharma | Pokhara⚡ FK Link Explorer: See Tables Connect
Click a customer row to highlight their related orders via the Foreign Key (FK).
| CustomerID (PK) | Name | City |
|---|---|---|
| C001 | Ram Thapa | Kathmandu |
| C002 | Sita Sharma | Pokhara |
| C003 | Bikash Rai | Biratnagar |
| OrderID | CustID (FK) | Product | Amount |
|---|---|---|---|
| O101 | C001 | Laptop | Rs.65,000 |
| O102 | C002 | Mobile | Rs.28,000 |
| O103 | C001 | Headphones | Rs.3,500 |
| O104 | C003 | Tablet | Rs.45,000 |
| O105 | C002 | Charger | Rs.1,500 |
Connecting the Dots: Primary & Foreign Keys
Tables are linked together using special columns called keys.
🔑 Primary Key (PK)
A unique identifier for each row in a table. No two rows can have the same PK.
Example: `CustomerID` in the `Customers` table.
🔗 Foreign Key (FK)
A primary key from one table that is placed in another table to create a link.
Example: `CustomerID` in the `Orders` table.
This link allows us to see all orders placed by a specific customer!
🎬 Watch: Primary vs Foreign Keys
🆔 Real Case: A Primary Key for a Nation
- Aadhaar is India's 12-digit unique identity number, issued by the UIDAI since 2010.
- Like a primary key: no two residents share a number, and the number never changes.
- Like a foreign key: banks, mobile operators and welfare schemes store the Aadhaar number in their records to link back to one verified identity.
- The debate: linking many databases with one key makes services easier, but raises privacy and security concerns.
Nepal's National ID card follows a similar idea.
Aadhaar Enrolment Camp, Kerala (2013)
⚡ Key Identifier Quiz: PK, FK, or Regular?
Table: Hotel_Booking — Thamel Hotel, Kathmandu. Click PK, FK, or Regular for each column.
Designing a Database: The Blueprint 🔍
Before building, you must design. This is done with a data model.
Data Model: An abstract diagram that illustrates the data structure and relationships between different pieces of data.
A common tool for this is the Entity-Relationship Diagram (ERD).
Core Components of an ERD
ERDs visually represent the database structure using three main components:
1. Entities
The "things" we store data about.
e.g., Customer, Product, Order
2. Attributes
The properties or characteristics of an entity.
e.g., CustomerName, ProductPrice
3. Relationships
How entities are connected to each other.
e.g., A Customer `places` an Order.
Practical Application: Databases in Nepal
The database approach is the backbone of modern digital services.
🛒 E-commerce: Daraz
A central database stores `Products`, `Customers`, and `Orders`. This single source of truth ensures your cart is accurate and sellers know what to ship.
💳 Digital Wallets: eSewa / Khalti
Your `UserID` (Primary Key) links your account to all your `Transactions`. The DBMS ensures security and data integrity for every payment.
🚗 Government: DOTM Vehicle Registration
A national database links `Vehicle` information to `Owner` information using keys, helping manage taxes ("blue book") and ownership records efficiently.
⚡ ERD Sorter: Entities, Attributes, Relationships
Context: Khalti digital wallet. Click a term to select it, then click the correct bucket.
Entity
(a thing we store data about)
Attribute
(a property of an entity)
Relationship
(how entities connect)
🔗 How Many? Relationship Cardinality
| Type | Meaning | Example |
|---|---|---|
| One-to-One (1:1) | Each A has exactly one B | A country has one capital city |
| One-to-Many (1:M) | One A has many Bs | One department employs many teachers |
| Many-to-Many (M:N) | Many As relate to many Bs | Students take many courses; each course has many students |
A 1:M link puts the FK on the "many" side. An M:N link needs a third, linking table.
🎬 Watch: Reading Crow's Foot Notation
📊 Quick Poll: 1:1, 1:M or M:N?
- Doctor and patient appointments in a clinic. → M:N: a doctor sees many patients; a patient may see many doctors
- A mother and the children she has given birth to. → 1:M
- A citizen and their passport (one valid passport per country at a time). → 1:1
- Authors and books. → M:N: many books have co-authors
🏫 Worked Example: Design Tables for a College
Exam Long Q2, step by step. Illustrative data.
- Entities: Student, Course
- Relationship: a student enrolls in many courses; a course has many students → M:N, so add Enrollment.
- Tables and keys:
Student(StudentID PK, Name, Program)Course(CourseCode PK, Title, Credits)Enrollment(EnrollID PK, StudentID FK, CourseCode FK, Semester, Grade) - Sample row: Enrollment (E501, S1023, IT233, 3rd, A)
Exam tip: name the entities, state the relationship, list each table with PK and FK marked, then give one sample row.
Summary & Key Takeaways
- The database approach uses a central database and a DBMS to solve the problems of old file-based systems.
- It creates a "single source of truth", minimizing redundancy and improving data sharing, security, and integrity.
- The relational model is the most common, organizing data into tables linked by primary and foreign keys.
- Entity-Relationship Diagrams (ERDs) are used as blueprints to design the structure of a database before it is built.
Exam Practice 📝
Short answer
- What is a database management system (DBMS)?
- Distinguish between a primary key and a foreign key.
- What is an entity-relationship diagram (ERD)?
Exam Practice 📝 (continued)
Long answer
- Explain the advantages of the database approach over the traditional file-based approach.
- Describe the relational database model, and design a simple set of tables with keys for a college or a shop.
Revise the answers in the full note.
Image & Video Credits
Images
- Crow's foot cardinality diagram: made for this course
- Aadhaar Enrolment Camp, Moonniyur, Malappuram (2013): Press Information Bureau, Government of India, GODL-India, via Wikimedia Commons
Videos (YouTube)
- Neso Academy, "Introduction to Database Management Systems" (165–241 s)
- Develope with SJ, "Difference between Primary and Foreign Key in DBMS" (28–87 s)
- Lucid Software, "Crow's foot notation tutorial" (9–73 s)
Thank You
Any questions?
Next Up: Unit 3.4 - Understanding Big Data and the Five V's