Draw an ER diagram that captures the preceding information
1 ) Consider the following schema (keys are in Bold):
Product(pid, name, type, manufacturer, price), Buys(cid, pid), Customer(cid, cname, age, gender)
a) Write the following query in relational algebra. Find the names of all customers who have
purchased all products manufactured by Sears.
b) Write the following query in SQL. Find the names of all customers who have not purchased the
most expensive product
c) Write the following query in SQL. Find the names and cids of all customers who have purchased
the second most expensive product. You can assume that no two products have the same prices.
2) The relation R(A,B,C,D,E) has the following functional dependencies.
A, B C
B, C D
C, D E
D, E A
A) What are all of the keys of R?
B) Find a lossless-join decomposition of R into BCNF relations.
c) Find a lossless-join decomposition of R into 3NF relations
3)Consider the following table Xbox_Games(name, price) and assume that these values already
exist in the database (‘ok_game’, 40), (‘good_game’, 50), (‘AWESOME_game’, 60). We have the
following two transactions:
T1: BEGIN TRANSACTION
S1: UPDATE Xbox_Games SET price=22 WHERE name =’ ok_game’
S2: INSERT INTO Xbox_Games VALUES (‘Another_Game’, 0)
S3: UPDATE Xbox_Games SET price=38 WHERE name =’ ok_game’
COMMIT
T2: BEGIN TRANSACTION
sS: SELECT AVG{price} AS average_price FROM Xbox_Games
COMMIT;
Above two transactions are hitting the DBMS roughly at the same time. What are the possible values
for average_prices?
4)Each pharmaceutical company is identified by name and has a phone number.
For each drug, the trade name and formula must be recorded. Each drug is sold by a given
pharmaceutical company, and the trade name identifies a drug uniquely from among the
products of that company. If a pharmaceutical company is deleted, you need not keep track
of its products any longer.
Each pharmacy has a name, address, and phone number.
Each pharmacy sells several drugs and has a price for each. A drug could be sold at several
pharmacies, and the price could vary from one pharmacy to another.
Pharmaceutical companies have long-term contracts with pharmacies. A pharmaceutical
company can contract with several pharmacies, and a pharmacy can contract with several
pharmaceutical companies. For each contract, you have to store a start date, an end date,
and the text of the contract.
Pharmacies appoint a supervisor for each contract. There must always be a supervisor for
each contract, but the contract supervisor can change over the lifetime of the contract.
1. Draw an ER diagram that captures the preceding information. Identify any
constraints not captured by the ER diagram.
2. How would your design change if each drug must be sold at a fixed price by all
pharmacies?
5)Assume we have two relations R(A,B) and S(B,C). All three attributes (A, B and C) are
integer attributes. Assume that Relation R contains the following tuples: (1,2)
Assume we have two relations R(A, B) and S(B, C). All three attributes (A, B and C) are
integer attributes. Assume that Relation R contains the following tuples: (1, 2), (2, 3), and (3,
4). Assume that relation S contains the following tuples (2, 2), (2, 3), and (5, 1). Recall that a
key is a minimal superkey, and that a key is not a superkey.
1. Give an example of an attribute (combination) that cannot be a key for relation
S.
2. How many tuples are in the result of the cross-product between R and S?
3. How many tuples are in the result of the following relational algebra expression
πA{R natural Join S}