1
Normalization Example
Guide
Interview
Domains
Assumptions
FD Diagrams
Entities
Some Variations
Change in Assumptions = FD !hange
"ultiple use o# Domains == Entit$ !hange
Di##erent Attri%utes == Entit$ !hange
FD and E& relations
'
Guide
( De#ine the Domains Atomize as less as possible
' De#ine the internal Format Use the one that will cover for all views
) *rite the initial semanti! assumptions
+ Draw the dependen!ies diagram Connect all domains
, Determine dire!tion o# the arrows Using Functional Dependencies
- Eliminate transitive dependen!ies
. /%tain the entities Underline the independent domains as PKs
0 *rite down additional semanti! assumptions
1 2resent the the domains and the entities to the user
(3 Get $our designed approved and SIGNED
A good design should be between
2 to ! domains and " to # entities per s$stem
)
Interview
Manager Manager
Listen to me, we want you to set up the most modern system in place, so go ahead and Listen to me, we want you to set up the most modern system in place, so go ahead and
tell me what machine to buy and the advantages we get. Something like a tell me what machine to buy and the advantages we get. Something like a
database, you know. database, you know.
Listen I am not an expert; this is why I called you. Yet I can tell you that this company Listen I am not an expert; this is why I called you. Yet I can tell you that this company
is dedicated to manufacture elegant fittings that are used in good furniture, rather is dedicated to manufacture elegant fittings that are used in good furniture, rather
than the awful nails and screws you see in cheap furniture. ith them you can than the awful nails and screws you see in cheap furniture. ith them you can
make modular designs. !y the way, have you gone to I"#$ or !eautiful make modular designs. !y the way, have you gone to I"#$ or !eautiful
"itchens in %allas or &ouston, you should. "itchens in %allas or &ouston, you should.
$nyway, a piece of furniture has different types of fittings, an each piece re'uires a $nyway, a piece of furniture has different types of fittings, an each piece re'uires a
certain amount. (he same fittings are also used in other type of furniture, such as certain amount. (he same fittings are also used in other type of furniture, such as
a () stand, a bookshelf, a table or a chair, but the amount is different for each a () stand, a bookshelf, a table or a chair, but the amount is different for each
piece. piece.
$lso we have an order system, and for each order we keep the information on the $lso we have an order system, and for each order we keep the information on the
delivery address, the name of the customer, the 'uantity ordered, the type of delivery address, the name of the customer, the 'uantity ordered, the type of
fitting, the name of the customer. You know, mostly we deal with manufacturers. fitting, the name of the customer. You know, mostly we deal with manufacturers.
*or each order we keep a number and a detailed line for each fitting or item *or each order we keep a number and a detailed line for each fitting or item
ordered. In any case, we know the price of each item and how many are needed ordered. In any case, we know the price of each item and how many are needed
for a given type of furniture, so we can plan our production. for a given type of furniture, so we can plan our production.
!e my guest. !e my guest. Since we get the fittings from various manufacturing plants *or each Since we get the fittings from various manufacturing plants *or each
fitting we also need to know the plant where it is manufactured and 'uantity in fitting we also need to know the plant where it is manufactured and 'uantity in
stock. +ertainly each plant provides us with various fittings of the same kind as stock. +ertainly each plant provides us with various fittings of the same kind as
other plants. *inally for each fitting we know its type, its 'uality and a other plants. *inally for each fitting we know its type, its 'uality and a
description. *or each of our customers we keep his,her addresses. e provide description. *or each of our customers we keep his,her addresses. e provide
discounts based on 'uantity only. discounts based on 'uantity only.
%ere&e'o (nterprises )imited is dedicated to manufacture of fittings
used in furniture* +he business is booming and the$ want to have a
solid database to help fill their orders* Knowing $ou are a ,+ gu$ that
-nows it all. the$ have contacted $ou. $ou made an appointment with
the manager/ and as the$ sa$. the rest is histor$.
. -here is short transcript of your . -here is short transcript of your
interview interview. . You You
// #asy does it Sir. *irst I need to know your // #asy does it Sir. *irst I need to know your
Information Reality Information Reality, this is to say, what , this is to say, what
reports you use, what are your input formats reports you use, what are your input formats
in your order and so on 0 in your order and so on 0
0-nowing that this is what $ou need1 0-nowing that this is what $ou need1
1 1 )ery interesting, tell me more )ery interesting, tell me more
02ust -eep him tal-ing. it is important to 02ust -eep him tal-ing. it is important to
record ever$thing1 record ever$thing1
1 1 I will have some coffee. %o you mind2 I will have some coffee. %o you mind2
0-nowing that there is nothing more coming 0-nowing that there is nothing more coming
from him for the time being1 from him for the time being1
1 1 )ery well let me work a bit on this and I )ery well let me work a bit on this and I
will propose you a database design before we will propose you a database design before we
go any further. go any further.
+
Domains
3. *urniture I%4 Integer (3ample 4 52
5. 6iece %escription4 String ( 40 ) (3ample 4 6+7 8tand9
7. $ddress4 String (40) (3ample 4 65#": ;ellaire . %ouston<
8. +ustomer I%4 Integer (3ample 4 #"
9. *itting I%4 Integer (3ample 4 !2
:. *itting %escription4 String ( 40 ) (3ample 49=edium hinge9
;. <uality4 String ( 10 ) (3ample 49;rass<
=. >rder ?umber4 Integer (3ample 4 !25#
@. %ate4 Date long (3ample 4 !2>!2>2#
3A. %etail Line4 Integer (3ample 4 !5
33. <uantity >rdered4 Integer (3ample 4 ?
35. B6lant I%4 Integer (3ample 4 !2
37. Stock4 Integer (3ample 4 #5@
38. B6lant ?ame4 String (30) (3ample 4 9Denton<
39. )olume4 Integer (3ample 4 "
3:. %iscount4 Integer (3ample 4 2"
3;. 6rice4 Float (3ample 4A"B*:?
3=. <uantity Ce'uired Integer (3ample 4 5#
!
,
Assumptions
D
In each plant various fittings are manufactured
D
(he same fitting is manufactured in different plants
D
(he discount is based on volume only
D
(he customer has various shipping addresses
D
(he same fitting is used in different pieces of furniture
D
$ piece of furniture uses various fittings
D
$n order is comprised of more than one detail lines
-
FD Diagram
Quantity
Required
Price
Fitting
Description
Quality
MPlant Name
Discount
Piece
Description
Address
rder
Num!er
Detail "ine
Quantity rdered
#ustomer ID
Date
Furniture ID
Fitting ID
MPlant ID
Stoc$
Discount 4 E Quantity Ordered, %iscount F
Furniture4 E Furniture ID, 6iece %escription F
Addresses 4 E ddre!!, +ustomer I% F
(nsembles4 E Fitting ID, Furniture ID, <uantity Ce'uired F
Fittings 4 E Fitting ID, *itting %escription, <uality, 6rice F
Crders 4 E Order "um#er, $ddress, %ate F
Details 4 E Order, Detail $ine, <uantity >rdered, *itting I%F
8toc-s 4 EM%lant, Fitting ID, Stock F
Plants 4 E M%lant, B6lant %escription F
.
Discount 4 E Quantity Ordered, %iscount F
Furniture4 E Furniture ID, 6iece %escription F
Addresses 4 E ddre!!, +ustomer I% F
Fittings 4 E Fitting ID, *itting %escription, <uality, 6rice F
Crders 4 E Order "um#er, $ddress, %ate F
Details 4 E Order "um#er, Detail $ine, <uantity >rdered, *itting I%F
Plants 4 E M%lant, B6lant %escription F
8toc-s 4 EM%lant, Fitting ID, Stock F
It is all in t%e
It is all in t%e
relations
relations
(nsembles4 E Fitting ID, Furniture ID, <uantity Ce'uired F
0
Entities
3. Furniture4 E Furniture ID, 6iece %escription F
5. Addresses 4 E ddre!!, +ustomer I% F
7* (nsembles4 E Fitting ID, Furniture ID, <uantity Ce'uired F
8. Fittings 4 E Fitting ID, *itting %escription, <uality, 6rice F
9. Crders 4 E Order "um#er, $ddress, %ate F
:. Details 4 E Order, Detail $ine, <uantity >rdered, *itting I%F
;. 8toc-s 4 E M%lant, Fitting ID, Stock F
=. Plants 4 E M%lant, B6lant %escription F
@. Discount 4 E Quantity Ordered, %iscount F
(0 domains (0 domains with with 1 entities 1 entities
Accept
&
1
'%e !igger picture(
'%e !igger picture(
Discount 4 E Quantity Ordered, %iscount F
Furniture4 E Furniture ID, 6iece %escription F
Addresses 4 E ddre!!, +ustomer I% F
Fittings 4 E Fitting ID, *itting %escription, <uality, 6rice F
Crders 4 E Order "um#er, $ddress, %ate F
Details 4 E Order "um#er, Detail $ine, <uantity >rdered, *itting I%F
Plants 4 E M%lant, B6lant %escription F
8toc-s 4 EM%lant, Fitting ID, Stock F
(nsembles4 E Fitting ID, Furniture ID, <uantity Ce'uired F
Inventories
"anu#a!ture
Customers
Finan!e
More domains and entities,
More domains and entities,
but within the same
but within the same
Database
Database
4his s$stem is 5ust a su%s$stem that relates 4his s$stem is 5ust a su%s$stem that relates
to other s$stems in the enterprise to other s$stems in the enterprise
(3
#%ange in Assumptions
) * FD c%ange )* Di++erent Entities Di++erent Entities
3. Furniture4 E Furniture ID, 6iece %escription F
5. Addresses 4 E ddre!!, +ustomer I%F
7* (nsembles4 E Fitting ID, Furniture ID, <uantity Ce'uired F
8. Fittings 4 E Fitting ID, *itting %escription, <uality, 6rice, Bplant, Stock F
9. Crders 4 E Order "um#er, Ship$ddress, +ustomer I% ,%ate F
:. Details 4 E Order, Detail $ine, <uantity >rdered, *itting I%F
;. Plants 4 E M%lant, B6lant %escription F
=. Discount 4 E Fitting ID, Quantity Ordered, %iscount
D In each plant various fittings are manufactured
D (he fitting is manufactured in Gust one plant
D (he discount is based on volume and fitting
D #ach order may have a different shipping address
D (he same fitting is used in different pieces of furniture
D $ piece of furniture uses various fittings
D
$n order is comprised of more than one detail lines
(1 domains (1 domains with with 0 entities 0 entities
((
"ultiple use o# Domains = Entit$ !hange
3. Furniture4 E Furniture ID, %escription F
5. Addresses 4 E ddre!!, +ustomer I%F
7* (nsembles4 E Fitting ID, Furniture ID, <uantityF
8. Fittings 4 E Fitting ID, %escription, <uality, 6rice, Bplant, Stock F
9. Crders 4 E Order "um#er, $ddress, +ustomer I% ,%ate F
:. Details 4 E Order, Detail $ine, <uantity, *itting I%F
;. Plants 4 E M%lant, %escription F
=. Discount 4 E Fitting ID, Quantity, %iscountF
(- domains (- domains with with 0 entities 0 entities
Although it !an %e solved in the di!tionar$ with Although it !an %e solved in the di!tionar$ with
some name !hanges6 it is not a good idea to some name !hanges6 it is not a good idea to
over do it7 over do it7
&emem%er nowada$s6 dis8 spa!e is rather !heap6 &emem%er nowada$s6 dis8 spa!e is rather !heap6
neurons aren9t :;< neurons aren9t :;<
3. *urniture I%4
5. %escription4
7. $ddress4
8. +ustomer I%4
9. *itting I%4
:. <uality4
;. >rder ?umber4
=. %ate4
@. %etail Line4
3A. <uantity4
33. B6lant I%4
35. Stock4
37. B6lant ?ame4
38. <uantity4
39. %iscount4
3:. 6rice4
!
('
Di##erent Attri%utes == Entit$ !hange
3. Furniture4 E Furniture ID, %escription F
5. Addresses 4 E ddre!!, +ustomer I%F
7* (nsembles4 E Fitting ID, Furniture ID, <uantityF
8. Fittings 4 E Fitting ID, %escription, <uality, 6rice, Bplant, Stock,
*itting +olor, *itting eight F
9. Crders 4 E Order "um#er, $ddress, +ustomer I% , %ate F
:. Details 4 E Order, Detail $ine, <uantity, *itting I%F
;. Plants 4 E M%lant, %escription, 6lant Banager, 6lant Location F
=. Discount 4 E Fitting ID, Quantity, %iscountF
@. Customer4 (&u!tomer ID, +ustomer ?ameF
'( domains '( domains with with 1 entities 1 entities
Sin!e the$ ma$ %e part o# an existing s$stem6 Sin!e the$ ma$ %e part o# an existing s$stem6
%ind them %ind them together in the same data%ase together in the same data%ase
3. *urniture I%4
5. %escription4
7. $ddress4
8. +ustomer I%4
9. *itting I%4
:. <uality4
;. >rder ?umber4
=. %ate4
@. %etail Line4
3A. <uantity4
33. B6lant I%4
35. Stock4
37. B6lant ?ame4
38. <uantity4
39. %iscount4
3:. 6rice4
1'( Fitting )eig*t+
1,( Fitting &olor+
1-( %lant $[Link]+
/0( %lant Manager+
/1( &u!tomer "ame+
!
13
And the E;& model=
color
Fitting
Plant Plant
Description Description
Manager Manager
Quantity Quantity
#odd,s
-eig%t
Plant Plant
#%en,s
#itting
Sto!8
Quantity
-eig%t color
F . P .
Description
Manager
t m
#%en,s
Plant Plant
#itting
Quantity
-eig%t
color
F . P .
Description
Manager
t
(+
"odeling &ealit$
"odeling &ealit$
Enterprise Enterprise
Data!ase Data!ase
A good model generates A good model generates
a lasting design a lasting design
R
e
l
a
t
i
o
n
s
a
n
d
i
n
f
o
r
m
a
t
i
o
n
f
l
o
w
s
dia Num Est
lunes 23 ok
viernes 45 mal
sabado 76 ok
Num Pieza Costo
23 viga $45
45 clavo $67.35
76 aro $17.35
Num Fitting #ost
!rass
%inge
tap
Day Num Status
Mon
'ue
/ed
/rong
Truth is the conformity that exists between
the thing 0reality o+ t%e enterprise1 and the
description of it 0data!ase1
Saint '%omas Aquinas 02334523671