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), D_name, 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), D_name, 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), 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

  • 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), 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

  • 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), 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), 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

  • 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

  • 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)
  4. D_ID(PK), D_name, D_head, E_ID(FK)
  5. E_ID(PK), E_Date, E_type, E_roomno, Mark
  6. S_ID(PK), S_name, S_mail, S_contact, EnrollYear
  • E_ID(PK), E_Date, E_type, E_roomno, Mark
  1. S_ID(PK), E_ID(FK)
  2. S_ID(PK), S_name, S_contact, S_mail, EnrollYear, F_ID(FK)
  3. F_ID(PK), F_name, F_contact, F_mail
  4. S_ID(PK), S_name, S_contact, S_mail, EnrollYear
  5. Cl_roomno(PK), Cl_time, Cl_name
  6. S_ID(PK), Cl_roomno(FK)
  • S_ID(PK), S_name, S_contact, S_mail, EnrollYear
  1. P_ID(PK), P_name, P_duration
  2. 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), Fname, F_contact, F_mail, C_ID(FK), Cname, 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)
Clone this wiki locally