Relational model

definition: relational model

a relational db is a collection of related tables

purposes of a function

Maps sets of inputs to sets of outputs.

Associates an input value with a specific output value.

Ensures each input is related to exactly one output.

what is a relation/table

Represents data logically.
Physically implemented as a table.
Enables precise explanation of database operations.

types of relational model

logical
formal
data

logical model

independent of physical implementation

formal model

uses set theory and predicate logic

data model

Concepts used to describe the database, i.e., the contain information

Simple but powerful; still the foundation for databases

three aspects of data

data structure
data integrity
data manipulation

data structure

table, column, domain, row

entities and their relations

data integrity

null, entity Integrity, referential integrity, general constraints

(rules that ensure the data is accurate, consistent and valid)

data manipulation

relational algebra

(using relational algebra to retrieve, edit and add data to our database)

what does a relational model consist of

data structure
data constraints
relational operators

data constraints

Restrictions on data, relations, …

relational operators

Restrict, project, cartesian product, union, difference, …

What are the objectives of a relational model in databases?

Achieve high data independence (allow changes without affecting applications).
Control redundancy (data not stored in multiple locations).
Maintain consistency (copies are accurate and up-to-date).
Enable set-oriented manipulation languages (e.g., relational algebra).

row

records or tuple
Represents an individual instance of an entity.

Uniquely identified by one or more attributes (primary key).

Primary key must not contain null values (entity integrity rule).

column

field or attribute
Each column heading in a table has a distinct name

Its value must be atomic, i.e. a single value drawn from the domain of the column

table

relation
has rows and columns
represents a kind of entity or class
each table is has a unique name for id

Key properties:
No duplicate rows.
Row/column order doesn't matter.
Columns have unique names.

What is a domain in databases?

A domain is the set of possible values that a column can take.

How are columns in a table defined in relation to domains?

Every column in a table is defined on a domain.

Why are domains unrelated in databases?

Unrelated domains restrict column comparisons and prevent meaningless operations.

Can multiple columns share the same domain?

Yes, columns like SID and CID may have the same domain.

Can domains be infinite?

Domains may be potentially infinite but are constrained in the real world.

Key

one or more columns that determine (imply) other columns

Or: one or more columns to uniquely identify a row

types of keys:

candidate
primary
alternate
foreign
composite

candidate key

A minimal set of columns which uniquely identifies each row

alternate key

The remaining candidate keys after one is chosen as the primary key.
Alternate keys are potential unique identifiers for table rows.

primary key

The chosen candidate key to uniquely identify each row

foreign key

Columns (or a set of columns) that matches the candidate key of the associated table

Used to represent relationship between tables

composite key

Refers to a key consisting of two or more columns

null values

A NULL value is required to deal with missing information: value unknown, inserted later..
not the same as “ “ (blank) or 0

db constraints

Integrity constraints ensure the database doesn't have invalid values or configurations.

They ensure the data is accurate and reliable.

What are the types of database constraints?

Application/DB-Specific Constraints: Align with enterprise or business rules.

Integrity Constraints:
Entity Integrity: Each row is unique and identifiable.
Referential Integrity: Ensures accuracy and consistency between tables.

Multiplicity Constraints:
Required Data: Primary key cannot be null
Domain Constraints: Enforces valid values for a column.

integrity constraints

Entity integrity rule:
constraint on primary keys
Value of a primary key must be unique and
not null

Referential integrity rule:
constraint on foreign keys
Value of a foreign key must either match the candidate key it refers to
or be null

what does a relational model comprise of

structural definition
integrity rules
data manipulation

main languages used to deal with data manipulation

relational algebra
relational calculus

What is relational algebra in databases?

Theoretical language for manipulating relations
Provides operations to define new tables without modifying original ones.
Forms the basis for SQL query execution.
Supports nesting expressions and closure.
Operands and results are tables, enabling chaining of operations.

visual summary of operations

natural join

restrict operation

defines horizontal subset table
called a unary operator as it works on a single tables

project operation ⫪

defines vertical partitioning of a table

intersection operation

set intersection on tables:
only rows contained in both

natural join operation  ⋈

cartesian product, removing common duplicated columns

relational algebra is a procedural language

query is evaluated in the order specified

complex queries are evaluated by:

Nesting operations to create a relational expression

or applying operations one at a time and creating temporary tables

processing complex queries

most efficient one is preferable

relational algebra Vs Relational calculus - similarities

they are formal, theoretical - based on maths principals, non user friendly
used as a basis for data manipulation
lang for relational db
used to define operations to manipulate tables
equivalent in terms of expressive power

relation algebra Vs relational calculus - differences

relational algebra - is procedural , specifies how to achieve
relational calc - is declarative , specifies what to achieve

closure

property where operations produce only tables based on elements of the input tables

relational algebra operations 5+2

5 basic operations:
restrict (unary)
projection(unary)
cartesian product
union
set difference

2 operations expressed in terms of basic operations:
join
intresections

relational algebra symbols

visual interpretation summary

comparison operators

<, >, <=, >=, =, ≠

boolean operators

∧ - and ∨- or ∾- not

set operations

∪ = union
∩= intersection
- = difference

∪ union

combines two tables
removes all duplicate rows

∩ intersection

returns rows that appear in both tables

- = difference

returns rows that are in on and not the other table
T ← R - S
output all the rows that are in R but not in S

cartesian product (X)

multiplies two tables
PQ ← P x Q

types of join operations

equi-join
natrual join
outer join : left outer join, right outer join, full outer join

join ⋈

combines 2 tables to form a new one
derivative of the cartesian product where all elements satisfy p
col involves in ajoin condition p must have the same domain

equi - join

condition of equi- join contains only equality comparisons

outer join

used when displaying rows in the result that don’t have matching values in the join col

avoids info loss and includes unmatched rows in the result table

problems with natural join

rows not included when:
join cols are null or dont have matching rows in another tables

can cause loss of data

left outer join

includes all rows from R1 even unmatched ones
missing values of rows from R2 are set to null

right outer join

includes all rows from R2 even the unmatched ones
missing values of froms from R1 are set to null

full outer join

inludes all rows from R1 and R2, even unmatched ones
missing val set to null