The Relational Data Model David J Stucki 1

The Relational Data Model David J. Stucki 1

Relational Model Concepts n Fundamental concept: the relation q q The Relational Model represents an entire database as a collection of relations Idea of a relation: A table of values n n Each row is some collection of facts about an entity Each column is a single attribute about the entities in the table 2

Relational Model Concepts n n Tuple – one row of a relation Attribute – one column of a relation Relation – the whole table Domain of an Attribute – all the values that attribute can have 3

Domain n All the possible values an attribute can take q q q Atomic Remember your mathematics? Example domains: n n n q US Phone Numbers: the set of all 10 -digit phone numbers Local Phone Numbers: the set of all 7 -digit phone numbers Social Security Numbers: the set of all 9 -digit numbers Names: the set of all possible names GPAs: the set of all possible values between 0. 0 and 4. 0 Data type: a format for a domain n n US Phone Numbers: (ddd)ddd-dddd GPAs: any real-valued number 4

Relation Schema n Denoted by R(A 1, A 2, . . . , An) q q n Consists of a relation name and a list of attributes Description of what the relation should contain STUDENT relation: q q q STUDENT(Name, SSN, Address, GPA) Can also include data type: STUDENT(Name: String, SSN: Social_Security_Nums, Address: String, GPA: Real) n Degree (or arity) of a relation q Number of attributes n of its relation schema 5

Relation n A particular set of tuples for a given relation schema (also known as a relation state) n n q Set of n-tuples r = {t 1, t 2, . . . , tm} The “state” of the relation is its current configuration (i. e. current contents) Each tuple in the set is an ordered list of values n Each element of the tuple corresponds to a particular attribute for the relation 6

Characteristics of Relations n Ordering of tuples q q q n Uniqueness of tuples q n No duplicate tuples in a relation! Unknown values q n Relation is a set Sets have no order Relation is not sensitive to ordering of tuples NULL value – used when value can’t be known or does not exist Interpretation q q Relation is an assertion of facts Each tuple can be thought of as a fact about the world n Or as a predicate in first order logic 7

Characteristics of Relations n Order of attributes and values is not that important q n Alternative definition of a relation q q n As long as correspondence between attributes and values maintained 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 q q q Attributes and the values within tuples are ordered Simpler notation But alternative has application in later formalisms

Characteristics of Relations n Values in tuples q q Each value in a tuple is atomic Flat relational model • • q Multivalued attributes • q Composite and multivalued attributes not allowed First normal form assumption Must be represented by separate relations Composite attributes • Represented only by simple component attributes in basic relational model

Relational Model Constraints n A constraint is a restriction on the values in a database state q Implicit constraints (model-based constraints) n Inherent in the data model itself q q Explicit constraints (schema-based constraints) n q E. g. no duplicate tuples in a relation Can be explicitly enforced/expressed in the schema Application-based constraints (business rules) n n n Derived from miniworld represented by database Cannot be explicitly enforced by the schema Must be enforced by application programs themselves 10

Schema-based constraints n Domain constraints q q Data type constraint Each attribute in a tuple may only take on a value from the domain of that attribute 11

Schema-based constraints n Key constraints q Remember – each tuple in a relation must be unique n n q No duplicate tuples! This means that no two tuples have the same combination of attributes for all of their attributes Usually there is a subset of attributes that control uniqueness n n We call this subset a superkey Definition: Let SK be a subset of attributes of the relation R that form a superkey. Then for any two distinct tuples t 1 and t 2 in a relation state r of R: t 1[SK] != t 2[SK] 12

Schema-based constraints q Superkeys can have redundant attributes n n q Ex: {First Name, Last Name, SSN} could be a superkey Don’t really need First Name and Last Name to be unique – SSN is guaranteed to be unique keys n A key is a minimal superkey q n n Remove one attribute from a key and it is no longer a superkey! A key is always a superkey, but not all superkeys are keys There can be more than one key in a relation q q Ex: {SSN} {Student ID} Common to identify one of these keys as as a primary key § Primary key uniquely identifies tuples in a relation to other relations and outside applications 13

Schema-based constraints n Constraints can apply not just to single relations q q n Relational Database Schema q q n We need to be able to talk about constraints that cross relations More Terminology! Set of relation schemas S = {R 1, R 2, . . . , Rm} Set of integrity constraints IC Relational Database State q Set of relation states for a relational database such that all integrity constraints are satisfied n Invalid state – state that violates an integrity constraint 14

Schema-based constraints n Entity integrity constraint q q n No primary key can have a NULL value Remember – primary key uniquely identifies a tuple! Referential integrity constraint q q Specified between two relations A tuple in one relation that refers to a tuple in a second relation MUST refer to an existing tuple n q You can’t put in “placeholder” references – every reference must be resolvable when you make the reference Uses the concept of a foreign key 15

