!" #Chris Irwin Davis, Ph.D. Email: [email protected] Phone: (972) 883-3574 Office: ECSS 4.705Chapter 4: Basic SQLCS-6360 Database Design!" #Chapter 4 Outline•4.1 –!SQL Data Definition and Data Types•4.2 –!Specifying Constraints in SQL•4.3 –!Basic Retrieval Queries in SQL•4.4 –!INSERT, DELETE, and UPDATE Statements in SQL•4.5 –!Additional Features of SQL24.1 –!SQL Data Definition and Data Types!" #Basic SQL•SQL (Structured Query Language) °Considered one of the major reasons for the commercial success of relational databases°Appeared 1974°Last stable release SQL:2011°Core specification - Standards°ANSI (since 1986)°ISO/IEC 9075 (since 1987)4!" #Basic SQL•Statements for data definitions, queries, and updates•DDL, DML, and VDL•Plus specialized extensions (which may be implementation specific)5!" #Basic SQL•SQL language is case insensitive•keywords•namespaces•SQL data values are case sensitive•For readability, some case style conventions may be used•Each statement in SQL ends with a semicolon•with some exemptions (e.g. USE)6!" #Schema and Catalog Concepts in SQL•SQL schema (In most systems, a Database)°Identified by a schema name°Includes an authorization identifier and descriptors for each element •Schema elements include °Tables°Constraints°Views°Domains°and other constructs7!" #SQL Terminology•Terminology:°Table, row, and column used for relational model terms relation, tuple, and attribute•CREATE statement°Main SQL command for data definition•Much of what you’ll see in the Data Definition Language is normally done with user-friendly tools like SQL Server Management Studio, etc.8!" #Schema and Catalog Concepts in SQL•CREATE SCHEMA statement°CREATE SCHEMA schema_name [auth]; °e.g.!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 system9!" #The CREATE TABLE Command in SQL•Specify a new relation (table)°Provide name°Specify attributes and initial constraints•Can optionally specify schema:°CREATE TABLE COMPANY.EMPLOYEE ...or°CREATE TABLE EMPLOYEE ...10!" #The CREATE TABLE Command in SQL•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 statement11!" #CREATE TABLES for Company Data12!" #CREATE TABLES for Company Data13!" #CREATE TABLE Command•Does the order of table creation matter?•Some foreign keys may cause errors °Specified either via: •Circular references •Or because they refer to a table that has not yet been created14!" #Attribute Data Types and Domains in SQL•Different dialects of SQL may have different types°Microsoft SQL Server°Oracle 11g, 10g, XE, etc.°MySQL°IBM DB2°PostgreSQL°SQLite15!" #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)16!" #Attribute Data Types and Domains in SQL°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 (Use DateTime instead)•Ten positions•Components are YEAR, MONTH, and DAY in the form YYYY-MM-DD17!" #Attribute Data Types and Domains in SQL•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 timestamp18!" #Attribute Data Types and Domains in SQL•Custom 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);194.2 –!Specifying Constraints in SQL!" #Specifying Constraints in SQL•Basic constraints:°Key and referential integrity constraints°Restrictions on attribute domains and NULLs°Constraints on individual tuples within a relation21!" #Giving Names to Constraints•Keyword CONSTRAINT°Explicitly name a constraint°Useful for later altering22!" #Specifying Attribute Constraints and Attribute Defaults•NOT NULL °NULL is not permitted for a particular attribute•Default value°DEFAULT <value> •CHECK clause°Dnumber INT NOT NULL CHECK ! (Dnumber > 0 AND Dnumber < 21);23!" #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 <= Mgr_start_date);24!" #DEFAULT Clause2518!" #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;26!" #Specifying Key and Referential Integrity Constraints•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” relations274.3 –!Basic Retrieval Queries in SQL!" #Basic Retrieval Queries in SQL•SELECT statement°One basic statement for retrieving information from a database•SQL allows a table to have two or more tuples that are identical in all their attribute values°Unlike relational model°Multi-set or bag behavior29!" #The Structure of Basic SQL Queries§Basic form of the SELECT statement:30!" #•Projection attributes°SELECT °Attributes whose values are to be retrieved•Selection condition°WHERE°Boolean condition that must be true for any retrieved tuple•Logical comparison operators°=, <, <=, >, >=, and <>31!" #Some Queries32!" #Some Queries33!" #• Query 1• The condition Dnumber = Dno is called a join condition, because it combines two tuples: one from DEPARTMENT and one from EMPLOYEE, whenever the value of Dnumber in DEPARTMENT is equal to the value of Dno in EMPLOYEE.34!"
View Full Document
Unlocking...