Metropolitan State University

ICS 311 Database Management Systems

Term Project Step 3

Database Implementation on MySQL

Due: See Syllabus

Part 1:

In this part of the project you are required to implement your database on MySQL. Your implementation should include the following:

1- Create all the tables that you specified in your relational schema (Part 2, of Step 2). Make sure to include primary keys and foreign keys.

2- Populate all tables with data of your choice. Make sure that each table includes at least 10 rows.

Part 2:

Design and clearly explain at least ten query scenarios that may be useful on your database. Write SQL syntax to answer each query. A query like this: select count(*) from tableOne, can only be used as one of the ten. Strongly suggested to include some queries using Table Joins, Aggregates, Group By, Order By, and Having. At least one query must use a view (that you created from your application tables) in the FROM clause. Note: the SELECT used for in the creation of the view, does not count as one of the 10 queries.

What to submit:

1- A word document to explain your query scenarios (in good detail) along with SQL syntax to answer these queries. Make sure to include at least two aggregate queries, also some doing table joins, some order bys and group bys (in other words have a good variety that incorporates the material we have been learning).

2- There is a dropbox folder on D2L to turn in your project code. You need to turn in the .sql scripts that include:

a. SQL statements to create the tables (from Part 1 above).

b. SQL statements to populate tables with data (from Part 1 above).

c. SQL statements for your query scenarios (from Part 2).

d. SQL used to create the view (from Part 2).

Note that you need to include all the required SQL statements in file. This could be a Word Doc. or a text file that ends in .sql. Make sure to test your file before you submit it.