Creating and manipulating data base using mySQL

MUST BE MySQL, RELATIONAL/ DATABASE EXPERT
You will need to answer all of the questions accordingly and please use MySQL workbench.
Refer to the abridged data file “2013-07 – Citi Bike Trip Data” (I will give you the data file later) You are required to design and implement a relational database to help better manage the
CitiBike program in New York City.
[I]
1- Study the data provided for the use of Citi bikes to gain an understanding of the program utilization.
2- Explore the information on the program available on the Web to verify the basic facts and collect
additional information regarding the utilization of Citi Bikes. Write down the additional
information and/or requirements you gathered. Give the URLs for the sites contacted.
3- Draw an appropriate E/R diagram that satisfies the basic and additional facts, indicating, weak and
subclass entity sets, whenever exist, multiplicity of relationships, and the key, or keys, for each
entity set. Distinguish between the parts of the E/R diagram pertaining to the given and extra basic
facts.
4- Translate the E/R diagram in [3] to a relational database schema.
5- Specify a number of essential functional dependencies for each relation. Identify possible keys,
whenever exist, and the primary key and foreign keys for each relation.
6- Examine the database relations for possible BCNF and/or 3NF violations. Decompose the relations
as appropriate.
[II]
Create SQL expressions to break the data provided in the file into the following tables:
Stations(Id, Name, Latitude, Longitude)
Trips(StationId, MinTripDuration, MaxTripDuration, AvgTripDuration, NumberUsers)
UsageByDay(StationId, NumberUsersWeekday, NumberUsersWeekend)
UsageByGender(StationId, NumberMaleUsers, NumberFemaleUsers)
UsageByAge(StationId, NumberUsersUnder18, NumberUsers18To40, NumberUsersOver40)
[III]
1- Create appropriate SQL expressions to determine the most frequent trips between any two stations
by the day of the week.
2- Create appropriate SQL expressions to determine usage patterns by gender and age for any given
station.
3- Create appropriate SQL expressions to identify any dormant or vacant bike stations.
4- Reconstruct the tables and recreate the SQL expressions above to include zip codes, hence allowing
for aggregation by area. Identify the area with the highest usage of the program. Note that zip
2
codes are not included in the data provided but may be related to the StationId by its Latitude and
Longitude. This could be part of your research and may require some coding!
[IV]
And at last create a written report that includes:
1- The complete E/R diagram and schema of the relational database fully specifying the given
requirements and any other requirements gathered. Identify all keys, foreign keys, functional
dependencies of the database relations.
2- SQL code that creates the tables’ structure.
3- SQL code that loads the data.
4- SQL code that answer the given queries.
5- Sample outputs for your SQL code.
6- Provide a narrative explaining the outputs produced whenever possible.
Order Now

Calculate a fair price for your paper

Such a cheap price for your free time and healthy sleep

1650 words
-
-
Place an order within a couple of minutes.
Get guaranteed assistance and 100% confidentiality.
Total price: $78
WeCreativez WhatsApp Support
Our customer support team is here to answer your questions. Ask us anything!
👋 Hi, how can I help?