Skip to content

Normalization & Finalization

Jump to bottom
Md Ehsanul Haque edited this page Aug 12, 2023 · 14 revisions

Student Management System Database Design

Introduction

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.

ER Diagram

Figure 1: ER Diagram illustrating the Student Management System's data model.

Normalization

Assigns

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:
    1. P_ID(PK), P_name, P_duration, P_type
    2. D_ID(PK), D_name, D_head
    3. P_ID(PK), D_ID(FK)
  • 3NF: Same as 2NF
  • Tables:
    1. P_ID(PK), P_name, P_duration, P_type
    2. D_ID(PK), D_name, D_head
    3. P_ID(PK), D_ID(FK)

Conducts

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:
    1. D_ID(PK), D_name, D_head
    2. E_ID(PK), E_Date, E_type, E_roomno, Mark, D_ID(FK)
  • 3NF: Same as 2NF
  • Tables:
    1. D_ID(PK), D_name, D_head
    2. E_ID(PK), E_Date, E_type, E_roomno, Mark, D_ID(FK)

Sits

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:
    1. S_ID(PK), S_name, S_contact, S_mail, EnrollYear
    2. E_ID(PK), E_Date, E_type, E_roomno, Mark
    3. S_ID(PK), E_ID(FK)
  • 3NF: Same as 2NF
  • Tables:
    1. S_ID(PK), S_name, S_contact, S_mail, EnrollYear
    2. E_ID(PK), E_Date, E_type, E_roomno, Mark
    3. S_ID(PK), E_ID(FK)

Teaches

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:
    1. S_ID(PK), S_name, S_contact, S_mail, EnrollYear, F_ID(FK)
    2. F_ID(PK), F_name, F_contact, F_mail
  • 3NF: Same as 2NF
  • Tables:
    1. S_ID(PK), S_name, S_contact, S_mail, EnrollYear, F_ID(FK)
    2. F_ID(PK), F_name, F_contact, F_mail

Attends

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:
    1. S_ID(PK), S_name, S_contact, S_mail, EnrollYear
    2. Cl_roomno(PK), Cl_time, Cl_name
    3. S_ID(PK), Cl_roomno(FK)
  • 3NF: Same as 2NF
  • Tables:
    1. S_ID(PK), S_name, S_contact, S_mail, EnrollYear
    2. Cl_roomno(PK), Cl_time, Cl_name
    3. S_ID(PK), Cl_roomno(FK)

Enrolls

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:
    1. S_ID(PK), S_name, S_contact, S_mail, EnrollYear
    2. P_ID(PK), P_name, P_duration, P_type
    3. S_ID(PK), P_ID(FK)
  • Tables:
    1. S_ID(PK), S_name, S_contact, S_mail, EnrollYear
    2. P_ID(PK), P_name, P_duration, P_type
    3. S_ID(PK), P_ID(FK)

Takes

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:
    1. S_ID(PK), S_name, S_contact, S_mail, EnrollYear
    2. C_ID(PK), C_name, C_duration, C_credit
    3. S_ID(PK), C_ID(FK)
  • 3NF: Same as 2NF
  • Tables:
    1. S_ID(PK), S_name, S_contact, S_mail, EnrollYear
    2. C_ID(PK), C_name, C_duration, C_credit
    3. S_ID(PK), C_ID(FK)

Chooses

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:
    1. F_ID(PK), F_name, F_contact, F_mail
    2. C_ID(PK), C_name, C_duration, C_credit
    3. F_ID(PK), C_ID(FK)
  • 3NF: Same as 2NF
  • Tables:
    1. F_ID(PK), F_name, F_contact, F_mail
    2. C_ID(PK), C_name, C_duration, C_credit
    3. F_ID(PK), C_ID(FK)

