$1.$ Modify the initial ER diagram presented in Figure B$\mathrm{.}19$ to include the following entity supertype and subtypes: The University Computer Lab USER may be a student or a faculty member.

$2.$ Using an ER diagram, illustrate how the change you made in Problem $1$ affects the relationship of the USER entity to the following entities:

a$.$ LOG

b$.$ RESERVATION c$.$ CHECK$\_$OUT d$.$ WITHDRAW

$3.$ Create the initial ER diagram for a car dealership. The dealership sells both new and used cars, and it operates a service facility. Base your design on the following business rules:

a$.$ A salesperson can sell many cars, but each car is sold by only one salesperson.

b$.$ A customer can buy many cars, but each car is sold to only one customer.

c$.$ A salesperson writes a single invoice for each car sold.

d$.$ A customer gets an invoice for each car $($s$)$he buys.

e$.$ A customer might come in only to have a car serviced; that is$,$ one need not buy a car to be classified as a customer.

f$.$ When a customer takes in one or more cars for repair or service, one service ticket is written for each car.

g$.$ The car dealership maintains a service history for each car serviced. The service records are referenced by the car$$s serial number.

h$.$ A car brought in for service can be worked on by many mechanics, and each mechanic can work on many cars.

i$.$ A car that is serviced may or may not need parts. $($For example, parts are not necessary to adjust a carburetor or to clean a fuel injector nozzle.$)$

$4.$ Create the initial ER diagram for a video rental shop. Use $($at least$)$ the following description of operations on which to base your business rules.

The video rental shop classifies movie titles according to their type: comedy, western, classical, science fiction, cartoon, action, musical, or new release. Each type contains many possible titles, and most titles within a type are available in mul$-$ tiple copies. For example, note the summary in the following table of the relationship between video rental type and title.

Keep the following conditions in mind as you design the video rental database:

$$ The movie type classification is standard; not all types are necessarily in stock.

$$ The movie list is updated as necessary; however, a movie on that list might not be ordered if the video shop owner decides that the movie is not desirable for some reason.

TYPE:

TITLE COPY

MUSICAL My Fair Lady,

My Fair Lady $,$

Oklahoma! $,$

Oklahoma! $,$

Oklahoma!

$1$

$2$

$1$

$2$

$3$

CARTOON Dilly Dally & Chit Chat Cat

Dilly Dally & Chit Chat Cat

Dilly Dally & Chit Chat Cat

$1$

$2$

$3$

ACTION Amazon Journey

Amazon Journey

$1$

$2$

The video rental shop does not necessarily order movies from all vendors on the vendor list; some vendors on the

vendor list are merely potential vendors from whom movies may be ordered in the future.

$$ Movies classified as new releases are reclassified to an appropriate type after they have been in stock for more than $30$ days. The video shop manager wants to have an end$-$of$-$period $($week$,$ month, year$)$ report for the number of rentals by type.

$$ If a customer requests a title, the clerk must be able to find it quickly. When a customer selects one or more titles, an invoice is written. Each invoice can contain charges for one or more titles. All customers pay in cash.

$$ When a customer checks out a title, a record is kept of the check$-$out date and time and the expected return date and time. When rented titles are returned, the clerk must be able to check quickly whether the return is late and to assess the appropriate late return fee.

$$ The video store owner wants to generate periodic revenue reports by title and by type. The owner also wants to generate periodic inventory reports and track titles on order.

$$ The video store owner, who employs two $($salaried$)$ full$-$time and three $($hourly$)$ part$-$time employees, wants to keep track of all employee work time and payroll data. Part$-$time employees must arrange entries in a work schedule, while all employees sign in and out on a work log$.$