Structured Query LanguageSQLSection 3.pptVIP

Farkas CSCE 520 Structured Query LanguageSQLSection 3 Reading Assignments Database Systems The Complete Book: Chapters 6 and 7 Oracle9 Programming: finish Chapters 2 and 3.5 Following lecture slides are modified from Jeff Ullman’s slides for Fall 2002 -- Stanford Defining Database Schema Database Schema: declarations for the relations (tables) of the database Other components: Views Indexes Triggers Relation CREATE TABLE name (list of elements); DROP TABLE name; Elements of Table Declarations Pairs of: attribute attribute-type Most common types INT or INTEGER REAL or FLOAT CHAR(n): string of length n VARCHAR(n): variable length string up to length n Example CREATE TABLE Dog ( name VARCHAR(20), age INT, weight REAL, breed CHAR(10) ); Dates and Times DATE and TIME are types in SQL Forms: DATE ‘yyyy-mm-dd’ DATE ‘2003-03-18’ for March 18, 2003 TIME ‘hh:mm:ss’ with optional decimal point for second TIME ’11:00:00’ for 11 am Declaring Keys Attribute (or set of attributes) declared as PRIMARY KEY or UNIQUE Note, Keys functionally determine all other attributes of the relation schema Single-Attribute Key Place PRIMARY KEY or UNIQUE after the type of the attribute in the declaration Example CREATE TABLE Owner ( name CHAR (20), SSN INT UNIQUE, Phone CHAR (12) ); Multi-Attribute Keys Use key declaration: PRIMARY KEY in the CREATE TABLE statement Must be used for keys with more than one attribute Can be used for single-attribute keys too. Example Dog(name,age,weight,breed) CREATE TABLE Dog ( name VARCHAR(20), age INT, weight REAL, breed CHAR(10) PRIMARY KEY (name, breed) ); PRIMARY KEY v.s. UNIQUE SQL standards allow DBMS vendors to differentiate between PRIMARY KEY and UNIQUE For example: create index for PRIMARY KEY but not for UNIQUE Required Distinctions SQL standard requirements Only 1 PRIMARY KEY for a relations Several UNIQUE attributes are allowed PRIMARY KEY attributes cannot be NULL UNIQUE attributes may be NULL Other declarat