University Database using Microsoft Access

A relational database made in Access which is used for structuring randomly generated data for a university system.

The process for making this database is described as follows:

• Created relationships and constraints using an Entity Relationship Diagram.
• Created a relational schema to meet the business needs of the theoretical university.
• Developed the entity relationship diagram and relational schema in LaTeX using the TiKZ er package.
• Implemented the entity relationship diagram and relational schema in Microsoft Access.
• Manually added randomly generated data into the database.
• Developed SQL queries to fetch data concerning student's major and minor (LEFT JOIN), all the pre-requisites offered by the university (LEFT JOIN), courses including sections professors are teaching and a specific courses students majoring in CS are taking using AND and WHERE clause, use aggregate function COUNT, GROUP BY, and ORDER BY to create table count of all the students and their majors at the college.

The entity relationship diagram and the relational schema are developed in LaTeX. I cannot share the code for the ERD because I by mistake over-wrote the original code.

The entity relationship diagram for the project as shown in Fig. 1.

Figure 1: Illustration of ERD for a University database coded in the LaTeX document scripting language.

The relational schema for the project as shown in Fig. 2.

Figure 2: Illustration of Relational Schema for a University database coded in the LaTeX document scripting language.

The fact and dimension table implemented in Microsoft Access, Fig. 3.

Figure 3: Relationships tab view in Access for the database.

Here are 5 SQL queries I ran on the data.

Query #1: We want the name of the student, their major and their minor.

SELECT student.ssn, student.fname AS [first name], student.lname AS [last name], minor.code AS minor, major.code AS major
FROM (Student LEFT JOIN Minor ON student.ssn = minor.ssn) LEFT JOIN major ON student.ssn = major.ssn;

Query #2: Which professor is teaching what course and section e.g., CS5000-080 in the Fall semester.

SELECT ssn, iname AS name, course.number & '-' & section.number AS [course-section]
FROM Instructor, Course, [Section], Teaches
WHERE ssn = issn 
AND course.number = section.cnumber 
AND teaches.number = section.number
AND section.semester = 'Fall';

Query #3: Table of computer science majoring students that are enrolled in CS4400 including their degree and class rank.

SELECT student.ssn AS ssn, fname AS [first name], lname AS [last name], program AS [degree program], class, code AS major
FROM Student, Enrolls, Major, Course, [Section]
WHERE student.ssn = major.ssn
AND student.ssn = enrolls.ssn
AND enrolls.snumber = section.number
AND course.number = cnumber
AND cnumber = '4400'
AND dcode = 'CS';

Query #4: List of pre-requisites for all courses offered in the university.

SELECT Course.number AS [course number], Course.coursename AS [course name], Course_Prerequisite.prerequisite AS [pre-requisite], Course.offering_dept AS [department name]
FROM Course LEFT JOIN Course_Prerequisite ON Course.number = Course_Prerequisite.Number;

Query #5: A count of the students that are majoring in each major offered by the college.

SELECT count(student.ssn) AS students, major.code AS major
FROM Student, Major
WHERE student.ssn = major.ssn
GROUP BY major.code
ORDER BY count(student.ssn);