Part 2

Final Project Announcement - Requirements for Part 2

 

 I thought it would be best to segment the requirements of Part 1 of the Final Project and the requirements for Part II of the Final Project.   I released the requirements for Part I of the Final Project in a class Announcement.  This segment contains the requirements for Part II of the Final Project.   I will also send out a separate class announcement containing the requirements for Part II of the Final Project.  The entire combined list of requirements for both Part I and Part II of the Final Project can be found in this Assignment Description Area of the Final Project Submission Link.  My thoughts are that segmenting the two segments in two separate announcements will make understanding the phases of building the components of the Final Project more understandable and less overwhelming.   

 

In Part 1 of the Final Assignment, is an option to create the design individually or collaboratively in groups. Part II of the Final Project is a totally individual effort.  Students working in a group collaboratively, are to take ownership of a set of tables in your database that does not match the set of tables of another group member.   One of the tables that are chosen should be an Entity Table acting as the Primary Key Entities in a 1-M relationship in the s database. This is a table that is on one side of one to many relationships. Remember, no two students can work on the same combination of related tables in the Part II section of the Final Project.

 

PART II Individual Database Information Extraction

 In Part II of the Final Project, each student must take a subset of tables from the entire set of tables of the topic area that you are working on.  Before starting Part II, it is recommended that Part I is fully completed. 

 

This means that the following has been completed

  1. A paragraph or two describing the Relational Database being designed and implemented.

  2. An Acceptable ER Diagram

  3. A Translated Relational Schema

  4. Metadata to support the Relational Schema

  5. SQL Script that successfully creates the sequence of tables described in the Relational Schema

  6. A list of Sample Data for the tables in the database

  7. SQL Script that will Create a Database and Create a series of related tables in a relational database.

  8. SQL Script that will successfully insert the sample data into the tables of the created relational database

 

In the Part II section, Students are required to perform the following types of queries using tables of the segment of the database that you are testing in your group’s or individual database project.

 

All Queries must return at least one row of information in the Resultset that they Return in order to receive points for the assignment in the project

 

Part II of the Assignment

 

  1. Write a query that performs a Union on one of your main tables and another table

 

Write an SQL statement that will combine the instances in both tables as a relational algebra union operation using an SQL SELECT Statement.  This is basically each student demonstrating their individual use of the UNION Clause in SQL

 

  1. Write a query that performs an intersection on one of your main tables and another table. 

 

  This will produce a result set containing the instances that match in both the table that you took ownership of. If you have difficulties with this concept, I have published a video series to provide students with a framework to accomplish this.  The Query linked to this task must produce a result set of 1 or more rows in order to receive points for this task.

 

  1. Write two separate queries that perform a Difference Operation on your main tables and another table.

  This will produce a result set containing the instances from one of the main tables that you took ownership of that do not match instances in the other table

  
It will also produce a result set that contains the instances from the other table that do not match instances in the entity table you took ownership of.  The video described earlier will provide a framework for doing this. The Queries linked to this task must produce a result set of 1 or more rows in order to receive points for this task.

  .

  1. You are to write a JOIN Query using two or more of your tables.  A table and two or more tables that it has a relationship with:

 Remember a basic relationship is established when two tables share a common attribute.  One is the primary key table the other is the foreign key table. For the multi-table select query, use the entity table as the one side of a 1-M relationship. Use one of the relationship tables created in the group segment as the many sides of a 1-M relationship.  The SELECT Query may include 2 or more tables. The practice videos will provide a framework for doing this. The Query linked to this task must produce a result set of 1 or more rows in order to receive points for this task.

  1. Create two queries that will alter the structure of your entity table:

Normally when integrating systems, there are a few datatype compatibility issues and data alignment issues that need resolution. To prepare for handling such problems, create a set of Alter Table commands to demonstrate that you can handle these types of situations should they arise.  The practice videos and homework assignments will provide a framework for doing this.  Each ALTER TABLE Statement must be preceded by a statement that describes the structure of the table before it is altered and a statement that will display the structure of the table after it is altered.

 

  1. Write two queries that will update two different categories of rows in the tables of the set of tables that you have chosen.

To demonstrate that you understand update queries, create at least two UPDATE Statements to process and update one or more existing records in your chosen tables. This could be accomplished by populating the new attributes added to the table.  The practice videos and homework assignments will provide a framework for doing this. The Queries linked to this task must produce a result set of 1 or more rows that are updated in order to receive points for this task.



 

  1. Write two queries that will delete two different categories of rows in your tables.

