2

Please compare the directions below to my ER Diagram and tell me if anything is wrong with my ER Diagram Please I think its accurate enough.

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.

enter image description here

1
  • I think there would need to be a MemberPhone table, since the description mentions storing multiple phones per member
  • I would create tables to store the possible values for for BicycleMake, Bicycle (which contains an attribute linking to a model), BicycleColor, and BicycleSize. Given these tables, there wouldn't need to be a reference to the BicycleMake in Bicycle.
  • I feel like the Total Rental Length attribute doesn't need to be stored, since this could be derived from the pick-up time and drop-off time. Or better yet, the drop-off time doesn't need to be stored, since it can be derived from the pickup time and total length (DateTime + int is smaller than DateTime + DateTime).
  • How were you planning on storing the sponsorships?
  • I suspect that subscription information would need to be stored, such as when a subscription was purchased, and what type it is (monthly, yearly, daily, ...). If this does need to be stored, then it's possible that the membership status can be derived from the subscription information, if the membership status is simply whether the member has an active subscription or not. I would ask questions about this...
  • Bicycles should have an attribute for what terminal it's currently stored in (perhaps a null-value meaning it's not currently in a Terminal). Otherwise, how do you know which terminal a bicycle is in if it has yet to be rented?
  • I think the Terminal_Info table is unnecessary, since the number of bicycles at a terminal is duplicating data.
  • I feel like the GPS tracking number would need to be stored for the bicycles. I would ask questions for this...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.