Skip to content

Normalization & Finalization

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

ER Diagram

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

  • 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

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

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

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

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, Fname, 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

Create Table using Oracle SQL

-- Create Table: program
CREATE TABLE program (
    P_ID NUMBER PRIMARY KEY,
    P_name VARCHAR2(255),
    P_duration NUMBER,
    P_type VARCHAR2(50)
);

-- Create Table: dept
CREATE TABLE dept (
    D_ID NUMBER PRIMARY KEY,
    D_name VARCHAR2(255),
    D_head VARCHAR2(255)
);

-- Create Table: prodept
CREATE TABLE prodept (
    P_ID NUMBER,
    D_ID NUMBER,
    PRIMARY KEY (P_ID),
    FOREIGN KEY (D_ID) REFERENCES dept(D_ID)
);

-- Create Table: deptconductexam
CREATE TABLE deptconductexam (
    E_ID NUMBER,
    E_Date DATE,
    E_type VARCHAR2(50),
    E_roomno VARCHAR2(50),
    Mark NUMBER,
    D_ID NUMBER,
    PRIMARY KEY (E_ID),
    FOREIGN KEY (D_ID) REFERENCES dept(D_ID)
);

-- Create Table: student
CREATE TABLE student (
    S_ID NUMBER PRIMARY KEY,
    S_name VARCHAR2(255),
    S_mail VARCHAR2(255),
    S_contact VARCHAR2(20),
    EnrollYear NUMBER
);

-- Create Table: exam
CREATE TABLE exam (
    E_ID NUMBER PRIMARY KEY,
    E_Date DATE,
    E_type VARCHAR2(50),
    E_roomno VARCHAR2(50),
    Mark NUMBER
);

-- Create Table: stdexam
CREATE TABLE stdexam (
    S_ID NUMBER,
    E_ID NUMBER,
    PRIMARY KEY (S_ID, E_ID),
    FOREIGN KEY (S_ID) REFERENCES student(S_ID),
    FOREIGN KEY (E_ID) REFERENCES exam(E_ID)
);

-- Create Table: stdfac
CREATE TABLE stdfac (
    S_ID NUMBER,
    F_ID NUMBER,
    PRIMARY KEY (S_ID),
    FOREIGN KEY (S_ID) REFERENCES student(S_ID),
    FOREIGN KEY (F_ID) REFERENCES faculty(F_ID)
);

-- Create Table: faculty
CREATE TABLE faculty (
    F_ID NUMBER PRIMARY KEY,
    F_name VARCHAR2(255),
    F_contact VARCHAR2(20),
    F_mail VARCHAR2(255)
);

-- Create Table: classroom
CREATE TABLE classroom (
    Cl_roomno VARCHAR2(20) PRIMARY KEY,
    Cl_time VARCHAR2(50),
    Cl_name VARCHAR2(255)
);

-- Create Table: stdcls
CREATE TABLE stdcls (
    S_ID NUMBER,
    Cl_roomno VARCHAR2(20),
    PRIMARY KEY (S_ID),
    FOREIGN KEY (S_ID) REFERENCES student(S_ID),
    FOREIGN KEY (Cl_roomno) REFERENCES classroom(Cl_roomno)
);

-- Create Table: stdpro
CREATE TABLE stdpro (
    S_ID NUMBER,
    P_ID NUMBER,
    PRIMARY KEY (S_ID),
    FOREIGN KEY (S_ID) REFERENCES student(S_ID),
    FOREIGN KEY (P_ID) REFERENCES program(P_ID)
);

-- Create Table: course
CREATE TABLE course (
    C_ID NUMBER PRIMARY KEY,
    C_name VARCHAR2(255),
    C_duration NUMBER,
    C_credit NUMBER
);

-- Create Table: stdcrs
CREATE TABLE stdcrs (
    S_ID NUMBER,
    C_ID NUMBER,
    PRIMARY KEY (S_ID, C_ID),
    FOREIGN KEY (S_ID) REFERENCES student(S_ID),
    FOREIGN KEY (C_ID) REFERENCES course(C_ID)
);

-- Create Table: faccrs
CREATE TABLE faccrs (
    F_ID NUMBER,
    C_ID NUMBER,
    PRIMARY KEY (F_ID, C_ID),
    FOREIGN KEY (F_ID) REFERENCES faculty(F_ID),
    FOREIGN KEY (C_ID) REFERENCES course(C_ID)
);

