HS2021- Database Design and Use - SQL - FK Errors - PK Errors - Constraints - Database Analysing Assignment Help

Assignment Task

1. Section 1
A database analyst has developed the following ER Diagram:

Create a script file named ASS1_SQL.TXT
Add your Student ID and Student name the first lines of the script file.
1.1. Relationships
Write the English sentences that best describe the ERD above.
a. Place the text in the specified location in the file: ASS1_SQL.TXT
b. Prefix each line with comment symbols -- (two hyphens)
    E.g.: ONE Employee MUST belong to ONE Branch
    ONE Branch MAY employ to MANY Employees

    ONE Branch MUST belong to ONE Organisation
    ONE Organisation MAY employ to MANY Branches
1.2.Drop Tables
Write SQL DROP statements that will drop all the tables.
Add these statements to the appropriate location within the script file.
1.3. Create Tables
Write SQL CREATE TABLE statements to create all the tables.
Add these statements to the appropriate location within the script file.
Note:
a. All tables must have primary keys.
b. All tables must have appropriate foreign key constraints.
c. Each foreign key column must have identical column name, data type and size of the primary key that it refers to
d. Add any NOT NULL constraints as dictated by the ERD
e. The following columns data types and sizes must be used

The following constraints must be used:

1.4. Insert Customers
Write SQL INSERT statements that add the data shown to the CUSTOMER table.
Add these statements to the appropriate location within the script file.

1.5. Check constraint error
Write SQL INSERT statements that attempt to add the data shown to the CUSTOMER table.
This statement must fail due to check constraints.
Add these statements to the appropriate location within the script file.

1.6. Insert Products
Write SQL INSERT statements that add the data shown to the PRODUCT table.
Add these statements to the appropriate location within the script file.

1.7. Check constraint error
Write SQL INSERT statements that attempt to add the data shown to the PRODUCT table. This statement must fail due to check constraints. Add these statements to the appropriate location within the script file.

1.8. Insert Salespersons
Write SQL INSERT statements that add the data shown to the SALESPERSON table. Add these statements to the appropriate location within the script file.

1.9. Insert Shop Orders
Write SQL INSERT statements that add the data shown to the SHOP ORDER table.
Add these statements to the appropriate location within the script file.
Customer 1 Orders:

1.10. FK Error
Write these SQL INSERT statements that attempt to add the data shown to the SHOP ORDER table. These statements must fail. If they don't fail, there is a problem with your Foreign Key constraint clause in your Create Table statement. Add these statements to the appropriate location within the script file.

1.11. Insert Oder Lines
Write SQL INSERT statements that add the data shown to the ORDERLINE table. Add these statements to the appropriate location within the script file.

1.12. FK errors
Write these SQL INSERT statements that attempt to add the data shown to the ORDERLINE table. These statements must fail. If they don't fail, there is a problem with your Foreign Key constraint clause in your Create Table statement. Add these statements to the appropriate location within the script file.

1.13. List rows in all Tables
Write five SQL statements that will list all of the rows in all tables in ascending primary key sequence. Add these statements to the appropriate location within the script file.

2. Section 2
Add each statement to the appropriate location within the script file.
2.1.1 Count the total number of orders in the SHOP ORDER table.
2.1.2 Count the total number of orders that have been made by female customers.
2.1.3 Count the total number of orders that have been made by each gender of the customer.
2.1.4 List the order id, customer id, first name & surname for all shop orders where the
customer is female. List in ascending customer id/order id sequence
2.1.5 List order id, customer id, first name & surname, product id and quantity sold for all
rows in the ORDERLINE table. List in ascending customer id/order id/product id
sequence.

3. Section 3
The database analyst has modified the existing ER Diagram:

Each product is assigned to one or more managers.
It is the responsibility of a manager to perform a quality check once a week on each product that they have been assigned to.
For each quality check performed by a manager, the week no and score (a value between 1 and 3) is recorded.
3.1.Drop Tables
Write SQL DROP statements that will drop all the additional tables. Add these statements to section 1.2 of the script file.
3.2. Create Tables
Write SQL CREATE TABLE statements to create all additional tables. Add these statements to the appropriate location within the script file.
Note:
a. All tables must have primary keys.
b. All tables must have appropriate foreign key constraints.
c. Each foreign key column must have identical column name, data type and size of the primary key that it refers to
d. Add any NOT NULL constraints as dictated by the ERD
e. Choose your own appropriate column data types and sizes

3.3. List all rows in additional Tables
Write three SQL statements that will list all of the rows from all additional tables in ascending
primary key sequence. Add these statements to the appropriate location within the script file.

4. Section 4:
Write these SQL SELECT statements.
Add each statement to the appropriate location within the script file.
4.1. List the order id, product id, product name, current price, sale price and the price difference for all products that appear in the ORDERLINE table. Only list rows where the price difference is greater than 10% of the product's current selling price. List in ascending price difference sequence.
4.2. Use a Union clause to list the names of all customers, salespersons and managers in surname / firstname sequence. Show the role of each person.
4.3. Use a UNION clause to list each order id and address. The list must be in ascending Order ID sequence. If the Shop Order delivery address is NULL, then you must display the customer's billing address instead of the delivery address.
4.4. List the order id, customer id, first name & surname, product id & name for all rows in the ORDERLINE table. List in ascending customer id / order id / product id sequence.
4.5. List the product id, name and current price of all products that have a selling price greater than the average current price of all products. List in ascending current price sequence

5. Section 5:
5.1. For each row in the SHOP ORDER table, show the customer id & surname and order id. Ensure that every customer is the CUSTOMER table is listed – even if they have not created any shop orders. List in ascending customer id/order id sequence.
5.2. Count the number of shop orders for each customer. Ensure that every customer is the CUSTOMER table is listed – even if they have not created any shop orders. List in ascending customer id.
5.3. List order id, customer id, first name & surname, product id and quantity sold for all rows in the ORDERLINE table. List in ascending customer id/order id/product id sequence.
5.4. List the product id, product name and total quantity sold for all products that appear in the ORDERLINE table. List in ascending total sequence/product id.
5.5. Same as above, but only show products where total sales are in the range 2 to 10 (inclusive)
5.6. Same as above, but only show products where total sales are greater than 1 that contain the word 'Lamp' or 'Table' in the product name.
5.7. List the average score in the QUALITY CHECK table for each product. Only show results if the average score is greater than or equal to 2.0. List an ascending average score sequence.
5.8. List the total orders for products with an average quality check score greater than or equal to 2.0. Do not 'hard code' product ids 33 and 31 in your query as obviously data values will change over time.

This HS2021- Computer Science Assignment has been solved by our Computer Science Experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.