(total 24 points) Assume the following ``real-world'' environment concerning a
small supermarket, the Yappy Valley Culinary Boutique (YVCB). YVCB is organized into
departments, each of which has some employees. Items are sold by departments, and are
supplied by suppliers. Each supplier gives its own unique codes to the items it supplies. Also,
each supplier has exactly one contact person who is an employee.
Consider the relation
Primary keys are underlined. Each relation schema has one candidate key (therefore, it's the
primary key), it may be missing candidate (unique) keys and foreign keys.
Do the following (3 steps):
1. (8 points) Complete (i.e., reverse engineering) the ER diagram below such that the 4 relation
schemas above are exactly the
of a translation from the ERD to the relational model.
are NOT allowed to add any additional entity set.
, dName, budget)
d stands for department.
Gives the departments, their ID, name and
, dID, eName, eSalary)
e stands for employee
Gives the employees, their IDs, where they
work, their names and salaries.
, sName, sCity, sState, eID)
s stands for supplier.
Gives the suppliers, their IDs, their names,
and where they are located (city and state).
Also tells which employees are contact
persons for the suppliers.
, dID, iName, MSRP)
i stands for item, siCode stands for supplier-
assigned item code.
Gives the items and the departments that sell