Relational Model E.F. Codd at IBM 1970 Chapter 3

Relational Model • • • • • • “A relational model of data for large shared data banks” Most popular simplest most uniform data structures most formal files + mathematical foundation

• Example of a information in a database?

EMPLOYEE FNAME John Franklin Alicia Jennifer Ramesh Joyce Ahmad James MINIT B T J S K A V E LNAME Smith Wong Zelaya Wallace Narayan English Jabbar Borg SSN 123456789 333445555 999887777 987654321 666884444 453453453 987987987 888665555 BDATE 1965-01-09 1955-12-08 1968-01-19 1941-06-20 1962-09-15 1972-07-31 1969-03-29 1937-11-10 ADDRESS 731 Fondren, Houston, TX 638 Voss, Houston, TX 3321 Castle, Spring, TX 291 Berry, Bellaire, TX 975 Fire Oak, Humble, TX 5631 Rice, Houston, TX 980 Dallas, Houston, TX 450 Stone, Houston, TX SEX M M F F M F M M SALARY 30000 40000 25000 43000 38000 25000 25000 55000 SUPERSSN 333445555 888665555 987654321 888665555 333445555 333445555 987654321 null DNO 5 5 4 4 5 5 4 1 DEPARTMENT DNAME Research Administration Headquarters DNUMBER 5 4 1 MGRSSN 333445555 987654321 888665555 MGRSTARTDATE 1988-05-22 1995-01-01 1981-06-19

Database

• • • Tables Object, instance, entity Relationships

Relational Model

• • • a table is composed of rows and columns row - a collection of related data values – describing an entity or relationship instance column – same attribute for different entities • • Relation is a table of values Database is a collection of relations

Glossary of terms:

• • • • Table -> RELATION Row -> TUPLE Column header -> ATTRIBUTE Data type of a column -> DOMAIN

Glossary Cont’d

• • DOMAIN D – specify data type, format for each domain – is of data-type (or format) – set of atomic values requirement) (relational model TUPLE – no composite values (all values are atomic) – no multivalued attributes ( First Normal Form )

Relations

• • • RELATION Characteristics –

Set

of tuples not ordered – Values within tuples are ordered ATTRIBUTES – – A i is an attribute with a domain dom(A i ) degree of a relation - the number of attributes RELATION SCHEMA: R(A 1 , A 2 ,...,A n ): – includes relation name R and list (set) of attributes A i

DB Schema – 2 notations

• EMPLOYEE(Fname, Minit, Lname, SSN, Bdate, Address, Sex, Salary) • EMPLOYEE Fname Minit Lname SSN Bdate Address Sex Salary

Relations

• RELATION (instance) r( R ) – – current relation state set of n-tuples (where n is a number of attributes) – – – – – r = {t 1 , t 2 ,...,t m } each n-tuple t is an ordered list on n values t =

n > where v i is an element of dom(A i ) or null t j [A i ] is the value v i for attribute A i of tuple t j r( R ) is a subset of what? (use domains of attributes) • dom(A 1 ) X dom(A 2 ) X...X dom(A n ) the tuples themselves are not ordered

Relations

• • • relation intension refers to the schema relation extension to the state all tuples within a relation are distinct

Constraints

1. DOMAIN CONSTRAINTS: – • •

1st normal form 1NF

value of each attribute must be an atomic (single) value from the domain for that attribute no composite attributes

Constraints

2. KEY CONSTRAINTS: –

All elements are distinct

• no two tuples can have the same combination of values for all their values (uniqueness constraint). Therefore, all elements are distinct.

• superkey sk – any set of attributes with property: – – no two tuples from the relation have the same combination of values for those attributes t 1 [sk] != t 2 [sk] every relation has at least one superkey, what is it?

Constraints cont’d

• • • key is a minimal superkey if – we cannot remove any attribute and still have uniqueness constraint hold – – (city, state, zip) Tuscaloosa has > 1 zip, same zip for Cottondale, Coaling candidate key is any one of the keys primary key PK is designated candidate key – underlined in both relation and ER models

Constraints

3. ENTITY INTEGRITY CONSTRAINTS: –

no primary key value can be null

Referential Constraint Relationships

4. REFERENTIAL INTEGRITY CONSTRAINTS –

a tuple in one relation that refers to another relation must refer to an existing tuple in that relation

Student(Name, SSN, HomePhone, Address, OfficePhone, Age, GPA) Department(Name, DeptHead, Location, College) • • Students have a major To Student relation add?

Referential Constraint Relationships

Student(Name, SSN, HomePhone, Address, OfficePhone, Age, GPA, MajorName) Department(Name, DeptHead, Location, College)

Constraints

4. REFERENTIAL INTEGRITY CONSTRAINTS –

a tuple in one relation that refers to another relation must refer to an existing tuple in that relation

• • • specified between two relations maintain the consistency among tuples in two relations if one relation refers another, then the primary key of the referred relation is a foreign key FK in the referring relation. t 1 [PK] = t 2 [FK] • • Can a foreign key can be null?

Yes, for example if an employee does not have a supervisor

Referential Constraint Relationships

Student(Name, SSN, HomePhone, Address, OfficePhone, Age, GPA, MajorName) Department(Name, DeptHead, Location, College)

Referential Constraint

Name SSN HomePhone Address OfficePhone Age GPA Major Name DeptHead Location College

Relations

• RELATIONAL DATABASE SCHEMA – a set of relation schemas and a set of referential integrity constraints S = {R 1 , R 2 ,..., R m } and a set of integrity constraints IC • RELATIONAL DATABASE INSTANCE DB – DB = {r 1 , r 2 ,...r

m }

ER to Relational

• How do we go from the ER to the relational?

– What are the components that we must map?

– What are the rules for mapping from ER to relational?