CSC 453 Database Systems Lecture 1 - facsrv.cs.depaul.edu

CSC 453 Database Systems Lecture 1 Facsrv Cs Depaul Edu-ppt Download

  • Date:09 Aug 2020
  • Views:12
  • Downloads:0
  • Size:534.50 KB

Share Presentation : CSC 453 Database Systems Lecture 1 Facsrv Cs Depaul Edu

Download and Preview : CSC 453 Database Systems Lecture 1 Facsrv Cs Depaul Edu

Report CopyRight/DMCA Form For : CSC 453 Database Systems Lecture 1 Facsrv Cs Depaul Edu


Transcription:

CSC 453 Database SystemsTanu MalikCollege of CDMDePaul University A general purpose procedural programming.
that includes SQL commands PL SQL can create and issue SQL statements store and process the results of queries define procedures to respond to database events.
Basic Structure of Code Simplest form is an anonymous block variable and subprogram declarations every statement must end with a PL SQL statements to execute.
every statement must end with a statements can be nested with another B E exception handling optional To display output RAISE NOTICE string arguments .
Output buffer displayed in DBMS Output tab Use View Dbms Output and to open tab Single line comments with Multi line with Data Types.
Numeric Character Boolean Datetime Data types are not case sensitive.
num1 INTEGER num2 REAL num3 DOUBLE PRECISION BEGIN null Declaring Variables.
All variables must be declared varName CONSTANT dataType NOT NULL initialValue Assignments use and PL SQL has typicalarithmetic operations Global variables.
num1 number 95 num2 number 85 dbms output put line Outer Variable num1 num1 dbms output put line Outer Variable num2 num2 Local variables.
num1 number 195 num2 number 185 dbms output put line Inner Variable num1 num1 dbms output put line Inner Variable num2 num2 Declaring Variables.
Only one variable can be declared per line butvariable types can be given in terms of thedomain of another variable or attribute varName otherVar type varName TABLE Attribute type .
Operators Arithmetic operators Relational operators Comparison operators LIKE BETWEEN IN IS NULL.
Logical operators String operators Branching if then if else if condition then if condition then.
true statements true statements end if else false statements Branching if elsif .
if condition1 then true statements elsif condition2 then false true statements elsif condition3 then.
false false true statements as many times as needed all false statements Case StatementCASE expression .
WHEN condition 1 THEN result 1WHEN condition 2 THEN result 2WHEN condition n THEN result nELSE result Case Statement.
expression Optional It is the value that you are comparing to the list ofconditions ie condition 1 condition 2 condition n condition 1 condition 2 condition n The conditions that must all be the same datatype The conditions are.
evaluated in the order listed Once a condition is found to be true theCASE statement will return the result and not evaluate the conditionsany further result 1 result 2 result n Results that must all be the same datatype This is the value returned.
once a condition is found to be true Case Statement If no condition is found to be true then theCASE statement will return the value in theELSE clause .
If the ELSE clause is omitted and no conditionis found to be true then the CASE statementwill return NULL Case Statement Multiple if then else better expressed by CASE.
grade CHAR 1 grade B CASE gradeWHEN A THEN DBMS OUTPUT PUT LINE Excellent... WHEN B THEN DBMS OUTPUT PUT LINE Very Good .
WHEN C THEN DBMS OUTPUT PUT LINE Good WHEN D THEN DBMS OUTPUT PUT LINE Fair WHEN F THEN DBMS OUTPUT PUT LINE Poor ELSE DBMS OUTPUT PUT LINE No such grade General loop While loop .
loop while condition loop loop body loop body end loop end loop Repeats until exit is Repeats until conditionexecuted in loop body is false.
For loop for variable in reverse lower upper loop loop body Can only increment decrement by one lower always appears before upper in header.
Incorporating SQL Queries Result of a query can be stored in a set ofvariables by adding INTO clause to query SELECT list of attributesINTO list of variables.
FROM list of tables Variable types must match attribute types Procedures In Oracle CREATE OR REPLACE PROCEDURE name paramName IN OUT paramType AS.
declarations body of procedure IN parameters are passed by value for input only read only parameters OUT parameters are passed by reference IN OUT parameters are passed by reference to return results to the calling.
sub program FunctionsCREATE OR REPLACE FUNCTIONmode IN OUT INOUT name RETURNS returnType AS declarations .
body of function return returnValue language plpgsql IN parameters are default Specify return type and return value instead.
Executing Procedures and A standalone procedure Using the EXECUTE keyword Calling the name of the procedure from a PL SQL A standalone function.
Calling the name of the function from a PL SQL Calling the name of the function in a SQL query A cursor represents a pointer into a set ofrecords returned by a querydeclare name cursor for query .
cursor name can be used to iterate throughthe records returned by query Cursor Commands Expressions open name initializes to beginning of set fetch name into variableList .
reads the next record into the variables close name closes the cursor Parameterized Cursors Can supply a parameter in cursor declarationdeclare name parameter in type cursor for query .
Each time cursor is opened value ofparameter is specified in parentheses tocomplete the query Implicit Cursors for DMLstatements.
Whenever a DML statement INSERT UPDATEand DELETE is issued an implicit cursor isassociated with this statement INSERT operations the cursor holds the datathat needs to be inserted .
UPDATE and DELETE operations the cursoridentifies the rows that would be affected Data structure to hold data items of different kinds Table based Records Can create a record with same structure as the row of a.
table fields are table attributes recordName TABLE rowtype Can select a row of a table directly into a record andaccess individual fields withrecordName Attribute.
Cursor based records Assign rowtype from aquery in cursor User defined Records Declare a new data type and a table of records create type newType .
attr1 datatype attr2 datatype Exceptions declarations section executable command s .
exception handling goes here WHEN exception1 THEN exception1 handling statementsWHEN exception2 THEN exception2 handling statementsWHEN exception3 THEN exception3 handling statements WHEN others THEN exception3 handling statements.
Practice Question 1 Practice Question 2How many times will the loop run Practice Question 3What is wrong with this code snippet .
Practice Question 4Which of the following will open a cursor name cur employee Database Active Elements Checks Assertions.
Triggers Why Active Elements Other wise application programs have toinclude checks with every DML statement topreserve data integrity .
Better to store checks in DBMS and let DBMSadminister the checks User defined verified when a tuple is added or Attribute level CHECK within an attribute can only involve that attribute.
Verified when a tuple is inserted updated in that attribute Tuple level A separate CONSTRAINT can involve any attributes Verified when a tuple is inserted updated in any attribute Attribute level Check.
create table enrolled StudentID number 5 CourseID number 4 Quarter varchar 6 CHECK quarter in Fall Winter Spring .
Year number 4 has to be true compare WHERE create table memberof attribute checks get evaluated when anStudentID number 5 attribute is modified i e when row isGroupName varchar 40 inserted updated.
Joined number 4 subqueries not allowed in Oracle checksCHECK Joined SELECT StartedFROM studentWHERE studentID SID Tuple level CHECK.
create table course CID number 4 CourseName varchar 40 Department varchar 4 CourseNr char 3 .
primary key CID check department CSC OR CourseNR 100 same as attribute level check just involves anynumber of attributes and different placement Assertion.
Boolean value SQL expression that is true at all Hard to implementCREATE ASSERTION joinedCHECK NOT EXISTSFROM student memberof.
WHERE SID StudentID and Joined Started not supported by anybody can be mimicked using materialized views and or Triggers Triggers allow general responses to changes in.
the database state Enforcement of business rules Notification of events Maintenance of derived information Maintenance of replicated data.
Implementation of cross table constraints Event Condition Action Model An event causes the trigger to fire Before or after an insertion deletion or update A condition is tested to see whether or not the.
trigger will respond optional May depend on original state or modified state An action may be executed in response May be sequence of SQL statements or somestored procedure.
Oracle Trigger SyntaxCREATE OR REPLACE TRIGGER NameBEFORE AFTER INSERT DELETE UPDATE OF Attribute ON Table REFERENCINGOLD AS OldName.
NEW AS NewName FOR EACH ROW WHEN condition PL SQL statements or stored procedure Oracle Trigger Syntax.
BEFORE AFTER Indicates whether queries on TABLE will beperformed on the original state of the table or themodified state INSERT DELETE UPDATE OF Attribute .
Indicates what operation s will cause the trigger Oracle Trigger Syntax REFERENCING OLD AS OldName NEW AS Re names old and new row states only allowed iftrigger is row level .
FOR EACH ROW If included the trigger fires once for each row thatis modified row level if not the trigger fires justonce for the entire table statement level Oracle Trigger Syntax.
WHEN condition Condition tested to see if the trigger action willactually execute can refer to new and old row PL SQL statements The code that is executed when trigger fires and.
condition is satisfied may call other storedprocedures functions Trigger Restrictions new and old can only refer to row states sothey can only be used for row level triggers.
Use new and old in WHEN condition newand old elsewhere Subqueries are not allowed in WHEN PL SQL block in a row level trigger cannot queryor modify the table that triggered the action.
BEFORE vs AFTER Order of trigger execution All BEFORE execute in arbitrary order but before all AFTER triggers Cannot modify new values after.
UPDATE INSERT Triggers for Other Events Trigger attached to DDL commands E g BEFORE AFTER DROP ON username SCHEMA Trigger attached to Views.
INSTEAD OF INSERT ON View Allows updates where they cannot be doneautomatically Triggers attached to other events CREATE TRIGGER log errors AFTER.
SERVERERROR ON DATABASE TriggersCREATE OR REPLACE TRIGGER startedBEFORE UPDATE OF started ON studentFOR EACH ROW.
WHEN new started old started new started old started DBMS OUTPUT PUT LINE Rejected change ofstarted SET SERVEROUTPUT ON .
UPDATE studentSET Started 2001 SELECT FROM student Triggers Create triggerCREATE OR REPLACE TRIGGER started.
triggering eventBEFORE UPDATE OF started ON studentattribute tableFOR EACH ROWrow trigger.
WHEN new started old started trigger restrictionold row before updatenew row after update new started old started DBMS OUTPUT PUT LINE Rejected.
change of started END trigger action in PL SQL Triggering EventsCREATE OR REPLACE TRIGGER started.
When do we trigger BEFORE UPDATE OF started ON studentFOR EACH ROWWHEN new started old started before after new started old started .
instead of DBMS OUTPUT PUT LINE Rejected only for views change of started What is doing the triggering insert update delete system events.
row statement triggerCREATE OR REPLACE TRIGGER startedBEFORE UPDATE OF started ON studentfor row levelFOR EACH ROW triggers.
WHEN new started old started CSC 453 Database Systems Lecture Tanu Malik College of CDM DePaul University

