Chapter 4 Basic SQL CS 6360 Database Design Chris Irwin Davis Ph D Email cid021000 utdallas edu Phone 972 883 3574 O ce ECSS 4 705 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 SQL 2 4 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 constructs 7 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 system 9 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 statement 11 CREATE TABLES for Company Data 12 CREATE TABLES for Company Data 13 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 created 14 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 SQLite 15 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 YYYYMM DD 17 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 timestamp 18 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 19 4 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 relation 21 Giving Names to Constraints Keyword CONSTRAINT Explicitly name a constraint Useful for later altering 22 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 Clause 25 18 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 relations 27 4 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 behavior 29 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 Queries 32 Some Queries 33 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 Some Queries 35 Some Queries 36 Ambiguous Attribute Names Same name can be used for two or more attributes As long as the attributes are in different relations Must qualify the attribute name with the relation name to prevent ambiguity 37 Aliasing Renaming and Tuple Variables The SQL AS keyword Attribute or Table Aliases Examples SELECT attribute AS attr alias name FROM table name AS table alias name FROM subquery AS virtual table name CAVEAT SQL syntax allows AS to be implied A
View Full Document
Unlocking...