Chapter 4:Advanced SQL SQL Data Types and Schemas Integrity Constraints Authorization Embedded SQL Dynamic SQL Functions and Procedural Constructs** Recursive Queries** Advanced SQL Features** Database System Concepts,5th Edition,Oct 5.2006 4.2 @Silberschatz,Korth and Sudarshan
Database System Concepts, 5th Edition, Oct 5. 2006 4.2 ©Silberschatz, Korth and Sudarshan Chapter 4: Advanced SQL SQL Data Types and Schemas Integrity Constraints Authorization Embedded SQL Dynamic SQL Functions and Procedural Constructs** Recursive Queries** Advanced SQL Features**
Built-in Data Types in SQL date:Dates,containing a(4 digit)year,month and date Example:date 2005-7-27' time:Time of day,in hours,minutes and seconds. Example:time '09:00:30' time‘09:00:30.75 timestamp:date plus time of day Example:timestamp 2005-7-27 09:00:30.75' interval:period of time Example:interval '1'day Subtracting a date/time/timestamp value from another gives an interval value Interval values can be added to date/time/timestamp values Database System Concepts,5th Edition,Oct 5.2006 4.3 @Silberschatz,Korth and Sudarshan
Database System Concepts, 5th Edition, Oct 5. 2006 4.3 ©Silberschatz, Korth and Sudarshan Built-in Data Types in SQL date: Dates, containing a (4 digit) year, month and date Example: date ‘2005-7-27’ time: Time of day, in hours, minutes and seconds. Example: time ‘09:00:30’ time ‘09:00:30.75’ timestamp: date plus time of day Example: timestamp ‘2005-7-27 09:00:30.75’ interval: period of time Example: interval ‘1’ day Subtracting a date/time/timestamp value from another gives an interval value Interval values can be added to date/time/timestamp values
Build-in Data Types in SQL (Cont.) Can extract values of individual fields from date/time/timestamp Example:extract (year from r.starttime) Can cast string types to date/time/timestamp Example:cast as date Example:cast as time Database System Concepts,5th Edition,Oct 5.2006 4.4 Silberschatz,Korth and Sudarshan
Database System Concepts, 5th Edition, Oct 5. 2006 4.4 ©Silberschatz, Korth and Sudarshan Build-in Data Types in SQL (Cont.) Can extract values of individual fields from date/time/timestamp Example: extract (year from r.starttime) Can cast string types to date/time/timestamp Example: cast as date Example: cast as time
User-Defined Types create type construct in SQL creates user-defined type create type Dollars as numeric(12,2)final create domain construct in SQL-92 creates user-defined domain types create domain person_name char(20)not null Types and domains are similar.Domains can have constraints,such as not null,specified on them. Database System Concepts,5th Edition,Oct 5.2006 4.5 @Silberschatz,Korth and Sudarshan
Database System Concepts, 5th Edition, Oct 5. 2006 4.5 ©Silberschatz, Korth and Sudarshan User-Defined Types create type construct in SQL creates user-defined type create type Dollars as numeric (12,2) final create domain construct in SQL-92 creates user-defined domain types create domain person_name char(20) not null Types and domains are similar. Domains can have constraints, such as not null, specified on them
Domain Constraints Domain constraints are the most elementary form of integrity constraint.They test values inserted in the database,and test queries to ensure that the comparisons make sense. New domains can be created from existing data types Example:create domain Dollars numeric(12,2) create domain Pounds numeric(12,2) We cannot assign or compare a value of type Dollars to a value of type Pounds. However,we can convert type as below (cast r.A as Pounds) (Should also multiply by the dollar-to-pound conversion-rate) Database System Concepts,5th Edition,Oct 5.2006 4.6 Silberschatz,Korth and Sudarshan
Database System Concepts, 5th Edition, Oct 5. 2006 4.6 ©Silberschatz, Korth and Sudarshan Domain Constraints Domain constraints are the most elementary form of integrity constraint. They test values inserted in the database, and test queries to ensure that the comparisons make sense. New domains can be created from existing data types Example: create domain Dollars numeric(12, 2) create domain Pounds numeric(12,2) We cannot assign or compare a value of type Dollars to a value of type Pounds. However, we can convert type as below (cast r.A as Pounds) (Should also multiply by the dollar-to-pound conversion-rate)
Large-Object Types Large objects(photos,videos,CAD files,etc.)are stored as a large object: blob:binary large object--object is a large collection of uninterpreted binary data(whose interpretation is left to an application outside of the database system) clob:character large object--object is a large collection of character data When a query returns a large object,a pointer is returned rather than the large object itself. Database System Concepts,5th Edition,Oct 5.2006 4.7 Silberschatz,Korth and Sudarshan
Database System Concepts, 5th Edition, Oct 5. 2006 4.7 ©Silberschatz, Korth and Sudarshan Large-Object Types Large objects (photos, videos, CAD files, etc.) are stored as a large object: blob: binary large object -- object is a large collection of uninterpreted binary data (whose interpretation is left to an application outside of the database system) clob: character large object -- object is a large collection of character data When a query returns a large object, a pointer is returned rather than the large object itself
Integrity Constraints Integrity constraints guard against accidental damage to the database,by ensuring that authorized changes to the database do not result in a loss of data consistency. A checking account must have a balance greater than $10,000.00 A salary of a bank employee must be at least $4.00 an hour A customer must have a(non-null)phone number Database System Concepts,5th Edition,Oct 5.2006 4.8 Silberschatz,Korth and Sudarshan
Database System Concepts, 5th Edition, Oct 5. 2006 4.8 ©Silberschatz, Korth and Sudarshan Integrity Constraints Integrity constraints guard against accidental damage to the database, by ensuring that authorized changes to the database do not result in a loss of data consistency. A checking account must have a balance greater than $10,000.00 A salary of a bank employee must be at least $4.00 an hour A customer must have a (non-null) phone number
Constraints on a Single Relation not null primary key unique check(P),where Pis a predicate Database System Concepts,5th Edition,Oct 5.2006 4.9 @Silberschatz,Korth and Sudarshan
Database System Concepts, 5th Edition, Oct 5. 2006 4.9 ©Silberschatz, Korth and Sudarshan Constraints on a Single Relation not null primary key unique check (P ), where P is a predicate
Not Null Constraint Declare branch name for branch is not null branch_name char(15)not null Declare the domain Dollars to be not null create domain Dollars numeric(12,2)not null Database System Concepts,5th Edition,Oct 5.2006 4.10 Silberschatz,Korth and Sudarshan
Database System Concepts, 5th Edition, Oct 5. 2006 4.10 ©Silberschatz, Korth and Sudarshan Not Null Constraint Declare branch_name for branch is not null branch_name char(15) not null Declare the domain Dollars to be not null create domain Dollars numeric(12,2) not null
The Unique Constraint unique(A1,A2,....Am) The unique specification states that the attributes A1,A2,...Am form a candidate key. Candidate keys are permitted to be null (in contrast to primary keys). Database System Concepts,5th Edition,Oct 5.2006 4.11 Silberschatz,Korth and Sudarshan
Database System Concepts, 5th Edition, Oct 5. 2006 4.11 ©Silberschatz, Korth and Sudarshan The Unique Constraint unique ( A1 , A2 , …, Am) The unique specification states that the attributes A1, A2, … Am form a candidate key. Candidate keys are permitted to be null (in contrast to primary keys)