Purdue School of Engineering and Technology

Purdue School of Engineering and Technology

Database Programming

CIT 30400 / 3 Cr.

This course explores the concepts and skills required for advanced database programming and their implementation using programmatic extensions to Structured Query language (SQL).  Topics include advanced data manipulation, stored procedures, triggers, and query optimization. Concepts will apply to any modern distributed database management system.

  • Oracle SQL Developer


Course Outcomes (What are these?)

  • Understand database design issues (CIT c)
  • Write complex SQL queries (CIT a)
  • Use advanced DML and DDL statements (CIT i)
  • Explain what PL/SQL is and why it is needed (CIT i)
  • Create PL/SQL blocks (CIT c)
  • Use a variety of variable types to handle data in a block (CIT c)
  • Conditionally process statements using control structures (CIT c)
  • Manage errors with exception handlers (CIT c)
  • Create and use procedures and functions (CIT c)
  • Bundle programs units with packages (CIT c)
  • Develop database triggers (CIT c)
  • Identify and manage program unit dependencies (CIT i)
  • Use object relational features (CIT i)
  • Perform basic statement tuning (CIT m)

CIT Student Outcomes (What are these?)

(a) An ability to apply knowledge of computing and mathematics appropriate to the program’s student outcomes and to the discipline.

(c) An ability to design, implement, and evaluate a computer-based system, process, component, or program to meet desired needs.

(i) An ability to use current techniques, skills, and tools necessary for computing practice.

(m) An understanding of best practices and standards and their application.

  • Database Design & Complex Queries
  • SQL Functions
  • Advanced DML and DDL
  • PL/SQL Basics
  • Exception Handling
  • Stored Procedures & Functions
  • PL/SQL Packages
  • Program Unit Dependencies
  • Database Triggers
  • Large objects and object relational features
  • Performance Tuning
What You Will Learn

Database Design & Complex Queries

  • Create E-R diagrams to document logical database requirements
  • Transform E-R diagram into a relational database schema
  • Transform database tables using normalization
  • Correctly use group by, decode, and exists in queries
  • Correctly use inner and outer joins and subqueries

SQL Functions

  • Transform data using SQL number, string and date scalar functions

Advanced DML and DDL

  • Write DDL to perform conditional inserts
  • Perform updates using subqueries
  • Successfully merge tables
  • Use DML to create savepoints, constraints, indexes, and sequences

PL/SQL Basics

  • Create basic PL/SQL blocks
  • Declare and initialize variables and use NULL, CONSTANT, and %TYPE options
  • Correctly use PL/SQL decision and loop structures
  • Perform calculations using variables.
  • Use SQL single-row functions.
  • Write queries that return single values into scalar variables.
  • Use record and collection structures that contain more than one value.
  • Embed DML statements in PL/SQL

Exception Handling

  • Manage errors with exception handlers
  • Address exception-handling issues such as RAISE_EXCEPTION_ERROR and propagation

Stored Procedures & Functions

  • Create procedures and functions with and without parameters
  • Understand and control how parameters values are passed in procedures and functions
  • Call procedures and functions from another procedure
  • Use subprograms
  • Use multiple RETURN statements in a function
  • Explain the scope of variables, exception handling and transaction control

PL/SQL Packages

  • Organize program units using packages
  • Invoke a program unit in a package
  • Use a forward declaration
  • Create one-time-only procedure in a package.
  • Overload a program units
  • Manage restrictions on packaged functions
  • Use a cursor variable in a package

Program Unit Dependencies

  • Understand the impact of program unit and package dependencies.
  • Distinguish between direct & indirect dependencies
  • Find information on dependences using the data dictionary.
  • Use the dependency tree utility to track indirect dependencies.
  • Grant program unit privileges.
  • Compile program units.

Database Triggers

  • Create and test various types of triggers: DML, instead of, user event
  • Use compound triggers
  • Create and test a user event trigger
  • Set the firing order of triggers
  • Correctly identify when triggers should be used
  • Retrieve data dictionary information about triggers.

Large objects and object relational features

  • Define and implement LOBs, VARRAYs, and nested tables

Performance Tuning

  • Identify resource-intense SQL statements using V$SQLAREA and SQL TRACE
  • Explain how statement processing and options are managed
  • Use explain plan to find processing information
  • Learn common methods to increase performance