A. ERD and Database Specification Your task here is to create an appropriate Entity Relationship Diagram (ERD) for the data associated with your online application. This ERD must document the data model for the entire system, and should conform to the second normal form (2nf) specification for the data model. Based on this ERD, provide the SQL statements that build the table structure for your database. Ensure that you have specified primary keys and foreign key relationships. Note that you should carefully read through the next two sections of this assignment and the last two assignments so that you fully understand what data is to be stored and retrieved. This will help you to make important design decisions for the database. Hint: Since posts and reposts are essentially the same, with the only difference being that a repost includes a reference to another post, we can consider these as being the same kind of entity. While this might appear to make things more complex, it will actually make other aspects of future assignments simpler.

B. There are a number of forms in this web application that will require that data be stored in the database. Your task here is to write test queries to verify that the data storage is working correctly. 1. Sign-up Form save the new user information in the database for the avatar image/graphic, you can assume that the file has been saved on the server already and that you have constructed its URL to store in the database 2. Post/Repost Form save an original post, including linking it to its author save a repost, including linking it to its author and to the original post 3. Like save the information to show that a specific user has liked a specific post delete a specific users previously saved like of a specific post 4. Dislike save the information to show that a specific user has disliked a specific post delete a specific users previously saved dislike of a specific post

c. C. Data Retrieval Queries There are a number of pages in this web application that require that data be extracted from the database. Your task here is to write sample queries to verify that the data is being extracted correctly. 1. Login Form given the username and password, retrieve the matching user ID, screenname, and avatar image/graphic if the username and password pair do not match any records in the database, return an empty result set 2. Post List Page retrieve up to 20 of the posts, ordered with most recent first this should include the content of the post, the date/time it was posted, the screenname of the author, the avatar of the author, the number of likes and the number of dislikes if this is a repost, it should also include the content of the original post, the screenname of the original author, and the date of the original post; this information will be null if this is not a repost hint: this is a complex query that will require the joining of multiple tables; do this carefully and incrementally, testing as you go 3. User Detail Page given a specific user ID, retrieve all of the users posts following the data retrieval requirements as on the Post List Page query above