Final Project Part II Requirements

Final Project Part II Requirements

The DML QUERY SQL statements in PART II of the Final Project must reflect each student's individual SQL coding.

CIS 205 Final Project Spring 2024

Part I of the Final Project assignment consists of separating a database project into the phases that were outlined during the semester. The project should be completed with a combination of group collaboration and individual effort. The Final Project follows the development process that was outlined during the semester. It consisted of the following activities:

creating the database design documentation
creating the database
creating the tables in the database
Creating the Sample Data
inserting the sample data into the tables in the database.

Below is the list of documents that each student must be completed in the Part 1 Component of the Final Project

A Requirements Statement containing a description of the Database and its requirements that you are designing and implementing
ER Diagram of the Database
Relational Schema of the Database
Metadata for the Database
Sample Data that you are planning to use to test the database
SQL Script to Create and USE the Database
SQL Script to Load the Sample Data in the Tables

Combine all SQL script into a Single SQL Text File

The SQL Script to Create the Database, Load the Database with Sample Data and the SQL Script of your individual SQL Statements must be combined into a single SQL Text File

Reminders

All SQL Script must be submitted as a text file with an SQL extension. Do not submit SQL script in a Word Processor file Format. Word Processors embed special characters into files that may cause syntax errors when (executing / running) the script. Do not submit SQL Script as text in the Submission Text Area. All SQL scripts must be submitted as a Test File with an SQL extension. An SQL extension is required since it is the standard in the industry for SQL scripts.

Test Your SQL Script

Check Referential Integrity

Make sure the tables are created in the correct order that follows the rules of referential integrity. The tables on the one side must be created before the tables on the many sides of the relationship.

Make sure that Data Insertion occurs in the correct order that follows the rules of referential integrity. The Primary Key Values on the one side must be inserted before the Foreign Key Values on the many sides of the relationship.

PART II - Individual Database Information Extraction Phase

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.

Final Project PART II- The Individual Database Information Extraction Phase

In Part II of the Final Project, if working in a group, each student should take a subset of tables from the entire set of tables of the topic area that you are working on. Students working individually can choose a subset or they can work with the entire set of tables in their database. Before starting Part II, it is recommended that Part I is fully completed. This means that the following has been completed.

A paragraph or two describing the Relational Database being designed and implemented.
An Acceptable ER Diagram
A Translated Relational Schema
Metadata to support the Relational Schema
A list of Sample Data for the tables in the database
SQL Script that will Create a Database, USE the DAtabase, and Create a series of related tables in a relational database.
SQL Script that will successfully insert the sample data into the tables of the created relational database

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

Final Project Announcement - Requirements for Part 2

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.

A paragraph or two describing the Relational Database being designed and implemented.
An Acceptable ER Diagram
A Translated Relational Schema
Metadata to support the Relational Schema
SQL Script that successfully creates the sequence of tables described in the Relational Schema
A list of Sample Data for the tables in the database
SQL Script that will Create a Database and Create a series of related tables in a relational database.
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 Result set that they Return to receive points for the assignment in the project.

Part II of the Assignment

Write a query that performs a Union Operation 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 that includes the reserved word UNION. This is basically each student demonstrating their individual use of the UNION Clause in SQL

Write a query that performs an intersection on one of your main tables and another table using A Sub Query.

This will produce a result set containing the instances that match in 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 to receive points for this task.

Write two separate queries that perform a Difference Operation on your main tables and another table using A Sub Query.

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 to receive points for this task.

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 to receive points for this task.

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.

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.

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.

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

These are two SELECT Statements containing aggregate functions such as SUM, MAX, AVG, and MIN. The practice videos and homework assignments will provide a framework for doing this. The Queries linked to this task must produce a result set.

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

The SELECT Queries should contain only the GROUP BY Clause. This will separate these set of queries from the set of queries that must contain both.

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 to receive points for this task.
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 SELECT Queries should contain both the GROUP BY Clause and the HAVING clause. This will separate these set of queries from the set of queries that should only contain the 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.

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

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.

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

At least one of the views must be associated with a multiple table query. Students can reused a multiple table query from information requests 1 through 4 for this information request. 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 to receive points for this task.

Create New Users

Write two CREATE USER Statements. One of the CREATE USER Statements must include yourself.

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

Extra Credit

GRANT and REVOKE Privileges to USERS

Write GRANT and REVOKE Statements to GRANT and REVOKE DATABASE or TABLE privileges to the two users that were created in the CREATE USER segment of the project.

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.

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.

Extra Credit - Write two Stored Procedures

Create a set of 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 list 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 are that the Stored Procedure accepts an IN Parameter and an OUT Parameter. Additionally, the script should print the results in the OUT Parameter

The requirements for the second 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 to receive points for this task.

Extra Credit - Write Stored Triggers

Create a set of Stored Triggers that will automatically backup a tuple when it is deleted or updated from a table. 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 backup table must contain an attribute that indicates the type of operation that took place.

