Relationa

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Brownsmith, J. D., UNCA, Copyright 2004.

Note These slides have been edited (improved by adding slides, figures and symbols, and in minor ways) by Dr. JDBrownsmith

Chapter 5 The Relational Data Model and Relational Database Constraints

Chapter Outline Relational Model Concepts Relational Model Constraints and Relational Database Schemas Update Operations and Dealing with Constraint Violations

Chapter 5-4

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.

Chapter 5-5

Relational Model Concepts 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.

Chapter 5-6

Relational Model Concepts INFORMAL DEFINITIONS

 RELATION: 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.

Chapter 5-7

I'm not sure I got that. Please provide a lucid example or I'll shoot you with my finger.

Chapter 5-8

**

TABLE 1. A relation is a table of values column Employee

row

ENum 204 1917

EName R. B. Jones M. R. Brent

Dept E21 E21

Hire_date 11/01/1999 10/10/2003

table relation

tuple The "ENum" column

Chapter 5-9

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 204 1917

EName R. B. Jones M. R. Brent

Dept E21 E21

Hire_date 11/01/1999 10/10/2003

"Tuple" rhymes with "couple". Got it. Chapter 5-10

The ROWS of a TABLE 2. No two rows in a table can be the same (i.e., they can't have identical values) Employee

row

ENum 204 1917

EName R. B. Jones M. R. Brent

tuple

Dept E21 E21

Hire_date 11/01/1999 10/10/2003

table relation

Well why not?

Chapter 5-11

The ROWS of a TABLE OK, to clarify, in the Relational Model no two rows in a table can be the same. In practice, using SQL, two rows can be the same.

So, the theory is no good!

Chapter 5-12

The ROWS of a TABLE The theory is excellent and should be your foundation/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. OK for now, but I'll be watching.

Chapter 5-13

FORMAL DEFINITIONS  A 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 domain or a set of valid values. For example, the domain of ENum is 4 digit integer numbers.

Chapter 5-14

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.

Chapter 5-15

The ROWS of a TABLE  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 Employee

ENum 204 1917

EName R. B. Jones M. R. Brent

Dept E21 E21

Hire_date 11/01/1999 10/10/2003

"Tuple" rhymes with "supple". Got it. Chapter 5-16

The ROWS of a TABLE Employee

ENum 204 1917

EName R. B. Jones M. R. Brent

Dept E21 E21

Hire_date 11/01/1999 10/10/2003

 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.

Chapter 5-17

Notes

Chapter 5-18

The COLUMNS of a TABLE  The columns of a table are also called attributes of the relation.  The attribute help in interpreting the meaning of the values  The attribute names will be useful in searching the table. For example: Find all EName where Dept=E21. In plain English, that would be ________________________ Employee

ENum 204 1917

EName R. B. Jones M. R. Brent

Dept E21 E21

Hire_date 11/01/1999 10/10/2003

Attributes Chapter 5-19

The COLUMNS of a TABLE  A domain is a set of values.

...

Employee

ENum 204 1917

...

EName R. B. Jones M. R. Brent Attributes

...

...

Dept E21 E21

Hire_date 11/01/1999 10/10/2003

Domains

p. 127 Chapter 5-20

The COLUMNS of a TABLE  A domain is a set of values. Employee

ENum 204 1917

EName R. B. Jones M. R. Brent

Dept E21 E21

Hire_date 11/01/1999 10/10/2003

Domains are established in accordance with business rules. Example domains • 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 _________________ Chapter 5-21

The COLUMNS of a TABLE  A domain has a name, data type, and a format.  It also has constraints and a set of permissible operators

Employee

ENum 204 1917

EName R. B. Jones M. R. Brent

Dept E21 E21

Hire_date 11/01/1999 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 string The domain format is mm/dd/yyyy Chapter 5-22

The COLUMNS of a TABLE

Revised

 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 Employee

ENum 204 1917

EName R. B. Jones M. R. Brent

Dept E21 E21

Hire_date 11/01/1999 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 domain The 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 Chapter 5-23

The COLUMNS of a TABLE A domain has a name, data type, and a format. Consider the Hire_date column of the Employee table: The domain name is denoted dom(Hire_date) The domain data type is, say, character string The 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 stores an integer value (representing degrees centigrade). Chapter 5-24

The COLUMNS of a TABLE A domain has a name, data type, and a format. Consider the Hire_date column of the Employee table: The domain name is denoted dom(Hire_date) The domain data type is, say, character string The domain format is mm/dd/yyyy

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), and BIT, and DATE (see p. 212-213) Chapter 5-25

The COLUMNS of a TABLE A domain has a name, data type, and a format. Consider the Hire_date column of the Employee table: The domain name is denoted dom(Hire_date) The domain data type is, say, character string The 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). Chapter 5-26

The COLUMNS of a TABLE  A domain is a set of atomic values.  Atomic means that each value is indivisible as far as the relational model is concerned Employee

