SQL Database

Alumni Website

You’ve been asked to create a database schema for an alumni website. Initially, you’re given the following list of web pages:

Home – landing page, major highlights, courses, success stories, statistics and numbers

Join – registration by email address (ensure not campus email address as may expire), requires acceptance

Login/Update – member contact/information update

Events – CIT-related events (past, upcoming)

Blog – monthly article by staff or alumni members

Contact

Create a list of objects (entities) that would be required to deliver the functionality from the list of web pages. From this list of entities, create a list of tables that corresponds with these entities. Use MySQL Workbench to create a model with all of these entities. For each table, add an appropriate primary key (surrogate or natural).

Save the model as alumni.mwb.

Export the model schema as an SQL file called alumni.sql.

Pick three of the the tables (at least one with a relationship), and add any necessary properties and relationships. All relationships must also include relationship labels. You must also add at least five rows of data for these three tables. Export the updated model schema and data to a file called data.sql.

To help with the properties and relationships, create a list of relationship “rules” that correlate with these three tables. Write out each rule using language similar to the boat-sailor examples on Canvas. You should have at least two rules for each relationship (relationships are bidirectional).

Save these rules as alumni-rules.txt.

Update your model mwb file, and re-export the SQL schema.

Export a PNG image of the model, and save the image as alumni.png.

Queries

Write at least five queries that select data from the three tables.

One of the queries must involve the two tables involved in a relationship.

One of the queries must use a subquery.

Save the queries in a file called alumni-queries.sql.