Transcript SQL-DDL - Computer Science and Engineering

CSE 480: Database Systems
Lecture 9: SQL-DDL
Reference:
Read Chapter 4.1-4.2 of the textbook
‹#›
Announcements

Exam 1 to be held on February 19
– Cover materials from lecture 1 – 7 (homework 1 and 2)
– Open book and notes but no computer, cell phone, or other
electronic devices
‹#›
SQL

Stands for Structured Query Language
– Has both DDL and DML components
– Also contains additional facilities for


Defining views on the database

Specifying security and authorization

Specifying transaction controls
History
– SEQUEL for IBM System R
=> SQL1 (1986) => SQL 1989 (minor variation)
=> SQL2 (1992)
=> SQL3 (1999)
=> SQL 2003, SQL 2006, and SQL 2008
‹#›
SQL

Does not fully subscribe to all concepts in relational model
– A relation is a set whereas a table is a bag (or multi-set)

An element may appear more than once in a bag
– Every relation must have a primary key; yet SQL allows some
tables not to have any key attributes
‹#›
SQL DDL

Used to CREATE, DROP, and ALTER the descriptions of
the tables (relation schema) in a database

Examples:
– CREATE DATABASE
(not needed unless you’re the system administrator)
– CREATE TABLE
– DROP TABLE
– ALTER TABLE
‹#›
CREATE TABLE
struct DepartmentType{char Dname[10]; int Dnumber;};
DepartmentType DEPARTMENT; /* In C++ or C */
CREATE TABLE DEPARTMENT (
DNAME
VARCHAR(10)
DNUMBER
NUMBER(5)
MGRSSN
CHAR(9),
MGRSTARTDATE CHAR(9) );
NOT NULL,
PRIMARY KEY,
CREATE TABLE DEPT (
DNAME
VARCHAR(10) NOT NULL,
DNUMBER
NUMBER(5)
NOT NULL,
MGRSSN
CHAR(9),
Secondary key
MGRSTARTDATE
CHAR(9),
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN) );
NOTE: In MySQL, FOREIGN KEY is implemented in a slightly different way
‹#›
MySQL Data Types

Numeric (int, float, real, double, decimal, smallint)
Character strings (char(n), varchar(m))
Bit string (binary, varbinary)
Boolean
Date, time, and timestamp
Others (blobs, enum, text, geometric objects, etc)

To check the data types available for your Mysql:





mysql> help data types
or read documentation (http://dev.mysql.com/doc/)
‹#›
MySQL Data Type Examples
‹#›
MySQL Data Type Examples
‹#›
Attribute Constraints

Default values for attributes
‹#›
Attribute Constraints

CHECK clause (available in Oracle but not in MySQL)
CREATE TABLE Department (
DName
VARCHAR(15) NOT NULL,
DNum
INT
NOT NULL,
CHECK (DNum > 0 AND DNum < 21)
);
‹#›
CREATE TABLE By Copying

You can also create a table by copying the content from
another table

Example:
CREATE TABLE empl AS SELECT * FROM Employee;
‹#›
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) DEFAULT '888665555',
MGRSTARTDATE
CHAR(9),
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN)
ON DELETE SET DEFAULT
ON UPDATE CASCADE
);
Specifies the corrective action upon constraint violation
‹#›
Example for DELETE
FK
R
PK
S
x
x
FOREIGN KEY (FK) REFERENCES S(PK)
ON DELETE [ACTION]
Request to delete
row in B
Possible choices of [ACTION]:
– NO ACTION: Reject if row in R references row to be deleted
(default option)
– SET NULL: Set value of foreign key in A to NULL
– SET DEFAULT: Set value of foreign key in A to default value
which must exist in B
– CASCADE: Delete referencing row(s) in A as well
‹#›
Example for UPDATE
FK
R
PK
S
x
x
FOREIGN KEY (FK) REFERENCES S(PK)
ON UPDATE [ACTION]
Request to modify
PK in S
Possible choices of [ACTION]:
– NO ACTION: Reject if row(s) in A references row to be updated
(default response)
– SET NULL: Set value of foreign key to null
– SET DEFAULT: Set value of foreign key to default
– CASCADE: Propagate new value to foreign key
‹#›
Exercise
‹#›
Exercise
‹#›
Exercise
‹#›
DROP TABLE

