The Relational Data Model

last updated 12-sep-14

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

 

Student

stuId

lastName

firstName

major

credits

S1001

Smith

Tom

History

90

S1002

Chin

Ann

Math

36

S1005

Lee

Perry

History

3

S1010

Burns

Edward

Art

63

S1013

McCarthy

Owen

Math

0

S1015

Jones

Mary

Math

42

S1020

Rivera

Jane

CSC

15

 

Class

classNumber

facId

schedule

room

ART103A

F101

MWF9

H221

CSC201A

F105

TuThF10

M110

CSC203A

F105

MThF12

M110

HST205A

F115

MWF11

H221

MTH101B

F110

MTuTh9

H225

MTH103C

F110

MWF11

H225

 

 

 

 

Faculty

facId

name

department

rank

F101

Adams

Art

Professor

F105

Tanaka

CSC

Instructor

F110

Byrne

Math

Assistant

F115

Smith

History

Associate

F221

Smith

CSC

Professor

Enroll

stuId

classNumber

grade

S1001

ART103A

A

S1001

HST205A

C

S1002

ART103A

D

S1002

CSC201A

F

S1002

MTH103C

B

S1010

ART103A

 

S1010

MTH103C

 

S1020

CSC201A

B

S1020

MTH101B

A

 

 

 

 

 

 

 

 


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.

A relation ,then, is any subset of the Cartesian product

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