Chapter Seven:SQL for Database

Constructionand Application

Processing

Chapter Objectives• To create and manage table structures using SQL statements• To understand how referential integrity actions are

implemented in SQL statements• To create and use SQL constraints• To understand several uses for SQL views• To use SQL statements to create and use views• To understand how SQL is used in an application

programming• To understand SQL/Persistent Stored Modules (SQL/PSM)• To understand how to create and use functions• To understand how to create and use triggers• To understand how to create and use stored procedures

7-2

Data Modeling in the SDLC

Database construction occurs in the implementation step of the SDLC

The systems development life cycle (SDLC) as discussed in Appendix B

The final database is part of the final system ready for users to use

7-3

SQL Categories• SQL statements can be divided into five

categories:– Data definition language (DDL)– Data manipulation language (DML)

statements– SQL/Persistent Stored Modules (SQL/PSM)

statements– Transaction control language (TCL)

statements– Data control language (DCL) statements

7-4

SQL DDL• Data definition language (DDL)

statements– Used for creating tables, relationships, and

other structures– Covered in this chapter

7-5

SQL DML• Data manipulation language (DML)

statements– Used for:

• Queries – SQL SELECT statement• Inserting data – SQL INSERT statement• Modifying data – SQL UPDATE statement• Deleting data – SQL DELETE statement

7-6

SQL SQL/PSM• SQL/Persistent Stored Modules

(SQL/PSM) statements– Addition SQL that adds procedural

programming capabilities• Variables• Control-of-flow statements• Uses

– Triggers – Stored Procedures

7-7

IF USER() = 'adssys' THEN  UPDATE Employee SET "Modified By" = 'SYSTEM'    WHERE "Employee Number" =    (SELECT "Employee Number" FROM __new);ELSE  UPDATE Employee SET "Modified By" = USER()    WHERE "Employee Number" =    (SELECT "Employee Number" FROM __new);ENDIF

SQL TCL• Transaction control language (TCL)

statements– Used to mark transaction boundaries and

control transaction behavior• Commit• Rollback• Savepoint

7-8

SQL DCL• Data control language (DCL) statements

– Used to grant (or revoke) database permissions to (from) users and groups

– Example:

7-9

GRANT SELECTON HR.employees TO Joe

REVOKE SELECTON HR.employees FROM Joe

Joe can see the employees table

Joe can not see the employees table

Chapter 7 SQL Elements

7-10

• CREATE TABLE statement is used for creating relations.

• Each column is described with three parts: column name, data type, and optional constraints.

• Format:

SQL CREATE TABLE Statement

7-11

• 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.

• Column and table constraints include:– PRIMARY KEY ─ may not have NULL values– FOREIGN KEY ─ may not have NULL values– NULL / NOT NULL– UNIQUE– CHECK

• The DEFAULT keyword (not a constraint)

Column and Table Constraints

7-12

SQL CREATE TABLE Statement Example I

Column Characteristics:

7-13Sample to play with

SQL CREATE TABLE Statement Example II

SQL CREATE TABLE statement:

7-14

Auto–incrementStart at one and increment by 1

Creating Relationships III

7-15

Added if no value is specified

FOREIGN KEY enforces the relationship

SQL for Constraints

7-16

SQL ALTER TABLE Statement• The SQL ALTER TABLE statement changes

table structure, properties, or constraints after it has been created.

• ExampleALTER TABLE ASSIGNMENTADD CONSTRAINT EmployeeFK

FOREIGN KEY (EmployeeNumber) REFERENCES EMPLOYEE

(EmployeeNumber)ON UPDATE CASCADEON DELETE NO ACTION;

7-17

Adding and Dropping Columns• The following statement will add a column

named MyColumn to the CUSTOMER table:– Note that the SQL COLUMN keyword is not used!

• You can drop an existing column with the statement:

7-18

Adding and Dropping Constraints

• The SQL ALTER TABLE statement can be used to add a constraint:

• The SQL ALTER TABLE statement can be used to drop a constraint:

7-19

Removing Tables I

• The SQL DROP TABLE statement:

• If there are constraints:

ALTER TABLE CUSTOMER_ARTIST_INTDROP CONSTRAINT

Customer_Artist_Int_CustomerFK;ALTER TABLE TRANS

DROP CONSTRAINT TransactionCustomerFK;DROP TABLE CUSTOMER;

7-20

May need to alter constraints of other tables first

Removing Tables II• If there are constraints:

• or

7-21

Removing Data Only

• The SQL TRUNCATE TABLE statement:

• Deletes the data in the table• Cannot be used with a table that is referenced

by a foreign key constraint.• Resets surrogate key values to initial value.

7-22

SQL DDL—CREATE INDEX• An index is a data structure used to improve

database performance.• The SQL CREATE INDEX statement• The SQL ALTER INDEX statement• The SQL DROP INDEX statement

7-23

SQL DML—INSERT I

• The SQL INSERT statement:

7-24

You’ve done this already

SQL DML—INSERT II

