Transcript Databases: Design, Implementation, and Management

SQL
Components of SQL
• Data definition language (DDL)
– To create database structures
– To enforce constraints etc.
• Data manipulation language (DML)
– To access data
• Authorization language
– To grant privileges to users
• SQL supports dynamic database definition
– Structures can be modified easily
About SQL
• Its fairly standard across the all relational
database management systems but…
• There are some differences in
– Syntax
– Data types
– Functionality
• As a rule, Oracle is a better environment in
which to learn SQL than Access…
– But we will look at both, with an emphasis on
Oracle
DDL Commands
•
•
•
•
•
•
•
CREATE TABLE
CREATE INDEX
ALTER TABLE
RENAME TABLE
DROP TABLE
DROP INDEX
CREATE VIEW
CREATE TABLE
CREATE TABLE base-table-name
(colname datatype
[column constraints
NULL/NOT NULL,
DEFAULT…,
UNIQUE,
CHECK…,
PRIMARY KEY],
[,colname datetype [column constraints …]]
...
[table constraints PRIMARY KEY…,
FOREIGN KEY…,
UNIQUE…,
CHECK…]
[storage specifications]);
Student Table
CREATE TABLE Student
(
stuId
CHAR(6),
lastName
CHAR(20) NOT NULL,
firstName
CHAR(20) NOT NULL,
major
CHAR(10),
credits
SMALLINT DEFAULT 0,
CONSTRAINT Student_stuId_pk PRIMARY KEY
(stuId),
CONSTRAINT Student_credits_cc CHECK
(credits>=0 AND credits < 150));
Create Tables for University DB
CREATE TABLE Student
( previous slide )
CREATE TABLE Faculty
(
facId
CHAR(6),
name
CHAR(20) NOT NULL,
department
CHAR(20) NOT NULL,
rank
CHAR(10),
CONSTRAINT Faculty_facId_pk PRIMARY KEY (facId));
CREATE TABLE Class
(
classNumber CHAR(8),
facId
CHAR(6) NOT NULL,
schedule
CHAR(8),
room
CHAR(6),
CONSTRAINT Class_classNumber_pk PRIMARY KEY (classNumber),
CONSTRAINT Class_facId_fk FOREIGN KEY (facId) REFERENCES Faculty (facId)) ;
CREATE TABLE Enroll
(
stuId
CHAR(6),
classNumber CHAR(8),
grade
CHAR(2),
CONSTRAINT Enroll_classNumber_stuId_pk PRIMARY KEY (classNumber, stuId),
CONSTRAINT Enroll_classNumber_fk FOREIGN KEY (classNumber) REFERENCES
Class (classNumber),
CONSTRAINT Enroll_stuId_fk FOREIGN KEY (stuId) REFERENCES Student(stuId));
Rules for Identifiers
• No SQL keywords may be used
• Table name must be unique within the
database
• Column names user must be unique within
the table
Data Types
• Each column must have a data type specified
– Numeric, fixed and varying-length character
strings, bit strings, and user-defined types
• Data types vary by rdbms
– See text and mysql documentation
User-defined Data Types
• We can declare our own domains and set
constraints at the domain level:
CREATE DOMAIN creditValues INTEGER
DEFAULT 0
CHECK (VALUE >=0 AND VALUE <150);
• This domain (data) type can then be used in
defining columns:
– In Student, can write credits creditValues
– And we can use creditValues to define other data
•
Constraints
• These can be defined at:
– column level
– table level
• Column-level constraints
– NULL/NOT NULL
– UNIQUE
– PRIMARY KEY
– CHECK
– DEFAULT
– Written immediately after column name or
datatype
Table-level constraints
• PRIMARY KEY
– For composite keys (simple ones at column level)
• FOREIGN KEY
– Requires that the referenced table has already been
created
• UNIQUE
– Specifies values in a combination of columns that must
be unique
– Used to specify candidate keys
• ON DELETE / ON UPDATE for foreign keys
– CASCADE / SET NULL/ SET DEFAULT / NO ACTION;
• Constraints can be given a name
– This can be useful for disabling them at times
Indexes
• We can create any number of indexes for a
given table
– They facilitate fast retrieval of records with specific
values in a column
– They don’t always return unique values
• Overhead is involved
– The rdbms must maintain the index
• Tree data structures used to order values on
index
CREATE INDEX
CREATE [UNIQUE] INDEX indexname
ON basetablename
(colname [order] [,colname [order]]...) [CLUSTER] ;
CREATE INDEX Student_lastName_firstName_ndx ON
Student (lastName, firstName);
• UNIQUE enforces unique values for indexed column or
combination of columns
– The column need not be unique
– Order is ASC (default) or DESC
• CLUSTER specification keeps records with same value
for indexed field together (only one per table)
•
ALTER TABLE
• To add a new column
ALTER TABLE basetablename ADD columnname datatype;
ALTER TABLE Student ADD COLUMN birthdate DATETYPE;
• To drop a column
ALTER TABLE basetablename DROP COLUMN columnname;
ALTER TABLE Student DROP COLUMN major;
• To add a constraint
ALTER TABLE basetablename ADD CONSTRAINT constraint_defn;
• To drop a constraint
ALTER TABLE basetablename DROP CONSTRAINT constraint_name;
Other Changes to Tables
• Renaming a table:
RENAME TABLE old-table-name TO new-table-name;
RENAME TABLE FACULTY TO TEACHERS;
• Dropping a table:
DROP TABLE basetablename;
DROP TABLE CLASS;
• Dropping an index:
DROP INDEX indexname;
DROP INDEX Student_lastName_fristName_ndx;
SQL DML
• Non-procedural, declarative language
• Can be
– interactive
– embedded in host language
– stand-alone (SQL/PSMs)
• Basic commands
SELECT
UPDATE
INSERT
DELETE
SELECT Statement
SELECT
[DISTINCT] col-name [AS newname],
[,col-name..]…
FROM
table-name [alias] [,table-name]…
[WHERE
predicate]
[GROUP BY
col-name [,col-name]…[HAVING
predicate]
or …
[ORDER BY
col-name [,col-name]…];
UniversityDB
Student
Enroll
Class
stuID
stuID
classNo
facID
lastName
classNo
schedule
name
firstName
grade
room
dept
facID
rank
major
credits
Faculty
Get names & ids of all Math majors
SELECT lastname, firstname, stuID
FROM
Student
WHERE major = ‘Math’;
Get all info about CSC faculty
SELECT *
FROM
Faculty
WHERE dept = ‘CSC’;
Get the course numbers for all
courses with enrollment
SELECT DISTINCT classNum
FROM
Enroll;
Display the Student table
SELECT *
FROM
Student;
Get names & ID’s of all faculty,
listed alphabetically
SELECT
name, facID
FROM
Faculty
ORDER BY name;
SELECT
name, facID
FROM
Faculty
ORDER BY name, facID;
Get names of all math majors
with > 30 credits
SELECT
FROM
WHERE
lastName, firstName
Student
major = ‘MATH’
AND credits > 30;
Find ID’s and names of all
students in ART 103A
SELECT
FROM
WHERE
Student.stuID, lastName,
firstName
Student, Enroll
Student.stuID = Enroll.stuID;
(Joining two tables)
Find ID’s (in ascending order) and
grades of all students taking a
course with faculty ‘F110’
SELECT
FROM
WHERE
ORDER BY
stuID, grade
Class, Enroll
Class.classNumber =
Enroll.classNumber
AND facID = ‘F110’
stuID;
Find class #’s, names & majors of
all students taking a course with
faculty ‘F110’
SELECT
FROM
WHERE
ORDER BY
Enroll.classNumber, lastName,
firstName, major
Class, Enroll, Student
Class.classNumber =
Enroll.classNumber
AND Enroll.stuID = Student.stuID
AND facID = ‘F110’
stuID;
Find class #’s of all of classes
that meet in the same room
SELECT
FROM
WHERE
C1.classNumber,
C2.classNumber
Class C1, Class C2
C1.room = C2.room
AND C1.classNumber <>
C2.classNumber
Must compare the Class table with itself – requires aliases.
This example works on the assumption that a maximum of two
classes will meet in the same room
Subqueries
• These are queries within queries
SELECT columnName(s)
FROM
tableName(s)
WHERE someColumnName = or IN
(SELECT columnName(s)
FROM tableName(s)
WHERE someCondition);
The
subquery
executes
FIRST
Find all class #’s for Byrne of the
Math Dept
SELECT
FROM
WHERE
classNumber
Class
facID =
(SELECT facID
FROM Faculty
WHERE name = ‘Byrne’
AND dept = ‘Math’);
Aggregate functions
•
•
•
•
•
COUNT
SUM
MAX
MIN
AVG
How many students are in ART 103A?
SELECT COUNT (DISTINCT stuID)
FROM
ENROLL
WHERE classNumber = ‘ART103A’;
Find the ID of the student with
the most credits
SELECT
FROM
WHERE
stuID
Student
credits =
(SELECT MAX(credits)
FROM
Student);
Must use subquery
List each class# along with its
enrollment
SELECT
FROM
GROUP BY
classNumber, COUNT(stuID)
Enroll
classNumber;
Recap SQL commands so far
• SELECT is basic
– * is the ‘wildcard’ or list column names
• WHERE for all conditions
– Including joins
• ORDER BY for sorting
• AND, OR, NOT logical operations
• AS for aliases
– good for using same table twice
• Subqueries
• Aggregate functions (Example 18 is excellent)
– COUNT, SUM, MAX, MIN, AVG
– GROUP BY
Some new SQL DML
•
•
•
•
•
•
•
•
Nested subqueries
EXISTS / NOT EXISTS
UNION
Formatting query results
HAVING with GROUP BY
LIKE
NULL
Updating operators
Nested queries
• Get an alphabetical list of names & id’s of
all students in any class taught by F110
SELECT
lastName, firstName, stuID
FROM
Student
WHERE
stuID IN
(SELECT
stuID
FROM
Enroll
WHERE
classNumber IN
(SELECT
classNumber
FROM
Class
WHERE
facID = 'F110'))
ORDER BY lastName, firstName;
3
2
1
EXISTS / NOT EXISTS
Correlated subqueries
• Names of all students enrolled in CSC201A
SELECT
lastName, firstName
FROM
Student
WHERE
EXISTS
(SELECT
*
FROM
Enroll
WHERE
Enroll.stuID = Student.stuID
AND
classNumber = ‘CSC201A’;
• Also, NOT EXISTS
UNION
• Can be used instead of OR in a condition
if the condition
SELECT
FROM
WHERE
facID
Faculty
department = 'History'
UNION
SELECT
FROM
WHERE
facID
Class
room = 'H221';
One
query
Formatting query results
• List the student id and # courses each
student has taken.
– Uses an expression, a string constant and an
alias for a column name.
SELECT stuID, 'Number of courses =', credits/3 AS Courses
FROM Student;
HAVING
• HAVING : GROUP BY as WHERE : SELECT
• Find all courses with fewer than 3 students
SELECT
FROM
GROUP BY
HAVING
className
Enroll
className
COUNT(*) < 3;
LIKE
• For pattern matching
• List the names of all Math classes
SELECT *
FROM Class
WHERE classNumber LIKE 'MTH*';
In Access, use *
NULL
• Find the stuID & classes of all students
with missing grades.
SELECT stuID, classNumber
FROM Enroll
WHERE grade IS NULL;
Can’t use = here
Updating operators
• UPDATE
• INSERT
• DELETE
UPDATE Operator
UPDATE
SET
tablename
columnname = expression
[columnname = expression]...
[WHERE predicate];
• Used for changing values in existing records
• Can update one or many records in a table
– All that meet the WHERE condition will be changed
• For null value, use SET columnname = NULL
• Can use a sub-query to identify records to be
updated
UPDATE examples
• See hand-out, pp. 323-325
– Examples 1-6
INSERT Operator
INSERT
INTO
tablename [(colname [,colname]...)]
VALUES (constant [,constant]...);
• Used for inserting new records into database,
one at a time
• Not necessary to name columns if values are
supplied for all columns, in proper order
• To insert null value for a column, specify only the
other columns or write null as the value
• Can specify values for some columns, in any
order, as long as values match order
INSERT examples
• See hand-out, 326-327
– Examples 1-4
DELETE Operator
DELETE
FROM
WHERE
tablename
predicate;
• Used to remove records from a table
• Can delete one or many records at a time
• Operation may not work if it would violate
referential integrity
• Can use a sub-query to target records to be
deleted
• If you delete all records from a table, its structure
still remains, and you can insert into it later
DELETE examples
• See hand-out, pp. 328-329
– Examples 1-4
Exercises
• Use the ASSIGNS DDL commands to
create an Access database
– Code the SQL commands for
• Hand-out, pp. 355-356, problems 6.3-6.9; 6.11-6.21
– Code the SQL commands for
• Hand-out, pp. 357-358, Lab Exercise 6.2c
Active Databases
• DBMS monitors database to prevent
illegal states
– Constraints
– Triggers
Constraints
• Constraints
– Specified when table is created, or later
– IMMEDIATE MODE
• constraint checked when each INSERT, DELETE,
UPDATE is performed
– DEFERRED MODE – see pp. 329-330
• postpones constraint checking to end of transaction
SET CONSTRAINT name DEFERRED
– Can use
DISABLE CONSTRAINT name,
ENABLE CONSTRAINT name
Triggers
• ECA model:
– Event
• some change made to the database
– Condition
• a logical predicate
– Action
• a procedure done when the event occurs
and the condition is true, also called firing
the trigger
Triggers
• Can be fired before or after
– Insert
– Update
– Delete
• It accesses values it needs as :OLD. and :NEW.
– :OLD refers to values being deleted or replaced
– :NEW refers to the values just inserted or those
replacing old values
• Can specify how many times a trigger fires
– once for each triggering statement, or
– for each row that is changed by the statement
Trigger Syntax
CREATE TRIGGER trigger_name
[BEFORE/AFTER] [INSERT/UPDATE/DELETE]
ON table_name
[FOR EACH ROW] [WHEN condition]
BEGIN
trigger body
END;
•
•
•
•
Can ALTER TRIGGER name DISABLE;
ALTER TRIGGER name ENABLE;
DROP TRIGGER name;
See examples in Figure 6.5
Relational Views
• Can be subsets of base tables, subsets of
joins, or contain calculated data
• Many reasons for views
– Allow different users to see the data in
different forms
– Provide a simple authorization control device
– Free users from complicated DML operations
– If database is restructured, view can keep the
user's model constant
Create View
CREATE VIEW
AS SELECT
FROM
WHERE
viewname [(viewcolname(s)…]
colname(s)...
basetablename(s)…
condition(s);
• Value-independent view
– A vertical subset of a table, with no WHERE,
• Value-dependent view
– Choose only certain rows, using WHERE
• Join view
• Can use functions in SELECT
Using Views
• Views can be queried
– SELECT colname(s) FROM viewname
– Views can be used to create other views
• Views can be updated
– The primary key must be in the view
– An INSTEAD OF trigger is used to update base
table(s), and the change is reflected in the view
CREATE TRIGGER InsertStuVw2
INSTEAD OF INSERT ON StudentVw2
FOR EACH ROW
BEGIN
INSERT
INTO Student
VALUES (:NEW.stuId, :NEW.lastName, :NEW.firstName,
:NEW.
Credits);
Ending Transactions
• COMMIT makes permanent changes in
the current transaction
• ROLLBACK undoes changes made by the
current transaction