L07 - Relational Query Languages DDL, ICs, Updates, Views...

Info iconThis preview shows pages 1–9. 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 DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon

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

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

Unformatted text preview: Relational Query Languages DDL, ICs, Updates, Views in SQL CS174A: DDL, ICs, Updates, Views 2 SQL is More Than Just a QL ¡ Data-definition language ( DDL ): – Create / destroy / alter relations and views – Define integrity constraints ( IC ’s) ¡ Update language : – Insert /delete / modify (update) tuples – Interact closely with ICs ¡ Access Control : – Can grant / revoke the right to access and manipulate tables (relations / views) CS174A: DDL, ICs, Updates, Views 3 ¡ Creates the Boats relation with three fields, the names and types ¡ A small change: Reserves uses sname instead of sid ¡ No ICs have been specified (we’ll discuss this later) CREATE TABLE Boats (bid INTEGER , bname CHAR(10), color CHAR(10) ) CREATE TABLE Reserves (sname CHAR(10), bid INTEGER, day DATE ) Create Relations CS174A: DDL, ICs, Updates, Views 4 ¡ Destroys Boats . The schema information and the tuples are deleted ¡ The schema of Boats is altered by adding a new field; every tuple in the current instance is extended with a null value in the new field DROP TABLE Boats ALTER TABLE Boats ADD COLUMN boatkind: CHAR(10) Destroy and Alter Relations CS174A: DDL, ICs, Updates, Views 5 Creating and Deleting Indexes ¡ Creates a B +-tree index on Boats , with ( bname , color ) as the search key ¡ This statement is not included in the SQL standard! – Syntax usually differs slightly between systems – e.g., CREATE INDEX NameColorInd ON Boats WITH STRUCTURE = BTREE, KEY = (bname,color) ¡ To delete an index: CREATE INDEX NameColorInd ON Boats (bname , color) DROP INDEX NameColorInd CS174A: DDL, ICs, Updates, Views 6 Integrity Constraints (Review) ¡ An IC describes conditions that every legal instance of a relation must satisfy – Inserts/deletes/updates that violate IC’s are disallowed – Can be used to ensure application semantics (e.g., sid is a key), or prevent inconsistencies (e.g., sname has to be a string, age must be <200 ) ¡ Types of IC’s : – Domain constraints: Field values must be of right type. Always enforced – Primary key constraints, foreign key constraints, general constraints CS174A: DDL, ICs, Updates, Views 7 Primary and Candidate Keys (Review) CREATE TABLE Reserves ( sname CHAR(10), bid INTEGER, day DATE, PRIMARY KEY (sname, bid, day)) CREATE TABLE Reserves ( sname CHAR(10) NOT NULL, bid INTEGER, day DATE, PRIMARY KEY (bid, day), UNIQUE (sname)) ¡ Key for a relation: Minimal set of fields such that in any legal instance, two distinct tuples do not agree upon the key field values – Possibly many candidate keys (specified using UNIQUE ), one of which is chosen as the primary key – Primary key fields cannot contain null values CS174A: DDL, ICs, Updates, Views 8 CREATE TABLE Boats ( bid INTEGER, bname CHAR(10),...
View Full Document

This note was uploaded on 05/02/2010 for the course CS 174a taught by Professor Zhin during the Fall '09 term at UCSB.

Page1 / 25

L07 - Relational Query Languages DDL, ICs, Updates, Views...

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

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