• Bulk INSERT:

7-25

We did this when filling out tables from existing data.

SQL DML—UPDATE I

• The SQL UPDATE statement:

7-26

SQL DML—UPDATE II

• Bulk UPDATE:

7-27

SQL DML—UPDATE III

• Using values from other tables:

7-28

SQL DML—MERGE

• The SQL MERGE statement:

7-29

INSERT & UPDATE put together in one SQL command

SQL DML—DELETE

• SQL DELETE statement:

• If you omit the WHERE clause, you will delete every row in the table.

• Does not reset surrogate key values.

7-30

Using Aliases

• Use of aliases:SELECT C.Name, A.NameFROM CUSTOMER AS C

JOINCUSTOMER_ARTIST_INT AS CI

ON C.CustomerID = CI.CustomerIDJOIN ARTIST AS A

ON CI.ArtistID = A.ArtistID;

• DBMS products differCUSTOMER AS C versus CUSTOMER C

7-31

SQL Views• An 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.

7-32

SQL Views

7-33

SQL CREATE VIEW Statement I

• The SQL CREATE VIEW statement:

• In the SQL standard, views do not support the SQL ORDER BY clause.– Individual DBMS products may support the SQL ORDER BY

clause – see documentation.

7-34

SQL CREATE VIEW Statement II• To see the results, use an SQL SELECT statement with

the view name as the table name in the FROM clause:

7-35

Try an ORDER BY

SQL ALTER VIEW Statement I

• The SQL ALTER VIEW statement:

• In the Oracle Database or MySQL 5.6, use the SQL CREATE OR REPLACE VIEW statement.– This allows creation and modification of SQL VIEW code.

7-36

Just switching order here

Updateable Views

7-37

Can update underlying table

Embedding SQL in Program Code

• SQL cursors are used to select one row at a time from pseudo-files.

• 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

7-38

We do this in PhP by saving the return of the mysqli query.

If you remember, our mysqli queries return one row at a time into an array

Variables in Program Code

• Oracle Database and MySQL 5.6 style variables:

7-39

Other platform’s way of doing it

SQL Cursors in Program Code• SQL can be embedded in triggers, stored procedures,

and program code.• A typical cursor code pattern is:

7-40

Cursors create a pointer to a particular row, that way we can assign variables to it.We do this a different way for web applications in PhP

SQL/Persistent Stored Modules• SQL/Persistent Stored Modules (SQL/PSM) is an

extension to standard SQL• It adds procedural extensions like IF, WHILE, ..etc.• Each DBMS implements SQL/PSM in a different way.

– We would use this in triggers and stored procedures– It has TRY/CATCH functionality– TRY

  CREATE TABLE Test( id integer, name char( 20 ) );CATCH ADS_SCRIPT_EXCEPTION  -- Only do something if the error code indicates  -- table already exists  IF __errcode = 2010 or __errcode = 5189 THEN     DROP TABLE Test;    CREATE TABLE Test( id integer, name char( 20 ) );  ELSE    RAISE;   -- re-raise the exception  END IF;END TRY;

7-41

If table exists

If something else is wrong

User-Defined Functions • A user-defined function (stored function) is a stored

set of SQL statements that:– is called by name from another SQL statement– may have input parameters passed to it by the calling SQL

statement, and– returns an output value to the SQL statement hat called the

function.

7-42

User-Defined Functions

7-43

/* DELIMITER changes the statement delimiter from ; to // this is so we can use the ; in the function/*DELIMITER //CREATE FUNCTION ComputerMakeAndModel(Make CHAR(12), Model CHAR(24))RETURNS VARCHAR(40)BEGIN

DECLARE MakeAndModel VARCHAR(40);SET MakeAndModel = CONCAT(Make, ': ', Model);RETURN MakeAndModel;

END; //DELIMITER ;

User-Defined Functions IIIThe NameConcatenation Function

7-44

/*** Test Function ***/SELECT "Output of the procedure";SELECT SerialNumber, ComputerMakeAndModel(Make, Model) AS ComputerTypeFROM COMPUTER;

Should really say “function”

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 (INSTEAD OF and

AFTER).• MySQL supports six trigger types (BEFORE and AFTER).

7-45

Triggers II

7-46

7-47

SELECT "About to run trigger";/*************** Simple Trigger **************/

delimiter //CREATE TRIGGER is_old BEFORE INSERT ON COMPUTERFOR EACH ROWBEGIN

IF NEW.ProcessorSpeed < 3.5 THENSET NEW.Is_Old = "Is Old";

ELSESET NEW.Is_Old = "Is New";

END IF;END;//DELIMITER ;

Sample Trigger

This runs every time a record is inserted to the COMPUTER table

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.

7-48

Stored Procedure Advantages

7-49

7-50

Example artist table

7-51

Triggers vs. Stored Procedures

7-52

Functions, Triggers, and Stored Procedures

7-53

Database Design and Implementation Summary

7-54

End of Presentation:Chapter Seven

7-55