PowerPoint PresentationChapter 4 OutlineBasic SQLSQL Data Definition and Data TypesSchema and Catalog Concepts in SQLSchema and Catalog Concepts in SQL (cont’d.)The CREATE TABLE Command in SQLThe CREATE TABLE Command in SQL (cont’d.)Slide 9Slide 10Slide 11Attribute Data Types and Domains in SQLAttribute Data Types and Domains in SQL (cont’d.)Slide 14Slide 15Specifying Constraints in SQLSpecifying Attribute Constraints and Attribute DefaultsSlide 18Specifying Key and Referential Integrity ConstraintsSpecifying Key and Referential Integrity Constraints (cont’d.)Giving Names to ConstraintsSpecifying Constraints on Tuples Using CHECKBasic Retrieval Queries in SQLThe SELECT-FROM-WHERE Structure of Basic SQL QueriesThe SELECT-FROM-WHERE Structure of Basic SQL Queries (cont’d.)Slide 26Slide 27Ambiguous Attribute NamesAliasing, Renaming, and Tuple VariablesUnspecified WHERE Clause and Use of the AsteriskUnspecified WHERE Clause and Use of the Asterisk (cont’d.)Tables as Sets in SQLTables as Sets in SQL (cont’d.)Substring Pattern Matching and Arithmetic OperatorsOrdering of Query ResultsDiscussion and Summary of Basic SQL Retrieval QueriesINSERT, DELETE, and UPDATE Statements in SQLThe INSERT CommandThe DELETE CommandThe UPDATE CommandAdditional Features of SQLAdditional Features of SQL (cont’d.)SummaryCopyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-WesleyChapter 4Basic SQLCopyright © 2011 Ramez Elmasri and Shamkant NavatheChapter 4 OutlineSQL Data Definition and Data TypesSpecifying Constraints in SQLBasic Retrieval Queries in SQLINSERT, DELETE, and UPDATE Statements in SQLAdditional Features of SQLCopyright © 2011 Ramez Elmasri and Shamkant NavatheBasic SQLSQL language Considered one of the major reasons for the commercial success of relational databasesSQL Structured Query LanguageStatements for data definitions, queries, and updates (both DDL and DML)Core specificationPlus specialized extensionsCopyright © 2011 Ramez Elmasri and Shamkant NavatheSQL Data Definition and Data TypesTerminology:Table, row, and column used for relational model terms relation, tuple, and attributeCREATE statementMain SQL command for data definitionCopyright © 2011 Ramez Elmasri and Shamkant NavatheSchema and Catalog Concepts in SQLSQL schema Identified by a schema nameIncludes an authorization identifier and descriptors for each element Schema elements include Tables, constraints, views, domains, and other constructsEach statement in SQL ends with a semicolonCopyright © 2011 Ramez Elmasri and Shamkant NavatheSchema and Catalog Concepts in SQL (cont’d.)CREATE SCHEMA statementCREATE SCHEMA COMPANY AUTHORIZATION ‘Jsmith’;CatalogNamed collection of schemas in an SQL environmentSQL environmentInstallation of an SQL-compliant RDBMS on a computer systemCopyright © 2011 Ramez Elmasri and Shamkant NavatheThe CREATE TABLE Command in SQLSpecify a new relation Provide nameSpecify attributes and initial constraintsCan optionally specify schema:CREATE TABLE COMPANY.EMPLOYEE ...orCREATE TABLE EMPLOYEE ...Copyright © 2011 Ramez Elmasri and Shamkant NavatheThe 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 DBMSVirtual relationsCreated through the CREATE VIEW statementCopyright © 2011 Ramez Elmasri and Shamkant NavatheCopyright © 2011 Ramez Elmasri and Shamkant NavatheCopyright © 2011 Ramez Elmasri and Shamkant NavatheThe 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 createdCopyright © 2011 Ramez Elmasri and Shamkant NavatheAttribute Data Types and Domains in SQLBasic data typesNumeric data types •Integer numbers: INTEGER, INT, and SMALLINT•Floating-point (real) numbers: FLOAT or REAL, and DOUBLE PRECISIONCharacter-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 NavatheAttribute 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 NULLDATE data type •Ten positions•Components are YEAR, MONTH, and DAY in the form YYYY-MM-DDCopyright © 2011 Ramez Elmasri and Shamkant NavatheAttribute Data Types and Domains in SQL (cont’d.)Additional data typesTimestamp data type (TIMESTAMP)•Includes the DATE and TIME fields•Plus a minimum of six positions for decimal fractions of seconds•Optional WITH TIME ZONE qualifierINTERVAL data type•Specifies a relative value that can be used to increment or decrement an absolute value of a date, time, or timestampCopyright © 2011 Ramez Elmasri and Shamkant NavatheAttribute Data Types and Domains in SQL (cont’d.)Domain Name used with the attribute specificationMakes it easier to change the data type for a domain that is used by numerous attributes Improves schema readabilityExample:•CREATE DOMAIN SSN_TYPE AS CHAR(9);Copyright © 2011 Ramez Elmasri and Shamkant NavatheSpecifying Constraints in SQLBasic constraints:Key and referential integrity constraintsRestrictions on attribute domains and NULLsConstraints on individual tuples within a relationCopyright © 2011 Ramez Elmasri and Shamkant NavatheSpecifying Attribute Constraints and Attribute DefaultsNOT NULL NULL is not permitted for a particular attributeDefault valueDEFAULT <value> CHECK clauseDnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21);Copyright © 2011 Ramez Elmasri and Shamkant NavatheCopyright © 2011 Ramez Elmasri and Shamkant NavatheSpecifying Key and Referential Integrity ConstraintsPRIMARY KEY clause Specifies one or more attributes that make up the primary key of a relationDnumber INT PRIMARY KEY;UNIQUE clause Specifies alternate (secondary) keysDname VARCHAR(15) UNIQUE;Copyright © 2011 Ramez Elmasri and Shamkant NavatheSpecifying Key and Referential Integrity Constraints (cont’d.)FOREIGN KEY clauseDefault operation: reject update on violationAttach referential triggered action clause•Options include SET NULL, CASCADE, and SET DEFAULT•Action taken by the DBMS for SET NULL or SET
View Full Document
Unlocking...