CS3402 Introduction

Introduction

File Systems

permanent records stored in various files
application programs are written to extract & add records

Disadvantages of traditional FS

data redundancy & inconsistency
difficult to access
concurrent access anomalies
Security problem

Database Management System (DBMS) Characteristics

DBMS provides efficient, reliable, convenient and safe multi-user storage of and access to a massive amount of persistent data.

Massive: TB Scale
Persistent: Like file systems
Safe: No loss of data
Multi-user: Concurrency
Convenient: Easy to extract information
Efficient: High Performance
Reliable: Up all the time

Key Concepts

Data Model: Relational (set of records, XML, graph)
Schema versus data: Types versus variables
Data definition language (DDL): Set up schema or structure for a particular database
Data manipulation language (DML): Querying and modifying the database

Relational Model

The Relational Model

Used by all major commercial DB Systems
Very simple
Ad-hoc Query with high-level languages
Many existing efficient implementations

Basic Structure

Database = set of named relations (or tables)
Each relation has a set of named attributes (or columns)
Each tuple (or row) has a value for each attribute
Each attribute has a type (domain)

Schema: The schema consists of the structure of the relation and the attributes of the relation

Instance: Actual contents at given point in time

NULL: Unknown/Undefined

Key: Unique value attribute

模式（Schema）
定义：也称逻辑模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。
理解：
①   一个数据库只有一个模式；
②   是数据库数据在逻辑级上的视图；
③   数据库模式以某一种数据模型为基础；
④   定义模式时不仅要定义数据的逻辑结构（如数据记录由哪些数据项构成，数据项的名字、类型、取值范围等），而且要定义与数据有关的安全性、完整性要求，定义这些数据之间的联系

Querying Relational Databases

Creating and using a (relational) database

Design schema: Define table structures and the type of each attribute (create using DDL)
Load initial data (Files, Excel sheets): Load data from outside source
Query and modify the data: (Repeat)
- Insert rows,
- Modify tables and attribute types
- Run queries

Relational Algebra*

Select operator (σ): To pick certain rows out of a relation

σ _cond Rel

Project operator (π): To pick certain columns out of a relation

π _{A1,..., An Rel}

To pick both rows and columns: Project and select

σ _cond (Expression)

π _A1,..., An(Expression)

Join (⋈)

A theta join allows for arbitrary comparison relationships (such as ≥).

An equijoin is a theta join using the equality operator.

A natural join is an equijoin on attributes that have the same name in each relation.

Additionally, a natural join removes the duplicate columns involved in the equality comparison so only 1 of each compared column remains; in rough relational algebraic terms: ⋈ = π_{R,S-a_s} ○ ⋈_{a_R=a_S}

Union operator (U): To combine information

Difference operator (-):

关系代数概览

传统的关系代数的操作包含四个大类
1 平常的集合操作—并、交、差
2 selection(选择)：选择满足某些条件的行; projection(投影)：选择某些属性的列
3 合并两个关系元组的操作，包括笛卡尔乘积(Cartesian product)以及联合(join)操作
4 重命名(renaming)操作

以上是关系代数的操作，下面对其进行一一讲解：