Question 1: Entity-relationship (ER) model ()
Create an ER diagram using the set of requirements provided for Oxford City Council's bicycle sharing scheme. Your ER diagram should use UML notation and identify the following:
- Main entity types
- Relationships and their multiplicity
- Attributes
- Candidate and primary keys
- Any assumptions that you made
Bicycle Sharing Scheme
Oxford City Council is preparing to launch an innovative program that will provide community members with short-term access to bicycles through self-service terminals placed at convenient locations across the city. Each terminal is given a unique identification number, street address, and maximum storage capacity. The city keeps track of the bicycles currently available at each terminal for logistical reasons.
The program will offer subscriptions that include weekly, monthly, and annual memberships. Membership costs range from a few dollars each week to over $100 for an annual membership. Information stored about each member includes full name, email, contact phone numbers, residential address, driver's license number, and membership status. A subscriber may only have one active membership at any given point in time. In addition to memberships, visitors or casual users can access the system by purchasing a $5 one-day pass using a credit card.
The city has made an initial purchase of 700 bicycles, each of which is fitted with a GPS tracking device. The city records the unique ID of each bicycle, along with it's make, model, color, and size. To reduce costs, private-sector companies are invited to sponsor individual bicycles and self-service terminals and, in return for a fee, their logo will be displayed prominently on the sponsored asset. The city tracks the name of each sponsoring company along with their address, contact name, email, telephone number, and a list of the assets that they have chosen to sponsor.
Each rental transaction is fully automated and records details of the user, bicycle, pick-up time and location, drop-off time and location, and total rental length. A user may not borrow more than one bicycle at any given point in time.
Question 2: Derive a relational model
Derive a relational model from the ER diagram provided below. Your relational model should identify the main relations, attributes, primary key, and foreign keys.
Question 3: Identify the minimal set of functional dependencies and all candidate keys for the relation R (
R (ABCDEFGH)
Functional Dependencies:
ACD → EF
A G → A
B → C F H
D → C
D F → G
F → C
F →D
Question 4: Using the functional dependencies provided, normalize each of the following relations, R1, R2, and R3 to the third normal form
You should assume that there are no duplicate rows present and that all values are atomic. Each solution must clearly identify the steps required to normalize the relation to 3NF and demonstrate that it is lossless and preserves all functional dependencies.
R1 (ABCDEFGH)
FDs: AB → D, B → C, B→ E, A→H, H → G
R2 (ABCDEFGH)
FDs: ABC→DE, BC →G, G → HF
R3 (ABCDEFGH)
FDs: BC →DE, C→ F, F→GH