Transcript A presentation for “Cuppa” Corner Tony Rogerson, SQL Server MVP

A presentation for
“Cuppa” Corner
Database Design
A trip to First Normal Form
Tony Rogerson, SQL Server MVP
http://sql-server.co.uk
http://sqlblogcasts.com/blogs/tonyrogerson
http://twitter.com/tonyrogerson
Agenda
•
•
•
•
•
•
Introduction to Normalisation
Domains
Functional Dependencies
Repeating Groups
Relation Valued Attributes
First Normal Form
Tony Rogerson, SQL Server MVP
http://sql-server.co.uk
Introduction to Normalisation
• Nothing to do with Performance
• Nothing to do with SQL
• Nothing to do with Implementation (the
Physical)
• Reduce Data Redundancy
• Method of putting Data into “Relations”
• Sets
Tony Rogerson, SQL Server MVP
http://sql-server.co.uk
Domain is “The Set of Possible Values”
• Examples
– Colours
• Red, Green, Blue
– Age
• 0 to 180
– Car Models
• Jaguar, Ford, Audi
• May be Finite (colours, age) or Dynamic (Car
Models)
Tony Rogerson, SQL Server MVP
http://sql-server.co.uk
Functional Dependency
Determinant
(Many)
•
•
•
•
Dependant
Functionally Determines
(One)
Adds Semantics to the data
Many to one relationship
Left is a candidate key
Right can be another key/part of a
key/attribute
Tony Rogerson, SQL Server MVP
http://sql-server.co.uk
FD Examples
Determinant
(Many)
Dependant
(One)
Functionally Determines
National Insurance Number
Full Name
Car Registration Number
Make of Vehicle
VIN
Car Registration Number
Time of Day
Type of Meal
Tony Rogerson, SQL Server MVP
http://sql-server.co.uk
Repeating Groups
An Attribute having the same meaning and the same “Domain” of values which
is repeated, and the placement of values in those repetitions has no meaning
and is entirely by chance.
Session#
Attendee1
Attendee2
Atendee3
Attendee4
NonFuncDepend
Rosie
Ester
Hazel
Joe
DeNormalisation
Ester
Poppy
Attribute is Attendee
Domain is {Rosie, Ester, Hazel, Poppy, Joe}
Tony Rogerson, SQL Server MVP
http://sql-server.co.uk
Repeating Groups – Not!
An Attribute having the same meaning and the same “Domain” of values which
is repeated, and the placement of values in those repetitions has no meaning
and is entirely by chance.
Attendee#
Address1
Address2
Address3
Town
Rosie
Torver
26 Moorla
Harpenden
Ester
Torver
26 Moorla
Harpenden
Attribute is Address1, Address2, Address3
Domain for Address1 is {Torver}
Address2 is {26 Moorla}
Tony Rogerson, SQL Server MVP
http://sql-server.co.uk
Relation Valued Attribute
Session#
DBA’s
Developer
Analyst
Project Manager
NonFuncDepend
Rosie
Ester
Hazel
Joe
DeNormalisation
Ester
Poppy
Hazel
Joe
Attribute is ?
Domain for ?
Session#
Attendee {JobTitle, Attendee}
NonFuncDepend
{DBA, Rosie},
{Developer, Ester}.
{Analyst, Hazel},
{Project Manager, Joe}
DeNormalisation
....
Tony Rogerson, SQL Server MVP
http://sql-server.co.uk
RVA Expanded
Session#
DBA’s
Developer
Analyst
Project Manager
NonFuncDepend
Rosie
Ester
Hazel
Joe
DeNormalisation
Ester
Poppy
Hazel
Joe
Session#
Job Role#
Attendee#
NonFuncDepend
DBA’s
Rosie
NonFuncDepend
Developer
Ester
DeNormalisation
DBA’s
Ester
Tony Rogerson, SQL Server MVP
http://sql-server.co.uk
First Normal Form
• Identify at least one Candidate Key
• Get rid of Repeating Groups
• Values must be Atomic (no CSV’s)
Sessions
Session#
Attendee1
Attendee2
Atendee3
Attendee4
Room
NonFuncDepend
Rosie
Ester
Hazel
Joe
Hall1
DeNormalisation
Ester
Poppy
Hall2
SessionAttendees
Sessions
Session#
Room
NonFuncDepend
Hall1
DeNormalisation
Hall2
Session#
Attendee#
NonFuncDepend
Rosie
NonFuncDepend
Hazel
NonFuncDepend
Ester
DeNormalisation
EsterTony Rogerson, SQL Server MVP
http://sql-server.co.uk
A presentation for
“Cuppa” Corner
Database Design
A trip to First Normal Form
Thanks for Listening!
Tony Rogerson, SQL Server MVP
[email protected]
http://sql-server.co.uk
http://sqlblogcasts.com/blogs/tonyrogerson
http://twitter.com/tonyrogerson