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. The buffer manager's task is to bring appropriate portions of the data from secondary storage (disk) where it is kept permanently, to the main memory buffers.
2. The ACID Properties of a transaction are mainly concerned with how fast a transaction should operate.
3. The Semistructured Model organizes data into relations.

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.