Chapter 3 The Relational Data Model and Relational Database Constraints CS 6360 Database Design Chris Irwin Davis Ph D Email cid021000 utdallas edu Phone 972 883 3574 O ce ECSS 4 705 Chapter 3 Outline 3 1 The Relational Data Model and Relational Database Constraints 3 2 Relational Model Constraints and Relational Database Schemas 3 3 Update Operations Transactions and Dealing with Constraint Violations 2 3 1 The Relational Data Model and Relational Database Constraints The Relational Data Model Relational model Based on Relational Algebra First commercial implementations available in early 1980s Has been implemented in a large number of commercial system 4 Other Database Models Physical Data Models Flat File Model Inverted Index Model Logical Data Models Network Model Hierarchical Model Object oriented Model Other Models XML Database Triplestore 5 Database Models 6 Relational Model Concepts Represents data as a collection of relations i e Relational Algebra relations Set Theory 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 7 Relational Model Concepts cont d 8 Relational Model Concepts cont d Table Name Table Columns Table Rows Database Implementation Names 9 Domains Attributes Tuples and Relations Domain D Set of valid atomic values Atomic Each value indivisible Specifying a domain Data type specified for each domain 10 Examples of Domains Usa phone numbers The set of ten digit phone numbers valid in the U S Social security numbers The set of valid nine digit Social Security numbers Names The set of character strings that represent names of persons 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 11 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 12 Relation Schema Example Relation with arity degree 7 Ssn Home phone Address STUDENT Name Office phone Age Gpa Using Data Types Relations sometimes written as STUDENT Name string Ssn string Home phone string Address string Office phone string Age integer Gpa real 13 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 14 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 15 Domains Attributes Tuples and Relations cont d 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 16 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 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 Default attribute order 17 Characteristics of Relations cont d 18 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 Order is explicitly not important However we use the first definition of relation by convention Attributes and the values within tuples are ordered Simpler notation 19 Attribute Value Pairs 20 Characteristics of Relations cont d Values and NULLs in tuples Each value in a tuple is atomic This is called 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 21 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 22 Characteristics of Relations cont d Two alternative interpretations meanings of a relation Declarative Assertion of facts Each tuple in the relation is a fact or a particular instance of the assertion For example the schema of the STUDENT relation of Figure 3 1 asserts that in general a student entity has a Name Ssn Home phone Address Office phone Age and Gpa Each tuple in the relation can then be interpreted as a fact or a particular instance of the assertion For example the first tuple in Figure 3 1 asserts the fact that there is a STUDENT whose Name is Benjamin Bayer Ssn is 305 61 2435 Age is 19 and so on Notice that some relations may represent facts about entities whereas other relations may represent facts about relationships 23 Characteristics of Relations cont d Two alternative interpretations meanings of a relation Logical Predicate Values in each tuple interpreted as values that satisfy the predicate Predicate Logic First Order Logic FOL Relation name is the predicate which has relation arity i e degree number of parameters For example the predicate STUDENT Name Ssn is true for the five tuples in relation STUDENT of Figure 3 1 These tuples represent five different propositions or facts in the real world Triple Store 24 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 25 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
View Full Document
Unlocking...