| CODE | CIS2080 | ||||||||||||
| TITLE | DBMS Design and Implementation in Software Solutions | ||||||||||||
| UM LEVEL | 02 - Years 2, 3 in Modular Undergraduate Course | ||||||||||||
| MQF LEVEL | 5 | ||||||||||||
| ECTS CREDITS | 6 | ||||||||||||
| DEPARTMENT | Information Systems | ||||||||||||
| DESCRIPTION | This study-unit aims to allow students to demonstrate their knowledge of logical design and database models to construct a sound physical implementation of a software solution. The unit material covers some important pragmatic aspects of DBMS. These aspects include data storage, disk access, file structures, file access, index files, data caches (main memory), database engines, and query processing and optimization. Other pragmatic issues, e.g. data security, are also covered. Functional dependencies and normalization techniques are re-visited to define the relationships within the data and thus reduce duplication. Database assertions and triggers are re-introduced to maintain the data integrity and operational characteristics of an application’s processes. Advanced SQL query operations to handle more complex reporting requirements are introduced. Performance issues regarding optimizing queries, database design set-up, and transactional throughput are discussed. Although relational databases are the leading example, NoSQL databases have also been introduced. Issues dealing with system failure, including problems arising from concurrent use, are studied. Data security and DBMS mechanisms to support it are discussed, too. The operational roles of a database administrator (for example, data backup, data replication decisions, database design reviews, and monitoring of access and resources) are enumerated. Study-Unit Aims: The principle aim is for a candidate to appreciate that many different physical implementations exist for a logical database design. The candidate must understand the fundamental trade-off that describes the satisfaction of physical requirements and how to tune the physical design to meet these. A secondary aim is for a candidate to gain exposure to relational and non-relational data models, e.g. NoSQL, and how these implementations address performance and availability issues. Learning Outcomes: 1. Knowledge & Understanding By the end of the study unit, the student will be able to: • Evaluate a DBMS through its parts; • Understand the computational costs of a DBMS component; • Understand the difference in reliability, volatility and speed of resources made available to a DBMS; • Understand the subtleness of declarative queries in software development; • Understand what query optimization is and how it is undertaken; • Appreciate and understand the limits of query optimizations; • Knowledgeable in physical design best practice pragmatics. 2. Skills By the end of the study unit, the student will be able to apply the following techniques and skills: • Write SQL statements for more involved queries; • Write simple scripts and programs for database administration; • Define different data structures for data coming from a database that is more appropriate for the function at hand; • Understand hardware set-up for reliability, e.g. deploy and use RAID unit set-ups in DBMSs; • Create indexes in various disguises, including, e.g. expression indexes, covering indexes; • Configure and tune B Tree and Extensible Hashing indexes for queries; • Build Bit-mapped indexes and execute joins and “secondary” key searches; • Choose an appropriate index structure for point, range and join queries; • Evaluate the cost of an index construction, keeping out to date, and usage to pattern match; • Evaluate if processing a query is facilitated or not by an index structure; • Translate a logical design into a physical design that best meets the performance requirements indicated; • Identify queries that a DBMS is not computing efficiently (i.e. doing more work than the reasonable cost of running it). The candidate can attempt optimization techniques offered by DBMS. Textbooks: • Fundamentals of Database Systems, Ramez Elmasri, Shamkant B. Navathe, 7th Edition, 2015, Pearson, ISBN-13: 978-0133970777 • Reference: Systems Manuals available on request. |
||||||||||||
| RULES/CONDITIONS | Before TAKING THIS UNIT YOU ARE ADVISED TO TAKE CIS1042 | ||||||||||||
| STUDY-UNIT TYPE | Lecture | ||||||||||||
| METHOD OF ASSESSMENT |
|
||||||||||||
| LECTURER/S | Joseph Vella |
||||||||||||
|
The University makes every effort to ensure that the published Courses Plans, Programmes of Study and Study-Unit information are complete and up-to-date at the time of publication. The University reserves the right to make changes in case errors are detected after publication.
The availability of optional units may be subject to timetabling constraints. Units not attracting a sufficient number of registrations may be withdrawn without notice. It should be noted that all the information in the description above applies to study-units available during the academic year 2025/6. It may be subject to change in subsequent years. |
|||||||||||||