-
Notifications
You must be signed in to change notification settings - Fork 5
Normalization & Finalization
The Student Management System (SMS) is a comprehensive solution designed to streamline the management of student-related information, academic courses, faculty records, and administrative processes within educational institutions. This document presents a detailed Entity-Relationship (ER) diagram and a meticulously crafted database design that encapsulates the SMS's data architecture. By depicting the relationships between entities and attributes, this design ensures robustness, data integrity, and optimized query performance.
Figure 1: ER Diagram illustrating the Student Management System's data model.
Figure 2: Unnormalized and Normalized Forms of the "Assigns" Table.
- Unnormalized Form: P_ID(PK), P_name, P_duration, P_type, D_ID(PK), D_name, D_head
- 1NF: P_ID(PK), P_name, P_duration, P_type, D_ID(PK), D_name, D_head
-
2NF:
- P_ID(PK), P_name, P_duration, P_type
- D_ID(PK), D_name, D_head
- P_ID(PK), D_ID(FK)
- 3NF: Same as 2NF
- Tables:
- P_ID(PK), P_name, P_duration, P_type
- D_ID(PK), D_name, D_head
- P_ID(PK), D_ID(FK)
Figure 3: Unnormalized and Normalized Forms of the "Conducts" Table.
- Unnormalized Form: D_ID(PK), D_name, D_head, E_ID(PK), E_Date, E_type, E_roomno, Mark
- 1NF: D_ID(PK), D_name, D_head, E_ID(PK), E_Date, E_type, E_roomno, Mark
-
2NF:
- D_ID(PK), D_name, D_head
- E_ID(PK), E_Date, E_type, E_roomno, Mark, D_ID(FK)
- 3NF: Same as 2NF
- Tables:
- D_ID(PK), D_name, D_head
- E_ID(PK), E_Date, E_type, E_roomno, Mark, D_ID(FK)
Figure 4: Unnormalized and Normalized Forms of the "Sits" Table.
- Unnormalized Form: S_ID(PK), S_name, S_contact, S_mail, EnrollYear, E_ID(PK), E_Date, E_type, E_roomno, Mark
- 1NF: S_ID(PK), S_name, S_contact, S_mail, EnrollYear, E_ID(PK), E_Date, E_type, E_roomno, Mark
-
2NF:
- S_ID(PK), S_name, S_contact, S_mail, EnrollYear
- E_ID(PK), E_Date, E_type, E_roomno, Mark
- S_ID(PK), E_ID(FK)
- 3NF: Same as 2NF
- Tables:
- S_ID(PK), S_name, S_contact, S_mail, EnrollYear
- E_ID(PK), E_Date, E_type, E_roomno, Mark
- S_ID(PK), E_ID(FK)
Figure 5: Unnormalized and Normalized Forms of the "Teaches" Table.
- Unnormalized Form: F_ID(PK), F_name, F_contact, F_mail, S_ID(PK), S_name, S_contact, S_mail, EnrollYear
- 1NF: F_ID(PK), F_name, F_contact, F_mail, S_ID(PK), S_name, S_contact, S_mail, EnrollYear
-
2NF:
- S_ID(PK), S_name, S_contact, S_mail, EnrollYear, F_ID(FK)
- F_ID(PK), F_name, F_contact, F_mail
- 3NF: Same as 2NF
- Tables:
- S_ID(PK), S_name, S_contact, S_mail, EnrollYear, F_ID(FK)
- F_ID(PK), F_name, F_contact, F_mail
Figure 6: Unnormalized and Normalized Forms of the "Attends" Table.
- Unnormalized Form: S_ID(PK), S_name, S_contact, S_mail, EnrollYear, Cl_roomno(PK), Cl_time, Cl_name
- 1NF: S_ID(PK), S_name, S_contact, S_mail, EnrollYear, Cl_roomno(PK), Cl_time, Cl_name
-
2NF:
- S_ID(PK), S_name, S_contact, S_mail, EnrollYear
- Cl_roomno(PK), Cl_time, Cl_name
- S_ID(PK), Cl_roomno(FK)
- 3NF: Same as 2NF
- Tables:
- S_ID(PK), S_name, S_contact, S_mail, EnrollYear
- Cl_roomno(PK), Cl_time, Cl_name
- S_ID(PK), Cl_roomno(FK)
Figure 7: Unnormalized and Normalized Forms of the "Enrolls" Table.
- Unnormalized Form: S_ID(PK), S_name, S_contact, S_mail, EnrollYear, P_ID(PK), P_name, P_duration, P_type
- 1NF: S_ID(PK), S_name, S_contact, S_mail, EnrollYear, P_ID(PK), P_name, P_duration, P_type
-
2NF:
- S_ID(PK), S_name, S_contact, S_mail, EnrollYear
- P_ID(PK), P_name, P_duration, P_type
- S_ID(PK), P_ID(FK)
- Tables:
- S_ID(PK), S_name, S_contact, S_mail, EnrollYear
- P_ID(PK), P_name, P_duration, P_type
- S_ID(PK), P_ID(FK)
Figure 8: Unnormalized and Normalized Forms of the "Takes" Table.
- Unnormalized Form: S_ID(PK), S_name, S_contact, S_mail, EnrollYear, C_ID(PK), C_name, C_duration, C_credit
- 1NF: S_ID(PK), S_name, S_contact, S_mail, EnrollYear, C_ID(PK), C_name, C_duration, C_credit
-
2NF:
- S_ID(PK), S_name, S_contact, S_mail, EnrollYear
- C_ID(PK), C_name, C_duration, C_credit
- S_ID(PK), C_ID(FK)
- 3NF: Same as 2NF
- Tables:
- S_ID(PK), S_name, S_contact, S_mail, EnrollYear
- C_ID(PK), C_name, C_duration, C_credit
- S_ID(PK), C_ID(FK)
Figure 9: Unnormalized and Normalized Forms of the "Chooses" Table.
- Unnormalized Form: F_ID(PK), F_name, F_contact, F_mail, C_ID(PK), C_name, C_duration, C_credit
- 1NF: F_ID(PK), F_name, F_contact, F_mail, C_ID(PK), C_name, C_duration, C_credit
-
2NF:
- F_ID(PK), F_name, F_contact, F_mail
- C_ID(PK), C_name, C_duration, C_credit
- F_ID(PK), C_ID(FK)
- 3NF: Same as 2NF
- Tables:
- F_ID(PK), F_name, F_contact, F_mail
- C_ID(PK), C_name, C_duration, C_credit
- F_ID(PK), C_ID(FK)
Figure 10: Unnormalized and Normalized Forms of the "Has" Table.
- Unnormalized Form: C_ID(PK), C_name, C_duration, C_credit, E_ID(PK), E_date, E_type, E_roomno, Mark
- 1NF: C_ID(PK), C_name, C_duration, C_credit, E_ID(PK), E_date, E_type, E_roomno, Mark
-
2NF:
- C_ID(PK), C_name, C_duration, C_credit
- E_ID(PK), E_date, E_type, E_roomno, Mark, C_ID(FK)
- 3NF: Same as 2NF
- Tables:
- C_ID(PK), C_name, C_duration, C_credit
- E_ID(PK), E_date, E_type, E_roomno, Mark, C_ID(FK)
Figure 11: Unnormalized and Normalized Forms of the "Offers" Table.
- Unnormalized Form: D_ID(PK), D_name, D_head, F_ID(PK), F_name, F_contact, F_mail, C_ID(FK), C_name, C_duration, C_credit
- 1NF: D_ID(PK), D_name, D_head, F_ID(PK), F_name, F_contact, F_mail, C_ID(FK), C_name, C_duration, C_credit
-
2NF:
- D_ID(PK), D_name, D_head
- F_ID(PK), F_name, F_contact, F_mail, C_ID(FK), C_name, C_duration, C_credit, D_ID(FK)
- 3NF: Same as 2NF
- Tables:
- D_ID(PK), D_name, D_head
- F_ID(PK), F_name, F_contact, F_mail, C_ID(FK), C_name, C_duration, C_credit, D_ID(FK)
Figure 12: Unnormalized and Normalized Forms of the "Observes" Table.
- Unnormalized Form: D_ID(PK), D_name, D_head, A_mail(PK), A_name, A_role, A_contact
- 1NF: D_ID(PK), D_name, D_head, A_mail(PK), A_name, A_role, A_contact
-
2NF:
- D_ID(PK), D_name, D_head, A_mail(FK)
- A_mail(PK), A_name, A_role, A_contact
- 3NF: Same as 2NF
- Tables:
- D_ID(PK), D_name, D_head, A_mail(FK)
- A_mail(PK), A_name, A_role, A_contact
Figure 13: Unnormalized and Normalized Forms of the "Gets" Table.
- Unnormalized Form: S_ID(PK), S_name, S_contact, S_mail, EnrollYear, G_ID(PK), CGPA, Semester, Sem_year
- 1NF: S_ID(PK), S_name, S_contact, S_mail, EnrollYear, G_ID(PK), CGPA, Semester, Sem_year
-
2NF:
- S_ID(PK), S_name, S_contact, S_mail, EnrollYear, G_ID(FK)
- G_ID(PK), CGPA, Semester, Sem_year
- 3NF: Same as 2NF
- Tables:
- S_ID(PK), S_name, S_contact, S_mail, EnrollYear, G_ID(FK)
- G_ID(PK), CGPA, Semester, Sem_year
Figure 13: Unnormalized and Normalized Forms of the "Submits" Table.
- Unnormalized Form: F_ID(PK), F_name, F_contact, F_mail, G_ID(PK), CGPA, Semester, Sem_year
- 1NF: F_ID(PK), F_name, F_contact, F_mail, G_ID(PK), CGPA, Semester, Sem_year
-
2NF:
- F_ID(PK), F_name, F_contact, F_mail
- G_ID(PK), CGPA, Semester, Sem_year, F_ID(FK)
- 3NF: Same as 2NF
- Tables:
- F_ID(PK), F_name, F_contact, F_mail
- G_ID(PK), CGPA, Semester, Sem_year, F_ID(FK)
- P_ID(PK), P_name, P_duration, P_type
- D_ID(PK), D_name, D_head
- P_ID(PK), D_ID(FK)
D_ID(PK), D_name, D_head
- E_ID(PK), E_Date, E_type, E_roomno, Mark, D_ID(FK)
- S_ID(PK), S_name, S_contact, S_mail, EnrollYear
- E_ID(PK), E_Date, E_type, E_roomno, Mark
- S_ID(PK), E_ID(FK)
- S_ID(PK), S_name, S_contact, S_mail, EnrollYear, F_ID(FK)
- F_ID(PK), F_name, F_contact, F_mail
S_ID(PK), S_name, S_contact, S_mail, EnrollYear
- Cl_roomno(PK), Cl_time, Cl_name
- S_ID(PK), Cl_roomno(FK)
S_ID(PK), S_name, S_contact, S_mail, EnrollYearP_ID(PK), P_name, P_duration, P_type
- S_ID(PK), P_ID(FK)
S_ID(PK), S_name, S_contact, S_mail, EnrollYear
- C_ID(PK), C_name, C_duration, C_credit
- S_ID(PK), C_ID(FK)
F_ID(PK), F_name, F_contact, F_mailC_ID(PK), C_name, C_duration, C_credit
- F_ID(PK), C_ID(FK)
C_ID(PK), C_name, C_duration, C_credit
- E_ID(PK), E_date, E_type, E_roomno, Mark, C_ID(FK)
D_ID(PK), D_name, D_head
- F_ID(PK), F_name, F_contact, F_mail, C_ID(FK), C_name, C_duration, C_credit, D_ID(FK)
- D_ID(PK), D_name, D_head, A_mail(FK)
- A_mail(PK), A_name, A_role, A_contact
- S_ID(PK), S_name, S_contact, S_mail, EnrollYear, G_ID(FK)
- G_ID(PK), CGPA, Semester, Sem_year
F_ID(PK), F_name, F_contact, F_mail
- G_ID(PK), CGPA, Semester, Sem_year, F_ID(FK)
The culmination of the normalization process yields a set of refined and structured tables:
Table No | Table Name | Table Columns | Primary Key | Foreign Key |
---|---|---|---|---|
1 | program | P_ID, P_name, P_duration, P_type | P_ID | |
2 | dept | D_ID, D_name, D_head | D_ID | |
3 | prodept | P_ID, D_ID | P_ID | D_ID |
4 | deptconductexam | E_ID, E_Date, E_type, E_roomno, Mark, D_ID | E_ID | D_ID |
5 | student | S_ID, S_name, S_mail, S_contact, EnrollYear | S_ID | |
6 | exam | E_ID, E_Date, E_type, E_roomno, Mark | E_ID | |
7 | stdexam | S_ID, E_ID | S_ID | E_ID |
8 | stdfac | S_ID, S_name, S_contact, S_mail, EnrollYear, F_ID | S_ID | F_ID |
9 | faculty | F_ID, F_name, F_contact, F_mail | F_ID | |
10 | classroom | Cl_roomno, Cl_time, Cl_name | Cl_roomno | |
11 | stdcls | S_ID, Cl_roomno | S_ID | Cl_roomno |
12 | stdpro | S_ID, P_ID | S_ID | P_ID |
13 | course | C_ID, C_name, C_duration, C_credit | C_ID | |
14 | stdcrs | S_ID, C_ID | S_ID | C_ID |
15 | faccrs | F_ID, C_ID | F_ID | C_ID |
16 | crsexam | E_ID, E_date, E_type, E_roomno, Mark, C_ID | E_ID | C_ID |
17 | deptofferfaccrs | F_ID, F_name, F_contact, F_mail, C_ID, Cname, C_duration, C_credit, D_ID | F_ID | C_ID, D_ID |
18 | deptauth | D_ID, D_name, D_head, A_mail | D_ID | A_mail |
19 | authority | A_mail, A_name, A_role, A_contact | A_mail | |
20 | stdgrade | S_ID, S_name, S_contact, S_mail, EnrollYear, G_ID | S_ID | G_ID |
21 | grade | G_ID, CGPA, Semester, Sem_year | G_ID | |
22 | facgrade | G_ID, CGPA, Semester, Sem_year, F_ID | G_ID | F_ID |
The Student Management System's database design, meticulously captured through an Entity-Relationship diagram and its subsequent normalization into well-structured tables, ensures a robust foundation for efficiently managing student information, academic programs, faculty records, and administrative processes within educational institutions. The normalized tables exhibit optimal data organization, minimizing redundancy and ensuring accurate and reliable information retrieval for enhanced educational administration and decision-making.