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

1. Relational Model E.F. Codd at IBM 1970 Chapter 5

2. Before the relational model what did people use? • Hierarchical – confined to 1:M relationships • IBM Information Management System (IMS)

3. How is this queried? Still being used???

4. How would you store information in a database?

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

6. Database • Object, instance, entity • Relationships • The relational model captures all of this in a table

7. 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

8. Glossary of terms: • Table => RELATION • Row => TUPLE • Column header => ATTRIBUTE • Data type of a column  =>  DOMAIN

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

12.  Relations • RELATION Characteristics  • Set of tuples - not ordered • Values within tuples are ordered • ATTRIBUTES •  Ai is an attribute with a domain dom(Ai), e.g., dom(Age) is integer •  degree of a relation - the number of attributes • RELATION SCHEMA: R(A1, A2,...,An): • includes relation name R and list (set) of attributes Ai… An • RELATIONAL DATABASE SCHEMA • a set of relation schemas and a set of referential integrity constraints

13. Relation Schema – 2 notations • EMPLOYEE(Fname, Minit, Lname, SSN, Bdate, Address, Sex, Salary) • EMPLOYEE Fname Minit Lname SSN Bdate Address Sex Salary

14. Relations • RELATION (instance) r( R ) • current relation state   • set of n-tuples (where n is a number of attributes) • r = {t1, t2,...,tm} each n-tuple t is an ordered list on n values • t = <v1, v2,...vn> where vi is an element of dom(Ai) or null • tj[Ai] is the value vi for attribute Ai of tuple tj • the tuples themselves are not ordered

15. r( R ) is a subset of what? (answer this using domains of attributes) • Example: dom(A1) = {A, B}, dom(A2) = {1, 2, 3} • A cartesian product combines each values in one domain, with all the values in the other domain • Answer in general terms? • dom(A1) X dom(A2) X...X dom(An)

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

17. Constraints • 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, no set-valued attributes

18. 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. • How can this be enforced? • With keys • lots of different types of keys

19. Constraints cont’d - keys  • superkey sk • any set of attributes with property: no two tuples from the relation have the same combination of values for those attributes • t1[sk] != t2[sk] • every relation has at least one superkey, what is it? • key is a minimal superkey - meaning • we cannot remove any attribute and still have uniqueness constraint hold • for example: Name of newspaper, city • primary key PK is designated candidate key • underlined in relational schema

20. Constraints cont’d - keys EMPLOYEE(Fname, Minit, Lname, SSN, Bdate, Address, Sex, Salary) NEWSPAPER(Name, City, Publisher, YearStarted) • candidate key is any one of the keys • Underlined in ER model

21. Constraints 3. ENTITY INTEGRITY CONSTRAINTS: • no primary key value can be null

22. Referential Constraints 4. REFERENTIAL INTEGRITY CONSTRAINTS Relationships Student(Name, SSN, HomePhone, Address, OfficePhone, Age, GPA) Department(Name, DeptHead, Location, College) • Students have a major • To Student relation add? Student(Name, SSN, HomePhone, Address, OfficePhone, Age, GPA, Major) Where Major is the Name of a Department

23. 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. t1[PK] = t2[FK] • Can a foreign key can be null? • Yes, for example if an employee does not have a supervisor, so • t2[FK] is NULL

24. Referential Constraint - Relationships Student(Name, SSN, HomePhone, Address, OfficePhone, Age, GPA, Major) Department(Name, DeptHead, Location, College)

25. Referential Constraint Name SSN HomePhone Address OfficePhone Age GPA Major Name DeptHead Location College

26. Relations • RELATIONAL DATABASE INSTANCE DB • DB = {r1, r2,...rm}