Dening a Database Sc hema name (list of elemen ts). CREATE - PDF document

De�ning a Database Sc hema name (list of elemen ts). CREATE TABLE Principal elemen ts are attributes and their � t yp es, but k ey declarations and constrain ts also app ear. Similar commands for other sc hema CREATE X � elemen ts : views, indexes, assertions, X triggers. \ DROP name" deletes the created elemen t of X � kind with that name. X Example CREATE TABLE Sells ( bar CHAR(20), beer VARCHAR(20), price REAL ); DROP TABLE Sells; 1

T yp es 1. or INTEGER . INT 2. or FLOAT . REAL 3. = �xed length c haracter string, CHAR( n ) padded with \pad c haracters." 4. = v ariable-lengt h strings up to VARCHAR( n ) n c haracters. ✦ Oracle uses as w ell. VARCHAR2( n ) Di�erence: storage for is truly VARCHAR2 v arying length; uses �xed arra y VARCHAR with endmark er. ✦ in Oracle is \deprecated" (they VARCHAR ma y discon tin ue it in the future), so they suggest y ou alw a ys use VARCHAR2 . 2

5. Dates. SQL form is DATE 'yyyy-mm-dd' ✦ Oracle uses a di�eren t format | to b e explained. 6. Times. F orm is in TIME 'hh:mm:ss[.ss...]' SQL. 7. In Oracle: is either in teger or �oating NUMBER p oin t as appropriate. 3

Oracle Default Dates (Used at Stanford) F ormat 'dd-mon-yy' Behind the scenes (as stored in the relation), � the v alue of a date �eld has as m uc h precision as the computer allo ws. Example CREATE TABLE Days ( d DATE ); INSERT INTO Days VALUES('06-nov-97 '); Oracle function con v erts a sp eci�ed to date � format in to default format. INSERT INTO Days VALUES(to date('2000-01-01', 'yyyy-mm-dd')); 4

Declaring Keys Use or UNIQUE . PRIMARY KEY Oracle treats these as synon yms. � But only one primary k ey , man y \uniques" � allo w ed. SQL p ermits implemen tations to create an � (data structure to sp eed access giv en a index k ey v alue) in resp onse to only . PRIMARY KEY ✦ But Oracle creates indexes for b oth. SQL do es not allo w n ulls in primary k ey , � but allo ws them in \unique" columns (whic h ma y ha v e t w o or more n ulls, but not rep eated nonn ull v alues). 5

Declaring Keys Tw o places to declare: 1. After an attribute's t yp e, if the attribute is a k ey b y itself. 2. As a separate elemen t. ✦ Essen tial if k ey is 1 attribute. > 6

Example CREATE TABLE Sells ( bar CHAR(20), beer VARCHAR(20), price REAL, PRIMARY KEY(bar,beer) ); On the Stanford Oracle system for this class, � there is a separate data area on a separate disk for indexes. ✦ Sp eeds access | t w o heads are b etter than one. ✦ Th us, y ou should follo w an y implicit index-creating statemen t lik e \primary k ey ," b y: USING INDEX TABLESPACE indx 7

Example CREATE TABLE Beers ( name CHAR(20) UNIQUE USING INDEX TABLESPACE indx, manf CHAR(20) ); 8

Other Prop erties Y ou Can Giv e to A ttributes 1. = ev ery tuple m ust ha v e a real v alue NOT NULL for this attribute. 2. v alue = a v alue to use whenev er no DEFAULT other v alue of this attribute is kno wn. Example CREATE TABLE Drinkers ( name CHAR(30) PRIMARY KEY USING INDEX TABLESPACE indx, addr CHAR(50) DEFAULT '123 Sesame St', phone CHAR(16) ); 9

INSERT INTO Drinkers(name) VALUES('Sally') results in the follo wing tuple: name addr phone Sally 123 Sesame St. NULL Primary k ey is b y default not NULL . � This insert is legal. � ✦ OK to list a subset of the attributes and v alues for only this subset. But if w e had declared � phone CHAR(16) NOT NULL then the insertion could not b e made. 10

Changing Columns Add an attribute of relation with R < column declaration > ; ALTER TABLE R ADD Example ALTER TABLE Bars ADD phone CHAR(16) DEFAULT 'unlisted'; Columns ma y also b e dropp ed. � ALTER TABLE Bars DROP license; 11

Views An expression that describ es a table without creating it. V R S View de�nition form is: � < name > CREATE VIEW AS < query > ; 12

Example The view is the set of drink er-b eer pairs CanDrink suc h that the drink er frequen ts at least one bar that serv es the b eer. CREATE VIEW CanDrink AS SELECT drinker, beer FROM Frequents, Sells WHERE Frequents.bar = Sells.bar; Querying Views T reat the view as if it w ere a materialized relation. Example SELECT beer FROM CanDrink WHERE drinker = 'Sally'; 13

Seman tics of View Use SQL query rel. algebra SQL SQL view def. rel. algebra Example � � dr ink er ;beer beer . / � dr ink er 0 S ally 0 = F requen ts Sells CanDrink CanDrink Query 14

Comp ose � beer � dr ink er 0 S ally 0 = � dr ink er ;beer . / F requen ts Sells 15

Optimize Query 1. Push selections do wn tree. 2. Eliminate unnecessary pro jections. � beer . / Sells � dr ink er 0 S ally 0 = F requen ts 16