Chapter Two – Two –
The Relational Model
CHAPTER OBJECTIVES
•
Learn the conceptual foundation of the relational model
•
Understand how relations differ from nonrelational tables
•
Learn basic relational terminology
•
Learn the meaning and importance of keys, foreign keys, and related terminology
•
Understand how foreign keys represent relationships
•
Learn the purpose and use of surrogate keys
•
Learn the meaning of functional dependencies
•
Learn to apply a process for normalizing relations
CHAPTER ERRATA
There are no known errors at this time. Any errors that are discovered in the future will be reported and corrected in the Online !" e#$ %rrata document, which will be available at http&''www.pearsonhighered.com'kroenke.. http&''www.pearsonhighered.com'kroenke
THE ACCESS WORKBENCH
(olutions to the Access Workbench Workbench e)ercises may be found in Solutions to all Sections: The Access Workbench Workbench, which is a separate document within the *nstructor+s anual.
•
TEACHING SUGGESTIONS
The Art "ourse database discussed in "hapter - is a good database to use for an in class demo of the concepts in this chapter. The !( screenshots in "hapter / use that database as the e)ample database. 0or e)ample, see 0igure /1, /2 and /3. (ee the list, data and database files supplied, and use the following& •
•
( Access& •
4Art "ourse List5 in !"e#6ListsAndata.)ls)
•
!"e#$Art"ourseatabase"7#-.accdb
(8L (erver /#-/ %)press %dition& •
!"e#$((8LArt"ourseatabase"reateTables.s9l
•
!"e#$((8LArt"ourseatabase*nsertata.s9l
•
:OT%& "reate a database diagram for the database database
Chapter Two – Two –
The Relational Model
Chapter Two – Two –
•
•
•
•
•
•
•
•
The Relational Model
Oracle atabase --g ;elease ;elease /& •
!"e#$O!Art"ourseatabase"reateTables.s9l
•
!"e#$O!Art"ourseatabase*nsertata.s9l
•
!"e#$O!Art"ourseatabase(8L8ueries"7#-.s9l
y(8L 6.6& •
!"e#$y(8LArt"ourseatabase"reateTables.s9l
•
!"e#$y(8LArt"ourseatabase*nsertata.s9l
The goal of this chapter is to present an overview of the ma
hen students e)ecute (8L (%L%"Ts, (%L%"Ts, they may generate gen erate relations with duplicate rows. (uch results do not fit the definition of relations, but they are considered relations nonetheless. This is a good e)ample of 4theory versus practice5. ?ou ?ou may want to emphasize that foreign keys and the primary key that they the y reference need not have the same name. They must, however, however, have the same underlying set of values @domain. This means that the values values not e favor the use use of surrogate keys. Unless there is a natural, numeric numeric * @like =art:umber, we almost always add a surrogate key to our database designs. (ometimes a surrogate key will be added even if there is a natural, numeric * for consistency. consistency. (urrogate keys can cause problems @primarily patching up foreign keys if the database imports data from other databases that either do not employ a surrogate key or use a different different one. *n some cases, institutions institutions have developed policies for ensuring that surrogate keys keys are uni9ue globally. *t+s probably best for the students to get into the habit of using them and consider not using them as an e)ception. =rofessional opinions vary on this, however. however.
Chapter Two – Two –
•
•
•
•
•
•
•
The Relational Model
*f you+re using Oracle atabase, then you+ll need to teach the use of se9uences to implement surrogate keys. keys. (e9uences are an awkward awkward solution to this problem, however, and may be why surrogate keys keys are less used in the Oracleworld. aybe there will be a better solution to them from Oracle in the future. The discussion of functional dependencies is criticalDmaybe the most important in the book. *f students can understand that all tables do is record 4data points5 of functional dependencies, then normalization will be easier and seem more natural. *n physics, because there are formulae like F = ma, we need not store tables and tables of data recording data points for force, mass, and acceleration. acceleration. The formula suffices for all data points. 7owever, there is no formula for computing computing how much a customer of, say, American Airlines, Airlines, owes for his or her ticket from :ew ?ork to 7ouston. *f we could say the cost of an airline ticket was E.#6 per mile, then we we could compute the cost of a ticket, and tables of airline flight prices would be unnecessary. !ut, we we cannotB it all depends on F (o, we we store the data points for functional dependencies in tables. This chapter presents the design principle that every determinant should be a candidate key. key. This is, of course, the definition of !oyce"odd !oyce"odd :ormal 0orm. This leaves out G:0, 6:0, 6:0, and domain'key normal form. At this level, we do not think those omissions are critical. critical. (ee the normalization discussion in "hapter 6 for more on this topic. *f we use domain'key normal form as the ultimate, then, insofar as functional dependencies are concerned, the domain'key definition that 4every constraint is a logical conse9uence of domains and keys,5 ke ys,5 comes down to !oyce"odd :ormal 0orm. Therefore, we proceed on good theoretical theoretical ground with the discussion as presented in this chapter. (tudents should understand three ambiguities in a null value. This understanding will help them comprehend the issues addressed by *::%; and OUT%;
Chapter Two – Two –
2.1 2.1
The Relational Model
ANSWERS TO REVIEW QUESTIONS
Why Why is is the the rela relati tion onal al mode modell impo import rta ant? nt?
It is the single most important standard in database processing and is used for the design and implementation of almost every commercial database worldwide. 2.2
Define the term entity and entity and gie an e!ample of an entity "other than the one from this chapter#.
Entity is Entity is the formal name for a “thing” that is being tracked in a database, and is dened as something of importance to the user that needs to be represented in the database. Ex!"#$% TE&TBOOK 2.$
2.&
%ist %ist the charac character terist istics ics a tabl table e must must hae hae to be be consi conside dered red a rela relatio tion. n. •
Rows contain data about an entity.
•
olumns contain data about attributes of the entity
•
ells of the table hold a single value.
•
!ll entries in a column are of the same kind.
•
"ach column has a uni#ue name.
•
$he order of the columns columns is unimportant.
•
$he order of the rows rows is unimportant.
'ie 'ie an an e!am e!ample ple of a relati relation on "ot "othe herr than than one one from from this this cha chapte pter#. r#.
Ex!"#$% TE&TBOOK 'ISBN( Tit#$( P)*#i+,$P)*#i+,$-(( C."y-i/,t0 2.(
'ie an e!amp e!ample le of of a table table that that is not not a relation relation "other "other than than one from this chapter chapter#. #.
Ex!"#$% TE&TBOOK 'ISBN( Tit#$( P)*#i+,$P)*#i+,$-(( C."y-i/,t( A)t,.-+0 ! table is not a relation when there are multiple author names in the !uthors column. 2.)
*nder *nder +hat +hat circ circums umstan tance ces s can an an attri attribu bute te of a rel relati ation on be be of ari ariab able le leng length? th?
It can be of a variable length, if that attribute is considered to be a single thing like a memo or other variable length data item. 2.,
-!pla plain th the use of the the term terms s file file record record and field. field.
Chapter Two – Two –
The Relational Model
$hese terms are synonyms for table, table, row, and and column. $hese terms, however, generally refer to pre%relational pre%relational bases. 2./
-!pla plain th the use of the the term terms s relation relation tuple tuple and attribute. attribute.
$hese terms are synonyms for table, table, row, and and column. $hese terms, however, are the ones used in relational database theory. theory. 2.0
*nder *nder +hat +hat circ circums umstan tance ces s can can a relat relation ion hae hae dupli duplica cate te ro+ ro+s? s?
&hen manipulating a relation with a '()* we may end up with duplicate rows. !lthough in theory we should eliminate eliminate the duplicates, in in practice this is often not done. 2.1
Define th the te term unique key and key and gie an e!ample.
! uni#ue key is a column whose values identify one and only one row. row. Ex!"#$% TE&TBOOK 'ISBN ISBN(( Tit#$( P)*#i+,$-( C."y-i/,t0 where ISBN where ISBN is is a uni#ue identier. identier. 2.11
Define th the te term nonunique key and key and gie an e!ample.
! nonuni#ue key not only identies a row, but it potentially identies more than one row. E&APE% TE&TBOOK 'ISBN( Tit#$( P)*#i+,$P)*#i+,$-(( C."y-i/,t0 P)*#i+,$- is P)*#i+,$- is a nonuni#ue identier. 2.12
'ie an e!amp e!ample le of of a relation relation +ith a uniu uniue e compos composite ite key. key.
E&APE% AP APARTENT ARTENT 'B)i#i 'B)i#in/N)!*$ n/N)!*$-- ( A"-t!$ntN)!*$-( N)!*$-OB$-..!+( R$nt0 where 'B)i#in/N)!*$-( A"-t!$ntN)!*$-0 is A"-t!$ntN)!*$-0 is a uni#ue composite key. 2.1$
-!plain -!plain the the diffe differenc rence e bet+ee bet+een n a prima primary ry key key and and a cand candidat idate e key. key.
(oth are uni#ue identiers. identiers. +ne is chosen to be the identier identier for the relation and for foreign foreign keys based on the relation. relation. $he other could be chosen as well, but since it is not, it is called a candidate. 2.1& 2.1&
Desc Describ ribe e four four use uses s of a prim primar ary y key key.
! primary key can be used •
to identify a row.
•
to represent the row in foreign keys.
Chapter Two – Two –
•
•
2.1(
The Relational Model
to organie storage for the relation. as a basis for inde-es and other structures to facilitate searching in storage.
What is a surrogate key key and under +hat circumstances +ould you use one?
! surrogate key is a uni#ue, numeric identier that is appended to a relation to serve as the primary key. 2.1) 2.1)
3o+ do surro surroga gate te keys keys obtai obtain n thei theirr alu alues? es?
$hey are supplied automatically by the '()*. '()*. 2.1,
Why are are the alue alues s of surroga surrogate te keys keys normally normally hidde hidden n from users users on on forms forms ueries ueries and and reports?
*urrogate keys keys are normally hidden because they usually have no meaning to the users. 2.1/ .1/
-!pla plain the term term foreign key and key and gie an e!ample.
! foreign key creates the relationship between the tables its key value corresponds to a primary key in a relation other than the one where the key is a primary key. E&APE% TE&TBOOK 'ISBN( Tit#$( Publisher ( C."y-i/,t0 PUBISHER 'P)*#i+,$-N!$( St-$$t( City( Stt$( 7i"0 P)*#i+,$- in P)*#i+,$- in $"/$(++0 is a foreign key that references P)*#i+,$-N!$ in P)*#i+,$-N!$ in P1(2I*3"R. 2.10
-!plain -!plain ho+ ho+ prima primary ry keys keys and and foreig foreign n keys keys are are denote denoted d in this book. book.
Primary keys are underlined and foreign keys are in italics. 2.2
Define th the te term referential integrity constraint and gie an e!ample of one.
R$$-$nti# int$/-ity 8.n+t-int is 8.n+t-int is a rule specifying that every value of a foreign key matches a value of the primary key. Ex!"#$% P)*#i+,$- in TE&TBOOK !)+t $xi+t in P)*#i+,$-N!$ in PUBISHER 2.21
-!plain -!plain three three possi possible ble interpre interpretati tations ons of a null null alue. alue.
$hree possible interpretations interpretations are4 •
5alue not appropriate
•
5alue known to be blank
•
5alue appropriate and unknown
Chapter Two – Two –
The Relational Model
Chapter Two – Two – 2.22
The Relational Model
'ie an an e!ample e!ample of a null null alue alue "other "other than than one one from this this chapte chapter# r# and and e!plain e!plain each each of the three possible interpretations for that alue.
!n e-ample of null value would be4 6ull value for the attribute attribute 'eceased'ate in the table *1(*RI("R. *1(*RI("R. •
$he subscriber may be a corporation corporation and a value is inappropriate. inappropriate.
•
$he subscriber may be alive, and the value is known to be be blank.
•
2.2$ .2$
$he subscriber may be dead, dead, but the date of death death is unknown, and the value is appropriate, but not none. Defin fine the the term terms s functional dependency and dependency and determinant, using determinant, using an e!ample not from this book.
! functional dependency is a logical relationship in which the value of one item in the relationship can be determined by knowing the value of the other item. EXAM!E" #$%&
Title
$his means that if the I*(6 7of 7of a te-tbook8 is known, then we will also also know 7can determine8 the title. title. $he item on the left9the one one whose value is known 9is called the determinant . 2.2&
4n the follo follo+ing +ing eua euation tion name the the functi functiona onall depende dependency ncy and and identify identify the the determinant"s#: Area ' !ength
(idth
$he functional dependency is4 is4 '$n/t,( Wit,0
A-$
72ength, &idth8 is the determinant. 6ote this is di:erent than saying “2ength and &idth are the determinants”. 2.2( 2.2(
-!pla -!plain in the the mean meaning ing of of the foll follo+i o+ing ng e!pr e!pres essio sion: n: A
)%, C*
'ien this e!pression e!pression tell if it is also true that: A
A
%
and
C
Chapter Two – Two –
The Relational Model
$he functional dependency4 dependency4 A
'B( C0
means that a value of ! determines the value of both ( and . ;es, ;es, it is true that A 2.2) 2.2)
B and A
C
-!pla -!plain in the the mean meaning ing of of the foll follo+i o+ing ng e!pr e!pres essio sion: n: )+, E*
'ien this e!pression e!pression tell if it is also true that: +
E
and
$he functional dependency4 dependency4 '<( E0
=
means that values of the pair 7', "8 determine the value of <. <. 6o, it is
not
< 2.2,
true that
= n E
=
-!plain -!plain the the diffe differenc rences es in your ans+ers ans+ers to to uesti uestions ons 2.2( and 2.2). 2.2).
A
'B( C0 is =ust shorthand for A
B and A
3owever, '<( E0
C
= means that the composite, as a whole, identies =.
'=i-+tN!$( +tN!$0
$his means that E!"#.y$$N)!*$-
=i-+tN!$
and that E!"#.y$$N)!*$-
+tN!$
(ut4 '=i-+tN!$( +tN!$0 does not mean mean that =i-+tN!$ employees named “(ob”.8
Hi-$<t$
Hi-$<t$ 7$here could be be lots of
Chapter Two – Two – 2.2/
The Relational Model
Define th the te term primary key in key in terms of functional dependencies.
! primary key is one or more attributes that functionally determines all of the other attributes. 2.20
4f you assum assume e that that a relation relation has has no dupli duplicate cate data data ho+ do do you kno+ kno+ there there is al+ays al+ays at least one primary key?
(ecause the collection of all the attributes in the relation can identify a uni#ue row. 2.$
3o+ does does your your ans+er ans+er to uesti uestion on 2.20 2.20 change change if you you allo+ allo+ a relation relation to to hae hae duplica duplicate te data?
It doesn>t work9such tables do not have h ave a primary key. key. 2.$1
4n your your o+n +ords +ords descr describe ibe the the nature nature and purpose purpose of of the norma normali5a li5ation tion proce process. ss.
$he purpose of the normaliation normaliation process is is to prevent update problems problems in the tables 7relations8 7relations8 in the database. $he nature of the normaliation normaliation process is that we break up relations as necessary to ensure that every determinant is a candidate key. 2.$2
-!amine -!amine the data data in the the 6eterin 6eterinary ary 7ffice 7ffice %ist86 %ist86ers ersion ion 7ne 7ne in 9igure 9igure 1$ "see "see page page (2# and state assumptions about functional dependencies in that table. What is the danger of making such conclusions on the basis of sample data?
P$tN!$ 'P$tTy 'P$tTy"$( "$( P$tB-$$( P$tn$-+tN!$( O>n$-=i-+tN!$( O>n$-P,.n$( O>n$-E!i#0
O>n$-E!i# 'O>n$-+tN!$( O>n$-=i-+tN!$( O>n$-P,.n$0
O>n$-P,.n$ O>n$-E!i#0
'O>n$-+tN!$( O>n$-=i-+tN!$(
$he danger is that there may may be possibilities not apparent from from sample data.
*sing *sing the assum assumptio ptions ns you you stated stated in your your ans+er ans+er to uestio uestion n 2.$2 2.$2 +hat +hat are the the determinants of this relation? What attribute"s# can be the primary key of this relation?
!ttributes that can be the primary key are called candidate keys. <$t$-!innt+%
P$tN!$( O> O>n$-E!i#( O> O>n$-P,.n$
Cn C ni it t$ $ ?$y+ ?$y+%% P$ P$tN tN! !$ $ 2.$&
Describe Describe a modif modificat ication ion proble problem m that occurs occurs +hen chan changing ging data data in the the relatio relation n in uestion 2.$2 and a second modification problem that occurs +hen deleting data in this relation.
hanges to owner data may need to be made in several rows. 'eleting data for the last pet of an owner deletes owner data as well.
Chapter Two – Two – 2.$(
The Relational Model
-!amine -!amine the data data in the the 6eterin 6eterinary ary 7ffice 7ffice %ist86 %ist86ers ersion ion T+o T+o in 9igur 9igure e 1$1 "see "see page page (2# and state assumptions about functional dependencies in that table.
P$tN!$ 'P$tTy 'P$tTy"$( "$( P$tB-$$( P$tn$-+tN!$( O>n$-=i-+tN!$( O>n$-P,.n$( O>n$-E!i#0
O>n$-E!i# 'O>n$-+tN!$( O>n$-=i-+tN!$( O>n$-P,.n$0
O>n$-P,.n$ O>n$-E!i#0
'O>n$-+tN!$( O>n$-=i-+tN!$(
'P$tN!$( <t$0
'S$-@i8$( C,-/$0
$he last functional dependency dependency assumes a pet is seen at most on one one day and that there is no standard charge for a service. 2.$)
*sing *sing the assum assumptio ptions ns you you stated stated in your your ans+er ans+er to uestio uestion n 2.$( 2.$( +hat +hat are the the determinants of this relation? What attribute"s# can be the primary key of this relation?
<$t$-!innt+% 'P$tN!$( <t$0
P$tN!$( O>n$-E!i#( O>n$-P,.n$(
Cni Cn it$ t$ ?$ ?$y+% y+% 'P$ 'P$tN tN!$( !$( <t <t$0 $0 2.$,
-!plain -!plain a modifi modificati cation on problem problem that that occur occurs s +hen chang changing ing data data in the the relatio relation n in uestion 2.$( and a second modification problem that occurs +hen deleting data in this relation.
*ame as ?.@A4 hanges to owner data may need to be made in several rows. 'eleting data for the last pet of an owner deletes owner data as well.
2.$/
ANSWERS TO E&ERCISES
Apply Apply the normali5 normali5atio ation n process process to the 6ete 6eterina rinary ry 7ffice 7ffice %ist86 %ist86ers ersion ion 7ne 7ne relation relation sho+n in 9igure 1$ "see page (2# to deelop a set of normali5ed relations. Sho+ the results of each of the steps in the normali5ation process.
STEP ONE% PETAN<OWNER 'P$tN!$( P$tTy"$( P$tB-$$( P$tn$-+tN!$( O>n$-=i-+tN!$( O>n$-P,.n$( O>n$-E!i#0 Functional Dependencies: P$tN!$ 'P$tTy 'P$tTy"$( "$( P$tB-$$( P$tn$-+tN!$( O>n$-=i-+tN!$( O>n$-P,.n$( O>n$-E!i#0
O>n$-E!i# 'O>n$-+tN!$( O>n$-=i-+tN!$( O>n$-P,.n$0
O>n$-P,.n$ O>n$-E!i#0
'O>n$-+tN!$( O>n$-=i-+tN!$(
PET-AND-OWNE !andidate "e#s: P$tN!$
Chapter Two – Two –
The Relational Model
Chapter Two – Two –
The Relational Model
Is e$er# determinant a candidate %e#& NOO>n$-E!i# n O>n$-P,.n$ -$ NOT 8nit$ ?$y+ STEP TWO% B-$? int. t>. -$#ti.n+% OWNER n PET OWNER 'O>n$-+tN!$( O>n$-=i-+tN!$( O>n$-P,.n$( O>n$-E!i#0 PET 'P$tN!$( P$tT P$tTy"$( y"$( P$tB-$$( P$tn$-E!i# 'O>n$-+tN!$( O>n$-=i-+tN!$( O>n$-P,.n$0
O>n$-P,.n$ O>n$-E!i#0
'O>n$-+tN!$( O>n$-=i-+tN!$(
OWNE !andidate "e#s:
O>n$-P,.n$( O>n$-E!i#
Is e$er# determinant a candidate %e#& ESO>n$-E!i# n O>n$-P,.n$ O>n$-P,.n$ -$ 8nit$ ?$y+ N.-!#iFti.n 8.!"#$t$ W$ 8n 8,..+$ $it,$- 8nit$ ?$y + "-i!-y ?$y 'A0 'A0
I= WE USE USE O>n O>n$$-P, P,.n .n$ $ + + "-i! "-i!-y y ?$y ?$y(( THE THEN% N%
OWNER 'O>n$-P,.n$( O>n$-+tN!$( O>n$-=i-+tN!$( O>n$-E!i#0 PET 'P$tN!$( P$tT P$tTy"$( y"$( P$tB-$$( P$t
'P$tTy"$( 'P$tT y"$( P$tB-$$( P$tn$-P,.n$0
PET !andidate "e#s:
P$tN!$
Is e$er# determinant a candidate %e#& ESP$tN!$ i+ 8nit$ ?$yN.-!#iFti.n ?$yN.-!#iFti.n 8.!"#$t$ 8.!"#$t$ =INA NORAI7E< REATIONS% OWNER 'O>n$-P,.n$( O>n$-+tN!$( O>n$-=i-+tN!$( O>n$-E!i#0 PET 'P$tN!$( P$tT P$tTy"$( y"$( P$tB-$$( P$t
I= WE WE USE USE O>n$ O>n$-E -E! !i# i# + "-i! "-i!-y y ?$y ?$y(( THE THEN% N%
OWNER 'O>n$-P,.n$( O>n$-+tN!$( O>n$-=i-+tN!$( O>n$-E!i#0 PET 'P$tN!$( P$tT P$tTy"$( y"$( P$tB-$$( P$t
Chapter Two – Two –
The Relational Model
Functional Dependencies: P$tN!$
'P$tTy"$( 'P$tT y"$( P$tB-$$( P$tn$-E!i#0
PET !andidate "e#s:
P$tN!$
Is e$er# determinant a candidate %e#& ESP$tN!$ i+ 8nit$ ?$yN.-!#iFti.n ?$yN.-!#iFti.n 8.!"#$t$ 8.!"#$t$ =INA NORAI7E< REATIONS% OWNER 'O>n$-P,.n$( O>n$-+tN!$( O>n$-=i-+tN!$( O>n$-E!i#0 PET 'P$tN!$( P$tT P$tTy"$( y"$( P$tB-$$( P$t
Apply Apply the normali5 normali5atio ation n process process to the 6eteri 6eterinary nary 7ffice 7ffice %ist86 %ist86ers ersion ion T+o T+o relati relation on sho+n in 9igure 1$1 "see page (2# to deelop a set of normali5ed relations. Sho+ the results of each of the steps in the normali5ation process.
STEP ONE% PETAN<OWNER 'P$tN!$( P$tTy"$( P$tB-$$( P$tn$-+tN!$( O>n$-=i-+tN!$( O>n$-P,.n$( O>n$-E!i#( S$-@i8$( <t$( C,-/$0 Functional Dependencies: P$tN!$ 'P$tT 'P$tTy"$( y"$( P$tB-$$( P$tn$-+tN!$( O>n$-=i-+tN!$( O>n$-P,.n$( O>n$-E!i#0
O>n$-E!i# 'O>n$-+tN!$( O>n$-=i-+tN!$( O>n$-P,.n$0
O>n$-P,.n$ O>n$-E!i#0
'O>n$-+tN!$( O>n$-=i-+tN!$(
'P$tN!$( <t$0
'S$-@i8$( C,-/$0
$he last functional dependency dependency assumes a pet is seen at most on one one day and that there is no standard charge for a service. PET-AND-OWNE !andidate "e#s: 'P$tN!$( <t$0 Is e$er# determinant a candidate %e#& NOP$tN!$( O>n$-E!i# n O>n$-P,.n$ -$ NOT 8nit$ ?$y+
Chapter Two – Two –
The Relational Model
STEP TWO% (reak into two relations4
+&6"R and P"$%*"R5I"
OWNER 'O>n$-+tN!$( O>n$-=i-+tN!$( O>n$-P,.n$( O>n$-E!i#0 PETSERVICE 'P$tN!$( P$tTy"$( P$tB-$$( P$tn$-E!i# 'O>n$-+tN!$( O>n$-=i-+tN!$( O>n$-P,.n$0
O>n$-P,.n$ O>n$-E!i#0
'O>n$-+tN!$( O>n$-=i-+tN!$(
OWNE !andidate "e#s:
O>n$-P,.n$( O>n$-E!i#
Chapter Two – Two –
The Relational Model
Is e$er# determinant a candidate %e#& ESO>n$-E!i# n O>n$-P,.n$ O>n$-P,.n$ -$ 8nit$ ?$y+ N.-!#iFti.n 8.!"#$t$ W$ 8n 8,..+$ $it,$- 8nit$ ?$y + "-i!-y ?$y ?$y W$ >i## )+$ O>n$-P,.n$ If a student chooses +wner"mail, the steps will be similar as shown in "-ercise ?.@B. I= WE USE O>n$-P,.n$ + "-i!-y ?$y( THEN% OWNER 'O>n$-P,.n$( O>n$-+tN!$( O>n$-=i-+tN!$( O>n$-E!i#0 PETSERVICE 'P$tN!$( P$tTy"$( P$tB-$$( P$t
'P$tTy"$( 'P$tT y"$( P$tB-$$( P$tn$-P,.n$0
'P$tN!$( <t$0
'S$-@i8$( C,-/$0
$he last functional dependency dependency assumes a pet is seen at most on one one day and that there is no standard charge for a service. PET-AND-SE(I!E !andidate "e#s: 'P$tN!$( <t$0 Is e$er# determinant a candidate %e#& NOP$tN!$ i+ NOT 8nit$ ?$y ?$y STEP THREE% B-$? PETSERVICE PETSERVICE int. t>. -$#ti.n+% -$#ti.n +%
PET n SERVICE
OWNER 'O>n$-P,.n$( O>n$-+tN!$( O>n$-=i-+tN!$( O>n$-E!i#0 PET 'P$tN!$( P$tT P$tTy"$( y"$( P$tB-$$( P$t
'P$tTy"$( 'P$tT y"$( P$tB-$$( P$tn$-P,.n$0
PET !andidate "e#s:
P$tN!$
Is e$er# determinant a candidate %e#& ESP$tN!$ i+ 8nit$ ?$yN.-!#iFti.n ?$yN.-!#iFti.n 8.!"#$t$ 8.!"#$t$ SE(I!E Functional Dependencies: 'P$tN!$( <t$0
'S$-@i8$( C,-/$0
$he functional dependency assumes assumes a pet is seen at most on one day day and that there is no standard charge for a service. SE(I!E !andidate "e#s:
'P$tN!$( <t$0
Chapter Two – Two –
The Relational Model
Chapter Two – Two –
The Relational Model
Is e$er# determinant a candidate %e#& ES'P$tN!$( <t$0 i+ 8nit$ ?$yN.-!#iFti.n ?$yN.-!#iFti.n 8.!"#$t$ =INA NORAI7E< REATIONS% OWNER 'O>n$-P,.n$( O>n$-+tN!$( O>n$-=i-+tN!$( O>n$-E!i#0 PET 'P$tN!$( P$tT P$tTy"$( y"$( P$tB-$$( P$t
;ons ;onsid ider er the the fol follo lo+i +ing ng rel relat atio ion: n:
STU also assume that students hae at most one maor. A.
@.
Sho+ an e!ample of of this relation relation for t+o students students one of +hom has three siblings siblings and the other of +hom has only t+o siblings. $tudent&umber
$tudent&ame
$ibling&ame
Ma-or
.//
Mary 0ones
1ictoria
Accounting
.//
Mary 0ones
$lim
Accounting
.//
Mary 0ones
2eginald
Accounting
3//
red (illows
2e4
inance
3//
red (illows
%illy
inance
%ist ist th the ca candida idate ke keys in in th this re relatio tion.
ST)DENT !andidate "e#s: Si*#in/N!$0
'St)$ntN)!*$-(
$his assumes that *tudent6ame is not uni#ue. ;.
Stat State e the the func functi tion onal al depe depend nden enci cies es in this this rela relati tion on..
St)$ntN)!*$-
'St)$ntN!$( .-0
'St)$ntN)!*$-( Si*#in/N!$0 D.
'St)$ntN!$( .-0
-!pla -!plain in +hy +hy this this relati relation on doe does s not not meet meet the the relati relation onal al des design ign crite criteria ria set set out out in this this chapter "i.e. +hy this is not a +ellformed relation#.
*ome attributes are functionally dependent on a part of the composite primary key. key.
Chapter Two – Two – -.
The Relational Model
Diide Diide this this relat relation ion into into a set set of rela relatio tions ns tha thatt meet meet the the rel relati ation onal al des design ign criter criteria ia "tha "thatt is is that are +ell formed#.
B-$? int. t>. -$#ti.n+%
STU
STU
'St)$ntN)!*$-0
'St)$ntN)!*$-( Si*#in/N!$0
ST)DENT-SIB*IN+ !andidate "e#s: Si*#in/N!$0
'Si*#in/N!$0 'St)$ntN)!*$-(
Is e$er# determinant a candidate %e#& ES'St)$ntN)!( Si*#in/N!$0 Si*#in/N!$0 i+ 8nit$ ?$y N.-!#iFti.n 8.!"#$t$ FO ST)DENT: STU
'St)$ntN!$( .-0
ST)DENT !andidate "e#s:
St)$ntN)!*$-
Is e$er# determinant a candidate %e#& ESSt)$ntN)!*$- i+ 8nit$ 8nit$ ?$yN.-!#iFti.n 8.!"#$t$ =INA NORAI7E< REATION+% STU
Alter Alter uestion uestion 2.& 2.& to allo+ allo+ students students to hae hae multipl multiple e maors. maors. 4n this this case the the relation relational al structure is:
STU
Sho+ an e!ample of of this relation relation for t+o students students one of +hom has three siblings siblings and the other of +hom has one sibling. Assume that each student has a single maor.
Chapter Two – Two –
@.
;.
The Relational Model
$tudent&umber
$tudent&ame
$ibling&ame
Ma-or
.//
Mary 0ones
1ictoria
Accounting
.//
Mary 0ones
$lim
Accounting
.//
Mary 0ones
2eginald
Accounting
3//
red (illows
2e4
inance
Sho+ Sho+ the the dat data a chan changes ges neces necessar sary y to add add a seco second nd maor maor for for onl only y the the first first stude student nt.. $tudent&umber
$tudent&ame
$ibling&ame
Ma-or
.//
Mary 0ones
1ictoria
Accounting
.//
Mary 0ones
$lim
Accounting
.//
Mary 0ones
2eginald
Accounting
3//
red (illows
2e4
inance
.//
Mary 0ones
1ictoria
#nfo$ystems
.//
Mary 0ones
$lim
#nfo$ystems
.//
Mary 0ones
2eginald
#nfo$ystems
@ased @ased on you yourr ans+e ans+err to part part @ @ sho+ sho+ the the data data cha chang nges es nec necess essary ary to add add a secon second d maor for the second student. $tudent&umber
$tudent&ame
$ibling&ame
Ma-or
.//
Mary 0ones
1ictoria
Accounting
.//
Mary 0ones
$lim
Accounting
.//
Mary 0ones
2eginald
Accounting
3//
red (illows
2e4
inance
.//
Mary 0ones
1ictoria
#nfo$ystems
.//
Mary 0ones
$lim
#nfo$ystems
.//
Mary 0ones
2eginald
#nfo$ystems
3//
red (illows
2e4
Accounting
Chapter Two – Two – D.
The Relational Model
-!pla -!plain in the the diffe differen rences ces in your your ans+ ans+ers ers to parts parts @ and and ;. ;omme ;omment nt on on the the desir desirab abili ility ty of this situation.
&e had to add three rows in the rst case9one ma=or for each of the siblings of the student. If we didn>t do that, that, it would appear appear the student has a sibling with one ma=or, but doesn>t have the sibling as a second ma=or. ma=or. $his is nutsC -.
Dii Diide de this this rela relati tion on into into a set set of +ell +ellf for orme med d rel relat atio ions ns..
If we split *$1'"6$ into two relations, *$1'"6$ and *$1'"6$% *$1'"6$% *I(2I6D, then we get4 STU
'St)$ntN!$0
'St)$ntN)!*$-( .-0 ST)DENT !andidate "e#s:
St)$ntN!$
'St)$ntN)!*$-( .-0
Is e$er# determinant a candidate %e#& NOSt)$ntN)!*$- i+ NOT 8nit$ ?$y ?$y B-$? int. t>. -$#ti.n+% -$#ti. n+%
STU
STU
St)$ntN!$
ST)DENT, !andidate "e#s: St)$ntN)!*$Is e$er# determinant a candidate %e#& ESSt)$ntN)!*$- i+ 8nit$ 8nit$ ?$yN.-!#iFti.n 8.!"#$t$
Chapter Two – Two –
The Relational Model
FO ST)DENT-.A/O: Functional Dependencies: 'St)$ntN)!*$-( .-0
St)$ntN)!*$-
'St)$ntN)!*$-( .-0
.-
ST)DENT, !andidate "e#s: 'St)$ntN)!*$-( .-0 Is e$er# determinant a candidate %e#& ES'St)$ntN)!*$-( .-0 i+ 8nit$ ?$y ES'St)$ntN)!*$-( N.-!#iFti.n 8.!"#$t$ =INA NORAI7E< REATION+% STU
The te!t te!t states states that that you you can argue argue that that the the only only reason reason for for haing haing relati relations ons is to to store store instances of functional dependencies.B -!plain in your o+n +ords +hat this means.
In a properly normalied relation, each row of the relation consists of a primary key value 7which is a determinant8 and attribute values 7which are all functionally dependent on the the primary key8. $hus, properly normalied normalied relations store instances of functional dependencies, and only instances of functional dependencies. *o we can say that the purpose purpose of relations is to to store instances of functional dependencies.
ANSWERS TO REGIONA ABS CASE QUESTIONS
Cegional %abs is a company that conducts research and deelopment +ork on a contract basis for other companies and organi5ations. 9igure 2$1 sho+s data that Cegional %abs collects about proects and the employees assigned to them. This data is stored in a relation "table# named C7E-;T:
PROJECT 'P-.$8tI<( E!"#.y$$N!$( E!"#.y$$S#-y0
Chapter Two – Two –
The Relational Model
A. Assuming that that all functional functional dependen dependencies cies are apparent apparent in in this data data +hich of of the follo+ing are true? G. ro-ect#+ 5 Employee&ame
A!$E
?. ro-ect#+ 5 Employee$alary
A!$E
@. )ro-ect#+, Employee&ame* 5 Employee$alary TRUE, TRUE, but only if Employee&ame 5 Employee$alary A. Employee&ame 5 Employee$alary
T26E
H. Employee$alary 5 ro-ect#+
A!$E
. Employee$alary 5 )ro-ect#+, Employee&ame*
A!$E
@. What is the the primar primary y key key of of C7EC7E-;T? ;T?
'P-.$8tI<( E!"#.y$$N!$0 ;. Are all the the nonkey nonkey attributes attributes "if any# dependent dependent on the primary key? key?
NO, NO, "mployee*alary is dependent only on "mployee6ame D. 4n +hat +hat normal normal form form is C7EC7E-;T? ;T?
1N= ON -. Describe t+o modification modification anomalies anomalies that affect C7E-;T. C7E-;T.
$he two modication modication anomalies that a:ect PR+F"$ are4 INSERTION% $ INSERTION% $o o give an employee employee a salary, we must rst assign the employee employee to a pro=ect. O
NO '. 4s -mployee
ES
E!"#.y$$N!$ E!"#.y$$S#-y
3. 4s "roect4D "roect4D -mployee
ES
'P-.$8tI<( E!"#.y$$N!$0 E!"#.y$$S#-y
Chapter Two – Two – 4.
The Relational Model
4s -mployeeSala -mployeeSalary ry a determinant? determinant? 4f so based based on on +hich functional functional dependencies dependencies in part part A?
NO !ctually, for the data in
NO F. Cedesign the relation to eliminate eliminate modification modification anomalies. anomalies.
$he following seems workable4 workable4 ASSIGNENT ASS IGNENT 'P-.$8tI<( E!"#.y$$N!$0 SAAR SAAR 'E!"#.y$$N!$( E!"#.y$$S#-y0
ANSWERS TO GAR
'arden 'lory is a partnership that proides gardening and yard maintenance serices to indiiduals and organi5ations. 'arden 'lory is o+ned by t+o partners. They employ t+o office administrators and a number of full and parttime gardeners. 'arden 'lory +ill proide onetime onetime garden garden serices serices but it speciali5es speciali5es in in ongoing serice and and maintenance. Gany of its customers hae multiple buildings apartments and rental houses that reuire gardening and la+n maintenance serices. 9igure 2$2 sho+s data that 'arden 'lory collects about properties and serices.
A. *sing these these data state assumptions assumptions about about functional functional dependen dependencies cies among the columns columns of data. Eustify your assumptions on the basis of these sample data and also on the basis of +hat you kno+ about serice businesses.
Chapter Two – Two – P-."$-tyN!$
The Relational Model
P-."$-tyTy"$
'P-."$-tyN!$( St-$$t0 'P-."$-tyN!$( City0 'P-."$-tyN!$( 7i"0
'P-."$-tyTy"$( City( 7i"0
'P-."$-tyTy"$( St-$$t( 7i"0
'P-."$-tyTy"$( St-$$t( City0
'P-."$-tyN!$( <$+8-i"ti.n( S$-@i8$<t$0
A!.)nt
6one of these seem to be more more than =ust coincidence, coincidence, however. however. It would seem, for e-ample, that an “"lm *t !pts” could e-ist in more than one city9 there are certainly enough cities with a street named "lm *treetC $here is simply not enough data to reply reply on it. 2ogically, it seems that we need one I' column9a surrogate key will be re#uired here. &ith regard to services, it would seem likely that a given service could be given to the same property, property, but on di:erent dates. *o, if we had a good good determinant for property, then the last functional dependency would be true. *o, the following seems workable4 P-."$-tyI< 7i"0
'P-."$-tyN!$( P-."$-tyTy"$( St-$$t( City(
'P-."$-tyI<( <$+8-i"ti.n( S$-@i8$<t$0
A!.)nt
@. 'ien your your assumptions assumptions in part A comment comment on the the appropriateness appropriateness of of the follo+ing follo+ing designs:
-.
=;O=%;T? @=roperty:ame, =ropertyType, (treet, "ity, Hip, (erviceate, escription, Amount NOT GOO<4 GOO<4
/.
=;O=%;T? @=roperty:ame, =ropertyType, (treet, "ity, Hip, (erviceate, escription, Amount NOT GOO<4 GOO<4 $here may be more than one service on a given date.
I.
=;O=%;T? @=roperty:ame, =ropertyType, (treet, "ity, Hip, (erviceate, escription, Amount NOT GOO<4 GOO<4
G.
=;O=%;T? @=roperty*, =roperty:ame, =ropertyType, (treet, "ity, Hip, (erviceate, escription, Amount NOT GOO<4 GOO<4
6.
=;O=%;T? @=roperty*, =roperty:ame, =ropertyType, (treet, "ity, Hip, (erviceate, escription, Amount NOT GOO<4 GOO<4
Chapter Two – Two – $.
The Relational Model
=;O=%;T? @=roperty*, =roperty:ame, =ropertyType, (treet, "ity, Hip, SericeDate
and (%;C*"% @(erviceate, escription, Amount BETTER4 BETTER4 *ervice'ate *ervice'a te is properly set up as a foreign key in PR+P"R$;. 3+&"5"R, this will limit the system to only one service per property9 the foreign key is in the wrong tableC 1.
=;O=%;T? @=roperty*, =roperty:ame, =ropertyType, (treet, "ity, Hip, (erviceate and& (%;C*"% @(ervice*, SericeDate , escription, Amount 6+$ D++'4 *ervice'ate is supposedly supposedly a foreign key key in PR+P"R$;, PR+P"R$;, but isn>t even a primary key key in *"R5I". *"R5I". $his simply doesn>t workC workC
2.
=;O=%;T? @=roperty*, =roperty:ame, =ropertyType, (treet, "ity, Hip, Serice4D
and& (%;C*"% @(ervice*, (erviceate, escription, Amount, roperty4D NOT GOO<4 GOO<4 *erviceI' is properly properly used as a foreign foreign key in PR+P"R$;, PR+P"R$;, but we also have PropertyI' PropertyI' as a foreign foreign key in *"R5I". *"R5I". $his simply doesn>t work there cannot be be two foreign foreign keys like like thisC $he #uestion then becomes4 &hich one should sh ould we keepJ 3.
=;O=%;T? @=roperty*, =roperty:ame, =ropertyType, (treet, "ity, Hip and& (%;C*"% @(ervice*, (erviceate, escription, Amount, =roperty* GOO<
SERVICE=EE 'P-."$-tyI<( S$-@i8$I<( <$+8-i"ti.n( A!.)nt0 Add this table to +hat +hat you consider consider to be the best best design design in your ans+er ans+er to part @. Godify the tables from part @ as necessary to minimi5e the amount of data duplication. Will this design +ork for the data in 9igure 2$1? 4f not modify the design so that this data +ill +ork. State the assumptions implied by this design.
Chapter Two – Two –
The Relational Model
3ere>s the best design from part (4 PROPERT'P-."$-tyI<( P-."$-tyN!$( P-."$-tyTy"$( PROPERT'P-."$-tyI<( P-."$-tyTy"$( St-$$t( City( 7i"0 SERVICE'S$-@i8$I<( S$-@i8$<t$( <$+8-i"ti.n( A!.)nt( Propert#ID00 Propert#ID !dding SERVICE=EE 'P-."$-tyI<( S$-@i8$I<( S$-@i8$<t$( A!.)nt0 means that we need to take PropertyI', PropertyI', *ervice'ate, and !mount out of *"R5I". 6ote that PropertyI' of *"R5I"%<"" is a foreign key in PR+P"R$;. 6ow, for this design to make sense, we need to allow for multiple services on a property by making 7*erviceI', PropertyI', *ervice'ate8 the key in *"R5I"% *"R5I"% <"". PROPERT'P-."$-tyI<( P-."$-tyN!$( P-."$-tyTy"$( PROPERT'P-."$-tyI<( P-."$-tyTy"$( St-$$t( City( 7i"0 SERVICE 'S$-@i8$I<( <$+8-i"ti.n0 SERVICE=EE 'Propert#ID ' Propert#ID(( Ser$iceID Ser$iceID(( S$-@i8$<t$( A!.)nt0 $his means that a service can be applied applied to a property property on di:erent, but but multiple, dates and that a property can have multiple, but di:erent, services on the same date. $his also means that a service service can be applied to to multiple, but di:erent, properties properties on the same date. 3owever, a property property may not have the same service on the same date. !ll of this seems reasonable. reasonable. and will work with the data in
ANSWERS TO JAES JA ES RIVER JEWER PROJECT QUESTIONS
H<7T-: The Eames Cier Ee+elry roect Iuestions are aailable online for Appendi! D +hich can be do+nloaded from the te!tbook=s Web site: +++.pearsonhighered.comJkroenke . The solutions for these uestions +ill be included in the 4nstructor=s Ganual for each chapterK
James ;iver Jewelry is a small hile James ;iver Jewelry does sell typical
Chapter Two – Two –
The Relational Model
0igure - shows data that James ;iver Jewelry collects for its fre9uent buyer program.
A. *sing these these data state assumptions assumptions about about functional functional dependen dependencies cies among the columns columns of data. Eustify your assumptions on the basis of these sample data and also on the basis of +hat you kno+ about retail sales.
'P,.n$( E!i#0
P,.n$ E!i#
'N!$( E!i#0
'N!$( P,.n$0
3owever, these are based on a very limited dataset and cannot be trusted. s also possible possible that some customers could have the same phone, even though they do not in this e-ample. !nother functional dependency is4 [email protected]$N)!*$P-$TxA!.)nt0
'N!$( P,.n$( E!i#( [email protected]$<t$(
@. 'ien your your assumptions assumptions in part A comment comment on the the appropriateness appropriateness of of the follo+ing follo+ing designs:
-.
"U(TO%; @:ame, =hone, %mail, *nvoice:umber, *nvoiceate, =reTa)Amount NOT GOO<4 GOO<4
/.
"U(TO%; @:ame, =hone, %mail, *nvoice:umber, *nvoiceate, =reTa)Amount TRUE( *)t n.t n.-!#iF$
Chapter Two – Two – I.
The Relational Model
"U(TO%; @:ame, =hone, %mail, *nvoice:umber, *nvoiceate, =reTa)Amount GOO< =OR CUSTOERS( BUT NOT INVOICES4 INVOICES4 Diven a uni#ue "mail address, "mail works as a key key for customer data. 1nfortunately, "mail does not determine Invoice6umber and therefore is not a suMcient suMcien t key. key.
G.
"U(TO%; @"ustomer*, :ame, =hone, %mail, *nvoice:umber, *nvoiceate, =reTa)Amount GOO< =OR CUSTOERS( BUT NOT INVOICES% ! uni#ue I' I' column is a good idea, and works as a key key for customer data. 1nfortunately, ustomerI' does not determine Invoice6umber and therefore is not a suMcient suMcien t key. key.
6.
"U(TO%; @:ame, =hone, %mail and =U;"7A(% @*nvoice:umber, *nvoiceate, =reTa =reTa)Amount )Amount GETTING BETTER( BUT INCOPETE &e cannot be sure 6ame is uni#ue, and the relationship between 1*$+)"R and P1R3!*" is not dened.
$.
"U(TO%; @:ame, =hone, %mail and =U;"7A(% @*nvoice:umber, *nvoiceate, =reTa)Amount, =reTa)Amount, -mail GOO< $he design breaks breaks up the themes and has a proper proper foreign key. key. 3owever, the use of "mail as a primary key may be a problem if two customers share an "mail address.
1.
"U(TO%; @:ame, %mail and =U;"7A(% @*nvoice:umber, =hone, *nvoiceate, =reTa =reTa)Amount, )Amount, -mail A GOO<
P,.n$
Chapter Two – Two –
The Relational Model
;. Godify +hat +hat you consider consider to be the best best design in part @ to include a column called called A+ardurchaseAmo A+ardurchaseAmount. unt. The purpose of of this column column is to keep keep a balance balance of the the customers= purchases for a+ard purposes. Assume that returns +ill be recorded +ith inoices haing a negatie reTa!Amount.
$he best design in part ( was number , so we>ll put !wardPurchase!mount !wardPurchase!mount in in 1*$+)"R. $he result is4 is4 CUSTOER 'N!$( P,.n$( E!i#( A>-P)-8,+$A!.)nt0 PURCHASE '[email protected]$N)!*$-( [email protected]$<t$( P-$T P-$TxA!.)nt( xA!.)nt( Email 0 3owever, the problem with this design is that there>s the re>s no history of prior !wardPurchase!mounts. D. Add a ne+ AWACD AWACD table table to your ans+er ans+er to part ;. Assume Assume that the the ne+ table +ill +ill hold data concerning the date and amount of an a+ard that is gien after a customer has purchased purchased 1 items. items. -nsure that that your ne+ ne+ table has has appropriate appropriate primary primary and foreign foreign keys.
$he new table is4 AWAR< 'A>-I<( A>-<t$( A>-A!.)nt( AWAR< A>-P)-8,+$A!.)nt( Email 0 $he other tables need to be be ad=usted, and the nal design design will be4 CUSTOER 'N!$( P,.n$( E!i#0 PURCHASE '[email protected]$N)!*$-( [email protected]$<t$( P-$T P-$Tx x A!.)nt( E!i#( A'ardID E!i#( A'ardID00 AWAR< 'A>-I<( A>-<t$( A>-A!.)nt( AWAR< A>-P)-8,+$A!.)nt( Email 0 Placing !wardI' !wardI' into P1R3!*" as a foreign key allows for each purchase to be allocated to a particular award. award. (usiness rules need to be in place place to ensure that the count of the number of P1R3!*"s having a positive Pre$a-!mount minus the count of the number having a negative Pre$a-!mount never e-ceeds GN for any given !&!R' row.
ANSWERS TO THE QUEEN ANNE CURIOSIT SHOP PROJECT QUESTIONS
The Iueen Anne ;uriosity Shop sells both antiues and currentproduction household items that complement or are useful +ith the antiues. 9or e!ample the store sells antiue dining room tables and ne+ tablecloths. The antiues are purchased from both indiiduals and +holesalers and the ne+ items are purchased from distributors. The store=s customers include indiiduals o+ners of bedandbreakfast operations and local interior designers +ho +ork +ith both indiiduals and small businesses. The antiues are uniue although some multiple items such as dining room chairs may be aailable as a set "sets are neer broken#. The ne+ items are not uniue and an item may be reordered if it is out of stock.
Chapter Two – Two –
The Relational Model
"for e!ample a particular style of tablecloth may be aailable in seeral si5es and in a ariety of colors#. 9igure 2$$ sho+s typical sales data for the Iueen Anne ;uriosity Shop and 9igure 2$& sho+s typical purchase data.
Chapter Two – Two –
The Relational Model
A. *sing these these data state assumptions assumptions about about functional functional dependen dependencies cies among the columns columns of data. Eustify your assumptions on the basis of these sample data and also on the basis of +hat you kno+ about retail sales.
'=i-+tN!$( P,.n$0
'+tN!$( P,.n$0
'+tN!$( =i-+tN!$0
'Tx( T.t#0
'+tN!$( [email protected]$<t$( [email protected]$It$!0 T.t#0 '=i-+tN!$( [email protected]$<t$( [email protected]$It$!0 T.t#0
'P-i8$( Tx(
'P-i8$( Tx(
'P,.n$N!$( [email protected]$<t$( [email protected]$It$!0 T.t#0
'P-i8$( Tx(
3owever, these are based on a very limited dataset and cannot be trusted. s also possible possible that some customers could have the same phone, even though they do not in this e-ample. $he one trustable functional dependency dependency here is4 P-i8$
'Tx( T.t#0
'P)-8,+$<t$( V$n.-(
'[email protected]$It$!( P)-8,+$P-i8$( P)-8,+$<t$0 P,.n$0 'P)-8,+$P-i8$( P)-8,+$<t$0 V$n.-( P,.n$0 V$n.P,.n$
'V$n.-(
[email protected]$ '[email protected]$It$!(
P,.n$
V$n.-
3owever, these are based on a very limited dataset and cannot be trusted. s also possible that multiple purchases on the same purchase date will be for the purchase price. $he most trustworthy functional dependencies here are4 V$n.P,.n$
P,.n$
V$n.-
(ut, the rst of these may fail if the vendor has multiple phone numbers. @. 'ien your your assumptions assumptions in part A comment comment on the the appropriateness appropriateness of of the follo+ing follo+ing designs:
Chapter Two – Two –
The Relational Model
-. "U(TO%; "U(TO%; @Last: @Last:ame, ame, 0irst: 0irst:ame, ame, =hone, =hone, %mail, %mail, *nvoic *nvoiceate eate,, *nvoice*tem, =rice, Ta), Total NOT GOO<. GOO<. $here may be be many customers with with the same last name. /. "U(TO%; "U(TO%; @Last:ame, @Last:ame, 0irst: 0irst:ame, ame, =hone, =hone, %mail, %mail, *nvoice *nvoiceate, ate, *nvoice*tem, =rice, Ta), Total NOT GOO<. GOO<. $here may be be many customers with with the same last name and rst name. I. "U(TO%; "U(TO%; @Last: @Last:ame, ame, 0irst: 0irst:ame, ame, =hone, =hone, %mail, %mail, *nvoice *nvoiceate, ate, *nvoice*tem, =rice, Ta), Total NOT GOO<. GOO<. Phone will be fairly fairly uni#ue, and combined with with
"U(TO%; @Last:ame, 0irst:ame, =hone, %mail, *nvoiceate, *nvoice*tem, =rice, Ta), Ta), Total NOT GOO<. GOO<. &e>re &e>re still trying to make make the unworkable actually actually work. *ame ob=ections as above in A, e-cept that now customers would be limited to one of a particular item per day. day.
$.
"U(TO%; @Last:ame, 0irst:ame, =hone, %mail and& (AL% @*nvoiceate, *nvoice*tem, =rice, Ta), Total NOT GOO<. GOO<.
1.
"U(TO%; @Last:ame, 0irst:ame, =hone, %mail, 4noiceDate and&
Chapter Two – Two –
The Relational Model
(AL% @*nvoiceate, *nvoice*tem, =rice, Ta), Total NOT GOO< &e>ve &e>ve got a foreign key of Purchase'ate Purchase'ate in *!2". (ut everything else that was wrong in design above is still a problem. )oreover, by using Purchase'ate as the foreign key, we limit the customer to only one purchaseC 2.
"U(TO%; @Last:ame, 0irst:ame, =hone, %mail, 4noiceDate , 4noice4tem and& (AL% @*nvoiceate, *tem, =rice, Ta), Total STI NOT GOO<. GOO<. $he customer is still still limited to only one purchaseC purchaseC 3owever, this is the best design of the bunch if we rework the foreign keys. ;. Godify +hat +hat you consider consider to be the best best design in part @ to include surrogate surrogate 4D columns called ;ustomer4D and Sale4D. 3o+ does this improe the design?
$he best design in part ( was number E, so we>ll put in the I' columns. $hese columns will become become the new primary keys, keys, and we>ll need to ad=ust the foreign key key so that it is in *!2". $he result is4 CUSTOER 'C)+t.!$-I<( +tN!$( =i-+tN!$( P,.n$( E!i#0 SAE 'S#$I<( !ustomerID !ustomerID(( [email protected]$<t$( [email protected]$It$!( P-i8$( Tx( Tx( T.t#0 &e now have a clean design, and 1*$+)"R is in (6<. (6<. *!2" is not in (6< because P-i8$
'Tx( T.t#0
&e could further normalie *!2", but we will intentionally leave it this way. way. $his is called $n.-!#iFti.n, $n.-!#iFti.n, and is discussed in hapter H. $he point is that creating creating the e-tra table 7PRI"O$ 7PRI"O$!/O$+$ !/O$+$!28 !28 is more trouble than it is worth. 7an you imagine what the data for that table would look likeJ8 $he primary key problems problems with both tables tables are resolved, resolved, and now a customer can purchase as many of an item on the same date as he or she wants toC D. Godify the design in part ; by breaking SA%- into t+o relations relations named SA%- and and SA%-L4T-G. Godify columns and add additional columns as you think necessary. 3o+ does this improe the design?
$he main problem with with the design in part is that only one item can be included in each sale. sale. )oving items into a *!2"OI$") *!2"OI$") table linked linked *!2" will allow multiple items to be purchased as part of one sale. &e>ll &e>ll need to include *aleI' as part of a composite primary key so that
Chapter Two – Two –
The Relational Model
the sale items are grouped according to their corresponding *!2". *aleI' will also be the foreign foreign key linking to *!2". Item and Price now belong in *!2"OI$"), and we>ll need to add a Pre$a-$otal to *!2"9tawill now only be calculated calculated on the pretapreta- total value of the sale. $he result is4 CUSTOER 'C)+t.!$-I<( +tN!$( =i-+tN!$( P,.n$( E!i#0 SAE 'S#$I<( !ustomerID !ustomerID(( [email protected]$< I [email protected]$<t$( t$( P-$T P-$TxT xT.t#( .t#( Tx( T.t#0 SAEITE 'SaleID 'SaleID(( S#$It$!I<( [email protected]$It$!( P-i8$0 &e now have an improved clean design, and the 1*$+)"R and *!2"OI$") *!2"O I$") tables are in (6<. *!2"* is still denormalied denormali ed as discussed discuss ed in part . &e have good primary and foreign keys, and now a customer can purchase as many of an item on the same date as he or she wants to and all items can be part of =ust one saleC -. 'ien your your assumptions assumptions comment comment on the appropriateness appropriateness of the the follo+ing follo+ing designs: designs:
-.
=U;"7A(% @=urchase*tem, =urchase=rice, =urchaseate, Cendor, =hone NOT GOO< $here may be be many purchases of the same item item 7“andles”8.
/.
=U;"7A(% @=urchase*tem, =urchase=rice, =urchaseate, Cendor, =hone NOT GOO< $here may be be many purchases of the same item item that cost the same amount of money 7“andles, @N.NN”8.
I.
=U;"7A(% @urchase4tem , =urchase=rice, =urchaseate, Cendor, Cendor, =hone NOT GOO< $his limits purchases of a particular item to one per day. day.
G.
=U;"7A(% @=urchase*tem, =urchase=rice, =urchaseate, Cendor, =hone NOT GOO< $his limits purchases purchases of a particular particular item to one per vendor.
6.
=U;"7A(% @=urchase*tem, =urchase=rice, =urchaseate and& C%:O; @Cendor, =hone NOT GOO< It does, however however separate the two two themes of P1R3!*" P1R3!*" and 5"6'+R. 3owever, there is no foreign foreign key to link the tables. )oreover, this design still limits purchases of a particular item to one per day.
$.
=U;"7A(% @=urchase*tem, =urchase=rice, =urchaseate, Cendor
Chapter Two – Two –
The Relational Model
and& C%:O; @Cendor, =hone BETTER( BUT STI NOT GOO< It separates the two themes of P1R3!*" and 5"6'+R, but they are not properly properly linked by a foreign key. 1.
=U;"7A(% @=urchase*tem, =urchase=rice, =urchaseate, 6endor and& C%:O; @Cendor, =hone GOO<( *)t 8.)# *$ BETTER It separates the two themes of P1R3!*" and 5"6'+R, which are now properly linked by a foreign key. key. 3owever, this design still limits purchases of a particular particular item to one per day d ay.. $hat said, this is the best design design of the bunch. 9. Godify +hat +hat you consider consider to be the best best design in part - to include surrogate surrogate 4D columns called urchase4D and 6endor4D. 3o+ does this improe the design?
$he best design in part " was was number B, so we>ll put in in the I' columns. $hese columns will become become the new primary keys, keys, and we>ll need to ad=ust the foreign foreign key in P1R3!*". P1R3!*". $he result is4 PURCHASE 'P)-8,+$I<( It$!( P)-8,+$P-i8$( P)-8,+$<t$( (endorID00 (endorID VEN
'V$n.-I<( P,.n$0
'V$n.-I<( V$n.-0
&e could further normalie 5"6'+R, but we will intentionally leave it this way. way. !s discussed in part ', this is called denormaliation denormaliation and is discussed in hapter H. $he point is that creating creating the e-tra table table 75"6'+ROP3+6"8 is more trouble than it is worth. $he primary key problems problems with both tables tables are resolved, resolved, and now the Queen !nne uriosity *hop can purchase as many of an item on the same date as needed.
Chapter Two – Two –
The Relational Model
'. The relations relations in your design design from part D and part part 9 are are not connected. connected. Godify the the database design so that sales data and purchase data are related.
$he connection between the two two parts of the database database design is the item being rst purchased and then sold. $hus, we can create an integrated design by replacing InvoiceItem in *!2"OI$") * !2"OI$") with PurchaseI' as a foreign key. key. &e will rename Price in *!2"OI$") as *alePrice. +ur nal design will be4 CUSTOER 'C)+t.!$-I<( +tN!$( =i-+tN!$( P,.n$( E!i#0 SAE 'S#$I<( !ustomerID !ustomerID(( [email protected]$< I [email protected]$<t$( t$( P-$T P-$TxT xT.t#( .t#( Tx( T.t#0 SAEITE 'SaleID 'SaleID(( S#$It$!I<( PurchaseID PurchaseID(( S#$P-i8$0 PURCHASE 'P)-8,+$I<( P)-8,+$It$!( P)-8,+$P-i8$( P)-8,+$<t$( (endorID (endorID00 VEN
