Relational Databases

Database Model

• Data Structures - how data is organized

• Operations - Manipulated data structured

• Rules - Govern valid data

Data Model

Conceptual Tool for describing data relationships, semantics, constraints

Relational Database Management System (RDBMS)

A DBMS based on a relational model for organizing and managing data

Relation

Table in a relational database

Relational Model

Data model based on relations (tables) where it is organized in rows and columns

Table

Has name, fixed tuple of columns, and set of rows

Tuple

Ordered collection of elements; row in relational database

Column

Name and a data type

Row

Unnamed tuple of values

Data Type

Named set of values

ALTER TABLE

Adds, deletes, or modifies columns on an existing table

Relational Operations

• SELECT

• PROJECT

• PRODUCT

• JOIN

• UNION

• INTERSECT

• DIFFERENCE

• RENAME

• AGGREGATE

Relational Rules

Part of relational model, governs data in every relational database

Types of Relational Rules

• Unique Primary Key

• Unique column names

• No duplicate rows

Business Rules

State that defines/constraints something of a business

Constraint

Rules for relations, specifying conditions for valid data

Data Definition Language (DDL)

Defines Database structure

• CREATE

• ALTER

• DROP

Data Manipulation Language (DML)

• SELECT

• INSERT

• UPDATE

• DELETE

NULL

Unknown/inapplicable/does not exist; NOT zero

INSERT

Adds rows to a table

UPDATE

Modifies existing rows

DELETE

Deletes existing row

Primary Keys

• Unique

• Not NULL

• Usually left side

Composite Primary Key

• Unique - together they must be unique

• NOT NULL

• Minimal - Meaning that all the columns are needed to be unique, and if one were removed, there would be repeating/non-unique pairs

Auto-Increment Column

• Only once in each table

• No default value

• Integer data type ONLY

• Have an Index

Foreign Keys

• Data type must be the same as primary key

• Column names can be different

• May be NULL

• Not Unique

Referential Integrity Rule

Relational Rule that requires foreign key values to either be fully NULL or match some primary key value

No partially NULL ALLOWED - Mainly for composite foreign keys

Referential Integrity Violations

• A primary key (value) is updated (aka changed)

• A foreign key (value) is updated (to some nonmatching number)

• A row containing a primary key is deleted

• A (new) row containing a foreign key is inserted (and doesn’t match)

Referential Integrity Actions

• RESTRICT - Rejects referential integrity violations

• SET NULL - sets invalid foreign keys to NULL

• SET DEFAULT - sets invalid foreign keys to foreign key default

• CASCADE - when a primary key is deleted, matching foreign key rows are deleted as well