Task 1: Submit a work proposal for this assignment or before 13/06/2020 (23:59) which must include: Understanding of deliverables -a detail description of deliverables. (3 Marks) General overview of proposed plan-initial understanding of solution of all tasks. (3 Marks) Timeline and resources required for completion of the given tasks. (4 Marks) The work proposal must be submitted in a word file through the link available in Moodle. MEC_AMO_TEM_034_01 Page 1 of 11 Fundamentals of Relational Database Management Systems (COMP 20002) - Spring - 2020 -CW Assignment2 - ALL-QP (10 Marks) Task 2: You have been assigned as a database administrator in an organization. Explain your role as a database administrator and compare it with data administrator in insuring good security for the organization's data. Write your reflection with concepts adopted from literature. Conference papers, academic articles, text & reference books can be included in the literature. (20 Marks) Task 3: Write the appropriate SQL statement for the following: (30 Marks) Table name: Brand Column Name Data Type Constraint Brandid Integer Primary key BrandName Varchar Not null Brandcountry Varchar Not null Brandcategory varchar Table Name: Dress Column Name Data Type Constraint DressID Integer Primary key DressName varchar Not null DressType varchar Not null Sell Price Numeric(10,2) Fundamentals of Relational Database Management Systems (COMP 20002) - Spring - 2020 -CW Assignment2 - ALL-QP Quantity Integer Should be greater than 0 Brandid Integer References to Brand table 1. Create brand and dress tables as per the above given structure. (6 marks) 2. Display the total number of dresses for each type whose total number is less than 10. (6 marks) 3. Increase the selling price by 10% of all the dresses belonging to Brandid 117 and 119. (6 marks) 4. Create a view to display the ID and name of all dresses of type Cotton and Polimer and whose quantity is less than 10. Display the result in alphabetical order of the IDs (6 marks) 5. Write a subquery that will display the Dress ID, Dress type, Brand country, Brand category of all brands whose sell price is greater than the average sell price. (6 marks) Task 4: Consider the tables given below and write the relational Algebra for the following tasks: (30 Marks) Table Name: Products Products Product Name Unit Price Supplier 1 Chais 10 boxes x 20 bags 18 1 2 Chang 24 - 12 oz bottles 19 1 3 Aniseed Syrup 12 - 550 ml bottles 10 1 4 ChefAnton's Cajun Seasoning 48-6 oz jars 22 2 MEC_AMO_TEM_034_01 Page 3 of 11 Fundamentals of Relational Database Management Systems (COMP 20002) - Spring - 2020-CW Assignment2 - ALL-QP Table Name: Suppliers Supplierid SupplierName ContactName Address city 1 Exotic Liquid 49 Gilbert St. Charlotte Cooper Londona 2 New Orleans Cajun Shelley Burke Delights P.O. Box 78934 New Orleans 3 Grandma Homestead Kelly's Regina Murphy 707 Oxford Rd. Ann Arbor 4 Tokyo Traders Yoshi Nagase Sekimai Tokyo Musashino-shi A. Write the Relational algebra for the following questions. (3x 6 Marks=18 Marks) i. Find all the information of products whose price is from 15 to 25 inclusively. ii. Find the lowest price of products for each supplier ID. ii. Find ID, name and address of suppliers whose city is Tokyo. B. Convert the following SQL statements to Relational Algebra expressions: (2x 6 Marks=12 Marks) i. Select From Products Where SupplieriD in (1,2,3); ii Select ProductID, ProductName From Products Where Price between 10 and 20; Task 5: In 300 words, write an individual reflective writing section that summarizes the online learning experience of this semester. (10 Marks) Task 1: Submit a work proposal for this assignment or before 13/06/2020 (23:59) which must include: Understanding of deliverables -a detail description of deliverables. (3 Marks) General overview of proposed plan-initial understanding of solution of all tasks. (3 Marks) Timeline and resources required for completion of the given tasks. (4 Marks) The work proposal must be submitted in a word file through the link available in Moodle. MEC_AMO_TEM_034_01 Page 1 of 11 Fundamentals of Relational Database Management Systems (COMP 20002) - Spring - 2020 -CW Assignment2 - ALL-QP (10 Marks) Task 2: You have been assigned as a database administrator in an organization. Explain your role as a database administrator and compare it with data administrator in insuring good security for the organization's data. Write your reflection with concepts adopted from literature. Conference papers, academic articles, text & reference books can be included in the literature. (20 Marks) Task 3: Write the appropriate SQL statement for the following: (30 Marks) Table name: Brand Column Name Data Type Constraint Brandid Integer Primary key BrandName Varchar Not null Brandcountry Varchar Not null Brandcategory varchar Table Name: Dress Column Name Data Type Constraint DressID Integer Primary key DressName varchar Not null DressType varchar Not null Sell Price Numeric(10,2) Fundamentals of Relational Database Management Systems (COMP 20002) - Spring - 2020 -CW Assignment2 - ALL-QP Quantity Integer Should be greater than 0 Brandid Integer References to Brand table 1. Create brand and dress tables as per the above given structure. (6 marks) 2. Display the total number of dresses for each type whose total number is less than 10. (6 marks) 3. Increase the selling price by 10% of all the dresses belonging to Brandid 117 and 119. (6 marks) 4. Create a view to display the ID and name of all dresses of type Cotton and Polimer and whose quantity is less than 10. Display the result in alphabetical order of the IDs (6 marks) 5. Write a subquery that will display the Dress ID, Dress type, Brand country, Brand category of all brands whose sell price is greater than the average sell price. (6 marks) Task 4: Consider the tables given below and write the relational Algebra for the following tasks: (30 Marks) Table Name: Products Products Product Name Unit Price Supplier 1 Chais 10 boxes x 20 bags 18 1 2 Chang 24 - 12 oz bottles 19 1 3 Aniseed Syrup 12 - 550 ml bottles 10 1 4 ChefAnton's Cajun Seasoning 48-6 oz jars 22 2 MEC_AMO_TEM_034_01 Page 3 of 11 Fundamentals of Relational Database Management Systems (COMP 20002) - Spring - 2020-CW Assignment2 - ALL-QP Table Name: Suppliers Supplierid SupplierName ContactName Address city 1 Exotic Liquid 49 Gilbert St. Charlotte Cooper Londona 2 New Orleans Cajun Shelley Burke Delights P.O. Box 78934 New Orleans 3 Grandma Homestead Kelly's Regina Murphy 707 Oxford Rd. Ann Arbor 4 Tokyo Traders Yoshi Nagase Sekimai Tokyo Musashino-shi A. Write the Relational algebra for the following questions. (3x 6 Marks=18 Marks) i. Find all the information of products whose price is from 15 to 25 inclusively. ii. Find the lowest price of products for each supplier ID. ii. Find ID, name and address of suppliers whose city is Tokyo. B. Convert the following SQL statements to Relational Algebra expressions: (2x 6 Marks=12 Marks) i. Select From Products Where SupplieriD in (1,2,3); ii Select ProductID, ProductName From Products Where Price between 10 and 20; Task 5: In 300 words, write an individual reflective writing section that summarizes the online learning experience of this semester. (10 Marks)