CSBP 430 Database Systems Chapter 7 The Relational

CSBP 430 – Database Systems Chapter 7 - The Relational Data Model Elarbi Badidi College of Information Technology United Arab Emirates University ebadidi@uaeu. ac. ae 1

In this chapter, you will learn: Relational Model Concepts Characteristics of Relations Relational Integrity Constraints Key Constraints Entity Integrity Constraints Referential Integrity Constraints Update Operations on Relations Relational Algebra Operations SELECT and PROJECT Set Operations JOIN Operations Additional Relational Operations 2

BASIS OF THE MODEL The relational Model of Data is based on the concept of a Relation. A Relation is a mathematical concept based on the ideas of sets. The strength of the relational approach to data management comes from the formal foundation provided by theory of relations. 3

INFORMAL DEFINITIONS RELATION: A table of values A relation may be thought of as a set of rows. A relation may alternately be though of as a set of columns. Each row of the relation may be given an identifier. Each column typically is called by its column name or column header or attribute name. 4

FORMAL DEFINITIONS (1) A Relation may be defined in multiple ways. The Schema of a Relation: R (A 1, A 2, . . . An) Relation R is defined over attributes A 1, A 2, . . . An For Example – CUSTOMER (Cust-id, Cust-name, Address, Phone#) Here, CUSTOMER is a relation defined over the four attributes Cust-id, Cust-name, Address, Phone#, each of which has a domain or a set of valid values. For example, the domain of Cust-id is 6 digit numbers. 5

FORMAL DEFINITIONS (2) A tuple is an ordered set of values Each value is derived from an appropriate domain. Each row in the CUSTOMER table may be called as a tuple in the table and would consist of four values. <632895, "John Smith", "101 Main St. Atlanta, GA 30332", "(404) 894 -2000"> is a tuple belonging to the CUSTOMER relation. A relation may be regarded as a set of tuples (rows). Columns in a table are also called as attributes of the relation. 6

FORMAL DEFINITIONS (3) 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 Cust-name is defined over the domain of strings of 25 characters. The role these strings play in the CUSTOMER relation is that of the name of customers. Formally, Given R(A 1, A 2, . . , An) r(R) subset-of dom (A 1) X dom (A 2) 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 Let S 1 = {0, 1} Let S 2 = {a, b, c} Let R subset-of S 1 X S 2 for example: r(R) = {<0, a> , <0, b> , <1, c> } 7

DEFINITION SUMMARY Informal Terms Table Column Row Values in a column Table Definition Populated Table Formal Terms Relation Attribute/Domain Tuple Domain Schema of Relation Extension Notes: Whereas languages like SQL use the informal terms of TABLE (e. g. CREATE TABLE), COLUMN (e. g. SYSCOLUMN variable), the relational database textbooks present the model and operations on it using the formal terms. 8

9

Characteristics of Relations Ordering of tuples in a relation r(R): The tuples are not considered to be ordered, even though they appear to be 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(A 1, A 2, . . . , An) and the values in t=<v 1, v 2, . . . , 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. 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. 10

11

Relational Integrity Constraints are conditions that must hold on all valid relation instances. There are four main types of constraints: Domain constraints Key constraints, Entity integrity constraints, and Referential integrity constraints 12

Domain constraints Domains constraints specify that the value of each attribute A must be an atomic value from the domaine dom(A). 13

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 t 1 and t 2 in r(R), t 1[SK] # t 2[SK]. 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#, Serial. No, Make, Model, Year) has two keys Key 1 = {State, Reg#}, Key 2 = {Serial. No}, which are also superkeys. {Serial. No, 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. 14

15

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 = {R 1, R 2, . . . , 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. 16

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 R 1 have attributes FK (called foreign key attributes) that reference the primary key attributes PK of the referenced relation R 2. A tuple t 1 in R 1 is said to reference a tuple t 2 in R 2 if t 1[FK] = t 2[PK]. A referential integrity constraint can be displayed in a relational database schema as a directed arc from R 1. FK to R 2. 17

18

19

20

Update Operations on Relations INSERT a tuple. DELETE a tuple. MODIFY a tuple. 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. 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 21

The Relational Algebra Operations to manipulate relations. Used to specify retrieval requests (queries). Query result is in the form of a relation. Relational Operations: SELECT σ and PROJECT operations. Set operations: These include UNION U, INTERSECTION ||, DIFFERENCE -, CARTESIAN PRODUCT X. JOIN operations . Other relational operations: DIVISION, OUTER JOIN, AGGREGATE FUNCTIONS. σ(sigma) (pi) 22

SELECT σ and PROJECT operations SELECT operation (denoted by σ ): Selects the tuples (rows) from a relation R that satisfy a certain selection condition c Form of the operation: σ c(R) The condition c is an arbitrary Boolean expression on the attributes of R Resulting relation has the same attributes as R Resulting relation includes each tuple in r(R) whose attribute values satisfy the condition c Examples: σDNO=4(EMPLOYEE) σSALARY>30000(EMPLOYEE) σ(DNO=4 AND SALARY>25000) OR DNO=5(EMPLOYEE) 23

Select Yields a subset of rows based on specified criterion 24

PROJECT operation (denoted by ): Keeps only certain attributes (columns) from a relation R specified in an attribute list L Form of operation: L(R) Resulting relation has only those attributes of R specified in L Example: FNAME, LNAME, SALARY(EMPLOYEE) The PROJECT operation eliminates duplicate tuples in the resulting relation so that it remains a mathematical set (no duplicate elements) Example: SEX, SALARY(EMPLOYEE) If several male employees have salary 30000, only a single tuple <M, 30000> is kept in the resulting relation. Duplicate tuples are eliminated by the operation. 25

Project Yields all values for selected attributes 26

27

Sequences of operations Several operations can be combined to form a relational algebra expression (query) Example: Retrieve the names and salaries of employees who work in department 4: FNAME, LNAME, SALARY (σDNO=4(EMPLOYEE) ) Alternatively, we specify explicit intermediate relations for each step: DEPT 4_EMPS σDNO=4(EMPLOYEE) R FNAME, LNAME, SALARY(DEPT 4_EMPS) Attributes can optionally be renamed in the resulting left-hand-side relation (this may be required for some operations that will be presented later): DEPT 4_EMPS σDNO=4(EMPLOYEE) R(FIRSTNAME, LASTNAME, SALARY) <- FNAME, LNAME, SALARY(DEPT 4_EMPS) 28

29

Set Operations Binary operations from mathematical set theory: UNION: R 1 U R 2, INTERSECTION: R 1 | | R 2, SET DIFFERENCE: R 1 - R 2, CARTESIAN PRODUCT: R 1 X R 2. For U, ||, -, the operand relations R 1(A 1, A 2, . . . , An) and R 2(B 1, B 2, . . . , Bn) must have the same number of attributes, and the domains of corresponding attributes must be compatible; that is, dom(Ai)=dom(Bi) for i=1, 2, . . . , n. This condition is called union compatibility. The resulting relation for U, ||, or - has the same attribute names as the first operand relation R 1 (by convention). 30

Union Combines all rows 31

Intersect Yields rows that appear in both tables 32

Difference Yields rows not found in other tables 33

34

CARTESIAN PRODUCT R(A 1, A 2, . . . , Am, B 1, B 2, . . . , Bn) <R 1(A 1, A 2, . . . , Am) X R 2 (B 1, B 2, . . . , Bn) A tuple t exists in R for each combination of tuples t 1 from R 1 and t 2 from R 2 such that: t[A 1, A 2, . . . , Am]=t 1 and t[B 1, B 2, . . . , Bn]=t 2 If R 1 has n 1 tuples and R 2 has n 2 tuples, then R will have n 1*n 2 tuples. CARTESIAN PRODUCT is a meaningless operation on its own. It can combine related tuples from two relations if followed by the appropriate SELECT operation. Example: Combine each DEPARTMENT tuple with the EMPLOYEE tuple of the manager. DEP_EMP DEPARTMENT X EMPLOYEE DEPT_MANAGER σMGRSSN=SSN(DEP_EMP) 35

Product Yields all possible pairs from two tables 36

37

Join Information from two or more tables is combined 38

JOIN Operations THETA JOIN: Similar to a CARTESIAN PRODUCT followed by a SELECT. The condition c is called a join condition. R(A 1, A 2, . . . , Am, B 1, B 2, . . . , Bn) R 1(A 1, A 2, . . . , Am) c R 2 (B 1, B 2, . . . , Bn) c is of the form: (Ai Bj) AND. . . AND (Ah Bk); 1<i, h<m, 1<j, k<n is one of the following operators: =, >, <, , , EQUIJOIN: The join condition c includes one or more equality comparisons involving attributes from R 1 and R 2. That is, c is of the form: (Ai = Bj) AND. . . AND (Ah = Bk); 1<i, h<m, 1<j, k<n In the above EQUIJOIN operation: Ai, . . . , Ah are called the join attributes of R 1 Bj, . . . , Bk are called the join attributes of R 2 Example of using EQUIJOIN: Retrieve each DEPARTMENT's name and its manager's name: T DEPARTMENT MGRSSN=SSN EMPLOYEE RESULT DNAME, FNAME, LNAME(T) 39

NATURAL JOIN (*) In an EQUIJOIN R R 1 c R 2, the join attribute of R 2 appear redundantly in the result relation R. In a NATURAL JOIN, the redundant join attributes of R 2 are eliminated from R. The equality condition is implied and need not be specified. R R 1 *(join attributes of R 1), (join attributes of R 2) R 2 Example: Retrieve each EMPLOYEE's name and the name of the DEPARTMENT he/she works for: T EMPLOYEE *(DNO), (DNUMBER) DEPARTMENT RESULT FNAME, LNAME, DNAME(T) If the join attributes have the same names in both relations, they need not be specified and we can write R R 1 * R 2. Example: Retrieve each EMPLOYEE's name and the name of his/her SUPERVISOR: SUPERVISOR(SUPERSSN, SFN, SLN) SSN, FNAME, LNAME(EMPLOYEE) T EMPLOYEE * SUPERVISOR RESULT FNAME, LNAME, SFN, SLN(T) 40

41

Note: In the original definition of NATURAL JOIN, the join attributes were required to have the same names in both relations. There can be a more than one set of join attributes with a different meaning between the same two relations. For example: JOIN ATTRIBUTES RELATIONSHIP EMPLOYEE. SSN= EMPLOYEE manages DEPARTMENT. MGRSSN the DEPARTMENT EMPLOYEE. DNO= EMPLOYEE works for DEPARTMENT. DNUMBER the DEPARTMENT Example: Retrieve each EMPLOYEE's name and the name of the DEPARTMENT he/she works for: T EMPLOYEE DNO=DNUMBERDEPARTMENT RESULT FNAME, LNAME, DNAME(T) 42

A relation can have a set of join attributes to join it with itself : JOIN ATTRIBUTES RELATIONSHIP EMPLOYEE(1). SUPERSSN= EMPLOYEE(2) supervises EMPLOYEE(2). SSN EMPLOYEE(1) One can think of this as joining two distinct copies of the relation, although only one relation actually exists In this case, renaming can be useful Example: Retrieve each EMPLOYEE's name and the name of his/her SUPERVISOR: SUPERVISOR(SSSN, SFN, SLN) SSN, FNAME, LNAME(EMPLOYEE) T EMPLOYEE SUPERSSN=SSSNSUPERVISOR RESULT FNAME, LNAME, SFN, SLN(T) 43

Complete Set Operations of Relational Algebra All the operations discussed so far can be described as a sequence of only the operations SELECT, PROJECT, UNION, SET DIFFERENCE, and CARTESIAN PRODUCT. Hence, the set {σ, , U, - , X } is called a complete set of relational algebra operations. Any query language equivalent to these operations is called relationally complete. For database applications, additional operations are needed that were not part of the original relational algebra. These include: Aggregate functions and grouping. OUTER JOIN and OUTER UNION. 44

Additional Relational Operations AGGREGATE FUNCTIONS Functions such as SUM, COUNT, AVERAGE, MIN, MAX are often applied to sets of values or sets of tuples in database applications <grouping attributes> F <function list> (R) The grouping attributes are optional Example 1: Retrieve the average salary of all employees (no grouping needed): R(AVGSAL) F AVERAGE SALARY (EMPLOYEE) Example 2: For each department, retrieve the department number, the number of employees, and the average salary (in the department): R(DNO, NUMEMPS, AVGSAL) DNO, F COUNT SSN, AVERAGE SALARY (EMPLOYEE) DNO is called the grouping attribute in the above example 45

46

OUTER JOIN In a regular EQUIJOIN or NATURAL JOIN operation, tuples in R 1 or R 2 that do not have matching tuples in the other relation do not appear in the result Some queries require all tuples in R 1 (or R 2 or both) to appear in the result When no matching tuples are found, nulls are placed for the missing attributes LEFT OUTER JOIN: R 1 X R 2 lets every tuple in R 1 appear in the result RIGHT OUTER JOIN: R 1 X R 2 lets every tuple in R 2 appear in the result FULL OUTER JOIN: R 1 X R 2 lets every tuple in R 1 or R 2 appear in the result 47

48