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
SMART_READER_LITE
LIVE PREVIEW

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

Apr 14, 2023 684 likes •868 views

Dening 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

slide-1
SLIDE 1 Dening a Database Sc hema CREATE TABLE name (list
  • f
elemen ts).
  • Principal
elemen ts are attributes and their t yp es, but k ey declarations and constrain ts also app ear.
  • Similar
CREATE X commands for
  • ther
sc hema elemen ts X : views, indexes, assertions, triggers.
  • \DROP
X name" deletes the created elemen t
  • f
kind X with that name. Example CREATE TABLE Sells ( bar CHAR(20), beer VARCHAR(20), price REAL ); DROP TABLE Sells; 1
slide-2
SLIDE 2 T yp es 1. INT
  • r
INTEGER. 2. REAL
  • r
FLOAT. 3. CHAR(n) = xed length c haracter string, padded with \pad c haracters." 4. VARCHAR(n) = v ariable-lengt h strings up to n c haracters.

✦

Oracle uses VARCHAR2(n) as w ell. Dierence: storage for VARCHAR2 is truly v arying length; VARCHAR uses xed arra y with endmark er.

✦

VARCHAR in Oracle is \deprecated" (they ma y discon tin ue it in the future), so they suggest y
  • u
alw a ys use VARCHAR2. 2
slide-3
SLIDE 3 5. Dates. SQL form is DATE 'yyyy-mm-dd'

✦

Oracle uses a dieren t format | to b e explained. 6. Times. F
  • rm
is TIME 'hh:mm:ss[.ss...]' in SQL. 7. In Oracle: NUMBER is either in teger
  • r
  • ating
p
  • in
t as appropriate. 3
slide-4
SLIDE 4 Oracle Default Dates (Used at Stanford) F
  • rmat
'dd-mon-yy'
  • Behind
the scenes (as stored in the relation), the v alue
  • f
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 to date con v erts a sp ecied format in to default format. INSERT INTO Days VALUES(to date('2000-01-01', 'yyyy-mm-dd')); 4
slide-5
SLIDE 5 Declaring Keys Use PRIMARY KEY
  • r
UNIQUE.
  • Oracle
treats these as synon yms.
  • But
  • nly
  • ne
primary k ey , man y \uniques" allo w ed.
  • SQL
p ermits implemen tations to create an index (data structure to sp eed access giv en a k ey v alue) in resp
  • nse
to PRIMARY KEY
  • nly
.

✦

But Oracle creates indexes for b
  • th.
  • 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
  • r
more n ulls, but not rep eated nonn ull v alues). 5
slide-6
SLIDE 6 Declaring Keys Tw
  • 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
slide-7
SLIDE 7 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
  • n
a separate disk for indexes.

✦

Sp eeds access | t w
  • heads
are b etter than
  • ne.

✦

Th us, y
  • u
should follo w an y implicit index-creating statemen t lik e \primary k ey ," b y: USING INDEX TABLESPACE indx 7
slide-8
SLIDE 8 Example CREATE TABLE Beers ( name CHAR(20) UNIQUE USING INDEX TABLESPACE indx, manf CHAR(20) ); 8
slide-9
SLIDE 9 Other Prop erties Y
  • u
Can Giv e to A ttributes 1. NOT NULL = ev ery tuple m ust ha v e a real v alue for this attribute. 2. DEFAULT v alue = a v alue to use whenev er no
  • ther
v alue
  • f
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
slide-10
SLIDE 10 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
  • f
the attributes and v alues for
  • nly
this subset.
  • But
if w e had declared phone CHAR(16) NOT NULL then the insertion could not b e made. 10
slide-11
SLIDE 11 Changing Columns Add an attribute
  • f
relation R with ALTER TABLE R ADD <column declaration>; Example ALTER TABLE Bars ADD phone CHAR(16) DEFAULT 'unlisted';
  • Columns
ma y also b e dropp ed. ALTER TABLE Bars DROP license; 11
slide-12
SLIDE 12 Views An expression that describ es a table without creating it. R S V
  • View
denition form is: CREATE VIEW <name> AS <query>; 12
slide-13
SLIDE 13 Example The view CanDrink is the set
  • f
drink er-b eer pairs suc h that the drink er frequen ts at least
  • ne
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
slide-14
SLIDE 14 Seman tics
  • f
View Use SQL query SQL view def. rel. algebra rel. algebra SQL Example
  • dr
ink er ;beer . / F requen ts Sells CanDrink
  • beer
  • dr
ink er = S ally CanDrink Query 14
slide-15
SLIDE 15 Comp
  • se
  • dr
ink er ;beer . / F requen ts Sells
  • dr
ink er = S ally
  • beer
15
slide-16
SLIDE 16 Optimize Query 1. Push selections do wn tree. 2. Eliminate unnecessary pro jections. . / Sells F requen ts
  • beer
  • dr
ink er = S ally 16