1 / 26

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

Delve into the core concepts of the relational model of databases, including tables, rows, columns, relationships, and constraints. Explore essential terminology like relations, tuples, attributes, domains, and keys.

josewilliams
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

More Related