Collge_task/database-normalization.sql at main · jhapendra-kandel/Collge_task · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
-- =========================
-- NON NORMALIZED (NNF)
-- =========================
CREATE TABLE student_all (
    student_id INT,
    student_name VARCHAR(50),
    course1 VARCHAR(50),
    course2 VARCHAR(50),
    teacher VARCHAR(50),
    dept VARCHAR(50)
);

INSERT INTO student_all VALUES
(1, 'Ram', 'Math', 'Science', 'Mr.A', 'Science'),
(2, 'Sita', 'Math', 'English', 'Ms.B', 'Arts');

SELECT * FROM student_all;

-- =========================
-- 1NF_P1
-- =========================
CREATE TABLE student_course_1nf (
    student_id INT,
    student_name VARCHAR(50),
    course VARCHAR(50),
    teacher VARCHAR(50),
    dept VARCHAR(50)
);

INSERT INTO student_course_1nf VALUES
(1, 'Ram', 'Math', 'Mr.A', 'Science'),
(1, 'Ram', 'Science', 'Mr.A', 'Science'),
(2, 'Sita', 'Math', 'Ms.B', 'Arts'),
(2, 'Sita', 'English', 'Ms.B', 'Arts');

SELECT * FROM student_course_1nf;

-- =========================
-- 2NF
-- =========================

-- 2NF_P1
CREATE TABLE student_2nf (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50)
);

-- 2NF_P2
CREATE TABLE course_2nf (
    course_name VARCHAR(50) PRIMARY KEY,
    teacher VARCHAR(50),
    dept VARCHAR(50)
);

-- 2NF_P3
CREATE TABLE enrollment_2nf (
    student_id INT,
    course_name VARCHAR(50),
    PRIMARY KEY (student_id, course_name)
);

INSERT INTO student_2nf VALUES
(1, 'Ram'),
(2, 'Sita');

INSERT INTO course_2nf VALUES
('Math', 'Mr.A', 'Science'),
('Science', 'Mr.A', 'Science'),
('English', 'Ms.B', 'Arts');

INSERT INTO enrollment_2nf VALUES
(1, 'Math'),
(1, 'Science'),
(2, 'Math'),
(2, 'English');

SELECT * FROM student_2nf;
SELECT * FROM course_2nf;
SELECT * FROM enrollment_2nf;

-- =========================
-- 3NF
-- =========================

-- 3NF_P1
CREATE TABLE student_3nf (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50)
);

-- 3NF_P2
CREATE TABLE department_3nf (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

-- 3NF_P3
CREATE TABLE course_3nf (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50),
    dept_id INT
);

-- 3NF_P4
CREATE TABLE enrollment_3nf (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

INSERT INTO student_3nf VALUES
(1, 'Ram'),
(2, 'Sita');

INSERT INTO department_3nf VALUES
(10, 'Science'),
(20, 'Arts');

INSERT INTO course_3nf VALUES
(101, 'Math', 10),
(102, 'Science', 10),
(103, 'English', 20);

INSERT INTO enrollment_3nf VALUES
(1, 101),
(1, 102),
(2, 101),
(2, 103);

SELECT * FROM student_3nf;
SELECT * FROM department_3nf;
SELECT * FROM course_3nf;
SELECT * FROM enrollment_3nf;