TABLE OF CONTENT

1.0 INTRODUCTIONS...............................................................................................................................2
2.0 THE FIRST SITE:.................................................................................................................................2
3.0 THE SECOND SITE:............................................................................................................................6
4.0 THE THIRD SITE:..............................................................................................................................10
5.0 LIST OF ATTRIBUTE FROM THE THREE SITES..........................................................................13
6.0 LIST OF REJECTED ATTRIBUTES WITH REASONS...................................................................14
7.0 LIST OF ADDED ATTRIBUTES.......................................................................................................14
9.0 NORMALIZATION............................................................................................................................16
10.0 EXTENDED ENITITY RELATIONSHIP DIAGRAM....................................................................18
10.1 JUSTIFICATION..............................................................................................................................18
11.0 TABLE SHEMA...............................................................................................................................18
12.0 CREATION OF TABLES.................................................................................................................21
13.0 INSERTING VALUES INTO TABLES...........................................................................................25
14.0 QUERY.............................................................................................................................................28
1.0 INTRODUCTIONS
Database design is a very crucial technical practice in the world of information technology. Analyzing
data for proper design is one key factor in reducing redundancy from database. Redundancy is a key
factor to consider as the same data in different table can cause redundancy if one table is updated without
the others. In this report I will be analysis data fields from three website, this data I will be using in
creating entities and the attribute for a small commercial database.

The sites are

www.tommyhilfiger.com

www.replica-watches.net

www.markandspencer.com

2.0 THE FIRST SITE: www.tommyhilfiger.com

The interactive page of the first site tommy Hilfiger starts for the home page where prospective customers
are welcomed.
Figure 1: tommy Hilfiger welcome page.

Customer selects the category of item him requires.

Figure 2: category selection

Customer makes choice of product he wants, which is then added to a cart bag.
Figure 3: product selection

This product is added to the cart bag where customer is asked to checkout to the payment section.

Figure 4: cart bag

Customer is asked to sign in to make order.

Figure 5: Customer sign in

New customer sign up to order while already registered customer sign in with their email

Figure 6: new customer registration

Customer are asked to key in their payment detail
Figure 7: payment details

3.0 THE SECOND SITE: www.replica-watches.net

The second site replica watches.net start like that of the first, customer are welcomed and told about the
different category of watches then the customer select the brand name he/her want from the list.
Figure 8: replica watch home page

From the brand the customer are presented with the different brand in stock

Figure 9: watch brand

When customer selects the brand of watch he wants he is informed about the price

Figure 10: watch price

After this customer is asked to either continue or checkout to payment section.

Figure 11: checkout section

When customer checkout they are presented with the payment section page

Figure 12: payment page.

4.0 THE THIRD SITE: www.markandspencer.com

Customers are welcomed to the site at the home page and informed about their different types of product.

Figure 13: welcome page

Customers select the product she wants from the list of available product type
Figure 14: select product page

When customer selects the item to add to her shopping bag, she is asked to either continue or check out.
Figure 15: checkout page.

When customer check out they are asked sign in if already registered customer.
5.0 LIST OF ATTRIBUTE FROM THE THREE SITES
Tommy hilfiger Mask and spencer Replica watches
Fist name Title First name
Last name First name Last name
Address line 1 Surname Address
Address line 2 My email address City
City Retype password Zip code
State Enter password Country
Zip code Date of birth Phone
Phone number Title Email
Card number First name Card number
Security code Hand phone Card type
Expiry date Alternative phone number Expiry date
Country House number Cvc code
Address line 1 Postcode Card issuing bank
Address line 2 country Product
City Shipping First Name
Quantity
Post code
Shipping Last Name
Phone First Name:
Shipping Address
Last Name:
Shipping City
Date of Birth:
Shipping State
E-Mail Address:
Shipping Post Code
Street Address:
Shipping Country
Password
Card number
PasswordConfirmation :
Card type
Expiry date Post Code:
Cvc code
City:
Card issuing bank
Product E-Mail Address:
Password
Payment method
Card type
Credit Card Number
Expiry date
Card security number
Item category or make
Shipping First Name
Shipping Last Name
 Shipping Address
Shipping City
Shipping State
Shipping Post Code
Shipping Country

6.0 LIST OF REJECTED ATTRIBUTES WITH REASONS

E-Mail Address:
Street Address:
Password
Password Confirmation:
My email address

Retype password

Enter password

Reason: this data were rejected because they were vague now and been repeated causing redundancy.

7.0 LIST OF ADDED ATTRIBUTES

Customer number

Category number

Brand number

Product ID

Brand name

Brand specification

Price

8.0 GENERIC LIST

First name
Last name
Address
City
Zip code
Country
Phone
Email
Product
Quantity
Date of Birth:
Post Code:
City:
Payment method
Card type
Credit Card Number
Expiry date
Card security number
Item category or make
Shipping First Name
Shipping Last Name
Shipping Address
Shipping City
Shipping State
Shipping Post Code
Shipping Country
Customer number

Category number

Brand number

Product ID

Brand name

Purchase number
Purchase name

Purchase id

Purchase date

Brand specification

Price

Delivery method

Delivery date

Delivery ID

