The Les Rouleurs Bike Company

The Les Rouleurs Bike Company (Est. 2009) build high quality Bicycles for discerning customers. The company previously kept its records in a flat file Java database applet written by an intern. However, the steam driven system ran into memory issues after 15,000 records so a replacement was commissioned.

The replacement system will be created using a traditional relational database system. You have been called in as a contractor (Quis Custodiet Ipsos Custodes) to design the database. Your first task is to create the conceptual design which will be a conceptual entity relationship diagram (ERD).

You will take the information presented herein and represent it as a set of entity types and their primary keys and some major attributes and the relationships between the entity types (not instances).

The following narrative was provided by the CEO Maurice Garin.

We are very much a customer focussed company. A Customer makes an Order for one or more Bikes. A Customer over the years may make many Orders and each Order is associated with a single Customer.

Each Order may be for one or several Bikes, because each Bike is individually hand assembled and hand painted they are all unique so each individual Bike is associated with exactly one Order.

Although they are hand built they are built from industry standard parts. Each Bike consists of 3 main component sets: A Drivetrain (Crankset, Pedals. Cassette, Shifters and Brakes) . A Wheelset (Front and Rear wheels) and a Frameset (Frame, Bottom Bracket, Seat post, Saddle, forks, headset and bars.)

A particular Drivetrain such as Shimano Dura-Ace Di2 will be used in many Bikes, but each Bike of course just has one Drivetrain. Drivetrains are supplied by specialist Drivetrain_Suppliers. Each Drivetrain_Supplier may supply us with many Drivetrains but each Drivetrain has just one Drivetrain_Supplier.

A specific Frameset such as the Ritte Snob will be used in many Bikes, but each Bike of course just has one Frameset. Frameset are supplied by specialist Frameset_Suppliers. Each Frameset_Supplier may supply us with many Framesets but each Frameset has just one Frameset_Supplier.

A specific Wheelset such as the Campagnolo Bora WTO 33 will be used in many Bikes, but each Bike of course just has one Wheelset. Wheelsets are supplied by specialist Wheelset_Suppliers. Each Wheelset_Supplier may supply us with many Wheelsets but each Wheelset has just one Wheelset_Supplier.

Bikes as mentioned earlier are hand built by a team of highly skilled Builders. It may take several Builders to build a single Bike and of course each Builder will build many Bikes.

Painting is a different matter each Bike is assigned to a single Painter based on the complexity of the design , only Terry can do the "Wreck of the Hesperus" with any deal of skill. A good Painter might complete 10 moderately complex paint jobs per month.

Of course we are a business so the vulgar topic of monies needs to be addresse. When an Order is placed it generates an Invoice, each Order creates just one Invoice and each invoice refers to just one Order. The Customer receives the Invoice. A Customer who makes many Orders will naturally receive many Invoices and each invoice is sent to the Customer who made the Order.

Invoices need Payment. Since the Bikes are quite pricey we allow Customers to pay invoices in installments , so each Invoice might have multiple Payments but each Payment is associated with just one Invoice.

Customers make Payments, as Customers may receive many Invoices they may make many Payments but each Payment comes from a single Customer.

Part One

You will create a conceptual ERD. The ERD will show a rectangle for each entity type which will be split in two. The top half will include the Entity type name as a singular noun so Bike not Bikes (points will be deducted for every mistake) .

The bottom half will list the attribute(s) used to create the primary key (guarantee uniqueness for each instance). As the CEO has given you few clues you may exercise your creativity, if you can find a natural set of attributes that guarantee uniqueness use them if not define your own primary key.

Relationships between entities will be shown by a line with a relationship name and an arrow

pointing towards the "possessed" or object entity for instance

Remember your English constructions Subject............Verb...............Object

Staff Manages Branch

The above is a one to one relationship where a Staff member as a Manager manages 0 or 1 Branch and each Branch is managed by one and one only Staff member. The above makes sense.

If two entity types as above are in a one to one relationship then the narrative will give you help for instance in the above a Staff member manages a Branch, the Branch does not manage the Staff member so the version below makes no sense

If you wanted the relationship to point from Branch to Staff you would use the passive voice such as "Is_managed_by" as below

The cardinality and participation must be shown on both ends of the line. Points will be deducted for failing to do so.

In the above the Branch has Mandatory participation, Branch must be managed by a Staff member, (minimum 1). Branch has a cardinality of one with Staff, Branch can be managed by a maximum of one Staff member

Staff has optional participation in the manages relationship , a Staff member (most in fact) may manage zero Branches. The cardinality for Staff is 1 a Staff member can manage a maximum of one Branch.

One to many relationships

The above is a one-to-many relationship. An Analyst works on many Projects , but each Project has just one Analyst.

Many to many relationships

The above is a many to many relationship, a book such as "How I Did It" by Victor Frankenstein may be borrowed by 1 or many borrowers and a borrower may borrow 1 or many books.

Notation rules important

Entity boxes will have two parts the upper part shows the entity type name the lower half shows the primary key nothing else e.g

Relationships will be show by one or more straight lines no diagonals will be allowed

Any of these would be fine

A Diagonal such as above would not be acceptable

All Relationships must be named

All relationships must have participation and cardinality shown at both ends e.g

The direction of the relationship must be shown with the Symbol no other variation will be accepted

In the above it is the Borrower (blunt end of the triangle) that does the Borrowing not the Book

Part two

The ERD is the first part - the second part is a list of important non-key attributes for each entity (plus the primary key), for instance for an entity called Necromancer you might list it thusly

Necromancer

Necromancer_ID{PK}

Fname

Sname

Dateofbirth

Datewentinsane

Dateofdeath

Dateofreturn

Street

City

State

Country

Planet

However, for this exercise have no more than about 6 non-key attributes for each entity type (plus the primary key {pk} as above, but you must list some attributes for all entity types if you come up with an entity type and you cannot think of any attributes for it that's a big hint that it probably is not a terrific entity type after all. The challenge here is to come up with a set of important attributes that we will want to know about each entity instance

As this is a conceptual ERD do not create Foreign Keys

You may use any diagramming tool to create the ERD and attributes lists as long as I can read them.

Far and away the easiest tool for this job is creaky old PowerPoint which can draw rectangles, lines, triangles, and text boxes dead easy, and stuff can be easily scaled. PP Hints coming soon