CSC 370 Database Systems Assignment 2


5/5 - (2 votes)

CSC 370
Database Systems
Assignment 2

• 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
• 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
The set of FDs for this schema are � → �, � → �, � → �, �� → �, and � → �.
1. What are all the keys for Stocks?
Page 2 of 3
2. Are the given FDs already their own minimal basis? Explain.
3. Find a lossless-join, dependency-preserving decomposition of Stocks into
3NF relations.
4. Indicate and explain which relations, if any in the previous answer, are not in
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
• 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
Page 3 of 3
location (building + room).
• Academic units have a professor who leads the unit (known as the “chair” or
• 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.
toCust toFit
Customers Flights
phone number

PlaceholderCSC 370 Database Systems Assignment 2
Open chat
Need help?
Can we help?