Database Design and Implementation - PowerPoint Presentation Example

1. Data investigation and capture 1 of the three websites chosen (optional, and no more than 100 words each 1 http www.oxfam.org.uk/shop “Oxfam” is an online portal for shopping of new and used products belonging to different categories. Figure 1 Portion of “Oxfam” Home Page 1.1.2 http://www.uniqlo.com/uk/ “Uniqlo” is an online shopping site of clothing for men and women. User can browse through the various categories of clothing and select the ones of interest. Once the products are added to the cart, after choosing a delivery and payment plan, the order can be placed. Figure 2 Portion of Home Page for "Uniqlo" 1.1.3 http://www.next.co.uk/ “Next” is an online shopping site that lists the various products for men, women and children, home and special events. It lists the available features and options for a product, the prices, etc. In order to shop from the site a user can browse through the products from the various categories and selects the products to buy. To get the selected items delivered, the user has to register with the site and set up a payment and delivery plan. The order details are mailed to the user’s registered email. Figure 3 Portion of “Next” Home Page 1.2 List of data fields (may include additional fields/attributes/data items) In each of the three websites, the user’s input can be of the following two types; Selection from the options presented Entering Information 1.2.1 List of data fields users have to complete from site 1 (Oxfam) Consider the following screenshots from the website: Figure 4 Product Categories and Subcategories Figure 5 Product Details Figure 6 Product Added to Cart Figure 7 Customer Registation Figure 8 Order Details Figure 9 Payment Details From the above screenshots of the site, following entities and attributes can be extracted: Product (ID, Name, Type, Condition, Description, TechnicalDescription, Price, BrandName) Condition will be new or old Picture (ID, PathtoSmall, PathtoLarge) Category (ID, Name) Subcategory (ID, Name) Customer (ID, FirstName, LastName, Email, Country, HomeAddress, Postcode, RegistrationDate, RegistrationTime) DeliveryAddress (ID, HomeAddress, Postcode) Donation (ID, Amount) Cart (ID, CustomerID, Code) Cart Item (ID, ProductName, Qty, Cost, TimeOfSelection) Order (ID, ProductName, Qty, Date, Time, Price, Shipping price) Payment (ID, CardHolderName, CardtNumber, ExpiryDate, SecurityNumber) 1.2.2 List of data fields users have to complete from site 2 (Uniqlo) Figure 10 Product Categories and Subcategories Figure 11 Product Item Figure 12 Product Added to Cart Figure 13 Customer Registration Figure 14 Set Delivery Options Figure 15 Order Details Figure 16 Payment Details Considering the above screenshots, the entities and attributes formed are as follows: Product (ID, Name, Description, Colour, Size, Length. Qty, Type, Price) Picture (ID, PathtoSmall, PathtoLarge) Review (ID, Description, Date, Time, Rating) Category (ID, Name) Subcategory (ID, Name) Customer (ID, Title, FirstName, LastName, AdressLine1, AddressLine2, City, Email, Phone, password, DOB, Gender, HomeAddress, Postcode, RegistrationDate, RegistrationTime, Subscription) Cart (ID, CustomerID) Cart Item (ID, Image, Colour, ItemName, Size, Length, Price, Qty, Cost, Note) Order (ID, Image, Colour, ItemName, Size, Length, Price, Qty, Cost, Note, ShippingCost) Payment (ID, CardType, CardNumber, ExpiryDate, CardSecurityNumber) Delivery (ID, IssueDate, IssueTime, Type, DeliveryDate, mobileNumber,RecipientFirstName, RecipientLastName, RecipientAddressLine1, RecipientAddressLine2, RecipientCity, RecipientPostCode, RecipientPhone) 1.2.3 List of data fields users have to complete from site 3 Consider the following screenshots of the website: Figure 17 Type of Products in Main Menu Figure 18 Categories and Subcategories in each Type of Product Figure 19 Detail of a Product Figure 20 Item Added to Cart Figure 21 Customer Registration to Checkout Figure 22 Setting Delivery Options Figure 23 Setting Payment Options and Viewing Order Details Considering the above screenshots, the entities and attributes derived are as follows: Product (ID, Name, Type, OnSale, Composition, QtyInStock, Price) Where type would be either of the stated (Christmas, Women, Men, Girls, Boys, Homeware, Electrical, Flowers, Lipsy) OnSale would be either true or false Picture (ID, PathtoSmall, PathtoLarge) Review (ID, Description, Data, Time, Rating) Brand (ID, Name, Description) Category (ID, Name) Subcategory (ID, Name) Customer (ID, FirstName, LastName, DOB, Phone, Email, password, passwordHint, pin, Gender, HomeAddress, Postcode, RegistrationDate, RegistrationTime) Subscription (ID, Type) Cart (ID, CustomerID) Cart Item (ID, ProductID, Qty, Cost) Order (ID, ProductID, CustomerID, Qty, Date, Time, Price, Shipping price) Payment (ID, CustomerID, OrderID, CardHolderName, AccountNumber, ExpiryDate, IssueNumber, StartDate, email) Delivery (ID, IssueDate, IssueTime) HomeDelivery (Address, Date, Time, Cost) OnSite (StoreID) Store (ID, Name, Location, Description, Timing) 1.3 The generic list of attributes derived from the three websites investigated 1.3.1 The generic list Product (ID, Name, Type, Condition, Description, TechnicalDescription, QtyInStock, Price) Condition will be new or old Where type could be any of the stated (Christmas, Women, Men, Girls, Boys, Homeware, Electrical, Flowers, Lipsy, etc) Picture (ID, PathtoSmall, PathtoLarge) Brand (ID, Name, Description) Category (ID, Name) Subscription (ID, Type) Review (ID, Description, Date, Time, Rating) Customer (ID, FirstName, LastName, DOB, Phone, Email, password, passwordHint, pin, Gender, HomeAddress, Country, Postcode, RegistrationDate, RegistrationTime) Delivery (ID, IssueDate, IssueTime, Type,) Delivery (ID, Cost) HomeDelivery (DeliveryDate, RecipientFirstName, RecipientLastName, RecipientAddressLine1, RecipientAddressLine2, RecipientCity, RecipientPostCode, RecipientPhone) OnSite (StoreID) Cart (ID) Cart Item (ID, ProductName, Qty, Cost) Order (ID, ProductName, Qty, Date, Time, Price, Shipping price, Status) Payment (ID, CardHolderName, CardtNumber, ExpiryDate, SecurityNumber) Store (ID, Name, Location, Description, Timing) 1.3.2 Justification 1.3.2.1 Attributes retained Product (ID, Name, Type, Condition, Description, TechnicalDescription, QtyInStock, Price) Condition will be new or old Where type could be any of the stated (Christmas, Women, Men, Girls, Boys, Homeware, Electrical, Flowers, Lipsy, etc) Picture (ID, PathtoSmall, PathtoLarge) All these attributes are essential for an online shopping. A small thumbnail sized picture should be visible next to a product, which can then be zoomed for a bigger view. Category (ID, Name) All products are listed under categories. A category may have another category. Brand (ID, Name, Description) The products belong to certain brands. Some customers buy products of only selected brands. Subscription (ID, Type) Customers may be subscribed to different listings e.g. promotions, sales, events, etc. Review (ID, Description, Date, Time, Rating) Customers can review and rate the products that they purchased. This helps the other customers in making their decisions regarding purchasal. Customer (ID, FirstName, LastName, DOB, Phone, Email, password, passwordHint, pin, Gender, HomeAddress, Country, Postcode, RegistrationDate, RegistrationTime) All these fields completely represent the data related to a customer. Delivery (ID, Cost) HomeDelivery (DeliveryDate, RecipientFirstName, RecipientLastName, RecipientAddressLine1, RecipientAddressLine2, RecipientCity, RecipientPostCode, RecipientPhone) OnSite (StoreID) The attributes completely represent all required aspects of the delivery. If the customer wishes to have the products delivered at residence, the option can be selected or the customer can get the order shipped to a particular shop. Cart (ID) Every customer has a unique cart. Cart Item (ID, ProductName, Qty, Cost) The attributes represent the information required for a selected item Order (ID, ProductName, Qty, Date, Time, Price, Shipping price, Status) The attributes completely represent the required information that are required for an order Payment (ID, CardHolderName, CardtNumber, ExpiryDate, SecurityNumber) An online payment usually has these four attributes Store (ID, Name, Location, Description, Timing) 1.3.2.2 Attributes rejected Product (BrandName, Colour, Size, Length, composition, OnSale) Brand Name is to be considered as an attribute of a separate entity brand. This is to ensure, a particular brand name is always referred to with the same spelling and name description. In case the name of the brand changes, only one change will have to be made. Colour, Size, Length are only relavant to the clothing or shoes Composition can be incorporated within the attribute of description Not every site necessarily offers a sale feature. If needed the feature can be added as a separate entity with list of products on sale Subcategory (ID, Name) The subcategory is also a category, therefore it is saved within the Category entity. Donation (ID, Amount) Not every shopping site is involved with social work and therefore does not request for a donation amount Cart Item (TimeOfSelection) The item selected is usually not timestamped. The item stays in the cart only till the user’s login session. In case, a user logs out without checking out, the selected item is sent back to the available product listing Customer (Subscription) Subscription is treated as a separate entity as there may be a number of subscriptions a customer may be associated with. Cart Item (Colour, Size, Length, Note) Image and Note are not a requirement of the cart functionality Colour, Size, Length are not a required in shopping sites that are selling a variety of products not merely clothing. Order (Image, Colour, ItemName, Size, Length, Price,Note, ShippingCost) Image and Note are not a requirement of an Order Payment (IssueNumber, StartDate, email) IssueNumber, StartDate, email are not a necessary requirement for payment Delivery (Type, IssueDate, IssueTime) Type is not a requirement as most of the shopping sites offer the standard shipping or one type of delivery. Issue date and time is generally stored with the order details Delivery -OnSite (StoreID) Generally Onsite delivery is not an option in the shopping sites 2. Database design 2.1 Entity Relationship Modelling 2.1.2 Extended Entity Relationship Model 2.2 Database Schema Following Tables shall be implemented in the database. The Table for Cart and Cart Item will not be implemented because the two tables are dynamic and are not for long term storage. The data from cart is shifted to the Order table for long term storage. 3. Database implementation 3.1 Screen dumps for table creation (note that CREATE TABLE statements can be saved, and pasted from, a text file) Database Creation CREATE DATABASE `shopping` ; Tables Creation CREATE TABLE Customer( CustID integer NOT NULL AUTO_INCREMENT , FName varchar( 15 ) NOT NULL , LName varchar( 15 ) NOT NULL , DOB date NOT NULL , Gender ENUM(M,F) , Email varchar( 50 ) NOT NULL , Phone varchar( 15 ) NOT NULL , Address varchar( 100 ) NOT NULL , City varchar( 25 ) NOT NULL , Country varchar( 25 ) NOT NULL , PASSWORD varchar( 15 ) NOT NULL , Hint varchar( 15 ) NULL , Pin int NULL , RegDateTime Datetime NOT NULL , PRIMARY KEY ( CustID ) ) CREATE TABLE Category( CategoryID integer NOT NULL AUTO_INCREMENT , Name varchar( 25 ) NOT NULL , ParCatID integer NULL , PRIMARY KEY ( CategoryID ) , FOREIGN KEY ( ParCatID ) REFERENCES Category( CategoryID ) ) CREATE TABLE Brand( BrandID integer NOT NULL AUTO_INCREMENT , Name varchar( 15 ) NOT NULL , Descr varchar( 100 ) NOT NULL , PRIMARY KEY ( BrandID ) ) CREATE TABLE Product( ProdID integer NOT NULL AUTO_INCREMENT , Name varchar( 100 ) NOT NULL , TYPE ENUM( Girl, Boy, Men, Women, Electrical, Home ) , Cond ENUM( Old, New, Good ) , Descr varchar( 50 ) NOT NULL , TechDesc varchar( 15 ) NOT NULL , Price decimal NOT NULL , QtyInStock int NULL , CategoryID int NOT NULL , BrandID int NOT NULL , PRIMARY KEY ( ProdID ) ) CREATE TABLE Image( ImageID integer NOT NULL AUTO_INCREMENT , ShortPath varchar( 50 ) NOT NULL , LargePath varchar( 50 ) NOT NULL , ProdID integer NOT NULL, PRIMARY KEY ( ImageID ) , FOREIGN KEY ( ProdID) REFERENCES Product (ProdID) ) CREATE TABLE Review( ReviewID int NOT NULL AUTO_INCREMENT , Descr varchar( 100 ) NOT NULL , ReviewDateTime datetime NOT NULL , Rating int NOT NULL , CustID integer NOT NULL , ProdID integer NOT NULL , PRIMARY KEY ( ReviewID ) , FOREIGN KEY ( CustID ) REFERENCES Customer( CustID ) , FOREIGN KEY ( ProdID ) REFERENCES Product( ProdID ) ) CREATE TABLE Subscription( SubsID integer NOT NULL AUTO_INCREMENT , TYPE ENUM( Promotion, Event ) , PRIMARY KEY ( SubsID ) ) CREATE TABLE CustSubs( SubsID integer NOT NULL , CustID integer NOT NULL , PRIMARY KEY ( SubsID, CustID ) , FOREIGN KEY ( SubsID ) REFERENCES Subscription( SubsID ) , FOREIGN KEY ( CustID ) REFERENCES Customer( CustID ) ) CREATE TABLE store( StoreID integer NOT NULL AUTO_INCREMENT , name varchar( 25 ) NOT NULL , Location varchar( 25 ) NOT NULL , Descr varchar( 50 ) NULL , Timings varchar( 25 ) NOT NULL , PRIMARY KEY ( StoreID ) ) CREATE TABLE POrder( OrderID integer NOT NULL AUTO_INCREMENT , OrderDateTime DateTime NOT NULL , Cost decimal NOT NULL , STATUS ENUM( Complete, Pending ) , CustID Integer NOT NULL , PRIMARY KEY ( OrderID ) , FOREIGN KEY ( CustID ) REFERENCES Customer( CustID ) ) CREATE TABLE OrderProduct( OrderID integer not null, ProdID integer not null, Qty integer not null, Price double not null, PRIMARY KEY (OrderID,ProdID), Foreign Key (OrderID) REFERENCES Porder(OrderID), Foreign Key (ProdID) REFERENCES Product(ProdID) ) CREATE TABLE Payment( PayID integer NOT NULL AUTO_INCREMENT , CardHolderName varchar( 50 ) NOT NULL , CarNum varchar( 25 ) NOT NULL , ExpiryDate date NOT NULL , CarSecNum varchar( 25 ) NOT NULL , OrderID Integer NOT NULL , PRIMARY KEY ( PayID ) , FOREIGN KEY ( OrderID ) REFERENCES POrder( OrderID ) ) CREATE TABLE delivery( DeliveryID integer NOT NULL AUTO_INCREMENT , Cost Integer NOT NULL , OrderID Integer NOT NULL , PRIMARY KEY ( DeliveryID ) , FOREIGN KEY ( OrderID ) REFERENCES Porder( OrderID ) ) CREATE TABLE OnSiteDelivery( DeliveryID integer NOT NULL , EstimateDeliveryDate Date NOT NULL , StoreID Integer NOT NULL , PRIMARY KEY ( DeliveryID ) , FOREIGN KEY ( StoreID ) REFERENCES store( StoreID ) ) CREATE TABLE homedelivery( DeliveryID integer NOT NULL , DeliveryDate date NULL , RecFname varchar( 25 ) NOT NULL , RecLname varchar( 25 ) NOT NULL , RecAddr varchar( 100 ) NOT NULL , RecCity varchar( 15 ) NOT NULL , RecPostCode varchar( 9 ) NOT NULL , RecPhone varchar( 15 ) NOT NULL , PRIMARY KEY ( DeliveryID ) ) 3.2 Screen dumps for displaying data in tables 4. SQL Queries 4.1 Query 1 4.1.1 For what purpose will this query be used in business terms? Calculate the total value of company sales so far can help the manager to see if the current business advertising setup is bringing sales. 4.1.2 Query in natural language Sum the cost of all orders within a certain time period. 4.1.3 SQL Code and output select month(OrderDateTime), SUM(porder.Cost) as Monthly Sales from `porder` group by month(OrderDateTime) 4.1.4 Explain the output of the data (was this what was predicted?) The output should be the sum of the orders’ cost listed month wise. The output of the query gives the expected results. 4.2 Query 2 4.2.1 For what purpose will this query be used in business terms? Finding the number of male and female Customers can show the target potential advertisement area, 4.2.2 Query in natural language Count the number of male and female Customers and group the result according to the gender 4.2.3 SQL Code and output select Gender, Count(Customer.Gender) as Customers from `customer` group by Gender 4.2.4 Explain the output of the data (was this what was predicted?) The output of the query should display the total number of male and female customers of the site. The query outputs the expected result. 4.3 Query 3 4.3.1 For what purpose will this query be used in business terms? By showing the other reviews and products by a customer, more products can be bought that the same customer gave feedback for. 4.3.2 Query in natural language Find all product reviews for a particular customer. 4.3.3 SQL Code and output Select reviewID, Product.Name from review,product where review.CustID=2 and review.ProdID=product.ProdID 4.3.4 Explain the output of the data (was this what was predicted?) The output of the query should be a list of all the reviews by a specific customer. The output is as predicted. 4.4 Query 4 4.4.1 For what purpose will this query be used in business terms? Finding the number of orders made by the male and female customers can tell whether male products are more in demand or female. 4.4.2 Query in natural language Count all orders for the male and female customers and group the count according to gender. 4.4.3 SQL Code and output select Gender, count(OrderID) as Orders from `customer` Inner Join `porder` on Customer.CustID=porder.CustID group by Gender 4.4.4 Explain the output of the data (was this what was predicted?) The output should be the count of order placed by female and male customers. The query gives the expected output. 4.5 Query 5 4.5.1 For what purpose will this query be used in business terms? By finding which shop receives more delivery requests, the products can be placed in stock in that particular shop to facilitate more sales. 4.5.2 Query in natural language Find all the stores and the number of deliveries in each. 4.5.3 SQL Code and output select Store.Name, count(onsitedelivery.DeliveryID) as Deliveries from `onsitedelivery` Inner Join `store` on onsitedelivery.storeID=store.storeID 4.5.4 Explain the output of the data (was this what was predicted?) The query must list down all the stores and the associated count of orders fulfilled by the store. The query provides with the expected results. 5. References Sun, P. 2008. Template for DDI Assignment. Additional Supporting Materials for Chelmsford Campus. Database Design and Implementation module (EJ215004S) materials. Anglia Ruskin University. Read More