Draw an E-R diagram for a restaurant application that satisfies the following
constraints. Use only binary relationships and use no specialization or
aggregation. Then translate the E-R diagram into relational schemas.
(1) Each branch has a unique branch name, a location, a seating capacity, and a
set of contact phone numbers.
(2) Each employee has a unique employee ID, a name consisting of the first name,
the last name, and the middle initial, a title, a start date, a number of years
of employment, and a contact phone number.
(3) Each customer has a unique customer ID, a name, and a contact phone number.
(4) Each transaction has a transaction ID, a transaction time, an amount, and a
seat ID. The transaction IDs are unique for a given branch.
(5) Each dish has a unique dish ID, a name, and a price.
(6) Each branch has exactly one manager.
(7) Each employee works for at most one manager and manages zero or more
(8) Each customer has conducted at least one transaction; each transaction is
associated with exactly one customer.
(9) Each transaction contains zero or more dishes; a dish can be in any number
(10) Each transaction is served by exactly one employee; each employee can serve
zero or more transactions.
(11) Each transaction is served at exactly one branch; each branch has zero or
Note: Attributes are omitted in the E-R diagram.
The E-R diagram is translated into the following seven table schemas:
Branch(name, location, capacity, manager_ID)
Employee(employee_ID, firstname, lastname, middle_initial, title, startdate,
Customer(customer_ID, name, phone_number)
Transaction(branch_name, transaction_ID, time, amount, seat, costumer_ID,
Dish(dish_ID, name, price)
Transaction_dish(branch_name, transaction_ID, dish_ID)
E-R Diagram Example COMP231 Fall 2006 Page 1 of 1