Relational Model

Relational Data Model
Example Database Schema
Example Database (Instance)
Integrity Constraints
Referential Integrity
Relational Databases
Describing Relational Schemas

COMP3311 20T3 ♢ Rel Model ♢ [0/13]

∧ >>

❖ Relational Data Model

The relational data model describes the world as

a collection of inter-connected relations (or tables)

The relational model has one structuring mechanism: relations

relations are used to model both entities and relationships

Each relation (denoted R,S,T,...) has:

a name (unique within a given database)
a set of attributes (which can be viewed as column headings)

Each attribute (denoted A,B,... or a₁,a₂,...) has:

a name (unique within a given relation)
an associated domain (set of allowed values)

COMP3311 20T3 ♢ Rel Model ♢ [1/13]

<< ∧ >>

❖ Relational Data Model (cont)

Consider relation R with attributes a₁, a₂, ... a_n

Relation schema of R : R(a₁:D₁, a₂:D₂, ... a_n:D_n)

Tuple of R : an element of D₁ × D₂ × ... × D_n (i.e. list of values)

Instance of R : subset of D₁ × D₂ × ... × D_n (i.e. set of tuples)

Note: tuples: (2,3) ≠ (3,2) relation: { (a,b), (c,d) } = { (c,d), (a,b) }

Domains are comprised of atomic values (e.g. integer, string, date)

A distinguished value NULL belongs to all domains

Each relation has a key (subset of attributes unique for each tuple)

COMP3311 20T3 ♢ Rel Model ♢ [2/13]

<< ∧ >>

❖ Relational Data Model (cont)

A relation: Account(branchName, accountNo, balance)

And an instance of this relation:

{
  (Sydney, A-101, 500),
  (Coogee, A-215, 700),
  (Parramatta, A-102, 400),
  (Rouse Hill, A-305, 350),
  (Brighton, A-201, 900),
  (Kingsford, A-222, 700)
  (Brighton, A-217, 750)
}

Note: accountNo is a primary key.

COMP3311 20T3 ♢ Rel Model ♢ [3/13]

<< ∧ >>

❖ Relational Data Model (cont)

Account relation as a table:

COMP3311 20T3 ♢ Rel Model ♢ [4/13]

<< ∧ >>

❖ Example Database Schema

Consider the following ER data model:

COMP3311 20T3 ♢ Rel Model ♢ [5/13]

<< ∧ >>

❖ Example Database Schema (cont)

Relational schema derived from this ER model:

Note: distinguish attributes via e.g. Branch.address vs Customer.address

COMP3311 20T3 ♢ Rel Model ♢ [6/13]

<< ∧ >>

❖ Example Database (Instance)

COMP3311 20T3 ♢ Rel Model ♢ [7/13]

<< ∧ >>

❖ Integrity Constraints

To represent real-world problems, need to describe

what values are/are not allowed
what combinations of values are/are not allowed

Constraints are logical statements that do this:

domain constraints:
limit the set of values that attributes can take
key constraints:
identify attributes that uniquely identify tuples
entity integrity constraints: require keys to be fully-defined
referential integrity constraints:
require references to other tables to be valid

COMP3311 20T3 ♢ Rel Model ♢ [8/13]

<< ∧ >>

❖ Integrity Constraints (cont)

Domain constraints example:

Employee.age attribute is typically defined as integer
better modelled by adding extra constraint (15<age<66)

Note: NULL satisfies all domain constraints (except (NOT NULL))

Key constraints example:

Student(id, ...) is guaranteed unique
Class(...,day,time,location,...) is unique

Entity integrity example:

Class(...,Mon,2pm,Lyre,...) is well-defined
Class(...,NULL,2pm,Lyre,...) is not well-defined

COMP3311 20T3 ♢ Rel Model ♢ [9/13]

<< ∧ >>

❖ Referential Integrity

Referential integrity constraints

describe references between relations (tables)
are related to notion of a foreign key (FK)

Example:

COMP3311 20T3 ♢ Rel Model ♢ [10/13]

<< ∧ >>

❖ Referential Integrity (cont)

A set of attributes F in relation R₁ is a foreign key for R₂ if:

the attributes in F correspond to the primary key of R₂
the value for F in each tuple of R₁
- either occurs as a primary key in R₂
- or is entirely NULL

Foreign keys are critical in relational DBs; they provide ...

the "glue" that links individual relations (tables)
the way to assemble query answers from multiple tables
the relational representation of ER relationships

COMP3311 20T3 ♢ Rel Model ♢ [11/13]

<< ∧ >>

❖ Relational Databases

A relational database schema is

a set of relation schemas { R₁, R₂, ... R_n } , and
a set of integrity constraints

A relational database instance is

a set of relation instances { r₁(R₁), r₂(R₂), ... r_n(R_n) }
where all of the integrity constraints are satisfied

One of the important functions of a relational DBMS:

ensure that all data in the database satisfies constraints

Changes to the data fail if they would cause constraint violation

COMP3311 20T3 ♢ Rel Model ♢ [12/13]

<< ∧

❖ Describing Relational Schemas

We need a language to express relational schemas
(which is more detailed than boxes-and-arrows diagrams used above)

SQL provides a Data Definition Language (DDL) for this.

CREATE TABLE TableName (
   attrName₁ domain₁ constraints₁ ,
   attrName₂ domain₂ constraints₂ ,
   ...
   PRIMARY KEY (attr_i,attr_j,...),
   FOREIGN KEY (attr_x,attr_y,...)
               REFERENCES
               OtherTable (attr_m,attr_n,...), ...
);

To be continued ...

COMP3311 20T3 ♢ Rel Model ♢ [13/13]

Produced: 15 Sep 2022