School of Business I.T. Semester 1 - 2017 Business Database Management and Analytics Page 1 of 2 SCHOOL OF BUSINESS INFORMATION TECHNOLOGY Business Data Management and Analytics Assignment 2b – Data Model Due Date: (Monday 29th May, 2017) – or earlier if you prefer 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: toys, DVDs and clothes. 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. Some of the toys and DVDs are individual, but many are part of a larger theme. For example, with Toy Story 4 recently released, they have a large stock of Toy Story 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 (eg. List all toys and DVDs by the Wiggles). 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 tick off plus the paypal user id of the payer. Each order placed by a customer can be for multiple products so you will need to account for this. School of Business I.T. Semester 1 - 2017 Business Database 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. The site will also provide a sort of blog system where registered customers 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 ASSESSMENT Assessment of the data model will be based on the following areas (by the tutor): • 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 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 • Assignment will be submitted online using the learning hub. • Please submit the assignment file (it can be a Word or Pdf document)