-- Create Table: crsexam
CREATE TABLE crsexam (
    E_ID NUMBER,
    E_date DATE,
    E_type VARCHAR2(50),
    E_roomno VARCHAR2(50),
    Mark NUMBER,
    C_ID NUMBER,
    PRIMARY KEY (E_ID),
    FOREIGN KEY (C_ID) REFERENCES course(C_ID)
);

-- Create Table: deptofferfaccrs
CREATE TABLE deptofferfaccrs (
    F_ID NUMBER,
    Fname VARCHAR2(255),
    F_contact VARCHAR2(20),
    F_mail VARCHAR2(255),
    C_ID NUMBER,
    Cname VARCHAR2(255),
    C_duration NUMBER,
    C_credit NUMBER,
    D_ID NUMBER,
    PRIMARY KEY (F_ID, C_ID),
    FOREIGN KEY (F_ID) REFERENCES faculty(F_ID),
    FOREIGN KEY (C_ID) REFERENCES course(C_ID),
    FOREIGN KEY (D_ID) REFERENCES dept(D_ID)
);

-- Create Table: deptauth
CREATE TABLE deptauth (
    D_ID NUMBER PRIMARY KEY,
    D_name VARCHAR2(255),
    D_head VARCHAR2(255),
    A_mail VARCHAR2(255),
    FOREIGN KEY (D_ID) REFERENCES dept(D_ID)
);

-- Create Table: authority
CREATE TABLE authority (
    A_mail VARCHAR2(255) PRIMARY KEY,
    A_name VARCHAR2(255),
    A_role VARCHAR2(50),
    A_contact VARCHAR2(20)
);

-- Create Table: stdgrade
CREATE TABLE stdgrade (
    S_ID NUMBER,
    S_name VARCHAR2(255),
    S_contact VARCHAR2(20),
    S_mail VARCHAR2(255),
    EnrollYear NUMBER,
    G_ID NUMBER,
    PRIMARY KEY (S_ID),
    FOREIGN KEY (S_ID) REFERENCES student(S_ID),
    FOREIGN KEY (G_ID) REFERENCES grade(G_ID)
);

-- Create Table: grade
CREATE TABLE grade (
    G_ID NUMBER PRIMARY KEY,
    CGPA NUMBER,
    Semester VARCHAR2(50),
    Sem_year NUMBER
);

-- Create Table: facgrade
CREATE TABLE facgrade (
    G_ID NUMBER,
    CGPA NUMBER,
    Semester VARCHAR2(50),
    Sem_year NUMBER,
    F_ID NUMBER,
    PRIMARY KEY (G_ID),
    FOREIGN KEY (G_ID) REFERENCES grade(G_ID),
    FOREIGN KEY (F_ID) REFERENCES faculty(F_ID)
);

Create Table using MySQL

-- Create the program table
CREATE TABLE program (
    P_ID INT PRIMARY KEY,
    P_name VARCHAR(255),
    P_duration INT,
    P_type VARCHAR(50)
);

-- Create the dept table
CREATE TABLE dept (
    D_ID INT PRIMARY KEY,
    D_name VARCHAR(255),
    D_head VARCHAR(255)
);

-- Create the prodept table
CREATE TABLE prodept (
    P_ID INT,
    D_ID INT,
    PRIMARY KEY (P_ID),
    FOREIGN KEY (D_ID) REFERENCES dept(D_ID)
);

-- Create the deptconductexam table
CREATE TABLE deptconductexam (
    E_ID INT,
    E_Date DATE,
    E_type VARCHAR(50),
    E_roomno INT,
    Mark DECIMAL(5, 2),
    D_ID INT,
    PRIMARY KEY (E_ID),
    FOREIGN KEY (D_ID) REFERENCES dept(D_ID)
);

-- Create the student table
CREATE TABLE student (
    S_ID INT PRIMARY KEY,
    S_name VARCHAR(255),
    S_mail VARCHAR(255),
    S_contact VARCHAR(20),
    EnrollYear INT
);

-- Create the exam table
CREATE TABLE exam (
    E_ID INT PRIMARY KEY,
    E_Date DATE,
    E_type VARCHAR(50),
    E_roomno INT,
    Mark DECIMAL(5, 2)
);

-- Create the stdexam table
CREATE TABLE stdexam (
    S_ID INT,
    E_ID INT,
    PRIMARY KEY (S_ID, E_ID),
    FOREIGN KEY (S_ID) REFERENCES student(S_ID),
    FOREIGN KEY (E_ID) REFERENCES exam(E_ID)
);

