note6-sql - CSc134 DatabaseManagementand FileOrganization...

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

View Full Document Right Arrow Icon
1 CSc 134 Database Management and  File Organization 6. SQL Ying Jin Computer Science Department California state University, Sacramento
Background image of page 1

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

View Full DocumentRight Arrow Icon
2 SQL History SQL-86 (SQL 1) SQL-92 (SQL 2) SQL-99 (SQL 3) Features A comprehensive database language: data definition,  query, update. Both DDL and DML Define views Specify security and authorization Specify integrity constraints
Background image of page 2
3 CREATE TABLE Specifies a new base relation by giving it a  name, and specifying each of its attributes  and their data types  A constraint NOT NULL may be specified on  an attribute CREATE TABLE   DEPARTMENT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9)   ...);
Background image of page 3

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

View Full DocumentRight Arrow Icon
4 Attribute Data Types and  Domains in SQL Numeric INTEGER or INT FLOAT or REAL DECIMAL(i,j), or DEC(i,j), or NUMBERIC(i,j) i: total number of decimal digits j: number of digits after the decimal point Character-string fixed length CHAR(n) or CHARACTER(n) varying length VARCHAR(n)
Background image of page 4
5 Attribute Data Types and  Domains in SQL (Cont.) Boolean TRUE, FALSE Data DATA: year, month, day in the form YYYY-MM-DD TIME: hour, minute, second in the form  HH:MM:SS CREATE DOMIAN  SSN_TYPE  AS  CHAR(9); ( . ... MGRSSN SSN_TYPE, ...)
Background image of page 5

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

View Full DocumentRight Arrow Icon
6 CREATE TABLE (Cont.) Specify primary key  Referential integrity constraints (foreign keys).   Key attributes PRIMARY KEY  UNIQUE phrases CREATE TABLE   DEPARTMENT (   DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL CHECK  (DNUMBER>0 AND DNUMBER <21), MGRSSN CHAR(9), MGRSTARTDATE DATE, PRIMARY KEY (DNUMBER), UNIQUE (DNAME), FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN)    );
Background image of page 6
7 REFERENTIAL INTEGRITY  OPTIONS We can specify CASCADE, SET NULL or SET  DEFAULT on referential integrity constraints  CREATE TABLE   EMPLOYEE ( ... DNOINT NOT NULL  DEFAULT 1, ... PRIMARY KEY (SSN), FOREIGN KEY (SUPERSSN) REFERENCES  EMPLOYEE(SSN) ON DELETE SET NULL  ON UPDATE CASCADE, FOREIGN KEY (DNO) REFERENCES  DEPARTMENT(DNUMBER) ON DELETE SET DEFAULT  
Background image of page 7

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

View Full DocumentRight Arrow Icon
8 Giving Names to Constraints CREATE TABLE   EMPLOYEE ( SSN CHAR(9), ... CONSTRAINT  EMPPK PRIMARY KEY (SSN), CONSTRAINT EMPDEPTFK FOREIGN KEY(DNO) REFERENCES DEPARTMENT  (DNUMBER) ON DELETE SET DEFAULT ON UPDATE  CASCADE ... )
Background image of page 8
9 DROP TABLE Remove a relation (base table) and its  definition The relation can no longer be used in  queries, updates, or any other  commands  Example: DROP TABLE  DEPENDENT  RESTRICT; DROP TABLE  DEPENDENT  CASCADE;
Background image of page 9

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

View Full DocumentRight Arrow Icon
10 Drop Table (Cont.) Cascade All constraints (e.g. foreign key definitions  in another relation) and views reference  the table are dropped automatically from  the schema.
Background image of page 10
Image of page 11
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 10/26/2009 for the course CSC CSC 134 taught by Professor Jinyin during the Spring '09 term at CSU Sacramento.

Page1 / 55

note6-sql - CSc134 DatabaseManagementand FileOrganization...

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

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