FINAL CLASS PROJECT

A significant part of your grade this semester is the delivery of a final project.
Students will be expected to use a professor provided empty course database to create, populate multiple tables as per requirements below.
Students will be responsible for designing multiple database tables, creating those tables, inserting data in those tables.
In addition, students will be responsible for coding multiple SQL queries as per requirements below.

Your final project is important!

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

Your project should at minimum include:

• 1. The design of a normalized database for an order entry system, as follows:
  • A book table
  • An author table
  • A book has one or more authors, an author can publish one or more books
  • Add whatever columns you feel are necessary to support the requirements
  • Add any additional tables if your design requires that
  • Relate the tables to one another using any of the 1:1, 1:N or N:M relationships as we learned
  • All tables should be created in database='sqlcourse' with userid='sqlcourse' and password='sqlcourse'
  • All tables created should begin with up to 4 characters of your lastname in the format of lastname_tablename

• 2. 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 tables
  • Tables should contain a minimum of 10 rows, and a maximum of 50 rows each

• 3. 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 database (DBname=sqlcourse)
• Use the MySQL database
• The tables should be fully loaded with data
• All "create table" and "insert into" SQL commands should be submitted to Brightspace assignment "Final Project"
• All SQL queries and query results screenshots should also be submitted to Brightspace assignment "Final Project"