Relational Database Design and Implementation for Dominican College

This document details the design and implementation of a relational database system for Dominican College. The database encompasses various entities such as academic managers, administrators, lecturers, students, courses, units, and campuses. The document includes an ER diagram, normalized tables, form views, SQL queries, and reports. It also demonstrates the application of database tools and techniques to enhance the user interface.

DAD

Contents
LO2 Be able to design and implement relational database systems..........................................3
2.1 Design a relational database system to meet a given requirement:.............................3
2.2 Build a relational database system based on a prepared design:....................................11
2.3 Apply a range of database tools and techniques to enhance the user interface:............16
Figure 1: ER Diagram................................................................................................................4
Figure 2: Form view of Academic Manager..............................................................................6
Figure 3: Form view of campus.................................................................................................7
Figure 4: Form View of Course.................................................................................................7
Figure 5: Form view of Admin..................................................................................................7
Figure 6: Form View of Lecturer...............................................................................................8
Figure 7: Form View of Student................................................................................................8
Figure 8: Form View of Unit.....................................................................................................9
Figure 9: Query 1 of the Database.............................................................................................9
Figure 10: Query 2 of the Database...........................................................................................9
Figure 11: Query 3 of the Database...........................................................................................9
Figure 12: Query 4 of the Database...........................................................................................9
Figure 13: Query 4 of the Database.........................................................................................10
Figure 14: Query 5 of the Database.........................................................................................10
Figure 15: Report 1 of the Database........................................................................................10
Figure 16: Report 2 of the Database........................................................................................10
Figure 17: Report 3 of the Database........................................................................................11
Figure 18: Report 4 of the database.........................................................................................11
Figure 19: Report 5 of the Database........................................................................................11
Figure 20: Report 6 of the Database........................................................................................12
Figure 21: Design View of Academic manager.......................................................................13
Figure 22: Datasheet view of the Academic Manager.............................................................13
Figure 23: Design View of Admin...........................................................................................13
Figure 24: Datasheet view of the Admin.................................................................................13
Figure 25: Design View of Campus.........................................................................................14
Figure 26: Datasheet view of the Campus...............................................................................14
Figure 27: Design View of Course...........................................................................................14
Figure 28: Datasheet view of the Course.................................................................................14
Figure 29: Design View of Lecturer........................................................................................15
Figure 30: Datasheet view of the Lecturer...............................................................................15
Figure 31: Design View of Student..........................................................................................15
Figure 32: Datasheet view of the Student................................................................................15

Figure 33: Design View of Unit...............................................................................................16
Figure 34: Datasheet view of the Unit.....................................................................................16
Figure 35: Form view of Academic Manager..........................................................................18
Figure 36: Form view of campus.............................................................................................18
Figure 37: Form View of Course.............................................................................................19
Figure 38: Form view of Admin..............................................................................................19
Figure 39: Form View of Lecturer...........................................................................................19
Figure 40: Form View of Student............................................................................................20
Figure 41: Form View of Unit.................................................................................................20
Figure 42: Report 1 of the Database........................................................................................21
Figure 43: Report 2 of the Database........................................................................................21
Figure 44: Report 3 of the Database........................................................................................22
Figure 45: Report 4 of the database.........................................................................................22
Figure 46: Report 5 of the Database........................................................................................22
Figure 47: Report 6 of the Database........................................................................................23
Figure 48: Diagram of Relationship.........................................................................................23

LO2 Be able to design and implement relational database systems
2.1 Design a relational database system to meet a given requirement:
Data types are used to create the database. Data types contain the properties of the tables that
hold the records of the organization. Different types of data types are used to create the
database like int, float, chat, double and other. Here the database of the Dominican College is
represented with the different views of the database.
ER Diagram
Figure 1: ER Diagram
Normalization of the database:
First Normal Form:
Academic manager Admin
Manager_ID Admin_ID
Manager_Name Course_ID
Address Student_ID
Phone Campus
Email Campus_ID
Lecturer_Id Campus_Name
Unit_ID Lecturer
Course Lecturer_ID

Course_ID Unit_ID
Course_Fee Lecturer_Name
Course_Name Lecturer_Address
Campus_ID Lecturer_Email
Unit_ID Lecturer_Number
Student Unit
Student_ID Unit_ID
Student_Name Admin_ID
Student_Address Level
Student_Email Unit_Name
Student_Number
Course_ID
Second Normal Form:
Academic manager Admin Student
Manager_ID Admin_ID Student_ID
Manager_Name Course_ID Student_Name
Address Student_ID Student_Address
Phone Campus Student_Email
Email Campus_ID Student_Number
Lecturer_Id Campus_Name Course_ID
Unit_ID Lecturer Unit
Course Lecturer_ID Unit_ID
Course_ID Unit_ID Admin_ID
Course_Fee Lecturer_Name Level
Course_Name Lecturer_Address Unit_Name
Campus_ID Lecturer_Email
Unit_ID Lecturer_Number
Third Normal Form:
Academic manager Admin Lecturer Student
Manager_ID Admin_ID Lecturer_ID Student_ID

