Question

Q) Create a relational model and list any business assumptions made.

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.

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.

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.

Answer 1

The required diagram is given below for the schema.

The customer Email Id is the User Id of the customer, used for both login and customer details

Customers can save multiple payment options (Credit cards)

Customers have a default ordering and billing address which they may have saved

1 comment can be associated with 1 customer and 1 item, However, 1 customer can make multiple comments and an item can have multiple comments.

All items have an item id , price and count . Items have subtypes of clothes, dvds and games and whie searching in the website, one may opt to search in each or all of the categories ( assuming each category has a type id number)

1 order can have 1 payment option

Payment can be 1 of Cash-on-delivery (requires no description), Credit Card( Card number in description), Paypal (Paypal ID in description)

The database has been designed keeping in mind that customers may not be registered members of the site. Hence the delivery address and payment fields are assumed to copy the data from database accounts of existing customers or to be filled up by the unregistered customer manually during payment.