5 The Relational Data Model and Relational Database

5. The Relational Data Model and Relational Database Constraints Chapter 3 Course Coordinator: Dr. Deema Alathel Lecturer: Ms. Arwa Alturki

5. 1 Relational Model Concepts

5. 1. 1 Domains, Attributes, Tuples, and Relations n Represents the database as a collection of relations ¡ n Relation = table = file Relational Model Concepts ¡ ¡ Table Relation Column header Attribute Row Tuple Columns’ sets of values Domain

5. 1. 1 Domains, Attributes, Tuples, and Relations n A relation is a table of values n A relation may be thought of as a set of rows n A relation may alternately be though of as a set of columns n Each row represents a fact that corresponds to a real-world entity or relationship n All values in a column are of the same data type

5. 1. 1 Domains, Attributes, Tuples, and Relations n Each row has a value of an item or set of items that uniquely identifies that row in the table. n Sometimes row-ids or sequential numbers are assigned to identify the rows in the table. n Each column typically is called by its column name or column header or attribute name n The relation name and the column name are used to interpret the meaning of the values in each row

5. 1. 1 Domains, Attributes, Tuples, and Relations Domain n ¡ Set of atomic values n Specifying a Domain n Choose a meaningful name for the domain Specify the data type and format (if any) Specify any additional information (if any) for interpreting the values 1. 2. 3. n Atomic means indivisible Several attributes could have the same domain ¡ Each attribute indicate a different role (or interpretation) of the domain

5. 1. 1 Domains, Attributes, Tuples, and Relations n Domain Example ¡ ¡ “USA_phone_numbers” is the set of 10 digit phone numbers valid in the U. S. The format for this domain is (ddd)-dddd where each d is a decimal digit “Employee_ages” is the set of possible ages of employees of a company; each must be a value between 15 and 80 The age is considered in years

5. 1. 1 Domains, Attributes, Tuples, and Relations n Relation Schema ¡ A description of the relation and its attributes n ¡ Denoted by R(A 1, A 2, …, An) dom(A 1) = D 1, dom(A 2) = D 2, …, dom(An) = Dn n ¡ Relation = Relation Schema + Data R is the name of the relation Ai is an attribute in the relation R Di is the domain for an attribute Ai The degree of a relation is the number of attributes n in its relation schema

5. 1. 1 Domains, Attributes, Tuples, and Relations n Relation Schema Example ¡ University students relation schema: STUDENT(Name, SSN, Home. Phone, Address, Office. Phone, Age, GPA) ¡ Degree of STUDENT is 7 ¡ dom(Name) = Names dom(SSN) = Social_security_numbers dom(Home. Phone) = USA_phone_numbers …

5. 1. 1 Domains, Attributes, Tuples, and Relations n Relation ¡ A relation r of the relation schema R (A 1, A 2, …, An) is the set of n-tuples r = {t 1, t 2, …, tm} ¡ Also known as relation state ¡ Denoted by r(R) ¡ ti = <v 1, v 2, …, vn> vi is an element (value) of dom(Ai)

5. 1. 1 Domains, Attributes, Tuples, and Relations n Relation ¡ ¡ R is considered the intension of r r is considered the extention of R R is not supposed to change frequently r should be expected to change frequently

5. 1. 1 Domains, Attributes, Tuples, and Relations n Example ¡ STUDENT relation of the UNIVERSITY database

5. 1. 2 Characteristics of Relations n Ordering of tuples in a relation r(R) ¡ n The tuples are not considered to be ordered, even though they appear to be in the tabular form Ordering of values within a tuple t ¡ The ordering of values in a tuple and the order of attributes in a relation schema R is important to maintain the correspondence between each attribute and its value

5. 1. 2 Characteristics of Relations n 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

5. 1. 3 Relational Model Notation n A relation schema R of degree n is denoted by R(A 1, A 2, …, An) n An n-tuple t in a relation r(R) is denoted by t = <v 1, v 2, …, vn> n To refer to the value vi in t for attribute Ai, use t[Ai] or t. Ai n To refer to a subtuple of values <vu, vw, …, vz> from t, use t[Au, Aw, …, Az] or t. (Au, Aw, …, Az)

5. 1. 3 Relational Model Notation n The letters Q, R, S denote relation names n The letters q, r, s denote relation states n The letters t, u, v denote tuples n The name of a relation schema, for example STUDENT, indicates the current set of tuples in that relation, i. e. the current relation state n While STUDENT(Name, SSN, …) refers to the relation schema

5. 2 Relational Model Constraints and Relational Database Schemas

5. 2 Relational Constraints and Relational Database Schemas n Constraints are conditions that must hold on all valid relation instances. There are four main types of constraints: 1. 2. 3. 4. Domain constraints Key constraints Entity integrity constraints Referential integrity constraints

5. 2. 1 Domain Constraints n Domain constraints specify that the value of each attribute A must be an atomic value from the domain dom(A) n Data types associated with domains include ¡ ¡ Integers (short, integer, long, …) Characters n n ¡ ¡ ¡ Fixed-length string Variable-length string Date Time Money A subrange of values from a data type Enumerated data type …

