University Database Schema and Tables

Having a cohesive and uniform example schema and tables is much essential to understand SQL queries. To make it happen, here we present a university database schema and the associated tables.

University database ER diagram

The ER diagram (conceptual design) for a university information management application is shown in the image below:

University database schema

The logical design (relational model), the relational schema for the university information management application is given below:

Department (Dept_ID, Dept_Name, Chair_Prof_ID)

Department_Phone (Dept_ID, Phone_Number)

Professor (Prof_ID, Prof_Name, Dept_ID)

Dependent (Prof_ID, Dep_Name, Age)

Student (Student_ID, Student_Name, Dept_ID)

Course (Course_ID, Course_Name, Prof_ID)

Enrollment (Student_ID, Course_ID)

Project_Assignment (Student_ID, Course_ID, Prof_ID)

University database SQL Queries

The DDL and DML queries for creating, inserting and updating data in tables for the university information management application are given below.

DDL queries

1. Department Table

CREATE TABLE Department (
    Dept_ID INT PRIMARY KEY,
    Dept_Name VARCHAR(50) NOT NULL,
    Chair_Prof_ID INT
);

2. Department Phone Table

CREATE TABLE Department_Phone (
    Dept_ID INT,
    Phone_Number VARCHAR(15),
    PRIMARY KEY (Dept_ID, Phone_Number),
    FOREIGN KEY (Dept_ID) REFERENCES Department(Dept_ID) ON DELETE CASCADE
);

3. Professor Table

CREATE TABLE Professor (
    Prof_ID INT PRIMARY KEY,
    Prof_Name VARCHAR(50) NOT NULL,
    Dept_ID INT,
    FOREIGN KEY (Dept_ID) REFERENCES Department(Dept_ID) ON DELETE SET NULL
);

Add Foreign Key constraint for Department Chair after Professor table is created

ALTER TABLE Department
ADD CONSTRAINT FK_Dept_Chair
FOREIGN KEY (Chair_Prof_ID) REFERENCES Professor(Prof_ID) ON DELETE SET NULL;

4. Dependent Table

CREATE TABLE Dependent (
    Prof_ID INT,
    Dep_Name VARCHAR(50),
    Age INT CHECK (Age >= 0),
    PRIMARY KEY (Prof_ID, Dep_Name),
    FOREIGN KEY (Prof_ID) REFERENCES Professor(Prof_ID) ON DELETE CASCADE
);

5. Student Table

CREATE TABLE Student (
    Student_ID INT PRIMARY KEY,
    Student_Name VARCHAR(50) NOT NULL,
    Dept_ID INT,
    FOREIGN KEY (Dept_ID) REFERENCES Department(Dept_ID) ON DELETE SET NULL
);

6. Course Table

CREATE TABLE Course (
    Course_ID INT PRIMARY KEY,
    Course_Name VARCHAR(50) NOT NULL,
    Prof_ID INT,
    FOREIGN KEY (Prof_ID) REFERENCES Professor(Prof_ID) ON DELETE SET NULL
);

7. Enrollment Table

CREATE TABLE Enrollment (
    Student_ID INT,
    Course_ID INT,
    PRIMARY KEY (Student_ID, Course_ID),
    FOREIGN KEY (Student_ID) REFERENCES Student(Student_ID) ON DELETE CASCADE,
    FOREIGN KEY (Course_ID) REFERENCES Course(Course_ID) ON DELETE CASCADE
);

8. Project Assignment Table

CREATE TABLE Project_Assignment (
    Student_ID INT,
    Course_ID INT,
    Prof_ID INT,
    PRIMARY KEY (Student_ID, Course_ID, Prof_ID),
    FOREIGN KEY (Student_ID, Course_ID) REFERENCES Enrollment(Student_ID, Course_ID) ON DELETE CASCADE,
    FOREIGN KEY (Prof_ID) REFERENCES Professor(Prof_ID) ON DELETE CASCADE
);

DML queries

Insert into Department (Chair_Prof_ID set to NULL initially to handle circular dependency)

