ch4 - Database System Concepts 5th Ed ©Silberschatz Korth...

Info iconThis preview shows pages 1–8. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Database System Concepts, 5th Ed . ©Silberschatz, Korth and Sudarshan See www.db-book.com for conditions on re-use Chapter 4: Advanced SQL Chapter 4: Advanced SQL ©Silberschatz, Korth and Sudarshan 4.2 Database System Concepts, 5 th Edition, Oct 5. 2006 Chapter 4: Advanced SQL 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** ©Silberschatz, Korth and Sudarshan 4.3 Database System Concepts, 5 th Edition, Oct 5. 2006 Built-in Data Types in SQL 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 ©Silberschatz, Korth and Sudarshan 4.4 Database System Concepts, 5 th Edition, Oct 5. 2006 Build-in Data Types in SQL (Cont.) 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 <string-valued-expression> as date ● Example: cast <string-valued-expression> as time ©Silberschatz, Korth and Sudarshan 4.5 Database System Concepts, 5 th Edition, Oct 5. 2006 User-Defined Types 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. ©Silberschatz, Korth and Sudarshan 4.6 Database System Concepts, 5 th Edition, Oct 5. 2006 Domain Constraints 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) ©Silberschatz, Korth and Sudarshan 4.7 Database System Concepts, 5 th Edition, Oct 5. 2006 Large-Object Types 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...
View Full Document

{[ snackBarMessage ]}

Page1 / 58

ch4 - Database System Concepts 5th Ed ©Silberschatz Korth...

This preview shows document pages 1 - 8. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online