The second Stored Trigger will insert the tuple that was deleted into the same backup table that the update trigger uses. The backup table must contain an attribute that indicates the type of operation that took place.

Both stored triggers should be linked to one of the one table 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.

Reminders

Test Your SQL Script

Make sure that you test your SQL script by copying and pasting your SQL script from your text file into the MariaDB command line. While testing your script make sure that all attribute declarations for you create table statements end with a comma. Make sure that the last constraint declaration for you create table statements does not end with a comma. Commas indicate that there is another declaration. In a CREATE TABLE Statement, if there is not a declaration following the last constraint declaration, do not include a comma in that declaration. A provided a couple of class announcements describing Syntax Error problems and solutions to the problems. Students should review the Class Announcements Section in Canvas to review these recommendations and guidance in handling some types of SQL Syntax errors.

Check Referential Integrity

Make sure the tables are created in the correct order that follows the rules of referential integrity. The tables on the one side must be created before the tables on the many sides of the relationship.

Make sure that Data Insertion occurs in the correct order that follows the rules of referential integrity. The Primary Key Values on the one side must be inserted before the Foreign Key Values on the many sides of the relationship.
Make sure that Deletion Operations occur in the correct order. A deletion operation cannot occur on a row in a Primary Key Table if that instance has a row in a Foreign Key Table linked to it. The row or rows in the Foreign Key table that is linked to the Primary Key row must be deleted before the Primary Key row can be deleted. The Exception to this is if the tables associated with the operation were created with the cascade delete clause.

For the Entire Final Project, Each Student Should Submit the Following Documents

Overview explaining the purpose and goals of the Project

Requirements Statement Document

This is the Requirements Statement. This brief overview should be submitted in a Word Processor Format or in a PDF. The overview should be contained in a one or two-paragraph statement.

ER Diagram Document

The document containing the ER Diagram, this file must be submitted in PDF.

Relational Schema Document

The document containing Relational Schema, this file must be submitted in either an Excel Spreadsheet format, Microsoft Word format, or in a PDF

Metadata Document

The document containing the Metadata, this file must be submitted in either a Microsoft Excel format, a Microsoft Word format, or a PDF.

Document Containing Planned Test Data

The recommended format is an Excel Workbook with the set of data for each table contained in a separate worksheet in the workbook. Students can optionally use a Word Processor Table or third-party software. This file must be submitted in either a Microsoft Excel format, a Microsoft Word format, or a PDF.

SQL Script to CREATE the Database, USE the Database, CREATE the Tables, and INSERT data INTO the Tables.

An SQL Text File with a SQL Extension containing the Following SQL Script

SQL Script to Create the Database and Database Tables
SQL Script to INSERT the Test Data into the Database Tables
SQL Script to perform the 15 activities listed and described in Part II of the Final Project Requirements

ALL SQL Script must be is a Single SQL Text File that Runs Continuously

The SQL Script listed above should be contained in a single SQL File that can be run and executed continuously without errors. The script that does not run continuously and has errors will not be counted towards points in the Final Project Assignment Grading. All SQL Script must be combined into a Single SQL Text File with an SQL Extension

Reminder SQL Script must be in One File

The main three requirements for the SQL Script for the Final Project is that all SQL Scrip should follow these guidelines

The script should be well documented with comments.
All the scripts should be placed in one file.
The script should run continuously without errors.

Syntax Errors in SQL Statements may cause errors in the following statements causing them to be evaluated as being incorrect relies.

The sequence of Statements in a Single SQL Script File

You should combine all SQL scripts in a single file in order of importance. This means the basic sequence of SQL Code should be in the following sequence.

DROP DATABASE IF EXIST Statement
CREATE DATABASE Statements
Use Database Statements
CREATE TABLE Tables in correct order to maintain referential integrity.
INSERT INTO Statements in correct order to maintain referential integrity.
List of Individual Queries in the correct order for them to work correctly.
Comments Describing each set of queries responding to a Part II Query Request.

There are 12 different Categories of Queries that are a part of Part II of the Final Project. Make sure that you have responded to each of the 12 categories. Categories that were not responded to should be commented on using a sequence of line comments or beginning and ending block comments.

Extra Credit Requirements

Students participating in the Extra Credit Segment of the Final Project should include the Extra Credit script after the Regular Mandatory Final Project SQL Script.

If Working in a Group

For the individual segment of the assignment, each group member is required to write the required queries to a combination of tables that no other group member of the group is working on individually. Some of the tables that different group members are using may overlap but not all of them and not the Primary Entity Tables. This means that two people in the same group cannot work on the same combination of tables. My recommendation is that each group member should take one or more entities tables that no other team is working on and connect them to one or more other relationship tables or entity tables in the project database. A framework like this will make sure that each group member is working on a unique combination of tables in their individual segment of the final project.