代写数据库作业，完成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.
Question 4 (Normalization): Wellington Hospital InPatient Form
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.
