Create an er diagram using the set of requirements

Assignment Help Database Management System
Reference no: EM13164800

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.

702_Derive a relational model.png

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

Reference no: EM13164800

Questions Cloud

What is the empirical formula of putrescine : Putrescine is a compound that is partially responsible for the rotten smell of decaying flesh. It consists of 54.50% C, 13.73% H, and 31.77% N. What is the empirical formula of putrescine?
The basic solutions of the simplex method. : Solve the following problem by inspection, and justify the method of solution in terms of the basic solutions of the simplex method.
The menu is displayed and the user must select : The menu is displayed and the user must select an option (a number between 0 and 7). The action corresponding to the selection is performed, then the menu is displayed again and the user can choose another option.
Compute the concentrations of fe : Calculate the concentrations of Fe3+ and SCN- in the reaction mixture in part I when you add 10.00 mL of 0.200 M Fe3+ to 1.00 mL of 2.0 x 10-3 M SCN- and make up the total volume to 50.00 mL? What is the concentration of FeSCN2+?
Create an er diagram using the set of requirements : Create an ER diagram using the set of requirements provided for Oxford City Council's bicycle sharing scheme and identify the minimal set of functional dependencies and all candidate keys for the relation R
What degrees celsius and pressure of hg occupy : What colume will 500 mL of gas at 20 degrees celsius and a pressure of 420 mm Hg occupy if the temperature is reduced to -80 degrees celsius
Modify the functions for the bisection and false-position : Modify the functions for the bisection and false-position techniques of finding a root of an equation (see below questions) so that the number of iterations can be determined and displayed. (The count should only be displayed after the loop is com..
Growing issues of women and larger cultural issues : The realities of women in the workplace and the growing issues of women and poverty are definitely major issues that the 21st century is confronting. Can we link these problems with larger cultural issues?
Estimate the vapor pressure of sea water : Estimate the vapor pressure of sea water at 20 degrees celsius given that the vapor pressure of pure water is 2.338 kPa at that temperature

Reviews

Write a Review

Database Management System Questions & Answers

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Design a database schema

Design a Database schema

  Entity-relationship diagram

Create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices and concepts.

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Prepare the e-r diagram for the movie database

Energy in the home, personal energy use and home energy efficiency and Efficient use of ‘waste' heat and renewable heat sources

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Data modeling and normalization

Data Modeling and Normalization

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Practice on topic of normalization

Practice on topic of Normalization

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd