FINAL CLASS PROJECT

A significant part of your grade this semester is the delivery of a final project.
Students will be expected use their professor provided database to create, populate and query multiple tables.
Students will be responsible for designing multiple database tables, creating those tables, inserting data in those tables,
and finally performing multiple queries against those table.

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:

• The design of a normalized database for an order entry system, as follows:
  • 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.

• Create and load the tables with your own (made up) data, as follows:
  • Create the above tables
  • Assign proper column data type as you see fit
  • Create primary keys and foreign keys to relate the tables to one another
  • Make up your own data, specific to each table
  • Insert data into the variaous tables
  • Tables should contain a minimum of 10 rows, and a maximum of 50-100 rows each

• Query the "demo" database tables, as follows:
  • Perform 3 queries
  • Query 1 -
    - 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 -
    - 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 -
    -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  
    
    

Posting your project:

• Your project tables should be created on the professor provided individual database (with your lastname)
• You can use either the MySQL database or the Oracle database
• The tables should be fully loaded with data
• All "create table" and "insert into" SQL commands should be submitted to NYU Brightspace "Final Project"
• All SQL queries and query results screenshots should be submitted to NYU Brightspace "Final Project"