，ER、SQL DDL。
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
- Even if you are working in a group, each member of the group must submit a copy of the assignment as a pdf file on moodle. Name it as lastnameFirstnameA2.pdf.
- Your assignment must be typed - handwritten assignments will not be marked. You may use any text editor of your choice to type in the answers.
- Your submission must include a list of group member names and userids.
Question 1: (ER Modeling) - UofG_Schema
You are asked to design an ER model for UofG, 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.
Department: Each department is described by a name, department code, office building, office number, office phone, faculty (e.g. Science, Engineering) and director. Note that director of a department is also a professor in this University. Both name and code have unique values for each department.
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.
Section: Each section has an instructor, semester, year, course, and section number. The section number distinguishes different sections of the same course that are taught during the same semester/year; its values are 1, 2, 3, …, up to the number of sections taught during each semester.
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.
Mentor: When students join the University, they are assigned to a faculty member (professor) who acts as his or her mentor. The mentor is responsible for monitoring the student’s welfare and academic progression throughout his or her time at UofG.
Buddy: UofG also runs a buddy program and keeps track of student buddies. When students joins UofG, they are assigned a returning student as a buddy to help them transition into the UofG lifestyle.
Family: Information on student’s family is stored which includes the name, relationship, address, and contact telephone number. A student may have more than one family member’s information stored in this database.
TO DO:
- a. 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. You must indicate the keys and structural constraints for each relationship in your ER model.
- b. Create / develop a requirement specification and add it to the list given above using
English sentences (you may add more requirements, if you need to). Add this specification to the ER model you designed in step 1a - it must include entity type(s), attribute(s) and relationship(s) (along with their structural constraints).
Question 2: (ER modeling)
DATA REQUIREMENTS FOR International Recruitment by Canadian Universities (IRCU):
There is an increasing trend in Canadian Universities today to recruit international students. As competition heats up, universities rely on paid, foreign-based recruiting agents to attract students. Agents work with both UG (undergraduate) and PG (post graduate or masters) curriculums. Draw an ER model, given the following requirements. You may have to make certain assumptions when designing the model - remember to state every assumption that you make and that is not listed below.
University: Universities accept students from different countries (e.g. India, China, US). They keep track of number of students they accept from every country in the database. Each University has a unique id, and is described by a unique name, city, state and its url. Every University offers various degree programs and hires several agents who are foreign-based.
Degree: Assume that that there are only 5 degrees currently in this database that accept international students (B.Sc., MAC, MBA, M.Engg and BAC). A degree may or may not be offered to international student by a University. For example, Guelph offers MAC, MBA and M.Engg. To international students, whereas Windsor offers all 5 degrees.Each degree has a unique id and a type (UG for undergrad or PG for Masters).
Department: The database keeps track of departments of every university. Each department has a name, number, office, phone and director’s name. Department number is unique within a given University. For example, department number 10 in Guelph is CS, whereas in Windsor, it is SW.
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.
- b. List all PG (Masters) degrees that University of Guelph offers to international students.
- c. List names of universities that offer all five degrees to international students.
- d. List all countries and total number of students that University of Guelph hires from?
- e. List all departments in University of Guelph that hire international students and name of their director (note that this database stores only such departments).
- f. List first and last names of agents who get max commission and their country names.
TO DO:
- a. 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. You must indicate the keys and structural constraints for each relationship in your ER model.
- b. Convert the ER model to a relational model, clearly identifying all primary and foreign keys.
Question 3. (Normalization)
Consider a database in which researchers submit their papers for consideration to a conference. Referee reviews are recorded as accepted or rejected. As shown in the table conference_review below, authors are described by their first name, last name and a unique email address. Each paper is identified by a unique id. Its title and the electronic filename containing the paper is stored. Each paper has 1 (and only 1) contact author. Referees who review conference papers are identified by their email address. Their first name, affiliated University and their review of each paper they review are also stored.
To DO:
- (a) Identify the functional dependencies represented by conference_review. You may draw an FD diagram or list the dependencies.
- (b) Using the functional dependencies identified in part (a), describe and illustrate the process of normalizing and decomposing conference_review to a set of relations that are in 3NF.
- (c) Repeat step (b) to decompose conference_review to a set of relations that are in BCNF.
Often, when designing databases and refining them to a certain acceptable normal form, requirements are not specified and described conveniently (as shown in questions 1 and 2). Nor are we given a table with all the information to begin the process of refinement (as given in question 3). In this question, you are given the following patient form from Wellington Hospital.
To DO:
- (a) Create an initial relation from the information given in the form. Call it patient_info.
- (b) Identify the functional dependencies represented by patient_info. You may draw an FD diagram or list the dependencies.
- (c) Using the functional dependencies identified in part (b), describe and illustrate the process of normalizing and decomposing patient_info to a set of relations that are in 3NF.
Question 5: (SQL DDL)
- a. Write a DDL creation script to create the tables you design in question 3. 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 2 more conference papers. Name it as insert_icru.sql.
Question 6: This question is ONLY for those who are working in a group with 3 members
Identify all entity types, their attributes and relationships from University of Guelph’s graduate course waiver form and draw an ER model using them. Please state clearly any assumptions you make. You must indicate the keys and structural constraints for each relationship in your ER model.