MSIT 630 Database Systems (Winter, 2018)
Total: 50 points
Due: 3/11/2018 11:59PM
1. Consider the relation, r, shown below. Give the result of the following query: (4 points)
Building room_number time_slot_id course_i
...
MSIT 630 Database Systems (Winter, 2018)
Total: 50 points
Due: 3/11/2018 11:59PM
1. Consider the relation, r, shown below. Give the result of the following query: (4 points)
Building room_number time_slot_id course_id sec_id
SELECT building, room_number, time_slot_id, count(*)
FROM r
GROUP BY ROLLUP (building, room_number, time_slot_id)
In MySQL, use:
SELECT building, room_number, time_slot_id, count(*)
FROM r
GROUP BY building, room_number, time_slot_id with rollup;
2. Consider an employee database with two relations
employee(employee-name, street, city)
works(employee-name, company-name, salary)
where the primary keys are underlined. Write a query to find companies whose employees earn a
lower salary, on average, than the average salary at “First Bank Corporation”. Use user defined
SQL functions (create function command) as appropriate to answer the above query, the
function takes the company name as the input and returns the average salary of the given
company. (6 points)
page 1271) (16 points, 4 points each)
a. Find the names of all students who have taken at least one Comp. Sci. course.
b. Find the IDs and names of all students who have not taken any course offering
before Spring 2009.
Ans: π ID, name (student) - π ID, name (σ year < 2009 (student ⋈ takes ))
Note the MySQL equivalent of the above relational algebra:
c. For each department, find the maximum salary of instructors in that department.
You may assume that every department has at least one instructor.
Note the MySQL equivalent of the above relational algebra:
d. Find the lowest, across all departments, of the per-department maximum salary
computed by the preceding query.
Ans: Gmin(max_salary)(dept_name Gmax(salary) ρ max_salary(Instructor))
Note the MySQL equivalent of the above relational algebra:
4. Construct an E-R diagram for a hospital with a set of patients and a set of medical
doctors. Associate with each patient a log of the various tests and examinations conducted.
(6 points)
5. Explain the distinction between disjoint and overlapping constraints. Provide an example for
each constraint. (3 points)
6. Explain the distinction between total and partial constraints. Provide an example for each
constraint. (3 points)
Ans: Total constraints is explained by an entity set E in a relationship set R, where every entity
in E participates in at least one relation in R. For example, the expectation is that every
student entity is expected to be related to at least one instructor through the advisor
relationship.
shared via CourseHero.com
Partial constraint is explained by an entity set E in a relationship set R, where only some
entities in E participates in relationships in R. For example, an instructor need not advice
any students. Hence, it is possible that only some of the instructor entities are related to
the student entity set through the advisor relationship.
7. Consider the following set F of functional dependencies on the relation schema
r(A,B,C,D,E,F): (12 points, 3 points each.)
A→BCD
BC→DE
B→D
D→A
a. Compute B+.
Iteration Using Result
1 B
2 B→D BD
3 D→A ABD
4 A→BCD ABCD
5 BC→DE ABCDE
Ans: B+ = {ABCDE}
b. Compute D+.
Iteration Using Result
1 D
2 D→A AD
3 A→BCD ABCD
4 BC→DE ABCDE
Ans: D+ = ABCDE
c. Prove (using Armstrong’s axioms) that AF is a superkey.
Ans: Armstrong’s first axiom rule reflexivity states that an attribute determines itself.
As we can logically imply that:
AF→AF;
By composition rule, if:
A→BCD and AF holds, then AF→ABCDF by means of augmentation axiom;
By the transitivity rule, if:
BC→DE then it must imply, that AF→ABCDEF
Because the attribute AF→ABCDEF, it is a superkey for the relation R.
https://www.coursehero.com/file/31526131/MSIT630-Winter18-Assignment2pdf/
This study resource was
shared via CourseHero.com
d. Compute a canonical cover for the above set of functional dependencies F; give each step
of your derivation with an explanation.
Ans: 1. D is extraneous in BC→DE because D ∈ BC→DE and {A→BCD, BC→DE,
B→D, D→A} logically implies {A→BCD, BC→E, B→D, D→A. This is
because every functional dependencies (FD) in the 1st set is found in the second
set BC→E, as shown. This FD can be derived using Armstrong’s
pseudotransitivity axiom rule which states: if α→β holds and γ β → δ holds,
then α γ→ δ holds.
2. D is also extraneous in A→BCD because D ∈ A→BCD and {A→BCD,
BC→E, B→D, D→A} logically implies {A→BC, BC→E, B→D, D→A. This is
because:
1. A→BC decompose
2. BC→E given
So, remove D from A→BC.
F = {A→BC, BC→E, B→D, D→A}
3. C is extraneous in BC→E because C ∈ BC→E and {A→BC, BC→E, B→D,
D→A} logically implies {A→BC, B→E, B→D, D→A. This is because:
1. A→BC decompose
2. B→E transitivity
3. B→D given
4. D→A given
Knowing that B+ is equivalent to that of ABCD, FD B→E could be resolved from
this equation set. As a result, the attribute C is extraneous in the second
dependence. Eliminating this attribute and using the transitivity rule on the first
FD will result in the following:
1. A→B
2. A→C
3. B→D
4. D→A
[Show More]
![Preview of THE TEFL ACADEMY LEVEL 5 - ASSIGNMENT C - AUTHENTIC MATERIAL [2026]](https://browseimages.nyc3.digitaloceanspaces.com/paper-images/2026/04/10/HpVkRcWV2026-04-10-11-5769d96426bd64f.png)
