The Relational Data Model

last updated 31-oct-16

History of Relational Model

Microsoft’s Access, now most popular microcomputer-based DBMS, is relational

Oracle, DB2, Informix, Sybase, Microsoft’s SQL Server, MySQL, PostgreSQL- most popular enterprise DBMSs, all relational

 

 


Advantages of Relational Model

 


Data Structures

Relations are represented abstractly as tables

Rows (tuples) correspond to individual entities

Columns correspond to attributes

 


Example: Relations

So a table ≠ entity.


Mathematical Relations

For two sets D1 and D2, the Cartesian product, D1 X D2 , is the set of all ordered pairs in which the first element is from D1 and the second is from D2. The domains for the two sets are abitrary.

If D1={a,b,c} and D2={x,y} then D1 X D2={(a,x), (a,y), (b,x), (b,y), (c,x), (c,y)}

A relation then is any subset of the Cartesian product, e.g., {(a,x), (a,y), (c,x)}

One can form a Cartesian product of 3 sets; a relation is any subset of the ordered triples so formed.

This can extend to n sets, using n-tuples

Database Relations

A relation schema, named R, is a set of attributes A1, A2,…,An with their corresponding domains D1, D2,…Dn

A relation r on relation schema R is a set of mappings from the attributes to their domains,
or to say r is a set of n-tuples (A1:d1, A2:d2, …, An:dn) such that d1∈ D1, d2∈D2 , …, dn∈Dn

In a table to represent the relation, list the Ai's as column headings, and let the (d1, d2, …dn) become the n-tuples, the rows of the table

 


Relation Schema

A schema defines the following

Relation name

Attribute names and domains

Integrity constraints - e.g.,:

Default values

 


Relational Database

Example

TableName (attr1:type, attr2:type, ... ) is a simplified non-SQL description of the table.

A running example used in the course: Presidential DB


Integrity Constraints

These constraints, determined by the database designer, are part of the schema.  They represent restrictions on the state (or sequence of states) of data base.  The constraints are enforced by the DBMS.

Intra-relational - involve only one relation -- are part of the relation definition

Inter-relational - involve several relations -- are part of the relation schema or database schema

 

Kinds of Integrity Constraints (IC)

Static IC is a limitation on the state of the database

Dynamic IC  is a limitation on the sequence of  the database states (supported by some DBMSs, but not in current the SQL standard)


Relation Keys

Relations never have duplicate tuples, so you can always tell tuples apart; implies there is always a key (which may be a composite of all attributes, in worst case)

Superkey: set of attributes that uniquely identifies tuples

Candidate key: superkey such that no proper subset of itself is also a superkey (i.e. it has no unnecessary attributes)

Primary key: candidate key chosen for unique identification of tuples

Cannot verify a key by looking at an instance; need to consider semantic information (common sense and knowledge about the enterprise) to ensure uniqueness.

A foreign key is an attribute or combination of attributes that is the primary key of some relation (called its home relation). Usually the home relation is some other relation but there can be cases of self-referencing (recursuve relationship)

 


Key Constraint

Values in a column (or columns) of a relation are unique: at most one row in a relation instance can contain a particular value(s)

Key - set of attributes satisfying key constraint

Minimality - no subset of a key is a key.  When you determine a key, this rule should be applied.

Superkey - set of attributes containing key

Every relation has a key.  The goal is to determine the "best" key, but a relation can have several keys:

 


Foreign Key Constraint

Also known as Referential integrity => Item named in one relation must correspond to tuple(s) in another that describes the item

Examples:

We say "a1 is a foreign key of R1 referring to a2 in R2" meaining that "if v is the value of a1, then there is a unique tuple in R2 in which a2 has the same value v

Example

Note the foreign key might consist of several columns:

In general, when R1(a1, …an) references R2(b1, …bn):

 


Semantic Constraints

Domain, primary key, and foreign key are examples of structural (syntactic) constraints

Semantic constraints express rules of application: