Outline

• SQL
• Relational Algebra
• Quiz

Introduction

• Last week, we introduced the concept of a data model for a DBMS.
• We said to specify a data model we need to say: the structure of the data, the operations on the data, and the constraints on the data that will be stored.
• We defined the relation model and defined some of its terminology.
• In particular, we said data is stored in a set relations. A relation consists of a schema which can be used to described the structure and constraints on the data, and the data is then a set of tuples matching this schema.
• For example, R1(A1:int, A2:string, A3:date) is an example schema, (15, "hi there", 17-Aug-2018) might be a tuple existing in this relation.
• We said operations in the relational model are often done using the relational algebra.
• Today, we are going to look at SQL (pronounced sequel, Structured Query Language).
• It is the most popular language for describing and manipulating real world relational databases.
• Its most recent version is SQL 2016.

SQL - Overview

• There are two main aspects of SQL:
  1. The Data-Definition sublanguage for declaring database schemas and
  2. The Data-Manipulation sublanguage for querying and modifying the database.
• We consider each of these languages in turn.

SQL - Relations

• SQL supports three distinct kinds of relations:
  1. Stored relations, which are called tables. This is most common kind of relation. Each of its tuples are physically stored on secondary media. (In SQL, can define using CREATE TABLE command).
  2. Views. The tuples of this kind of relation are computed from other relations and are not usually physically stored. (In SQL, can define using CREATE VIEW command)
  3. Temporary Relations. This relations might be computed as intermediate results in computing the answer to a query. (In SQL, can define using CREATE TEMPORARY TABLE command)
• The CREATE TABLE declares the schema associated with a stored relation. It gives the name for the tables, its attributes, and their data types.
• It also allows one to specify keys, additional kinds of constraints on the data, and indexes.

SQL - Types

Before we give the syntax of CREATE TABLE in any detail, let's consider some of the supported attribute data types:

1. Character strings of fixed or varying length. CHAR(n) is used to denote a fixed-length string of up to `n` characters padded using trailing spaces to make `n` chars. So a CHAR(5) column asked to store 'foo' stores it as 'foo '. VARCHAR(n) is used to store strings of up to `n` characters. These are usually represented as null terminated strings or as structs with a length component.
2. Bit strings of fixed or varying length. BIT(n) and BIT VARYING(n) denote strings of length `n` or up to length `n` respectively.
3. BOOLEAN. This can have as values TRUE, FALSE, or UNKNOWN.
4. INT or INTEGER. These denote integer values (32 bits). SHORTINT and BIGINT might be used to store an integer number of half as many or twice as many bits.
5. Floating-point numbers. Typical floating point numbers (32 bits) are indicated using REAL or FLOAT (synonyms). Higher precision floats can be represented using DOUBLE PRECISION. There is also a fixed decimal point type DECIMAL(n,d) where `n` is the total number of digits and d is after the decimal point. For example, DECIMAL(6,2) might store a value like: 0123.45
6. Dates and times. DATE for dates and TIME for times. Example values might be: DATE '1948-05-14' and TIME '15:00:02.5'.

Quiz

Which of the following is true?

1. To fully describe a data model one must specify how it structures data, what operations it allows, and what kinds of constraints it permits.
2. The DBMS log manager is responsible for transaction concurrency control.
3. To speed up DBMS access, log records are only stored in RAM.

SQL - Table Definitions

The simplest form of declaration of a relation in SQL consists of the keywords CREATE TABLE followed by the name of the relation and a parenthesized, comma separated list of the attribute names and their types. Below are some examples:

CREATE TABLE Movies (
    title CHAR(100), 
    year INT, 
    length INT, 
    genre CHAR(10), 
    studioName CHAR(30), 
    producerC# INT
);

CREATE TABLE MovieStar (
    name CHAR(30),
    address VARCHAR(255), 
    gender CHAR(1), 
    birthdate DATE
);

SQL - Table Modification

• We can delete a table in our current database using the DROP command. For example,

  DROP TABLE R;
  

  would delete the table named `R` from the current database.
• To change the columns in a table we use the ALTER TABLE command.
• This command has several options, the most important of which are:
  1. ADD followed by an attribute name and its type.
  2. DROP followed by an attribute name.
• For example,

  /* Add a column to an existing table (doesn't work sqlite).
     Existing tuples will have value null for this column.
  */
  ALTER TABLE MovieStar ADD phone CHAR(16);
  
  
  /* Delete a column */
  ALTER TABLE MovieStar DROP birthdate;
  

SQL - Default Values

• When we create or modify tuples, we sometimes don't have values for all the components.
• It is often useful to have a method to specify a default value in SQL which will be used if that column is not specified when doing a row insert.
• Default values can be specified in either the CREATE TABLE statement or in an ALTER TABLE statement. For example,

  CREATE TABLE PERSON (
      name VARCHAR(30),
      gender CHAR(1) DEFAULT '?',
      birthdate DATE DEFAULT DATE '0000-00-00' 
  );
  
  ALTER TABLE MovieStar ADD phone CHAR(16) DEFAULT 'unlisted';
  

