Transcript Chapter 6

Chapter 6
The Relational Data Model &
Relational Algebra
Chapter 6
1
• A relation schema R, denoted R(A1, …, An),
consists of a relation name R and a list of
attributes A1,…,An eg. Course (CourseName,
CourseNumber,
EndTime, Days)
InstructorName,
StartTime,
• Each attribute Ai is the name of a role
played by domain dom (Ai). The number n
of attributes is the degree of the relation
schema.
• A data type is specified for each dom(Ai),
eg., char, int, money, ...
Chapter 6
2
• A relation (or relation instance) r of the
relation schema R(A1,…An) is a set
r={t1,...,tn} where each ti is a n-tuple of the
form <r1,…,rn> s.t. each value ri  dom(Ai) or
ri is a special null value.
• The value of attribute Ai for tuple tj is
denoted tj(Ai).
• Informally, a relation is a table of tuples.
Chapter 6
3
For the relation schema
President (Name, YearStart, YearEnd)
a relation is
{<Clinton,’92,NULL
>,<Bush,’88,’92>}*
* The intension of schema is to assert facts
from the miniworld - equivalently, it defines
a “predicate” for relation tuples to satisfy.
The tuples in a relation are unordered, and
need not be a complete set satisfying the
schema predicate; however, no duplicates.
Chapter 6
4
President
Name
YearStart YearEnd
Bush
‘88
‘92
Carter
‘76
‘80
Regan
‘80
‘88
Clinton ‘92
NULL
Chapter 6
5
• A subset S of attributes of a relation schema
R, s.t. NO two tuples in any relation
instance r of R can have the same
combination of values for attributes in S, is
called a superkey. Thus, the values of the
attributes in S uniquely identify a tuple.
• A superkey K, with the property that
removing any attribute from K leaves a
subset that is not a superkey, is called a key:
it is a minimal superkey.
Chapter 6
6
Car License Number
EngineSerial Make Model
Number
Texas ABC-739
A69352
Ford Mustan
g
Florida TVP-347
B43696
Olds Cutlass
New York MPO-222 X83554
Olds Delta
Person
Name
J. Smith
P. Tan
N. Ram
Year
90
93
89
StreetAddress City
Zipcode
52 N. Cramer St. Milwaukee
53211
162 Lake Dr.
Red Bank
07703
22 State St
Ann Arbor
48105
Chapter 6
7
• A relation schema may have more than one
key. Each such key is called a candidate key.
One candidate key is designated the primary
key and is used to identify tuples in a
relation. Attributes in a primary key are
shown underlined in a relation schema.
Car LicenseNumber EngineSerial Number Make Model Year
Person Name Street City Zipcode PhoneNumber
Chapter 6
8
Constrains
• Domain Constraint: value of each attribute
A must be an atomic value from dom(A).
This means that the DBMS does type
checking.
• Key Constraint: No two tuples can have
the same combination of values for
attributes in the primary key. This is also
DBMS enforced
Chapter 6
9
Relational Database Schema
• A relational database schema is a set
S = { R1, . . . , Rn} of relational schemas.
• A relation database instance I of S is a set
I = {r1, . . ., rn} of relational instances, s.t.
each ri is an instance of Ri.
Chapter 6
10
Chapter 6
11
Chapter 6
12
Chapter 6
13
Referential Integrity Constraints
• The arrows on the next slide represent
referential integrity constraints. The
value of the attribute at the tail of the arrow
(called foreign key) must, for each tuple, be
either null or equal to the value of the
attribute at the head of the arrow for some
tuple
Chapter 6
14
Chapter 6
15
Maintenance Operations
• Insert: adds new tuples to a relation.
• Delete: removes tuples from a relation.
• Update: changes values of attributes of
existing tuples in a relation.
Chapter 6
16
Relational Algebra
• Select : s<selection condition> (<relation name>)
• Project : p<attribute list> (<relation name>)
s(DNO =4 & SALARY>25000)OR(DNO=5 & SALARY.30000)
(EMPLOYEE)
p LNAME, FNAME, SALARY (EMPLOYEE),
p SEX, SALARY (EMPLOYEE)*
* Projection requires duplicate elimination.
Chapter 6
17
Chapter 6
18
Relational Algebra Expressions
• Query: Retrieve first name, last name,
salary of employee in department number 5.
(a) p FNAME,LNAME, SALARY
(s
DNO=5(EMPLOYEE))
(b)TEMP <-- s DNO=5 (EMPLOYEE)
R(FIRSTNAME, LASTNAME, SALARY)*
<-- p FNAME, LANEM,SALARY(TEMP)
* Attributes renamed.
Chapter 6
19
Chapter 6
20
Fig 6.11
Chapter 6
21
Fig 6.12
Chapter 6
22
Joins
• A Join is like a Cartesian Product except
that it has a join condition in which an
attribute(s) of one table are compared to
those of another table.
Chapter 6
23
Joins (con’t)
• There are three types of joins.
– Equijoin Join. Where the attributes are equal
and the result has both of them in it.
– Natural Join. Where the attributes are both
equal and have the same name. Result has
that name only once.
– Theta Join. The General Condition where a
comparison is made between selected attributes
of two or more tables.
Chapter 6
24
Joins (con’t)
• Join:
<relation name> |><|<join condition> <relation name>
Query: Retrieve the name of each department
manager.
DEPT_MGR
DEPARTMENT|><|MGRSSN=SSN EMPLOYEE
Chapter 6
25
AB
BC
Flight
21
30
18
Departs
9:40
12:30
15:18
Arrives
12:10
15:37
19:05
Flight Departs Arrives
11
9:18
11:10
104
14:45 16:05
6
18:06 20:08
Chapter 6
26
Connect_AC
AB
AB
AB
BC
BC
Flight Departs Arrives Flight Departs
21
9:40
12:10
104
14:45
21
9:40
12:10
6
18:06
30
12:30
15:37
6
18:06
Chapter 6
27
Fig 6.14
Chapter 6
28
Division
• If r is an instance of R(Z) and S is an
instance of S(X) and X then rs is an
instance of T(Y), where Y= Z-X and s.t. a
tuple is in rs iff its values appear in r in
combination with every tuple in S
Chapter 6
29
Fig 6.15
Chapter 6
30
Aggregate (or Set) Functions
<Grouping attributes>F<function list>(<relation name>)
• Valid Aggregate Functions
–
–
–
–
Count
Average
Maximum
Minimum
Chapter 6
31
Fig 6.16
Chapter 6
32
ER to Relational Mapping
• Step 1: For each regular entity type E in the
ER schema, create a relation R that includes
all the simple attributes of E. Include on the
simple component attributes of a composite
attribute. Choose one of the key attributes
of E as primary key for R. If the chosen
key of E is composite, the set of simple
attributes that form it will together form the
primary key of R.
Chapter 6
33
ER to Relational Mapping(con’t)
• Step 2: For each weak entity type W in the
ER Schema with owner entity type E, create
a relation R, and include all simple
attributes (or simple components of
composite attributes of W as attributes of R.
In addition, include as foreign key attributes
of R the primary key attribute(s) of the
relation(s) that correspond to the owner
entity type(s).
Chapter 6
34
ER to Relational Mapping(con’t)
• Step 3: For each binary 1:1 relationship
type R in the ER schema, identify the
relations S and T that correspond to the
entity types participating in R. Choose one
of the relations-S, say- and include as
foreign key in S the primary key of T. It is
better to choose an entity type with total
participation in R in the role of S. Include
the simple attributes of the 1:1 relationship
type R as attributes of S.
Chapter 6
35
ER to Relational Mapping(con’t)
• Step 4: For each regular (non-weak) binary
1:N relationship type R, identify the relation
S that represents the participating entity
type at the N-side of the relationship type.
Include as foreign key in S the primary key
of the relations T that represents the other
entity type participating in R. Include an
simple attributes of the 1:n relationship type
as attributes of S.
Chapter 6
36
ER to Relational Mapping(con’t)
• Step 5: For each binary M:N relationship
type R, create a new relation S to represent
R. Include as foreign key attributes in S the
primary keys of the relations that represent
the participating entity types; their
combination will form the primary key of S.
Also, include any simple attributes of the
M:N relationship type as attributes of S.
Chapter 6
37
ER to Relational Mapping(con’t)
• Step 6: For each multivalued attribute A,
create a new relation R that includes an
attribute corresponding to A plus the
primary key attribute K(as a foreign key in
R) of the relation that represents the entity
type or relationship type that has A as an
attribute. The primary key of R is the
combination of A and K. If a multivalued
attribute is composite, we include its
components.
Chapter 6
38
ER to Relational Mapping(con’t)
• Step 7: For each n-ary relationship type R,
n>2, create a new relation S to represent R.
Include as foreign key attributes in the S the
primary keys of the relations that represent
the participating entity types. Also include
any simple attributes of the n-ary relationship
types as attributes of S. The primary key for
S is usually a combination of all the foreign
keys that reference the relations representing
the participating entity types. However, if
Chapter 6
39
Step 7 (con’t)
The participation constraint (min,max) of one
of the entity types E participating in the R
has max =1, then the primary key of S can
be the single foreign key attribute that
references the relation E’ corresponding to
E; this is because , in this case, each entity e
in E will participate in at most one
relationship instance of R and hence can
uniquely identify that relationship instance.
Chapter 6
40