Related Presentations

CSC 453 Database Systems Lecture 1 DePaul University

Design a general programming language that includes database commands (e.g., PL/SQL) First approach can suffer from impedance mismatch * Impedance Mismatch Data model in SQL differs from the models of other languages Other languages: record, structures, arrays, pointers SQL: sets, no arrays, pointers, loops, branches A host and a SQL language ...

24 Views0 Downloads

CSC 380 Foundations of Artificial Intelligence CSC 480 Artificial Intelligence I

The term “Artificial Intelligence” (AI) was coined in 1956 by John McCarthy, “the science and engineering of making intelligent machines” ... Modern systems can rely on both general and domain specific publicly available knowledge graphs. Google Knowledge Graph. Wikipedia.

5 Views0 Downloads

ECE 453 Lecture 1 UTK

Arial Comic Sans MS Wingdings Times New Roman Cordia New Symbol Times Helvetica Digital Dots 1_Digital Dots Microsoft Equation 3.0 Microsoft Equation ECE-517: Reinforcement Learning in Artificial Intelligence Lecture 12: Generalization and Function Approximation Outline Introduction General Approach Value Prediction with FA Adapt Supervised ...

21 Views0 Downloads

ECE 453 Lecture 1

ECE 517: Reinforcement Learning in Artificial Intelligence Lecture 21: Dynamic Multi-Criteria RL problems November 16, 2009 Dr. Itamar Arel College of Engineering Department of Electrical Engineering and Computer Science The University of Tennessee Fall 2009 *

