First: Refer to the abridged data file “2013-07 - Citi Bike Trip [login to view URL]” (data from CitiBike) will be given.
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  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.
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)
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
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!
21 freelancer chào giá trung bình$158 cho công việc này
Hi, I am a Senior DBA with more than 20 years experience in databases, Oracle, sql sever, mysql, ... modeling and programming. I can help you. Regards.
Hi I have 15 years of experience in SQL server Database development, MySQL, Postgress development, C#, Python, power bi. I will be able to help you with the project. please ping me offline to discuss further