!" #Chris Irwin Davis, Ph.D.Email: [email protected]: (972) 883-3574Office: ECSS 4.705Chapter 3: The Relational Data Model and Relational Database ConstraintsCS-6360 Database Design!" #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 Violations23.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 system4!" #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°Triplestore5!" #Database Models6!" #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 attribute7!" #Relational Model Concepts (cont’d.)8!" #Relational Model Concepts (cont’d.)9Table NameTable ColumnsTableRowsDatabase Implementation Names!" #Domains, Attributes, Tuples, and Relations• Domain D °Set of valid atomic values• Atomic°Each value indivisible• Specifying a domain °Data type specified for each domain10!" #Examples of Domains11• 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.!" #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 schema12!" #Relation Schema Example• Relation with arity degree 7°STUDENT(Name, Ssn, Home_phone, Address, 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 Schema14!" #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 domain16!" #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 order17!" #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 notation19!" #Attribute-Value Pairs20!" #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 model21!" #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
View Full Document
Unlocking...