9.0 NORMALIZATION
ATTRIBUTES 1NF 2NF 3NF
Customer number Customer number Customer number CUSTOMER
First name First name First name Customer number
Last name Last name Last name First name
Address Address Address Last name
City City City Address
Zip code Zip code Zip code City
Country Country Country Zip code
Phone Phone Phone Country
Email Email Email Phone
Product Date of Birth: Date of Birth: Email
Date of Birth:
Quantity Payment method Payment method ----------------------
Date of Birth: Card type Customer number* PAYMENT
Card type Payment method
{ Credit Card Number
Credit Card Number Customer number*
Payment method Expiry date Card type
Card type Expiry date
Card security number Credit Card Number
Credit Card Number Card security number
Category number Expiry date
Expiry date Item category Category number
Product ID* Card security number
Card security number Shipping First Name Item category CATEGORY
Category number Shipping Last Name Category number
Shipping id
Item category Shipping Address
Customer number* Product ID*
Shipping First Name Shipping City
Shipping First Name Item category
Shipping Last Name Shipping State
Shipping Last Name SHIPPING DETAIL
Shipping Address Shipping Post Code Shipping Address Shipping id
Shipping City Shipping Country Shipping City Customer number*
Shipping State Product ID Shipping State Shipping First Name
Shipping Post Code Product make Shipping Post Code Shipping Last Name
Shipping Country Quantity Shipping Country Shipping Address
Product ID Price Shipping City
Product ID
Product make
Category number* Shipping State
Quantity Brand number Product make Shipping Post Code
Brand number Brand name Quantity
Brand specification Shipping Country
Brand name
Brand specification ---------------------- Brand number PRODUCT
Price Purchase number Brand name Product ID
Purchase number Purchase name
Purchase name Brand specification Category number*
Purchase id Product make
Purchase id Purchase date --------------------------
Purchase date Delivery method Quantity
Delivery method Purchase number
Delivery date BRAND
Delivery date Delivery ID Product ID*
Delivery ID Brand number
Purchase name
} Brand name
Purchase time
Brand specification
Purchase date
PURCHASE
Delivery method
Purchase number
Delivery date Product ID*
Delivery ID Purchase name
Purchase time
Shipping id*
Purchase date
amount
DELIVERY
Delivery method
Delivery date
Delivery ID
Shipping id*
10.0 EXTENDED ENITITY RELATIONSHIP DIAGRAM

CATEGORY BRAND PRODUCT

PURCHASE
SHIPPING PAYMENT

DELIVERY CUSTOMER

10.1 JUSTIFICATION
Categories have one to many relationships with brand as brand there are many brand existing in
each category. Brands have one to many relationships with product as many product items are of
different brand. Products have one to many relationships with purchase because many products
can be purchase once. Purchase has one to many relationships with payment because one
purchase can have many payments. Payment have one to one relationship with payment because
product paid for are shipped once. Shipping experience one to many relationships with delivery
as many deliveries can be done using one shipping detail. Delivery has one to many relationships
with customer because many deliveries can be for one customer.

11.0 TABLE SHEMA

ATTRIBUTE DECRIPTION PRIMARY KEY FOREIGN KEY
CUSTOMER
Customer number Varchar (30) Primary key
First name Varchar (30)
Last name Varchar (30)
Address Varchar (300)
City Varchar (30)
Zip code Varchar (10)
Country Varchar (20)
Phone Int
Email Varchar (30)
Date of Birth: date

PRODUCT
Product ID Varchar (30) Primary key
Foreign key
Category number* Varchar (30)
Product make
Varchar (30)
Quantity
int

PAYMENT
Payment method Varchar (30) Primary key
Foreign key
Customer number* Varchar (30)
Card type Varchar (30)
Credit Card Number Varchar (30)
Expiry date Date
Card security number Varchar (30)

DELIVERY
Shipping id* Varchar (30) Foreign key
Delivery method Varchar (30)
Delivery date Date
Delivery ID Varchar (30)
Primary key

CATEGORY
Category number Varchar (30) Foreign key
Primary key
Product ID* Varchar (30)
Item category Varchar (30)
SHIPPING DETAIL
Shipping id Varchar (30) Primary key
Foreign key
Customer number* Varchar (30)
Shipping First Name Varchar (30)
Shipping Last Name Varchar (30)
Shipping Address Varchar (300)
Shipping City Varchar (30)
Shipping State Varchar (30)
Shipping Post Code Varchar (30)
Shipping Country Varchar (30)

BRAND
Brand number Varchar (30) Primary key
Brand name Varchar (30)
Brand specification Varchar (30)

PURCHASE
Purchase number Varchar (30) Primary key Foreign key
Product ID*
Varchar (30)
Purchase name
Varchar (30)
Purchase time
Purchase date date
amount
12.0 CREATION OF TABLES
Creation of table customer
Creation of table brand

Creating table shipping

Creating table delivery

Creating table payment

Creating table category

Creating table product

Creating table purchase
13.0 INSERTING VALUES INTO TABLES
Inserting values into brand

Inserting values into category

Inserting values into customer table
Inserting values into table shipping

Inserting into table delivery

Inserting values into table payment

Inserting values into table purchase

14.0 QUERY
1 Query for retrieving customer detail using name
2 query for selecting and making table union

3 Query for updating tables