Over the past few weeks, you have gained a general understanding of designing databases and how...

Transcribed Image Text:

Over the past few weeks, you have gained a general understanding of designing databases and how to use SQL to retrieve information from business data. This assignment allows you to demonstrate what you have learned. Academic Integrity Plagiarism occurs when you use words, ideas, or work products attributable to another identifiable person or source: - without attributing the work to the source from which it was obtained in a situation in which there is a legitimate expectation of original authorship in order to obtain some benefit, credit, or gain which need not be monetary Collusion is a form of cheating which occurs when people work together in a deceitful way to develop a submission for an assessment which has been restricted to individual effort. By submitting this piece of work and signing this document, I declare that: 1. The work is my own individual work. 2. I have not previously submitted all or part of this work for assessment in any subject, unless the subject coordinator for the current subject (or my research supervisor, if applicable) has given me written permission to reuse specific material and I have correctly referenced the material taken from my own earlier work. 3. I have read and agree to be bound by the Statutes, Regulations and Policies of the University relating to Academic Integrity available at http://www.latrobe.edu.au/students/academic- integrity; and 4. I may be subject to student discipline processes in the event of an act of academic misconduct by me including an act of plagiarism or cheating. I further grant to the University or any third party authorised by the University (www.latrobe.edu.au/text-match) the right to reproduce and/or communicate (make available online or electronically transmit) the work I have submitted for the purpose of detecting plagiarism. Do Part 1: Database design Investigate the following table recording the sales information of a shop and answer the following questions. Order Number Product Name 1001 MacBook Pro 1002 T-Shirt 1003 iPhone 12 1004 MacBook Pro 1004 Hat 1005 AirPods Pro Answer: Customer Name Bob Johnson Jane Doe John Smith Jane Doe Jane Doe John Smith . Order 1 4/01/2023 Sally Product Product Product Category Price Quantity Date Seller Electronics $1,299 2 3/01/2023 Tom Clothing Electronics Electronics Accessory Electronics $40 $999 $1,299 $20 1 5/01/2023 Tom. 1 5/01/2023 Tom 1 5/01/2023 Tom $249 2 7/01/2023 Sally Customer Email Customer Phone Color bob@hotmail.com 0456988332 jane@yahoo.com 0453249987 john@gmail.com 0439394825 jane@yahoo.com 0453249987 jane@yahoo.com 0453249987 john@gmail.com 0439394825 Question 1 [12 marks] Will this table satisfy 1NF, 2NF, and 3NF? Explain why. Give examples for insertion anomaly, deletion anomaly and update anomaly that may happen when using this table to record data. Grey Red Green Red Blue White It satisfies 1NF because all attributes are atomic (no multi-valued attributes). It does not satisfy 2NF because Ba A It does not satisfy 2NF because It does not satisfy 3NF Insertion anomaly: can't record a new product category if product name is not added / If a customer has not made an order yet, their information cannot be recorded in the table. Deletion anomaly: deleting the order ID will delete the customer name Bob Johnson and his entire entries for the product. Update anomaly: a change of seller having product would require multiple updates in pricing. Question 2 [14 mark] Using https://erdplus.com, let's redesign it into two or more tables in the third normal form. You are asked to: a) (8 marks) Present your entity-relationship diagram (ERD). b) (3 marks) Present the generated relational schema. The primary key and possible foreign keys of each table should be shown. c) (3 marks) Present the generated SQL statements to create the tables with the appropriate data type set for each attribute. Part 2: Database retrieval using SQL We will make use of the data contained in bus5dwr.BookAvenue schema within the Imban- teaching.database.windows.net. Please refer to Workshop 1 for instructions on how to access the server via DBeaver if you have not accessed it already. BookAvenue Tables > > > > > authors publishers sales stores titleauthor > > > Procedures > Sequences > titles External Tables Views Indexes Synonyms Triggers Data Types >bus5dwr_social_network > CoVid19 sales acorderID ac titlelD storelD order Date 123 qty payterms stores storelD storeName nostoreAdd city no state se zip titles no titleID title acbookType noc pubID 123 price 123 advance 123 royalty noc notes pubdate 123 rating publishers pubID pub_name ADC not city noc state country titleauthor not authorID titleID 123 authorOrder 123 royaltyper authors caulD noclastName noc firstName phone AC address as city no state asc zip 123 contract Start by exploring the contents of the tables to understand the meaning of the attributes of each table. Question 3 [4 mark] State the relationships between the tables to show the constraints imposed in the database. Answer: Question 4 [30 marks]: The manager wants a better insight into the company business to support his future decisions. He requests the IT department to provide answers to the following questions. You are asked to write SQL code to find the answer for each question. Fill in below the code in text and screenshot the result table (limit to the first 8 rows and must show the number of fetched rows and time). Result of one question must be shown in only one return table. Example (No marks) Question Code Display the books that have their price greater than 10. SELECT t.title, t.bookType, price FROM BookAvenue.titles t WHERE t.price > 10 Returned Results 1 x table Text Grid SELECT t.title, t.bookType.Enter a SQL expression to filter results 123 Record title bookType 1 The Busy Executive's Database Guide business 2 Cooking with Computers: Surreptition business 3 Straight Talk About Computers business 4 Silicon Valley Gastronomic Treats 5 The Psychology of Computer Cookin 6 But Is It User Friendly? 7 Secrets of Silicon Valley Net Etiquette Refresh 200 8 1@Save 14 mod_cook popular comp popular comp popular comp popular comp price 19.9900 11.9500 19.9900 19.9900 20.9500 22.9500 20.0000 15.9900 Canc EK >Export data 14 row(s) fetched - 65ms (1ms fetch), on 2023-03-18 at 14:25:49 (Adjust your screen to include the yellow line in the screenshot.) 4.1. (2 marks) How many USA publishers the company have bought books from? SELECT p.country ,p.pub_name From BookAvenue.publishers p WHERE country Question Code Returned table 4.2. Results 1 X T SELECT p.country. 3 Record T Text 1 23456 = 'USA' (2 marks) noc country USA USA USA USA USA USA Refresh 1, Export data 7:22 [83] Enter a SQL expression to filter res noc pub_name New Moon Books Binnet & Hardley Algodata Infosystems Five Lakes Publishing Ramona Publishers Scootney Books Save 200 Sel: 8313 Concel K < < < > > @ Panels R 17 Question Display author names and book titles of authors who live in Oakland city. 0 Code Returned table 4.3. Question How many distinct books sold in 2012 that have either the word 'recipe' or 'cook' in their notes? Display also the total sold quantity. Code (3 marks) Returned table 4.4. Code (3 marks) Question Display store name, year and its annual sales in descending order of both year and annual sales. Returned table 4.5. Question Calculate the quarter sales of 'business' books. Results must have three columns that named as year, quarter, total quantity, and total sales, and displayed in time order. Code (3 marks) Returned table 4.6. (3 marks) Question What are the two best selling book type of 2022? Code 4.7. Returned table (4 marks) Question Proposed criterion Justification Code Returned table 4.8. The company wants to choose two publishers to order more books from them that can improve the company sales. To help the manager choose these publishers, you are required to propose a criterion/criteria with justification, and write code to find the answer. Your answer must not be an empty table. (3 marks) Question Based on the answer of Question 4.7, display the store that have sold the highest quantity of books from these two publishers. Code Returned table 4.9. (4 marks) 4.9. (4 marks) Question Proposed criterion Justification Code Returned table 4.10. Based on the answer of Question 4.7, the company wants to know the most popular authors of these publishers. You are required to propose a criterion/criteria to define "the most popular authors" with justification, and write code to find the answer. Your answer must not be an empty table. (3 marks) Question Based on the answer of Question 4.9, what are the book types that these authors have written? Code Returned table Submission Guide Your submission is this WORD file with all the questions answered. The SQL code has to be copied as text (not an image or screenshot) so that the markers can immediately re-run and check your answers. Marks will not be given if the code is not provided in plain text. Submissions with high similarity in the SQL code with another submission, especially having the same wrong code, will be considered plagiarism/collusion and will be reported to the Academic Integrity Advisors (AIA) for further investigation. Marking rubrics The following marking guide will be used by the marker in assessing your work. Please have a look to understand what you need to cover for each question in this assignment. Questions 1 to 3: Full marks for the correct answer, which addressed all the questions. Half the mark for something very close to the correct answers. You will also lose marks if your answer is unclear, e.g. text in your ERD screenshots are invisible. Question 4: Full marks for faultless SQL statements (i.e., correct code to answer the question and can be run on our device without errors to return a correct returned table). Half the mark for something very close to the correct answers. You will also lose marks if your answer is unclear. Over the past few weeks, you have gained a general understanding of designing databases and how to use SQL to retrieve information from business data. This assignment allows you to demonstrate what you have learned. Academic Integrity Plagiarism occurs when you use words, ideas, or work products attributable to another identifiable person or source: - without attributing the work to the source from which it was obtained in a situation in which there is a legitimate expectation of original authorship in order to obtain some benefit, credit, or gain which need not be monetary Collusion is a form of cheating which occurs when people work together in a deceitful way to develop a submission for an assessment which has been restricted to individual effort. By submitting this piece of work and signing this document, I declare that: 1. The work is my own individual work. 2. I have not previously submitted all or part of this work for assessment in any subject, unless the subject coordinator for the current subject (or my research supervisor, if applicable) has given me written permission to reuse specific material and I have correctly referenced the material taken from my own earlier work. 3. I have read and agree to be bound by the Statutes, Regulations and Policies of the University relating to Academic Integrity available at http://www.latrobe.edu.au/students/academic- integrity; and 4. I may be subject to student discipline processes in the event of an act of academic misconduct by me including an act of plagiarism or cheating. I further grant to the University or any third party authorised by the University (www.latrobe.edu.au/text-match) the right to reproduce and/or communicate (make available online or electronically transmit) the work I have submitted for the purpose of detecting plagiarism. Do Part 1: Database design Investigate the following table recording the sales information of a shop and answer the following questions. Order Number Product Name 1001 MacBook Pro 1002 T-Shirt 1003 iPhone 12 1004 MacBook Pro 1004 Hat 1005 AirPods Pro Answer: Customer Name Bob Johnson Jane Doe John Smith Jane Doe Jane Doe John Smith . Order 1 4/01/2023 Sally Product Product Product Category Price Quantity Date Seller Electronics $1,299 2 3/01/2023 Tom Clothing Electronics Electronics Accessory Electronics $40 $999 $1,299 $20 1 5/01/2023 Tom. 1 5/01/2023 Tom 1 5/01/2023 Tom $249 2 7/01/2023 Sally Customer Email Customer Phone Color bob@hotmail.com 0456988332 jane@yahoo.com 0453249987 john@gmail.com 0439394825 jane@yahoo.com 0453249987 jane@yahoo.com 0453249987 john@gmail.com 0439394825 Question 1 [12 marks] Will this table satisfy 1NF, 2NF, and 3NF? Explain why. Give examples for insertion anomaly, deletion anomaly and update anomaly that may happen when using this table to record data. Grey Red Green Red Blue White It satisfies 1NF because all attributes are atomic (no multi-valued attributes). It does not satisfy 2NF because Ba A It does not satisfy 2NF because It does not satisfy 3NF Insertion anomaly: can't record a new product category if product name is not added / If a customer has not made an order yet, their information cannot be recorded in the table. Deletion anomaly: deleting the order ID will delete the customer name Bob Johnson and his entire entries for the product. Update anomaly: a change of seller having product would require multiple updates in pricing. Question 2 [14 mark] Using https://erdplus.com, let's redesign it into two or more tables in the third normal form. You are asked to: a) (8 marks) Present your entity-relationship diagram (ERD). b) (3 marks) Present the generated relational schema. The primary key and possible foreign keys of each table should be shown. c) (3 marks) Present the generated SQL statements to create the tables with the appropriate data type set for each attribute. Part 2: Database retrieval using SQL We will make use of the data contained in bus5dwr.BookAvenue schema within the Imban- teaching.database.windows.net. Please refer to Workshop 1 for instructions on how to access the server via DBeaver if you have not accessed it already. BookAvenue Tables > > > > > authors publishers sales stores titleauthor > > > Procedures > Sequences > titles External Tables Views Indexes Synonyms Triggers Data Types >bus5dwr_social_network > CoVid19 sales acorderID ac titlelD storelD order Date 123 qty payterms stores storelD storeName nostoreAdd city no state se zip titles no titleID title acbookType noc pubID 123 price 123 advance 123 royalty noc notes pubdate 123 rating publishers pubID pub_name ADC not city noc state country titleauthor not authorID titleID 123 authorOrder 123 royaltyper authors caulD noclastName noc firstName phone AC address as city no state asc zip 123 contract Start by exploring the contents of the tables to understand the meaning of the attributes of each table. Question 3 [4 mark] State the relationships between the tables to show the constraints imposed in the database. Answer: Question 4 [30 marks]: The manager wants a better insight into the company business to support his future decisions. He requests the IT department to provide answers to the following questions. You are asked to write SQL code to find the answer for each question. Fill in below the code in text and screenshot the result table (limit to the first 8 rows and must show the number of fetched rows and time). Result of one question must be shown in only one return table. Example (No marks) Question Code Display the books that have their price greater than 10. SELECT t.title, t.bookType, price FROM BookAvenue.titles t WHERE t.price > 10 Returned Results 1 x table Text Grid SELECT t.title, t.bookType.Enter a SQL expression to filter results 123 Record title bookType 1 The Busy Executive's Database Guide business 2 Cooking with Computers: Surreptition business 3 Straight Talk About Computers business 4 Silicon Valley Gastronomic Treats 5 The Psychology of Computer Cookin 6 But Is It User Friendly? 7 Secrets of Silicon Valley Net Etiquette Refresh 200 8 1@Save 14 mod_cook popular comp popular comp popular comp popular comp price 19.9900 11.9500 19.9900 19.9900 20.9500 22.9500 20.0000 15.9900 Canc EK >Export data 14 row(s) fetched - 65ms (1ms fetch), on 2023-03-18 at 14:25:49 (Adjust your screen to include the yellow line in the screenshot.) 4.1. (2 marks) How many USA publishers the company have bought books from? SELECT p.country ,p.pub_name From BookAvenue.publishers p WHERE country Question Code Returned table 4.2. Results 1 X T SELECT p.country. 3 Record T Text 1 23456 = 'USA' (2 marks) noc country USA USA USA USA USA USA Refresh 1, Export data 7:22 [83] Enter a SQL expression to filter res noc pub_name New Moon Books Binnet & Hardley Algodata Infosystems Five Lakes Publishing Ramona Publishers Scootney Books Save 200 Sel: 8313 Concel K < < < > > @ Panels R 17 Question Display author names and book titles of authors who live in Oakland city. 0 Code Returned table 4.3. Question How many distinct books sold in 2012 that have either the word 'recipe' or 'cook' in their notes? Display also the total sold quantity. Code (3 marks) Returned table 4.4. Code (3 marks) Question Display store name, year and its annual sales in descending order of both year and annual sales. Returned table 4.5. Question Calculate the quarter sales of 'business' books. Results must have three columns that named as year, quarter, total quantity, and total sales, and displayed in time order. Code (3 marks) Returned table 4.6. (3 marks) Question What are the two best selling book type of 2022? Code 4.7. Returned table (4 marks) Question Proposed criterion Justification Code Returned table 4.8. The company wants to choose two publishers to order more books from them that can improve the company sales. To help the manager choose these publishers, you are required to propose a criterion/criteria with justification, and write code to find the answer. Your answer must not be an empty table. (3 marks) Question Based on the answer of Question 4.7, display the store that have sold the highest quantity of books from these two publishers. Code Returned table 4.9. (4 marks) 4.9. (4 marks) Question Proposed criterion Justification Code Returned table 4.10. Based on the answer of Question 4.7, the company wants to know the most popular authors of these publishers. You are required to propose a criterion/criteria to define "the most popular authors" with justification, and write code to find the answer. Your answer must not be an empty table. (3 marks) Question Based on the answer of Question 4.9, what are the book types that these authors have written? Code Returned table Submission Guide Your submission is this WORD file with all the questions answered. The SQL code has to be copied as text (not an image or screenshot) so that the markers can immediately re-run and check your answers. Marks will not be given if the code is not provided in plain text. Submissions with high similarity in the SQL code with another submission, especially having the same wrong code, will be considered plagiarism/collusion and will be reported to the Academic Integrity Advisors (AIA) for further investigation. Marking rubrics The following marking guide will be used by the marker in assessing your work. Please have a look to understand what you need to cover for each question in this assignment. Questions 1 to 3: Full marks for the correct answer, which addressed all the questions. Half the mark for something very close to the correct answers. You will also lose marks if your answer is unclear, e.g. text in your ERD screenshots are invisible. Question 4: Full marks for faultless SQL statements (i.e., correct code to answer the question and can be run on our device without errors to return a correct returned table). Half the mark for something very close to the correct answers. You will also lose marks if your answer is unclear.