Common and Basic Attributes Across the Three Sites - Assignment Example

Database Design and Implementation Faculty: Department: Module Academic Year: Semester Data investigation and capture 1 of the three websites chosen 1.1.1 http://www.ubid.com This is an online auction site gaining popularity close to that of e-bay. Even though it does not have as much traffic as e-bay, it generates quite a number of sales. The fact that it does not charge listing fee puts it at a greater competitive edge over the similar online purchasing sites. A major pitfall exists in the payment duration; the buyer’s payment can take up to a fortnight since it goes through Ubid which can cause a serious cash flow problem for the new sellers. 1.1.2 http://www.ebid.net/ E-bid auction is one of the largest auction sites in the UK. It is the second largest to be precise. A major downside of this site is that it is very slow. The seller is not required to pay any fee for posting an item for sale. The sites has strong measures and applications installed that ensures that it is fraud proof and the sellers can list as many items as they want as opposed to most online purchasing sites. 1.1.3 http://www.ebay.com E-bay is one of the leading online shopping market place where buyers and sellers transact a wide variety of goods and services worldwide. It has become a multi-billion dollar company after it was founded back in 1995. It has included buy-it-now as a standard shopping, an expansion from its original auction format. A number of items, from cars to antiques, are listed by a seller who then chooses to only accept bids for the item thus enabling the buyer to purchase the item immediately. The first buyer eager to pay the sellers stated price gets the item on a buy-it-now option. 1.2 List of data fields 1.2.1 List of data fields users have to complete from http://www.ubid.com/ Sign in page Login ID Password Register page  First name Last name E-mail address Re-enter e-mail address Create a login ID Create a password Re-enter password Country 1.2.2 List of data fields users have to complete from http://www.ebid.net/ Home page Location Log in page Your eBid Username Your eBid Password Sign up page Choose a username Add your email Where did you hear about eBid Choose a password Confirm password Full Name Street address Street address two City State Zip Code Country Phone Number 1.2.3 List of data fields users have to complete from http://www.ebay.com/ Sign in page User ID Password Sign up Page (Personal account) Country / Region First name Last name Street address Postal code City, State Email address Phone number eBay user ID Password Confirm password Secret question Answer Sign up Page (Business account) Business name Street address 1 Street address 2 City State / Province ZIP / Postal code Country First name Last name Email address Re-enter email address Primary telephone number Secondary telephone (optional) Sell your item Enter a UPC, ISBN, VIN/ keywords that describe your item Title Condition Item Specifics Type Brand Product Line Model MPN Operating System Screen Size Online Auction Starting price Buy It Now price Quantity Duration 1.3 The generic list of attributes derived from the three websites investigated 1.3.1 The generic list A list of common and basic attributes across the three sites include the following Auction ID Starting bid Stopping bid Product ID Reserved price Payment details/ Shipping details Product Image Product Category Seller ID Seller location 1.3.2 Justification All the three sites have five major entities that is; the auction, product, payment, seller and the buyer. Of the four entities only three are actively involved at any given time, mostly, the auction, product and the buyer. The three entities have major common attributes across the three sites most of which were identified as the generic attributes. The product id, seller/.buyer id and the product id alongside other attributes formed the major attributes across the three sites. The other attributes that were rejected were either irrelevant or less important and could be retrieved through the major attributes such as the auction, seller/buyer or product ids. Normalizing the possible tables consisting of the entities alongside their attributes led to the rejection or dropping off of some attributes to avoid data redundancy. 1.3.2.1 Attributes retained Auction ID Starting bid Stopping bid Product ID Reserved price Payment details/ Shipping details Product Image Product Category Seller ID Seller location Additional information Name Contacts Auction Start time Close time Bid price Condition Number of bids Location Quantity Item specifics Product category Product features and benefits 1.3.2.2 Attributes rejected Type Brand Product Line Model MPN Operating System Screen Size Product Manufacturer Originating warehouse Total bids Winning bidder Bid increments Bid history 2. Database design 2.1 Entity Relationship Modelling 2.1.1 Initial Entity Relationship Model 2.1.2 Extended Entity Relationship Model using extended notation 2.2 Normalisation 2.2.1 General Rules for Normalisation (1NF-3NF) http://www.ebay.com/ From the item page it is evident that there are four major entities which form four wide un-normalized tables as follows Auction Auction ID Product id Starting bid Product name Product description Product Price Payment details Seller id Product Image Shipping details Product Product Id Condition Description Image Location Quantity category Specifications Feature Payment Method Date Shipping address Auction id Product id Zip code country delivery Seller Buyer id Location contacts Email address Shipping address Payment details (2NF) Auction The product name, product description, product prices and product image from the auction table can be compressed into a different table named product with a primary key being product id. The auction table therefore will be normalized to Auction Auction id Product id Seller id Payment id Payment details Shipping details This can be further normalised by combining the shipping details and the payment details into the payment table represented by the payment id as the primary key. The resulting auction table is therefore as follows. (3NF) Auction Auction id Product id Seller id Payment id Product (2NF) Product Id Condition Description Image Location Quantity category Specifications Feature Product Product Id Condition Description Image Location Quantity category Specifications Feature Attributes condition, specification and feature can be normalised into a different table called description. The normalised Product table thus; Product (2NF) Product Id Description Image Location Quantity category Description Product id Condition Specification Features Payment Method Date Shipping address Auction id Product id Zip code country delivery The attributes zip code and country can be normalised into the shipping address table thus reducing the payment table to Payment (3NF) Method Date Shipping address Auction id Product id Seller Buyer id Location contacts Payment details 2.2.2 ER Model derived from the 3NF entities 2.3 Database Schema There are several assumptions made in the development of the above database schema. One of the many assumptions made in the design of the database schema is that a seller can have more than one product on auction. One or more products can be on auction at the same time but with different auction identification number. Each auction has its own specific payment details. A product has only one description with a product identity used as the primary key for the unique identification of the product description. 3. Database implementation 3.1 Screen dumps for table creation Auction Product Payment Seller Shipping Prod Description 3.2 Screen dumps for displaying data in tables Auction Table Product Seller Shipping Prod Description 4. SQL Queries 4.1 Query 1 4.1.1 The Query is to be used to display and retrieve all the available products alongside on auction Together with their details. 4.1.2 Query in natural language Display the product Identity number, Auction identity number, Seller identity number, Product name, payment details, price and category from the product table. 4.1.3 SQL Code and output SELECT ProductID,AuctionID, SellerID, PaymentDetails,ProductName,Price,Category FROM `product` 4.1.4 Explain the output of the data (was this what was predicted?) The output displays the list of products together with the attributes as expected. The product id, Auction id, Payment details, Price and the category to which the product belongs is displayed in form of a table as expected from the SQL code. 4.2 Query 2 4.2.1 For what purpose will this query be used in business terms? The query will be used to retrieve data and all the information concerning the products on auctions including their detailed description 4.2.2 Query in natural language Use the primary key ProductID to display all the attributes of the products using the product table and the proddescription table. From the product and proddiscription tables, display; AuctionID, SellerID, PaymentDetails, ProductName,Price, Category,Pcondition, Features, Manufacture, ItemSpecs where the product identification code in the product table equals the product identification code in the product description table. 4.2.3 SQL Code and output SELECT AuctionID, SellerID, PaymentDetails, ProductName,Price, Category,Pcondition, Features, Manufacture, ItemSpecs FROM product, proddiscription WHERE product.ProductID = proddiscription.ProductID 4.2.4 Explain the output of the data (was this what was predicted?) The code joins two tables; product and proddiscription table and outputs the contents of two tables using the primary ProductID. Apart from the product identification code, the query outputs the auction identification code, seller ID, the payment details, product name, Price, category to which the product belong and the state or the condition of the product. 4.3 Query 3 4.3.1 For what purpose will this query be used in business terms? The query will be used to display all the details of a product together with the details of a particular seller. A seller may have more than one product on auction. The query is to be used to list the details of all the products a seller has on auction. 4.3.2 Query in natural language Use both product table and the seller table to display AuctionID,PaymentDetails,ProductName,Price,Category,Location, Address Of seller id code 7030 4.3.3 SQL Code and output SELECT AuctionID,PaymentDetails,ProductName,Price,Category,Location, Address FROM product, seller WHERE product.SellerID = seller.SellerID AND product.SellerID= 7030 4.3.4 Explain the output of the data (was this what was predicted?) The code outputs the expected results that is, the details of the products owned by a seller together with details of the seller. 4.4 Query 4 4.4.1 For what purpose will this query be used in business terms? The query is a supposed to be used to add and insert new entries into the seller table. 4.4.2 Query in natural language Insert the values 7654, 9076,Manchester and 34 MancCity intio the corresponding column of SellerID, ProductID, Location and Address 4.4.3 SQL Code and output INSERT INTO seller (SellerID, ProductID, Location, Address) VALUES (7654, 9076,Manchester,34 MancCity) 4.4.4 Explain the output of the data (was this what was predicted?) The code inserts an additional row in the seller table with the details such as the seller id, product id, location and Address. 4.5 Query 5 4.5.1 For what purpose will this query be used in business terms? The query is to be use to provide a summarised report on the auction details including the payment details of each auction in progress 4.5.2 Query in natural language Display all the data in both auction and payment table using auction ID as the primary key. 4.5.3 SQL Code and output SELECT Product ID, SellerID, PaymentID, Starting bid, NoOf Bids, ClosingBid, PaymentDetails, ShippingID, PaymentMethod FROM auction, payment WHERE auction. auctionID = payment. auctionID 4.5.4 Explain the output of the data (was this what was predicted?) The SQL code outputs data as predicted; the product id, Seller id, starting bid closing bid payment details shipping id and payment methods are displayed form both the auction and payment tables as predicted. 5. References Wilton, P., & Colby, J. W. (2005). Beginning SQL. Indianapolis, IN: Wiley Pub. Read More