Information Technology 541
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 databse course including hands-on experience with the relational model, SQL, security, databse 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
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.
A significant component of the assessment must measure each individual student’s 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
Review and Approval
May 1, 2018
April 23, 2014