| CODE | CIS2091 | ||||||||||||
| TITLE | Practical Design and Implementation with DBMS | ||||||||||||
| UM LEVEL | 02 - Years 2, 3 in Modular Undergraduate Course | ||||||||||||
| MQF LEVEL | 5 | ||||||||||||
| ECTS CREDITS | 5 | ||||||||||||
| DEPARTMENT | Computer Information Systems | ||||||||||||
| DESCRIPTION | The objective of this study-unit is to allow students to demonstrate their acquired knowledge on logical design and database models to construct a sound physical implementation. The course material covers some important pragmatic aspects of DBMS. These aspects include data storage, disk access, file structures; file access, index files, database engines; and query processing and query optimisation. Other pragmatic issues, e.g. data security, are also covered. Functional dependencies and normalization techniques are studied with the aim of defining the relationships contained within the data and reducing duplication. Database assertions and triggers are re-introduced to maintain the data integrity and operational characteristics of an application. Advanced SQL query operations to handle more complex reporting requirements. Performance issues are discussed in terms of optimizing queries, database design and tuning. Although relational databases are the main example NoSQL example is also introduced. Issues dealing with system failure, problems arising from concurrent use – introducing transaction processing, concurrency control and recovery mechanisms. Data security and DBMS mechanisms to support it discussed at some length. The operational roles of a database administrator (for example data back-up, data replication decisions, database design reviews, monitoring of access and resources) are enumerated. Students are assigned a description of an application on which the practicals are done. Study-Unit Aims: The principle aim is for a candidate to appreciate that for a logical database design there exists many different physical implementations. The candidate must understand the basic matrix that describes satisfaction of physical requirements and how to tune physical design to meet these. A secondary aim is for a candidate to gain exposure to non-relational data models, e.g. NoSQL, and how these 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 component 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 to software development; • Understand what query optimisation is and how it’s undertaken; • Appreciate and understand the limits of query optimisations; • Knowledgeable on physical design best practice pragmatics. 2. Skills: By the end of the study-unit the student will be able to: • Techniques and skills a candidate is expected to uptake include; • Write SQL statements for more involved queries; • Write simple scripts and program for database administration; • Define different data structures, for data coming from a database, that are more appropriate for the function at hand; • Understand hardware set-up for reliability; e.g. deploy and use RAID unit set-ups; • Create indexes in various disguises including e.g. expression indexes, covering indexes; • Configure and tune B Tree and Extensible Hashing queries; • Build Bit-mapped indexes and to 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 an 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 what is a reasonable cost of running it). Candidate is able to attempt optimisation techniques offered by DBMS. Textbooks: • Fundamentals of Database Systems, Ramez Elmasri, Shamkant B. Navathe, 6th Edition, 2010, Addison Wesley, ISBN-13: 978-0136086208 • Reference: Systems Manuals available on request |
||||||||||||
| STUDY-UNIT TYPE | Lecture and Practical | ||||||||||||
| METHOD OF ASSESSMENT |
|
||||||||||||
| LECTURER/S | Joseph Vella (Co-ord.) |
||||||||||||
|
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 2023/4. It may be subject to change in subsequent years. |
|||||||||||||