Lab 1: University Database ER Diagram

Design and Implementation of University Database System

Objective

Design and implement a comprehensive University Database System that tracks students' transcripts, including their enrollment in courses, grades, and academic information. This lab focuses on creating an Entity-Relationship (ER) diagram and implementing the corresponding relational database schema.

Entity Types and Attributes

1. STUDENT Entity

Attribute Type Constraints Description
StudentNumber INT Primary Key, Unique Unique student identifier
SSN CHAR(9) Unique, NOT NULL Social Security Number
Name Composite FirstName, LastName Student's full name
CurrentAddress Composite Street, City, State, ZipCode Current residential address
PermanentAddress Composite Street, City, State, ZipCode Permanent residential address
Class ENUM Freshman, Sophomore, Junior, Senior, Graduate Academic level
DegreeProgram ENUM BA, BS, MS, PhD Degree being pursued

2. DEPARTMENT Entity

Attribute Type Constraints Description
DeptCode VARCHAR(10) Primary Key Department code identifier
DeptName VARCHAR(100) Unique, NOT NULL Department name
OfficeNumber VARCHAR(20) Department office location
College VARCHAR(100) College the department belongs to

3. COURSE Entity

Attribute Type Constraints Description
CourseNumber VARCHAR(10) Primary Key Unique course identifier
CourseName VARCHAR(100) NOT NULL Course title
Description TEXT Course description
SemesterHours INT Credit hours
Level ENUM Undergraduate, Graduate Course level

4. SECTION Entity

Attribute Type Constraints Description
SectionID INT Primary Key, Auto Increment Unique section identifier
CourseNumber VARCHAR(10) Foreign Key, NOT NULL References Course
Semester ENUM Spring, Summer, Fall, Winter Academic semester
Year YEAR NOT NULL Academic year
SectionNumber INT NOT NULL Section number within course/semester

5. GRADE_REPORT Entity

Attribute Type Constraints Description
StudentNumber INT Primary Key, Foreign Key References Student
SectionID INT Primary Key, Foreign Key References Section
LetterGrade CHAR(2) Letter grade (A, B, C, D, F)
NumericGrade INT CHECK (0-4) Numeric grade equivalent

Relationship Types

Relationship Entities Cardinality Participation Description
MAJORS_IN STUDENT ↔ DEPARTMENT N:1 Total (Student), Partial (Department) Every student must have a major department
MINORS_IN STUDENT ↔ DEPARTMENT N:1 Partial (Both) Students may have a minor department
OFFERS DEPARTMENT ↔ COURSE 1:N Total (Course), Partial (Department) Every course is offered by a department
SECTION_OF COURSE ↔ SECTION 1:N Total (Section), Partial (Course) Every section belongs to a course
ENROLLED_IN STUDENT ↔ SECTION M:N Partial (Both) Captured through GRADE_REPORT entity

Integrity Constraints

Primary Key Constraints:

  • Student: Both StudentNumber and SSN must be unique
  • Department: Both DeptCode and DeptName must be unique
  • Course: CourseNumber must be unique
  • Section: Composite key (CourseNumber, Semester, Year, SectionNumber)
  • Grade Report: Composite key (StudentNumber, SectionID)

Referential Integrity:

  • Student's major department must exist in DEPARTMENT table
  • Student's minor department (if specified) must exist in DEPARTMENT table
  • Course's offering department must exist in DEPARTMENT table
  • Section's course must exist in COURSE table
  • Grade report's student must exist in STUDENT table
  • Grade report's section must exist in SECTION table

Domain Constraints:

  • NumericGrade must be between 0 and 4
  • Class must be one of: Freshman, Sophomore, Junior, Senior, Graduate
  • DegreeProgram must be one of: BA, BS, MS, PhD
  • Semester must be one of: Spring, Summer, Fall, Winter

SQL Schema Implementation

The following SQL script creates the complete database schema based on the ER diagram:

