Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
This course is about database management systems. Main topics covered in this course are: manipulate data, use standard query language, creating a database and logical query language. De fining a Database Schema, Create Table, Types, Integer, Real or Float, Varchar(n), Primary Key or Unique, Declaring Keys, Attributes, Changing Columns, Views, Querying Views, Optimize Query, Date, Time
Typology: Slides
1 / 16
This page cannot be seen from the preview
Don't miss anything!
De ning a Database Schema
CREATE TABLE name (list of elements).
Principal elements are attributes and their typ es, but key declarations and constraints also app ear.
Similar CREATE X commands for other schema elements X : views, indexes, assertions, triggers.
\DROP X name" deletes the created element of kind X with that name.
Example
CREATE TABLE Sells ( bar CHAR(20), beer VARCHAR(20), price REAL );
DROP TABLE Sells;
Typ es
Oracle Default Dates (Used at Stanford)
Format 'dd-mon-yy'
Behind the scenes (as stored in the relation), the value of a date eld has as much precision as the computer allows.
Example
CREATE TABLE Days ( d DATE );
INSERT INTO Days VALUES('06-nov-97 ');
Oracle function to date converts a sp eci ed format into default format. INSERT INTO Days VALUES(to date('2000-01-01', 'yyyy-mm-dd'));
Declaring Keys
Use PRIMARY KEY or UNIQUE.
Oracle treats these as synonyms.
But only one primary key, many \uniques" allowed.
SQL p ermits implementations to create an index (data structure to sp eed access given a key value) in resp onse to PRIMARY KEY only. F But Oracle creates indexes for b oth.
SQL do es not allow nulls in primary key, but allows them in \unique" columns (which may have two or more nulls, but not rep eated nonnull values).
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. F Sp eeds access | two heads are b etter than one. F Thus, you should follow any implicit index-creating statement like \primary key," by: USING INDEX TABLESPACE indx
Example
CREATE TABLE Beers ( name CHAR(20) UNIQUE USING INDEX TABLESPACE indx, manf CHAR(20) );
INSERT INTO Drinkers(name) VALUES('Sally')
results in the following tuple:
name addr phone Sally 123 Sesame St. NULL
Primary key is by default not NULL.
This insert is legal.
F OK to list a subset of the attributes and values for only this subset.
But if we had declared
phone CHAR(16) NOT NULL then the insertion could not b e made.
Changing Columns
Add an attribute of relation R with
ALTER TABLE R ADD
Example
ALTER TABLE Bars ADD phone CHAR(16) DEFAULT 'unlisted';
Columns may also b e dropp ed.
ALTER TABLE Bars DROP license;
Example
The view CanDrink is the set of drinker-b eer pairs such that the drinker frequents at least one bar that serves the b eer.
CREATE VIEW CanDrink AS SELECT drinker, beer FROM Frequents, Sells WHERE Frequents.bar = Sells.bar;
Querying Views
Treat the view as if it were a materialized relation.
Example
SELECT beer FROM CanDrink WHERE drinker = 'Sally';
Semantics of View Use
SQL query
SQL view def.
rel. algebra
rel. algebra
Example
dr ink er;beer
./
Frequents Sells
CanDrink
beer
dr ink er =^0 S ally 0
CanDrink
Query
Optimize Query
./
Sells
Frequents
beer
dr ink er =^0 S ally 0
