You have been asked to design a database system for GeoNotes, a company that wants to create a mobile application that anyone would use to create routes (Geo) that would automatically be produced while walking, biking, driving, etc., and comments (Notes) - text, photos, points-of-interest, location ratings, etc. - that the user would enter while using the application.
For example, tourists could use this application while walking through a city, and it would automatically capture their walking route. When they encountered something they wanted to comment on or "geo-tag", they would simply enter some text, or take a photo, or identify a point-of-interest, or "rate" a location based on its significance or beauty. Once they returned home from their vacation, they could review all their GeoNotes - the walks they took, associated comments and geo-tags, and "relive" their vacation.
One useful feature of GeoNotes is that users do not need to be connected to the Internet - GeoNotes are captured regardless of network connectivity. However, once users decide to publish their GeoNotes, they need to connect to the GeoNotes "cloud" to upload their information. Once uploaded, they could share these GeoNotes with their friends and family so that they could also experience the vacation and/or use them for planning their own vacation.
The GeoNotes system has two databases - one for the mobile application and one for the cloud-based GeoNotes management system. These databases need to "talk" to each other so that a user's mobile GeoNotes are synchronized with the cloud-based system.
Design a database for this project.
1. Draw a conceptual data model (entity-relationship model diagram) for your database. There should be at least four entities with their relations. Clearly describe and explain all the symbols you use for your ER model diagram.
2. Working from your conceptual data model, draw the physical data model (database design) for your database. This diagram must contain sufficient detail to implement your database in a database management system. Again, clearly describe and explain all the symbols you use for your database design.
3. Explain the difference between your ER diagram (part 1 above) and your final database design (part 2 above).
4. Describe at least one stored procedure for this project in detail, explain why using this stored procedure would be a good idea, and use pseudo-code to demonstrate what the stored procedure would do.
5. During the requirements analysis phase of the database design, you discovered that GeoNotes wants to use social media data in some manner. What type of "Big Data" from social networks would you recommend they look at and how might it be used concurrently within your database?