Question

Using draw.io diagram tool, design a database schema for an application that tracks and manages students’ applications for a university. Make sure to create proper primary key for each entity. Add relations and specify cardinalities.

• All entities and attributes must be named using the underscore convention. Highlight primary keys in red
• List any assumptions you have made

• Use diamonds and the association on the arrow.

• THINGS TO KEEP IN MIND :

• The university needs to keep track of students’ application. The application contains information about student: name, address, phone number, DOB, SSN, and generate a unique ID for each student.
• Education records has to be submitted as well. The education records contain information about previous degrees, GPA, name of school attended and dates ( from – to dates)
• Test scores are also submitted along with applications. The student can submit more than one test score. The test score consist of test type, date and score.
• The student should also specify which major they are applying to, for what term
• The university assign one employee from the admission office to process the application. The employee will handle multiple application at the same time.
• Applications can be submitted to different departments.

Answer 1

Based on the information provided, there are five tables created

• Students
• Education records
• Admission Form(only one field provided, given it is not mentioned what all the form will contain)
• University
• Course
• Department

Below are the few assumptions made

• Students can have one to many relationship with education records and test scores table
• Student can have one to one relationship with admission form
• Admission form can have one to many relationship with Departments
• University can have one to many relationship
• A student has one to one relationship with University and Admission Form

Below is the diagram of Database Schema