The Relational Data Model, Relational Constraints, and The Relational Algebra

1. The Relational Data Model, Relational Constraints, and The Relational Algebra

2. Contents • Relational Model Concepts • Relational Constraints and Relational Database Schemas • Basic Relational Algebra Operations • Additional Relational Operations

3. Relational Model Concepts • The Relational model represents the database as a collection of relations • Relation is thought of as “table” of values, where each row represents a collection of related data values. • Table name and Column names are used to help in interpreting the meaning of the values in each row. • In the formal relational model: row – tuple, column header – attribute, table – relation, type of values that can appear in each column – domain. Domains: • A domain D is a set of atomic values. • A domain is given a name, data type and format. Units can also be given to a domain. • R (A1, A2, A3…, An) • D = dom (Ai) • R – name of the relation • n – degree of a relation

4. Example STUDENT (Name, IRD, HomePhone, Address, OfficePhone, Age, GPA) • What is the relation name? • What is the degree of the relation? • What are the attributes? • List each dom(Ai). • A relation r (or relation state) of the relation schema R(A1, A2, . . ., An), also denoted by r(R), is a set of n tuples r = {t1, t2, . . ., tk}. • Each n-tuple t is an ordered list of n values t = <v1, v2, . . ., vn>, where each value vi is an element of dom(Ai) or is a special null value. • Possible for several attributes to have the same domain. The attributes indicate different roles or interpretations for the domain.

5. Characteristics of Relations • Ordering of tuples in a relation » No • Ordering of values within a tuple » Yes • Values in the tuples » is an atomic value » composite and multivalued attributes are not allowed • How to represent multi-valued attributes? » by separate relation • How to represent composite attributes? » only by their simple component attributes. • NULL » Value unknown » May not apply » exists but not available

6. Relational Model Notations • Q, R, S denote relation names • q, r, s denote relation states • t, u, v denote tuples • STUDENT indicates the current set of tuples • STUDENT(Name, IRD, . . .) refers only to the relation schema • An attribute can be qualified with a relation name, such as R.A

7. Relational Constraints and Relational Database Schemas • Domain Constraints • Key Constraints and Constraints on Null • Relational Databases and Relational Database Schemas • Entity Integrity, Referential Integrity and Foreign Keys Domain Constraints • Domain constraints specify that the value of each attribute ‘A’ must be an atomic value from the domain. • Data types are usually associated with domains • Examples • Subrange

8. Key Constraints and Constraints on Null • No two tuples can have the same values for all their attributes. • Superkey - a set of attributes such that for any two distinct tuples t1 and t2 » t1[SK] != t2[SK] » Any set of attributes satisfying the above is a superkey • The superkey specifies a uniqueness constraint on the tuples • A key K of a relation schema R is a superkey of R with the additional property that removing any of the attributes from K leaves a set of attributes that is not a superkey. • A relation schema may have more than one key. Each of the keys is called a candidate key. • Typically one candidate key is designated the primary key.

9. Relational Databases and Relational Database Schemas

10. Entity Integrity • The primary key is used to identify individual tuples. • The entity integrity constraint states that no primary key value can be null. • Key constraints and entity constraints are specified on individual relations. Referential Integrity • The referential integrity constraint is specified between two relations. It is used to maintain consistency among tuples of the two relations. • Informally, it states that a tuple in one relation that refers to another relation must refer to an existing tuple in that relation. • Example

11. Foreign Keys • A set of attributes FK in relation schema R1 is a foreign key of R1 that references relation R2 if it satisfies these conditions: » The attributes of FK have the same domain(s) as the primary key attributes PK of R2 » A value of FK in a tuple t1 of the current state r1(R1) either occurs as a value of PK for some tuple t2 in the current state r2(R2) or is null. • The attributes FK are said to refer to the relation R2. • From (2), we have t1[FK] = t2[PK].

13. Basic Relational Algebra Operations • Need a set of operations • Relational algebra • Retrieval can be from one relation or more than one relation • Result is a relation • Relational algebra expression – sequence of relational algebra operations • Operations divided into two groups » Set operations from mathematical set theory: UNION, INTERSECTION, SET DIFFERENCE and CARTESIAN PRODUCT. » Operations developed for relational databases: SELECT, PROJECT, JOIN SELECT: • σ<selection condition>(R) <attr name> <comparison op> <constant value> <attr name> <comparison op> <attr name> • σDNO =4(EMPLOYEE) • σSALARY >30000(EMPLOYEE) • σ(DNO =4 AND SALARY>25000) OR DNO=5 AND SALARY>30000)(EMPLOYEE)

14. SELECT is commutative σ<cond1>(σ<cond2>(R)) = σ<cond2>(σ<cond1>(R)) • A cascade of SELECTs can be combined into a single SELECT σ<cond1>(σ<cond2>(. . .(σ<condn>(R)) = σ<cond1> AND <cond2> AND . . AND <condn>(R) PROJECT: • Selects some attributes from a relation • Π<attribute list>(R) • The result is a relation having only the attributes in the attribute list. • Order of attributes is the <attribute list> order. • The degree of the relation is the number of attributes in the list. • Duplicates are removed. • Π<list1>(Π<list2>(R)) = Π<list1>(R) » As long as <list2> contains the attributes in <list1> • Commutativity does not hold for PROJECT. • Example: ΠLNAME, FNAME, SALARY(EMPLOYEE) ΠSEX, SALARY(EMPLOYEE)

15. Sequences of Operations • Operations can be » Nested » Applied one (or a few) at a time, creating intermediate results • Example » ΠFNAME, LNAME, SALARY (σDNO=5(EMPLOYEE)) or » DEP5_EMPS ← σDNO=5(EMPLOYEE) » RESULT ← ΠFNAME, LNAME, SALARY(DEP5_EMPS) RENAME: • ρS (R) • ρ(B1, B2, . . ., Bn) (R) • ρS(B1, B2, . . ., Bn) (R) • ρ (rho) – RENAME Operator • S is the new relation name. • B1, B2, . . ., Bn are the new attribute names.

16. Set Theoretic Operations • UNION » R ∪ S • Includes all tuples that are in R or in S or in both • INTERSECTION » R ∩ S • Includes every tuple that is simultaneously in both R and S • SET DIFFERENCE » R - S » Includes all tuples that are in R but not is S • UNION and INTERSECTION are commutative » R ∪ S = S ∪ R » R ∩ S = S ∩ R • UNION and INTERSECTION are associative » R ∪ (S ∪ T) = (R ∪ S) ∪ T » R ∩ (S ∩ T) = (R ∩ S) ∩ T • SET DIFFERENCE is not commutative » R - S != S - R • Must be union compatible » Have the same degree n » dom(Ai) = dom(Bi) for all i

17. DEPT3_EMPS  σDNO=5(EMPLOYEE) RESULT1  ΠSSN(DEP5_EMPS) RESULT2  ΠSUPERSSN(DEP5_EMPS) RESULT  RESULT1 U RESULT2

19. CARTESIAN PRODUCT • Also called CROSS PRODUCT • R X S • R(A1, A2, . . ., An) X S(B1, B2, . . ., Bm) = Q(A1, A2, . . ., An, B1, B2, . . ., Bm) • Attributes are in the order given above. • Q has one tuple for each combination of tuples in R and S. • n + m attributes • If R has x tuples and S has y tuples » x * y tuples • Meaningless unless used with other operations

20. Example: Retrieve for each female employee a list of the names of her dependents.

21. JOIN • Used to combine related tuples from two relations into single tuples • Denoted by R <join condition>S • Retrieve the name of the manager of each department.

22. Example