1 / 17

Chapter 4: Advanced SQL (1)

Chapter 4: Advanced SQL (1). -- Introduction to database principles Maoying Wu ( bi203.sjtu@gmail.com ) March 25, 2013. Outline. SQL : Data Types Integrity Constraints. Built-in Data Types. date: Dates, containing 4-digit year, month and date date ‘2005-7-27’

renate
Download Presentation

Chapter 4: Advanced SQL (1)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 4: Advanced SQL (1) -- Introduction to database principles Maoying Wu (bi203.sjtu@gmail.com) March 25, 2013

  2. Outline • SQL: Data Types • Integrity Constraints

  3. Built-in Data Types • date: Dates, containing 4-digit year, month and date • date ‘2005-7-27’ • time: Time of day, in hours, minutes and seconds • time ’09:00:30’’09:00:30.75’ • timestamp: date plus time of day • timestamp ‘2005-7-27 09:30:127.5’ • interval: period of time • interval ‘1’day • The difference between 2 date / time / timestamp values • interval values can be added to date / time / timestamp values

  4. Built-in Data Types in SQL (Cont.) • extract(year fromr.starttime) • extract values of individual fields from date/time/timestamp • cast <string-valued-expression> as date/time • cast string data to date/time/timestamp

  5. User-Defined Types • CREATE TYPE construct in SQL creates a user-defined type • CREATE TYPE dollarsAS numeric(12,2) FINAL • CREATE DOMAIN construct in SQL-92 creates user-defined domain type • CREATE DOMAIN person_namechar(20) NOTNULL • Types and domains are similar. Domains can have constraints, such as NOT NULL, specified on them.

  6. Domain Constraints • Most elementary form of integrity constraint • Create domains • CREATE DOMAIN Dollar numeric(12,2); • CREATE DOMAIN Pound numeric(12,2); • We cannot assign or compare values of different domains • however, we can use cast to convert • (CAST r.A as Pound)

  7. Large-object types • Large object include photos, videos, CAD files, etc. • blob: binary large object • clob: character large object • When a query returns a large object, a pointer is returned rather than the large object itself

  8. 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 be of balance more than $10,000.00 • The lowest salary for a bank employee must be $10.00 an hour • A customer must have a non-null phone number

  9. Constraints on a single relation • NOT NULL • PRIMARY KEY • UNIQUE • CHECK(p), where p is a predicate

  10. NOT NULL • branch_namechar(15) NOT NULL • CREATE DOMAIN Dollarnumeric(12,2) NOT NULL

  11. UNIQUE • UNIQUE (A1, A2, …, Am) • UIQUE statements specifies that the attributes • (A1, A2, …, Am) form a candidate key • NULL values are allow for candidate keys (in contrast to primary keys)

  12. ENTITY INTEGRITY • PRIMARY KEY must be NOT NULL

  13. CHECK(p) • check(p), where p is a predicate CREATE TABLE branch (branch_name char(15), branch_city char(30), assets integer, primary key (branch_name), check (assets >= 0); CREATE DOMAIN hourly_wage numeric(5,2) [constraint value_test ] check (value >= 10.00);

  14. Referential Integrity • create table customer (customer_name char(20), customer_street char(30), customer_city char(30), primary key (customer_name )) • create table branch (branch_name char(15), branch_city char(30), assets numeric(12,2), pr • create table account (account_number char(10), branch_name char(15), balance integer, primary key (account_number), foreign key (branch_name) references branch ) • create table depositor (customer_name char(20), account_number char(10), primary key (customer_name, account_number), foreign key (account_number ) references account, foreign key (customer_name ) references customer );

  15. Assertions • An assertion is a predicate • An assertion in SQL takes the form create assertion <assertion-name> check <predicate>  • When an assertion is made, the system tests it for validity, and tests it against every update • Asserting for all X, P(X) is achieved in a round-about fashion using not exists X such that not P(X)

  16. Assertion: Example • Every loan has at least one borrower who maintains an account with a minimum balance of $1000.00 create assertionbalance_constraint check (not exists ( select * from loan where not exists ( select * from borrower, depositor, account where loan.loan_number = borrower.loan_number and borrower.customer_name = depositor.customer_name and depositor.account_number = account.account_number and account.balance >= 1000)))

  17. End!

More Related