Relational Model

Today’s lecture is mostly about relational databases, however keep in mind that there are other popular database models that don’t follow this model, such as NoSQL or Graph Database.

History & Introduction

• Relational Model was introduced in 1970 by E.F. Codd (at IBM).
• Is the basis for most DBMS, e.g, Oracle, Microsoft SQL Server, IBM DB2, Sybase, PostgreSQL, MySQL, etc.
• Typically used in conceptual design: either directly (creating tables) or derived from a given Entity Relationship schema.

Tabular Form

The relational model structures data in tabular form. A relational database is a set of named tables (a table can also be called a relation).

Each table contains multiple rows. Each row is a tuple. Each column an attribute.

Basic Structure of Relational Model

A relation r over collection of sets (domain values) D~1~, D~2~, . . . , D~n~ is a subset of the Cartesian Product D~1~ × D~2~ × . . . × D~n~

A relation is a set of n-tuples (d~1~, d~2~, . . . , dn) where d~i~ $\in$ D~i~.

Example:

Given the sets

| Name = {Ironman, Spiderman, Hulk} | Hometown = {New York, New York, Ohio} | Occupation = {Engineer, Student, Scientist} | DOB = {1970-05-29, 2001-08-10, 1969-12-18}

then

| r = {(Ironman, New York, Engineer, 1970-05-29), (Spiderman, New York, Student, 2001-08-10), (Hulk, New York, Scientist, 1969-12-18)}

is a relation over Name x Hometown x Occupation x DOB

Relation Schema

The formal definition for a table follows from set relations. Let A~1~, A~2~, . . . , A~n~ be attribute names with associated domains D~1~, D~2~, . . . , D~n~, then R(A~1~ : D~1~, A~2~ : ~D2~, . . . , A~n~ : D~n~) is a relation schema. For example, SuperHero(Name: String, Hometown, String, Occupation, string, DOB: date)

A relation schema specifies the name and the structure of the relation. A collection of relation schemas is called a relational database schema.

A relation instance r(R) of a relation schema can be thought of as a table with n columns and a number of rows. Instead of relation instance we often just say relation. An instance of a database schema thus is a collection of relations.

Tuple (or row): An element t $\in$ r(R)

The order of rows is irrelevant.

There are no duplicate rows in a relation (ideally).

Database Schemas

A relational database schema defines:

• Names of tables in the database
• The columns of each table, i.e. the column name and the data types of the column entries,
• Integrity constraints, i.e. conditions that data entered into the tables is required to satisfy.

Primary Key Constraints * When an attribute is a primary key, it must be unique

Foriegn Key Constraints * When an attribute is a foreign key, it refers to the primary key of the referenced relation