David M Kroenkes Database Processing Fundamentals Design and
David M. Kroenke’s Database Processing: Fundamentals, Design, and Implementation Chapter Seven: SQL for Database Construction and Application Processing Part One DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 1
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 2
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 3
View Ridge Gallery Database Design DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 4
SQL DDL and DML DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 5
The Database Design for ARTIST and WORK DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 6
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 7
Data Types DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 8
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 9
Creating Relationships DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 10
Implementing Cardinalities DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 11
Default Values and Data Constraints 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
ALTER Statement • ALTER statement changes table structure, properties, or constraints after it has been created • Example: ALTER TABLE ASSIGNMENT ADD CONSTRAINT Employee. FK FOREIGN KEY (Employee. Num) REFERENCES EMPLOYEE (Employee. Number) ON UPDATE CASCADE ON DELETE NO ACTION; DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 14
Adding and Dropping Columns • The following statement will add a column named My. Column to the CUSTOMER table: ALTER TABLE CUSTOMER ADD My. Column Char(5) NULL; • You can drop an existing column with the statement: ALTER TABLE CUSTOMER DROP COLUMN My. Column; DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 15
Adding and Dropping Constraints • ALTER can be used to add a constraint as follows: ALTER TABLE CUSTOMER ADD CONSTRAINT My. Constraint CHECK ([Name] NOT IN ('Robert No Pay')); • ALTER can be used to drop a constraint: ALTER TABLE CUSTOMER DROP CONSTRAINT My. Constraint; DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 16
Removing Tables • SQL DROP TABLE: DROP TABLE [TRANSACTION]; • If there are constraints: ALTER TABLE CUSTOMER_ARTIST_INT DROP CONSTRAINT Customer_Artist_Int_Customer. FK; ALTER TABLE [TRANSACTION] DROP CONSTRAINT Transaction. Customer. FK; DROP TABLE CUSTOMER; DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 17
SQL DML - INSERT • INSERT command: INSERT INTO ARTIST ([Name], Nationality, Birthdate, Deceased. Date) VALUES ('Tamayo', 'Mexican', 1927, 1998); • Bulk INSERT: INSERT INTO ARTIST ([Name], Nationality, Birthdate) SELECT [Name], Nationality, Birthdate FROM IMPORTED_ARTIST; DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 18
David M. Kroenke’s Database Processing Fundamentals, Design, and Implementation (10 th Edition) End of Presentation: Chapter Seven Part One DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 19
David M. Kroenke’s Database Processing: Fundamentals, Design, and Implementation Chapter Seven: SQL for Database Construction and Application Processing Part Two DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 20
SQL DML: UPDATE • UPDATE command: UPDATE SET WHERE CUSTOMER City = 'New York City' Customer. ID = 1000; • Bulk UPDATE: UPDATE SET WHERE CUSTOMER Area. Code = '333' City = 'Denver'; DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 21
SQL DML: DELETE • DELETE command: DELETE FROM CUSTOMER WHERE Customer. ID = 1000; • If you omit the WHERE clause, you will delete every row in the table! DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 22
JOIN ON Syntax • JOIN ON syntax: SELECT FROM ON CUSTOMER. Name, ARTIST. Name CUSTOMER JOIN CUSTOMER_ARTIST_INT CUSTOMER. Customer. ID = CUSTOMER_ARTIST_INT. Customer. ID JOIN ARTIST ON CUSTOMER_ARTIST_INT. Artist. ID = ARTIST. Artist. ID; • Use of aliases: SELECT FROM ON C. Name, A. Name CUSTOMER AS C JOIN CUSTOMER_ARTIST_INT AS CI C. Customer. ID = CI. Customer. ID JOIN ARTIST AS A ON CI. Artist. ID = A. Artist. ID; DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 23
DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 24
Outer Joins • Left Outer Join: SELECT FROM ON C. [Name] Customer, A. [Name] Artist CUSTOMER C LEFT JOIN CUSTOMER_ARTIST_INT CI C. Customer. ID = CI. Customer. ID LEFT JOIN ARTIST A ON CI. Artist. ID = A. Artist. ID; DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 25
Result of Outer Join DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 26
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 DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 27
SQL Views DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 28
CREATE VIEW Command • CREATE VIEW command: CREATE 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 29
Updateable Views DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 30
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 31
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 32
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 33
DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 34
DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 35
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 36
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 37
DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 38
Triggers vs. Stored Procedures DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 39
David M. Kroenke’s Database Processing Fundamentals, Design, and Implementation (10 th Edition) End of Presentation: Chapter Seven Part Two DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 40
- Slides: 40