Schema-based constraints n Where do referential integrity constraints come from? q Connections in the data 16

Other constraints n Semantic integrity constraints q Constraints that come from outside the basic relationships between tuples n “Business rules” q q q “no employee can have a salary larger than their supervisor” “no employee can log more than 60 hours of time in a week” Usually modeled at the application level, but sometimes can be modeled in the database n n “Triggers” – “when event X occurs perform action Y” “Assertions” – “make sure that no matter what action X does, condition Y is always true” 17

Operations n The relational model has two types of operations: q Retrievals n q Getting information out of the database Updates n n Adding/changing information in the database Different kinds of updates: q q q INSERT § Add new tuple to a relation DELETE § Remove a tuple from a relation UPDATE § Change an attribute value in a tuple in a relation 18

Updates & Constraints n The DBMS must make sure that updates are not allowed to violate integrity constraints q q Check to make sure that attributes in an INSERT do not violate constraints DELETE can cause referential constraint violations n q Removing a tuple being referred to by another tuple UPDATE can cause referential constraint violations n n Changing a primary key can cause all sorts of referential problems for any tuple referring to the updated tuple Changing a foreign key can only happen if the tuple the foreign key refers to already exists 19

ER-Relational Mapping 20

ER-Model to Relational Model n Once we have our ER-Model, we use it to come up with our Relational model q Must map elements of ER-model to elements of relational model n Entities, Relationships, Attributes, etc. must become Relations, Attributes, etc. 21

Strong Entity Types Each strong entity type in an ER model becomes a relation n q q Entity type E -> Relation R Simple attributes of E become attributes of R Include only the component attributes of a composite attribute Choose one key of E to become the primary key of R Fname M Lname EMPLOYEE Ssn Name EMPLOYEE Fname M Lname Address 22

Weak Entity Types n Each weak entity type becomes a relation q Weak entity W owned by entity E n n q Entity E -> Relation R 1 Entity W -> Relation R 2 All simple attributes of W become attributes in R 2 n n Include as a foreign key the primary key attribute of R 1 Primary key of R 2 will be that foreign key, plus the partial key of W EMPLOYEE Ssn 1 EMPLOYEE Fname M Lname Address Dependents_of DEPENDENT Sex Bdate N Essn Name Sex Bdate Relationship DEPENDENT Name Relationship 23

Binary 1: 1 Relationships n Three approaches: q q Foreign Key Approach Merged relation Approach Cross-reference Approach Should use “Foreign key approach” unless there is good reason not to 24

Binary 1: 1 Relationships n Foreign key approach q Two entities in the relationship – E 1 and E 2 1. 2. Generate two relations R 1 and R 2 associated with E 1 and E 2 Include in R 1 a foreign key pointing at the primary key of R 2 q R 1 should be an entity with Total Participation if possible EMPLOYEE Ssn Fname M Lname Address DEPARTMENT Dname Dnumber Mgr. Ssn 25

Binary 1: 1 relationships n Merged relation approach q If both entities have TOTAL participation in the relationship, you can merge them into a single entity n n Each table would have an exact one-to-one correspondence between rows, so they’re essentially the same entity Cross-reference approach q Set up a third relation as a “lookup table” for the relationship n Required for many-to-many relationships 26

Binary 1: N Relationships n Use the foreign key approach q q n Identify which relation represents the entity on “many” side of the relationship Give that relation a foreign key pointing at the primary key on the “one” side of the relationship Or use cross-reference EMPLOYEE Ssn Fname M Lname Address Dno DEPARTMENT Dname Dnumber Mgr. Ssn 27

Binary M: N Relationships n Cross-reference approach q q For Entities E 1 and E 2 connected by Relationship R Create a new relation for R n Include as attributes foreign keys pointing at the primary keys of E 1 and E 2 – combination will be the primary key Include any attributes tied to R n Think of this as a “lookup table” n EMPLOYEE Ssn Fname M Lname Address Dno WORKS_ON Ssn Pnumber Hours PROJECT Name Pnumber Location 28

Multivalued Attributes n Each multi-valued attribute A in the entity E 1 gets its own relation q q Relation includes the attribute A and a foreign key pointing at the primary key of the relation associated with E 1 Primary key of the new relation is a combination of A and the foreign key DEPARTMENT Dname Dnumber Mgr. Ssn DEPT_LOCATIONS Dno Dlocation 29

N-ary Relationships n N-ary relationships modeled using crossreference approach q Each n-ary relationship is made into a new relation n Attributes of this relation include foreign keys pointing at the primary keys of all the participating entity relations Include all simple attributes as well Primary key is usually a combination of all foreign keys q Be careful – cardinality constraints may mean that we need to leave some of these out 30

Summary of ER-to-Relational mapping 31