ENum 204 1917

EName R. B. Jones M. R. Brent

Dept E21 E21

Hire_date 11/01/1999 10/10/2003

Now, there you go again. You mean I can't search on a hire date of 1999 or find all Brents? Chapter 5-27

The COLUMNS of a TABLE We are discussing the definitions of the relational theory and atomic attribute values. This question goes to database operation (practice). 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. Employee

ENum 204 1917

EName R. B. Jones M. R. Brent

Dept E21 E21

Hire_date 11/01/1999 10/10/2003

Note: Using SQL, you can do what "angry man" wants. Chapter 5-28

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)-ddddddd where each d is a decimal digit. E.g., Dates have various formats such as monthname, date, year or yyyy-mmdd, 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 “Invoicedate” and “Payment-date”. p. 127 Chapter 5-29

Notes

*

Write the answer to Question 5.1 (p 144) here: domain attribute n-tuple degree of a relation set

Chapter 5-30

I can't bear to watch

Chapter 5-31

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 Chapter 5-32

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. Chapter 5-33

Let's see if I've got that. A populated table is created from the domain values of each attribute. ZZZzzz.

Chapter 5-34

DEFINITION SUMMARY Informal Terms

Formal Terms

Table Column Row Values in a column Table Definition Populated Table

Relation Attribute/Domain Tuple Domain Schema of a Relation Extension Chapter 5-35

Tell them we're working on populating our table. Waiter, more Java and croissants please.

Chapter 5-36

To reiterate...

Chapter 5-37

Figure 5.1 The attributes and tuples of a relation STUDENT.

Write down five things you know about this table:

Chapter 5-38

**

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= 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 Chapter 5-39

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.

Chapter 5-40

Figure 5.2 The relation STUDENT from Figure 5.1 with a different order of tuples

Figure 5.1

Chapter 5-41

Figure 5.3 Two identical tuples when the order of attributes and values is not part of relation definition.

Chapter 5-42

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.

Chapter 5-43

Notes Write the answer to Question 5.2 (p 144) here: Why are the tuples in a relation not ordered?

Write the answer to Question 5.3 (p 144) here: Why are duplicate tuples not allowed in a relation?

Chapter 5-44

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

Chapter 5-45

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 204 1917

EName R. B. Jones M. R. Brent

Dept E21 E21

Hire_date 11/01/1999 10/10/2003

What's the superkey of Employee? Chapter 5-46

Key Constraints Hey, what if I have a table that doesn't have a superkey? Now what? Employee

ENum 204 204

EName R. B. Jones R. B. Jones

Dept E21 E21

Hire_date 11/01/1999 11/01/1999

Chapter 5-47

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 tuple has the same value for year. Chapter 5-48

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.

Chapter 5-49

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. Chapter 5-50

Key Constraints Hey, what if I have a table that doesn't have a key? Now what? Coffees

Name Colombian FR Roast Colombian

Supplier 101 49 49

Price 7.99 8.99 7.99

Sales 9 4 9

Chapter 5-51

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? _____________

Chapter 5-52

Key Constraints Figure 5.4 The CAR relation, with two candidate keys: LicenseNumber and EngineSerialNumber

Chapter 5-53

I tell you Mike, a primary key is a minimal superkey. Simple as that. Say, where did everybody go?

Chapter 5-54

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

Chapter 5-55

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. 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}

Schema S = ... Chapter 5-57

Entity Integrity 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)

STUDENT key = ___________ COURSE key = ______________ Chapter 5-58

Entity Integrity 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)

These values are constrained

STUDENT key = ___________ COURSE key = ______________ Chapter 5-59

Notes

Chapter 5-60

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

Chapter 5-61

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. Chapter 5-62

Referential Integrity

referenced relation

referential integrity constraint

referencing relation foreign key Chapter 5-63

Referential Integrity Constraint Statement of the constraint The 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.

Chapter 5-64

Referential Integrity

referenced relation

referential integrity constraint

These values are constrained

referencing relation foreign key

Chapter 5-65

Chapter 5-66

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 Chapter 5-67

Figure 5.5 Schema diagram for the COMPANY relational database schema

Chapter 5-68

Figure 5.6 One possible database state for the COMPANY database schema

p. 137 Chapter 5-69

Righttttttt...

Chapter 5-70

Figure 5.7 Referential integrity constraints displayed on the COMPANY relational database schema.

p. 139 Chapter 5-71

Righttttttt again.

Chapter 5-72

5.3 Update Operations on Relations The update operations are:  INSERT a tuple.  DELETE a tuple.  MODIFY a tuple.

Chapter 5-73

 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.

Chapter 5-74

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

Chapter 5-75

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. Chapter 5-76

Figure 5.8 The AIRLINE relational database schema.

Chapter 5-77

Notes

Chapter 5-78