Contact

 fidel@fidelcaptain.com
  +1 284 542 4404
  +1 284 542 4404

SiteLock

Follow

Case Studies
Three case studies are used to guide the reader through the six steps outlined in the Six-Step Relational Database Design™ book, each increasing in complexity. Each case study begins with a real world scenario and takes the user through the E-R and R-M diagrams, ending with executable SQL commands to implement the derived designs.

 

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.

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

Case Study 3 simplified E-R diagram

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:

Case Study 3 detailed E-R diagram

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:

Case Study 3 Relational Model

Case Study 3 Relational Model

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