The Relational Data Model and Relational Database Constraints

1. The Relational Data Model and Relational Database Constraints

2. Outline • Relational Model Concepts • Characteristics of Relations • Relational Model Constraints • Key Constraints • Entity Integrity Constraints • Referential Integrity Constraints • Update Operations on Relations .

3. Relational Model Concepts • 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 the theory of relations. • We review the essentials of the relational approach in this chapter.

4. Formal Definitions • Domain • A set of atomic values • Given with a name, data type, and format • Attribute • The name of a role played by some domain D • D is called the domain of Ai: dom(Ai)

5. Formal Definitions • Relation schema • Defined over attributes A1, A2, …, An • Denoted R(A1,A2, …,An) • n is the degree (arity) of the relation • N-tuple • An ordered list of n values • Denoted t = <v1,v2,…,vn> • vi is an element of some domain Ai, denoted t[Ai]

6. Formal Definitions • Relation • A relation (or relation state) r of R(A1,A2,…,An) is a set of n-tuples r = {t1,t2,…,tm}. • Each value vi is an element of dom(Ai). • Denoted r(R)

7. Example CUSTOMER (Cust-id, Cust-name, Address, Phone#) • CUSTOMER is a relation defined over the four attributes Cust-id, Cust-name, Address, Phone#, • Each attribute has a domain or a set of valid values. E.g., the domain of Cust-id is 6 digit numbers. • <632895, "John Smith", "101 Main St. Atlanta, GA 30332", "(404) 894-2000">, for instance, is a tuple belonging to the CUSTOMER relation. • A relation (table) may be regarded as a set of tuples (rows). • Attributes of the relation correspond to columns in the table.

8. Figure 5.1 The attributes and tuples of a relation STUDENT.

9. Mathematical Relation • Relation r(R) where R(A1, A2, ..., An) • Mathematical relation of degree n on the domains dom(A1), dom(A2), …, dom(An). • A subset of the Cartesian product of the domains r(R)  dom(A1) × dom(A2) × .... × dom(An) • The Cartesian product specifies all possible combinations of values from the underlying domains: |dom(A1)| × |dom(A2)| × .... × |dom(An)| • r(R) is a set of valid tuples.

10. Example Let S1 = {0,1}, S2 = {a,b,c}. Let r(R)  S1 × S2 For example: r(R) = {<0.a>, <0,b>, <1,c>} • r(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

11. Definition Summary Informal TermsFormal Terms Table Relation Column Attribute/Domain Row Tuple Values in a column Domain Table Definition Relation Schema Populated Table Extension

12. Characteristics of Relations • Ordering of tuples • Tuple ordering is not part of a relation definition • Tuples are not considered to be ordered, even though they appear to be in the tabular form. • Logical orders can be specified, only to facilitate functions such as searching. • Ordering of attributes • Attributes in R(A1, A2, ..., An) and the values in t=<v1,v2, ..., vn> are considered to be ordered. • Alternative definition of relation is possible

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

14. Characteristics of Relations • Values in a tuple: • All values are considered atomic (indivisible). • Multivalued attributes must be represented by separate relations. • Composite attributes are represented only by their simple component attributes. • A special null value is used to represent values that are unknown or inapplicable to certain tuples.

15. Characteristics of Relations • Interpretation of a relation • Relations represent facts about entities and relationships • How to converse EER constructs to relations • Notation • Relation name: Q, R, S • Relation states: q, r, s • Tuples: t, u, v • t[Ai] = vi (the value of attribute Ai for tuple t). • t[Au,Av,...,Aw] refers to the subtuple of t containing the values of attributes Au, Av, ..., Aw, respectively.

16. Relational Model Constraints • Categories • Inherent model-based constraints • e.g. no duplicate tuples • Schema-based constraints • Expressed in the schema • Application-based constraints • Must be checked within application programs • Data dependencies • Used in the design of a relational database

17. Relational Model Constraints • Constraints • Conditions that must hold on all valid relation instances. • Types of constraints: • Domain constraints • Key constraints • Entity integrity constraints • Referential integrity constraints

18. Domain Constraints • Domain constraints • The value of each attribute A must be an atomic value from dom(A) • Typical data types: integers, real numbers, characters, booleans, strings

19. Key Constraints • Superkey of R: • A set of attributes SK of R, that specifies a uniqueness constraint • 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]. • 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.

20. Key Constraints 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 severalcandidate keys, one is chosen arbitrarily to be the primary key. The primary key attributes are underlined.

21. Figure 5.4 The CAR relation with two candidate keys: LicenseNumber and EngineSerialNumber.

22. 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)

23. Figure 5.5 Schema diagram for the COMPANY relational database schema; the primary keys are underlined.

24. Figure 5.6 One possible relational database state corresponding to the COMPANY schema.

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

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

28. Update Operations • Operations • INSERT, DELETE, MODIFY form a new relation. • Applying algebraic operators (relational algebra) • Defining the new relation (relational calculus) • Constraints • Update operations should not violate integrity constraints • 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.

29. Constraint Violations • In case of integrity violation: • 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