-- Create the stdfac table
CREATE TABLE stdfac (
    S_ID INT,
    S_name VARCHAR(255),
    S_contact VARCHAR(20),
    S_mail VARCHAR(255),
    EnrollYear INT,
    F_ID INT,
    PRIMARY KEY (S_ID),
    FOREIGN KEY (F_ID) REFERENCES faculty(F_ID)
);

-- Create the faculty table
CREATE TABLE faculty (
    F_ID INT PRIMARY KEY,
    F_name VARCHAR(255),
    F_contact VARCHAR(20),
    F_mail VARCHAR(255)
);

-- Create the classroom table
CREATE TABLE classroom (
    Cl_roomno INT PRIMARY KEY,
    Cl_time TIME,
    Cl_name VARCHAR(255)
);

-- Create the stdcls table
CREATE TABLE stdcls (
    S_ID INT,
    Cl_roomno INT,
    PRIMARY KEY (S_ID),
    FOREIGN KEY (S_ID) REFERENCES student(S_ID),
    FOREIGN KEY (Cl_roomno) REFERENCES classroom(Cl_roomno)
);

-- Create the stdpro table
CREATE TABLE stdpro (
    S_ID INT,
    P_ID INT,
    PRIMARY KEY (S_ID),
    FOREIGN KEY (S_ID) REFERENCES student(S_ID),
    FOREIGN KEY (P_ID) REFERENCES program(P_ID)
);

-- Create the course table
CREATE TABLE course (
    C_ID INT PRIMARY KEY,
    C_name VARCHAR(255),
    C_duration INT,
    C_credit INT
);

-- Create the stdcrs table
CREATE TABLE stdcrs (
    S_ID INT,
    C_ID INT,
    PRIMARY KEY (S_ID, C_ID),
    FOREIGN KEY (S_ID) REFERENCES student(S_ID),
    FOREIGN KEY (C_ID) REFERENCES course(C_ID)
);

-- Create the faccrs table
CREATE TABLE faccrs (
    F_ID INT,
    C_ID INT,
    PRIMARY KEY (F_ID, C_ID),
    FOREIGN KEY (F_ID) REFERENCES faculty(F_ID),
    FOREIGN KEY (C_ID) REFERENCES course(C_ID)
);

-- Create the crsexam table
CREATE TABLE crsexam (
    E_ID INT,
    E_date DATE,
    E_type VARCHAR(50),
    E_roomno INT,
    Mark DECIMAL(5, 2),
    C_ID INT,
    PRIMARY KEY (E_ID),
    FOREIGN KEY (C_ID) REFERENCES course(C_ID)
);

-- Create the deptofferfaccrs table
CREATE TABLE deptofferfaccrs (
    F_ID INT,
    Fname VARCHAR(255),
    F_contact VARCHAR(20),
    F_mail VARCHAR(255),
    C_ID INT,
    Cname VARCHAR(255),
    C_duration INT,
    C_credit INT,
    D_ID INT,
    PRIMARY KEY (F_ID),
    FOREIGN KEY (C_ID) REFERENCES course(C_ID),
    FOREIGN KEY (D_ID) REFERENCES dept(D_ID)
);

-- Create the deptauth table
CREATE TABLE deptauth (
    D_ID INT,
    D_name VARCHAR(255),
    D_head VARCHAR(255),
    A_mail VARCHAR(255),
    PRIMARY KEY (D_ID),
    FOREIGN KEY (A_mail) REFERENCES authority(A_mail)
);

-- Create the authority table
CREATE TABLE authority (
    A_mail VARCHAR(255) PRIMARY KEY,
    A_name VARCHAR(255),
    A_role VARCHAR(50),
    A_contact VARCHAR(20)
);

-- Create the stdgrade table
CREATE TABLE stdgrade (
    S_ID INT,
    S_name VARCHAR(255),
    S_contact VARCHAR(20),
    S_mail VARCHAR(255),
    EnrollYear INT,
    G_ID INT,
    PRIMARY KEY (S_ID),
    FOREIGN KEY (G_ID) REFERENCES grade(G_ID)
);

-- Create the grade table
CREATE TABLE grade (
    G_ID INT PRIMARY KEY,
    CGPA DECIMAL(3, 2),
    Semester VARCHAR(20),
    Sem_year VARCHAR(10)
);

-- Create the facgrade table
CREATE TABLE facgrade (
    G_ID INT,
    CGPA DECIMAL(3, 2),
    Semester VARCHAR(20),
    Sem_year VARCHAR(10),
    F_ID INT,
    PRIMARY KEY (G_ID),
    FOREIGN KEY (F_ID) REFERENCES faculty(F_ID)
);
Clone this wiki locally