CSE3DMS: Database Management Systems

Group assignment - database query performance analysis - 30%

This group assignment (groups of 2 or you can do it alone) is all about query optimisation. Given a database schema and business questions (e.g. some of the queries that the system will be expected to answer), you need to design indexes (primary index, secondary index, bitmap, clustering, hash index, etc.) in order to improve query performance. First, you must populate the tables using the ORACLE tool SQL*LOADER. Then, you will write a bunch of SQL statements to answer the business questions. For each business question you have to write the query in different ways.

e.g. In one query, use subqueries and then rewrite the same query but this time don't use subqueries. So you perform lots of experiments on the same business questions.

You need to compare which queries run faster (using EXPLAIN PLAN) and why. This a great assignment because analysing execution plans is fun for database nerds. It's really interesting to use Oracle Hints to try and manipulate the query (e.g. change the join algorithm, join order, etc.).

Note: Oracle 11G is used in the subject

One 3-hour examination - 70%

The three-hour examination requires you to provide written answers to questions designed to assess your understanding of the concepts covered. These questions cover all of the learning outcomes with the exception of Oracle programming which is not assessed on the examination.

Very similar to the past exams. Refer to past exams early and often.

CSE3DMS is a subject you either love or hate. Put simply, only take this class if you have a passion for databases. If you have taken the core subject CSE2DBF (Database Fundamentals), do not expect DMS to be similar. DBF is all about logical design with absolutely no emphasis on performance and optimisation. DMS is concerned about database performance. The nuts and bolts of database management systems.

The subject is broken into 4 major topics:

1. Indexing: You will learn all about various database indexes including B+Trees, Bitmap, static and dynamic hashing indexes.

2. Query optimisation: Selection algorithms, join algorithms (nested-loop join, single-loop join, sort and merge join, hash join), query trees, denormalisation, etc.

3. Transaction management: Serializability, recoverability, etc.

4. Concurrency control: Two-phase locking, timestamp protocols, deadlocks, etc.

If you don't know SQL then do yourself a favour and don't do this subject. However, if you're a fast learner you can quickly pick the language up. PL-SQL is not covered since the majority of the content is theoretical rather than practical.

The labs are really tough but I recommend trying to complete them. Solutions are posted weekly on LMS. Jinli is a fantastic lecturer who is always willing to answer your questions.

If you want to work with databases in the future, this subject is a must.

Topics:

* Introduction to database management systems

* B+ Tree Index, Bitmap, clustering, primary/secondary indexes, hash index

* Hashing techniques including static/extendible hashing

* Algorithms for query processing and optimisation - Selection algorithms and Join algorithms

* Translating SQL to relational algebra

* Query Trees

* Database tuning (i.e. denormalisation)

* ACID transactions

* Concurrency control techniques - 2PL and timestamp protocols

* Oracle Cost Based Optimizer (CBO)

* Database Recovery Techniques

Yes, with screen capture.

Dr Jinli Cao

Yes, many are available in the La Trobe library. You will receive solutions for the previous year.

5/5

CSE2DBF (Database Fundamentals)
CSE1IS (Information Systems)
CSE2DES (System Design Engineering)

Not essential, but the recommended text is listed below:

Fundamentals of Database Systems, Elmasri, 6th Edition.

1 X 2hr lecture per week
1 X 2hr lab per week

Semester 1, 2014

98 (A)