Question 1: 10pts Design a database for the Brothers Bar and Grill, including information about the Menu, Customers, Staff, Schedule, Tables, and Orders. Information about the menu includes the item name, price, calories. A Customer sits at a Table and is assigned a Staff. An Order is a relationship among Staff, Customers, and Tables. A table can have multiple customers, but only one Order (no split checks). An Order can also include gratuity to the Staff. Schedules must be made so that there is enough Staff on each shift for each day.
Draw the E/R Diagram for this database. Be sure to include constraints and arity where appropriate.
Question 2: 10pts Modify your solution to Q1 as follows (if needed):
2a: Change your diagram so that a Table can have multiple Orders, but still only one Waiter.
2b: Change your diagram (from 2a) so that Table can have multiple Waiters and multiple Orders.
2c: Change your diagram (you can start form Q1 or 2a or 2b, please indicate which) to include a new Entity Set and Relationship of your choice. Describe this addition in your own words.
Question 3: 10pts One way to represent “Students” and the grades they get in “Courses” is to use entity sets corresponding to “Students”, “Courses”, and “Enrollments”. Enrollment entities form a connecting entity set between Students and Courses. Enrollment entities can be used to represent not only the fact that a student is taking a certain course, but the grade of the student in the course. Draw an E/R Diagram for this situation, indicating weak entity sets and the keys for the entity sets.
Question 4: 10pts Consider again the example from Q1/Q2 of Brothers Bar and Grill. Staff at a restaurant are typically broken down into their specialties: Cook, Waiter, Host, Manager. Reconsider your answer to Q2b and this time use sub-entity sets for Cook, Waiter, Host, and Manager. Redraw relationships in the diagram to make it more faithful to how you see a restaurant operating.
Hint: Remember that a sub-entity must always have a reason to exist, i.e., at least one attribute or relationship in it that is not in its parent.
Question 5: 5pts We only had time to talk about the standard (Chen) notation for ER Diagrams, but there exist many other ways to create ER Diagrams. Some are more expressive than others, but they basically do the same thing. Use your Google skills and read up on Crows foot notation. Convert your answer to Q4 into Crows Foot notation.
Question 6: 15pts Now let’s consider a banking domain. For the relations Accounts and Customers here:
| acctNo | type | balance |
|---|---|---|
| 12345 | savings | 12000 |
| 23456 | checking | 1000 |
| 34567 | savings | 25 |
| firstName | lastName | ID | account |
|---|---|---|---|
| Robbie | Banks | 901222 | 12345 |
| Linda | Hand | 805333 | 12345 |
| Linda | Hand | 805333 | 23456 |
6a: Suppose string attributes (type, firstName, lastName) are all defined as CHAR(15). How much disk space will the tuples in each relation occupy? Show your work.
6b: Suppose string attributes are all defined as VARCHAR(20). How much disk space will the tuples in each relation occupy? Show your work.
6c: When might we choose CHAR instead of VARCHAR? Why?
Question 7: 10pts In this exercise we introduce one of our running examples of a relational database schema. The database schema consists of four relations, whose schemas are:
Product (maker, model, type)
PC (model, speed, ram, hd, price)
Laptop (model, speed, ram, hd, screen, price)
Printer (model, color, type, price)
The Product relation gives the manufacturer, model number and type (PC, laptop or printer) of various products. We assume for convenience that model numbers are unique over all manufacturers and product types (that assumption is not realistic, and a real database would include a code for the manufacturer as part of the model number). The PC relation gives for each model number that is a PC the speed (of the processor, in GHz), the amount of RAM (in MB), the size of the hard disk (in GB), and the price. The Laptop relation is similar, except that the screen size (in inches) is also included. The Printer relation records for each printer model whether the printer products color output (true, if so), the process type (laser or ink-jet, etc), and the price.
Write SQL declarations for each relation.
Note: remember to determine primary keys for each relation
Question 8: 10pts (from Garcia-Molina, p165) Convert this E/R Diagram below to a relational database schema.
Question 9: 10pts (from Garcia-Molina, p165) This E/R Diagram below represents ships. Ships are said to be sisters if they were designed from the same plans. Convert this diagram to a relational database schema.
Question 10: 10pts Convert your answer to Q4 to a relational database schema.
Some exercises are from Garcia-Molina, Ullman, Widom. Database Systems. 2nd Edition.
This is an individual assignment. Discussion is allowed, but students may not write submitted solutions in the presence of a group.
Cite your sources.
Submissions must be uploaded to GradeScope and marked by the due date.