Order of attributes is arbitrary , but in practice w e - - PDF document

▶
order of attributes is arbitrary but in practice w e need
SMART_READER_LITE
LIVE PREVIEW

Order of attributes is arbitrary , but in practice w e - - PDF document

Feb 02, 2023 247 likes •492 views

Relational Mo del T able = relation. Column headers = attributes . Ro w = tuple name manf Win terBrew P ete's BudLite A.B. Beers = name(attributes) + other R elation

slide-1
SLIDE 1 Relational Mo del
  • T
able = relation.
  • Column
headers = attributes.
  • Ro
w = tuple name manf Win terBrew P ete's BudLite A.B.
  • Beers
  • R
elation schema = name(attributes) +
  • ther
structure info., e.g., k eys,
  • ther
constrain ts. Example: Beers(name, manf).

✦

Order
  • f
attributes is arbitrary , but in practice w e need to assume the
  • rder
giv en in the relation sc hema.
  • R
elation instanc e is curren t set
  • f
ro ws for a relation sc hema.
  • Datab
ase schema = collecti
  • n
  • f
relation sc hemas. 1
slide-2
SLIDE 2 Wh y Relations?
  • V
ery simple mo del.
  • Often
a go
  • d
matc h for the w a y w e think ab
  • ut
  • ur
data.
  • Abstract
mo del that underlies SQL, the most imp
  • rtan
t language in DBMS's to da y .

✦

But SQL uses \bags," while the abstract relational mo del is set-orien ted. 2
slide-3
SLIDE 3 Relational Design Simplest approac h (not alw a ys b est): con v ert eac h E.S. to a relation and eac h relationship to a relation. En tit y Set ! Relation E.S. attributes b ecome relational attributes. name manf Beers Becomes: Beers(name, manf) 3
slide-4
SLIDE 4 E/R Relationships ! Relations Relation has attribute for key attributes
  • f
eac h E.S. that participates in the relationship.
  • Add
an y attributes that b elong to the relationship itself.
  • Renaming
attributes OK.

✦

Essen tial if m ultiple roles for an E.S. 4
slide-5
SLIDE 5 name name Drink ers Lik es Beers manf addr F a v
  • rite
Buddies Married 1 2 h usband wife Lik es(drink er, b eer) F a v
  • rite(drink
er, b eer) Buddies(name1, name2) Married(h usband, wife) 5
slide-6
SLIDE 6 Com bining Relations Sometimes it mak es sense to com bine relations.
  • Common
case: Relation for an E.S. E plus the relation for some man y-one relationship from E to another E.S. Example Com bine Drinker(name, addr) with Favorite(drinker, beer) to get Drinker1(name, addr, favBeer).
  • Danger
in pushing this idea to
  • far:
redundancy .
  • e.g.,
com bining Drinker with Likes causes the drink er's address to b e rep eated viz.: name addr beer Sally 123 Maple Bud Sally 123 Maple Miller
  • Notice
the dierence: Favorite is man y-one; Likes is man y-man y . 6
slide-7
SLIDE 7 W eak En tit y Sets, Relationships ! Relations
  • Relation
for a w eak E.S. m ust include its full k ey (i.e., attributes
  • f
related en tit y sets) as w ell as its
  • wn
attributes.
  • A
supp
  • rting
(double-diamond) relationship yields a relation that is actually redundan t and should b e deleted from the database sc hema. 7
slide-8
SLIDE 8 Example @ Logins Hosts name name Hosts(hostName) Logins(loginName, hostName) A t(loginName, hostName, hostName2)
  • In
At, hostName and hostName2 m ust b e the same host, so delete
  • ne
  • f
them.
  • Then,
Logins and At b ecome the same relation; delete
  • ne
  • f
them.
  • In
this case, Hosts' sc hema is a subset
  • f
Logins' sc hema. Delete Hosts? 8
slide-9
SLIDE 9 Sub classes ! Relations Three approac hes: 1. Ob ject-orien ted: eac h en tit y is in
  • ne
class. Create a relation for eac h class, with all the attributes for that class.

✦

Don't forget inherited attributes. 2. E/R st yle: an en tit y is in a net w
  • rk
  • f
classes related b y isa. Create
  • ne
relation for eac h E.S.

✦

An en tit y is represen ted in the relation for eac h sub class to whic h it b elongs.

✦

Relation has
  • nly
the attributes attac hed to that E.S. + k ey . 3. Use n ulls. Create
  • ne
relation for the ro
  • t
class
  • r
ro
  • t
E.S., with all attributes found an ywhere in its net w
  • rk
  • f
sub classes.

✦

Put NULL in attributes not relev an t to a giv en en tit y . 9
slide-10
SLIDE 10 Example isa Beers Ales manf color name 10
slide-11
SLIDE 11 OO-St yle name manf Bud A.B. Beers name manf color SummerBrew Pete's dark Ales E/R St yle name manf Bud A.B. SummerBrew Pete's Beers name color SummerBrew dark Ales 11
slide-12
SLIDE 12 Using Nulls name manf color Bud A.B. NULL SummerBrew Pete's dark Beers 12
slide-13
SLIDE 13 F unctional Dep endencies X ! A = assertion ab
  • ut
a relation R that whenev er t w
  • tuples
agree
  • n
all the attributes
  • f
X , then they m ust also agree
  • n
attribute A. Example Drinkers(name, addr, beersLiked, manf, favoriteBeer) name addr b eersLik ed manf fa v
  • riteBeer
Janew a y V
  • y