-- University Database Schema

CREATE DATABASE IF NOT EXISTS UniversityDB;
USE UniversityDB;

-- Table: DEPARTMENT
CREATE TABLE Department (
    DeptCode VARCHAR(10) PRIMARY KEY,
    DeptName VARCHAR(100) UNIQUE NOT NULL,
    OfficeNumber VARCHAR(20),
    OfficePhone VARCHAR(20),
    College VARCHAR(100)
);

-- Table: INSTRUCTOR
CREATE TABLE Instructor (
    InstructorID INT AUTO_INCREMENT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DeptCode VARCHAR(10),
    Email VARCHAR(100),
    Phone VARCHAR(20),
    FOREIGN KEY (DeptCode) REFERENCES Department(DeptCode)
);

-- Table: STUDENT
CREATE TABLE Student (
    StudentNumber INT PRIMARY KEY,
    SSN CHAR(9) UNIQUE NOT NULL,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    CurrentStreet VARCHAR(100),
    CurrentCity VARCHAR(50),
    CurrentState VARCHAR(50),
    CurrentZipCode VARCHAR(10),
    CurrentPhone VARCHAR(20),
    PermanentStreet VARCHAR(100),
    PermanentCity VARCHAR(50),
    PermanentState VARCHAR(50),
    PermanentZipCode VARCHAR(10),
    PermanentPhone VARCHAR(20),
    BirthDate DATE,
    Sex CHAR(1),
    Class ENUM('Freshman','Sophomore','Junior','Senior','Graduate'),
    DegreeProgram ENUM('BA','BS','MS','PhD'),
    MajorDeptCode VARCHAR(10) NOT NULL,
    MinorDeptCode VARCHAR(10),
    FOREIGN KEY (MajorDeptCode) REFERENCES Department(DeptCode),
    FOREIGN KEY (MinorDeptCode) REFERENCES Department(DeptCode)
);

-- Table: COURSE
CREATE TABLE Course (
    CourseNumber VARCHAR(10) PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL,
    Description TEXT,
    SemesterHours INT,
    Level ENUM('Undergraduate','Graduate'),
    DeptCode VARCHAR(10) NOT NULL,
    FOREIGN KEY (DeptCode) REFERENCES Department(DeptCode)
);

-- Table: SECTION
CREATE TABLE Section (
    SectionID INT AUTO_INCREMENT PRIMARY KEY,
    CourseNumber VARCHAR(10) NOT NULL,
    Semester ENUM('Spring','Summer','Fall','Winter') NOT NULL,
    Year YEAR NOT NULL,
    SectionNumber INT NOT NULL,
    InstructorID INT NOT NULL,
    UNIQUE (CourseNumber, Semester, Year, SectionNumber),
    FOREIGN KEY (CourseNumber) REFERENCES Course(CourseNumber),
    FOREIGN KEY (InstructorID) REFERENCES Instructor(InstructorID)
);

-- Table: GRADE_REPORT
CREATE TABLE GradeReport (
    StudentNumber INT NOT NULL,
    SectionID INT NOT NULL,
    LetterGrade CHAR(2),
    NumericGrade INT CHECK (NumericGrade BETWEEN 0 AND 4),
    PRIMARY KEY (StudentNumber, SectionID),
    FOREIGN KEY (StudentNumber) REFERENCES Student(StudentNumber),
    FOREIGN KEY (SectionID) REFERENCES Section(SectionID)
);

Download Resources

Access the complete lab resources including ER diagram, database model, and SQL script:

ER Diagram (PDF) SQL Schema MySQL Workbench Model Specifications Document

Conclusion

This lab demonstrates the complete process of designing a university database system from requirements analysis to implementation. The ER diagram captures all the essential entities, relationships, and constraints needed to track student transcripts effectively. The resulting relational schema provides a solid foundation for a comprehensive university information system.

Key Learning Outcomes: