Skip to content

Normalization & Finalization

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

Normalization

Assigns

  • 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

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

Sits

---
title: Sits
---
erDiagram
    STUDENT }|--|{ EXAM : sits
    STUDENT {
        number S_ID PK
        string S_name
        string S_mail
        string S_contact
        string EnrollYear
    }
    EXAM {
        int  E_ID PK
        string E_Date
        string E_type
        string E_roomno
        number Mark
    }
  • Unnormalized Form: S_ID(PK), S_name, S_mail, S_contact, EnrollYear, E_ID(PK), E_Date, E_type, E_roomno, Mark
  • 1NF: S_ID(PK), S_name, S_mail, S_contact, EnrollYear, E_ID(PK), E_Date, E_type, E_roomno, Mark
  • 2NF:
    1. S_ID(PK), S_name, S_mail, S_contact, 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_mail, S_contact, EnrollYear
    2. E_ID(PK), E_Date, E_type, E_roomno, Mark
    3. S_ID(PK), E_ID(FK)

Teaches

  • 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), Sname, 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), Sname, S_contact, S_mail, EnrollYear, _F_ID(FK)
    2. F_ID(PK), F_name, F_contact, F_mail

Attends

  • 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

  • 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
    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
    3. S_ID(PK), P_ID(FK)

Takes

  • 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

  • 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

  • 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), Edate, 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), Edate, E_type, E_roomno, Mark, _C_ID(FK)

Offers

  • Unnormalized Form: D_ID(PK), Dname, D_head, F_ID(PK), F_name, F_contact, F_mail, _C_ID(FK), C_name, C_duration, C_credit
  • 1NF: D_ID(PK), Dname, 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), Fname, F_contact, F_mail, _C_ID(FK), Cname, C_duration, C_credit, _D_ID(FK)
  • 3NF: Same as 2NF
  • Tables:
    1. D_ID(PK), D_name, D_head
    2. F_ID(PK), Fname, F_contact, F_mail, _C_ID(FK), Cname, C_duration, C_credit, _D_ID(FK)

Observes

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

Gets

  • 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), Sname, 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), Sname, S_contact, S_mail, EnrollYear, _G_ID(FK)
    2. G_ID(PK), CGPA, Semester, Sem_year

Submits

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

Finalization

Table Name Table Columns Primary Key Foreign Key
program P_ID, P_name, P_duration, P_type P_ID
dept D_ID, D_name, D_head D_ID
prodept D_ID, P_ID P_ID D_ID
deptexam E_ID, E_date, E_type, E_roomno, Mark, D_ID E_ID D_ID
exam E_ID, E_Date, E_type, E_roomno, Mark E_ID
student S_ID, S_name, S_mail, S_contact, EnrollYear S_ID
stdexam S_ID, E_ID S_ID E_ID
stdfac S_ID, S_name, S_contact, S_mail, EnrollYear, F_ID S_ID F_ID
faculty F_ID, F_name, F_contact, F_mail F_ID
classroom Cl_name, Cl_time, Cl_roomno Cl_roomno
stdcls S_ID, Cl_roomno S_ID Cl_roomno
stdpro S_ID, P_ID S_ID P_ID
course C_ID, C_name, C_duration, C_credit C_ID
faccrs F_ID, C_ID F_ID C_ID
stdcrs S_ID, C_ID S_ID C_ID
crsexam E_ID, E_date, E_type, E_roomno, Mark, C_ID E_ID C_ID
deptofferfaccrs F_ID, F_name, F_contact, F_mail, C_ID, C_name, C_duration, C_credit, D_ID F_ID D_ID, C_ID
deptauth D_ID, D_name, D_head, A_mail D_ID A_mail
authority A_mail, A_name, A_role, A_contact A_mail
stdgrade S_ID, S_name, S_contact, S_mail, EnrollYear, G_ID S_ID G_ID
grade G_ID, cgpa, Semester, Sem_year G_ID
facgrade G_ID, cgpa, Semester, Sem_year, F_ID G_ID F_ID
Clone this wiki locally