Assignment 2b – Data Model
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
Business Data Management and Analytics
Assignment 2b – Data Model
Due Date: End of Week 12
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 - Designing a Database for KidsStuff - An Online Store for Children's Products
Objective:
The objective of this project is to design a
comprehensive database system for KidsStuff,
an e-commerce website that aims to sell
products for children online. The database will
store product details, manage customer orders,
facilitate payments, and incorporate social
features to enhance customer engagement.
Product Categories:
KidsStuff will offer three main product categories: toys, DVDs, and clothes. The database will need to capture specific details for each category:
. Toys: Description, Retail Price, Count in Stock.
. DVDs: Description, Price, Rating (e.g., G, PG), Running Time, Count in Stock.
. Clothes: Description, Price, Size, Color, Model, Count in Stock.
Theme-based Products:
In addition to individual products, KidsStuff will offer products related to specific
themes. For example, they have a collection of Toy Story toys and DVDs, which will be categorized under the "Toy Story" theme. The database will enable users to search and list products based on their respective themes.
Customer Orders and Details:
The database will record customer orders along with their details, including name, address, and phone number. Each order can consist of multiple products, and the database will maintain associations between orders and the products they include.
Payment Information:
KidsStuff will accept payments through credit card, direct bank deposit, and PayPal. The database will store relevant payment details as follows:
. Credit Card: Credit card number and expiry date.
. Direct Bank Deposit: A field to indicate the payment receipt.
. PayPal: A field to indicate the payment receipt and the PayPal user ID of the payer.
User Registration and Discounts:
Customers will have the option to register on the website and obtain an account name and password. Registered customers will automatically receive a 10% discount on all purchases. The database will handle user registration information and apply discounts during the order placement process.
Social Features:
KidsStuff plans to implement social features on the website to enhance customer
engagement. The social system will include a blog where registered customers can leave comments about specific products and read comments from other users. This interactive feature aims to encourage customer recommendations and foster a sense of community.
Conclusion:
The formal database design for KidsStuff will facilitate the smooth functioning of the
online store, providing efficient storage and retrieval of product details, seamless order management, secure payment processing, and engaging social features. By incorporating these elements, KidsStuff aims to attract and retain customers,
providing them with a seamless shopping experience for children's products.
REQUIREMENTS – DATA MODEL
Create an ER diagram, relational model and any business rules or assumptions made.
REQUIREMENTS – SQL IMPLEMENTATION
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, Tableau or Excel.
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 (CREATE TABLEs & INSERTs)
. A number of business questions (of your design) + SQL Query solution (SELECTs)
. 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 visualisation, as a screen dump, if attempting the higher level component
. Assignment will be submitted online using the CANVAS learning hub.
. Please submit the assignment file (it can be a Word or Pdf document)
2023-09-27
Business Data Management and Analytics