The Relational Data Model

1. The Relational Data Model David J. Stucki

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

3. Relational Model Concepts • 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

4. Domain • All the possible values an attribute can take • Atomic • Remember your mathematics? • Example domains: • 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 • US Phone Numbers: (ddd)ddd-dddd • GPAs: any real-valued number

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

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

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

8. Characteristics of Relations • Order of attributes and values is not that important • As long as correspondence between attributes and values maintained • 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 • But alternative has application in later formalisms

9. Characteristics of Relations • Values 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

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

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

12. Schema-based constraints • Key constraints • Remember – each tuple in a relation must be unique • 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 • 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 t1 and t2 in a relation state r of R: t1[SK] != t2[SK]

13. Schema-based constraints • Superkeys can have redundant attributes • 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 • A key is a minimal superkey • 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 • 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

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

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

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

17. Other constraints • Semantic integrity constraints • Constraints that come from outside the basic relationships between tuples • “Business rules” • “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 • “Triggers” – “when event X occurs perform action Y” • “Assertions” – “make sure that no matter what action X does, condition Y is always true”

18. Operations • The relational model has two types of operations: • Retrievals • Getting information out of the database • Updates • Adding/changing information in the database • Different kinds of updates: • 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

19. Updates & Constraints • The DBMS must make sure that updates are not allowed to violate integrity constraints • Check to make sure that attributes in an INSERT do not violate constraints • DELETE can cause referential constraint violations • Removing a tuple being referred to by another tuple • UPDATE can cause referential constraint violations • 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

20. ER-Relational Mapping

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

22. Strong Entity Types • Each strong entity type in an ER model becomes a relation • 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 Address EMPLOYEE

23. Weak Entity Types • Each weak entity type becomes a relation • Weak entity W owned by entity E • Entity E -> Relation R1 • Entity W -> Relation R2 • All simple attributes of W become attributes in R2 • Include as a foreign key the primary key attribute of R1 • Primary key of R2 will be that foreign key, plus the partial key of W 1 EMPLOYEE EMPLOYEE Dependents_of N Sex DEPENDENT DEPENDENT Bdate Name Relationship

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

25. Binary 1:1 Relationships • Foreign key approach • Two entities in the relationship – E1 and E2 • Generate two relations R1 and R2 associated with E1 and E2 • Include in R1 a foreign key pointing at the primary key of R2 • R1 should be an entity with Total Participation if possible EMPLOYEE DEPARTMENT

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

27. Binary 1:N Relationships • Use the foreign key approach • 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 DEPARTMENT

28. Binary M:N Relationships • Cross-reference approach • For Entities E1 and E2 connected by Relationship R • Create a new relation for R • Include as attributes foreign keys pointing at the primary keys of E1 and E2 – combination will be the primary key • Include any attributes tied to R • Think of this as a “lookup table” EMPLOYEE WORKS_ON PROJECT

29. Multivalued Attributes • Each multi-valued attribute A in the entity E1 gets its own relation • Relation includes the attribute A and a foreign key pointing at the primary key of the relation associated with E1 • Primary key of the new relation is a combination of A and the foreign key DEPARTMENT DEPT_LOCATIONS

30. N-ary Relationships • N-ary relationships modeled using cross-reference approach • Each n-ary relationship is made into a new relation • 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 • Be careful – cardinality constraints may mean that we need to leave some of these out

31. Summary of ER-to-Relational mapping