Basic SQL Queries p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'} p.p2 {margin: 0.0px 0.0px 2.0px 0.0px; font: 14.0px 'Helvetica Neue'}
any business rules or assumptions made.
REQUIREMENTS
–
HIGHER LEVEL
–
SQL IMPLEMENTATION
For additional marks, provide the SQL commands to create the tables and insert a few rows into each table. Also provide several business question and SQL queries to test out the tables, include at least a:
SELECTION condition query
GROUP BY query
JOIN query
NESTED query
Also create a least one visualisation of the data using
Orange
ISYS2421 School of Business I.T. & Logistics Semester 1 - 2019 Business Data Management and Analytics Page 1 of 2 SCHOOL OF BUSINESS I.T. & LOGISTICS Business Data Management and Analytics Assignment 2b – Data Model Due Date: End of Week 12 (Thursday 31st May, 2019) SPECIFICATIONS Read the following Case study carefully. You will be asked to create a data model that is free from anomalies and therefore complies with being in third normal form. CASE STUDY – Kids Stuff Some of your friends want to set up a new website to sell products for children over the internet called KidsStuff. They have asked you to design a database to be used by the website to keep details of the products and also to record orders that are received from customers via the web site. They are planning to sell three major types of products for kids: console games, toys, DVDs and clothes. For console games, they will need to store a description of the game, type of console, retail price. For the toys, they will just need to store a description of the toy as well as the retail price. For DVDs they will need a description and price, but will also need a rating (eg G, PG etc) and a running time, so these can be displayed on the website. Clothes will need details stored such as size, colour, model etc. For all product types, a count will be needed to keep track of how many copies of the product are currently in stock. Console games and DVDs in Australia have a rating associated with them, i.e. G, PG, M, MA15+, and R18+ (see http://www.classification.gov.au/Guidelines/). Some of the toys and DVDs are individual, but many are part of a larger theme. For example, with Pikachu movie recently released, they have a large stock of Pokemon toys and DVDs which are in high demand. They want the website users to be able to search for products based on the theme they are part of, like Pokemon, or part of other themes (like 2019 Pikachu Movie) or products lines (like Toys/Console/Games/DVDs). When customers choose to purchase items, the order will need to be stored into the database, as well as details of the customer. Naturally this will include details such as name, address and phone number. The customer will also specify the delivery address for the order, as this may be different to their billing address. Customers will be required to pay before the products are sent so details will need to be kept of the payment. Your friends are planning to accept payment by credit card, direct bank deposit and paypal. For credit cards they need to store the credit card number and expiry date, for direct deposit they need a field to tick off that the payment has appeared in their bank account and for paypal they again need a field to note the paypal user id of the payer. http://www.classification.gov.au/Guidelines/ School of Business I.T. & Logistics Semester 1 - 2019 Business Data Management and Analytics Page 2 of 2 To try and attract and keep more customers, they are also planning to provide ‘social’ types of features on the website. This will mean that customers can register and get an account name and password that lets them log on to the site. As a registered customer, they will automatically get a 10% discount on all purchases that will be applied when they place an order. Without an account, no discount available, but items can be purchased and paid for and will be dispatched. The site will also provide a sort of blog system where registered customers (with an account) can leave comments about certain products and read comments left by others. This will hopefully encourage customers to give each other recommendations and generate more sales. REQUIREMENTS – PASS/CREDIT – DATA MDOEL Create an ER diagram, relational model and any business rules or assumptions made. REQUIREMENTS – HIGHER LEVEL – SQL IMPLEMENTATION For additional marks, provide the SQL commands to create the tables and insert a few rows into each table. Also provide several business question and SQL queries to test out the tables, include at least a: • SELECTION condition query • GROUP BY query • JOIN query • NESTED query Also create a least one visualisation of the data using Orange. ASSESSMENT Assessment of the data model will be based on the following areas (by the tutor) as per the rubric found on the CANVAS submission system: • Data Model • No anomalies • Dependence of all attributes to the whole primary key • Must be in third normal form (3NF) • SQL Queries • Appropriate implementation of data model using SQL • A number of business questions (of your design) + SQL Query solution • Visualisation appropriateness to the business question (of your design) SUBMISSION • submit a ER diagram (attributes are optional) • submit a Relational Model (separate from the ERD) • submit any Business Rules or Assumptions made • submit all SQL commands, if attempting the higher level component • submit all visualisation, as a screen dump, if attempting the higher level component • Assignment will be submitted online using the learning hub. • Please submit the assignment file (it can be a Word or Pdf document)