Database Design Table
daven9947
Class4Normalization.pptxNormalization
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: ServerName\Database\Schema\TableName\ColumnName\ColumnType
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
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
Phone
Employment
FirstName
LastName
SS
DOB
Gender
Address
City
State
Zip
Country
Phone
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.
