Chapter 5 The Relational Data Model and Relational Database Constraints 5 1 The Relational Data Model and Relational Database Constraints The Relational Data Model Relational model Based on Relational Algebra and Relational Calculus First commercial implementations available in early 1980s Has been implemented in a large number of commercial systems 4 Relational Model Concepts Represents data as a collection of relations From relational algebra a function is a relation F X Y Z F X Y Z Each row represents a relationship instance Represents data as a table of values tabular model Each Row called a Tuple Represents a collection of related data values that typically corresponds to a real world entity or relationship Each row represents an entity 5 Domains Attributes Tuples and Relations Set of valid atomic values for each attribute Domain D Atomic Each value is indivisible Specifying a domain Data type Range of values 6 Examples of Domains in the U S Security numbers persons Usa phone numbers The set of ten digit phone numbers valid Social security numbers The set of valid nine digit Social Names The set of character strings that represent names of Grade point averages The set of possible values of computed grade point averages must be a real number 0 to 4 Employee ages The set of possible ages of employees in a company e g must be an integer between 15 and 80 7 Domains Attributes Tuples and Relations cont d Relation schema R Attribute Ai Denoted by R A1 A2 An Made up of a relation name R and a list of attributes A1 A2 An 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 8 Relation Schema Example three formats Text based relation format with arity degree 7 i e 7 ary STUDENT Name Ssn Home phone Address Office phone Age Gpa Alternative text based format using data types STUDENT Name string Ssn string Home phone string Address string Office phone string Age integer Gpa real Alternative format as a schema diagram STUDENT Name Ssn Home phon Address Office phon Age Gpa e e 9 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 Based on a Relation Schema 1 0 Domains Attributes Tuples and Relations cont d Relation or relation state r R Mathematical i e Set Theory 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 1 1 Domains Attributes Tuples and Relations cont d Cardinality Total number of possible unique values in a domain Total number of possible unique values states in a relation 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 1 2 Characteristics of Relations Ordering of tuples in a relation NOPE Indices have an order relations do not Relation defined as a set of tuples Set elements members have no order among them Ordering of values within a tuple 1 3 Characteristics of Relations cont d Alternative definition of a relation Tuple considered as a set of attribute value Each pair gives the value of the mapping from an attribute Ai to a value vi from dom Ai Order is explicitly not important pairs However we use the first definition of relation by convention Attributes and the values within tuples are ordered Simpler notation 1 4 Characteristics of Relations Values and NULLs in tuples Each value in a tuple is atomic This is called Flat relational model First normal form assumption Composite and multivalued attributes not allowed Multivalued attributes must be represented by separate relations Composite attributes are represented only by simple component attributes in basic relational model 1 5 Characteristics of Relations NULL values Meanings for NULL values Value unknown Value exists but is not available Attribute does not apply to this tuple also known as value undefined 1 6 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 n tuple t for attribute Ai t Au Aw Az and t Au Aw Az refer to the sub tuple of t corresponding to the attributes values vu vw vz from specified in the list 1 7 5 2 Relational Model Constraints and Relational Database Schemas Relational Model Constraints What are constraints Restrictions on the actual values in a database state Constraints are derived from the rules in the mini world that the Also called integrity constraints which help to insure data database represents integrity of a database 1 9 Types of Constraints Key constraints Domain constraint NULL value constraint Entity integrity constraint Referential integrity constraint 2 0 Key Constraints No two tuples can have the same combination of values for all their attributes Superkey Any combination of attributes that uniquely identify a record tuple A superkey may or may not contain unnecessary attributes Every tuple has at least one superkey i e the set of ALL attributes Key A minimal superkey of R Removing any attribute A from key K leaves a set of attributes K that is not a superkey of R any more 2 1 Key Constraints continued Candidate key Relation schema may have more than one key Each must be a minimal superkey Primary key of the relation Designated among candidate keys Underline attribute Other candidate keys are designated as unique keys even though keys are unique by definition 2 2 Domain Constraints Typically include but not limited to data types integers real numbers strings date A restricted range within a data type e g GPA is a real valued number that is in the range 0 0 4 0 inclusive A restricted pattern within a data type e g NAME is a string of alpha only characters with max length of 25 A user defined data types 2 3 Integrity Referential Integrity and Foreign Keys Entity integrity constraint No primary key value can be NULL 2 4 Integrity Referential Integrity and Foreign Keys cont d Foreign key rules to maintain referential integrity The attributes in FK have the same domain s as the primary key attributes PK Value of FK in a tuple t2 of the current state r2 R2 either occurs as a value of PK for some tuple t1 in the current state r1 R1 or is NULL Other