Chapter 5 Relational Model Concepts

1. Chapter 5 Relational Model Concepts Dr. Bernard Chen Ph.D. University of Central Arkansas

2. Relational Model Concepts • A Relation is a mathematical concept based on the ideas of sets • The model was first proposed by Dr. E.F. Codd of IBM Research 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 Dr. Codd the coveted ACM Turing Award

3. Outline • Relational Model Concepts • Domains, Attributes, and Relations • Characteristics of Relations • Relational Model Constrains and Relational Database Schemas • Update Operations, Transactions, and Dealing with Constrain Violations

4. Relational Model Concepts • The relational model represents the database as a collection of relations • Each relation resembles a table of values • When a relation is thought of as a table of values, each row in the table represents a collection of related data values

5. Formal Terminology • A row is called a tuple • A column header is called an attribute • The table is called relation

6. Domain • A Domain D is a set of atomic values. • Atomic means that each value in the domain is indivisible as far as the relational model is concerned • It means that if we separate an atomic value, the value itself become meaningless, for example: • SSN • Local_phone_number • Names • Employee_ages

7. Relation Schema • Relation Schema R, denoted by R(A1, A2,…, An), is made up of relation name R and a list of attributes A1, A2, …,An • Each attribute Ai is the name of a role played by some domain D in the relation schema R. • D is called the domain of Ai and is denoted by dom(Ai) • R is called the name of the relation • The degree of a relation is the number of attributes n of its relation schema

8. Formal Definitions • Formally, • Given R(A1, A2, .........., An) • r(R)  dom (A1) X dom (A2) X ....X dom(An) • R(A1, A2, …, An) is the schema of the relation • R is the name of the relation • A1, A2, …, An are the attributes of the relation

9. Formal Definitions • Let R(A1, A2) be a relation schema: • Let dom(A1) = {0,1} • Let dom(A2) = {a,b,c} • Then: dom(A1) X dom(A2) is all possible combinations: {<0,a> , <0,b> , <0,c>, <1,a>, <1,b>, <1,c> } • The relation state r(R)  dom(A1) X dom(A2)

10. Definition Summary

11. Outline • Relational Model Concepts • Domains, Attributes, and Relations • Characteristics of Relations • Relational Model Constrains and Relational Database Schemas • Update Operations, Transactions, and Dealing with Constrain Violations

12. Ordering • Ordering of Tuples is a Relation • a relation is defined as a set of tuples. • Mathematically, elements of a set have NO order among them • The ordering indicates first, second, ith, and last records in the file • Hence, the following two relations are identical

13. Identical Relations

14. Values in the Tuples • Each value in a tuple is an atomic value • Hence, composite and multi-valued attributes are not allowed • This model is sometimes called the flat relational model • Much of the theory behind the relational model was developed with this assumption, which is called first normal form assumption

15. Null in tuples • An important concept is that if NULL values, which are used to represent the values of attributes that may be unknown or may not apply to a tuple

16. Relational Model Notation • An attribute A can be qualified with the relation name R to which it belongs by using the dot notation R.A • For example, STUDENT.Name or STUDENT.Age

17. Outline • Relational Model Concepts • Domains, Attributes, and Relations • Characteristics of Relations • Relational Model Constrains and Relational Database Schemas • Update Operations, Transactions, and Dealing with Constrain Violations

18. Domain Constrains • Each attribute A must be an atomic value from the dom(A) • The data types associated with domains typically include standard numeric data type for integers, real numbers, Characters, Booleans, fix-length strings, time, date, money or some special data types

19. Key Constrains • A relation is defined as a set of tuples • By definition, all elements of a set are distinct • This means that no two tuples can have the same combination of values for all their attributes • Superkey: a set of attributes that no two distinct tuples in any state r of R have the same value • Every relation has at least one default superkey – the set of all its attributes

20. Key Constrains • A superkey can have redundant attributes, so a more useful concept is that of a KEY which has no redundancy • Key satisfied two constrains: • Two distinct tuple in any state of the relation cannot have identical values for the attributes in the key • It is a minimal superkey

21. Key Constrains • For example, consider STUDENT relation • The attribute set {SSN} is a key of STUDENT because no two student can have the same value for SSN • Any set of attributes that includes SSN – for example {SSN, Name, Age} – is a superkey

