Chapter 8 SQL99 Schema Definition Constraints and Queries

Chapter 8 SQL-99: Schema Definition, Constraints, and Queries and Views Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe

Introduction to SQL n A standard language used in most DBMS. n Well, not as standardized as one might hope n n it keeps involving and growing Vendors have the tendency to add “unique” features. Pronounced as “S-Q-L” or “Sequel. ” Both as a DDL and DML language. n n DDL (Data Definition Language): define the schema of the database. DML (Data Manipulation Language): provides commands to manipulate the database (query, insert, update, delete). Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 2

SQL Continued n Based on relational algebra, but not entirely identical. n n Unlike a relation, a table is not a set. Duplicates are not automatically removed. n n Relations Tables Tuples Rows Attributes Columns This is for practical reasons. Duplicate eliminations are inefficient in implementation. Like a relation, the order of rows in a table is irrelevant. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 3

Basic DDL Commands in SQL n n CREATE: to define new tables (to define relation schemas) DROP: to delete table definitions (to delete relation schemas) ALTER: to change the definitions of existing tables (to change relation schema) Other features as DDL n n Specify referential integrity constraints (FKs) Specify user-defined attributes constraints Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 4

Basic DML Commands in SQL n n n INSERT: to add new rows to table UPDATE: to change the “state” (the value) of rows. DELETE: to remove rows SELECT: a query command that uses relation algebra like expressions Various options available to handle the enforcement/violation of integrity constraints Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 5

SQL Commands Are Sequential n n n Commands are executed in the order they are encountered. DDL commands are not like C/Java declarations. DDL and DML commands can be mixed n n For example, you can define a table, fill it up with contents, and delete a columns. That is, table definitions (relation schema) can be changed during the lifespan of a database. n n The ability of doing so does imply it is a good practice. It is best the schema/design of a database is well thought through before its use. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 6

Platforms of This Course n Platform 1: My. SQL n n Platform 2: Oracle 10 g Enterprise Edition n n Open source, free software Available on Windows and Linux. Easily installed on your own PC. Available thru IT&E labs Proprietary, popular DBMS Please see http: //labs. ite. gmu. edu/reference/faq_oracle. htm for details. I’ll be using My. SQL as the reference platform. You can submit projects on either platform. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 7

My. SQL n n Open source and free Generally not as powerful as Oracle n n Still, it is an industrial strength package. n n Our projects will not need advanced and/or proprietary features of Oracle. Users include Amazon, NASA, Google, Yahoo … A commercial edition is also available (My. SQL Enterprise) --- You are paying for the services. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 8

Installation on Windows n n n Download the Essential Version of My. SQL 5. 0 from mysql. com Click on the. exe file to start installation. In Setup Type, Choose “Complete” Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 9

Use Standard Configuration Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 10

Install As Windows Service Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 11

Set Root Password Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 12

Launch My. SQL n n Use the Start menu to launch the “My. SQL Command Line Client” Enter the root password Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 13

The COMPANY Database Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 14

Create the COMPANY Database n n n To create datatbase COMPANY; To use (or switch to) the database use COMPANY; Subsequent commands will operate on the COMPANY database by default. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 15

CREATE TABLE DEPARTMENT ( Dname VARCHAR(10) NOT NULL, Dnumber INTEGER Default 0, Mgr_ssn CHAR(9), Mgr_Sartdate CHAR(9), PRIMARY KEY (Dnumber), UNIQUE (Dname), FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE (Ssn)); n n n The “UNIQUE” clause specifies secondary keys. EMPLOYE)has to be created first for the FK Mgr_ssn to refer to it. How could we have defined the Dno FK in EMPLOYEE? Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 16

Additional Data Types n DATE: n n TIME: n n Has both DATE and TIME components Decimal (i, j): n n Made up of hour: minute: second in the format hh: mm: ss TIMESTAMP: n n Made up of year-month-day in the format yyyy-mm-dd i: total number of digits j: the number of digits after the decimal point Others: Boolean, Float, Double Precision See user’s manual for more data types. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 17

Adding the Dno FK to EMPLOYEE n n If “create table EMPLOYEE” is issued first, we cannot specify Dno as a FK in that create command. An ALTER command must be used to change the schema of EMPLOYEE, after the “create table DEPARTMENT, ” to add a FK. alter table EMPLOYEE add constraint foreign key (Dno) references DEPARTMENT (Dnumber); Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 18

The Check Clause Used to specify user-defined constraints n Assume that dept. numbers are from 0 to 99. create table DEPARTMENT ( n … Dnumber INTEGER Default 0 check (Dnumber>=0 AND Dumber<=99), …); n “Check” can also be a clause of the entire table. create table DEPARTMENT ( … Dept_create_date, Mgr_start_date, check (Dept_create_date <= Mgr_start_date) ); Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 19

Exercise n Create the table WORKS_ON, assuming tables EMPLOYEE and PROJECT have been created and Hours ranges from 1 to 56. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 20

Add Columns to Existing Tables n To add spouse SSN (S_ssn) to EMPLOYEE alter table EMPLOYEE add column S_ssn char(9); n n The new attribute will have NULLs in all the tuples of the relation right after the command is executed Alternatively, we can set a default value. alter table EMPLOYEE add column S_ssn char(9) default “ 00000”; Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 21

Delete Columns from Existing Tables n To delete column S_ssn alter table EMPLOYEE drop column S_ssn; n Reminder: changing relation schemas typically indicates ill-executed design phase of the database. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 22

Referential Integrity Options n Causes of referential integrity violation for a foreign key FK (consider the Mgr_ssn of DEPARTMENT). n On Delete: when deleting the foreign tuple n n On Update: when updating the foreign tuple n n What to do when deleting the manager tuple in EMPLOYEE ? What to do when updating/changing the SSN of the manager tuple in EMPLOYEE is changed ? Actions when the above two causes occur. n n n Set Null: the Mgr_ssn is set to null. Set Default: the Mgr_ssn is set to the default value. Cascade: the Mgr_ssn is updated accordingly n If the manager is deleted, the department is also deleted. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 23

The Mgr_ssn Example CREATE TABLE DEPARTMENT ( … Mgr_ssn CHAR(9), … FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE (Ssn) ON DELETE ? ? ? ON UPDATE ? ? ? ); Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 24

Another Example Create table EMP( … ESSN CHAR(9), DNO INTEGER DEFAULT 1, SUPERSSN CHAR(9), PRIMARY KEY (ESSN), FOREIGN KEY (DNO) REFERENCES DEPT ON DELETE SET DEFAULT ON UPDATE CASCADE, FOREIGN KEY (SUPERSSN) REFERENCES EMP ON DELETE SET NULL ON UPDATE CASCADE); Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 25

Miscellaneous Commands n show databases; n n show tables; n n n Delete the entire table t_name drop database db_name; n n Show all the tables of the present database show columns from table EMPLOYEE; drop table t_name; n n Show all the databases on the server Delete the entire database db_name load data infile f_name into table t_name; n To be discussed with the next homework. Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 26