Skip to content

Table Creation

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

Database Table Creation for Student Management System

In the Student Management System project, the establishment of a robust and well-organized database forms the cornerstone of efficient data management. This comprehensive guide outlines the step-by-step process of creating the essential database tables required for the system, utilizing both Oracle and MySQL database systems.

Prerequisites

Before embarking on the table creation process, it's imperative to ensure that the following prerequisites are met:

  • Oracle Database: Confirm the presence of a functional Oracle Database on your system. Additionally, ensure that you possess the necessary permissions to create tables and establish relationships.

OR

  • MySQL Database: Ensure the availability of a functional MySQL database. Verify that you hold the requisite permissions to create tables and establish relationships.

Step-by-Step Table Creation

Follow the instructions outlined below to create the requisite tables for the Student Management System using either Oracle SQL or MySQL:

  1. Copy the Appropriate SQL Script: Depending on your chosen database system, copy the corresponding SQL script provided below.

  2. Access a SQL Client: Open your preferred SQL client, such as SQL*Plus for Oracle or SQL Developer. Ensure that you have a connection established with your respective database.

  3. Paste and Execute: Paste the copied SQL script into the SQL client's query window. Execute the script to initiate the creation of the essential tables.

Create Table using Oracle SQL

-- Create Table: program
CREATE TABLE program (
    P_ID NUMBER PRIMARY KEY,
    P_name VARCHAR2(255),
    P_duration VARCHAR2(50),
    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: faculty
CREATE TABLE faculty (
    F_ID NUMBER PRIMARY KEY,
    F_name VARCHAR2(255),
    F_contact VARCHAR2(50),
    F_mail VARCHAR2(255)
);

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

-- 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: classroom
CREATE TABLE classroom (
    Cl_roomno VARCHAR2(50) PRIMARY KEY,
    Cl_time VARCHAR2(50),
    Cl_name VARCHAR2(255)
);

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

-- Create Table: stdexam
CREATE TABLE stdexam (
    S_ID NUMBER PRIMARY KEY,
    E_ID NUMBER,
    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 PRIMARY KEY,
    S_name VARCHAR2(255),
    S_contact VARCHAR2(50),
    S_mail VARCHAR2(255),
    EnrollYear DATE,
    F_ID NUMBER,
    FOREIGN KEY (S_ID, S_name, S_contact, S_mail, EnrollYear) REFERENCES student(S_ID, S_name, S_contact, S_mail, EnrollYear),
    FOREIGN KEY (F_ID) REFERENCES faculty(F_ID)
);

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

-- Create Table: stdcls
CREATE TABLE stdcls (
    S_ID NUMBER,
    Cl_roomno VARCHAR2(50),
    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 PRIMARY KEY,
    P_ID NUMBER,
    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 VARCHAR2(50),
    C_credit NUMBER
);

-- Create Table: stdcrs
CREATE TABLE stdcrs (
    S_ID NUMBER PRIMARY KEY,
    C_ID NUMBER,
    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 PRIMARY KEY,
    C_ID NUMBER,
    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 PRIMARY KEY,
    E_date DATE,
    E_type VARCHAR2(50),
    E_roomno VARCHAR2(50),
    Mark NUMBER,
    C_ID NUMBER,
    FOREIGN KEY (E_ID, E_date, E_type, E_roomno, Mark) REFERENCES exam(E_ID, E_Date, E_type, E_roomno, Mark),
    FOREIGN KEY (C_ID) REFERENCES course(C_ID)
);

-- Create Table: deptofferfaccrs
CREATE TABLE deptofferfaccrs (
    F_ID NUMBER,
    C_ID 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: 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, E_Date, E_type, E_roomno),
    FOREIGN KEY (E_ID, E_Date, E_type, E_roomno, Mark) REFERENCES exam(E_ID, E_Date, E_type, E_roomno, Mark),
    FOREIGN KEY (D_ID) REFERENCES dept(D_ID)
);

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

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

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

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

Create Table using MySQL

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

-- Create Table: faculty
CREATE TABLE faculty (
    F_ID INT PRIMARY KEY,
    F_name VARCHAR(255),
    F_contact VARCHAR(50),
    F_mail VARCHAR(255)
);

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

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

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

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

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

-- Create Table: course
CREATE TABLE course (
    C_ID INT PRIMARY KEY,
    C_name VARCHAR(255),
    C_duration VARCHAR(50),
    C_credit INT
);

-- Create Table: student
CREATE TABLE student (
    S_ID INT PRIMARY KEY,
    S_name VARCHAR(255),
    S_mail VARCHAR(255),
    S_contact VARCHAR(50),
    EnrollYear DATE
);

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

-- Create Table: stdfac
CREATE TABLE stdfac (
    S_ID INT PRIMARY KEY,
    S_name VARCHAR(255),
    S_contact VARCHAR(50),
    S_mail VARCHAR(255),
    EnrollYear DATE,
    F_ID INT,
    FOREIGN KEY (S_ID, S_name, S_contact, S_mail, EnrollYear) REFERENCES student(S_ID, S_name, S_contact, S_mail, EnrollYear),
    FOREIGN KEY (F_ID) REFERENCES faculty(F_ID)
);

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

-- Create Table: stdcls
CREATE TABLE stdcls (
    S_ID INT,
    Cl_roomno VARCHAR(50),
    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 INT PRIMARY KEY,
    P_ID INT,
    FOREIGN KEY (S_ID) REFERENCES student(S_ID),
    FOREIGN KEY (P_ID) REFERENCES program(P_ID)
);

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

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

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

-- Create Table: deptofferfaccrs
CREATE TABLE deptofferfaccrs (
    F_ID INT,
    C_ID INT,
    D_ID INT,
    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: deptconductexam
CREATE TABLE deptconductexam (
    E_ID INT PRIMARY KEY,
    E_Date DATE,
    E_type VARCHAR(50),
    E_roomno VARCHAR(50),
    Mark INT,
    D_ID INT,
    FOREIGN KEY (D_ID) REFERENCES dept(D_ID),
    FOREIGN KEY (E_ID, E_Date, E_type, E_roomno, Mark) REFERENCES exam(E_ID, E_Date, E_type, E_roomno, Mark)
);

-- Create Table: stdgrade
CREATE TABLE stdgrade (
    S_ID INT PRIMARY KEY,
    S_name VARCHAR(255),
    S_contact VARCHAR(50),
    S_mail VARCHAR(255),
    EnrollYear DATE,
    G_ID INT,
    FOREIGN KEY (S_ID, S_name, S_contact, S_mail, EnrollYear) REFERENCES student(S_ID, S_name, S_contact, S_mail, EnrollYear),
    FOREIGN KEY (G_ID) REFERENCES grade(G_ID)
);

-- Create Table: grade
CREATE TABLE grade (
    G_ID INT PRIMARY KEY,
    CGPA DECIMAL(3, 2),
    Semester VARCHAR(50),
    Sem_year VARCHAR(50)
);

-- Create Table: facgrade
CREATE TABLE facgrade (
    G_ID INT PRIMARY KEY,
    CGPA DECIMAL(3, 2),
    Semester VARCHAR(50),
    Sem_year VARCHAR(50),
    F_ID INT,
    FOREIGN KEY (G_ID, CGPA, Semester, Sem_year) REFERENCES grade(G_ID, CGPA, Semester, Sem_year),
    FOREIGN KEY (F_ID) REFERENCES faculty(F_ID)
);
Clone this wiki locally