Relational Database Design & Implementation for Dominican College

This project details the design and implementation of a relational database system for Dominican College. It covers the design of the database using an ER diagram and normalization techniques (1NF, 2NF, 3NF). The implementation includes creating tables with appropriate data types, primary keys, and foreign keys. The project also presents different views of the database, including form views for Academic Manager, Campus, Course, Admin, Lecturer, Student, and Unit tables. SQL queries are used to retrieve specific data from the database, and reports are generated to present the data in a user-friendly format. The project further illustrates the design and datasheet views of each table and enhances the user interface through forms and reports, concluding with a relationship diagram and references.

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