CIS3530 Assignment 2 Solution

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
Submission Instructions:
• Name your file as lastnameFirstnameA2.pdf.
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).



TO DO:

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:

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:
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.