ITEC 541: Advanced Database Management Systems
Prerequisite: admission into the Data and Information Management program, or permission
of instructor
Credit hours (3)
Advanced topics and techniques in the modeling and manipulation of large data sets.
Includes in-depth coverage of relational algebra and heavily nested SQL, physical
database design and optimization, triggers and stored procedures, transaction control,
assertions and other data integrity techniques, advanced modeling issues such as temporal
design, and key topics in database administration. Students must implement one or
more aspects of query execution and demonstrate an ability to implement scripts for
common DBA (data base administrator) tasks. Students must have completed a database
course including hands-on experience with the relational model, SQL, security, database
design, and stored procedures.
Detailed Description of Course
鈥� Relational Constructs of Data Manipulation
a. Review of Conceptual Underpinnings of Relational Databases with emphasis on
data independence and its impact on query languages
b. The Relational Algebra
c. Advanced SQL
d. Implementation of retrieval language constructs
鈥� Physical Database Implementations
a. Storage and File Structures
b. Tuning, indexing, and hashing of queries
c. Query Processing with emphasis on query optimization
d. Enterprise Database Tuning Opportunities
鈥� Advanced Logical Design Issues
a. Advanced Constraints, Types, and Assertions
b. Concurrency and Client/Server Systems, Transactions, Transaction Isolation
Levels
c. Temporal Databases and flashback
d. Missing Information
e. Object Relational Databases
f. Large Objects (LOBs)
鈥� Issues in Database Security
a. User Accounts, Roles, Profiles, and Privileges
b. Authentication
c. SQL Injection, Inference, and other common attacks
d. Data and Password Encryption, Password Policies
Detailed Description of Conduct of Course
The course will be delivered in a lecture and discussion format with demonstration
and application of concepts using one or more enterprise level database management
systems.
Goals and Objectives of the Course
Students who complete this course will be able to:
鈥� Describe the key attributes of a data retrieval language. Demonstrate proficiency
with the relational algebra or other mathematically based retrieval language.
鈥� Describe and apply basic concepts of file organization including the properties
and architecture of physical devices such as disk drives.
鈥� Describe and compare methods for efficient data retrieval of persistent data including
indexes, hashing, and sequential access.
鈥� Describe and explain the steps in query processing, evaluate execution plans.
鈥� Implement operations/algorithms from the relational algebra or other retrieval language.
鈥� Explain the purpose of query optimization, recognize opportunities for optimization,
draw and optimize expression trees.
鈥� Perform tuning tasks on an enterprise level DBMS.
鈥� Construct appropriate designs for databases that present significant temporal, null
value, or other complexities.
鈥� Explain the ACID properties of transaction control. Implement transactions with
those properties in stored procedures. Implement triggers for complex constraints.
鈥� Describe and use current extensions of relational database technology such as object-relational
or XML extensions.
鈥� Explain theoretical and practical uses and limitations of nested tables, arrays,
and user-defined types in relational databases.
鈥� Explain options for how large objects (video clips, pictures, documents, etc) are
stored and retrieved from a database and the advantages and disadvantages of each.
鈥� Implement a database application that uses large objects.
鈥� Describe fundamental challenges associated with database security and associate
and describe solutions to those challenges.
鈥� Analyze and manage typical privilege systems for database systems.
鈥� Employ data encryption techniques on an RDBMS.
鈥� Implement Password and/or other authentication policies on an RDBMS.
Assessment Measures
A significant component of the assessment must measure each individual student鈥檚 mastery
of the conceptual and applied knowledge and skills described in the course objectives.
Evaluations may include but are not limited to assignments, projects, presentations,
quizzes, and examinations.
Other Course Information
None
Review and Approval
May 1, 2018
April 23, 2014