Assignment-2 Solution

Description

Important

• A clear, handwritten submission is acceptable. Ensure your answers are clear and legible.

• You must have a cover sheet at the front of your submission (with course, assignment #, your name, your student number).

• All sheets must be stapled together. The teaching team will not be responsible for missing pages or incomplete answers associated with unstapled submissions.

• Please show all work! (Some questions remind you to do so.)

• The assignment is in three parts, with total marks of 100.

• Submit your work by placing it into the ECS second-floor dropbox for CSC 370 before the due date/time. Late submissions will not be accepted.

Part A (20 marks)

Consider the relation R with four attributes ABCD. For each of the two following sets of FDs, determine whether or not the R would be in BCNF, and if it is not, then decompose it into BCNF, and prove the result would produce a lossless join.

1. → , → , →

2. → , → , → , →

Part B (20 marks)

Consider a relation Stocks( , , , , , )whose attributes may be treated as broker, office (of the broker), a client of the investor, a particular stock investment, the number of shares owned by the client, and the rate of return so far for the stock investment.

The set of FDs for this schema are → , → , → , → , and → .

1. What are all the keys for Stocks?

2. Are the given FDs already their own minimal basis? Explain.

2. Find a lossless-join, dependency-preserving decomposition of Stocks into 3NF relations.

3. Indicate and explain which relations, if any in the previous answer, are not in BCNF.

Part C (40 marks)

Consider the following details regarding a database to be maintained by some Canadian university.

• All professors have a SIN, a name, a year of the initial appointment as professor, a current rank, and the name of their home unit (i.e., department or school).

• Projects have a identity number, a funding-agency name (such as NSERC, NSC, SSERC, etc.), a date of project start, the number of months of funding, and the budget provided to the project.

• Graduate students have a SIN, a name, a year of the start of their graduate degree, and whether they are in a master’s program or a doctoral program.

• Each project is managed by one professor, who is also known as the PI (or “principal investigator”).

• Each project has one or more professors who participate in its work known as CIs (or “collaborating investigators”).

• Professors can manage multiple projects, or work on multiple projects, or both.

• Each project has one or more graduate students on work on that project (known as RAs or research assistants).

• When a grad student is involved in a project, a professor must supervise that student’s work on the project. Such an RA may work on multiple projects; therefore it is possible that the RA has a different supervisor on each project.

• Academic units have a four-letter unit code (e.g., “COSI” for Computer Science, “CHEM” for chemistry, “HEIS” for Healthy and Information Science etc.), a full unit name (e.g., “Department of Computer Science”, “Department of Chemistry”, “School of Health Information Science”), and a main office

location (building + room).

• Academic units have a professor who leads the unit (known as the “chair” or “director”).

• Professors may work in one or two units, and the percentage of time expected in each unit is known (i.e., 100% if one unit, 50%/50% or some other ratio for two units).

• Grad students have one main unit (department, school) that is considered their “home” unit.

Design and draw an ER diagram that captures the information described above about the university. Some marks will be given for the quality of your answer.

Part D (20 marks)

Consider the ER diagram shown below. Convert its design into a relational schema. Describe all assumptions you have made during conversion. Some marks will be given for the quality of your answer.

row seats

Bookings

toCust toFit

	Customers		Flights	aircraft
SIN	addr			day
	name	phone	number

Page 3 of 3