Database Fundamentals Project: College Database - BIT208, Sem 1

This assignment presents a comprehensive solution to a database fundamentals project focused on designing and building a relational database for a college. The solution includes an Entity-Relationship (ER) diagram illustrating the database structure, a dependency diagram clarifying relationships between tables, and the tables themselves with defined keys and primary data fields. The assignment further provides a form example and several SQL queries demonstrating data retrieval and manipulation. A sample report showcasing attendance data is also included. The project concludes with a bibliography of relevant academic sources, demonstrating a strong understanding of database concepts and best practices. This project covers key aspects of database design, data modeling, and query formulation in a practical, real-world context.

Running head: DATABASE FUNDAMENTAL ASSESSMENT
DATABASE FUNDAMENTAL ASSESSMENT
Name of the Student
Name of the University
Author Note

1DATABASE FUNDAMENTAL ASSESSMENT
Table of Contents
Entity relationship Diagram for the case study................................................................................2
Dependency diagram for the tables.................................................................................................4
Tables along with keys and primary data fields..............................................................................5
Form.................................................................................................................................................7
Queries.............................................................................................................................................7
Report..............................................................................................................................................9
Bibliography..................................................................................................................................10

2DATABASE FUNDAMENTAL ASSESSMENT
Entity relationship Diagram for the case study
Relational database
Module (Code_Module (pk), Title, Credits, Code_Degree (FK), DepartmentID(FK),
CordinatorID(FK))
Student_Enrollement (EnrollID(PK), Code_Degree (fk), Code_STD (FK), Enroll_date)
ClassesDetails (Code_Class(pk), Code_module(FK), Time_class)

3DATABASE FUNDAMENTAL ASSESSMENT
StudentData (Code_STD (PK), Name_Student, Student_Address, Age)
Attendance (Attendance (pk), Code_STD (fk), Code_Class (fk), Status)
Department (DepartmentID (pk), DepartmentName)
Unit_cordinator (CordinatorID (PK), Employee_ID (pk))
Lecture_data (Employee_ID(pk), Emp_name, Qualifictaions, Department ID (FK),
Emp_address, Emp_phno)
Degree ( Code_degree (pk), Duration, Title, Elective)

4DATABASE FUNDAMENTAL ASSESSMENT
Dependency diagram for the tables
The above diagram has been represented for the purpose of defining the decencies
between each attributes of the table. Kit becomes easy to understand the dependency with the
help of this diagram.

5DATABASE FUNDAMENTAL ASSESSMENT
Tables along with keys and primary data fields
The above screenshots are being provided for understanding the tables that are created for
the college. All the necessary data types are entered along with the major primary keys for the
tables.
Attendance
Class details

6DATABASE FUNDAMENTAL ASSESSMENT
Degree
Lecture data

7DATABASE FUNDAMENTAL ASSESSMENT
Form
The below screenshot is provided for understanding the form related to the attendance
table. The attendance attribute, Code_Class and status of attendance has been viewed in the
form.
Queries
SELECT Degre.Title, Count(Student_Enrollement.EnrollID) AS CountOfEnrollID
FROM StudentData INNER JOIN (Degre INNER JOIN Student_Enrollement ON
Degre.Code_Degree = Student_Enrollement.Code_Degree) ON StudentData.Code_STD =
Student_Enrollement.Code_STD

8DATABASE FUNDAMENTAL ASSESSMENT
GROUP BY Degre.Title;
SELECT StudentData.Name_Student, StudentData.Student_Address, StudentData.Code_STD,
StudentData.Age, Degre.Title, Degre.Elective
FROM Degre INNER JOIN (StudentData INNER JOIN Student_Enrollement ON
StudentData.Code_STD = Student_Enrollement.Code_STD) ON Degre.Code_Degree =
Student_Enrollement.Code_Degree;
SELECT ClassDetails.Code_module, ClassDetails.Time_class, StudentData.Name_Student,
Attendance.Status

9DATABASE FUNDAMENTAL ASSESSMENT
FROM StudentData INNER JOIN (ClassDetails INNER JOIN Attendance ON
ClassDetails.Code_Class = Attendance.Code_Class) ON StudentData.Code_STD =
Attendance.Code_STD;
Report
The below screenshot is used for representing the report for attendance table.

10DATABASE FUNDAMENTAL ASSESSMENT
Bibliography
Ahadi, A., Prior, J., Behbood, V. and Lister, R., 2015, June. A quantitative study of the relative
difficulty for novices of writing seven different types of SQL queries. In Proceedings of the
2015 ACM Conference on Innovation and Technology in Computer Science Education (pp. 201-
206). ACM.
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.
Btoush, E.S. and Hammad, M.M., 2015. Generating ER diagrams from requirement
specifications based on natural language processing. International Journal of Database Theory
and Application, 8(2), pp.61-70.
Cascetta, E., Carteni, A., Pagliara, F. and Montanino, M., 2015. A new look at planning and
designing transportation systems: A decision-making model based on cognitive rationality,
stakeholder engagement and quantitative methods. Transport policy, 38, pp.27-39.
Jukic, N., Vrbsky, S. and Nestorov, S., 2016. Database systems: Introduction to databases and
data warehouses. Prospect Press.
Romiszowski, A.J., 2016. Designing instructional systems: Decision making in course planning
and curriculum design. Routledge.