Relational Database Design and Implementation Project (DBFN212)

This project report details the design and implementation of a relational database. It begins with an Entity-Relationship Diagram (ERD) representing the system's entities and relationships. The ERD is then converted into a relational database schema. The report showcases the creation of a database using MS Access, including the design of tables, forms for data input and updates, and SQL queries for data retrieval. Screenshots of the database structure, table views, and form designs are included. The report includes the ERD, a relational view of the database, tables (Attendance, ClassDetails, Coordinator, Degree, Department, Enrollment, Lecturer, Student, Student_Module), a form example, and various SQL queries (SELECT, JOIN, and Aggregate). The project concludes with a summary of the database design and functionality, along with a bibliography of cited sources.

Running head: SYSTEM ANALYSIS AND DESIGNING
SYSTEM ANALYSIS AND DESIGNING- DBFN212/ BIT 208
Name of the Student
Name of the University
Author Note

1
SYSTEM ANALYSIS AND DESIGNING
Introduction:
The database is quite useful for storing and retrieving information from an online
environments. The database is actually an organized collection of data. These data are accessible
electronically from a specific type of computer system. In many of the cases database can be
become very much complex while the database will be using formal modelling and design
techniques.
Here, regarding the case scenario in this report an ERD will be established with having
all the entities and the relationships. This report will be also converting ERD to a relational table
set. In this aspect a new database will be also created and for the creation of database MS Access
will be utilized. Also, form will be created in this assignment for inputting and updating the data.
The above discussed tasks will be performed for this assignment.

2
SYSTEM ANALYSIS AND DESIGNING
ERD diagram based for the case study
In the above section diagram of the whole system has been presented. In the following
section of this report the ERD of is explained.
Relational Database
Degree (Deg_Code (PK), Time_Schedule, Elective_Subject, Degree_Title)
Student_Module (ID_Mod (PK), Cor_ID (FK), mod_title, Department_ID (FK), Deg_Code
(FK))

3
SYSTEM ANALYSIS AND DESIGNING
Department (DeptID (PK), Name_dept, Employee_ID (FK))
ClassDetails (Class_code (PK), ID_Mod (FK), Time_period)
Coordinator (Cor_ID (PK), Coordinator_Name, ID_Enroll (FK))
Enrollment_Details (ID_Enroll (PK), Deg_Code (FK), Student_code (FK), Date_enroll)
Attendance_Details (Overall_attend (PK), Student_code (FK), Class_code (FK))
Lecture Details (Employee_ID (PK), Name_employee, Degree, Dept_ID (FK), Address_Details,
Contactno_emp)
StudentDetails (Student_code (PK), Name, Address_std)
Relational view for the created queries
The abode diagrams is describing the relational view of the queries that has been created.
From the above diagram relationship among the various of classes for the database has been

4
SYSTEM ANALYSIS AND DESIGNING
described. The above diagram implies that attendance details is directly related with the class
details and student details. The degree is related with the student module and the enrollment
details. Department is related with the student module and lecture details. Student module is also
dependent on the class details and the coordinator.
Attendance_details table
The above diagram is about the table of attendance details. The leftmost column shows
the overall attend. The next right table shows the code of the students. The following column
shows the class code. In this column the user can put the values. Thus allotment of the class code
can be controlled by the user.
Class Details table

5
SYSTEM ANALYSIS AND DESIGNING
The above screenshot shows the details of the class. The left most column is consisting
the code of the classes. In the next column, ID module has been presented. Following that time
period has been shown for the classes in the next column.
Coordinator
Coordinator details has been shared in the above screenshot. In the left most column, ID
of the coordinators has been presented. In the next column right to that has presented the name of
the coordinators. In the following column, ID of the enrollment of the coordinators has been
presented.

6
SYSTEM ANALYSIS AND DESIGNING
Degree table
In the above image degree table has been presented thoroughly. In this table degree code
has been presented in the left most column. In the next column time scheduling has been
presented. The following column is presenting the elective subjects for the respective degree
codes. In the right most column title for the degree has been presented.
Department table