22. Key Constrains • In general, a relation schema may have more than one key, in this case, each of the key is called a candidate key • Example: Consider the CAR relation schema: • CAR(State, Reg#, SerialNo, Make, Model, Year) • CAR has two keys: • Key1 = {State, Reg#} • Key2 = {SerialNo} • Both are also superkeys of CAR • {SerialNo, Make} is a superkey but not a key (because it is not minimum).

23. CAR table with two candidate keys – LicenseNumber chosen as Primary Key

24. Key Constrains • If a relation has several candidate keys, one is chosen arbitrarily to be the primary key. • Example: Consider the CAR relation schema: • CAR(State, Reg#, SerialNo, Make, Model, Year) • We chose SerialNo as the primary key • The primary key value is used to uniquely identify each tuple in a relation • Provides the tuple identity • Also used to reference the tuple from another tuple • General rule: Choose as primary key the smallest of the candidate keys (in terms of size) • Not always applicable – choice is sometimes subjective

25. Key Constrains and Constrains on NULL values • Another constraint on attributes specifies whether NULL value are or are not permitted • For example, if every STUDENT tuple must have a valid, non-NULL value for the Name attribute, then Name of STUDENT is constrained to be NOT NULL

26. Relational Database Schema • Relational Database Schema: • A set S of relation schemas that belong to the same database. • S is the name of the whole database schema • S = {R1, R2, ..., Rn} • R1, R2, …, Rn are the names of the individual relation schemas within the database S • Following slide shows a COMPANY database schema with 6 relation schemas

27. COMPANY Database Schema

28. 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) • If PK has several attributes, null is not allowed in any of these attributes • Note: Other attributes of R may be constrained to disallow null values, even though they are not members of the primary key.

29. Referential Integrity Constraint • Referential Integrity Constraint is specified between two relations and is used to maintain the consistency among tuples in the two relations • Informally define the constrain: a tuple in one relation must refer to an existing tuple in that relation • For example, the Dno in EMPLOYEE gives the department number for which each employee works, this number must match the Dnumber value in DEPARTMENT

31. Referential Integrity Constraint • 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].

32. Displaying a relational database schema and its constraints • Each relation schema can be displayed as a row of attribute names • The name of the relation is written above the attribute names • The primary key attribute (or attributes) will be underlined • A foreign key (referential integrity) constraints is displayed as a directed arc (arrow) from the foreign key attributes to the referenced table • Can also point the the primary key of the referenced relation for clarity • Next slide shows the COMPANY relational schema diagram

33. Referential Integrity Constraints for COMPANY database

34. Other Types of Constraints • Semantic Integrity Constraints: • based on application semantics and cannot be expressed by the model per se • Example: “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

35. Outline • Relational Model Concepts • Domains, Attributes, and Relations • Characteristics of Relations • Relational Model Constrains and Relational Database Schemas • Update Operations, Transactions, and Dealing with Constrain Violations

36. Modification and Updates • In this section, we concentrate on the database Updates and Modification • There are threee basic operation: Insert, Delete and Modify • Insert is used to insert a new tuple or tuples in a relation • Delete is used to delete tuples • Update (or Modify) is used to change the values of some attributes

37. Modification and Updates • Insert: insert new element with specify all related attributes • Delete: delete an element by giving Relation name and key of the tuple • Modify: modify a value by giving a relation name, Key of the target tuple and attribute to modify

38. Possible violations for each operation • INSERT may violate any of the constraints: • Domain constraint: • if one of the attribute values provided for the new tuple is not of the specified attribute domain • Key constraint: • if the value of a key attribute in the new tuple already exists in another tuple in the relation • Referential integrity: • if a foreign key value in the new tuple references a primary key value that does not exist in the referenced relation • Entity integrity: • if the primary key value is null in the new tuple

39. Insert Example • Insert <‘Cecilia’, ‘F’, ‘Kolonsky’, NULL, ‘1960-04-05’, ‘6357 Windy lane,Kate,TX’, F, 28000, NULL, 4> into EMPLOYEE • Insert < ‘Cecilia’, ‘F’, ‘Kolonsky’, 999887777, ‘1960-04-05’, ‘6357 Windy lane,Kate,TX’, F, 28000, NULL, 4 > • Insert < ‘ Cecilia’, ‘F’, ‘Kolonsky’, 667788999, ‘1960-04-05’, ‘6357 Windy lane,Kate,TX’, F, 28000, NULL, 7>

41. Possible violations for each operation • DELETE may violate only referential integrity: • If the primary key value of the tuple being deleted is referenced from other tuples in the database • Can be remedied by several actions: RESTRICT, CASCADE, SET NULL • RESTRICT option: reject the deletion • CASCADE option: propagate the new primary key value into the foreign keys of the referencing tuples • SET NULL option: set the foreign keys of the referencing tuples to NULL • One of the above options must be specified during database design for each foreign key constraint

42. Delete Example • Delete the EMPLOYEE tuple with Ssn=‘999887777’ • Delete the EMPLOYEE tuple with Ssn=‘333445555’ • Delete the EORKS_ON tuple with Essn=‘999887777’ and Pno=10 (accepted)

43. Possible violations for each operation • UPDATE may violate domain constraint and NOT NULL constraint on an attribute being modified • Any of the other constraints may also be violated, depending on the attribute being updated: • Updating the primary key (PK): • Similar to a DELETE followed by an INSERT • Need to specify similar options to DELETE • Updating a foreign key (FK): • May violate referential integrity • Updating an ordinary attribute (neither PK nor FK): • Can only violate domain constraints

44. Update Example • Update the salary of EMPLOYEE tuple with Ssn=‘999887777’ to 2800 (Accept) • Update the Dno of the EMPLOYEE tuple with Ssn=‘999887777’ to 1 (Accept) • Update the Dno of the EMPLOYEE tuple with Ssn=‘999887777’ to 7 • Update the Ssn of the EMPLOYEE tuple with Ssn=‘999887777’ to ‘987654321’