To demonstrate that you understand delete queries, create a set of DELETE Statements to process and delete one or more existing records in your chosen tables.  The practice videos and homework assignments will provide a framework for doing this.  The Queries linked to this task must produce a result set of 1 or more rows that are deleted in order to receive points for this task.

  1. Write two queries that perform aggregate functions on at least your Primary Tables in your set of individual tables.

The practice videos and homework assignments will provide a framework for doing this. The Queries linked to this task must produce a result set.

 

 

  1. Write two queries that use a HAVING clause on different categories of rows in your set of tables chosen. 

    The SELECT Queries should only include the HAVING Clause to separate them from the Queries that include both a HAVING Clause and a GROUP BY Clause.  

    The practice videos and homework assignments will provide a framework for doing this.  The Queries linked to this task must produce a result set of 1 or more rows that are updated in order to receive points for this task.
  2. Write two queries that use both a GROUP BY and HAVING clause on different categories of rows in your entity tables or a combination of the set of tables chosen


    The practice videos and homework assignments will provide a framework for doing this.  The Queries linked to this task must produce a result set of 1 or more rows that are updated in order to receive points for this task.

    There should be a collection of Two Queries that are using the GROUP BY Clause and Two Queries that are using both the GROUP BY Clause and the HAVING Clause

 

 

  1. Write two queries that sort the results in Ascending and Descending order of the set of tables chosen.  

    The practice videos and homework assignments will provide a framework for doing this.

    The Queries linked to this task must produce a result set of 2 or more rows in order to receive points for this task.

  2. Write statements that create two SQL VIEWS.  The set of view statements should also include the SELECT Statements to execute them.

The practice videos and homework assignments will provide a framework for doing this. The Queries linked to this task must produce a result set of 1 or more rows in order to receive points for this task.

 

 

13.  Create New Users  

The practice videos and homework assignments will provide a framework for doing this.

 

The GRANT Statements, REVOKE Statements and CREATE USER Statements will not interfere with the continuous execution of the SQL Script. The only statements that will interfere are the EXIT Statement and the MySQL Login sequences. The EXIT Statement and the MySQL Login sequences.should be commented using SQL comment indicators before submitting the script if using them in your testing.

 

 

14.  Extra Credit - Write two Stored Procedures

Create a set of CREATE Stored Procedure Statements.  One of the CREATE PROCEDURE Statements must include statements that describe the structure of the tables that you took responsibility for and lists all the tuples in the tables.  The script must include the call statements to invoke the stored procedure. The requirements for the first Store Procedure is that the Stored Procedure accepts an IN Parameter and an OUT Parameter.  Additionally the script should print the results in the OUT Parameter 


The second Create a Stored Procedure Statement must receive at least one passed parameter that it will use in a query within the body of the Stored Procedure.  This Stored Procedure should also have a selection sequence that performs a Binary Bypass or a Binary Choice. The script must include the call statements to invoke the stored procedure.

 

The practice videos and homework assignments will provide a framework for doing this. The Queries linked to this task must produce a result set of 1 or more rows that are updated in order to receive points for this task.

 

 

15.  Extra Credit - Write Stored Triggers

Create a set of Stored Triggers that will automatically backup a tuple when it is deleted or updated.  This will involve writing two triggers. 


One of the Stored Trigger inserts the older version of the tuple into a backup table before it was updated. 


The second Stored Trigger will insert the tuple that was deleted into the same backup table that the update trigger uses.  


Both stored triggers should be linked to one of the one tables that you took individual responsibility for in the project.   You must include code that invokes the triggers.  This means that you must include the DELETE Statements that delete information from the table.  You must also include the UPDATE Statement that updates information in the table.

 Finally, you must include SELECT Queries that display evidence that the triggers were invoked successfully. This is done by displaying the content of the backup audit table after the UPDATE and DELETE operations are performed.

 

The practice videos and homework assignments will provide a framework for doing this.


Write code that will create you and another user in the database you are participating in.  This means that two new users must be created

Include code that will do the following:

  • Code/Script that will grant you and the other user privileges to the primary tables that you have taken possession of in the Final Project Assignment.   
  • Code/Script that will revoke user privileges to the tables that you have taken possession of in the Final Project Assignment. 
  • Code/Script that includes statements that will demonstrate that the privileges that you granted and revoked are working.  This is an SQL statement that will display the current privileges of users to tables.  The sequence of code should display the privileges prior to granting, after granting privileges, and after revoking privileges.