Announcements• Written Homework 1 due Nov 2– See course web page– Exercises 5.12, 5.15, 6.17, 6.20, 6.22 (a,c,f only). • Today– SQL (chapter 8)– Instructional environment overview• Where we are heading– SQL programming (chapter 9)– Database design (ch 10), practical design (ch 12),SQL• Structured Query Language (SQL)• SQL (and its variants) is supported by nearly every modern RDBMS• While RA is useful for gaining a theoretical understanding of relational model, commercial implementations are based on SQL– One of the first steps of query processing is to convert a SQL statement into a RA query treeA brief history of SQL• In 1970s, team at IBM implemented System R based on the relational model• SEQUEL (Structured English Query Language) was created to manipulate system R data• In late 1970s Oracle V2 was first to market• IBMs system 38 followed• Adopted as ANSI standard in ‘86 and ISO in ’87– “Official” pronunciation is “es-queue-el”– SQL3 (1999) last major update to standard• Currently vendors “self certify” compliance ;-)SQL, the reality• every vendor supports slightly different version of SQL• Thus, oracle SQL for example, will not be directly portable to say Microsoft SQLSQL is a Declarative language • An imperative language describes how to perform some task:– C, C++, java, relational algebra– “project( lname, join( EMPLOYEE, DEP, ssn == essn)))• A declarative language describes what the results are likenot how to create it– HTML, latex, SQL, tuple relational calculus– “The set of all last names of employees such that the SSN of that employee is the ESSN of at least one member of the dependent relation”SQL has multiple roles• Data definition language (DDN)– Eg, define relation schemas • Data control language (DCL)– Eg, security and authorization controls• Data manipulation language (DML)– Query for tuples– Insert, delete and modify tuples• SQL supports constraints, transactions & views• SQL standard does notsupport indexesSQL DDL statements• SQL has two key DDL commands– CREATE SCHEMA• Creates a database schema– CREATE TABLE• Define a relation created in the context of some database created with CREATE SCHEMA• Other DDL commands– CREATE VIEW– CREATE DOMAIN• Not in mySQL.CREATE SCHEMA• CREATE SCHEMA <db_name>– creates a DB with the given name• also called CREATE DATABASE in mySQL• example: – CREATE SCHEMA 557_test;CREATE TABLECREATE TABLE <schema_name>.<table_name>(col1_name col1_type,col2_name col2_type,...colM_name colM_type,constraint1,...,constraintN );example with no constraints:CREATE TABLE dept_locations (dnumber integer(4),dlocation varchar(15) );if schema name is omitted, table created in the default DBstatement ends with semi-colonAdding Constraints in CREATE TABLECREATE TABLE DEPT (DNAME VARCHAR(10) NOT NULL,DNUMBER INTEGER NOT NULL,MGRSSN CHAR(9),MGRSTARTDATE CHAR(9),PRIMARY KEY (DNUMBER),UNIQUE (DNAME),FOREIGN KEY (MGRSSN) REFERENCES EMP );other options: DEFAULT, CHECK, UNIQUEDnumber INT CHECK(Dnumber > 0 AND Dnumber < 21)CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,price DECIMAL,PRIMARY KEY(category, id)) ENGINE=INNODB;CREATE TABLE customer (id INT NOT NULL,PRIMARY KEY (id)) ENGINE=INNODB;CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,product_category INT NOT NULL,product_id INT NOT NULL,customer_id INT NOT NULL,PRIMARY KEY(no),INDEX (product_category, product_id),FOREIGN KEY (product_category, product_id)REFERENCES product(category, id)ON UPDATE CASCADE ON DELETE RESTRICT,INDEX (customer_id),FOREIGN KEY (customer_id)REFERENCES customer(id)) ENGINE=INNODB;Referential Triggered Action(from mySQL “help constraint”)defines “storage engine”Basic Data Types and Domains• Numeric– INT, FLOAT, DECIMAL(precision,scale)•String– char(n), varchar(n)• Boolean•Date•Timestamp• BLOB (binary large object)– tinyblob, mediumblob, largeblob• Enum– enum(‘red’,’blue’,’green’);•set– set( ‘red’, ‘blue’, ‘green’);Other DDL Commands• DROP TABLE– removes a table from the DB•ALTER TABLE– changes something about the table, add a column or constraint for example• relations declared through CREATE TABLE statements are called base tables – a physical file is kept for each base table• CREATE VIEW command creates virtual tables (later)Retrieval Queries in SQL• SQL has one basic statement for retrieving information from a database; the SELECT statement–This is not the same as the SELECT operation of the relational algebra• Important distinction between SQL and the formal relational model:– SQL allows a table (relation) to have two or more tuples that are identical in all their attribute values– Hence, an SQL relation (table) is a multi-set (sometimes called a bag) of tuples; it is not a set of tuples• SQL relations can be constrained to be sets by specifying PRIMARY KEY or UNIQUE attributes, or by using the DISTINCT option in a queryRetrieval Queries in SQL (contd.)•A bag or multi-set is like a set, but an element may appear more than once.– Example: {A, B, C, A} is a bag. {A, B, C} is also a bag that also is a set.– Bags also resemble lists, but the order is irrelevant in a bag.• Example:– {A, B, A} = {B, A, A} as bags– However, [A, B, A] is not equal to [B, A, A] as listsRetrieval Queries in SQL (contd.)• Basic form of the SQL SELECT statement is called a mapping or a SELECT-FROM-WHERE blockSELECT <attribute list>FROM <table list>WHERE <condition>– <attribute list> is a list of attribute names whose values are to be retrieved by the query– <table list> is a list of the relation names required to process the query– <condition> is a conditional (Boolean) expression that identifies the tuples to be retrieved by the queryRelational Database Schema--Figure 5.5Simple SQL Queries• Basic SQL queries correspond to using the following operations of the relational algebra:– SELECT– PROJECT– JOIN• All subsequent examples use the COMPANY databaseSimple SQL Queries (contd.)• Example of a simple query on one relation• Query 0: Retrieve the birthdate and address of the employee whose name is 'John B. Smith'.Q0: SELECT BDATE, ADDRESSFROM EMPLOYEEWHERE FNAME='John' AND MINIT='B’AND LNAME='Smith’– Similar to a SELECT-PROJECT pair of relational algebra operations:• The SELECT-clause specifies the projection attributes and the