ager Bud A.B. Wic k edAle Janew a y V
  • y
ager Wic k edAle P ete's Wic k edAle Sp
  • c
k En terprise Bud A.B. Bud
  • Reasonable
FD's to assert: 1. name ! addr 2. name ! favoriteBeer 3. beersLiked ! manf 13
slide-14
SLIDE 14
  • Shorthand:
com bine FD's with common left side b y concatenating their righ t sides.
  • Sometimes,
sev eral attributes join tly determine another attribute, although neither do es b y itself. Example: beer bar ! price 14
slide-15
SLIDE 15 Keys
  • f
Relations K is a key for relation R if: 1. K ! all attributes
  • f
R . 2. F
  • r
no prop er subset
  • f
K is (1) true.
  • If
K at least satises (1), then K is a sup erkey. Con v en tio ns
  • Pic
k
  • ne
k ey; underline k ey attributes in the relation sc hema.
  • X
, etc., represen t sets
  • f
attributes; A etc., represen t single attributes.
  • No
set formers in FD's, e.g., AB C instead
  • f
fA; B ; C g. 15
slide-16
SLIDE 16 Example Drinkers(name , addr, beersLiked , manf, favoriteBeer)
  • fname,
beersLikedg FD's all attributes, as seen.

✦

Sho ws fname, beersLikedg is a sup erk ey .
  • name
! beersLiked is false, so name not a sup erk ey .
  • beersLiked
! name also false, so beersLiked not a sup erk ey .
  • Th
us, fname, beersLikedg is a k ey .
  • No
  • ther
k eys in this example.

✦

Neither name nor beersLiked is
  • n
the righ t
  • f
an y
  • bserv
ed FD, so they m ust b e part
  • f
any sup erk ey .
  • Imp
  • rtan
t p
  • in
t: \k ey" in a relation refers to tuples, not the en tities they represen t. If an en tit y is represen ted b y sev eral tuples, then en tit y-k ey will not b e the same as relation-k ey . 16
slide-17
SLIDE 17 Who Determines Keys/FD's?
  • W
e could assert a k ey K .

✦

Then the
  • nly
FD's asserted are that K ! A for ev ery attribute A.

✦

No surprise: K is then the
  • nly
k ey for those FD's, according to the formal denition
  • f
\k ey ."
  • Or,
w e could assert some FD's and de duc e
  • ne
  • r
more k eys b y the formal denition.

✦

E/R diagram implies FD's b y k ey declarations and man y-one relationship declarations.
  • Rule
  • f
th um b: FD's either come from k eyness, man y-1 relationship,
  • r
from ph ysics.

✦

E.g., \no t w
  • courses
can meet in the same ro
  • m
at the same time" yields room time ! course. 17
slide-18
SLIDE 18 Inferring FD's And this is imp
  • rtan
t b ecause . . .
  • When
w e talk ab
  • ut
impro ving relational designs, w e
  • ften
need to ask \do es this FD hold in this relation?" Giv en FD's X 1 ! A1, X 2 ! A2
  • X
n ! An, do es FD Y ! B necessarily hold in the same relation?
  • Start
b y assuming t w
  • tuples
agree in Y . Use giv en FD's to infer
  • ther
attributes
  • n
whic h they m ust agree. If B is among them, then y es, else no. 18
slide-19
SLIDE 19 Algorithm Dene Y + = closur e
  • f
Y = set
  • f
attributes functionally determined b y Y :
  • Basis:
Y + := Y .
  • Induction:
If X
  • Y
+ , and X ! A is a giv en FD, then add A to Y + . X A Y + new Y +
  • End
when Y + cannot b e c hanged. 19
slide-20
SLIDE 20 Example A ! B , B C ! D .
  • A
+ = AB .
  • C
+ = C .
  • (AC
) + = AB C D . A C B D 20
slide-21
SLIDE 21 Finding All Implied FD's Motiv atio n: Supp
  • se
w e ha v e a relation AB C D with some FD's F . If w e decide to decomp
  • se
AB C D in to AB C and AD , what are the FD's for AB C , AD ?
  • Example:
F = AB ! C , C ! D , D ! A. It lo
  • ks
lik e just AB ! C holds in AB C , but in fact C ! A follo ws from F and applies to relation AB C .
  • Problem
is exp
  • nen
tial in w
  • rst
case. 21
slide-22
SLIDE 22 Algorithm
  • F
  • r
eac h set
  • f
attributes X compute X + .

✦

But skip X = ;, X = all attributes.

✦

Add X ! A for eac h A in X +
  • X
.
  • Drop
X Y ! A if X ! A holds.
  • Finally
, pro ject the FD's b y selecting
  • nly
those FD's that in v
  • lv
e
  • nly
the attributes
  • f
the pro jection.

✦

Notice that after w e pro ject the disco v ered FD's
  • n
to some relation, the eliminated FD's can b e inferred in the pr
  • je
cte d r elation. 22
slide-23
SLIDE 23 Example In AB C with FD's A ! B , B ! C , pro ject
  • n
to AC . 1. A + = AB C ; yields A ! B , A ! C . 2. B + = B C ; yields B ! C . 3. AB + = AB C ; yields AB ! C ; drop in fa v
  • r
  • f
A ! C . 4. AC + = AB C yields AC ! B ; drop in fa v
  • r
  • f
A ! B . 5. C + = C and B C + = B C ; adds nothing.
  • Resulting
FD's: A ! B , A ! C , B ! C .
  • Pro
jection
  • n
to AC : A ! C . 23