CSC 426 Chapter 03, Fall 2011

Chapter 3 The basic relational model  The Relational Data Model and Relational Database Constraints  Relational Model Constraints and Relational Database Schemas  Update Operations, Transactions, and Dealing with Constraint Violations Chapter 3 Outline  Relational model ◦ First commercial implementations available in early 1980s ◦ Has been implemented in a large number of commercial system The Relational Data Model and Relational Database Constraints  Represents data as a collection of relations  Table of values ◦ Row • Represents a collection of related data values • Fact that typically corresponds to a real-world entity or relationship • Tuple ◦ Table name and column names • Interpret the meaning of the values in each row attribute Relational Model Concepts Relational Model Concepts (cont’d.)  Domain D ◦ Set of atomic values  Atomic ◦ Each value indivisible  Specifying a domain ◦ Data type specified for each domain Domains, Attributes, Tuples, and Relations RM Terminology  Relation: table with columns and rows.  Attribute: named column of a relation.  Domain: set of allowable values for one or more attributes.  Tuple: a record of a relation.  Relational Database - collection of normalized relations with distinct relation names. 7 Instances of DistributionCenter and Staff relations 8 Domains for some attributes of distributionCenter and staff relations 9 Properties of Relational Tables  Table name is distinct from all other table names in the database.  Each cell of table contains exactly one atomic (single) value.  Each column has a distinct name.  Values of a column are all from the same domain.  Each record is distinct; there are no duplicate records.  Order of columns has no significance.  Order of records has no significance, theoretically. 10 Relational Keys  Superkey ◦ A column, or a set of columns, that uniquely identifies a record within a table.  Candidate Key ◦ A superkey that contains only the minimum number of columns necessary for unique identification. ◦ In each record, values of the candidate key uniquely identify that record (uniqueness). ◦ No proper subset of the candidate key has the uniqueness property (irreducibility). 11 Relational Keys  Composite Key ◦ A key consists of more than one column  Primary Key ◦ The candidate key that is selected to identify records uniquely within table.  Alternate Keys ◦ Candidate keys that are not selected to be primary key.  Foreign Key ◦ Column, or set of columns, within one table that matches the candidate key of some (possibly the same) table. 12 Relational Integrity  Nulls ◦ Represents value for a column that is currently unknown or not applicable for record. ◦ Deals with incomplete or exceptional data. ◦ Represents the absence of a value and is not the same as zero or spaces, which are values. 13 Relational Integrity  Entity Integrity ◦ In a base table, no column of a primary key can be null.  Referential Integrity ◦ If a foreign key exists in a table, either foreign key value must match a candidate key value of some record in its home table or foreign key value must be wholly null.  Integrity Constraints ◦ Rules that define or constrain some aspect of the data used by the organization. 14  Relation schema R ◦ Denoted by R(A1, A2, ...,An) ◦ Made up of a relation name R and a list of attributes, A1, A2, ..., An  Attribute Ai ◦ Name of a role played by some domain D in the relation schema R  Degree (or arity) of a relation ◦ Number of attributes n of its relation schema Domains, Attributes, Tuples, and Relations (cont’d.)  Relation (or relation state) ◦ Set of n-tuples r = {t1, t2, ..., tm} ◦ Each n-tuple t • Ordered list of n values t =<v1, v2, ..., vn • Each value vi, 1 i ≤ ≤ n , is an element of dom(Ai) or is a special NULL value Domains, Attributes, Tuples, and Relations (cont’d.)  Cardinality ◦ Total number of values in domain  Current relation state ◦ Relation state at a given time ◦ Reflects only the valid tuples that represent a particular state of the real world  Attribute names ◦ Indicate different roles, or interpretations, for the domain Domains, Attributes, Tuples, and Relations (cont’d.)  Ordering of tuples in a relation ◦ Relation defined as a set of tuples ◦ Elements have no order among them  Ordering of values within a tuple and an alternative definition of a relation ◦ Order of attributes and values is not that important ◦ As long as correspondence between attributes and values maintained Characteristics of Relations Characteristics of Relations (cont’d.)  Relation schema R of degree n ◦ Denoted by R(A1, A2, ..., An)  Uppercase letters Q, R, S ◦ Denote relation names  Lowercase letters q, r, s ◦ Denote relation states  Letters t, u, v ◦ Denote tuples Relational Model Notation  Name of a relation schema: STUDENT ◦ Indicates the current set of tuples in that relation  Notation: STUDENT(Name, Ssn, ...) ◦ Refers only to relation schema  Attribute A can be qualified with the relation name R to which it belongs ◦ Using the dot notation R.A Relational Model Notation  n-tuple t in a relation r(R) ◦ Denoted by t = <v1, v2, ..., vn> ◦ vi is the value corresponding to attribute Ai  Component values of tuples: ◦ t[Ai] and t.Ai refer to the value vi in t for attribute Ai ◦ t[Au, Aw, ..., Az] and t.(Au, Aw, ..., Az) refer to the subtuple of values <vu, vw, ..., vz> from t corresponding to the attributes specified in the list Relational Model Notation  Constraints ◦ Restrictions on the actual values in a database state ◦ Derived from the rules in the miniworld that the database represents  Inherent model-based constraints or implicit constraints ◦ Inherent in the data model Relational Model Constraints  Typically include: ◦ Numeric data types for integers and real numbers ◦ Characters ◦ Booleans ◦ Fixed-length strings ◦ Variable-length strings ◦ Date, time, timestamp ◦ Money ◦ Other special data types Domain Constraints  No two tuples can have the same combination of values for all their attributes.  Superkey ◦ No two distinct tuples in any state r of R can have the same value for SK  Key ◦ Superkey of R ◦ Removing any attribute A from K leaves a set of attributes K that is not a superkey of R any more Key Constraints and Constraints on NULL Values  Key satisfies two properties: ◦ Two distinct tuples in any state of relation cannot have identical values for (all) attributes in key ◦ Minimal superkey • Cannot remove any attributes and still have uniqueness constraint in above condition hold Key Constraints and Constraints on NULL Values (cont’d.)  Candidate key ◦ Relation schema may have more than one key  Primary key of the relation ◦ Designated among candidate keys ◦ Underline attribute  Other candidate keys are designated as unique keys Key Constraints and Constraints on NULL Values (cont’d.) Key Constraints and Constraints on NULL Values (cont’d.)  Relational database schema S ◦ Set of relation schemas S = {R1, R2, ..., Rm} ◦ Set of integrity constraints IC  Relational database state ◦ Set of relation states DB = {r1, r2, ..., rm} ◦ Each ri is a state of Ri and such that the ri relation states satisfy integrity constraints specified in IC Relational Databases and Relational Database Schemas  Invalid state ◦ Does not obey all the integrity constraints  Valid state ◦ Satisfies all the constraints in the defined set of integrity constraints IC Relational Databases and Relational Database Schemas (cont’d.)  Entity integrity constraint ◦ No primary key value can be NULL  Referential integrity constraint ◦ Specified between two relations ◦ Maintains consistency among tuples in two relations Integrity, Referential Integrity, and Foreign Keys  Foreign key rules: ◦ The attributes in FK have the same domain(s) as the primary key attributes PK ◦ 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 Integrity, Referential Integrity, and Foreign Keys (cont’d.)  Diagrammatically display referential integrity constraints ◦ Directed arc from each foreign key to the relation it references  All integrity constraints should be specified on relational database schema Integrity, Referential Integrity, and Foreign Keys (cont’d.)  Operations of the relational model can be categorized into retrievals and updates  Basic operations that change the states of relations in the database: ◦ Insert ◦ Delete ◦ Update (or Modify) Update Operations, Transactions, and Dealing with Constraint Violations  Provides a list of attribute values for a new tuple t that is to be inserted into a relation R  Can violate any of the four types of constraints  If an insertion violates one or more constraints ◦ Default option is to reject the insertion The Insert Operation  Can violate only referential integrity ◦ If tuple being deleted is referenced by foreign keys from other tuples ◦ Restrict • Reject the deletion ◦ Cascade • Propagate the deletion by deleting tuples that reference the tuple that is being deleted ◦ Set null or set default • Modify the referencing attribute values that cause the violation The Delete Operation  Necessary to specify a condition on attributes of relation ◦ Select the tuple (or tuples) to be modified  If attribute not part of a primary key nor of a foreign key ◦ Usually causes no problems  Updating a primary/foreign key ◦ Similar issues as with Insert/Delete The Update Operation  Transaction ◦ Executing program ◦ Includes some database operations ◦ Must leave the database in a valid or consistent state  Online transaction processing (OLTP) systems ◦ Execute transactions at rates that reach several hundred per second The Transaction Concept  Characteristics differentiate relations from ordinary tables or files  Classify database constraints into: ◦ Inherent model-based constraints, explicit schema- based constraints, and application-based constraints  Modification operations on the relational model: ◦ Insert, Delete, and Update Summary