Solved by: AllAcademicHelp.com
Previous answers to this question 27: 16 7 3 0 1
DATABASE MANAGEMENT School of Information & Communication Technology, Designing a Database for BigM
September 24th, 2018 by admin
1804ICT – DATA MANAGEMENT7003ICT – DATABASE MANAGEMENTSchool of Information &Communication Technology Trimester2, 2018Assignment Part 2:Designing a Database for BigMAimsTo analyse and comprehend a given ER diagram and Relational Databaseschema To implement a database based on the given ER diagram and Databaseschema To write required SQL statements to query the databaseTo write SQL statements to manipulate the data in the databaseLearning ObjectivesIn the process of this assessment task you will:plan, schedule and execute project tasks with a view to improving your personal productivity;gain awareness of the typical challenges related to the practical implementation of databases;learn how to use Data Definition statements to implement a database from a given ER diagramand the corresponding Database schemalearn how to use Data Manipulation statements to query a database, and insert and updatedata in the tablesDue date: Week 11, 25 September Friday, 5:00 PMSubmit the individual work file, named ‘a2-.zip ‘, by theabove due date.Late Any submission after the due date will receive a deduction of submission: 10% per day.Marks: This assignment has a total 100 marks and it is worth 15% out ofthe total assessment.Extensions: An extension will only be considered with supportingdocumentation from a health professional and if theproblem/illness occurred within the week prior to the due date.If an extension is granted the extension will then equal the numberof days specified on the doctor’s certificate, with a maximum limitof five (5) working days.Authorship: This assignment is an individual assignment and it shall becompleted by the individual student only. The final submissionmust be identifiably the work of the individual. Breaches of thisrequirement will result in an assignment not being accepted forassessment and may result in the offending student or studentsbeing required to present before the Disciplinary Committee.1804ICT & 7003ICT – Assignment Part 2 Page 1Designing a Database for BigMAssignment SpecificationBigM now requires a partial implementation of the design made in Assignment Part 1. Inorder to keep consistency between the assignments, database specification containing theER diagram and the corresponding schema are provided in this document. You shouldcreate your database according to this documentation. Make sure that yourimplementation is consistent with this design, i.e., your table names, field names, anddata types are according to the specifications provided in this document. Theimplementation phase includes writing SQL statements to create a database and its tables,populating the tables with appropriate test data, and writing a number of queries to createreports that can be used by the management team. You need to insert at least five recordsin each of the tables and ensure that each of the query returns at least one record.Implementation of the Database and Manipulation of the DataYou are required to perform the followings tasks:1. Task 1: Create a text file named Create_.sql (forexample, Create_s3087654.sql) that will contain SQL statements to:I. Create a database named BigM_.II. Create all of the tables for the database according to the Databaseschema given with this document (separately attached).2. Task 2: Create a text file named Insert_.sql that will containSQL statements to:I. Insert at least five records in each of the tables. The test data inserted intothe table must ensure that each of the queries, specified in Task 3, outputsat least one record.3. Task 3: Create a text file named Query_.sql that will contain allthe queries to display the followingI. List of names and complete postal address of all employees sorted by theirsalary.II. The date on which the most recent customer order has been made. Thecustomer name and date of the order will be sufficient.III. List of all the store names and their addresses, sorted in dictionary orderof the store name.IV. A list of all customers that have not placed an order yet.Displaying customer number and name will be sufficient.V. A list containing the name of employees, which work as accountant.VI. A list containing the total quantity on hand for each product (productnumber and description) regardless of stores.VII. A list showing each product sold (picked) on or before May 20, 2018. Showproduct number, name and quantity sold, sorted by product number andthen quantity sold.VIII. A list of products (show product number, description and price) whose priceis less than or equal to the average product price.IX. Increase each employee’s salary by 7.5% and show the updated salary of allemployees (name and salary).X. Show the pay information (employee name, hours paid, amount paid) ofall employees in the most recent pay date.1804ICT & 7003ICT – Assignment Part 2 Page 2 of 4Additional queries for 7003ICT students only:XI. Make a list of all products of brand “Armani” and their price in ascendingorder of price. Show the product description and price.XII. A list of supervisors (employee id, name) and all of their subordinates(employee id, name).4. Task 4: Create a text file named Update_.sql that will performthe followings. Insert additional data in the tables appropriately if needed.A customer named Daniel Ortega, from 11 Fuller Road, MarsdenQLD 4132 and having a mobile number 0431xxx668, orders two shirtsof size “XL” of brand “Prada” on 6 Sep 2018. The full order is readyon 8 Sep 2018 for pickup, but the customer picks up on 10 Sep 2018.You should insert data into appropriate tables for the above order and makesure you update the data in the relevant tables after the order is picked up.Note: Tasks 1 to 4 for all students, only 7003ICT students should do Queries XI and XIIin Task 3.You are required to adhere to the following output formattingconventions:Any query requiring names of people should be printed as GivenNameFamilyName (e.g. John Smith) in a column labelled NAME;Any query requiring addresses should be printed as Street, Suburb State Postcode(e.g. 123 Anzac Pde, Maroubra NSW 2038) in a column labelled ADDRESS; andAll monetary values should be printed with a dollar symbol ($), two digits after thedecimal point.What to submit?An electronic copy of your assignment should be submitted online and should include acopy of your report and the four files described in Task 1 to Task 4 above. Zip all thefiles into a single file named ‘a2‐.zip’ before uploading.Your report should include:Use the supplied template for your Assignment Report.An appropriate title page that includes an acknowledgement of all studentsyou have spoken to about the assignment.A table of contents and page numbers.A report of the results from running the SQL queries (Task 3) by usingcopy/paste of their output.A bibliography containing a list of all resources used to complete the assignment. Ifno resources, apart from the course materials, have been used please indicate this.Assessment CriteriaHow clear and well organised your presentation is. On the front page of yourreport you should include a list of acknowledgements of all people who haveassisted you with this assignment including fellow students.Adherence to our standards. How clear and well organised your presentationis. You should write all the queries in consistent style and use indent format.1804ICT & 7003ICT – Assignment Part 2 Page 3 of 4Data correctness and quality. Please use appropriate data for your examples(e.g., do not use inappropriate person names)Please refer to the provided marking guide (below) to see the distribution of marks.Assignment ResourcesFind the following information in attached files:The logical ER Diagram, andRelational Database Schema.Assessment Criteria and Marking OverviewFor 1804ICT students only:ITEM Marks1. Presentation 8How clear and well‐presented your submission is.2. Creation of database and tables (Task 1) 20Create the relevant tables: Database correctly named and created,includes all of the PKs and FKs in the database. No evidence thattables have been created using the GUI. They work properly.3. Insertion (Task 2) 30Successfully inserts data into the tables. NO evidence exporting fromGUI. They work properly.4. Query (Task 3) 32Use of appropriate query statements. They work as intended. Eachquery should output at least one row of valid data.5. Update (Task 4) 10Use of appropriate data manipulation statements to perform therequired actions. They work as intended.Total 100For 7003ICT students only:ITEM Marks1. Presentation 5How clear and well‐presented your submission is.2. Creation of database and tables (Task 1) 20Create the relevant tables: Database correctly named and created,includes all of the PKs and FKs in the database. No evidence thattables have been created using the GUI. They work properly.3. Insertion (Task 2) 30Successfully inserts data into the tables. NO evidence exporting fromGUI. They work properly.4. Query (Task 3) 35Use of appropriate query statements. They work as intended. Theywork as intended. Each query should output at least one row of validdata.5. Update (Task 4) 10Use of appropriate data manipulation statements to perform therequired actions. They work as intended.Total 1001804ICT & 7003ICT – Assignment Part 2 Page 4 of 4
Do you need any assistance with this question?Send us your paper details nowWe’ll find the best professional writer for you!
READY TO PLACE AN ORDER