csce520-lect10

csce520-lect10 - StructuredQueryLanguage SQL Section3...

Info iconThis preview shows pages 1–16. Sign up to view the full content.

View Full Document Right Arrow Icon
Structured Query Language SQL Section 3
Background image of page 1

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

View Full DocumentRight Arrow Icon
Farkas CSCE 520 2 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  
Background image of page 2
Farkas CSCE 520 3 Example Database Dog-Kennel database Dog(name ,age,weight,breed ) Kennel(name ,license#,address ,phone) Owner(name, SSN ,phone) Boards(K.name,K.address,D.name,D.breed ) Owns(O.SSN,D.name, D.breed) Pays(O.SSN,  K.name,K.address,amount)
Background image of page 3

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

View Full DocumentRight Arrow Icon
Farkas CSCE 520 4 Defining Database Schema Database Schema: declarations for the  relations (tables) of the database Other components: Views Indexes Triggers
Background image of page 4
Farkas CSCE 520 5 Relation CREATE TABLE <name>     (<list of elements>); DROP TABLE <name>;
Background image of page 5

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

View Full DocumentRight Arrow Icon
Farkas CSCE 520 6 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
Background image of page 6
Farkas CSCE 520 7 Example CREATE TABLE Dog ( name VARCHAR(20), age INT, weight REAL, breed CHAR(10) );
Background image of page 7

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

View Full DocumentRight Arrow Icon
Farkas CSCE 520 8 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
Background image of page 8
Farkas CSCE 520 9 Declaring Keys Attribute (or set of attributes) declared as  PRIMARY KEY or UNIQUE Note, Keys functionally determine all other  attributes of the relation schema
Background image of page 9

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

View Full DocumentRight Arrow Icon
Farkas CSCE 520 10 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) );
Background image of page 10
Farkas CSCE 520 11 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.
Background image of page 11

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

View Full DocumentRight Arrow Icon
Farkas CSCE 520 12 Example Dog(name ,age,weight,breed ) CREATE TABLE Dog (   name VARCHAR(20), age INT, weight REAL, breed CHAR(10)  PRIMARY KEY (name, breed)  );
Background image of page 12
Farkas CSCE 520 13 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
Background image of page 13

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

View Full DocumentRight Arrow Icon
Farkas CSCE 520 14 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
Background image of page 14
CSCE 520 15 Other declarations NOT NULL: value for this attribute may  never be NULL DEFAULT <value>, i.e., if no value is know  for this attribute’s component, use the  default <value>
Background image of page 15

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

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

This note was uploaded on 12/13/2011 for the course CSCE 522 taught by Professor Farkas during the Fall '11 term at South Carolina.

Page1 / 61

csce520-lect10 - StructuredQueryLanguage SQL Section3...

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

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