1 Views0 Downloads

ECG 453 653 Introduction to Nanotechnology

The resulting solid (a version of soot) is air classified to recover oxide particles from by-product gases. Flash memory using Nanoparticles: Current flash Memory manufacturing technology forecasts no further developments in terms of density after 2012.

0 Views0 Downloads

COMP108 Lecture cgi csc liv ac uk

(Basics) Shortest path to go from A to B. How many paths between A & B? involving . 5. intermediate cities? A. B. For large n, it’s impossible to check all paths! We need more s

10 Views0 Downloads

CPE CSC 481 Knowledge Based Systems

CPE/CSC 481: Knowledge-Based Systems ... Judges’ bias for/against computers Judges did not know who recommended each therapy Difficulty of problems Medical student did badly, so problems not easy Level of Interest Hypothesis in MYCIN that “knowledge is power” Have groups with different levels of knowledge A. Gray, 2004 Good Points MYCIN ...

12 Views0 Downloads

CSC 501 Operating Systems

Early Operating Systems: Just Libraries. Implements commonly-used functionalities. Beyond Libraries: Protection. Systemcall. The Era of Multiprogramming. Unix. The Modern Era. Questions? Miss something? Reading for the next lecture. Book chapters on Virtualization. Dialogue. Processes. Process API. Direct Execution. Author: Guoliang Jin

0 Views0 Downloads

Lecture 1 Course Overview Database Systems

Choose 2 of 3. CA – data should be consistent between all nodes. As long as all nodes are online, users can read/write from any node and be sure that the data is the same on all nodes.

17 Views0 Downloads

Database Models Flat Files and the Relational Database

Database Models: Flat Files and the Relational Database Objectives: Understand the fundamental structure of the relational database model Learn the circumstances under which it is a better choice than the flat file

26 Views0 Downloads

Database Security with focus on Hyperion Database

Hyperion application security determines user access to products using the concept of roles. A role is a set of permissions that determines user access to product functions. User directories store information about the users who can access Hyperion products. Both the Authentication and the authorization processes utilize user information.

22 Views0 Downloads

CSC 2231 Parallel Computer Architecture and Programming

CSC 2231: Parallel Computer Architecture and ProgrammingMemory Hierarchy & Caches. Prof. Gennady Pekhimenko. University of Toronto. Fall 2017. The content of this lecture is adapted from the lectures of

24 Views0 Downloads