Relational Model E.F. Codd at IBM 1970 Chapter 7.1-7.2

Relational Model E.F. Codd at IBM 1970 Chapter 7.1-7.2

Relational Model • Most popular • simplest • most uniform data structures • most formal • files + mathematical foundation

Relational Model • database is a collection of relations • relation is a table of values • 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

Glossary of terms: • Table -> RELATION • Row -> TUPLE • Column header -> ATTRIBUTE • Data type of a column -> DOMAIN Fig. 7.1

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)

Relations • RELATION Characteristics • Set of tuples - not ordered • Values within tuples are ordered • ATTRIBUTES • Ai is an attribute with a domain dom(Ai) • degree of a relation - the number of attributes • RELATION SCHEMA: R(A1, A2,...,An): • includes relation name R and list (set) of attributes Ai • Fig 7.5

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 • t[Ai] is the value vi for attribute Ai of tuple t • r( R ) is a subset of dom(A1) X dom(A2) X...X dom(An) • 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 • Fig 7.6

Constraints • DOMAIN CONSTRAINTS: • value of each attribute must be an atomic (single) value from the domain for that attribute (1st normal form - 1NF) • no composite attributes

Constraints • KEY CONSTRAINTS: • 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 • t1[sk] != t2[sk] • every relation has at least one superkey, what is it?

Constraints • key is a minimal superkey if • we cannot remove any attribute and still have uniqueness constraint hold • candidate key is any one of the keys • primary key PK is designated candidate key (underlined)

Constraints • ENTITY INTEGRITY CONSTRAINTS • no primary key value can be null

Constraints • REFERENTIAL INTEGRITY CONSTRAINTS • specified between two relations • maintain the consistency among tuples in two relations • a tuple in one relation that refers to another relation must refer to an existing tuple in that relation • 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] Fig 7.7

Relations • RELATIONAL DATABASE SCHEMA • a set of relation schemas and a set of referential integrity constraints • S = {R1, R2,..., Rm} and a set of integrity constraints IC • RELATIONAL DATABASE INSTANCE DB • DB = {r1, r2,...rm}

Relational Model E.F. Codd at IBM 1970 Chapter 7.1-7.2