SIT772: Database and Information Retrieval Assessment 1: Database Design and Implementation Report This document supplies detailed information on...
This question has been answered
Question

document.pdfHi there, i am having some difficulty with the attached question 2, exercise 4 and 5. hope you are able to assist with how to word the sql query, i keep getting an error. This is what i have for exercise 5

Select customer1.Customer_ID, customer1.Customer_FName, customer1.Customer_LName

from customer1

join buying on customer1.customer_ID = buying.Customer_ID

JOIN product1 on product1.product_ID = buying.product_ID

where product1.Product_Address like '%Geelong%' or product1.product_name = 'Telephone';

Image transcriptions

SIT772: Database and Information Retrieval Assessment 1: Database Design and Implementation Report This document supplies detailed information on assessment tasks for this unit. Key information • • • Due: Sunday, 1 September 201 , 23:59 (AEST) Weighting: 30% Submit: Through CloudDeakin Learning Outcomes This assessment assesses the following Unit Learning Outcomes (ULO) and related Graduate Learning Outcomes (GLO): Unit Learning Outcome (ULO) Graduate Learning Outcome (GLO) ULO 2: Explain the concept of data modelling and use Entity-Relationship (ER) models to represent data. GLO 1: Discipline-specific knowledge and capabilities ULO 3: Design and implement relational database systems through the use of SQL GLO 1: Discipline-specific knowledge and capabilities GLO 4: Critical thinking Purpose This task requires students to apply their understanding and ability to use Relational Database Management Systems (RDBMS) as well as use SQL in the modelling of the physical world. Students will be provided with a set of business scenarios and are required to design a database and provide related SQL queries. Instructions This is an individual assessment task. Students are required to submit a written report that describes the design and implementation of a small database project. Students are also required to submit the associated SQL scripts. • • • • Read these instructions and the following four questions. Answer as many questions as possible. Place your name, ID and answers in your document. Please submit your Word file with your answers and graphs (embedded) where appropriate as a SINGLE document in the Submission Portal. Question 1 (5 marks) Problem statement: A shop has a list of electronic products to sell, which includes mobile phone, TV, Movie, etc.. Each product has a product number, a name, a price, a category, and a description. Each product is made by a single company only. Each company has an organization code, an organization name, an address, and telephone. People will visit the shop and purchase products. When a person makes an order, his/her name, address, phone number, and the ordered time will be recorded. The person will be given a unique customer id. A person can buy more than one products in one order. Exercise 1: Describe the elements (entities, attributes and relationships) of the table of this database system. Exercise 2: Draw the Entity Relationship Diagram (ERD) of the generated tables. Ó Deakin University 2019, CRICOS Provider Code: 00113B IT Help @ Deakin Page 1 of 6 Assessment 1: Database Design and Implementation Report Exercise 3: Write the SQL code to create the tables. Question 2 (10 marks): You are given the Customer, Buying, and Product tables in the database below: Customer ID Customer_FName Customer _Lname 10001 John Smith 10002 Dave Franklin 10003 Febby Johns 10004 Mary Gibson 10005 Glory Anson Product_ID Order_Time 10001 772 2016/09/01 10002 774 2015/08/02 10003 775 2017/04/15 10004 712 2016/09/28 10005 772 2019/07/05 Buying Customer_ID Product ID 772 Name Telephone Address 22 Ave, Burwood 774 Movie 33 Flower st, Burwood 775 Software Pack 2 Garden Rd, Geelong 712 Book 20 Ave, Burwood For each of the following exercises please provide a screenshot of SQL query execution. Exercise 1: Write the SQL query to create the above three tables, and insert the sample data into the tables. Exercise 2: Write the SQL query to find all customers who have bought a product in 2017 or earlier. List must include the customers' ID, names. Exercise 3: Write the SQL query to generate a list of all customers who have the letter 'o' in their name (first name/last name). List must include the customers' ID, names. Exercise 4: Write the SQL query to find customers who have bought products with their companies' address in 'Burwood'. List must include the customers' ID, names, product ID, product name and address. Exercise 5: Write the SQL query to show only customers who have purchased 'Telephone' or any product that has address in 'Geelong' Ó Deakin University 2019 IT Help @ Deakin Page 2 of 6 Assessment 1: Database Design and Implementation Report Question 3 (10 marks): Sales CUS_ID C001 C001 C075 C009 C009 C001 C001 Name Gold Gold Red Blue Blue Gold Gold Ord_NO O81 O81 O99 O56 O56 O88 O8 Ord_Date 15-Apr 15-Apr 16-Apr 16-Apr 16-Apr 17-Apr 1 -Apr Prod_ID P005 P004 P015 P033 P004 P015 P005 Description Chisel Plane Saw Punch Plane Saw Chisel Qty_Ord 6 14 3 24 9 10 Company A A B C A B Using the above Sales table structure, perform the following: Exercise 1: Write the relational schema for the above table, draw the dependency diagram, and identify all dependencies, including all partial and transitive dependencies. Provide the detailed explanation for your design and answers. You may assume that an order number references more than one product and that the table does not contain repeating groups. Exercise 2: Write the relational schemas after removing all partial dependencies and draw the new dependency diagrams. Identify the normal form for each created table structure. Provide the detailed explanation for your design and answers. Exercise 3: Write the relational schemas after removing all transitive dependencies and draw the new dependency diagrams. Identify the normal form for each created table structure. Provide the detailed explanation for your design and answers. Exercise 4: Draw the Entity Relationship Diagram (ERD) according to the result of Exercise 3. Provide the detailed explanation for your design. Question 4 (5 marks): You are given the following table structure with sample data in it. Gallery Customer History Form Customer Name Jackson, Elizabeth 123 - 4th Avenue Fonthill, ON L3J 4S4 Phone (206) 284-6783 Purchases Made Artist Title Purchase Date Sales Price 03 - Carol 15 - Dennis Frings 03 - Carol Channing 15 - Dennis Frings Channing Laugh with Teeth South toward Emerald Sea At the Movies South toward Emerald Sea 09/17/2000 05/11/2000 02/14/2002 07/15/2003 7000.00 1800.00 5550.00 2200.00 The Gill Art Gallery wishes to maintain data on their customers, artists and paintings. They may have several paintings by each artist in the gallery at one time. Paintings may be bought and sold several times. In other words, the gallery may sell a painting, then buy it back at a later date and sell it to another customer. Ó Deakin University 2019 IT Help @ Deakin Page 3 of 6 Assessment 1: Database Design and Implementation Report Exercise 1: Draw the dependency diagram of the table, and normalize the table to ensure all generated tables are in 3NF. Present all tables generated from the normalization. You have to provide the results step by step from 1NF to 3NF. Exercise 2: Write SQL code to create the generated tables. Assessment feedback General feedback to the class will be provided via FutureLearn or CloudDeakin-Discussion Forum. Students will have the opportunity to seek additional feedback during the fortnightly seminar sessions. Submission details The assessment file should be a Microsoft Word file with the filename formatted as: FirstName_Surname_Student ID_CourseCode_A1 (eg. Susan_Wolf_123456_SIT772_A1.docx). The document is to be submitted electronically via Assessment 1 in CloudDeakin by the due date. The submission link is on the FutureLearn SIT772 Assessment tab on the Program page. Extension requests Requests for extensions should be made to Unit/Campus Chairs 3 days early before the assessment due date. Special consideration You may be eligible for special consideration if circumstances beyond your control prevent you from undertaking or completing an assessment task at the scheduled time. See the following link for advice on the application process: http://www.deakin.edu.au/students/studying/assessmentand-results/special-consideration Assessment feedback Detailed written feedback will be provided within two weeks of submission. Referencing You must correctly use Harvard referencing in this assessment. See the Deakin referencing guide. Academic integrity, plagiarism and collusion Plagiarism and collusion constitute extremely serious breaches of academic integrity. They are forms of cheating, and severe penalties are associated with them, including cancellation of marks for a specific assignment, for a specific unit or even exclusion from the course. If you are ever in doubt about how to properly use and cite a source of information refer to the referencing site above. Plagiarism occurs when a student passes off as the student's own work, or copies without acknowledgement as to its authorship, the work of any other person or resubmits their own work from a previous assessment task. Collusion occurs when a student obtains the agreement of another person for a fraudulent purpose, with the intent of obtaining an advantage in submitting an assignment or other work. Work submitted may be reproduced and/or communicated by the university for the purpose of assuring academic integrity of submissions: https://www.deakin.edu.au/students/study-support/referencing/academic-integrity Ó Deakin University 2019 IT Help @ Deakin Page 4 of 6 SIT772: Database and Information Retrieval Assessment 1: Database Design and Implementation Report rubric Question 1 Q1-Excise 1 Excellent All elements of this table are correct. Good At least one of the elements of this table is correct. 1 mark Q1-Excise 2 Q1-Excise 3 0.5 marks Excellent The written SQL code is correct. Q2-Excise 2 The written SQL query is correct. Good The written SQL code is partially correct. 2 marks Q2-Excise 3 The written SQL query is correct. Q2-Excise 4 The written SQL query is correct. 1 mark The written SQL query is partially correct. 2 marks The written SQL query is correct. 1 mark Excellent The relational schema written is correct The relational schema written is correct. Ó Deakin University 2019, CRICOS Provider Code: 00113B 1 mark 0 marks The written SQL query is incorrect or missing. 1 mark Good The relational schema written is partially correct. 3 marks 0 marks The written SQL query is incorrect or missing. The written SQL query is partially correct. 2 marks 0 marks The written SQL query is incorrect or missing. The written SQL query is partially correct. 2 marks Q3-Excise 2 Unsatisfactory The written SQL code is incorrect or missing. 1 mark 0 marks The written SQL query to generate a combined is The written SQL query is to generate a combined list partially correct. missing. 2 marks Question 3 Q3-Excise 1 0 marks The Entity Relationship Diagram (ERD) of the generated The Entity Relationship Diagram (ERD) of the generated The Entity Relationship Diagram (ERD) of the generated tables is correct. tables is partially correct. tables is not correct or is missing. 2 marks 1 mark 0 marks The SQL code, which creates the tables is correct. The SQL code, which creates the tables is partially The SQL code, which creates the tables is incorrect or correct. missing. 2 marks 1 mark 0 marks Question 2 Q2-Excise 1 Q2-Excise 5 Unsatisfactory None of the elements of this table was correct or the elements are missing. 2 marks 0 marks Unsatisfactory The relational schema written is incorrect or missing. 0 marks The relational schema written is partially correct. The relational schema written is incorrect or missing. 3 marks 2 marks 0 marks IT Help @ Deakin Page 5 of 6 Assessment 1: Database Design and Implementation Report rubric Question 3 (cont) Q3-Excise 3 Q3-Excise 4 Question 4 Excellent Good Unsatisfactory The relational schema written is correct. The relational schema written is partially correct. The relational schema written is incorrect or missing. 2 marks 1 mark 0 marks The Entity Relationship Diagram (ERD) drawing is The Entity Relationship Diagram (ERD) drawing is The Entity Relationship Diagram (ERD) drawing is correct. partially correct. incorrect or missing. 2 marks 1 mark 0 marks Excellent Good Incorrect Q4-Excise 1 The dependency diagram drawing of the table is The dependency diagram drawing of the table is The dependency diagram drawing of the table is correct. partially correct. incorrect or missing. 3 marks 2 marks 0 marks Q4-Excise 2 The written SQL code is correct. The written SQL code is partially correct. 2 marks Ó Deakin University 2019 The written SQL code is incorrect or missing. 1 mark 0 marks IT Help @ Deakin Page 6 of 6

Answered by Expert Tutors

ur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, co

s a molestie consequat, ultrices ac magna. Fusce dui le




icitur laoreet. Nam risus ante,

e vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consec
Step-by-step explanation

WhatsApp Image 2019-09-01 at 12.50.28.jpeg

rem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laore
1 Attachment
WhatsApp Image 2019-09-01 at 12.50.28.jpeg
jpeg
Get unstuck

445,696 students got unstuck by Course
Hero in the last week

step by step solutions

Our Expert Tutors provide step by step solutions to help you excel in your courses