# Unit 5

Labs 2: Tables, Views, SQL Queries and Triggers

## Section 1: SQL as a Data Definition Language (DDL)

Read Chapter 6: Introduction to SQL

* Origins of the SQL standard
* The SQL Environment
* Defining a database in SQL

ANSI SQL was first published in 1986.
Updated in:

* 1989
* 1992
* 1999
* 2003
* 2006
* 2008

Concepts were first articulated in 1970 by E.F. Codds paper
"A Relational Model of Data for Large Shared Data Banks".

IBM devs started to build System R which was to demo an
implementation of a relational model in a dbms.

They used a language named "Sequel" which was renamed SQL.

SQL/DS: the first relational DBMS.
DB2: introduced in 1983

Original purpose of SQL standard:

1. specify syntax and semantics of SQL DDL and DML
2. define data structures and basic operations for designing, accessing, maintaining, controlling and protecting a SQL database.
3. portability between database definition and application modules between DBMS's.
4. Specify minimal and complete standards
5. Extension point for handling referential integrity, transaction management, user defined functions, join operators and character sets.

A standard provides

* Reduced training costs
* Productivity
* Application portability
* Application longevity
* Reduced dependence on a single vendor
* Cross system communication

The SQL Environment

SQL is used to:

* create tables
* translate user requests
* maintain data dictionary and system catalog
* update and maintain the tables
* establish security
* carry out back and recovery procedures


> RDBMS: a database management system that manages data as a collection of tables in which all data relationships are represented by common values in related tables.

Each database is contained in a catalog, which describes any object that is a part of the database.

> Catalog: A set of schemas that, when put together, constitute a description of a database.

> Schema: A structure that contains descriptions of objects created by a user, such as base tables, views and constraints as part of a database.


Each catalog must also contain an information schema.

* tables
* views
* attributes
* privileges
* constraints
* domains

SQL commands can be classified into three types:

1. DDL: Data definition language
2. DML: Data manipulation language
3. DCL: Data control language

> DDL: Commands used to define a database, including those for creating, altering and dropping tables and establishing constraints.
> DML: Commands used to maintain and query a database, including those for updating, inserting, modifying and querying data.
> DCL: Commands used to control a database, including those for administering privileges and committing (saving) data.

Each DBMS has a defined list of data types that it can handle.

* numeric
* string
* date/time
* graphic data
* spatial data
* image data

Defining a database in SQL

```sql
CREATE SCHEMA database_name;
AUTHORIZATION owner_user mokha;
```

To define a portion of a database that a particular user owns.

```sql
CREATE SCHEMA
```

To define a new table and columns

```sql
CREATE TABLE
```

To define a new view

```sql
CREATE VIEW
```

## Section 2: SQL as a Data Manipulation Language (DML)

Read Chapter 6: Introduction to SQL

* "Inserting, updating and deleting data"
* "Internal schema definition in RDBMS's"
* "Processing single tables"

Terms:

* scalar aggregate
* vector aggregate
* virtual table
* dynamic view
* materialized view

on 3: Advanced SQL and Data Control Commands

Read Chapter 7: Advanced SQL
* all sections