Download - relationa
![Page 1: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/1.jpg)
Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Pearson Education, Inc.
Brownsmith, J. D., UNCA, Copyright 2004.
![Page 2: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/2.jpg)
Note
These slides have been edited (improved by adding slides, figures and symbols, and in minor
ways) by Dr. JDBrownsmith
![Page 3: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/3.jpg)
Chapter 5
The Relational Data Model and Relational Database Constraints
![Page 4: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/4.jpg)
Chapter 5-4
Chapter Outline
Relational Model ConceptsRelational Model Constraints and Relational Database
SchemasUpdate Operations and Dealing with Constraint
Violations
![Page 5: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/5.jpg)
Chapter 5-5
5.1 Relational Model Concepts
The relational Model of Data is based on the mathematical concept of a relation (which is based on the ideas of sets).
The strength of the relational approach to data management comes from the formal foundation provided by the theory of relations.
We present the essentials of the relational approach in this chapter.
![Page 6: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/6.jpg)
Chapter 5-6
The model was first proposed by Dr. E.F. Codd of IBM in 1970 in the following paper:"A Relational Model for Large Shared Data Banks," Communications of the ACM, June 1970.
The above paper caused a major revolution in the field of Database management and earned Ted Codd the coveted ACM Turing Award.
Relational Model Concepts
![Page 7: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/7.jpg)
Chapter 5-7
INFORMAL DEFINITIONSRELATION: A table of values
– A relation may be thought of as a set of rows.– A relation may alternately be thought of as a set of columns.– Each row represents a fact that corresponds to a real-world entity or
relationship.– Each row has a value of an item or set of items that uniquely
identifies that row in the table.– Sometimes row-ids or sequential numbers are assigned to identify the
rows in the table.– Each column typically is called by its column name or column header
or attribute name.
Relational Model Concepts
![Page 8: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/8.jpg)
Chapter 5-8
I'm not sure I got that. Please provide a lucid example or I'll shoot you with my finger.
![Page 9: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/9.jpg)
Chapter 5-9
table
1. A relation is a table of values
row
The "ENum" column
column
relationtuple
Employee ENum EName Dept Hire_date
204 R. B. Jones E21 11/01/19991917 M. R. Brent E21 10/10/2003
TABLE **
![Page 10: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/10.jpg)
Chapter 5-10
FORMAL DEFINITIONS
Each row in the EMPLOYEE table may be referred to as a tuple in the table and would consist of four values, e.g.:<1917, "M. R. Brent", "E21", "10/10/2003"> .
A relation (table) may be regarded as a set of tuples (rows).
Employee ENum EName Dept Hire_date
204 R. B. Jones E21 11/01/19991917 M. R. Brent E21 10/10/2003
"Tuple" rhymes with "couple". Got it.
![Page 11: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/11.jpg)
Chapter 5-11
table
2. No two rows in a table can be the same (i.e., they can't have identical values)
relationtuple
Employee ENum EName Dept Hire_date
204 R. B. Jones E21 11/01/19991917 M. R. Brent E21 10/10/2003
The ROWS of a TABLE
Well why not?
row
![Page 12: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/12.jpg)
Chapter 5-12
OK, to clarify, in the Relational Modelno two rows in a table can be the same.
In practice, using SQL, two rows can bethe same.
The ROWS of a TABLE
So, the theory is no good!
![Page 13: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/13.jpg)
Chapter 5-13
The theory is excellent and should be yourfoundation/guide/mentor in practice. You need not implement according to the relational theory, but beware of poor designs, complexities, mistakes, and errors.
We will implement according to the theory.
The ROWS of a TABLE
OK for now, but I'll be watching.
![Page 14: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/14.jpg)
Chapter 5-14
FORMAL DEFINITIONSA Relation may be defined in multiple ways.
The Schema of a Relation: R (A1, A2, .....An)Relation schema R is defined over attributes A1, A2, .....An
For Example -EMPLOYEE (ENum, EName, Dept, Hire_date)
Here, EMPLOYEE is a relation defined over the four attributes ENum, EName, Dept, and Hire_date, each of which has a domainor a set of valid values. For example, the domain of ENum is 4 digit integer numbers.
![Page 15: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/15.jpg)
Chapter 5-15
Let's see if I've got that. Employee is a table with
attributes, each of which has a set of valid values. Sounds like a
data type to me. ZZZzzz.
![Page 16: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/16.jpg)
Chapter 5-16
A row (tuple) is a set of values In relational theory this set can be ordered or unordered In practice the values are ordered within a row
A row (tuple) is a collection of related data values
"Tuple" rhymes with "supple". Got it.
The ROWS of a TABLE
Employee ENum EName Dept Hire_date
204 R. B. Jones E21 11/01/19991917 M. R. Brent E21 10/10/2003
![Page 17: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/17.jpg)
Chapter 5-17
The second row states that an Employee has employee number (ENum) 1917, has name (EName) M.R. Brent, works in Department (Dept) E21, and was hired on (Hire_date) 10/10/2003.
The ROWS of a TABLE
Employee ENum EName Dept Hire_date
204 R. B. Jones E21 11/01/19991917 M. R. Brent E21 10/10/2003
![Page 18: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/18.jpg)
Chapter 5-18
Notes
![Page 19: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/19.jpg)
Chapter 5-19
The columns of a table are also called attributes of the relation.
The attribute help in interpreting the meaning of the valuesThe attribute names will be useful in searching the table.
For example: Find all EName where Dept=E21.
Employee ENum EName Dept Hire_date
204 R. B. Jones E21 11/01/19991917 M. R. Brent E21 10/10/2003
The COLUMNS of a TABLE
In plain English, that would be ________________________
Attributes
![Page 20: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/20.jpg)
Chapter 5-20
A domain is a set of values.
The COLUMNS of a TABLE
Employee ENum EName Dept Hire_date
204 R. B. Jones E21 11/01/19991917 M. R. Brent E21 10/10/2003
... ... ... ... Domains
Attributesp. 127
![Page 21: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/21.jpg)
Chapter 5-21
A domain is a set of values.
The COLUMNS of a TABLE
Employee ENum EName Dept Hire_date
204 R. B. Jones E21 11/01/19991917 M. R. Brent E21 10/10/2003
• A domain for Enum is all positive integers greater than 0 and less than 10000.• A domain for EName is all stings less than 26 characters.• A domain for Dept is ______________• A domain for Hire_date is _________________
Example domains
Domains are established in accordance with business rules.
![Page 22: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/22.jpg)
Chapter 5-22
A domain has a name, data type, and a format. It also has constraints and a set of permissible operators
The COLUMNS of a TABLE
Employee ENum EName Dept Hire_date
204 R. B. Jones E21 11/01/19991917 M. R. Brent E21 10/10/2003
Consider the Hire_date column of the Employee table:The domain name is denoted dom(Hire_date) and is the set of all valid hire dates.The domain data type is, say, character stringThe domain format is mm/dd/yyyy
![Page 23: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/23.jpg)
Chapter 5-23
A domain is a set of values and has a name, logical definition, data type, and a format.
It also has constraints and a set of permissible operators
The COLUMNS of a TABLE
Employee ENum EName Dept Hire_date
204 R. B. Jones E21 11/01/19991917 M. R. Brent E21 10/10/2003
Consider the Hire_date attribute of the Employee table:The domain name for this attribute is, say, date_of_hire.The attribute, Hire_date, indicates the role played by the domainThe logical definition is: the set of all valid hire dates.The domain data type is, say, character string The domain format is mm/dd/yyyy
Revised
![Page 24: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/24.jpg)
Chapter 5-24
The COLUMNS of a TABLE
Consider the Hire_date column of the Employee table:The domain name is denoted dom(Hire_date)The domain data type is, say, character stringThe domain format is mm/dd/yyyy
• This is the representation that the user sees. • There is another representation that is physically stored.• These representations need not match.
• e.g., user may see hot, warm, cold and the database storesan integer value (representing degrees centigrade).
A domain has a name, data type, and a format.
![Page 25: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/25.jpg)
Chapter 5-25
The COLUMNS of a TABLE
Consider the Hire_date column of the Employee table:The domain name is denoted dom(Hire_date)The domain data type is, say, character stringThe domain format is mm/dd/yyyy
A domain has a name, data type, and a format.
Data type: A set of rules describing a specific set of information, including the allowed range and operations, and how information is stored. (not from Elmasri text)
Data types in SQL include INT, FLOAT, CHAR(n), andBIT, and DATE (see p. 212-213)
![Page 26: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/26.jpg)
Chapter 5-26
The COLUMNS of a TABLE
Consider the Hire_date column of the Employee table:The domain name is denoted dom(Hire_date)The domain data type is, say, character stringThe domain format is mm/dd/yyyy
• There are constraints that may be in effect (e.g., yyyy must be greater than 1900).
• There is a set of valid operators for each data type (e.g., * and / are not valid, but > and < are).
A domain has a name, data type, and a format.
![Page 27: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/27.jpg)
Chapter 5-27
A domain is a set of atomic values.Atomic means that each value is indivisible as far as the
relational model is concerned
The COLUMNS of a TABLE
Employee ENum EName Dept Hire_date
204 R. B. Jones E21 11/01/19991917 M. R. Brent E21 10/10/2003
Now, there you go again. You mean I can't search on a hire date of 1999 or find all
Brents?
![Page 28: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/28.jpg)
Chapter 5-28
The COLUMNS of a TABLE
Employee ENum EName Dept Hire_date
204 R. B. Jones E21 11/01/19991917 M. R. Brent E21 10/10/2003
In general, when you define a table with attributes, you will know the data type and the operators that are in effect.This will provide the answer to that question.
We are discussing the definitions of the relational theoryand atomic attribute values.This question goes to database operation (practice).
Note: Using SQL, you can do what "angry man" wants.
![Page 29: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/29.jpg)
Chapter 5-29
Concept Review: Domain
A domain has a logical definition: e.g.,“USA_phone_numbers” are the set of 10 digit phone numbers valid in the U.S.
A domain may have a data-type or a format defined for it. The USA_phone_numbers may have a format: (ddd)-ddd-dddd where each d is a decimal digit. E.g., Dates have various formats such as monthname, date, year or yyyy-mm-dd, or dd mm,yyyy etc.
An attribute designates the role played by the domain. E.g., the domain Date may be used to define attributes “Invoice-date” and “Payment-date”.
p. 127
![Page 30: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/30.jpg)
Chapter 5-30
Notes
Write the answer to Question 5.1 (p 144) here:
domain
attribute
n-tuple
degree of a relation
set
*
![Page 31: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/31.jpg)
Chapter 5-31
I can't bear to watch
![Page 32: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/32.jpg)
Chapter 5-32
FORMAL DEFINITIONS
The relation is formed over the cartesian product of the sets; each set has values from a domain; that domain is used in a specific role which is conveyed by the attribute name.
For example, attribute EName is defined over the domain of strings of 25 characters. The role these strings play in the EMPLOYEE relation is that of the name of employees.
Formally,
Given R(A1, A2, .........., An) r(R) dom (A1) X dom (A2) X ....X dom(An)
R: schema of the relation r of R: a specific "value" or population of R.R is also called the intension of a relation r is also called the extension of a relation page 129
![Page 33: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/33.jpg)
Chapter 5-33
Example
Let S1 = {0,1}Let S2 = {a,b,c}
Let R S1 X S2
Then for example: r(R) = {<0,a> , <0,b> , <1,c> }
is one possible “state” or “population” or “extension” r of the relation R, defined over domains S1 and S2. It has three tuples.
![Page 34: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/34.jpg)
Chapter 5-34
Let's see if I've got that. A populated table is
created from the domain values of each attribute.
ZZZzzz.
![Page 35: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/35.jpg)
Chapter 5-35
DEFINITION SUMMARY
Informal Terms Formal Terms
Table Relation
Column Attribute/Domain
Row Tuple
Values in a column Domain
Table Definition Schema of a Relation
Populated Table Extension
![Page 36: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/36.jpg)
Chapter 5-36
Tell them we're working on populating our table. Waiter, more
Java and croissants please.
![Page 37: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/37.jpg)
Chapter 5-37
To reiterate...
![Page 38: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/38.jpg)
Chapter 5-38
Figure 5.1 The attributes and tuples of a relation STUDENT.
Write down five things you know about this table:
![Page 39: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/39.jpg)
Chapter 5-39
5.1.2 CHARACTERISTICS OF RELATIONS
Ordering of tuples in a relation r(R): The tuples (rows) are not considered to be ordered, even though they appear to be ordered in the tabular form.
Ordering of attributes in a relation schema R (and of values within each tuple): We will consider the attributes in R(A1, A2, ..., An) and the values in t=<v1, v2, ..., vn> to be ordered .(However, a more general alternative definition of relation does not require this ordering).
Values in a tuple: All values are considered atomic (indivisible). A special null value is used to represent values that are unknown or inapplicable to certain tuples.
p. 129
**
![Page 40: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/40.jpg)
Chapter 5-40
CHARACTERISTICS OF RELATIONS
Notation:- We refer to component values of a tuple t by t[Ai] = vi
(the value of attribute Ai for tuple t).
Similarly, t[Au, Av, ..., Aw] refers to the subtuple of t containing the values of attributes Au, Av, ..., Aw, respectively.
![Page 41: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/41.jpg)
Chapter 5-41
Figure 5.2 The relation STUDENT fromFigure 5.1 with a different order of tuples
Figure 5.1
![Page 42: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/42.jpg)
Chapter 5-42
Figure 5.3 Two identical tuples when the order of attributes and values is not part of
relation definition.
![Page 43: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/43.jpg)
Chapter 5-43
And then he showed us this totally cool example with two identical
tuples when the order of attributes and values is not part of the relation
definition.
![Page 44: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/44.jpg)
Chapter 5-44
Notes
Write the answer to Question 5.2 (p 144) here: Why arethe tuples in a relation not ordered?
Write the answer to Question 5.3 (p 144) here: Why areduplicate tuples not allowed in a relation?
![Page 45: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/45.jpg)
Chapter 5-45
5.2 Relational Integrity Constraints
Constraints are conditions that must hold on all valid relation instances. There are three main types of constraints:
1. Key constraints
2. Entity integrity constraints
3. Referential integrity constraints
![Page 46: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/46.jpg)
Chapter 5-46
Key Constraints
Superkey of R:
A set of attributes SK of R such that no two tuples in any valid relation instance r(R) will have the same value for SK. That is, for any distinct tuples t1 and t2 in r(R), t1[SK] t2[SK].
Employee ENum EName Dept Hire_date
204 R. B. Jones E21 11/01/19991917 M. R. Brent E21 10/10/2003
What's the superkey of Employee?
![Page 47: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/47.jpg)
Chapter 5-47
Key Constraints
Employee ENum EName Dept Hire_date
204 R. B. Jones E21 11/01/1999 204 R. B. Jones E21 11/01/1999
Hey, what if I have a table that doesn't have a superkey? Now what?
![Page 48: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/48.jpg)
Chapter 5-48
Key Constraints Superkey of R: A set of attributes SK of R such that no
two tuples in any valid relation instance r(R) will have the same value for SK. That is, for any distinct tuples t1 and t2 in r(R), t1[SK] t2[SK].
SuperKeys of CAR: 1. LN, ESN, Make, Model, Year;2. LN, Year; 3. ESN, Model, Year; 4. ESN; ...
Note: Year is not a superkey of CAR since more than one tuplehas the same value for year.
![Page 49: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/49.jpg)
Chapter 5-49
You're telling me about superkeys? SUPERKEYS?! Ralph, there's nothing new here. It just means that no two rows can be the same
for the set of superkey attributes. Call me back
when you have something new.
![Page 50: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/50.jpg)
Chapter 5-50
Key Constraints
Key of R: A "minimal" superkey; that is, a superkey K such that removal of any attribute from K results in a set of attributes that is not a superkey.
Example: The CAR relation schema:CAR(State, Reg#, SerialNo, Make, Model, Year)has two keys Key1 = {State, Reg#}, Key2 = {SerialNo}, which are
also superkeys. {SerialNo, Make} is a superkey but not a key.
If a relation has several candidate keys, one is chosen arbitrarily to be the primary key. The primary key attributes are underlined.
![Page 51: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/51.jpg)
Chapter 5-51
Key Constraints
Coffees Name Supplier Price Sales
Colombian 101 7.99 9FR Roast 49 8.99 4
Hey, what if I have a table that doesn't have a key? Now what?
Colombian 49 7.99 9
![Page 52: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/52.jpg)
Chapter 5-52
Key Constraints Key of R: A "minimal" superkey; that is, a superkey K
such that removal of any attribute from K results in a set of attributes that is not a superkey.
What are the candidate keys of the CAR relation? _____________
![Page 53: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/53.jpg)
Chapter 5-53
Figure 5.4 The CAR relation, with two candidate keys: LicenseNumber and EngineSerialNumber
Key Constraints
![Page 54: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/54.jpg)
Chapter 5-54
I tell you Mike, a primary key is a minimal superkey. Simple as that.
Say, where did everybody go?
![Page 55: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/55.jpg)
Chapter 5-55
5.2 Relational Integrity Constraints
Constraints are conditions that must hold on all valid relation instances. There are three main types of constraints:
1. KeyKey constraints constraints
2. Entity integrity constraints
3. Referential integrity constraints
![Page 56: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/56.jpg)
Chapter 5-56
Entity Integrity
Relational Database Schema: A set S of relation schemas that belong to the same database. S is the name of the database.
S = {R1, R2, ..., Rn}Entity Integrity: The primary key attributes PK of each
relation schema R in S cannot have null values in any tuple of r(R). This is because primary key values are used to identify the individual tuples.
t[PK] null for any tuple t in r(R) Note: Other attributes of R may be similarly constrained
to disallow null values, even though they are not members of the primary key.
![Page 57: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/57.jpg)
Chapter 5-57
Entity IntegrityRelational Database Schema: A set S of relation schemas
that belong to the same database. S is the name of the database.
S = {R1, R2, ..., Rn}
Schema S = ...
![Page 58: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/58.jpg)
Chapter 5-58
Entity Integrity
STUDENT key = ___________ COURSE key = ______________
Entity Integrity: The primary key attributes PK of each relation schema R in S cannot have null values in any tuple of r(R). This is because primary key values are used to identify the individual tuples. t[PK] null for any tuple t in r(R)
![Page 59: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/59.jpg)
Chapter 5-59
Entity Integrity
STUDENT key = ___________ COURSE key = ______________
Entity Integrity: The primary key attributes PK of each relation schema R in S cannot have null values in any tuple of r(R). This is because primary key values are used to identify the individual tuples. t[PK] null for any tuple t in r(R)
Thesevalues are constrained
![Page 60: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/60.jpg)
Chapter 5-60
Notes
![Page 61: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/61.jpg)
Chapter 5-61
5.2 Relational Integrity Constraints
Constraints are conditions that must hold on all valid relation instances. There are three main types of constraints:
1. KeyKey constraints constraints
2. Entity integrityEntity integrity constraints constraints
3. Referential integrity constraints
![Page 62: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/62.jpg)
Chapter 5-62
Referential Integrity
A constraint involving two relations (the previous constraints involve a single relation).
Used to specify a relationship among tuples in two relations: the referencing relation and the referenced relation.
Tuples in the referencing relation R1 have attributes FK (called foreign key attributes) that reference the primary key attributes PK of the referenced relation R2. A tuple t1 in R1 is said to reference a tuple t2 in R2 if t1[FK] = t2[PK].
A referential integrity constraint can be displayed in a relational database schema as a directed arc from R1.FK to R2.
![Page 63: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/63.jpg)
Chapter 5-63
Referential Integrity
foreign keyforeign key
referencing relationreferencing relation
referenced relationreferenced relation referential integrity constraintreferential integrity constraint
![Page 64: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/64.jpg)
Chapter 5-64
Referential Integrity Constraint
Statement of the constraintThe value in the foreign key column (or
columns) FK of the the referencing relation R1 can be either: (1) a value of an existing primary key value of the corresponding primary key PK in the referenced relation R2,, or..
(2) a null.
In case (2), the FK in R1 should not be a part of its own primary key.
![Page 65: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/65.jpg)
Chapter 5-65
Referential Integrity
foreign keyforeign key
referencing relationreferencing relation
referenced relationreferenced relation referential integrity constraintreferential integrity constraint
Thesevalues are constrained
![Page 66: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/66.jpg)
Chapter 5-66
![Page 67: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/67.jpg)
Chapter 5-67
Other Types of Constraints
Semantic Integrity Constraints:- based on application semantics and cannot be
expressed by the model per se- E.g., “the max. no. of hours per employee for all
projects he or she works on is 56 hrs per week”- A constraint specification language may have
to be used to express these- SQL-99 allows triggers and ASSERTIONS to
allow for some of these
![Page 68: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/68.jpg)
Chapter 5-68
Figure 5.5 Schema diagram for the COMPANY relational database schema
![Page 69: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/69.jpg)
Chapter 5-69
Figure 5.6One possible
database state for the COMPANY database schema
p. 137
![Page 70: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/70.jpg)
Chapter 5-70
Righttttttt...
![Page 71: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/71.jpg)
Chapter 5-71
Figure 5.7 Referential integrity constraints displayed on the COMPANY relational database
schema.
p. 139
![Page 72: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/72.jpg)
Chapter 5-72
Rightttttttagain.
![Page 73: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/73.jpg)
Chapter 5-73
5.3 Update Operations on Relations
The update operations are:
INSERT a tuple.DELETE a tuple.MODIFY a tuple.
![Page 74: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/74.jpg)
Chapter 5-74
Integrity constraints should not be violated by the update operations.
Several update operations may have to be grouped together.
Updates may propagate to cause other updates automatically. This may be necessary to maintain integrity constraints.
![Page 75: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/75.jpg)
Chapter 5-75
Update Operations on Relations
In case of integrity violation, several actions can be taken:– Cancel the operation that causes the violation (REJECT
option)
– Perform the operation but inform the user of the violation
– Trigger additional updates so the violation is corrected (CASCADE option, SET NULL option)
– Execute a user-specified error-correction routine
![Page 76: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/76.jpg)
Chapter 5-76
In-Class Exercise(Taken from Exercise 5.15)
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)
ENROLL(SSN, Course#, Quarter, Grade)
BOOK_ADOPTION(Course#, Quarter, Book_ISBN)
TEXT(Book_ISBN, Book_Title, Publisher, Author)
Draw a relational schema diagram specifying the foreign keys for this schema.
![Page 77: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/77.jpg)
Chapter 5-77
Figure 5.8The
AIRLINE relational database schema.
![Page 78: relationa](https://reader033.vdocuments.us/reader033/viewer/2022042814/552891ea49795928048b4971/html5/thumbnails/78.jpg)
Chapter 5-78
Notes