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:
- Edit an SQL script (named project.sql) making small changes and
- Begin by writing drop and create commands for tables that do not have
foreign keys. Remember to test the script after each change.
- Then do the same for tables having foreign keys. Add foreign key
constraints as appropriate.
- 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.
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.