CS 377 Database Systems Relational Data Model Department of - - PowerPoint PPT Presentation
CS 377 Database Systems Relational Data Model Department of Mathematics and Computer Science Emory University 1 Outline Relational Model Concepts Relational Model Constraints Relational Database and
1
Emory University
Relational Model Concepts Relational Model Constraints Relational Database and operations
2
Introduced by Codd in "A Relational Model for
First commercial implementations available in
3
First commercial implementations available in
Based on the concept of a mathematical relation
Other models: hierarchical model, network model
A relation may be thought of as a . A relation may alternately be thought of as a . Each row represents a fact that corresponds to a real/world or
!.
Table name and column names help interpret the meaning of the values
4
The table is called a relation, a row is a tuple, a column header is an attribute
(or arity) of a relation is the number of attributes of its relational schema E.g. STUDENT (Name, SSN, HomePhone, Address, OfficePhone, Age, GPA)
Each # has a that defines the possible values of the
attribute by a data/type or a format
5
attribute by a data/type or a format
E.g. The domain of SSN is the set of 9 digit numbers defined as: ddd/dd/dddd
where each d is a decimal digit.
A (or relation state) r of the relation schema
A ! t is an ordered set of n values t =<v1, v2, ..., vn>, where
E.g. <“Benjamin Bayer", 305/61/2435, 373/1616, “2918 Bluebonnet Lane”, null,
19, 3.21> is a tuple belonging to the STUDENT relation.
6
19, 3.21> is a tuple belonging to the STUDENT relation.
A relation () is a of degree on the domains
dom(1), dom(2), ..., dom(), which is a #of the $ !of the domains that define R:
⊆
The Cartesian product is the direct product of the sets of values of all
domains:
The total number of tuples in the Cartesian product is:
7
$ reflects only the valid tuples that represent a
particular state
Ordering of tuples in a relation r(R)
Ordering of attributes
list in our definition
where each pair gives the value of the mapping from an attribute to a value from dom()
Values in a tuple
8
Values in a tuple
assumption) – what about multi/valued attributes and composite attributes?
certain tuples.
9
Relation schema of degree : (, , ..., Relation names: , , Relations: , , Tuples: , , in a relation (): , , ..., is the
10
in a relation (): , , ..., is the
Component values of tuples:
[] and refer to the value in for attribute [, , ..., !] and (, , ..., !) refer to the subtuple
Relational Model Concepts Relational Model Constraints Relational Database and operations
11
Constraints
Restrictions on the actual values in a database state
&# or !
Inherent in the data model E.g. no duplicate tuples
12
&# or '!
Can be directly expressed in schemas of the data model
!!&#or or #
application program
E.g. the max. no. of hours per employee for all projects he or she
works on is 56 hrs per week
Domain constraints Key constraints Entity integrity constraints Referential integrity constraints
13
Referential integrity constraints
The value of each attribute A must be an atomic value
Typical data types associated with domains:
Numeric data types for integers and real numbers
14
Characters Booleans Fixed/length strings Variable/length strings Date, time, timestamp Money Other special data types
No two tuples can have the same combination of
!(
No two distinct tuples in any state of can have the
15
No two distinct tuples in any state of can have the
)
Superkey of R Removing any attribute from " leaves a set of
Key satisfies two properties:
Two distinct tuples in any state of relation cannot have
Minimal superkey
16
Cannot remove any attributes and still have uniqueness
constraint in above condition hold
$(
Relation schema may have more than one key
*(of the relation
Designated among candidate keys
17
Designated among candidate keys Underline attribute
Other candidate keys are designated as +(
18
# will have the same value for SK.
≠ ≠ ≠ t2[SK].
removal of any attribute from K results in a set of attributes that is not a superkey.
19
attributes are .
schema R cannot have null values in any tuple of r(R). t[PK] ≠ ≠ ≠ ≠ null for any tuple t in r(R)
Primary key values are used to &# the individual tuples. Note: Other attributes of R may be similarly constrained to disallow
null values, even though they are not members of the primary key.
20
null values, even though they are not members of the primary key.
relation must refer to an existing tuple in that relation.
Formally
FK have the same domains as the primary key attributes PK of R2
21
The value of FK in the current state of R1 can be either:
(1) a value of PK in the current state of R2: t1[FK] = t2[PK]. (2) a null
R1 is the and R2 is the . A tuple t1 in R1 is said to a tuple t2 in R2 if t1[FK] = t2[PK]. A constraint can be displayed in a relational
database schema as a directed arc from R1.FK to R2.
22
23
24
25
Relational Model Concepts Relational Model Constraints Relational Database and operations
26
#
Set of relation schemas {1, 2, ..., } Set of integrity constraints IC
#
Set of relation states '({ , , ..., }
27
Set of relation states '({, , ..., } Each is a state of such that the relation states satisfy integrity
constraints specified in IC
/
Does not obey all the integrity constraints Satisfies all the constraints in the defined set of integrity constraints
IC
Basic operations that change the states of relations in
Insert Delete
28
Update (or Modify)
Provides a list of attribute values for a new tuple t
Can violate any of the four types of constraints
Default option is to reject the insertion
29
Can violate only referential integrity
If tuple being deleted is referenced by foreign keys from other tuples, e.g.
delete a tuple from department
Reject the deletion $Propagate the deletion by deleting tuples that reference the deleted
tuple
violation
30
violation
Necessary to specify a condition on attributes of
Select the tuple (or tuples) to be modified
If attributes to be updated not part of a primary key
31
If attributes to be updated not part of a primary key
Usually causes no problems
Updating a primary/foreign key
Similar issues as with Insert/Delete
Consider the following relations for a database that keeps track of student enrollment in courses and the books adopted for each course: STUDENT(SSN, Name, Major, Bdate) COURSE(Course#, Cname, Dept)
32
COURSE(Course#, Cname, Dept) ENROLL(SSN, Course#, Quarter, Grade) BOOK_ADOPTION(Course#, Quarter, Book_ISBN) TEXT(Book_ISBN, Book_Title, Publisher, Author) ! ( .