Database Technology

Database Technology Jing Shen

Topics • Introductions • SQL Statements • View • Store Procedure/Functions • Triggers • Example of Object-Oriented Relational DB

SQL has several parts • DDL – Data Definition Language • DML – Data Manipulation Language • Embedded SQL – Designed for use within a host language • Authorization or DCL – Data Control Language • Integrity

Define Database and DBMS • Database: A collection of related data. • DBMS: A set of programs that enable users to create and maintain a database.

Data Models • It is a collection of concepts that can be used to describe the structure of a database. • Conceptual Data Model • Physical Data Model • Representational (or Implementation) Data Model

Three types of Representational Data Model • Relational Data Model • Hierarchical Data Model • Network Data Model

DML (Data Manipulation Language) • INSERT – insert into table_name[(column, [column]…)] {values ( expr[,expr]…) | subquery} • DELETE – delete [From] table_name [WHERE condition] • Update – update table_name set field = [, field = , …] [ WHERE condition ];

Transaction • A transaction is a sequence of SQL statements that Oracle treats as a single unit. • COMMIT; • ROLLBACK; “autocommit” is set on

SQL Join • The INNER JOIN returns all rows from both tables where there is a match. • Example: SELECT Employees.Name, Orders.Product FROM Employees INNER JOIN Orders ON Employees.ID = Orders.ID

Joining a table to itself • Return all the results where the attribute is in the same table. Example: select s1.s#, s2.s# from s s1, s s2 where s1.city = s2.city and s1.s#<s2.s#; Get all pairs of supplier numbers such that the two suppliers are located in the same city.

Continues… • The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). Example: SELECT Employees.Name, Orders.Product FROM Employees LEFT JOIN Orders ON Employees.ID = Orders.ID

Continues… • The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employee). Example:SELECT Employees.Name, Orders.Product FROM Employees RIGHT JOIN Orders ON Employees.ID = Orders.ID

View • A view is logical table • Data does not physically exist (no storage) • Views are defined in terms of base tables “created by SELECT statement”

Store Procedures • It allows for sharing o PL/SQL code by different applications running at different places. Syntax: create [or replace] procedure <proc-name> [(<parameter-list>)] as <declarations> begin <executable-section> end; Function: create [or replace] function <func-name> [(<parameter-list>)] return <datatype> as

Example of how user defined exceptions Set serveroutput on; declare incorrect_first_name exception; fname employee.first_name%type; lname employee.last_name%type; cursor a is select last_name, first_name from employee; begin open a; loop fetch a into lname, fname; dbms_output.put_line (fname); if (a%notfound) then exit; end if; if (fname = ‘ELINOR’) then raise incorrect_first_name; end if; end loop; exception when incorrect_first_name then dbms-output.put_line (“Wrong spelling.”); end;

Example of Function P4.sql: DECLARE enum customers.cno%type; total number(10,2); status boolean; function total_emp_sales( emp_no IN employees.eno%type) return number is sales number; begin select sum(price*qty) into sales from orders,odetails,parts where orders.eno = emp_no and orders.ono = odetails.ono and odetails.pno = parts.pno; return (sales); end; BEGIN enum := 1000; total := total_emp_sales(enum); DBMS_OUTPUT.PUT_LINE('Total sales for employee ' || enum || ' is ' || total); END; /

Trigger • It is a mechanism that automatically executes a specified PL/SQL block when a triggering event occurs on a table. The triggering event may be one of insert, delete, or update.

Example of Triggers CREATE OR REPLACE TRIGGER delete_orders BEFORE DELETE ON orders FOR EACH ROW BEGIN DELETE FROM odetails WHERE ono = :old.ono; END; /

Packages • A package is a group of related PL/SQL objects (variables, constants, types, and cursors), procedures, and functions that is stored in the database. • It consists of two parts: Package specification and the package body.

Example of Package create or replace package process_orders as procedure add_order_details (onum IN odetails.ono%type, pnum IN odetails.pno%type, quantity IN odetails.qty%type); procedure add_order (onum IN orders.ono%type, cnum IN orders.cno%type, enum IN orders.eno%type, receive IN date); procedure ship_order (onum IN orders.ono%type, ship IN date); end; /

Example of Relational/Object-Oriented Database Structure

References: • http://www.w3schools.com/sql/sql_join.asp • Oracle Book

Database Technology