Ch 4.ppt

Chapter 4 SQL: Data Definition, Constraints, and Basic Queries and Updates Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Chapter 4 Outline  SQL Data Definition and Data Types  Specifying Constraints in SQL  Basic Retri...

Chapter 4 SQL: Data Definition, Constraints, and Basic Queries and Updates Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Chapter 4 Outline  SQL Data Definition and Data Types  Specifying Constraints in SQL  Basic Retrieval Queries in SQL  INSERT, DELETE, and UPDATE Statements in SQL  Additional Features of SQL Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Basic SQL  SQL language  Considered one of the major reasons for the commercial success of relational databases  SQL  Structured Query Language  Statements for data definitions, queries, and updates (both DDL and DML)  Core specification  Plus specialized extensions Copyright © 2011 Ramez Elmasri and Shamkant Navathe  SQL Data Definition and Data Types  Terminology:  Table, row, and column used for relational model terms relation, tuple, and attribute  CREATE statement  Main SQL command for data definition Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Schema and Catalog Concepts in SQL  SQL schema  Identified by a schema name  Includes an authorization identifier and descriptors for each element  Schema elements include  Tables, constraints, views, domains, and other constructs  Each statement in SQL ends with a semicolon Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Schema and Catalog Concepts in SQL (cont’d.)  CREATE SCHEMA statement  CREATE SCHEMA COMPANY AUTHORIZATION ‘Jsmith’;  Catalog  Named collection of schemas in an SQL environment  SQL environment  Installation of an SQL-compliant RDBMS on a computer system Copyright © 2011 Ramez Elmasri and Shamkant Navathe The CREATE TABLE Command in SQL  Specify a new relation  Provide name  Specify attributes and initial constraints  Can optionally specify schema:  CREATE TABLE COMPANY.EMPLOYEE... or  CREATE TABLE EMPLOYEE... Copyright © 2011 Ramez Elmasri and Shamkant Navathe The CREATE TABLE Command in SQL (cont’d.)  Base tables (base relations)  Relation and its tuples are actually created and stored as a file by the DBMS  Virtual relations  Created through the CREATE VIEW statement Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Attribute Data Types and Domains in SQL  Basic data types  Numeric data types Integer numbers: INTEGER, INT, and SMALLINT Floating-point (real) numbers: FLOAT or REAL, and DOUBLE PRECISION  Character-string data types Fixed length: CHAR(n), CHARACTER(n) Varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Attribute Data Types and Domains in SQL (cont’d.)  Bit-string data types Fixed length: BIT(n) Varying length: BIT VARYING(n)  Boolean data type Values of TRUE or FALSE or NULL  DATE data type Ten positions Components are YEAR, MONTH, and DAY in the form YYYY-MM-DD Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Attribute Data Types and Domains in SQL (cont’d.)  Additional data types  Timestamp data type (TIMESTAMP) Includes the DATE and TIME fields Plus a minimum of six positions for decimal fractions of seconds. Optional WITH TIME ZONE qualifier  INTERVAL data type Specifies a relative value that can be used to increment or decrement an absolute value of a date, time, or timestamp Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe The CREATE TABLE Command in SQL (cont’d.)  Some foreign keys may cause errors  Specified either via: Circular references Or because they refer to a table that has not yet been created Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Attribute Data Types and Domains in SQL (cont’d.)  Domain  Name used with the attribute specification  Makes it easier to change the data type for a domain that is used by numerous attributes  Improves schema readability  Example: CREATE DOMAIN SSN_TYPE AS CHAR(9); Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Specifying Constraints in SQL  Basic constraints:  Key and referential integrity constraints  Restrictions on attribute domains and NULLs  Constraints on individual tuples within a relation Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Specifying Attribute Constraints and Attribute Defaults  NOT NULL  NULL is not permitted for a particular attribute  Default value  DEFAULT  CHECK clause  Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21); Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Specifying Key and Referential Integrity Constraints  PRIMARY KEY clause  Specifies one or more attributes that make up the primary key of a relation  Dnumber INT PRIMARY KEY;  UNIQUE clause  Specifies alternate (secondary) keys  Dname VARCHAR(15) UNIQUE; Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Specifying Key and Referential Integrity Constraints (cont’d.)  FOREIGN KEY clause  Default operation: reject update on violation  Attach referential triggered action clause Options include SET NULL, CASCADE, and SET DEFAULT Action taken by the DBMS for SET NULL or SET DEFAULT is the same for both ON DELETE and ON UPDATE CASCADE option suitable for “relationship” relations Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Giving Names to Constraints  Keyword CONSTRAINT  Name a constraint  Useful for later altering Copyright © 2011 Ramez Elmasri and Shamkant Navathe Specifying Constraints on Tuples Using CHECK  CHECK clauses at the end of a CREATE TABLE statement  Apply to each tuple individually  CHECK (Dept_create_date