7
SYSTEM ANALYSIS AND DESIGNING
The details of the department table has been shown on the above screenshot. The left
most column is holding the values of the department ID and the next column is showing the
name of the department for the respective department name.
Enrollment details
The screenshot of the enrollment details has been shared on the above. The column at the
left most side is presenting the ID of the enrollment. In the next column the code of the degree is
has been presented. The student code is presented in the following column next to the student
code column. The date of enrollment is can be seen on the right most column in this table.
Lecturer details
The above screenshot is having the info regarding the lecturers. In the left most column
the ID of the employees has been presented and following that column at the right side name
column of the employees has been presented. Degree of the employees has been shared in the

8
SYSTEM ANALYSIS AND DESIGNING
next column. The next column is having the information of the department ID. The next two
columns is having address details and class details information respectively.
Student details
Details of the students has been shared in the above screenshot. Here in the first column
code of the student is presented and in the following columns name of the students and the
address of the students has been shared respectively.
Student_module
The above screenshot is regarding student module. First column is having the info of
student module ID. The next columns are consisting coordinator ID, title of the module, ID of
the Department and the code of the degree.

9
SYSTEM ANALYSIS AND DESIGNING
Form
The form of the lecturer details has been shared in the above screenshot. Here the users
need to fill up the employee ID, employee ID, degree of them, ID of the department, detailed
address and the contact no.
Report

10
SYSTEM ANALYSIS AND DESIGNING
Details of enrolled lecturers has been shown on the above screenshot. ID of the
employee, name of the employee, degree of the employee, department ID and the address details
has presented.
Select query
SELECT [Student Details].*
FROM [Student Details];
Join query
SELECT Cordinator.Cordinator_name, Student_module.mod_title, Degree.Elective_Subject,
Degree.Degree_title, Degree.Time_scheudle
FROM Degree INNER JOIN (Cordinator INNER JOIN Student_module ON Cordinator.Cor_ID
= Student_module.Cor_ID) ON Degree.Deg_code = Student_module.Deg_code;
Aggregate query
SELECT Cordinator.Cordinator_name, Count(Student_module.ID_Mod) AS CountOfID_Mod
FROM Cordinator INNER JOIN Student_module ON Cordinator.Cor_ID =
Student_module.Cor_ID

11
SYSTEM ANALYSIS AND DESIGNING
GROUP BY Cordinator.Cordinator_name;
Conclusion:
The above report has shown various aspects of the projected database for this scenario. In
this report first the ERD that has prepared on the case study has been described. In the following
section of this report relational view of the created queries has been shown. In this relational
view first the attendance details table has been presented and this table has been elaborated
carefully. The same has been also done for the other table of the overall database. The tables that
has been presented and elaborated in this report are the class detail table, coordinator table,
degree table, department table, enrollment table, lecturer table, student details table and the
student module table. In the following section of this report form for the registration has been
presented. In the report section the select query, join query and the aggregate query has been
presented.

12
SYSTEM ANALYSIS AND DESIGNING
Bibliography:
Al-Masree, H.K., 2015. Extracting Entity Relationship Diagram (ERD) from relational database
schema. International Journal of Database Theory and Application, 8(3), pp.15-26.
Connolly, T. and Begg, C., 2015. Database systems. Pearson Education UK.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Gessert, F., Wingerath, W., Friedrich, S. and Ritter, N., 2017. NoSQL database systems: a survey
and decision guidance. Computer Science-Research and Development, 32(3-4), pp.353-365.
Guagliardo, P. and Libkin, L., 2017. A formal semantics of SQL queries, its validation, and
applications. Proceedings of the VLDB Endowment, 11(1), pp.27-39.
Leis, V., Kundhikanjana, K., Kemper, A. and Neumann, T., 2015. Efficient processing of
window functions in analytical SQL queries. Proceedings of the VLDB Endowment, 8(10),
pp.1058-1069.
Mühlbauer, T., Rödiger, W., Kipf, A., Kemper, A. and Neumann, T., 2015, May. High-
performance main-memory database systems and modern virtualization: Friends or foes?.
In Proceedings of the Fourth Workshop on Data analytics in the Cloud (p. 4). ACM.
van de Put, B., Vaandrager, F.W. and Achten, P., 2018. Scoring Entity-Relationship Diagrams
Drawn by a Computer Algorithm.