Manager_Name Course_ID Unit_ID Student_Name
Address Student_ID Lecturer_Name Student_Address
Phone Campus Lecturer_Address Student_Email
Email Campus_ID Lecturer_Email Student_Number
Lecturer_Id Campus_Name Lecturer_Number Course_ID
Unit_ID Unit
Course Unit_ID
Course_ID Admin_ID
Course_Fee Level
Course_Name Unit_Name
Campus_ID
Unit_ID
Here the forms and queries view of the database upload as follows:
Form View:
Figure 2: Form view of Academic Manager

Figure 3: Form view of campus
Figure 4: Form View of Course
Figure 5: Form view of Admin

Figure 6: Form View of Lecturer
Figure 7: Form View of Student

Figure 8: Form View of Unit
SQL view of the queries of the Database:
Figure 9: Query 1 of the Database
Figure 10: Query 2 of the Database
Figure 11: Query 3 of the Database
Figure 12: Query 4 of the Database

Figure 13: Query 4 of the Database
Figure 14: Query 5 of the Database
Reports:
Figure 15: Report 1 of the Database
Figure 16: Report 2 of the Database

Figure 17: Report 3 of the Database
Figure 18: Report 4 of the database
Figure 19: Report 5 of the Database

Figure 20: Report 6 of the Database

2.2 Build a relational database system based on a prepared design:
Represent the table view of the database of different tables as follows:
Academic Manager:
 Design View:
Figure 21: Design View of Academic manager
 Datasheet view:
Figure 22: Datasheet view of the Academic Manager
Admin:
 Design View:
Figure 23: Design View of Admin
 Datasheet view:
Figure 24: Datasheet view of the Admin

Campus:
 Design View:
Figure 25: Design View of Campus
 Datasheet view:
Figure 26: Datasheet view of the Campus
Course:
 Design View:
Figure 27: Design View of Course
 Datasheet view:
Figure 28: Datasheet view of the Course

Lecturer:
 Design View:
Figure 29: Design View of Lecturer
 Datasheet view:
Figure 30: Datasheet view of the Lecturer
Student:
 Design View:
Figure 31: Design View of Student
 Datasheet view:
Figure 32: Datasheet view of the Student

Unit:
 Design View:
Figure 33: Design View of Unit
 Datasheet view:
Figure 34: Datasheet view of the Unit
Data Dictionary:
Table Name Attribute Data type Primary
Key/
Foreign
Key
Academic
Manager
Manager_ID Number PK
Manager_Name Text
Address Text
Phone Number
Email Text
Lecturer_ID Number FK
Unit_ID Number FK
Campus Campus_ID Number PK
Campus_Name Text
Admin Admin_ID Number PK
Course_ID Number
Student_ID Number
Lecturer Lecturer_ID Number PK
Unit_ID Number
Lecturer_Name Text
Lecturer_Address Text
Lecturer_Email Text
Lecturer_Number Number
Course Course_ID Number PK
Course_Fee Currency

Course_Name Text
Campus_ID Number FK
Unit_ID Number FK
Student Student_ID Number PK
Student_Name Text
Student_Address Text
Student_Email Text
Student_Number Number
Course_ID Number FK
Units Unit_ID Number PK
Admin_ID Number
Level Number
Unit_Name Text

2.3 Apply a range of database tools and techniques to enhance the user
interface:
Form:
Figure 35: Form view of Academic Manager
Figure 36: Form view of campus

Figure 37: Form View of Course
Figure 38: Form view of Admin
Figure 39: Form View of Lecturer

Figure 40: Form View of Student
Figure 41: Form View of Unit

Report:
Figure 42: Report 1 of the Database
Figure 43: Report 2 of the Database

Figure 44: Report 3 of the Database
Figure 45: Report 4 of the database
Figure 46: Report 5 of the Database

Figure 47: Report 6 of the Database
Relationship Diagram
Figure 48: Diagram of Relationship

Reference
 Embley, D. and Mok, W. (2011). Mapping Conceptual Models to Database
Schemas. Handbook of Conceptual Modeling, pp.123-163.
 Sperko, R. (2003). Understanding Relational Database Management Systems. Java
Persistence for Relational Databases, pp.17-33.
 Weik, M. (2000). normalization. Computer Science and Communications Dictionary,
pp.1115-1115.