Database Construction and Data Population

Stage 2 of the TV Database Project is broken up into two Parts.

PART A: Database Construction and Data Population

Provide two SQL scripts.

Script 1 should...

1. Select the database to use
2. Drop all previous tables in the correct order if they currently exist.
3. Create all tables for your database design. Make sure to include all constraints.

Script 2 should...

1. Select the database to use.
2. Delete all data in the tables for your database design in the correct order if they currently exist.
3. Insert data (NOT using the LOAD DATA SQL statement), for 5 TV Show Viewings per Team member (15 for groups of 3, 20 for groups of 4, 25 for groups of 5). Include all relationship data.

You may make changes to your past project database design, but please provide a new ERD...either in Chen or IE/Crow's Foot notation.

DO NOT USE ANY AUTOMATED TOOLS TO GENERATE THE SQL. DOING SO WILL RESULT IN A ZERO SCORE.

Submit the two scripts in two separate text files.

PART B: Database Queries

In this part we will use your fully created project TV database that is populated with TV show viewings from Part A. Using this you will state SQL statements which retrieve, insert or delete information from your TV database.

For every question in PART B, you will supply...

1. i) A human understandable word description of the SQL statement (what is it showing, doing...what question are you trying to answer). ii) The SQL statements (fully visible). iii) If there exists, output results (showing at least the first 5 rows) for every statement.

See the example output provided with this assignment.

Part B ) Prepping for the final Project Stage.

The section will perform or give examples of webpages in the final TV database project. See the project overview for the webpages desired.

Question 1) Generate SQL to "Add a TV Show Category". Provide example data.

Question 2) Generate SQL to "Delete a TV Show Catgegory". Provide example data.

Question 3) Generate SQL to "Add A TV Show". Provide example data.

Question 4) Generate SQL to "Delete A TV Show". Provide example data.

Question 5) Generate SQL to "Produce searchable TV Show directory (search all characteristics). Display all characteristic per TV Show in output. Sort ascending by TV Show Name.". Provide example data.

Question 6) Generate SQL to "Add a TV Show Viewing". Provide example data.

Question 7) Generate SQL to "Delete a TV Show Viewing". Provide example data.

Question 8) Generate SQL to "Produce viewing statement between two times of day (if the viewing starts between these times). Show all viewing characteristics in output. Sort by date and time. Calculate the total length of time for these viewing as well as the percentage that this length is of all viewings.

Question 9) Generate SQL to "Produce a TV Category report. Show the Category, number of shows per Category, total length of time per Category, and percentage of this time per Category is of all time viewed. Sort by Percentage Length of Viewings in each Category.

See the Project Overview for detail on the SQL output for Part B.

Example output of