Declaring Keys

• There are two ways to declare an attribute or set of attributes to be a key using CREATE TABLE:
  1. when that attribute is listed in the relation schema
  2. separately in the declared items list of the schema
• For keys involving more than one attribute we need to use method 2.
• There are two declarations that may be used to say something is a key:
  1. PRIMARY KEY
  2. UNIQUE
• Only one set of attributes is allowed to be declared a PRIMARY KEY, many can be declared UNIQUE. Usually, the DBMS sorts the tuples when stored on a primary key.
• If a duplicate value is attempted to be inserted into unique column that insert will be rejected.
• Below are some examples of these declarations:

  /* Using declaration method 1 */
  CREATE TABLE MovieStar (
     name CHAR(30) PRIMARY KEY, 
     address VARCHAR(255), 
     gender CHAR(1), 
     birthdate DATE 
  );
  
  /* Using declaration method 2 */
  CREATE TABLE MovieStar (
     first_name CHAR(30), 
     last_name CHAR(30),
     ssn int UNIQUE,
     address VARCHAR(255),
     gender CHAR(1),
     birthdate DATE,
     PRIMARY KEY (first_name, last_name)
  );
  

Algebraic Query Languages

• We next introduce the data manipulation aspects of the relational model.
• To start, we discuss the relational algebra that consists of a set of operations to construct new relations from a given relation.
• We use these constructed relations then to answer simple queries.
• Relational Algebra is not used today as a query language in commercial DBMSs, although some early systems used it directly.
• Still, SQL incorporates relational algebra at its core, and many SQL programs can be viewed as "syntactically sugared" expressions of relational algebra.
• We don't directly use C or Java to manipulate data in databases because they don't provide common useful primitives for extracting and manipulating data, even if these operations could be coded with a fair amount of work.
• An algebra, in general, consists of operators and atomic operands. For example, the algebra of arithmetic might allow constants like, 777, and variables like `x` as atomic operands, and allow us to build up expressions using `+`, `*`, `/`, such as `(x + y) *z`.
• For the relational algebra the variables stand for relations and the constants are particular finite relations.

Overview of the Relational Algebra

• The operations of the traditional relational algebra fall into four broad classes:
  1. The usual set operations: Union, intersection, and difference applied to relations.
  2. Operations that remove parts of a relation: "selections", which eliminates rows, and "projection", which eliminates columns.
  3. Operations that combine the tuples of two relations, including "Cartesian product" and "join".
  4. An operation called "renaming" that does not affect the tuples of a relation, but changes the name of the relation or the names of some of its attributes.
• Expressions in the relational algebra are referred to as queries.

Set Operations on Relations

The three most common operations on sets are union, intersection, and difference. For relations, we will use the following notations and definitions for their meaning:

• `R \cup S := { t | t \in R vv t \in S }`. A tuple `t` is in `R \cup S` iff it belongs to either `R` or to `S`.
• `R \cap S := { t | t \in R ^^ t \in S}`. A tuple `t` is in `R \cap S` iff it belongs to both `R` and `S`.
• `R - S := { t | t \in R ^^ t !in S}`. A tuple `t` is in `R - S` iff it belongs in `R` and not in `S`.

Projection

• The projection operator is used to produce from a relation `R`, a new relation that has only some of `R`'s columns
• The value of the expression `pi_{A_1, A_2, ..., A_n}(R)` is a relation that has only the columns for attributes `A_1, A_2, ..., A_n` of `R`.
• The schema for the resulting relation is the set of attributes `{A_1, A_2, ..., A_n}`.
• For example, consider the Movies(title, year, length, genre, studioName, producerC#) relation:

  {('Star Wars', 1977, 124, 'sciFi', 'Fox' 12345),
  ('Galaxy Quest', 1999, 104, 'comedy', 'DreamWorks', 67890),
  ('Waynes World', 1992, 95, 'comedy', 'Paramount', 99999)}.
  

• The result of `pi_{mbox(title),mbox(year),mbox(length)}(mbox(Movies))` is:

  {('Star Wars', 1977, 124),
  ('Galaxy Quest', 1999, 104),
  ('Waynes World', 1992, 95)}.
  

• The result of `pi_{mbox(genre)}(mbox(Movies))` is:

  {('sciFi'),
  ('comedy')}. //notice its a set, so only one copy of comedy
  

Selection

• The selection operator applied to a relation `R` produces a new relation with the subset of `R`'s tuples which match the selection condition.
• If `C` denotes the condition, then this would be written as `sigma_C(R)`.
• A condition can either be a comparison of an attribute of `R` to some value, for example, `mbox(length) ge 100`, or is built out of sub-conditions using AND, OR, and NOT, for example, `mbox(length) ge 100` AND `mbox(studioName)=\'mbox(Fox)\'`.
• The relation output by the selection operation:
  `sigma_{mbox(length) ge 100 mbox( AND ) mbox(studioName)=\'mbox(Fox)\'}(mbox(Movies))`
  using the Movies table of the previous slide is:

  {('Star Wars', 1977, 124, 'sciFi', 'Fox' 12345)}.