Question 1

Consider the following entities with associated attributes (shown capitalized):

• A supplier with a unique name, SNAME, sells an item at a PRICE and holds a QUANTITY in its inventory
• A customer with unique name, CNAME, has its purchases shipped to an ADDRESS
• An item has a MANUFACTURER with a PARTNUMBER
• An order with a unique ORDERNUMBER is placed by a customer to a supplier on a DATE for a QUANTITY of items

a) Draw an E-R diagram representing the entities and relations described. Specify keys for each entity.

b) Suppose that every item is supplied by one or more suppliers. What does this constraint imply about the participation of entities in the relations of your diagram?

c) Design a relational schema for your E-R diagram, and provide "CREATE TABLE" SQL commands to implement it, and its constraints.

Question 2

Sloppy Joe’s Sloppy Joes, a Chapel Hill restaurant, wants to build a database to keep track of their inventory.  Their inventory consists of ingredients, which have a name and unit of measure (pound, gallon, etc.)  The ingredients are bought from suppliers, who have a name and address.  Each supplier may carry a different selection of ingredients; the database should model this.  Some of these suppliers are large corporations, so Sloppy Joe may have multiple contacts within each supplier.  Contacts have a name, phone number, and role (for instance, sales or billing).  Finally, the database should store the orders that Sloppy Joe has made from each supplier.  Each order is for a single ingredient, and has an id, order date, delivery date, price, and number of units.

Construct an ER diagram for Sloppy Joe’s database.  Document all assumptions you make about the mapping constraints.

Question 3

Consider the following ER Diagram, which models an employee database and a customer database.

a)     List the entity sets and their primary keys.

b)    Show how you would use an is-a hierarchy to combine these two databases, by factoring out a superclass of Employees and Customers.  Note that an employee can also be a customer.  Describe any overlap or covering constraints.

Question 4

Use the following ER diagram for parts (a) and (b).

a)     Write the SQL statements to create a relational database schema that models the diagram.  Suppose that the Teaches relationship is many-many: a professor may teach any number of classes, and multiple professors may collaborate to teach the same class.

b)    Write the SQL statements to create a relational database schema that models the diagram.  This time, suppose that the Teaches relationship is 1-many: professor may teach any number of classes, but each class only has one professor teaching it.

c)     Write the SQL statements to create a relational database schema for the following diagram.

Question 5

Using Python and SQLite to create the two relations given in Figure 3.19 and 3.20 from the textbook (pg. 90). Populate them with the data shown and construct the view specified on page 90, and verify that its contents match those shown in Figure 3.21. Turn in a listing of your code and the output of "SELECT * FROM ActiveStudents".