Chapter 3 The Relational Data Model and Relational Database Constraints Copyright 2011 Pearson Education Inc Publishing as Pearson Addison Wesley Chapter 3 Outline The Relational Data Model and Relational Database Constraints Relational Model Constraints and Relational Database Schemas Update Operations Transactions and Dealing with Constraint Violations Copyright 2011 Ramez Elmasri and Shamkant Navathe The Relational Data Model and Relational Database Constraints Relational model First commercial implementations available in early 1980s Has been implemented in a large number of commercial system Hierarchical and network models Preceded the relational model Copyright 2011 Ramez Elmasri and Shamkant Navathe Relational Model Concepts 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 Copyright 2011 Ramez Elmasri and Shamkant Navathe Relational Model Concepts cont d Copyright 2011 Ramez Elmasri and Shamkant Navathe Domains Attributes Tuples and Relations Domain D Set of atomic values Atomic Each value indivisible Specifying a domain Data type specified for each domain Copyright 2011 Ramez Elmasri and Shamkant Navathe Domains Attributes Tuples and Relations cont d 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 Copyright 2011 Ramez Elmasri and Shamkant Navathe 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 Copyright 2011 Ramez Elmasri and Shamkant Navathe Domains Attributes Tuples and Relations cont d Relation or relation state r R Mathematical relation of degree n on the domains dom A1 dom A2 dom An Subset of the Cartesian product of the domains that define R r R dom A1 dom A2 dom An Copyright 2011 Ramez Elmasri and Shamkant Navathe 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 Copyright 2011 Ramez Elmasri and Shamkant Navathe Characteristics of Relations 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 Copyright 2011 Ramez Elmasri and Shamkant Navathe Characteristics of Relations cont d Alternative definition of a relation Tuple considered as a set of attribute value pairs Each pair gives the value of the mapping from an attribute Ai to a value vi from dom Ai Use the first definition of relation Attributes and the values within tuples are ordered Simpler notation Copyright 2011 Ramez Elmasri and Shamkant Navathe Characteristics of Relations cont d Copyright 2011 Ramez Elmasri and Shamkant Navathe Characteristics of Relations cont d Values and NULLs in tuples Each value in a tuple is atomic Flat relational model Composite and multivalued attributes not allowed First normal form assumption Multivalued attributes Must be represented by separate relations Composite attributes Represented only by simple component attributes in basic relational model Copyright 2011 Ramez Elmasri and Shamkant Navathe Characteristics of Relations cont d NULL values Represent the values of attributes that may be unknown or may not apply to a tuple Meanings for NULL values Value unknown Value exists but is not available Attribute does not apply to this tuple also known as value undefined Copyright 2011 Ramez Elmasri and Shamkant Navathe Relational Model Notation 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 Copyright 2011 Ramez Elmasri and Shamkant Navathe 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 Copyright 2011 Ramez Elmasri and Shamkant Navathe 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 Copyright 2011 Ramez Elmasri and Shamkant Navathe Relational Model Constraints 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 Copyright 2011 Ramez Elmasri and Shamkant Navathe Relational Model Constraints cont d Schema based constraints or explicit constraints Can be directly expressed in schemas of the data model Application based or semantic constraints or business rules Cannot be directly expressed in schemas Expressed and enforced by application program Copyright 2011 Ramez Elmasri and Shamkant Navathe Domain 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 Copyright 2011 Ramez Elmasri and Shamkant Navathe Key Constraints and Constraints on NULL Values 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 Copyright 2011 Ramez Elmasri and Shamkant Navathe Key Constraints and Constraints on NULL Values cont d Key satisfies two properties Two distinct tuples
View Full Document
Unlocking...