Skip to content

Table Creation

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

Database Table Creation for Student Management System

In the Student Management System project, a robust and well-organized database forms the foundation of efficient data management. This guide walks you through the process of creating essential database tables required for the system using Oracle SQL.

Prerequisites

Before you proceed with table creation, ensure the following prerequisites are met:

  • Oracle Database: Make sure you have an Oracle Database installed and configured on your system. You should also have the necessary permissions to create tables and define relationships.

OR

  • MySQL Database: Have a MySQL database up and running, and ensure that you have the required permissions to create tables and define relationships.

Table Creation Steps

Follow these steps to create the required tables for the Student Management System:

  1. Copy the provided SQL script listed below.

  2. Open a preferred SQL client such as SQL*Plus or SQL Developer.

  3. Establish a connection to your Oracle Database.

  4. Paste the copied SQL script into the SQL client and execute it to create the 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: 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: deptconductexam
CREATE TABLE deptconductexam (
    E_ID NUMBER PRIMARY KEY,
    E_Date DATE,
    E_type VARCHAR2(50),
    E_roomno VARCHAR2(50),
    Mark NUMBER,
    D_ID NUMBER,
    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: 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: 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 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: faculty
CREATE TABLE faculty (
    F_ID NUMBER PRIMARY KEY,
    F_name VARCHAR2(255),
    F_contact VARCHAR2(50),
    F_mail VARCHAR2(255)
);

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

-- 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,
    F_name VARCHAR2(255),
    F_contact VARCHAR2(50),
    F_mail VARCHAR2(255),
    C_ID NUMBER,
    C_name VARCHAR2(255),
    C_duration VARCHAR2(50),
    C_credit NUMBER,
    D_ID NUMBER,
    PRIMARY KEY (F_ID, C_ID),
    FOREIGN KEY (F_ID, F_name, F_contact, F_mail) REFERENCES faculty(F_ID, F_name, F_contact, F_mail),
    FOREIGN KEY (C_ID, C_name, C_duration, C_credit) REFERENCES course(C_ID, C_name, C_duration, C_credit),
    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: authority
CREATE TABLE authority (
    A_mail VARCHAR2(255) PRIMARY KEY,
    A_name VARCHAR2(255),
    A_role VARCHAR2(50),
    A_contact VARCHAR2(50)
);

-- 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: program
CREATE TABLE program (
    P_ID INT PRIMARY KEY,
    P_name VARCHAR(255),
    P_duration VARCHAR(50),
    P_type VARCHAR(50)
);

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

-- 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: 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: 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: exam
CREATE TABLE exam (
    E_ID INT PRIMARY KEY,
    E_Date DATE,
    E_type VARCHAR(50),
    E_roomno VARCHAR(50),
    Mark INT
);

-- 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: faculty
CREATE TABLE faculty (
    F_ID INT PRIMARY KEY,
    F_name VARCHAR(255),
    F_contact VARCHAR(50),
    F_mail VARCHAR(255)
);

-- 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: course
CREATE TABLE course (
    C_ID INT PRIMARY KEY,
    C_name VARCHAR(255),
    C_duration VARCHAR(50),
    C_credit INT
);

-- 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,
    F_name VARCHAR(255),
    F_contact VARCHAR(50),
    F_mail VARCHAR(255),
    C_ID INT,
    C_name VARCHAR(255),
    C_duration VARCHAR(50),
    C_credit INT,
    D_ID INT,
    PRIMARY KEY (F_ID, C_ID),
    FOREIGN KEY (F_ID, F_name, F_contact, F_mail) REFERENCES faculty(F_ID, F_name, F_contact, F_mail),
    FOREIGN KEY (C_ID, C_name, C_duration, C_credit) REFERENCES course(C_ID, C_name, C_duration, C_credit),
    FOREIGN KEY (D_ID) REFERENCES dept(D_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: authority
CREATE TABLE authority (
    A_mail VARCHAR(255) PRIMARY KEY,
    A_name VARCHAR(255),
    A_role VARCHAR(50),
    A_contact VARCHAR(50)
);

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