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 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 4 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 Data Definition Language is normally done with user friendly tools like SQL Server Management Studio etc 5 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 Each statement in SQL ends with a semicolon with some exemptions 6 Schema and Catalog Concepts in SQL 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 7 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 8 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 9 CREATE TABLES for Company Data 10 CREATE TABLES for Company Data 11 CREATE TABLE Command Some foreign keys may cause errors Specified either via Circular references Or because they refer to a table that has not yet been created 12 Attribute Data Types and Domains in SQL Different dialects of SQL Microsoft Oracle IBM DB2 may have different types 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 13 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 14 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 15 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 16 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 18 Giving Names to Constraints Keyword CONSTRAINT Explicitly name a constraint Useful for later altering 19 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 20 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 21 DEFAULT Clause 22 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 23 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 24 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 26 The Structure of Basic SQL Queries Basic form of the SELECT statement 27 The Structure of Basic SQL Queries 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 28 Some Queries 29 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 30 Some Queries 31 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 32 Aliasing Renaming and Tuple Variables The SQL AS keyword Aliases or tuple variables Declare alternative relation names E and S EMPLOYEE AS E Fn Mi Ln Ssn Bd Addr Sex Sal Sssn Dno 33 Unspecified WHERE Clause and Use of the Asterisk Missing WHERE clause Indicates no condition on tuple selection CROSS PRODUCT All possible tuple combinations 34 Unspecified WHERE Clause and Use of the Asterisk Specify an asterisk Retrieve all the attribute values of the selected tuples 35 Tables as Sets in SQL SQL does not automatically eliminate duplicate tuples in query results Use the keyword DISTINCT in the SELECT clause Only distinct tuples should remain in the result 36 Tables as Sets in SQL Set operations UNION EXCEPT
View Full Document
Unlocking...