Database Design Table
Data Normalization
Exercise
You are a CIO for the corporation. You were hired to lead this corporation. You deliver packages all over the world and you function as a virtual marketplace. The name of your company is AMAZON.
Keep in mind that this is logical exercise. We are not coding yet.
Exercise
You need to determine your most basic units of operation. Please take a moment to identify them. Do not go to the next slide until you do.
Exercise
You should end up with something like this:
Products and Services – Online Sales
Billing and Payments
Customer Support
Shipping and Delivery Services
Merchant Interface and Merchant Services
Main Entity: an individual that opens the account: merchant, customer, employee, temp, part-time…
You may have others as well
Exercise
Take 10 minutes and think through these components and try to design them.
List all possible fields that you many need for each component
exercise
Products and Services – Online Sales
Products
Types of products
Prices
On Sale
Get More than one and save
Subscription Services for regular shipments
Product description
Similar products
User Comments and Rating System
Availability of the Product
Some choices may be Available, Back Order, Sold Out
Exercise
Billing and Payments
Who made the purchase
How much
Was there any sales
What type of payment was used
Is this valid payment type?
Can this be shipped?
Is the address valid?
Billing Address
Shipping Address
Is this a present
exercise
Customer Support
Who is the customer
Who is the representative
When was customer support contacted first
Who should follow up
Was issue resolved
Successful
Unsuccessful
Pending
Type of the issue
Description of the issue
exercise
Shipping and Delivery Services
Shipping Address
Is this permanent or mailing address is there another type that we need?
What are shipping charges
Is this witting the United States?
Can I even deliver to this address?
How much would it cost?
Who will deliver it?
How heavy is the package? Do I need extra money for the delivery?
What are the dimensions?
Do I need specific services to deliver it such a newly printed books?
Is this delivered online like music or the movie?
exercise
Merchant Interface and Merchant Services
Who are my merchants
Types of merchants
Do they have corporations or are they solo
What products do they sell
How can they post products for sale
How do they get paid
Who receives payment
How do they ship products to customers
Who is responsible for shipment and delivery: Amazon or customer
How do I interact with merchants
Hardest exercise
Main Entity
All roads lead to Rome
All roads lead to the Entity called Person
Person is responsible for all parts of the application
Types of people: merchant, employee, supervisor, director, sales, advertising, driver, helper, and much more
What is the relationship of the person with other parts of the database
How is this information is flowing through the rest of the database
What information do I need to know about each person that interacts with Amazon
Person
On the next slide you will see and approach to the design of the database
I want you to take is as a starting point
Find weak spots
Finds spots that need to be extended
Find what is missing
Fill in data
Person
Person needs a set of data that uniquely identifies each person in the system
Each person may have a different type that it belongs to
Each person needs to use name and password so that person can log into the system
Each person needs an address
Person can have more than one address for this reason person has an address type that identifies which address this is
Person
Each person needs a specific set of permissions as to what this person can do in the system
Person can have more than one permission set
Please take 30 minutes and create these components
Once you are done, please proceed to the next slide
Compare results
You will have to do this task on your own for your own project so do not look at the solution until you tried to design your own
Person
| PersonID | FirstName | LastName | PersonTypeID | SS# | DOB |
| PersonTypeID | Type | Description |
| Individual | ||
| Corporate | ||
| UserLogInID | UserLogin | Password |
| AddressID | PersonID | AddressTypeID | Address | City | State | Zip |
| AddressTypeID | AddressType | Description |
| Home | ||
| Mailing |
| AvailableComponentsID | ComponentTypeID | PersonID |
| ComponentID | Description |
| Can Sell | |
| Can Publish |
Products And Services
This is a new exercise
You need to design products and services table
You need a place to store all products and services that Amazon lists for sale
Products have types, names merchants, they can be on sale, they can be available.
Same the list applies to Services
Take 30 minutes
List all columns that you may need for this exercise
Do not look at the solution until you are done
Keep in mind that you need to practice the third normal form for your tables
Products And Services
Product can be used multiple times and therefore must be stored in a separate table
Merchant must be stored in a separate table
Product Type must be stored in a separate table
Sales information must be stored in separate table
Sales type must be stored in a separate table
Service must be stored in a separate table and follow a similar structure to the products table
Take required time now and create a set of the tables, once done, then proceed to the next slide
Products And Services
| ProductID | ProductNameID | ProductTypeID | DateListed | MerchantID | QuantityInStock | Price | SalesPriceID | IsAvailable |
| ProductNameID | ProductName | ProductDescription |
| ProductTypeID | ProductType | Description |
| MerchantID | MerchantName |
| SalesPriceID | SalesTypeID | Percentage |
| SalesTypeID | Type | Description |
| ServiceID | SerivceNameID | SerivceTypeID | MerchantID | IsAvailable | DateAvaialbe | Price | SalesID |
Products And Services
Prior slide has one or more tables missing
Please find it and report back to me what is missing and how do I fix the problem
Fill in some data
Do not proceed past this point until you find what is missing
Filling in data helps
Note: you may skip some tables if you find them confusing. They are here for the purpose of this exercise
Products And Services
| ServiceNameID | ServiceName | Description |
| ServiceTypeID | ServiceType | Description |
These are the missing tables. Please establish an appropriate relationships and finish this work.
Products And Services
| ProductID | ProductNameID | ProductTypeID | DateListed | MerchantID | QuantityInStock | Price | SalesPriceID | IsAvailable |
| ProductNameID | ProductName | ProductDescription |
| ProductTypeID | ProductType | Description |
| MerchantID | MerchantName |
| SalesPriceID | SalesTypeID | Percentage |
| SalesTypeID | Type | Description |
| ServiceID | SerivceNameID | SerivceTypeID | MerchantID | IsAvailable | DateAvaialbe | Price | SalesID |
| ServiceNameID | ServiceName | Description |
| ServiceTypeID | ServiceType | Description |
Products And Services
Keep in mind that there is more than one way that this can be done
Can you think of another way that this can be designed?
If so, post your ideas in the discussion board
Merchant
Take 30 minutes and design merchant section
You can have the list of multiple merchants
Merchants have sales representatives, can have more then one
Merchants have addresses and can have more than one and addresses have types same as in prior slides
You do not get a lot of hints on this
Think about merchants and design the table
Once done, progress to the next slide for answers
Merchant
| AddressID | PersonID | AddressTypeID | Address | City | State | Zip |
| AddressTypeID | AddressType | Description |
| MerchantID | MerchantTypeID | MerchantName | IsActive |
| SalesPersonID | PersonID | SalesPersonTypeID |
| MerchantAddressID | MerchantID | AddressID | MerchantAddressTypeID | DateUsed |
| MerchantAddressTypeID | AddressType | Description |
| MerchantSalesLogID | SalesID | DateOfSale | SalesPersonID |
From Sales Table
| MerchantTypeID | TypeName | Description |
Merchant
I hope that you have a different version from me
In both cases, take your time and improve my version
Think about other possibilities that merchants might have
Design better diagram and post it to the discussion board
Prior diagram may or may not contain inconsistencies or hidden improvements, please find them and report them to the discussion board
Other sections
You are responsible for designing other sections
You have two weeks to design them
Post them in the discussion board once they are ready
If you are interested in performing this task sole, you can
If you want to join the group, please let me know as well
I expect more work from the group
Keep in mind that this should be in 3NF
This exercise is designed to help you build your own project