View Ridge Gallery View Ridge Gallery is a

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, 10 th Edition © 2006 Pearson Prentice Hall 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, 10 th Edition © 2006 Pearson Prentice Hall 2

View Ridge Gallery Database Design DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 3

SQL DDL and DML DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 4

The Database Design for ARTIST and WORK DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 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, 10 th Edition © 2006 Pearson Prentice Hall 6

Data Types DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 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, 10 th Edition © 2006 Pearson Prentice Hall 8

Creating Relationships DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 9

Implementing Cardinalities DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 10

Default Values and Data Constraints DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 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, 10 th Edition © 2006 Pearson Prentice Hall 12

SQL for Constraints DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 13

Example CHECK Constraints (from Oracle) CREATE TABLE ARTIST (Artist. ID NUMBER(9) Not Null, Name Char(25) Not Null, Nationality char(30), Birth. Date NUMBER(4), Deceased. Date NUMBER(4), CONSTRAINT Artist. PK PRIMARY KEY (Artist. ID), CONSTRAINT Artist. AK 1 UNIQUE (Name), CONSTRAINT Nationality. Values CHECK (Nationality IN ('Canadian', 'English', 'French', 'German', 'Mexican', 'Russian', 'Spanish', 'US')), CONSTRAINT Birth. Values. Check CHECK (Birth. Date < Deceased. Date), CONSTRAINT Valid. Birth. Year CHECK (Birth. Date BETWEEN 999 AND 3000), CONSTRAINT Valid. Deceased. Year CHECK (Deceased. Date BETWEEN 999 AND 3000) ); DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 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, 10 th Edition © 2006 Pearson Prentice Hall 15

SQL Views DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 16

CREATE VIEW Command • CREATE VIEW command: CREATE OR REPLACE VIEW Customer. Name. View AS SELECT [Name] AS Customer. Name FROM CUSTOMER; • To see the view use: SELECT FROM ORDER BY * Customer. Name. View Customer. Name; • Results: DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 17

Example View from Oracle CREATE OR REPLACE VIEW Basic. Customer. Data AS SELECT name, area_code, phone FROM art_customer ORDER BY name; View Created SQL> select * from Basic. Customer. Data; 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 111 2222 555 -1234 555 -5678 555 -1234 555 -1000 11 rows selected. DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 18

Updateable Views DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 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, 10 th Edition © 2006 Pearson Prentice Hall 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, 10 th Edition © 2006 Pearson Prentice Hall 21

DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 22

DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 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_row. Count NUMBER(6); BEGIN SELECT COUNT(*) INTO V_row. Count FROM Transaction T WHERE : NEW. Work. ID = T. Work. ID; IF V_row. Count = 0 THEN INSERT INTO Transaction (Transaction. ID, Date. Acquired, Work. ID) VALUES(trans. Seq. next. Val, SYSDATE, : NEW. Work. ID); END IF; END; 24

Trigger logging changes (from Oracle) CREATE OR REPLACE TRIGGER Transaction_ADU_Trigger AFTER DELETE OR UPDATE ON Transaction DECLARE V_transaction. Type Char(6); BEGIN IF DELETING THEN V_transaction. Type : = 'Delete'; ELSIF UPDATING THEN V_transaction. Type : = 'Update'; END IF; INSERT INTO View_Ridge_Translog VALUES(V_transaction. Type, USER, SYSDATE); END; 25

Oracle trigger Don’t delete customers who owe us! CREATE OR REPLACE TRIGGER Delete. Customer BEFORE DELETE ON Customer FOR EACH ROW DECLARE V_row. Count 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_row. Count FROM Stove_Repair WHERE Stove_Repair. Customer. SK = : OLD. Customer. SK AND cost > paid; /* If we found any unpaid bills, raise an error. (Rollback is not allowed inside a Trigger. ) */ IF V_row. Count > 0 THEN RAISE_APPLICATION_ERROR(-20000, 'Cannot delete; money owed. '); END IF; END; 26

Oracle trigger Insert of customer parent requires 2 children CREATE OR REPLACE TRIGGER Insert. Customer 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. next. Val; */ SELECT Stove_SN_Seq. next. Val into V_SN from dual; /* Add a record to Stove. Add a record to Registration. */ INSERT INTO STOVE VALUES (V_SN, SYSDATE, 'Fired. Up', 2. 1); INSERT INTO REGISTRATION VALUES(: NEW. Customer. SK, V_SN, SYSDATE); END; 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 tab 1; SQL> select SYSDATE from DUAL; ENO -----101 102 103 Now if you select an expression, say 1, from tab 1 SQL> select 1 from tab 1; 1 -----1 1 1 If you select an expression a+b from tab 1 SQL> select 'a+b' from tab 1; 'A+ --a+b a+b SYSDATE ----08 -APR-05 SQL> select 25000*. 25 from DUAL; 25000*. 25 ----6250 SQL> select Customer. ID. next. Val from DUAL; NEXTVAL ----1020 28 Adapted from Indira Aramandla on http: //forums 1. 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. , Java. Script, VBScript – SQL command prompt, e. g. , SQL*Plus, Query Analyzer DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 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, 10 th Edition © 2006 Pearson Prentice Hall 30

DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 31

Triggers vs. Stored Procedures DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 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, 10 th Edition © 2006 Pearson Prentice Hall 33