Database Design Table
Using Excel to do normalization
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
Sales
ProductNameID ProductName ProductDescription ProductTypeID ProductType Description
1 Dell Desktop Tower dummydata 1 Desktop Tower dummydata
2 Asus Monitor dummydata 2 Monitor dummydata Color Key
3 Microsoft Keyboard dummydata 3 Keyboard dummydata Primary
4 Logitech Mouse dummydata 4 Mouse dummydata Foreign
ProductID ProductNameID ProductTypeID DateListed MerchantID QuantityInStock Price SalesTypeID IsAvailable
1 1 1 9/19/20 1 50 500 1 T
2 2 2 9/18/20 2 40 200 1 T
3 3 3 9/17/20 3 70 50 2 T
4 4 4 9/16/20 4 0 30 2 F
MerchantID Merchant Name ServicesNameID ServiceName Description SalesTypeID Type Description
1 Dell 1 Amazon Music dummydata 1 In Person dummydata
2 Asus 2 Netflix dummydata 2 Online dummydata
3 Microsoft 3 Google Work dummydata
4 Logitech 4 OneDrive dummydata
5 Amazon SubscritionTypeID Type Description
6 Netflix 1 Monthly dummydata
7 Google 2 Annual dummydata
ServiceID ServiceNameID ServiceTypeID MerchantID IsAvailable DateAvailable Price SubscritionTypeID
1 1 1 5 T 8/1/20 2.99 1
2 2 2 6 T 10/31/20 12.99 1
3 3 3 7 T 2/1/20 100 2
4 4 4 3 T 7/31/20 130 2
ServicesTypeID ServiceType Description
1 Music Streaming dummydata
2 Video Streaming dummydata
3 Productivity dummydata
4 Storage dummydata 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 Normalization
Practical Approach
Goal for this lesson
The goal for this lesson is to try and create database from the list of components.
Once again you are in charge of the corporation.
This time you are in the Hollywood.
You are building the movie production database.
You must build the database that will help me produce movies or shows
Ready, set, GO.
Step One
Where would you begin?
What are the components of the movie?
What should you do first?
Please take 10 minutes and allocate core components.
Once done, proceed to the next slide.
Step two
So you have the components that you listed. What is the next step?
The next step would be to select entities. Entities are nouns. So from the list of components select key components.
You should have 6 or 7 major components by this time.
Make a basic diagram out of these components. It is OK to use M:M relationship for now.
You will remove M:M relationship in a little bit.
As you create these entities, make sure that you pick right names.
Think of active nouns that you can use.
Step two
Have you decided on main IDs like we did in prior classes.
Keep in mind that table name is unique in the database. You cannot have duplicates.
Column name is unique to the table and you cannot have duplicates.
You can have same column name in different table.
Hierarchhy is as follows: ServerNameDatabaseSchemaTableNameColumnNameColumnType
In this class you will see servers, databases, tables, columns and types only.
We will not focus on Schema.
Take 10 minutes and complete initial design
Step two
You should end up with something like this:
Table name
Table name
Table name
Table name
Table name
It is ok to have missing connections. You will develop them later
Step three
So now you have main entities.
Keep the list running and add any ideas to the list that you may think.
Creation of the database is iterative process.
You design, grow, re-design, change, alter, delete, and repeat the whole process from start.
Start with logical part.
Take note that I have not shown you a single line how to actually crate a table statements.
The whole trick is to design something so well that it would be a very simple task to create tables directly from the logical part.
Step three
Now, for each entity that you located
I need you to list all relevant columns that help to describe each entity
What are relevant columns and how they describe entity
Person
Hight
Weight
Eye Color
Name
Step three
Please take a moment and go back to prior slide
When I designed person I made an intentional mix-up
Can you find it? Once you found it, proceed to the next slide
Step three
Person
Hight – biological
Weight -biological
Eye Color – biological
Name non biological
If I would be designing a person table then I will have to split these two types separately.
Take 10 minutes and determine why I need to split them up. Once ready, proceed to the next slide.
Step three
You need to determine what is stored once and what can change
You need to determine what is unique and what is not
You need to determine what describes the entity and what entity it truly is
Person:
PersonID
FirstName
LastName
SS
DOB
VitalStatistics:
VitalStatisticsID
PersonID
Weight
Height
Blood Pressure
Describes Person
Describes Other Details about the person and can have multiple row as statistics change
Step four
For each entity that you listed:
Create a list that helps you identify each entity correctly
You may have extra details, put them on the side for now but do keep them
Excel is your friend in this exercise
As you list your future columns, think what data you will be storing
This will come handy when you will be converting your design into code
Once done,
Do you have columns hat do not belong to the entity?
How do they describe your entity?
Are they aggregates of any form? If so, remove those immediately. You will calculate it on the fly.
Step five
Time to build your first diagram. The goal of this exercise is to get you started in the right direction.
You task is to create it and post it in the board. All students are required to post their version.
Actors, Staff, Specialists, Consultants, Producers, Tech People, Extras
Movies, Shows, Series, Short Films
Filming locations and logistics
Physical Aspects of Movie Making
Scripts, Writers, Logistics
Technical Aspects
Legal Problems and Representations
Financial Aspect
Step Five
Pick as many of the components as you can
Think through the details of each component
How do these components fit within the database
Create tables for each component similarly to the prior class
Design table and make them work together
The more components you create, the easier it will be for your project when you have to do this on your own
Step 6
Deliver your final solution to the discussion board
Comment on at least 3 other class students
You need to submit 2 positive comments and 2 constructive criticism
Based on critical comments, alter your design and re-post updated version
The goal of this exercise is to see how other people analyze exactly same problem and how your designs will be different from each other.
You will see same results in your projects when you see how different people solve different problems
You should be able to finish this exercise within 3 to 4 hours maximum
Switching gears
I have another exercise for you
This one we will take more in online mode
You have to move past 3NF into uncharted territory
You will be presented with the list of fields to normalize
This will be progressively more difficult exercise
Do not advance to the next slide unless you have finished current assignement
Step one
Please analyze the following columns:
Name
Address
Email
Phone
Employment
What you will notice that this is very high-level data
This is denormalized data
It is not in 3NF
You cannot create database with this. (You can but it would not be in 3NF)
Step one
Your first task is to break these columns in prior slide down into more manageable components.
Take 10 minutes and complete the task
Do not advance to the next slide until you finished with this task
Step one
Please analyze the following columns:
Name
Address
Email
Phone
Employment
FirstName
LastName
SS
DOB
Gender
Address
City
State
Zip
Country
Phone
Email
Employer
EmployerContact
Keep in mind that there can be many more columns. If you have more then this then this is highly advisable.
Step two
Please build database out of these columns and normalize them
Do not advance to the next slide until you solve this
Step three
Now you need to make additions to your new database
The new requirement is that your database has the following functionality:
Each person can have more then one address. New address types should be Primary, Secondary, Employment, School
Can your database store this data? If no, Please make adjustment
Did you separate person and address in two different tables? If no, please separate them now.
Establish relationship with Person ID (this is hint)
Step four
There are more additions to your database
You need to alter your database so that now you can store multiple email addresses for each person.
Addresses can be of certain types: Primary, Secondary, Work, School, Junk
Please make the change now
Hint: Use PersonID as your guide
Step five
Person can have more then one phone
Please alter your database to handle this requirement
Hint: Use PersonID as your guide to establish the relationship
Step six
You have new requirement to handle
You need to extend the database so that you maintain work history for a person
Person can work for more then one company at a time
You need to maintain history for all workplaces
Hint: Use PersonID
Hint: You can use two dates: start and end date of employment as your guide
Hint: Bland end of employment date means person is still working there
Step seven
You have new requirement to handle:
For every place that currently works, or worked in the past, you need to keep a list of all supervisors.
Person can have more then one supervisor in any corporation
You also need to know if this employment was full time or part time.
Hint: Use PersonID or some form of equivalent data that you will crate.
