First: Refer to the abridged data file “2013-07 - Citi Bike Trip [login to view URL]” (data from CitiBike) will be given.
Part 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.
Part 2:
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)
Part 3:
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!
Hello there, The citibike project is an interesting project for the New York city who want to hire the bike to run from station to another station. Depend on your project's description i can understand that you need to do 3 tasks:
+ Base on the given text file, explore the data in this file, draw the ERD within the entities, relationship.. Convert to relational schema, give the Functional Dependency and normalize to 3NF or BCNF if can be
+ Create the given tables, load the data from the given text file and insert into given tables
+ Create the SQL statement to answer some question such as frequent trip, usage pattern.... But the most challenge in this part is we need to match the zip code for each station by longitude and latitude. Actually i've done similar job like this before in MySQL, that is the Harversine formula to calculate the distance between 2 point(longitude and latitude). After that we will find the minimum distance base on this formula and within this minimum distance we will assign the zip code to this station.
Waiting for your response if interested. Thank you!!!
Hi sir,
I can complete your job.
I have rich experience in Web development. Also dont worry about time frame I will try to complete ASAP.
I have worked on more than 100+ projects so far where I worked on HTML5, CSS, JS, Bootstrap, PHP, Angular JS, Node JS, CI, Wordpress, Laravel, CakePHP, .Net, Magento etc.
This bid is approx. I’ll give you exact budget and price once discussing in details with you.
Also you can see my profile about my work and communication.
I have few queries and suggestions to discuss. Please initiate the Freelancer Chat Box.
Dear sir,
I'm very excited to work with you on your project. I have read the description and I can do this because I had completed this so many times for my clients. Hence, I'm very familiar with this.
Kindly share more information.
Thanks and be well,
Pratik Patel
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.
*** EXPERT DEVELOPER ***
I may provide you full application according to your requirements and customizations.
My expertise includes the following :
- .NET
- ASP.NET
- C#.NET
- MYSQL
- MS SQL
- WPF
- WEB FORMS
- WINDOWS FORMS
- Android
- PHP
- Python
- Files Processing (File Formats: PDF, WORD, XML, CSV, TEXT)
I have already made many websites and desktop-based software.
Let us talk and discuss your task to start it right now.
Hopefully seeking the acceptance of the proposal.
Hi, I have full experience with MySQL.
==========================================================
I'm sure I can complete your project on time with high quality.
you can contact me and discuss project more detail via chat.
Best regards. Jackson.
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
Hello,
I am a Database developer. I prepare data models and create ER diagrams and also write SQL scripts to create the tables, constraints, triggers, objects, procedures.
I also carry out data normalization by following 1NF, 2NF, 3NF rules.
I have delivered several database assignments successfully which involve designing the schema, creating dummy data and write queries on that data to provide desired outputs.
As a part of my delivery, I also provide the DDL/DML scripts etc.
I have executed several Data Migration projects using SQL procedures and ETL queries.
I also provide SQL tuning services. So, I can tune existing SQL queries, correct them for proper joins and indexed execution.
I am from Belgium and I work in Central European timezone. I dont outsource my assignments to others(sub-let). All my communications and transactions are carried out via this portal. My deliverables are always on schedule.
I hope I have provided you the information that you need to decide on if you would award this project to me.
If you assign this project to me, I would expect you to provide me clear documents/requirements indicating what data model you would need. We can establish the milestones accordingly.
Hope to hear from you soon.
Kind Regards
Paul
Hi, there. I can start just now.
I am a creative and professional Database and database convert application developer having more than 5+ years of professional experience as a senior developer in this field.
My main areas of expertise are MySQL, PostgreSQL, Excel, VBA, Access, Oracle, MongoDB and other bigdata king.
So i ensure to finish your project within your deadline.
If my project have some problems, you don't need to pay me.
Thanks.
Hi.
I have read proposal, fully.
I have good experience with C++, Python, Java programming.
I experienced in nlp and machine learning with rasa dialog flow ai and api.ai.
I good at mathematic and usually participate in contest like codeforce, codechef.
I can do this project.
Please hire me.
Regards, Tun Tun.
Hi,
I have 9+ years of experience in Database development and management.
I would be able to build your application in NoSQL, which will work with high performance considered your size of data.
Will commit long-term.
Ping me, we can discuss in detail
Hi
I’m SEYEDPOUYA and i have 21 years old.
Since i was 14 years old i started to work as a developer and now i’m here to work with you.
As i mentioned i have 6+ years experience in this field i know ups and downs of this kind of work.
I’m so motivated for coding and programming and I spend approximately 7-8 hours per day on it.
I’m so interested in your project and your idea and I think your idea is so creative.
Contact me for more information about myself and my experience.
Thank you.
i have read job description i have a nice background in Database design and administation.
I can complete every bit of requirement which you are looking for.
your task is fairly easy and simple.
We can discuss further in chat.
I am a computer science graduate with 5 years of experience in development and database field .I can create and upgrade and your database tables in SQL .I can write and optimize your stored Procedure and create views and build your function .
Hi there, I am Kareem
I am a professional database administrator with 15 years of experience in the filed of Database development and administration
I have read carefully your description and i fully understand your requirements, and be sure that i will deliver the project on time
Please contact me for more details
Regards,
SQL & database developer having experience of 3+ years of experience currently working as an database engineer having intense expertise in Database development
