This is the third of three case studies that are used to guide the reader through the six steps outlined in the Six-Step Relational Database Design™ book. The most important outputs of the six step database design process are depicted here, but the details of each of the steps and intermediary outputs are outlined in the book.
| What's on this page |
|---|
| Scenario List of entities and their corresponding attributes Entity-Relationship diagrams Relational-Model diagram SQL commands |
Scenario
Below is the scenario for Case Study 3 as described in the Six-Step Relational Database Design™ book:
The registrar at a small college wants an application that will help their department keep track of the schedule of classes, the courses and lecturers appearing in the schedule, and the students registering for courses according to the schedule.
Courses are scheduled every semester and this is documented in the schedule of classes, which also documents the lecturers assigned to each schedule of a class. Students register for courses according to the schedule of classes.
Users (students, lecturers, and other college staff) must login to the application to gain access, and the application must keep track of user logins/logouts. In addition, users must have different levels of access, which will determine their access to different parts of the application.
List of entities and their corresponding attributes
Below is the list of entities and their corresponding attributes that is output from Step 1 of the six step database design process as described in the Six-Step Relational Database Design™ book:
| Students |
|---|
| (PK) StudentId |
| SSNumber |
| LastName |
| FirstName |
| MiddleName |
| Gender |
| DOB |
| Mobile |
| HTel |
| AddressLine1 |
| AddressLine2 |
| City |
| State |
| PostCode |
| Courses |
|---|
| (PK) CourseId |
| CourseCode |
| ShortName |
| LongName |
| CourseDescription |
| ScheduleOfClasses |
|---|
| (PK) ScheduleId |
| ScheduleCode |
| Section |
| Day |
| Time |
| Location |
| Semesters |
|---|
| (PK) SemesterId |
| SemesterNumber |
| SemesterName |
| SemesterYear |
| StartDate |
| EndDate |
| Lecturers |
|---|
| (PK) LecturerId |
| SSNumber |
| LastName |
| FirstName |
| MiddleName |
| Gender |
| Mobile |
| HTel |
| WTel |
| About |
| AccessLevels |
|---|
| (PK) AccessLevelId |
| AccessLevelCode |
| ShortName |
| LongName |
| AccessLevelDescription |
| Users |
|---|
| (PK) UserId |
| Login |
| UserName |
| Password |
| Active |
| LogEntries |
|---|
| (PK) LogEntryId |
| LoggedOn |
| LoggedOn |
Top
Entity-Relationship diagrams
Below is the Simplified Entity-Relationship diagram that is output from Step 3 of the six step database design process as described in the Six-Step Relational Database Design™ book:
Below is the Detailed Entity-Relationship diagram that is output from Step 5 of the six step database design process as described in the Six-Step Relational Database Design™ book:
Top
Relational-Model diagrams
Below are the Relational-Model diagrams that are output from Step 6 of the six step database design process as described in the Six-Step Relational Database Design™ book:
Top
SQL Commands
The SQL commands below can be used to implement the design depicted above in a MySQL database. Some modifications will be necessary to execute these commands on MS SQL Server, Oracle, or any other RDBMS. Detailed implementation considerations can be found in the Six-Step Relational Database Design™ book.
CREATE TABLE Courses (
CourseId int(11) NOT NULL AUTO_INCREMENT,
CourseCode varchar(6) NOT NULL,
ShortName varchar(75) NOT NULL,
LongName varchar(150) DEFAULT NULL,
CourseDescription text,
PRIMARY KEY (CourseId),
INDEX CourseCode (CourseCode),
INDEX ShortName (ShortName)
);
CREATE TABLE Semesters (
SemesterId int(11) NOT NULL AUTO_INCREMENT,
SemesterNumber smallint(6) NOT NULL DEFAULT 1,
SemesterName varchar(12) NOT NULL,
SemesterYear year(4) NOT NULL,
StartDate date NOT NULL,
EndDate date NOT NULL,
PRIMARY KEY (SemesterId)
);
CREATE TABLE AccessLevels (
AccessLevelId int(11) NOT NULL AUTO_INCREMENT,
AccessLevelCode varchar(6) NOT NULL,
ShortName varchar(35) NOT NULL,
LongName varchar(75) DEFAULT NULL,
AccessLevelDescription varchar(1000) DEFAULT NULL,
PRIMARY KEY (AccessLevelId),
INDEX AccessLevelCode (AccessLevelCode),
INDEX ShortName (ShortName)
);
CREATE TABLE Users (
UserId int(11) NOT NULL AUTO_INCREMENT,
Login varchar(35) NOT NULL,
UserName varchar(50) NOT NULL,
Password varchar(50) NOT NULL,
Active smallint(6) NOT NULL DEFAULT '1',
AccessLevelId int(11) NOT NULL,
UserIdC int(11) NOT NULL,
PRIMARY KEY (UserId),
FOREIGN KEY AccessLevelId (AccessLevelId)
REFERENCES AccessLevels (AccessLevelId)
ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY UserIdC (UserIdC)
REFERENCES Users (UserId)
ON UPDATE CASCADE ON DELETE RESTRICT,
UNIQUE INDEX Login (Login)
);
CREATE TABLE Students (
StudentId int(11) NOT NULL AUTO_INCREMENT,
SSNumber varchar(11) NOT NULL,
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL,
MiddleName varchar(50) DEFAULT NULL,
Gender varchar(6) NOT NULL DEFAULT 'MALE',
DOB date NOT NULL,
Email varchar(75) NOT NULL,
Mobile varchar(14) DEFAULT NULL,
HTel varchar(14) NOT NULL,
AddressLine1 varchar(75) NOT NULL,
AddressLine2 varchar(75) DEFAULT NULL,
City varchar(50) NOT NULL,
State varchar(50) NOT NULL,
PostCode varchar(10) NOT NULL,
UserId int(11) NOT NULL,
PRIMARY KEY (StudentId),
FOREIGN KEY UserId (UserId)
REFERENCES Users (UserId)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX SSNumber (SSNumber),
INDEX FullName (LastName,FirstName),
INDEX DOB (DOB),
INDEX PostCode (PostCode)
);
CREATE TABLE Lecturers (
LecturerId int(11) NOT NULL AUTO_INCREMENT,
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL,
MiddleName varchar(50) DEFAULT NULL,
Gender varchar(6) NOT NULL DEFAULT 'MALE',
Email varchar(75) NOT NULL,
WTel varchar(14) DEFAULT NULL,
UserId int(11) NOT NULL,
PRIMARY KEY (LecturerId),
FOREIGN KEY UserId (UserId)
REFERENCES Users (UserId)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX FullName (LastName,FirstName)
);
CREATE TABLE LogEntries (
LogEntryId bigint(20) NOT NULL AUTO_INCREMENT,
LoggedOn datetime NOT NULL,
LoggedOff datetime DEFAULT NULL,
UserId int(11) NOT NULL,
PRIMARY KEY (LogEntryId),
FOREIGN KEY UserId (UserId)
REFERENCES Users (UserId)
ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE TABLE ScheduleOfClasses (
ScheduleId bigint(20) NOT NULL AUTO_INCREMENT,
ScheduleCode varchar(8) NOT NULL,
Section varchar(1) NOT NULL,
Day varchar(8) NOT NULL,
Time time NOT NULL,
Location varchar(75) DEFAULT NULL,
SemesterId int(11) NOT NULL,
CourseId int(11) NOT NULL,
LecturerId int(11) NOT NULL,
PRIMARY KEY (ScheduleId),
FOREIGN KEY SemesterId (SemesterId)
REFERENCES Semesters (SemesterId)
ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY CourseId (CourseId)
REFERENCES Courses (CourseId)
ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY LecturerId (LecturerId)
REFERENCES Lecturers (LecturerId)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX ScheduleCode (ScheduleCode)
);
CREATE TABLE CoursePrerequsites (
CoursePrereqId int(11) NOT NULL AUTO_INCREMENT,
MinPassGrade varchar(2) DEFAULT NULL,
CourseId int(11) NOT NULL,
CourseId1 int(11) NOT NULL,
PRIMARY KEY (CoursePrereqId),
FOREIGN KEY CourseId (CourseId)
REFERENCES Courses (CourseId)
ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY CourseId1 (CourseId1)
REFERENCES Courses (CourseId)
ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE TABLE CourseLecturers (
CourseLecturerId int(11) NOT NULL AUTO_INCREMENT,
CourseId int(11) NOT NULL,
LecturerId int(11) NOT NULL,
PRIMARY KEY (CourseLecturerId),
FOREIGN KEY CourseId (CourseId)
REFERENCES Courses (CourseId)
ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY LecturerId (LecturerId)
REFERENCES Lecturers (LecturerId)
ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE TABLE StudentScheduleOfClasses (
StudentScheduleId int(11) NOT NULL AUTO_INCREMENT,
RegisteredDate datetime NOT NULL,
StudentId int(11) NOT NULL,
ScheduleId bigint(20) NOT NULL,
PRIMARY KEY (StudentScheduleId),
FOREIGN KEY StudentId (StudentId)
REFERENCES Students (StudentId)
ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY ScheduleId (ScheduleId)
REFERENCES ScheduleOfClasses (ScheduleId)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX RegisteredDate (RegisteredDate)
);
| Other Case Studies |
|---|
| Case Study 1 Case Study 2 |
Follow