1. Map the ER schema shown below into a relational schema. Specify all primary and foreign keys.

Customer(CustID, Address, City, State, Zip, Name, Phone, ReferrerID)

Bicycle(BicycleID, DatePurchased, Color, CustID, ModelNo)

BicycleModel(ModelNo, Manufacturer, Style)

Service(StartDate, BicycleID, EndDate)

Repair(RepairID, RepairName, PartsCost)

Included(RepairID, StartDate, BicycleID, LaborTime)

2. Draw an ER diagram containing the Candy and CandySize entity types connected by a 1:M identifying relationship between Candy and CandySize. CandySize is a weak entity. Choose an appropriate relationship name. Define minimum cardinalities so that a Candy must have at least one CandySize and a CandySize is for exactly one Candy. For the Candy entity type, add attributes CandyID (primary key), CandyName, and Manufacturer. For the CandySize entity type, add attributes SizeName (primary key) and Calories.

Then extend the ERD with the House entity type and a 1:M relationship named GivesAway between House and CandySize. A House will give away just one CandySize, but a CandySize can be given away by more than one House. Define minimum cardinalities so that a House need not have any CandySizes and a CandySize not need have any Houses. For the House entity type, add attributes HouseID (primary key), and Address.

Then extend the ERD with the Child entity type and a M:N relationship named Treats between Child and House. Assume that a Child need not get a treat from any Houses, and that a House need not give a treat to any Child. The Child entity type includes a ChildID (primary key), a Name, and a PhoneNumber. There is one attribute of the relationship named Quantity.

3. Draw an EER diagram that models a pharmacy selling prescription drugs. Make note of any assumptions you make.

Each customer has a name, phone, address, city, state, and zip.

Some customers have coverage for prescription drugs from an insurance company. Assume that a customer has at most one insurance company. Assume that an insurance company need not have any customers.

For each insurance company, we keep track of a name, a phone number, a co-pay, and an address.

For each drug, we keep track of the drugID, the scientific name of the drug, the common name of the drug, and the price.

Some drugs are restricted drugs. For these, we keep track of the maximum permitted dosage, and the agency that we must report to about prescriptions for this drug.

Some drugs are experimental drugs. For these, we keep track of the grantID and the researcher’s name.

A drug could be both restricted and experimental. A drug need not be either.

Drugs can have interactions with other drugs. We keep track of the nature of all known interactions between two drugs.

Insurance companies pay the pharmacy different amounts for each drug we sell. We must keep track of the reimbursement price that each insurance company pays us for each drug we sell.