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.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-1
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 Web page
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-2
View Ridge Gallery Database Design
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-3
SQL DDL and DML
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-4
The Database Design for ARTIST
and WORK
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-5
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:
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-6
Data Types
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-7
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
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-8
Creating Relationships
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-9
Implementing Cardinalities
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-10
Default Values and Data Constraints
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-11
CHECK CONSTRAINT
• CHECK CONSTRAINT defines limits for column
values
• Two common uses
– Specifying a range of allowed values
– Specifying an enumerated list
• CHECK constraints may be used
– To compare the value of one column to another
– To specify the format of column values
– With subqueries (not in Oracle)
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-12
SQL for Constraints
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-13
Example CHECK Constraints
(from Oracle)
CREATE TABLE ARTIST
(ArtistID
NUMBER(9)
Not Null,
Name
Char(25)
Not Null,
Nationality
char(30),
BirthDate
NUMBER(4),
DeceasedDate NUMBER(4),
CONSTRAINT ArtistPK PRIMARY KEY (ArtistID),
CONSTRAINT ArtistAK1 UNIQUE (Name),
CONSTRAINT NationalityValues CHECK
(Nationality IN ('Canadian', 'English', 'French', 'German',
'Mexican', 'Russian', 'Spanish', 'US')),
CONSTRAINT BirthValuesCheck CHECK (BirthDate < DeceasedDate),
CONSTRAINT ValidBirthYear CHECK (BirthDate BETWEEN 999 AND 3000),
CONSTRAINT ValidDeceasedYear CHECK
(DeceasedDate BETWEEN 999 AND 3000)
);
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-14
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
– Exception: Oracle does allow ORDER BY
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-15
SQL Views
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-16
CREATE VIEW Command
• CREATE VIEW command:
CREATE OR REPLACE VIEW
CustomerNameView AS
SELECT [Name] AS CustomerName
FROM
CUSTOMER;
• To see the view use:
SELECT
FROM
ORDER BY
*
CustomerNameView
CustomerName;
• Results:
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-17
Example View from Oracle
CREATE OR REPLACE VIEW BasicCustomerData AS
SELECT name, area_code, phone
FROM art_customer
ORDER BY name;
View Created
SQL> select * from BasicCustomerData;
NAME
------------------------Chris Wilkens
David Smith
Donald G. Gray
Fred Smathers
Jack Jones
Jeffrey Janes
Lynda Johnson
Mary Beth Frederick
Selma Warning
Susan Wu
Tiffany Twilight
ARE
--206
303
705
206
585
206
703
303
253
721
206
PHONE
-------555-1134
555-5434
555-1234
555-1234
111 2222
555-1234
555-1234
555-5678
555-1234
555-1234
555-1000
11 rows selected.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-18
Updateable Views
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-19
Triggers
• 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)
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-20
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:
–
–
–
–
Provide default values
Enforce data constraints
Update views
Perform referential integrity actions
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-21
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-22
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-23
Trigger enforcing referential integrity:
Work entity must have a child in Transaction
(Oracle PL/SQL)
CREATE OR REPLACE TRIGGER Work_AI_Trigger
AFTER INSERT ON Work
FOR EACH ROW
DECLARE
V_rowCount
NUMBER(6);
BEGIN
SELECT COUNT(*) INTO V_rowCount
FROM Transaction T
WHERE :NEW.WorkID = T.WorkID;
IF V_rowCount = 0 THEN
INSERT INTO Transaction
(TransactionID, DateAcquired, WorkID)
VALUES(transSeq.nextVal, SYSDATE, :NEW.WorkID);
END IF;
END;
7-24
Trigger logging changes
(from Oracle)
CREATE OR REPLACE TRIGGER Transaction_ADU_Trigger
AFTER DELETE OR UPDATE ON Transaction
DECLARE
V_transactionType Char(6);
BEGIN
IF DELETING THEN
V_transactionType := 'Delete';
ELSIF UPDATING THEN
V_transactionType := 'Update';
END IF;
INSERT INTO View_Ridge_Translog
VALUES(V_transactionType, USER, SYSDATE);
END;
7-25
Oracle trigger
Don’t delete customers who owe us!
CREATE OR REPLACE TRIGGER DeleteCustomer
BEFORE DELETE ON Customer
FOR EACH ROW
DECLARE
V_rowCount NUMBER(9);
BEGIN
/*
Find all the unpaid bills for the customer we
are going to delete.
The :OLD refers to the old row/tuple in Customer
*/
SELECT Count(*) INTO V_rowCount
FROM Stove_Repair
WHERE Stove_Repair.CustomerSK = :OLD.CustomerSK
AND cost > paid;
/*
If we found any unpaid bills, raise an error.
(Rollback is not allowed inside a Trigger.)
*/
IF V_rowCount > 0 THEN
RAISE_APPLICATION_ERROR(-20000, 'Cannot delete; money owed.');
END IF;
END;
7-26
Oracle trigger
Insert
of customer parent requires 2 children
CREATE OR REPLACE TRIGGER InsertCustomer
AFTER INSERT ON Customer
FOR EACH ROW
DECLARE
V_SN NUMBER(9);
BEGIN
/*
Get the next sequence number for the serial number.
Note that this more intuitive form is illegal...
V_SN := Stove_SN_Seq.nextVal;
*/
SELECT Stove_SN_Seq.nextVal into V_SN from dual;
/*
Add a record to Stove.
Add a record to Registration.
*/
INSERT INTO STOVE
VALUES (V_SN, SYSDATE, 'FiredUp', 2.1);
INSERT INTO REGISTRATION
VALUES(:NEW.CustomerSK, V_SN, SYSDATE);
END;
7-27
A note on Oracle’s table ‘DUAL’
DUAL is a table owned by SYS that has only
1 row, and only 1 column called ‘dummy’.
The single field contains the single character X.
Since DUAL has only 1 row, we can conveniently
Use it to return single values:
To understand the SQL, note the following:
SQL> select * from tab1;
SQL> select SYSDATE from DUAL;
ENO
---------101
102
103
SYSDATE
--------08-APR-05
Now if you select an expression, say 1, from tab1
SQL> select 1 from tab1;
1
---------1
1
1
If you select an expression a+b from tab1
SQL> select 'a+b' from tab1;
'A+
--a+b
a+b
a+b
SQL> select 25000*.25 from DUAL;
25000*.25
--------6250
SQL> select CustomerID.nextVal from DUAL;
NEXTVAL
--------1020
7-28
Adapted from Indira Aramandla on http://forums1.itrc.hp.com/service/forums
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
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-29
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
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-30
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-31
Triggers vs. Stored Procedures
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-32
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 applications
work on one row at a time
• Solution: process the SQL results as
pseudofiles
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
7-33
Descargar

Kroenke-DBP-e10-PPT-Chapter07