Has

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:
    1. C_ID(PK), C_name, C_duration, C_credit
    2. E_ID(PK), E_date, E_type, E_roomno, Mark, C_ID(FK)
  • 3NF: Same as 2NF
  • Tables:
    1. C_ID(PK), C_name, C_duration, C_credit
    2. E_ID(PK), E_date, E_type, E_roomno, Mark, C_ID(FK)

Offers

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:
    1. D_ID(PK), D_name, D_head
    2. 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:
    1. D_ID(PK), D_name, D_head
    2. F_ID(PK), F_name, F_contact, F_mail, C_ID(FK), C_name, C_duration, C_credit, D_ID(FK)

Observes

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:
    1. D_ID(PK), D_name, D_head, A_mail(FK)
    2. A_mail(PK), A_name, A_role, A_contact
  • 3NF: Same as 2NF
  • Tables:
    1. D_ID(PK), D_name, D_head, A_mail(FK)
    2. A_mail(PK), A_name, A_role, A_contact

Gets

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:
    1. S_ID(PK), S_name, S_contact, S_mail, EnrollYear, G_ID(FK)
    2. G_ID(PK), CGPA, Semester, Sem_year
  • 3NF: Same as 2NF
  • Tables:
    1. S_ID(PK), S_name, S_contact, S_mail, EnrollYear, G_ID(FK)
    2. G_ID(PK), CGPA, Semester, Sem_year

Submits

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:
    1. F_ID(PK), F_name, F_contact, F_mail
    2. G_ID(PK), CGPA, Semester, Sem_year, F_ID(FK)
  • 3NF: Same as 2NF
  • Tables:
    1. F_ID(PK), F_name, F_contact, F_mail
    2. G_ID(PK), CGPA, Semester, Sem_year, F_ID(FK)

Finalization

  1. P_ID(PK), P_name, P_duration, P_type
  2. D_ID(PK), D_name, D_head
  3. P_ID(PK), D_ID(FK)
  • D_ID(PK), D_name, D_head
  1. E_ID(PK), E_Date, E_type, E_roomno, Mark, D_ID(FK)
  2. S_ID(PK), S_name, S_contact, S_mail, EnrollYear
  3. E_ID(PK), E_Date, E_type, E_roomno, Mark
  4. S_ID(PK), E_ID(FK)
  5. S_ID(PK), S_name, S_contact, S_mail, EnrollYear, F_ID(FK)
  6. F_ID(PK), F_name, F_contact, F_mail
  • S_ID(PK), S_name, S_contact, S_mail, EnrollYear
  1. Cl_roomno(PK), Cl_time, Cl_name
  2. S_ID(PK), Cl_roomno(FK)
  • S_ID(PK), S_name, S_contact, S_mail, EnrollYear
  • P_ID(PK), P_name, P_duration, P_type
  1. S_ID(PK), P_ID(FK)
  • S_ID(PK), S_name, S_contact, S_mail, EnrollYear
  1. C_ID(PK), C_name, C_duration, C_credit
  2. S_ID(PK), C_ID(FK)
  • F_ID(PK), F_name, F_contact, F_mail
  • C_ID(PK), C_name, C_duration, C_credit
  1. F_ID(PK), C_ID(FK)
  • C_ID(PK), C_name, C_duration, C_credit
  1. E_ID(PK), E_date, E_type, E_roomno, Mark, C_ID(FK)
  • D_ID(PK), D_name, D_head
  1. F_ID(PK), F_name, F_contact, F_mail, C_ID(FK), C_name, C_duration, C_credit, D_ID(FK)
  2. D_ID(PK), D_name, D_head, A_mail(FK)
  3. A_mail(PK), A_name, A_role, A_contact
  4. S_ID(PK), S_name, S_contact, S_mail, EnrollYear, G_ID(FK)
  5. G_ID(PK), CGPA, Semester, Sem_year
  • F_ID(PK), F_name, F_contact, F_mail
  1. G_ID(PK), CGPA, Semester, Sem_year, F_ID(FK)

Final Tables

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 deptexam 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

Conclusion

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.

Clone this wiki locally