Draw an E R diagram for a restaurant application

Document Sample
Draw an E R diagram for a restaurant application Powered By Docstoc
					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
employees.
(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
of transactions.
(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
more transactions.

                             Branch


                                           Employee


                             Xact
    Customer



                             Dish

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)
Branch_phonenumbers(branch_name, phone_number)
Employee(employee_ID, firstname, lastname, middle_initial, title, startdate,
phone_number, manager_ID)
Customer(customer_ID, name, phone_number)
Transaction(branch_name, transaction_ID, time, amount, seat, costumer_ID,
server_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