blm352ENACh08final - Copyright 2007 Ramez Elmasri and...

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

View Full Document Right Arrow Icon
Slide 8- 1 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Background image of page 1

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

View Full DocumentRight Arrow Icon
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Chapter 8 SQL-99: SchemaDefinition, Constraints, and Queries and Views
Background image of page 2
Slide 8- 3 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Data Definition, Constraints, and Schema Changes Used to CREATE, DROP, and ALTER the descriptions of the tables (relations) of a database
Background image of page 3

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

View Full DocumentRight Arrow Icon
Slide 8- 4 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe CREATE TABLE Specifies a new base relation by giving it a name, and specifying each of its attributes and their data types (INTEGER, FLOAT, DECIMAL(i,j), CHAR(n), VARCHAR(n)) 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 4
Slide 8- 5 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe CREATE TABLE In SQL2, can use the CREATE TABLE command for specifying the primary key attributes, secondary keys, and referential integrity constraints (foreign keys). Key attributes can be specified via the PRIMARY KEY and UNIQUE phrases CREATE TABLE DEPT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9), PRIMARY KEY (DNUMBER), UNIQUE (DNAME), FOREIGN KEY (MGRSSN) REFERENCES EMP );
Background image of page 5

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

View Full DocumentRight Arrow Icon
Slide 8- 6 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe DROP TABLE Used to remove a relation (base table) and its definition The relation can no longer be used in queries, updates, or any other commands since its description no longer exists Example: DROP TABLE DEPENDENT;
Background image of page 6
Slide 8- 7 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe ALTER TABLE Used to add an attribute to one of the base relations The new attribute will have NULLs in all the tuples of the relation right after the command is executed; hence, the NOT NULL constraint is not allowed for such an attribute Example: ALTER TABLE EMPLOYEE ADD JOB VARCHAR(12); The database users must still enter a value for the new attribute JOB for each EMPLOYEE tuple. This can be done using the UPDATE command.
Background image of page 7

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

View Full DocumentRight Arrow Icon
Slide 8- 8 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Features Added in SQL2 and SQL- 99 Create schema Referential integrity options
Background image of page 8
Slide 8- 9 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe CREATE SCHEMA Specifies a new database schema by giving it a name
Background image of page 9

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

View Full DocumentRight Arrow Icon
Slide 8- 10 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe REFERENTIAL INTEGRITY OPTIONS We can specify RESTRICT, CASCADE, SET NULL or SET DEFAULT on referential integrity constraints (foreign keys) CREATE TABLE DEPT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9), PRIMARY KEY (DNUMBER), UNIQUE (DNAME), FOREIGN KEY (MGRSSN) REFERENCES EMP
Background image of page 10
Slide 8- 11 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe REFERENTIAL INTEGRITY OPTIONS (continued) CREATE TABLE EMP( ENAME VARCHAR(30) NOT NULL, ESSN CHAR(9),
Background image of page 11

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

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

This note was uploaded on 03/21/2010 for the course CS 145 taught by Professor Markjan during the Spring '10 term at Abilene Christian University.

Page1 / 75

blm352ENACh08final - Copyright 2007 Ramez Elmasri and...

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

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