LLO 2.1 Create Database Schema for Given Application

🧪 Journal / Manual Entry

Subject: Database Management Systems (DBMS)
LLO 2.1: Create Database Schema for Given Application

---

🎯 Objective

To create a database schema for a Student Management System that is normalized up to Third Normal Form (3NF) for reducing redundancy and ensuring data integrity.

---

📘 Application: Student Management System

---

🔧 1. Create Database

CREATE DATABASE StudentDB;
USE StudentDB;

---

🗃️ 2. Create Tables (Normalized to 3NF)

A. Courses Table

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(50) UNIQUE
);

B. Students Table

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(50),
    Age SMALLINT,
    Email VARCHAR(50),
    CourseID INT,
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

---

🔑 3. Insert Data

A. Insert Data into Courses Table

INSERT INTO Courses (CourseID, CourseName) VALUES
(1, 'Computer Science'),
(2, 'Mechanical Engineering'),
(3, 'Electrical Engineering');

B. Insert Data into Students Table

INSERT INTO Students (StudentID, StudentName, Age, Email, CourseID) VALUES
(101, 'Rahul Sharma', 20, 'rahul.sharma@example.com', 1),
(102, 'Anjali Verma', 21, 'anjali.verma@example.com', 2),
(103, 'Vikram Singh', 19, 'vikram.singh@example.com', 3);

---

🧾 4. Final Table Structure

✅ Students Table (Normalized)

StudentID	StudentName	Age	Email	CourseID
101	Rahul Sharma	20	rahul.sharma@example.com	1
102	Anjali Verma	21	anjali.verma@example.com	2
103	Vikram Singh	19	vikram.singh@example.com	3

✅ Courses Table

CourseID	CourseName
1	Computer Science
2	Mechanical Engineering
3	Electrical Engineering

---

📝 Normalization Justification (3NF)

• 1NF: Atomic values, no repeating groups.

• 2NF: All non-key attributes fully dependent on the primary key.

• 3NF: No transitive dependencies; CourseName moved to separate Courses table.

---

📌 Conclusion

In this lab, we:

• Created a normalized relational schema for a Student Management System.

• Broke down a flat Students table into two related tables to ensure 3NF compliance.

• Eliminated data redundancy and maintained referential integrity using foreign keys.

This exercise demonstrated the importance of normalization in designing efficient and scalable databases.

---