Chapter 5 The Relational Data Model and Relational

Chapter 5 The Relational Data Model and Relational Database Constraints Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe

Chapter Outline n n n Relational Model Concepts Relational Model Constraints and Relational Database Schemas Update Operations and Dealing with Constraint Violations Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 2

Datatbase Modeling Clients Inputs ER/EER Model Relational Database System Client App 1 Client App 2 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 3

Database System: A Different View Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 4

Relational Model Concepts n n n The strength of the relational approach to data management comes from the formal foundation provided by theory of relations We review the essentials of the formal relational model in this chapter In practice, there is a standard model based on SQL – this is described in Chapters 8 and 9 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 5

Informal Definitions n Informally, a relation looks like a table of values. n A relation contains a set of rows, or tuples. n The relational model does not distinguish entities from relationships. n n It sees only “tables of facts. ” Each column is an attribute that gives an indication of the meaning of the data items in that column Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 6

Example of a Relation Schema (description/definition): STUDENT (Name, SSN, Home_phone, Addr, Office_phone, Age, GPA) State (present set of data): Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 7

Relation Schema and State n The Schema is a description of a Relation: n n n Denoted by R(A 1, A 2, . . . An) R is the name of the relation The attributes of the relation are A 1, A 2, . . . , An The relation state r(R) refers to the present set of data/tuples in the relation. Example: CUSTOMER (Cust-id, Cust-name, Address, Phone#) n n n CUSTOMER is the relation name Defined over the four attributes: Cust-id, Cust-name, Address, Phone# A CUSTOMER state may include 100 CUSTOMERS; another 250 CUSTOMERs. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 8

Relations States Are Sets n All tuples in a relation state r(R) form a set. n n n By definition, there cannot be duplicates, or identical tuples, in a set. By definition, set elements (tuples) are not ordered, even though tuples frequently appear to be in the tabular form. Ordering of attributes however is important n 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. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 9

Same state as previous Figure (but with different order of tuples) Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 10

Characteristics Of Relations n Values in a tuple: n n n A special null value is used to represent values that are not available. 3 reasons in reality: n n All values are considered atomic (indivisible). No composite or multi-valued attributes. Unknown Inapplicable Withheld IMPORTANT: NULL Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 11

Domain n All legal values of an attribute is called its domain. n A domain has a logical definition: n n Example: “USA_phone_numbers” are the set of 10 digit phone numbers valid in the U. S. A domain also has a data-type or a format defined for it. n n The USA_phone_numbers may have a format: (ddd)ddd-dddd where each d is a decimal digit. Dates have various formats such as year, month, date formatted as yyyy-mm-dd, or as dd mm, yyyy etc. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 12

Definition Summary Informal Terms Formal Terms Table Relation Column Header Attribute All possible Column Values Row Domain Table Definition Schema of a Relation Populated Table State of the Relation Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Tuple 13

Relational Database Schema n Relational Database Schema: n n n A set S of relation schemas that belong to the same database. S is the name of the whole database schema S = {R 1, R 2, . . . , Rn} R 1, R 2, …, Rn are the names of the individual relation schemas within the database S Following slide shows a COMPANY database schema with 6 relation schemas Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 14

COMPANY Database Schema Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 15

Populated database state n n Each relation will have many tuples in its current relation state The relational database state is a union of all the individual relation states Whenever the database is changed, a new state arises Basic operations for changing the database: n n n INSERT a new tuple in a relation DELETE an existing tuple from a relation MODIFY an attribute of an existing tuple Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 16

Populated database state for COMPANY Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 17

Relational Integrity Constraints n n Constraints are conditions that must hold on all valid relation states. There are three main types of constraints in the relational model: n n Key constraints Entity integrity constraints Referential integrity constraints Domain constraint n Every value in a tuple must be from the domain of its attribute (or it could be null, if allowed for that attribute) Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 18

Domain Constraints n n The value of an attribute is limited to its domain. A domain can impose rules on both formats and valid value ranges. n n n A salary value cannot be negative 2006 -02 -15 is an incorrect address. 2006 -02 -29 is an incorrect date. Something must be wrong with a present employee born in 1800 -01 -01. An employee’s name cannot be NULL. n This is called the NOT NULL constraint. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 19

Key Constraints n n Superkey of R is a set of attributes SK of R with the following condition: for any distinct tuples t 1 and t 2 in r(R), t 1[SK] t 2[SK] Key of R: A "minimal" superkey n That is, a key is a superkey K such that removal of any attribute from K will lose the uniqueness property n n Sometimes sequential numbers are created as keys n Called artificial key or surrogate key Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 20

Key Constraints (continued) n Example: Consider the CAR relation schema: n n CAR(State, Reg#, Serial. No, Make, Model, Year) CAR has two keys: n n n Key 1 = {State, Reg#} Key 2 = {Serial. No} Both are also superkeys of CAR {Serial. No, Make} is a superkey but not a key. In general: n n n Any key is a superkey (but not vice versa) Any set of attributes that includes a key is a superkey A minimal superkey is also a key Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 21

Key Constraints (continued) n If a relation has several candidate keys, one is chosen to be the primary key. n n Example: Consider the CAR relation schema: n n n The primary key attributes are underlined. CAR(State, Reg#, Serial. No, Make, Model, Year) We chose Serial. No as the primary key The primary key is used to reference the tuple from another tuple n n General rule: choose as primary key the smallest of the candidate keys (in terms of space) choice is sometimes subjective Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 22

CAR table with two candidate keys – License. Number chosen as Primary Key Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 23

Entity Integrity n The primary key attributes PK of each relation schema R in S cannot have null values n n Because PK values are used to identify the individual tuples. If PK has several attributes, null is not allowed in any of these attributes Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 24

Foreign Keys n Tuples in a referencing relation R 1 use a set of attributes FK (called foreign key) that reference the primary key attributes PK of the referenced relation R 2. n n If a person owns a car, whose primary key is Serial. No, then the PERSON relation must have a Car. Serial. No attribute to identify the car. FK={Car. Serial. No}. If the primary key of CAR is {state, Reg#}, then PERSON must have attributes Car. State and Car. Reg#. FK={Car. State, Care. Reg#}. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 25

Referential Integrity (or foreign key) Constraint n Statement of the constraint n The value in FK of the referencing relation R 1 can be either: n n n (1) a value of an existing primary key value of a corresponding primary key PK in the referenced relation R 2, or (2) a null. In case (2), the FK in R 1 should not be a part of its own primary key. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 26

Referential Integrity Constraints for COMPANY database Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 27

Other Types of Constraints n Application Semantic Integrity Constraints: n n n Also called business rules or real world constraints May not be expressed by a give database model Example: “the max. no. of hours per employee for all projects he or she works on is 56 hrs per week” Example: A department cannot have more than 100 employees unless it controls more than 5 projects. SQL-99 allows Triggers and Assertions to express for some of these n Heed the performance issue Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 28

Remedies to Constraint Violations n In case of integrity violation, several actions can be taken: n n Cancel the operation that causes the violation (RESTRICT or REJECT option) Trigger additional updates so the violation is corrected (CASCADE option, SET NULL option) Execute a user-specified error-correction routine Perform the operation but inform the user of the violation Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 29

Possible Violations for INSERT n Domain constraint: n n Key constraint: n n if the value of a key attribute in the new tuple already exists in another tuple in the relation Referential integrity: n n if one of the attribute values provided for the new tuple is not of the specified attribute domain if a foreign key value in the new tuple references a primary key value that does not exist in the referenced relation Entity integrity: n if the primary key value is null in the new tuple Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 30

Possible Violations for DELETE n Referential constraints n n Options of remedies: n n If the primary key value of the tuple being deleted is referenced from other tuples in the database RESTRICT: reject the deletion CASCADE: propagate the new primary key value into the foreign keys of the referencing tuples SET NULL: 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. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 31

Possible Violations for UPDATE n Constraint violations depending on the attribute being updated: n Updating the primary key (PK): n n n Updating a foreign key (FK): n n Similar to a DELETE followed by an INSERT Need to specify similar options to DELETE May violate referential integrity Updating an ordinary attribute (neither PK nor FK): n Can only violate domain and business rules constraints Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 32

Exercise n Give potential constraint violations when updating the info of a departement. n n Dname Dnumber Mgr_SSN Mgr_start_date Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 33

Exercise n Actions and consequences to avoid constraint violations when deleting a department n REJECT: n CASCADE: n SET NULL: Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 34