FINAL CLASS PROJECT

A significant part of your grade this semester is the delivery of a final project. The final project is 2 parts.
1. Students will be expected to use their professor provided database to create, populate and query multiple tables. Students will be responsible for designing multiple database tables, creating those tables, and inserting data in those tables,
2. Performing multiple queries against the "demo" database and your own database.

Your final project is important!

• Learn about database design, table creation, connecting tables, loading tables and querying tables
• Give you the opportunity to apply what you have learned in this class.
• Although some part of the projects cannot be implemented early in the course, begin thinking about it.

Your project should at minimum include:

• Part 1a. The design of a normalized database for an order entry system, as follows: (10 points)
  • A customer table
  • A customer address table (a customer can have multiple addresses)
  • A Customer orders table (a customer can have multiple orders)
  • A customer orders line item table (an order has multiple line items)
  • A product table
  • A vendor table
  • PS. A single product is supplied/provided by many vendors
  • Add whatever columns you feel are necessary to support an order entry process system.
  • Relate the tables to one another using 1:1, 1:N or N:M relationships as you see fit.
  • Submit your ER diagram

• Part 1b. Create the tables as per your design: (20 points)
  • Create the above tables in your own database (username=lastname, pswd=lastname)
  • Assign proper column data type as you see fit
  • Create primary keys and foreign keys to relate the tables to one another

• Part 1c. Load the tables with your own made up data, as follows: (20 points)
  • Make up your own data, specific to each table (best to use Excel)
  • Insert data into the various tables
  • Tables should contain a minimum of 10 rows, and a maximum of 50-100 rows each
  • Professor will provide a tool to facilitate the loading of your data from a CSV file

• Perform 3 queries (and repeat)
  
  
• Query 1 - (15 points)
  - Display all courses that no student has registered for (3 versions)
  - Version 1 - use left join syntax.
  - Version 2 - use "not in" syntax.
  - Version 3 - use "not exists" syntax.
  
  
• Query 2 - (15 points)
  - Display course description, student sex, fname, lname, course price for all classes taken
  - Followed by a total course price by course description and student sex
  - Followed by a total course price by course description
  - Followed by a grand total of course prices
  - Output should look something similar to...

  DESCRIPTION                   SEX        FNAME       LNAME       PRICE
  JavaScript  	               F         Natasha     Ryan        1,095  
  JavaScript  	               F         janet       miller      1,095  
  JavaScript  	               F         Cynthia     Owens       1,095  
  JavaScript  	               F         Anya        Milgrom     1,095  
  JavaScript  	               F-Total                           4,380  
  JavaScript  	               M         Joseph      Race        1,095  
  JavaScript  	               M         John        Stoley      1,095  
  JavaScript  	               M         Duncan      Davidson    1,095  
  JavaScript  	               M-Total                           3,285  
  JavaScript-total                                                 7,665  
  SQL Programming Language       F         Lillian    Vasquez        540  
  SQL Programming Language       F         janet      miller         540  
  SQL Programming Language       F         Cynthia    Owens          540  
  SQL Programming Language       F         Angel      Brinson        540  
  SQL Programming Language       F-Total                           2,160  
  SQL Programming Language       M         Vincent    Cambria        540  
  SQL Programming Language       M         Robert     Grace          540  
  SQL Programming Language       M         Rick       Myers          540  
  SQL Programming Language       M         Patrick    Stack          540  
  SQL Programming Language       M         Joseph     Race           540  
  SQL Programming Language       M         Edward     Teagan         540  
  SQL Programming Language       M         David      Smith          540  
  SQL Programming Language       M-Total                           3,780  
  SQL Programming Language-total                                   5,940  
  etc.
  _Grand-Total                                                    48,645  
  

• Query 3 - (15 points)
  - Display all student names, courses they are taking, course price, and their total tuition
  - Display this information horizontally
  - Display a total line at the botton
  - Output should look something similar to...

  NAME            SQL     WEB     J/S    XML    TUITION
  Angel Brinson  	540  	1095                     1635  
  Anya Milgrom            1095  	1095             2190  
  Barbara Burns           1095           1095      2190  
  Colette Nelson          1095           1095  	 2190  
  Cynthia Owens  	540  	1095  	1095             2730 
  etc.
                 5940    24090  	7665  10950  	48645  
  

• Repeat the above - (5 points)
  - Replicate all above 3 queries, but now use your own (newly created) database
  - Substitute your customer table for the "demo" student table
  - Substitute your product table for the "demo" course table
  - Output should resemble the output for queries 1,2 and 3
  - This effort should simply be a mechanical effort of table substitutions

Posting your project:

• Your project tables should be created within your own individual database (with your lastname)
• You can use either the MySQL database or the Oracle database
• The tables should be fully loaded with data
• Submit your ER diagram to NYU Brightspace "Final Project"
• Submit all "create table" and "insert into" SQL commands to NYU Brightspace "Final Project"
• Submit all SQL query commands (using text format) and query results (using screenshots) to NYU Brightspace "Final Project".