Remove a relation 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;
‹#›
ALTER TABLE

Add a new column to an existing table:
ALTER TABLE EMPLOYEE ADD Jobtitle VARCHAR(12);

Remove a column from an existing table:
ALTER TABLE EMPLOYEE DROP COLUMN Address;

Modify an existing column in a table:
ALTER TABLE DEPARTMENT MODIFY
Mgr_ssn CHAR(9)
DEFAULT ‘333444555’;

Modify constraints of a table:
ALTER TABLE DEPARTMENT DROP PRIMARY KEY;
ALTER TABLE DEPARTMENT ADD PRIMARY KEY(DNUMBER);
‹#›
TRUNCATE TABLE

Remove all the rows in Employee table
TRUNCATE TABLE EMPLOYEE;
– Table will be empty after truncated
‹#›
MySQL

Mysql server is available on mysql-user.cse.msu.edu

You can log on to the server from any machine that has
the mysql command line interpreter installed (e.g.,
arctic.cse.msu.edu)
– Username is your MSU NetID
– Password is your PID
‹#›
Summary of useful MySQL commands












set password=password(‘new password’);
show databases; -- show the list of databases available to you
use dbname; -- use the database called dbname
show tables; -- show tables available
create table student (
id integer not null,
name varchar(50) );
describe student;
insert into student values (30, ‘john doe’);
select * from student;
truncate table student;
drop table student;
source script-file;
-- executing SQL commands from a script-file
load data infile /path/file.txt into table skr;
‹#›
MySQL Storage Engines

A storage engine is a low level data storage/retrieval
module

MySQL supported multiple storage engines
– MyISAM, InnoDB, Heap (Memory), BDB, Merge, …

You can specify which storage engine to use:
CREATE TABLE t (i INT) ENGINE = INNODB;
CREATE TABLE t (i INT) TYPE = MEMORY;
‹#›
MySQL Databases

MySQL storage engines include both those that handle transactionsafe tables and those that handle non-transaction-safe tables

“Transaction-safe” tables would record all the database update
operations in a log file
– So even if MySQL crashes, you can still get your data back
– You can execute ROLLBACK to undo your updates
– Provides better concurrency
– Disadvantage: slower especially when there are many concurrent update
operations

Advantages of non-transaction-safe tables
– Much faster
– Lower disk space requirements
‹#›
MySQL Storage Engines

MyISAM (usually, the default storage engine)
– manages non-transactional tables.
– provides high-speed storage and retrieval
– Provides fulltext searching capabilities

InnoDB
– provides transaction-safe tables
– provides support for row locking and FOREIGN KEY constraints
Important: Foreign key is not enforced in MySQL
unless you use InnoDB as your storage engine!
‹#›
Example
Employee
Department
N
Works_For
1
‹#›
Example
Employee
Department
N
Works_For
1
‹#›
Example
Employee
Department
N
Works_For
1
‹#›
Example
Foreign key is not
enforced in this example
No dnumber = 3 in department
table
‹#›
Foreign key constraints in MySQL

In MySQL, foreign key constraints are supported by
InnoDB storage engine only
– Default storage engine in MySQL is MyISAM
– So, you need to make sure the referenced and referencing
relations are created using Innodb storage engine

You also need to create an index on the foreign key
attributes:
– Syntax: INDEX index_name(list_of_foreignkey_attributes)
– Example:
INDEX workson_fk_index1 (employeeID),
INDEX workson_fk_index2 (projectID),
FOREIGN KEY (employeeID) References Employee(ID),
FOREIGN KEY (projectID) References Project(ID)
‹#›
Example
‹#›
Example
Foreign key works now!!
‹#›
Example
Suppose we want to change ‘payroll’ dnumber from 1 to 3
‹#›
Example
Dnumber for John Doe
automatically changes
from 1 to 3 because
foreign key constraint
says “On update cascade”
‹#›