Data Analysis for National Treasure Online Shop - Essay Example

National Treasure Online Shop (Data Analysis Exercise – November 2008) ID Number Supervisors full CI3590: Bridging Studies January-2009 National Treasure Online Shop Table of Contents Chapter 1: The Class Diagram 3 Chapter 2: The Relational Model 5 Chapter 3: SQL Tables (Create Table Statements) 6 Chapter 4: SQL Queries and Output 10 Chapter 5: Conclusion 16 Chapter 1: The Class Diagram Figure 1 shows the class diagram for National Treasure Online Shop. The class diagram has three important classes that are Product, Customer, and Purchase Order along with three other classes as Product category, Line product, and Payment detail. The various attributes of the classes are shown in the class diagram (figure 1). Figure 1: Class Diagram for National Treasure Online Shop Every product has only one product category, therefore the association between the Product class and Product category class is one to one, i.e. one product have only one category. One customer can place one or more order, therefore the multiplicity between the Customer class and Purchase order class is one or more. Every order contains one or more products; therefore, the association between the Purchase order class and Line product class is one or more. However, every product in Purchase order refers to only one product in Product. Therefore, the association between the Line product class and Product class is one to one. There will only one payment detail for every purchase order; therefore, the association between the Purchase order class and Payment detail class will be one to one. Chapter 2: The Relational Model Figure 2: ERD for National Treasure Online Shop Figure 2 shows the relational model (Entity relationship diagram) for National Treasure Online Shop. Additional attributes as password, and email are included in Customer entity assuming that customer will register on website when he/she will make purchase first time so that for the next time the customer will only need to login for making any purchase with the website. PK and FK show the Primary key and foreign key in entity. Chapter 3: SQL Tables (Create Table Statements) Table 1: Customer CREATE TABLE "Customer" ( "CustomerID" CHAR(8) NOT NULL ENABLE, "Password" VARCHAR2(15) NOT NULL ENABLE, "Title" VARCHAR2(15), "Initials" VARCHAR2(15) NOT NULL ENABLE, "Last_Name" VARCHAR2(15) NOT NULL ENABLE, "House_No_Street" VARCHAR2(25) NOT NULL ENABLE, "Town" VARCHAR2(15) NOT NULL ENABLE, "Postcode" VARCHAR2(8) NOT NULL ENABLE, "Telephone" VARCHAR2(11) NOT NULL ENABLE, "Email" VARCHAR2(25) NOT NULL ENABLE, "Date_Added" DATE NOT NULL ENABLE, CONSTRAINT "Customer_PK" PRIMARY KEY ("CustomerID") ENABLE ) / Table 2: ProductCategory CREATE TABLE "ProductCategory" ( "CategoryID" CHAR(8) NOT NULL ENABLE, "Category_Name" VARCHAR2(15) NOT NULL ENABLE, "Picture" BLOB, "Date_Added" DATE NOT NULL ENABLE, CONSTRAINT "ProductCategory_PK" PRIMARY KEY ("CategoryID") ENABLE ) / Table 3: Product CREATE TABLE "Product" ( "ProductID" CHAR(8) NOT NULL ENABLE, "CategoryID" CHAR(8) NOT NULL ENABLE, "Title" VARCHAR2(25) NOT NULL ENABLE, "Picture" BLOB, "Price" NUMBER(4,2) NOT NULL ENABLE, "Price_Quantity" NUMBER(3,0) NOT NULL ENABLE, "Stock_Amount" NUMBER(6,0) NOT NULL ENABLE, "Last_Updated" DATE NOT NULL ENABLE, CONSTRAINT "Product_PK" PRIMARY KEY ("ProductID") ENABLE, CONSTRAINT "PRODUCT_FK" FOREIGN KEY ("CategoryID") REFERENCES "ProductCategory" ("CategoryID") ENABLE ) / Table 4: PurchaseOrder CREATE TABLE "PurchaseOrder" ( "OrderID" CHAR(8) NOT NULL ENABLE, "CustomerID" CHAR(8) NOT NULL ENABLE, "D_House_No_Street" VARCHAR2(25) NOT NULL ENABLE, "D_Town" VARCHAR2(15) NOT NULL ENABLE, "D_Postcode" VARCHAR2(8) NOT NULL ENABLE, "D_Telephone" VARCHAR2(11) NOT NULL ENABLE, "Packaging_Charge" CHAR(3) NOT NULL ENABLE, "Order_Date" DATE NOT NULL ENABLE, "Delivery_Date" DATE NOT NULL ENABLE, "Status" VARCHAR2(15), "Actual_Delivery_Date" DATE, CONSTRAINT "PurchaseOrder_PK" PRIMARY KEY ("OrderID") ENABLE, CONSTRAINT "PURCHASEORDER_FK" FOREIGN KEY ("CustomerID") REFERENCES "Customer" ("CustomerID") ENABLE ) / Table 5: LineProduct CREATE TABLE "LineProduct" ( "OrderID" CHAR(8) NOT NULL ENABLE, "ProductID" CHAR(8) NOT NULL ENABLE, "Quantity" NUMBER(4,0) NOT NULL ENABLE, CONSTRAINT "LINEPRODUCT_FK" FOREIGN KEY ("OrderID") REFERENCES "PurchaseOrder" ("OrderID") ENABLE, CONSTRAINT "LINEPRODUCT_FK2" FOREIGN KEY ("ProductID") REFERENCES "Product" ("ProductID") ENABLE ) / Table 6: PaymentDetail CREATE TABLE "PaymentDetail" ( "OrderID" CHAR(8) NOT NULL ENABLE, "Card_Type" VARCHAR2(15) NOT NULL ENABLE, "Card_Number" NUMBER(16,0) NOT NULL ENABLE, "Expiry_Date" DATE NOT NULL ENABLE, "Debit_Date" DATE NOT NULL ENABLE, "Debit_Amount" NUMBER(6,2) NOT NULL ENABLE, CONSTRAINT "PAYMENTDETAIL_FK" FOREIGN KEY ("OrderID") REFERENCES "PurchaseOrder" ("OrderID") ENABLE ) / Chapter 4: SQL Queries and Output Query 1: Product Categories SELECT "CategoryID","Category_Name","Date_Added","Picture" From "ProductCategory" Figure 3: Output (Screenshot) of Query 1 Figure 3 shows the output of Query 1 that displays the list of product categories for National Treasure Online Shop. When Query 1 is executed, it displays the list of product categories that is Cards, Calendars, Books, and Diaries. This is the first thing that customer visiting on the website is presented on the website home page. Query 2: Product List Select "ProductID", "Title","Category_Name", “Price", "Price_Quantity", "Stock_Amount" From "Product", "ProductCategory" Where "Product"."CategoryID" = "ProductCategory"."CategoryID" Order BY "ProductCategory"."Category_Name", "Product"."Title" Figure 4: Output (Screenshot) of Query 2 Figure 4 shows the output of Query 2 that displays the list of product with product categories for National Treasure Online Shop. This query will be useful for displaying the product list based on product categories. Query 3: Customer Registered Select "CustomerID", "Password", "Title", "Initials", "Last_Name", "House_No_Street", "Town", "Postcode", "Telephone", "Email", "Date_Added" From "Customer" Order BY "Initials", "Last_Name"; Figure 5: Output (Screenshot) of Query 3 Figure 5 shows the list of the customer visited the National Treasure Online Shop. Query 5 will display the list of the entire customer who had purchased any products or registered to the website as a customer. This query will be useful for selecting customer for any promotional offers or contests. Query 4: Purchase Order SELECT "PurchaseOrder"."OrderID", "Initials" || ||"Last_Name" AS "NAME", "D_House_No_Street", "D_Town", "D_Postcode","D_Telephone", "Packaging_Charge", "Debit_Amount", "Order_Date", "Delivery_Date", "Status", "Actual_Delivery_Date" FROM "PurchaseOrder", "Customer", "PaymentDetail" Where ("Customer"."CustomerID" = "PurchaseOrder"."CustomerID") AND ("PurchaseOrder"."OrderID"="PaymentDetail"."OrderID") Figure 6: Output (Screenshot) of Query 4 Figure 6 shows the list of orders with customer name, delivery address, and payment that customer had made with National Treasure Online Shop for products that they had purchased. Query 5: Order Detail SELECT "OrderID", "Product"."Title" AS "ProductName", "Quantity" FROM "LineProduct", "Product" Where ("OrderID" = &Order_ID) AND ("LineProduct"."ProductID"="Product"."ProductID") Figure 7: Output (Screenshot) of Query 5 Figure 7 shows the lists of product for any particular order that customer have made with National Treasure Online Shop. Query 5 will display the list of product for any order by inputting the order number. Query 5 along with Query 4 will be useful for displaying the invoice for the order that customer had made. Chapter 5: Conclusion The National Treasure Online Shop database implemented as an Oracle database using SQL Developer in data analysis exercise meets all the requirements, and the exercise as a whole. The database is capable of displaying the entire four groups (product categories) initially at the home page of the website. Customer can select products based on the categories for purchase. The database can also store the individual records of the customer and the purchase that the customer had made with the National Treasure Online Shop. The database is also capable of storing list of products with quantity for purchase orders that customer had made with the National Treasure Online Shop along with credit/debit card payment details. The database implemented here has one limitation that is it is not implemented actually for the National Treasure Online Shop. This is the only area of concern for the database. The database implemented here is tested for all the functional capabilities that are required for the National Treasure Online Shop. However, when it will be used actually along with the website, than, there may be possibility of some changes on it based on the feedback, error, etc from customer and website staff. In conclusion, it can be said that the database implemented meets all the requirements, and the exercise as a whole. Read More