HW: Final Project due Thu 04 May 10:30

\begin{purpose}
This assignment provides practice in:
\begin{itemize}
\item ...
...meaningful SQL queries on a self-designed database
\end{itemize}
\end{purpose}

Description

Begin this assignment by reworking the design of your database to be in line with whatever feedback you received from the instructor on your previous homework. If necessary redraw your UML diagram so you will have an accurate representation of your database to work with in the steps that follow.

You will continue to employ the workflow we used on lab day to create an SQL to create a database. These are the elements of the workflow you should follow:

  1. Edit an SQL script (named project.sql) making small changes and then testing.
  2. Begin by writing drop and create commands for tables that do not have foreign keys. Remember to test the script after each change.
  3. Then do the same for tables having foreign keys. Add foreign key constraints as appropriate.
  4. Add insert statements at the bottom of the document inserting into tables in the same order in which they are being created utilizing autoincrement for primary keys when appropriate.

Plan and write three SELECT queries that would be useful for the database you have created. When you have a query the way you want it, create a named view based on the query so it will be easy to launch. At least one of the queries should make appropriate use of a GROUP BY clause include use of one or more aggregate functions. Paste your working CREATE VIEW commands at the bottom of your script in project.sql.

Turning in Your Work

At the end of this assignment you should have a project.sql script that will create and populate a database with tables matching your UML design. The script should also populate the tables with enough data to provide meaningful results from the views that are also created by the script. The tables, data, and views that are created by your script should be in the project database in which the user class has full permissions.

Upload your project.sql document in Canvas prior to class on the due date. The instructor will evaluate your script and your database.

Quick Links