Pat's Bike Rentals

1.Look at the E-R diagram below, then answer the questions.

a.In words, list all the entities you identify from the diagram

b.In words, list all the attributes (organize your list by entity) you identify from the diagram

c.In words, list all the primary keys and which entity they belong to

d.Fill in the blanks to identify the minimum and maximum cardinalities in each direction:

i.A customer may rent a minimum of ____ bikes and a maximum of ____ bikes

ii.A bike may be rented by a minimum of ____ customers and a maximum of ____ customers

2.Redraw the diagram from Q1 to include a new entity called Rental Contract based on the following information. Important: we will assume a rental contract can contain only one single bicycle, rented to one customer, on one occasion. If a customer rents three bikes at the same time (e.g. for a family), there would be three separate rental contracts.

a.Over its lifetime, a bike may have many rental contracts. However, when the bike is new, it will not have had any rental contracts

b.A customer may have one rental contract or many rental contracts

c.Add two attributes to the rental contract entity that you think would be useful

3.Pat realizes a she needs another entity, repair ticket. A repair ticket describes the repair work that was done on one bike on one occasion. A bike can have zero, or many, repair tickets.

a.Create new E-R diagram (based on your diagram from Q2) to include the new entity, and draw its relationship to another entity on the diagram (you must decide which entity this should be)

b.Indicate the minimum and maximum cardinalities on each end of the relationship line using crow's foot notation

c.Create and identify the primary key for repair ticket

d.Add two more attributes for the repair ticket that you think would be useful

4.Create simulated table of data to represent the repair ticket entity in a relational database. You may create the table using Word, Excel, or any other program, or you may draw the table on paper.

a.Your table must have a name for each column, and must have one row of data. You may make up any values you wish for the data, as long as they make sense for the attributes you have included. E.g. if the attribute is Repair Date, do not suggest that the data example is "blue".

b.Based on your answer in #3, if a foreign key is necessary, ensure that it is part of the table.

Example of a simulated data table, using the Bike entity:

BikeID

Make

Model

Colour

MB1001

Norco

Bigfoot

Red