5. 2. 2 Key Constraints and Constraints on NULL Values n 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 n ¡ for any distinct tuples t 1 and t 2 in r(R), t 1[SK] t 2[SK] Every relation has at least one superkey which is the set of all its attributes

5. 2. 2 Key Constraints and Constraints on NULL Values n Key of R ¡ A minimal superkey ¡ A superkey K such that removal of any attribute from K results in a set of attributes that is not a superkey ¡ If a relation has multiple candidate keys, one is chosen arbitrarily to be the primary key n The primary key attributes are underlined

5. 2. 2 Key Constraints and Constraints on NULL Values n Key of R ¡ Ex. The CAR relation schema CAR(State, Reg#, Serial. No, Make, Model, Year) n n n The relation schema has two keys ¡ Key 1 = {State, Reg#}, Key 2 = {Serial. No} Both keys are also superkeys {Serial. No, Make} is a superkey but not a key

5. 2. 2 Key Constraints and Constraints on NULL Values n The primary key attributes PK of a relation schema R cannot have null values in any tuple of r(R) ¡ n Why? The primary key values are used to identify the individual tuples t[PK] null for any tuple t in r(R) Other attributes of R may be similarly constrained to disallow null values, even though they are not members of the primary key.

5. 2. 3 Integrity, Referential Integrity, and Foreign Keys n Referential Integrity ¡ Involves two relations in a database n ¡ Used to specify a relationship among tuples in two relations n ¡ 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 n ¡ The previous constraints involve only a single relation 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. PK

5. 2. 3 Integrity, Referential Integrity, and Foreign Keys n Referential Integrity

5. 2. 3 Integrity, Referential Integrity, and Foreign Keys n The referential integrity constraint states that the value in the foreign key column(s) FK of the referencing relation R 1 can be either: 1. 2. the value of an existing primary key PK in the referenced relation R 2, or null

5. 3 Update Operations, Transactions, and Dealing with Constraint Violations

5. 3 Update Operations, Transactions, and Dealing with Constraint Violations n Relational Model Operations ¡ ¡ n Retrievals Updates Basic Update Operations ¡ Insert n ¡ Delete n ¡ Used to insert a new tuple (or tuples) in a relation Used to delete tuples Update (Modify) n Used to change the values of some attributes in existing tuples

5. 3 Update Operations, Transactions, and Dealing with Constraint Violations n Retrieving information doesn’t change the database, so it cannot violate any constraints n Each time the database is updated, however, the DBMS has to ensure that no constraints are violated ¡ ¡ ¡ Inserting a new tuple could violate domain constraints, key constraints, the entity integrity constraint, or the referential integrity constraint. Deleting a tuple can violate only the referential integrity constraint Modifying a tuple can violate any of the types of constraints

5. 3 Update Operations, Transactions, and Dealing with Constraint Violations n In case of a violation, several actions can be taken ¡ Cancel the operation that causes the violation (REJECT option) ¡ Ask the user to make a correction before performing the operation ¡ 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

5. 3 Update Operations, Transactions, and Dealing with Constraint Violations n Examples ¡ Insert <‘Cecilia’, ‘F’, ‘Kolonsky’, null, ‘ 1960 -04 -05’, ‘ 676 Katy Lane, TX’, F, 28000, null, 4> into EMPLOYEE n Insertion violates entity integrity constraint (PK is null) ¡ ¡ Insert <‘Alicia’, ‘J’, ‘Zane’, ‘ 998877’, ‘ 1960 -04 -05’, ‘ 674 Windy Lane, TX’, F, 28000, ‘ 987654’, 4> into EMPLOYEE n Insertion violates key constraint (duplicate PK) ¡ ¡ Operation is rejected Insert <‘Cecilia’, ‘F’, ‘Kolonsky’, ‘ 677678’, ‘ 1960 -04 -05’, ‘ 676 Katy Lane, TX’, F, 28000, ‘ 987654’, 7> into EMPLOYEE n Insertion violates referential integrity constraint (FK references a PK that does not exist) ¡ Operation is rejected

5. 3 Update Operations, Transactions, and Dealing with Constraint Violations n Examples ¡ Delete the WORKS_ON tuple with ESSN = ‘ 998877’ and PNO = 10 n Operation does not violate any constraint ¡ ¡ Deletion is acceptable Delete the EMPLOYEE tuple with SSN = ‘ 998877’ n Operation violates referential integrity constraint because there is a tuple in WORK_ON that references this tuple ¡ Deletion is unacceptable

5. 3 Update Operations, Transactions, and Dealing with Constraint Violations n Examples ¡ Update the Salary of the EMPLOYEE tuple with SSN = ‘ 998877’ to 27000 n Operation does not violate any constraint ¡ ¡ Update the DNO of the EMPLOYEE tuple with SSN = ‘ 998877’ to 1 n Update violates referential integrity constraint ¡ ¡ Operation is acceptable Operation is unacceptable Update the SSN of the EMPLOYEE tuple with SSN = ‘ 998877’ to ‘ 848484’ n Update violates key constraints and referential integrity constraint ¡ Operation is unacceptable

Any Questions?