Relational Model

History and Basics, Constraints, SQL DDL

Relational Model

• A logical data model
• Preceded by hierarchical and network models
• Relational Database Management Systems (RDBMS) have been available since the late 1970’s
• Ruled the market until 2010+
• Newer models have appeared due to “big data”

Inventor

Edgar Frank “Ted” Codd (1923–2003)

“A Relational Model of Data for Large Shared Data Banks” Communications of the ACM 13 (6): 377–387, June 1970

Relational Model

• A formal model for data
• Data is organized as a set of relations

​

MUSICIAN

Relation (table)

Tuples (rows/records)

Attributes (columns/fields)

Attributes

• Atomic
• In one tuple/row, an attribute has a single, atomic value
• No multivalued attributes
• No composite attributes
• Domain – set of legal values for an attribute
• Same as in the ER model
• A datatype or set of values

Database Schema

• A relational schema is composed of relations and their attributes
• The schema defines the structure of the database and its data
• RELATION1( ATTR1, ATTR2, ATTR3, … , ATTRn )
• RELATION2( ATTR1, ATTR2, ATTR3, … , ATTRn )
• …
• RELATIONn( ATTR1, ATTR2, ATTR3, … , ATTRn )
• Metadata

Simple Relational Notation

• DOCTOR( ID, Name, Specialty )
• PATIENT( ID, Name, Street, City, Region, Country, DateOfBirth, Age )

Database Instance/State

• The set of data contained in a database at any point in time
• The set of tuples/rows in each relation/table
• The set of values for each attribute in each tuple in each relation

r1_1 = { v1, v2, v3, …, vn }

r1_2 = { v1, v2, v3, …, vn }

…

rk_n = { v1, v2, v3, …, vn }

…

​

Database Instance/State

• Every attribute value v, is either a legal value from the attribute’s domain, or NULL
• NULL means
• Unknown
• Unavailable
• Undefined (not applicable)
• The tuples in a relation have no defined order

Constraints

Constraints

• Schema-based constraints
• Expressed in the model
• Enforced by the RDBMS
• Application-based constraints
• Business rules
• Enforced by application software or other means

Model-based constraints

• Domain Constraints
• Datatypes of attributes (integer, date, string, etc.)
• Key Constraints
• In any relation instance, no row can have the exact set of values for its attributes as another row

Keys

• Superkey – set of attributes whose combined values are unique across all rows in a table
• Key – a minimal superkey – removing just one attribute from a minimal superkey will render it no longer a superkey (duplicate values are possible)
• Candidate key – one of possibly multiple minimal superkeys
• Primary key – one of the candidate keys is selected to be the primary key
• Underlined in the simple relational notation

Integrity Constraints

• Entity Integrity Constraints
• A primary key cannot be NULL
• No attribute that is part of the primary key can be NULL
• Referential Integrity Constraints
• Constraints on the relationships between tables
• In the relational model, relationships are established using foreign keys

Foreign Keys

• A foreign key (FK) in one table is the primary key (PK) of another table
• Matching values in the FK and PK serve to relate the 2 tables
• Referential integrity requires that
• A FK has the same domain as the PK it refers to
• The value of an FK in any row either,
• Occurs as a value of the PK in a row in the other table, or
• Is NULL

Referential Integrity

• In the simple relational model notation
• An arrow points from the FK in one relation to the PK it refers to in the other relation

Data Definition Language (DDL)

CREATE SCHEMA

• In MySQL, a schema is a container for a database
• Contains all the metadata that defines the database,
• and the data itself
• On our shared MySQL server, we each have our own schema
• Our schema name is our EID
• Even if we create several databases (sets of related tables), they must all be in that single schema

​

CREATE or DROP TABLE

DROP TABLE IF EXISTS `elkadima`.`PERSON` ;

​

CREATE TABLE IF NOT EXISTS `elkadima`.`PERSON` (

`PersonID` INT NOT NULL,

`TEAM_TeamName` VARCHAR(45) NOT NULL,

`PersonType` INT NOT NULL);

​

​

• If you are currently in your schema, the schema name is not required
• drop deletes the table and its data
• if (not) exists clauses are not required, but suppress error messages

MySQL docs

PRIMARY KEY constraint

CREATE TABLE IF NOT EXISTS `elkadima`.`GAME` (

`VisitorTeam` VARCHAR(45) NOT NULL,

`HomeTeam` VARCHAR(45) NOT NULL,

`GameDateTime` DATETIME NOT NULL,

`VisitorGoals` INT NULL,

`HomeGoals` INT NULL,

PRIMARY KEY (`VisitorTeam`, `HomeTeam`, `GameDateTime`));

​

• This constraint defines which field or fields form the primary key
• Any attempt to insert a duplicate key in the table will fail
• Fields that are part of the key must not be NULL

FOREIGN KEY constraints

CREATE TABLE IF NOT EXISTS `elkadima`.`GAME` (

`VisitorTeam` VARCHAR(45) NOT NULL,

`HomeTeam` VARCHAR(45) NOT NULL,

`GameDateTime` DATETIME NOT NULL,

`VisitorGoals` INT NULL,

`HomeGoals` INT NULL,

PRIMARY KEY (`VisitorTeam`, `HomeTeam`, `GameDateTime`),

CONSTRAINT `fk_GAME_TEAM`

FOREIGN KEY (`HomeTeam`)

REFERENCES `elkadima`.`TEAM` (`TeamName`)

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_GAME_TEAM1`

FOREIGN KEY (`VisitorTeam`)

REFERENCES `elkadima`.`TEAM` (`TeamName`)

ON DELETE NO ACTION

ON UPDATE NO ACTION));

MySQL docs

Foreign key constraints

• on delete and on update clauses:
• Specify what action to take if the referenced field is deleted or changed
• NO ACTION or RESTRICT - does not allow the delete/update
• CASCADE - propagate the delete/update to the referring field(s)
• SET NULL - preserve the row but set the referring field(s) to NULL
• SET DEFAULT - preserve the row but set the referring field(s) to the default value

​

MySQL docs

CHECK constraints

• These constraints can apply to columns or tables
• There are several ways to create them:

​

CREATE TABLE t1

(

CHECK (c1 <> c2),

c1 INT CHECK (c1 > 10),

c2 INT CONSTRAINT c2_positive CHECK (c2 > 0),

c3 INT CHECK (c3 < 100),

CONSTRAINT c1_nonzero CHECK (c1 <> 0),

CHECK (c1 > c3)

);

MySQL docs

ALTER TABLE

• Change an existing table
• Usually followed by ADD or DROP

​

alter table CO_DEPENDENT

drop foreign key `fk_DEPENDENT_EMPLOYEE1`;

​

alter table CO_DEPENDENT

add CONSTRAINT `fk_DEPENDENT_EMPLOYEE1`

FOREIGN KEY (`ESSN`)

REFERENCES `EmployeeDB`.`CO_EMPLOYEE` (`SSN`)

ON DELETE CASCADE

ON UPDATE NO ACTION;

MySQL docs

Issues with constraints

• Foreign key constraints may reference tables that have not yet been created
• Sometimes the FK relationships cannot be ordered to prevent forward references
• Constraints can be added after all tables have been created
• Bulk data insertion may violate constraints
• Constraints can be dropped
• Added after the bulk insertion