Pre-Requisite Modules	Co-Requisite Modules	ECTS Credits	Module Code
SENG2201 PROG2301	none	5	SENG4204

This module expands the students’ knowledge of SQL by introducing embedded procedural and object-oriented coding. Students learn to create blocks of application code in a database specific procedural language that can be shared by multiple forms, reports, and data management applications. Students learn to create procedures, functions, packages, and database triggers.

Students learn to manage these program units and database triggers, to manage dependencies, to manipulate large objects, and to use some vendor-supplied packages.

The aim of this module is to expand the programmer’s ability to manipulate the database with the help of embedded programming at an application level.

On completion of this module, the learner will be able to:

• Create, execute, and maintain procedures, functions, packages, and triggers
• Manage dependencies Manipulate large objects Conditionally control code flow (loops, control structures, and explicit cursors) Describe vendor-supplied packages Handle runtime errors Manage program constructs Describe the features and syntax of the chosen procedural language.Be aware of equivalences over different Database Management Systems.
• Manipulate the database using embedded object-oriented and procedural coding.

The learning methods will be three-fold:  Lectures (One hour per week) will impart the theory behind the techniques being used; Tutorials (one hour per week) will develop practical applications that will be implemented in practical classes (2 hours per week). Practicals will be done using a database server.

Introduce embedded coding language for the Developer and the Database Administrator, explaining the benefits of embedded coding language. Embedded coding language program constructs; anonymous block structure, Subprogram block structure

Declaring Variables

Basic Block, Sections, Variables (including dynamic variables), Executable Statements, nested blocks, coding conventions, execution and testing. Interact with Oracle Server.  Create embedded DML statements. Write Control Structures, Using Logic Tables Working with Composite Data types and records.

Differentiate Among Records, Tables, and Tables of Records.  Write Explicit Cursors,  Advanced Explicit Cursor Concepts and exception handling, Procedures and functions, sub-functions, privileges, packages and triggers.  Managing Dependencies and Manipulating Large Objects

Continuous Assessment will include both lab tests and programming assignments (50%).  End of module examination will test theoretical knowledge and design. (50%)

Rosenzweig, B. Silvestrova, A. “Oracle PL/SQL by Example”, Prentice Hall, 3^rd Ed. 2004

Connolly, Begg, 2004, Database Systems 3^rd Ed. Addison-Wesley.

Fehily, C., 2002, SQL, Peachpit Press.
Kline, Kline, 2001, SQL in a Nutshell, O’REILLY

http://www.sqlzoo.net,  course web page.

Class to be split into groups of no more than 25. 

4 hours contact per week.

To be delivered in one semester module.

On completion of this course, the student should be eligible to submit for examination to become an Oracle9i PL/SQL Developer Certified Associate

June 2005