DOC PREVIEW
UT Dallas CS 6360 - Ch04

This preview shows page 1-2-3-18-19-37-38-39 out of 39 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 39 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 39 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 39 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 39 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 39 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 39 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 39 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 39 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 39 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

PowerPoint PresentationChapter 4 OutlineBasic SQLSQL Data Definition and Data TypesThe CREATE TABLE Command in SQLThe CREATE TABLE Command in SQL (cont’d.)Slide 7Slide 8Slide 9Slide 10Attribute Data Types and Domains in SQLAttribute Data Types and Domains in SQL (cont’d.)Slide 13Specifying Constraints in SQLSpecifying Attribute Constraints and Attribute DefaultsSlide 16Specifying 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 24Slide 25Ambiguous 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 CommandSummaryCopyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-WesleyChapter 4Basic SQLCopyright © 2011 Ramez Elmasri and Shamkant NavatheChapter 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 SQLCopyright © 2011 Ramez Elmasri and Shamkant NavatheBasic 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)SQL is a declarative languageCopyright © 2011 Ramez Elmasri and Shamkant NavatheSQL 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 definitionCopyright © 2011 Ramez Elmasri and Shamkant NavatheThe 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 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 DBMSVirtual relationsCreated through the CREATE VIEW statementCopyright © 2011 Ramez Elmasri and Shamkant NavatheCopyright © 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 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 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 NULLDATE 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.)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 NavatheSpecifying Constraints in SQLBasic constraints:Key and referential integrity constraintsRestrictions on attribute domains and NULLsConstraints on individual tuples within a relationCopyright © 2011 Ramez Elmasri and Shamkant NavatheSpecifying 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);Copyright © 2011 Ramez Elmasri and Shamkant NavatheCopyright © 2011 Ramez Elmasri and Shamkant NavatheSpecifying 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 NavatheSpecifying 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” relationsCopyright © 2011 Ramez Elmasri and Shamkant NavatheGiving Names to ConstraintsKeyword CONSTRAINTName a constraintUseful for later alteringCopyright © 2011 Ramez Elmasri and Shamkant NavatheSpecifying 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);Copyright © 2011 Ramez Elmasri and Shamkant NavatheBasic 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Multiset or bag behaviorCopyright © 2011 Ramez Elmasri and Shamkant NavatheThe SELECT-FROM-WHERE Structure of Basic SQL QueriesBasic form of the SELECT statement:Copyright © 2011 Ramez Elmasri and Shamkant NavatheThe SELECT-FROM-WHERE Structure of Basic SQL Queries (cont’d.)Logical comparison operators=, <, <=, >, >=, and <>Projection attributesAttributes whose values are to be retrievedSelection


View Full Document

UT Dallas CS 6360 - Ch04

Documents in this Course
Ch22(1)

Ch22(1)

44 pages

Ch21

Ch21

38 pages

Ch19

Ch19

46 pages

Ch18

Ch18

25 pages

Ch17

Ch17

21 pages

Ch15

Ch15

42 pages

Ch09

Ch09

42 pages

Ch05

Ch05

34 pages

Ch22

Ch22

45 pages

Ch21

Ch21

38 pages

Ch19

Ch19

48 pages

Ch18

Ch18

24 pages

Ch17

Ch17

22 pages

Ch16

Ch16

17 pages

Ch15

Ch15

42 pages

Ch09

Ch09

42 pages

Ch08

Ch08

39 pages

Ch07

Ch07

34 pages

Ch06

Ch06

43 pages

Ch05

Ch05

34 pages

Ch03(2)

Ch03(2)

36 pages

Ch02

Ch02

33 pages

Ch08

Ch08

28 pages

Ch07

Ch07

31 pages

Ch06

Ch06

43 pages

Ch05

Ch05

39 pages

Ch04(1)

Ch04(1)

39 pages

Ch03(1)

Ch03(1)

38 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch24

Ch24

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

48 pages

Ch18

Ch18

24 pages

Ch17

Ch17

22 pages

Ch03(1)

Ch03(1)

38 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch24

Ch24

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

48 pages

Ch18

Ch18

24 pages

Ch17

Ch17

22 pages

Ch08

Ch08

28 pages

Ch07

Ch07

31 pages

Ch06

Ch06

43 pages

Ch05

Ch05

39 pages

Ch04(1)

Ch04(1)

39 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04

Ch04

43 pages

Ch03

Ch03

41 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch18

Ch18

24 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04

Ch04

43 pages

Ch03

Ch03

41 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch18

Ch18

24 pages

Ch17

Ch17

25 pages

lab-manual

lab-manual

215 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04

Ch04

43 pages

Ch03

Ch03

41 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch17

Ch17

25 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch18

Ch18

24 pages

Ch17

Ch17

25 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04

Ch04

43 pages

Ch03

Ch03

41 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch04(1)

Ch04(1)

43 pages

Ch07

Ch07

40 pages

Ch03

Ch03

42 pages

Ch01

Ch01

36 pages

Ch02

Ch02

38 pages

Ch05

Ch05

41 pages

Ch06

Ch06

47 pages

Ch08

Ch08

39 pages

Ch17

Ch17

25 pages

Ch18

Ch18

24 pages

Ch09

Ch09

42 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch18

Ch18

24 pages

Ch17

Ch17

25 pages

Ch09

Ch09

42 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04(1)

Ch04(1)

43 pages

Ch03

Ch03

42 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Load more
Download Ch04
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Ch04 and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Ch04 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?