Description

Total Marks: 60
Learning Goals
By the end of this assignment you should be able to:
• Design a conceptual model of a database (ER model)
• Improve upon an existing design using normalization
• Implement a given design using SQL DDL scripts
• Name your file as lastnameFirstnameA2.pdf.
Question 1: (ER Modeling) –UofG_Schema – 15 marks
You are asked to design an ER model for UofG_Schema, given the following requirements:
DATA REQUIREMENTS
Students: UofG keeps track of each student’s name, student number, social insurance number, current address and phone, permanent address and phone, birthdate, gender, major department, minor department (if any), and degree program (B.Sc, B.A.,…, Ph.D.). Note that some user applications need to refer to the city, state, and zip of the student’s permanent address, and to the student’s last name. Both social insurance number and student number have unique values for each student.
Course: Each course has a course name, description, course number, credit, level, and offering department (code). The value of course number is unique for each department. For example, Computer Science (CIS) and Psychology (PSY), both have a course 1500.
Grade Report: A grade report has a student, section, letter grade, and numeric grade (0, 1, 2, 3, 4 for F, D, C, B, A, respectively).

Professor: Each professor is described by his / her name, social insurance number, employee number, department to which they belong, building name, office number, phone extension, email.

TO DO:

a. (10 marks) Identify all entity types, their attributes and relationships in UofG_Schema and draw an ER model using them. Please state clearly any assumptions you make (in case a requirement is not clearly specified). You must indicate the keys and structural constraints for each relationship in your ER model.
b. (5 marks) Create / develop a requirement specification and add it to the list given above using
Question 2: (ER modeling and Normalization) – ICRU – 20 marks
You are asked to design an ER model for International Recruitment by Canadian Universities (IRCU), given the following requirements:
DATA REQUIREMENTS FOR IRCU:
Agents: Universities hire agents and each agent is described by a unique id, first name, last name, phone, email, city and country in which they reside and commission they get (in percentage).
Country: The database stores each country’s unique id and name. Note that only those countries that send students to Canadian Universities are stored in this database.
Here are some queries that might help you design the ER model. Note that this list is not exhaustive – it is given only to help you understand the requirements given above. You DO NOT need to submit these queries in SQL – they are given only for convenience.
a. List universities and the total number of agents hired by them.
c. List names of universities that offer all five degrees to international students.
f. List first and last names of agents who get max commission and their country names.
TO DO:

a. (10 marks) Identify all entity types, their attributes and relationships in IRCU schema and draw an ER model using them. Please state clearly any assumptions you make (in case a requirement is not clearly specified). You must indicate the keys and structural constraints for each relationship in your ER model.
b. (5 marks) Convert the ER model to a relational model, clearly identifying all primary and foreign keys.
c. (5 marks) Illustrate that the relations from 2b are in 3NF.
Question 3. (Normalization) – onLineExam – 15 marks
An excel file named onLineExamFirstNF.xlsx is posted for this question. The file shows a relation instance for an online exam system. It consists of entries for one exam numbered 13 and titled ‘GK’. Currently, in this database, there are 3 users that write the online exam for ‘GK’. This exam has 5 questions. Each question has 4 choices (‘A’, ‘B’, ‘C’ and ‘D’).
The given relation has several anomalies.
To DO:
(b) (5 marks) Using the functional dependencies identified in part (a), describe and illustrate the process of normalizing and decomposing onLineExam to a set of relations that are in 3NF.
(c) (5 marks) Repeat step (b) to decompose onLineExam to a set of relations that are in BCNF.
Question 4: (SQL DDL) – 10 marks:
a. Write and submit a DDL creation script to create the tables you design for question 2 (ICRU). Name it as create_icru.sql. Note that the tables must at least be in 3NF. Your script must include DDL commands to create each table with the primary and foreign key constraints.
b. Write and submit an insertion script that consists of statements to insert at least 5 tuples in each relation of ICRU. Name it as insert_icru.sql.