Transcript Kroenke-Auer-DBP-e11-PP

David M. Kroenke and David J. Auer
Database Processing:
Fundamentals, Design, and Implementation
Chapter Seven:
SQL for Database
Construction
and Application
Processing
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-1
Chapter Objectives
• To be able to create and manage table structures using
SQL statements
• To understand how referential integrity actions are
implemented in SQL statements
• To be able to create and use SQL constraints
• To understand several uses for SQL views
• To be able to use SQL statements to create and use
views
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-2
Chapter Objectives
• To gain an understanding of how SQL is used in an
application program
• To understand how to create and use triggers
• To understand how to create and use stored procedures
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-3
View Ridge Gallery
• View Ridge Gallery is a small art gallery that has
been in business for 30 years.
• It sells contemporary European and
North American fine art.
• View Ridge has one owner,
three salespeople, and two workers.
• View Ridge owns all of the art that it sells;
it holds no items on a consignment basis.
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-4
Application Requirements
• View Ridge application requirements:
– Track customers and their artist interests
– Record gallery’s purchases
– Record customers’ art purchases
– List the artists and works that have appeared
in the gallery
– Report how fast an artist’s works have sold
and at what margin
– Show current inventory in a Webpage
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-5
View Ridge Gallery Database Design
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-6
SQL DDL and DML
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-7
Creating the VRG Database
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-8
CREATE TABLE
• CREATE TABLE statement is used for creating
relations.
• Each column is described with three parts:
column name, data type, and optional
constraints.
• Example
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-9
Data Types:
SQL Server 2008 Data Types
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-10
Data Types:
Oracle Database 11g Data Types
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-11
Data Types:
MySQL 5.1Data Types I
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-12
Data Types:
MySQL 5.1Data Types II
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-13
Constraints
• Constraints can be defined within the CREATE
TABLE statement, or they can be added to the
table after it is created using the ALTER table
statement.
• Five types of constraints:
–
–
–
–
–
PRIMARY KEY may not have null values
UNIQUE may have null values
NULL/NOT NULL
FOREIGN KEY
CHECK
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-14
Creating Relationships
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-15
Implementing Cardinalities
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-16
Default Values and Data Constraints
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-17
SQL for Constraints
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-18
ALTER Statement
• ALTER statement changes table structure,
properties, or constraints after it has been
created.
• Example
ALTER TABLE ASSIGNMENT
ADD CONSTRAINT EmployeeFK
FOREIGN KEY (EmployeeNumber)
REFERENCES EMPLOYEE (EmployeeNumber)
ON UPDATE CASCADE
ON DELETE NO ACTION;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-19
Adding and Dropping Columns
• The following statement will add a column
named MyColumn to the CUSTOMER
table:
ALTER TABLE CUSTOMER ADD MyColumn Char(5) NULL;
• You can drop an existing column with the
statement:
ALTER TABLE CUSTOMER DROP COLUMN MyColumn;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-20
Adding and Dropping Constraints
• ALTER can be used to add a constraint as
follows:
ALTER TABLE CUSTOMER
ADD CONSTRAINT MyConstraint CHECK
([Name] NOT IN ('Robert No Pay'));
• ALTER can be used to drop a constraint:
ALTER TABLE CUSTOMER
DROP CONSTRAINT MyConstraint;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-21
Removing Tables
• SQL DROP TABLE:
DROP TABLE TRANS;
• If there are constraints:
ALTER TABLE CUSTOMER_ARTIST_INT
DROP CONSTRAINT
Customer_Artist_Int_CustomerFK;
ALTER TABLE [TRANSACTION]
DROP CONSTRAINT TransactionCustomerFK;
DROP TABLE CUSTOMER;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-22
SQL DML—INSERT
• INSERT command:
INSERT INTO ARTIST ([Name], Nationality,
DateOfBirth, DateDeceased)
VALUES ('Tamayo', 'Mexican', 1927, 1998);
• Bulk INSERT:
INSERT INTO ARTIST
([Name], Nationality, DateOfBirth)
SELECT [Name], Nationality, Birthdate
FROM IMPORTED_ARTIST;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-23
SQL DML—UPDATE
• UPDATE command:
UPDATE
SET
WHERE
CUSTOMER
City = 'New York City'
CustomerID = 1000;
• Bulk UPDATE:
UPDATE
SET
WHERE
CUSTOMER
AreaCode = '333'
City = 'Denver';
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-24
SQL DML—DELETE
• DELETE command:
DELETE
WHERE
FROM CUSTOMER
CustomerID = 1000;
• If you omit the WHERE clause, you will
delete every row in the table.
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-25
JOIN ON Syntax
• JOIN ON syntax:
SELECT
CUSTOMER.Name, ARTIST.Name
FROM
CUSTOMER JOIN CUSTOMER_ARTIST_INT
ON
CUSTOMER.CustomerID =
CUSTOMER_ARTIST_INT.CustomerID
JOIN ARTIST
ON CUSTOMER_ARTIST_INT.ArtistID =
ARTIST.ArtistID;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-26
Using Aliases
• Use of aliases:
SELECT
FROM
ON
C.Name, A.Name
CUSTOMER AS C
JOIN
CUSTOMER_ARTIST_INT AS CI
C.CustomerID = CI.CustomerID
JOIN ARTIST AS A
ON CI.ArtistID = A.ArtistID;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-27
VRG Data—CUSTOMER I
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-28
VRG Data—CUSTOMER II
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-29
VRG Data—ARTIST
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-30
VRG Data
CUSTOMER_ARTIST_INT
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-31
VRG Data—WORK I
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-32
VRG Data—WORK II
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-33
VRG Data—TRANS I
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-34
VRG Data—TRANS II
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-35
Outer Joins
• Left Outer Join:
SELECT
FROM
ON
C.LastName, C.FirstName,
A.LastName AS ArtistName
CUSTOMER C LEFT
JOIN
CUSTOMER_ARTIST_INT CI
C.CustomerID = CI.CustomerID
LEFT JOIN ARTIST A
ON CI.ArtistID = A.ArtistID;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-36
Result of Outer Join
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-37
SQL Views
• SQL view is a virtual table that is constructed from other
tables or views.
• It has no data of its own, but obtains data from tables or
other views.
• SELECT statements are used to define views:
– A view definition may not include an ORDER BY clause.
• SQL views are a subset of the external views:
– They can be used only for external views that involve one
multivalued path through the schema.
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-38
SQL Views
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-39
CREATE VIEW Command
• CREATE VIEW command:
CREATE VIEW CustomerNameView AS
SELECT LastName AS
CustomerLastName,
FirstName AS
CustomerFirstName,
FROM
CUSTOMER;
• Results:
SELECT
FROM
ORDER BY
*
CustomerNameView
CustomerLastName,
CustomerFirstName;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-40
Updateable Views
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-41
Embedding SQL in Program Code
• SQL can be embedded in triggers, stored procedures,
and program code.
• Problem: assigning SQL table columns with program
variables.
• Solution: object-oriented programming, PL/SQL.
• Problem: paradigm mismatch between SQL and
application programming language:
– SQL statements return sets of rows; an application works on one
row at a time.
• Solution: process the SQL results as pseudo-files.
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-42
Triggers I
• A trigger is a stored program that is executed by the
DBMS whenever a specified event occurs on a specified
table or view.
• Three trigger types:
BEFORE, INSTEAD OF, and AFTER:
– Each type can be declared for Insert, Update, and Delete.
– Resulting in a total of nine trigger types.
• Oracle supports all nine trigger types.
• SQL Server supports six trigger types (only for INSTEAD
OF and AFTER triggers).
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-43
Triggers II
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-44
Firing Triggers
• When a trigger is fired, the DBMS supplies:
– Old and new values for the update
– New values for inserts
– Old values for deletions
• The way the values are supplied depends on the
DBMS product.
• Trigger applications include:
–
–
–
–
Providing default values
Enforce data constraints
Updating views
Performing referential integrity actions
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-45
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-46
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-47
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-48
Stored Procedures
• A stored procedure is a program that is stored within the
database and is compiled when used:
– In Oracle, it can be written in PL/SQL or Java.
– In SQL Server, it can be written in TRANSACT-SQL.
• Stored procedures can receive input parameters and
they can return results.
• Stored procedures can be called from:
– Programs written in standard languages, e.g., Java, C#.
– Scripting languages, e.g., JavaScript, VBScript.
– SQL command prompt, e.g., SQL Plus, Query Analyzer.
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-49
Stored Procedure Advantages
• Greater security as store procedures are always
stored on the database server
• Decreased network traffic
• SQL can be optimized by the DBMS compiler
• Code sharing resulting in:
– Less work
– Standardized processing
– Specialization among developers
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-50
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-51
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-52
Triggers vs. Stored Procedures
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-53
David Kroenke and David Auer
Database Processing
Fundamentals, Design, and Implementation
(11th Edition)
End of Presentation:
Chapter Seven
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-54
All rights reserved. No part of this publication may be reproduced, stored in a
retrieval system, or transmitted, in any form or by any means, electronic,
mechanical, photocopying, recording, or otherwise, without the prior written
permission of the publisher. Printed in the United States of America.
Copyright © 2010 Pearson Education, Inc.
Publishing as Prentice Hall
KROENKE AND AUER: DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
7-55