Database Design & Implementation: ER-to-SQL, Normalization, and Real-World DBMS

TAKEAWAYS:

• Learn to convert ER diagrams into SQL schemas with primary/foreign keys, constraints, and relationships (1:1, 1:N, M:N).
• Apply normalization (1NF–BCNF) to eliminate redundancy and anomalies in real-world datasets (e.g., student-course enrollment).
• Design practical databases for scenarios like Daraz’s inventory or Pathao’s driver-ride matching using ER diagrams and SQL scripts.
• Understand database implementation tools (MySQL Workbench, pgAdmin) and how to test queries with sample data.
• Compare relational vs. NoSQL approaches for different use cases (e.g., Ncell’s user profiles vs. NEPSE’s stock trades).
• Write complete SQL scripts (DDL/DML) for a given scenario, including indexes, triggers, and views.

1. From ER Diagrams to SQL: Step-by-Step Translation

An ER diagram visually models entities, attributes, and relationships. To implement it in a DBMS, we translate each component into SQL tables with constraints.

Key Translation Rules

Worked Example: School Database

Scenario: A school has Students, Teachers, and Courses. Each Student can enroll in multiple Courses, and each Course has one Teacher. ER Diagram:

erDiagram
    Student ||--o{ Enrollment : takes
    Course ||--o{ Enrollment : offers
    Teacher ||--o{ Course : teaches
    Enrollment {
        int SID PK
        int CID PK
        char Grade
    }

SQL Implementation:

-- Step 1: Create tables for entities
CREATE TABLE Student (
    SID INT PRIMARY KEY,
    SName VARCHAR(50) NOT NULL,
    City VARCHAR(30),
    Faculty VARCHAR(20)
);

CREATE TABLE Teacher (
    TeacherID INT PRIMARY KEY,
    TName VARCHAR(50) NOT NULL,
    Subject VARCHAR(50)
);

CREATE TABLE Course (
    CID INT PRIMARY KEY,
    CName VARCHAR(100) NOT NULL,
    Credits INT,
    TeacherID INT,
    FOREIGN KEY (TeacherID) REFERENCES Teacher(TeacherID)
);

-- Step 2: Create junction table for M:N (Student-Course)
CREATE TABLE Enrollment (
    SID INT,
    CID INT,
    Grade CHAR(2),
    PRIMARY KEY (SID, CID),
    FOREIGN KEY (SID) REFERENCES Student(SID),
    FOREIGN KEY (CID) REFERENCES Course(CID)
);

2. Normalization: Eliminating Redundancy

Normalization ensures data integrity by organizing tables to minimize redundancy. We apply functional dependencies to decompose tables into normal forms (1NF–BCNF).

Normal Forms Summary

Worked Example: Normalizing a Student-Course Database

Unnormalized Table (Violates 1NF, 2NF, 3NF):

StudentCourse (
    SID INT,
    SName VARCHAR(50),
    City VARCHAR(30),
    Faculty VARCHAR(20),
    CID INT,
    CName VARCHAR(100),
    Credits INT,
    Teacher VARCHAR(50),
    Grade CHAR(2)
)

Problems:

• Repeating groups (e.g., multiple CID, CName for one student) → 1NF violation.
• Teacher depends only on CID (partial dependency) → 2NF violation.
• City depends on Faculty (transitive dependency) → 3NF violation.

Normalized Tables (3NF):

-- Student table (1NF, 2NF, 3NF)
CREATE TABLE Student (
    SID INT PRIMARY KEY,
    SName VARCHAR(50) NOT NULL,
    City VARCHAR(30),
    Faculty VARCHAR(20)
);

-- Course table (1NF, 2NF, 3NF)
CREATE TABLE Course (
    CID INT PRIMARY KEY,
    CName VARCHAR(100) NOT NULL,
    Credits INT,
    Teacher VARCHAR(50) NOT NULL
);

-- Enrollment table (1NF, 2NF)
CREATE TABLE Enrollment (
    SID INT,
    CID INT,
    Grade CHAR(2),
    PRIMARY KEY (SID, CID),
    FOREIGN KEY (SID) REFERENCES Student(SID),
    FOREIGN KEY (CID) REFERENCES Course(CID)
);

-- Teacher table (added for 2NF compliance)
CREATE TABLE Teacher (
    TeacherID INT PRIMARY KEY,
    TName VARCHAR(50) NOT NULL
);

3. Database Implementation Tools

To build and test databases, we use DBMS tools like MySQL Workbench, pgAdmin, or SQLite.

Key Tools and Features

Worked Example: Creating a Daraz Inventory Database

Scenario: Daraz needs to track Products, Suppliers, and Orders. ER Diagram:

erDiagram
    Product ||--o{ OrderItem : "contains"
    Supplier ||--o{ Product : "supplies"
    Order ||--o{ OrderItem : "has"
    OrderItem {
        int OrderID PK
        int ProductID PK
        int Quantity
        decimal UnitPrice
    }
    Product {
        int ProductID PK
        varchar ProductName
        varchar Description
    }
    Supplier {
        int SupplierID PK
        varchar SupplierName
        varchar Contact
    }
    Order {
        int OrderID PK
        datetime OrderDate
        int CustomerID
    }

SQL Implementation (MySQL):

-- Step 1: Create tables
CREATE TABLE Supplier (
    SupplierID INT PRIMARY KEY,
    SName VARCHAR(100) NOT NULL,
    Contact VARCHAR(20)
);

CREATE TABLE Product (
    ProductID INT PRIMARY KEY,
    PName VARCHAR(100) NOT NULL,
    Price DECIMAL(10, 2),
    SupplierID INT,
    FOREIGN KEY (SupplierID) REFERENCES Supplier(SupplierID)
);

CREATE TABLE Order (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL,
    CustomerName VARCHAR(100)
);

CREATE TABLE OrderItem (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID),
    FOREIGN KEY (OrderID) REFERENCES Order(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);

-- Step 2: Insert sample data
INSERT INTO Supplier VALUES (1, 'ABC Electronics', 'contact@abc.com');
INSERT INTO Product VALUES (101, 'Smartphone', 15000.00, 1);
INSERT INTO Order VALUES (1001, '2023-10-01', 'Ramesh');
INSERT INTO OrderItem VALUES (1001, 101, 2);

4. Real-World Applications of Database Design

## In the real world

1. Daraz’s Order Queue (M:N Relationships)

   • Idea: Daraz uses a junction table (OrderItem) to track which products are in which orders (M:N relationship between Order and Product).
   • How it works: When a customer buys 2 smartphones, Daraz inserts two rows in OrderItem with OrderID=1001 and ProductID=101, Quantity=2.
   • SQL Query to Find Total Orders for a Product:

     SELECT Product.PName, SUM(OrderItem.Quantity) AS TotalQuantity
     FROM Product
     JOIN OrderItem ON Product.ProductID = OrderItem.ProductID
     GROUP BY Product.PName;
     

2. Ncell’s User Profiles (Normalization)

   • Idea: Ncell stores user data (name, phone, plan) in separate tables to avoid redundancy.
   • How it works: The User table has PhoneNumber as PK, while UserPlan (a junction table) links users to their subscription plans (1:N relationship).
   • SQL Query to Find Users on a Plan:

     SELECT User.PhoneNumber, Plan.PlanName
     FROM User
     JOIN UserPlan ON User.UserID = UserPlan.UserID
     WHERE Plan.PlanName = 'Premium';
     

3. Pathao’s Driver-Ride Matching (Indexing for Performance)

   • Idea: Pathao uses indexes on DriverLocation and RideRequestTime to quickly match drivers to nearby riders.
   • How it works: The Driver table has an index on CurrentLocation (latitude/longitude) to speed up queries like:

     SELECT DriverID FROM Driver
     WHERE CurrentLocation WITHIN (ST_Point(85.315, 27.717), 1) -- Kathmandu area
     ORDER BY Distance FROM ST_Point(85.315, 27.717);
     

5. Database Constraints and Triggers

Constraints ensure data integrity, while triggers automate actions.

Common Constraints

Worked Example: Trigger for Auto-Grading

Scenario: In the Enrollment table, if a student’s grade is updated to 'A', send a notification (simulated here with a LOG table).

-- Step 1: Create LOG table
CREATE TABLE GradeLog (
    LogID INT AUTO_INCREMENT PRIMARY KEY,
    SID INT,
    OldGrade CHAR(2),
    NewGrade CHAR(2),
    ChangeDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Step 2: Create TRIGGER
DELIMITER //
CREATE TRIGGER after_grade_update
AFTER UPDATE OF Grade ON Enrollment
FOR EACH ROW
BEGIN
    IF NEW.Grade = 'A' THEN
        INSERT INTO GradeLog (SID, OldGrade, NewGrade)
        VALUES (NEW.SID, OLD.Grade, NEW.Grade);
    END IF;
END//
DELIMITER ;

-- Step 3: Test the trigger
UPDATE Enrollment SET Grade = 'A' WHERE SID = 101;
-- Check LOG table
SELECT * FROM GradeLog;

6. Database Views and Performance Optimization

Views: Virtual Tables

Views are predefined SQL queries that simplify complex queries. Example: A view for "Students in Computer Science Faculty":

CREATE VIEW CS_Students AS
SELECT SID, SName, City
FROM Student
WHERE Faculty = 'Computer Science';

-- Query the view
SELECT * FROM CS_Students;

Indexing for Speed

Indexes speed up searches but slow down INSERT/UPDATE. Example: Add an index to Student.City:

CREATE INDEX idx_student_city ON Student(City);
-- Now queries like `WHERE City = 'Kathmandu'` are faster.

7. Exam Tip: How to Score Full Marks

1. For ER-to-SQL Questions:

   • Always draw the ER diagram first (even if not asked).
   • Label primary keys (PK), foreign keys (FK), and relationships (1:1, 1:N, M:N) clearly.
   • Write complete CREATE TABLE statements with all constraints (NOT NULL, UNIQUE, etc.).
   • Example:

     -- Correct (full marks)
     CREATE TABLE Teacher (
         TeacherID INT PRIMARY KEY,
         TName VARCHAR(50) NOT NULL,
         Subject VARCHAR(50)
     );
     -- Incorrect (missing NOT NULL)
     CREATE TABLE Teacher (
         TeacherID INT PRIMARY KEY,
         TName VARCHAR(50),
         Subject VARCHAR(50)
     );
     

2. For Normalization Questions:

   • Identify violations (repeating groups, partial/transitive dependencies).
   • Decompose tables step-by-step into 1NF → 2NF → 3NF.
   • Justify each step (e.g., "Moved Teacher to a separate table to remove partial dependency").
   • Example Answer Structure:

     1. Original table violates 1NF due to repeating groups (multiple courses per student).
     2. Split into Student, Course, and Enrollment tables (1NF).
     3. Enrollment table violates 2NF because Grade depends only on (SID, CID), not on SID alone.
     4. Final tables are in 3NF with no transitive dependencies.
     

3. For Practical Implementation Questions:

   • Use real-world examples (e.g., "Design a database for Ncell’s user plans").
   • Include sample data (INSERT statements) to show the database is functional.
   • Test queries (e.g., "Find all students enrolled in a course taught by TeacherID=5").
   • Example Query:

     -- Find courses taught by TeacherID=5
     SELECT C.CName, C.Credits
     FROM Course C
     JOIN Teacher T ON C.TeacherID = T.TeacherID
     WHERE T.TeacherID = 5;
     

4. For NoSQL vs. Relational Questions:

   • Compare scalability, flexibility, and use cases:

     Feature	Relational (SQL)	NoSQL
     Schema	Fixed (tables, columns)	Flexible (documents, key-value)
     Scalability	Vertical (add more servers)	Horizontal (sharding)
     Use Case	Structured data (banks, schools)	Unstructured (social media, IoT)

   • Example:
     • NEPSE (Relational): Uses SQL for stock trades (structured data).
     • WhatsApp (NoSQL): Uses MongoDB for user chats (unstructured data).

8. Common Mistakes to Avoid

• Forgetting composite keys in M:N relationships (e.g., missing (SID, CID) in Enrollment).
• Incorrect foreign key references (e.g., FOREIGN KEY (CID) REFERENCES Course(CourseID) when PK is CID).
• Over-normalizing (e.g., splitting Student into Student_Personal and Student_Academic when not needed).
• Not testing queries (e.g., writing a query but not running it with sample data).
• Ignoring constraints (e.g., omitting NOT NULL for required fields like SName).

9. Sample Exam Questions and Answers

Question 1: Design an ER diagram and SQL tables for a hospital database with Patients, Doctors, and Appointments. Include constraints.

Answer: ER Diagram:

erDiagram
    Patient ||--o{ Appointment : "has"
    Doctor ||--o{ Appointment : "attends"
    Appointment {
        int AppointmentID PK
        int PatientID PK
        int DoctorID PK
        datetime AppointmentTime
        varchar Status
    }
    Patient {
        int PatientID PK
        varchar Name
        varchar Contact
        varchar Address
    }
    Doctor {
        int DoctorID PK
        varchar Name
        varchar Specialization
    }

SQL Tables:

CREATE TABLE Patient (
    PatientID INT PRIMARY KEY,
    PName VARCHAR(100) NOT NULL,
    Contact VARCHAR(20) UNIQUE,
    Disease VARCHAR(100)
);

CREATE TABLE Doctor (
    DoctorID INT PRIMARY KEY,
    DName VARCHAR(100) NOT NULL,
    Specialty VARCHAR(50)
);

CREATE TABLE Appointment (
    AppointmentID INT PRIMARY KEY,
    PatientID INT,
    DoctorID INT,
    AppointmentTime DATETIME NOT NULL,
    FOREIGN KEY (PatientID) REFERENCES Patient(PatientID),
    FOREIGN KEY (DoctorID) REFERENCES Doctor(DoctorID),
    CHECK (AppointmentTime > CURRENT_TIMESTAMP)
);

Question 2: Normalize the following table to 3NF:

UnnormalizedTable (
    OrderID INT,
    CustomerName VARCHAR(100),
    ProductName VARCHAR(100),
    Quantity INT,
    Price DECIMAL(10, 2),
    CustomerAddress VARCHAR(200),
    ProductSupplier VARCHAR(100)
)

Answer:

1. 1NF Violation: Repeating groups (multiple products per order).
   • Split into Order, OrderItem, Product, Customer, Supplier.
2. 2NF Violation: CustomerAddress depends only on CustomerName (partial dependency).
   • Move CustomerAddress to a Customer table.
3. 3NF Violation: ProductSupplier depends on ProductName (transitive dependency).
   • Move Supplier to a separate table.

Normalized Tables:

CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL,
    CustomerAddress VARCHAR(200)
);

CREATE TABLE Supplier (
    SupplierID INT PRIMARY KEY,
    SupplierName VARCHAR(100) NOT NULL
);

CREATE TABLE Product (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    SupplierID INT,
    FOREIGN KEY (SupplierID) REFERENCES Supplier(SupplierID)
);

CREATE TABLE Order (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE NOT NULL,
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

CREATE TABLE OrderItem (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    Price DECIMAL(10, 2),
    PRIMARY KEY (OrderID, ProductID),
    FOREIGN KEY (OrderID) REFERENCES Order(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);

10. Final Checklist for Implementation

Before submitting your database design:

1. ER diagram is clear with all entities, attributes, and relationships.
2. SQL tables have correct primary/foreign keys.
3. Constraints (NOT NULL, UNIQUE, CHECK) are applied where needed.
4. Sample data is inserted to test queries.
5. Queries are written and tested (e.g., SELECT, INSERT, UPDATE).
6. Normalization is applied (1NF–3NF) with justification.
7. NoSQL vs. Relational comparison is included if asked.