The second in a two course sequence applies the knowledge of BCS260 to administer and implement relational database systems. Topics covered may include: embedded SQL and other mixed language mechanisms; PL/SQL; advanced/optimized SQL queries; transaction management including concurrency and recovery; schema refinement; higher-level normal forms; integrity; security; and database administration. Credits: 3.00
BCS 260 with a grade of C or better.
At the completion of this course, students willl
1. Understand and use the database language SQL in a command line interface
2. Understand data integrity concepts and write SQL declarative constraints to provide integrity within a commercial database system
3. Know how to use a view as a security device to restrict user accessibility; and to allow DML operations to base tables through a view.
4. Understand the notion of a transaction using implicit/explicit commit and the savepoint, commit, and rollback statements.
5. Understand concurrency, consistency and general locking concepts.
6. Understand both the benefits of PL/SQL blocks and the assigned textbook code examples.
7. Understand the use of cursor as used in: PL/SQL block and C++ application program.
8. Understand the logical & physical structures of the database: the Tablespaces and physical data files; schema objects in user's domain: tables, view, indexes, sequences, etc.; data storage: data blacks, extents, segments; segments: data, index, rollback, temporary, bootstrap, extents: initial, next and storage size.
9. Understand the security features to provide access to database objects via privileges and roles.
Celko, SQL for Smarties, Fourth Edition: Advanced SQL Programming (The Morgan Kaufmann Series in Data Management Systems), ISBN: 0123820227