Introduction to databases FALL 20 Assignment III Based on the database schema that was used in assignment I, answer the following questions: 1. Write the business rules that are reflected by the schema. 2. Create a Crow's Foot ERD that represent the above business rules. Write all appropriate connectivities and cardinalities in the ERD.[2] 3. Write your answers in a document and save it as assignment3_yourID. Submit your file via Blackboard on or before [3] Assignment 1 Description of the schema The following database is used by a building materials company to edit invoices for its customers and to manage its inventory. The schema of this database contains the following tables: The customer table is used to record the properties of the company's customers: name, address, etc. The product table is used to record the properties concerning the building materials available for sale: their description, the unit price, the quantity in stock. The order table is used to record the properties concerning an order: its number, the date of the order and the customer identifier. > The orderedProduct table is used to record the "lines" of orders along with the products: the order identifier (order NO), the product identifier (productNO) and the quantity ordered. Customer (customerNO, customerName, address, city, category, custBalance) Product (productNO. label, price, QStock) customerOrder (orderNo. order Date, #customer NO) ordered Product (HorderNO. #productNO orderuantity Note: Primary keys are underlined and foreign key are marked with #. Use SQL Developer to answer the questions given below: 1) Open a new SQL worksheet then write the SQL commands to create the tables' structures of the above database (enforce primary key and foreign key constraints). Save this script Schema_creation.sql.[2.5] Customer Product customer Order customerNO char(8) productNO char(10) order NO char(8) customer Name Varchar (20) label Varchar (30) order Date Date address Varchar (40) price Decimal(9.2) customer NO char(8) city Varchar(20) QStock Number(11) category Char(2) ordered Product custBalance Decimal(9,2) order NO char(8) productNO char(10) order Quantity Number(11) 2) Open a new SQL worksheet then write the INSERT commands necessary to place the following data in the tables that were created in the previous question Save this script as table population.sql. (2.5] B2 Customer customerNO customerName address city Category custBalance B062 Ahmed 72 street Freedom Salalah B2 -100 B112 Salim Street Sultan Qaboos Muscat C1 1250 B332 Alia Building Hana Street Alhisn Ibri B2 0.00 B512 Taoufiq Building Alaamra Rustaq B1 -500 C003 Islam House N 5 Street Asahafa Salalah B1 -1000 C123 Murad Building Asalalm Main Street Ibri ci -2000 C400 Jamilah Street of garden House n 5 Muscat 350 Product customer Order productNO label Price QStock order NO order Date customerNO CS262 Chev.fir 200x6x2 75 45 30178 21-DEC-2017 C400 CS264 Chev fir 200x6x4 120 2690 30179 22-DEC-2017 B512 CS464 Chev.fir 400x6x4 220 450 30182 23-DEC-2017 C400 PA45 Steel point 45 (1K) 105 580 30184 23-DEC-2017 B062 PA60 Steel point 60 (1K) 95 134 30185 02-JAN-2018 B332 PH222 PL Hetre 200x20x2 230 782 30186 02-JAN-2018 C400 PS222 PL.fir 200x20x2 185 1220 30188 03-JAN-2018 C003 ordered Product order NO productNO order Quantity 30178 CS464 25 30179 CS262 60 30179 PA60 20 30182 PA60 30 30184 CS464 120 30184 PA45 20 30185 CS464 260 30185 PA60 15 30186 PS222 600 30186 PA45 3 30188 PA60 180 30188 PH222 92 3) Open a new SQL worksheet then write in SQL the queries, which answer the questions below. Save the script as queries.sql. (write the number of question as comment before each query) a) Display in alphabetical order the list of cities in which there is at least one customer [0.5] b) Display the number, name and city of customers who belong to category C1 and who are not living in Muscat.[0.5] c) List the characteristics of products made of steel. [0.5] d) Display the details of products having a price under 200 and a stock more than 150.[0.5] e) List the number, name and balance of customers who are from Salalah or Ibri and whose balance is positive.[0.5] f) List the number of cities in which there are customers of category C1.[0.5] g) Display the number of customers who have placed at least one order. [0.5] h) Calculate, for each city, the number of distinct categories. [0.75] 1) Assign zero to the balance of customers of category Cl and whose balance is negative. [0.75] 1) Change to 10 units the ordered quantity of the product number PA60 in the order number 30185.[0.75] k) Calculate the total amount in the stock of products made of fir. [0.75] 1) Display the details (orderNO, order Date ) of the most recent order. [1] m) Display the details of orders (order No. order Date, customerNO ) which doesn't include the products having the code PA60.[1] n) Display numbers and names of customers who are from Muscat and have not placed orders. [1] .) Display numbers of products (productNO) which are made of fir and are placed in orders. p) Find for each product the list of cities in which this product is ordered in more than 500 units [1.5) q) List, for each customer category, the number of products that were ordered on 23-12- 2008.[1.5] 4) Open a new SQL worksheet then write a SQL statement to create a view named 'orderPer Client' which will contain the number of orders placed by each customer. Save this script as view_creation.sql [1.5] 5) Create a new folder named assignmenti_your ID. Put in this folder all the scripts created in the above questions. Compress the folder then submit it via Blackboard on or before cswebdav/pid-142004-dt-content-rid-749269_1/courses/ITDR2106IBR_20FL_30/Project_ITDR2106_FALL 20%20%281 + A Fit to page D Page view Introduction to databases FALL 20 Assignment II 1) Open SQL Developer then load the Schema_creation.sql and table_population.sql scripts to create and populate the tables discussed in assignment 1 Open a new SQL worksheet then write the PL/SQL code that creates the following objects. 2) Create in PL/SQL a stored procedure called "Del_Details' to delete the record of orderedProduct table for which it receives as input the order number (Order NO) and the product number (productNO) [15] 3) Create in PL/SQL a stored Function called 'productPerOrder' to return the number of products placed for any order its nurnber is given as input [1.5] 4) Write a Trigger called "changeCustomer_Category. This trigger should be invoked before any update of customer balance in customer table. Its job is to modify the category of a customer from B2 to B1 and from C2 to Ci when a customer's balance status drops below a certain threshold (-10000).[2] 3) Save the script as assignment2_your ID then submit this file via Blackboard on or before Introduction to databases FALL 20 Assignment III Based on the database schema that was used in assignment I, answer the following questions: 1. Write the business rules that are reflected by the schema. 2. Create a Crow's Foot ERD that represent the above business rules. Write all appropriate connectivities and cardinalities in the ERD.[2] 3. Write your answers in a document and save it as assignment3_yourID. Submit your file via Blackboard on or before [3] Assignment 1 Description of the schema The following database is used by a building materials company to edit invoices for its customers and to manage its inventory. The schema of this database contains the following tables: The customer table is used to record the properties of the company's customers: name, address, etc. The product table is used to record the properties concerning the building materials available for sale: their description, the unit price, the quantity in stock. The order table is used to record the properties concerning an order: its number, the date of the order and the customer identifier. > The orderedProduct table is used to record the "lines" of orders along with the products: the order identifier (order NO), the product identifier (productNO) and the quantity ordered. Customer (customerNO, customerName, address, city, category, custBalance) Product (productNO. label, price, QStock) customerOrder (orderNo. order Date, #customer NO) ordered Product (HorderNO. #productNO orderuantity Note: Primary keys are underlined and foreign key are marked with #. Use SQL Developer to answer the questions given below: 1) Open a new SQL worksheet then write the SQL commands to create the tables' structures of the above database (enforce primary key and foreign key constraints). Save this script Schema_creation.sql.[2.5] Customer Product customer Order customerNO char(8) productNO char(10) order NO char(8) customer Name Varchar (20) label Varchar (30) order Date Date address Varchar (40) price Decimal(9.2) customer NO char(8) city Varchar(20) QStock Number(11) category Char(2) ordered Product custBalance Decimal(9,2) order NO char(8) productNO char(10) order Quantity Number(11) 2) Open a new SQL worksheet then write the INSERT commands necessary to place the following data in the tables that were created in the previous question Save this script as table population.sql. (2.5] B2 Customer customerNO customerName address city Category custBalance B062 Ahmed 72 street Freedom Salalah B2 -100 B112 Salim Street Sultan Qaboos Muscat C1 1250 B332 Alia Building Hana Street Alhisn Ibri B2 0.00 B512 Taoufiq Building Alaamra Rustaq B1 -500 C003 Islam House N 5 Street Asahafa Salalah B1 -1000 C123 Murad Building Asalalm Main Street Ibri ci -2000 C400 Jamilah Street of garden House n 5 Muscat 350 Product customer Order productNO label Price QStock order NO order Date customerNO CS262 Chev.fir 200x6x2 75 45 30178 21-DEC-2017 C400 CS264 Chev fir 200x6x4 120 2690 30179 22-DEC-2017 B512 CS464 Chev.fir 400x6x4 220 450 30182 23-DEC-2017 C400 PA45 Steel point 45 (1K) 105 580 30184 23-DEC-2017 B062 PA60 Steel point 60 (1K) 95 134 30185 02-JAN-2018 B332 PH222 PL Hetre 200x20x2 230 782 30186 02-JAN-2018 C400 PS222 PL.fir 200x20x2 185 1220 30188 03-JAN-2018 C003 ordered Product order NO productNO order Quantity 30178 CS464 25 30179 CS262 60 30179 PA60 20 30182 PA60 30 30184 CS464 120 30184 PA45 20 30185 CS464 260 30185 PA60 15 30186 PS222 600 30186 PA45 3 30188 PA60 180 30188 PH222 92 3) Open a new SQL worksheet then write in SQL the queries, which answer the questions below. Save the script as queries.sql. (write the number of question as comment before each query) a) Display in alphabetical order the list of cities in which there is at least one customer [0.5] b) Display the number, name and city of customers who belong to category C1 and who are not living in Muscat.[0.5] c) List the characteristics of products made of steel. [0.5] d) Display the details of products having a price under 200 and a stock more than 150.[0.5] e) List the number, name and balance of customers who are from Salalah or Ibri and whose balance is positive.[0.5] f) List the number of cities in which there are customers of category C1.[0.5] g) Display the number of customers who have placed at least one order. [0.5] h) Calculate, for each city, the number of distinct categories. [0.75] 1) Assign zero to the balance of customers of category Cl and whose balance is negative. [0.75] 1) Change to 10 units the ordered quantity of the product number PA60 in the order number 30185.[0.75] k) Calculate the total amount in the stock of products made of fir. [0.75] 1) Display the details (orderNO, order Date ) of the most recent order. [1] m) Display the details of orders (order No. order Date, customerNO ) which doesn't include the products having the code PA60.[1] n) Display numbers and names of customers who are from Muscat and have not placed orders. [1] .) Display numbers of products (productNO) which are made of fir and are placed in orders. p) Find for each product the list of cities in which this product is ordered in more than 500 units [1.5) q) List, for each customer category, the number of products that were ordered on 23-12- 2008.[1.5] 4) Open a new SQL worksheet then write a SQL statement to create a view named 'orderPer Client' which will contain the number of orders placed by each customer. Save this script as view_creation.sql [1.5] 5) Create a new folder named assignmenti_your ID. Put in this folder all the scripts created in the above questions. Compress the folder then submit it via Blackboard on or before cswebdav/pid-142004-dt-content-rid-749269_1/courses/ITDR2106IBR_20FL_30/Project_ITDR2106_FALL 20%20%281 + A Fit to page D Page view Introduction to databases FALL 20 Assignment II 1) Open SQL Developer then load the Schema_creation.sql and table_population.sql scripts to create and populate the tables discussed in assignment 1 Open a new SQL worksheet then write the PL/SQL code that creates the following objects. 2) Create in PL/SQL a stored procedure called "Del_Details' to delete the record of orderedProduct table for which it receives as input the order number (Order NO) and the product number (productNO) [15] 3) Create in PL/SQL a stored Function called 'productPerOrder' to return the number of products placed for any order its nurnber is given as input [1.5] 4) Write a Trigger called "changeCustomer_Category. This trigger should be invoked before any update of customer balance in customer table. Its job is to modify the category of a customer from B2 to B1 and from C2 to Ci when a customer's balance status drops below a certain threshold (-10000).[2] 3) Save the script as assignment2_your ID then submit this file via Blackboard on or before