INSERT INTO Department (Dept_ID, Dept_Name, Chair_Prof_ID) VALUES
(101, 'Computer Science', NULL),
(102, 'Electrical Engineering', NULL),
(103, 'Mechanical Engineering', NULL),
(104, 'Mathematics', NULL),
(105, 'Physics', NULL),
(106, 'Civil Engineering', NULL);

Insert into Professor

INSERT INTO Professor (Prof_ID, Prof_Name, Dept_ID) VALUES
(1, 'Dr. Alice Smith', 101),
(2, 'Dr. Bob Jones', 101),
(3, 'Dr. Charlie Brown', 102),
(4, 'Dr. Diana Prince', 103),
(5, 'Dr. Edward Elric', 104),
(6, 'Dr. Fiona Gallagher', 105);

Update Department to assign Department Chairs

UPDATE Department SET Chair_Prof_ID = 1 WHERE Dept_ID = 101;
UPDATE Department SET Chair_Prof_ID = 3 WHERE Dept_ID = 102;
UPDATE Department SET Chair_Prof_ID = 4 WHERE Dept_ID = 103;
UPDATE Department SET Chair_Prof_ID = 5 WHERE Dept_ID = 104;
UPDATE Department SET Chair_Prof_ID = 6 WHERE Dept_ID = 105;

Insert into Department_Phone

INSERT INTO Department_Phone (Dept_ID, Phone_Number) VALUES
(101, '555-0101'),
(101, '555-0102'),
(102, '555-0201'),
(103, '555-0301'),
(104, '555-0401'),
(105, '555-0501');

Insert into Dependent

INSERT INTO Dependent (Prof_ID, Dep_Name, Age) VALUES
(1, 'Emma Smith', 10),
(1, 'Liam Smith', 8),
(2, 'Noah Jones', 12),
(3, 'Olivia Brown', 5),
(4, 'Ethan Prince', 15),
(5, 'Alphonse Elric', 17);

Insert into Student

INSERT INTO Student (Student_ID, Student_Name, Dept_ID) VALUES
(1001, 'John Doe', 101),
(1002, 'Jane Smith', 101),
(1003, 'Alex Johnson', 102),
(1004, 'Emily Davis', 103),
(1005, 'Michael Wilson', 104),
(1006, 'Sarah Taylor', 105);

Insert into Course

INSERT INTO Course (Course_ID, Course_Name, Prof_ID) VALUES
(201, 'Database Systems', 1),
(202, 'Algorithms', 2),
(203, 'Circuit Analysis', 3),
(204, 'Thermodynamics', 4),
(205, 'Linear Algebra', 5),
(206, 'Quantum Mechanics', 6);

Insert into Enrollment

INSERT INTO Enrollment (Student_ID, Course_ID) VALUES
(1001, 201),
(1001, 202),
(1002, 201),
(1003, 203),
(1004, 204),
(1005, 205);

Insert into Project_Assignment

INSERT INTO Project_Assignment (Student_ID, Course_ID, Prof_ID) VALUES
(1001, 201, 1),
(1001, 202, 2),
(1002, 201, 1),
(1003, 203, 3),
(1004, 204, 4),
(1005, 205, 5);

University database tables

The tables in the university information management database with sample data are given below. They will be used to demonstrate different SQL queries in the future articles.

Department table

Professor table

Department_Phone table

Dependent table

Student table

Course table

Enrollment table

Project_Assignment table

Suryateja Pericherla

Mr. P.S.Suryateja, also known as Suryateja Pericherla, is at present a Research Scholar (full-time Ph.D.) in the Dept. of Computer Science & Systems Engineering at Andhra University, Visakhapatnam. Previously worked as an Associate Professor in the Dept. of CSE at Vishnu Institute of Technology, India.

He has 14+ years of teaching experience and is an individual researcher whose research interests are Cloud Computing, Internet of Things, Computer Security, Network Security and Blockchain.

He is a member of professional societies like IEEE, ACM, CSI and ISCA. He published several research papers which are indexed by SCIE, WoS, Scopus, Springer and others.