01 - Relational Model & Relational Algebra

1.Database

A database is an organized collection of inter-related data that models some aspects of the real-world.

Distinguish “database” with “database management systems”(DBMS).

DBMS is the software that manage a database.

2.Flat File Strawman

Database is stored as comma-separated value(CSV) files that DBMS manages.

Each entity has its own set of attributes ,so in each file,different records are delimited by new lines, while each of the corresponding attributes within a record are delimited by a comma.

3.Database Management System

A DBMS is an software that allows applications to store and analyze information in a database.

Function:

• Definition
• Creation
• Querying
• Update
• Administration of databases

Example:

• relational(most common)
• NoSQL(key/value,graph)
• Array/Matrix/Vectors

A schema is a description of a particular collection of data based on a data model.

4.Relational Model

The relational model defines a database abstraction based on realtions to avoid maintenance overhead.

It has three key points:

• Store database in simple data structure
• Access data through high-level language,DBMS figures out best execution strategy.
• Physical storage left up to the DBMS simplementation.

Three Concept of Realtional Data Model:

• Structure:The definition of relations and their contents.This is the attributes the relations have and the values that those attributes can hold.
• Integrity:Ensure the database’s contents satisfy constraints.
• Manipulation:How to access and modify a database’s contents.

A relation is an unordered set that contain the relationship of attributes that represent entities.

A tuple is a set of attribute value(also known as its domain) in the relation.

Keys:

• Primary key uniquely identifies a single tuple.
• Foreigin key specifies that an attributes from one relation has to map to a tuple in another relation.

5.Data Manipulation Language(DMLs)

Two classes of language to store and retrieve information from a database.

• Procedural:The query specifies the (high-level) strategy to find the desired result based on sets / bags
• Non-Procedural(Declarative):The query specifies only what data is wanted and not how to find it.

6.Relational Algebra

Based on set algebra

σ ：Select

π ：Projection

∪ ：Union

∩： Intersection

– ：Difference

× ：Product

⋈ ：Join

Select:

Choose a subset of the tuples from a relation that satisfies a selection predicate.

Syntax: $σ_{predicate}(R)$

Example: ${\sigma}_{a\underline{}i d ='a2'}(R)$

SELECT * FROM R WHERE a_id = 'a2'

Projection:

Projection takes in a relation and outputs a relation with tuples that contain only specified attributes. You

can rearrange the ordering of the attributes in the input relation as well as manipulate the values.

Syntax:$\pi_{A1,A2,...,An}(R)$

Example:$\pi_{b\underline{ }id,a\underline{ }id}(\sigma_{a\underline{ }id='a}(R))$

SELECT b_id-100,a_id FROM R WHERE a_id = 'a2'

Union:

Union takes in two relations and outputs a relation that contains all tuples that appear in at least one of the

input relations. Note: The two input relations have to have the exact same attributes.

Syntax:$R\cap S$