Note: This unit is an archived version! See Overview tab for delivered versions.
INFO3504: Database Systems 2 (Adv) (2017 - Semester 2)
| Unit: | INFO3504: Database Systems 2 (Adv) (6 CP) |
| Mode: | Normal-Day |
| On Offer: | Yes |
| Level: | Senior |
| Faculty/School: | School of Computer Science |
| Unit Coordinator/s: |
A/Prof Roehm, Uwe
|
| Session options: | Semester 2 |
| Versions for this Unit: | |
| Site(s) for this Unit: |
| Campus: | Camperdown/Darlington |
| Pre-Requisites: | Distinction-level result in: (INFO2120 or INFO2820) or (COMP2007 or COMP2907) |
| Prohibitions: | INFO3404. |
| Brief Handbook Description: | This unit of study provides a comprehensive overview of the internal mechanisms and algorithms of Database Management Systems (DBMS) and other systems that manage large data collections. These skills are needed for successful performance tuning and to understand the scalability challenges faced by the information age. This unit builds upon the second- year INFO2820 – 'Database Systems 1 (Adv)' and correspondingly assumes a sound understanding of SQL, schema design and transactional programs. The first part of this subject focuses on mechanisms for large-scale data management. It provides a deep understanding of the internal components of a database engine. Topics include: physical data organization and disk-based index structures, query processing and optimisation, locking and logging, and database tuning. The second part focuses on the large-scale management of textual data such as by an information retrieval system or with web search engines. Topics include: distributed and replicated databases, information retrieval, document management, text index structures, and web-scale data management. This is an advanced alternative to INFO3404; it covers material at an advanced and challenging level. In particular, students in this advanced stream will study an actual DBMS implementation on the source code level, and also gain practical experience in extending the DBMS code base. |
| Assumed Knowledge: | This unit of study assumes that students have previous knowledge of database concepts including (1) ER modelling, (2) the relational data model and (3) SQL. The prerequisite material is covered in INFO 2120/2820. Sound experience with the C programming language and the Unix software development environment is also expected. |
| Lecturer/s: |
Dr Jeffries, Bryn
A/Prof Roehm, Uwe |
||||||||||||||||||||||||||||||
| Tutor/s: | cf. UoS eLearning site | ||||||||||||||||||||||||||||||
| Timetable: | INFO3504 Timetable | ||||||||||||||||||||||||||||||
| Time Commitment: |
|
||||||||||||||||||||||||||||||
| T&L Activities: |
|
Attributes listed here represent the key course goals (see Course Map tab) designated for this unit. The list below describes how these attributes are developed through practice in the unit. See Learning Outcomes and Assessment tabs for details of how these attributes are assessed.
| Attribute Development Method | Attribute Developed |
| In the practical assignment, students have to analyze and design an optimized physical database schema. Advanced students in addition have to design and implement an extension to an existing DBMS engine such as a join algorithm. | Design (Level 4) |
| Students will learn several core techniques and algorithms for disk-based indexing, concurrency control, replication, distributed data processing and information retrieval. | Engineering/IT Specialisation (Level 4) |
| Students will study the fundamental principles for efficient large-scale data management. Advanced students will also study production-level source code with the PostgreSQL open-source database system, as well as software engineering tools such as Doxygen and the Subversion source control system. | Maths/Science Methods and Tools (Level 4) |
| Students are self-responsible for studying the documentation of the software used in the labs, as well as collecting background information for the pre-scribed readings and online quiz questions. Advanced students will make themselves familiar with the source code of an open-source DBMS such as PostgreSQL. | Information Seeking (Level 4) |
| Advanced students will gain expertise in the programming environment and source code principles of a production-grade open-source database system. | Professional Conduct (Level 4) |
For explanation of attributes and levels see Engineering & IT Graduate Outcomes Table.
Learning outcomes are the key abilities and knowledge that will be assessed in this unit. They are listed according to the course goal supported by each. See Assessment Tab for details how each outcome is assessed.
Design (Level 4)
1. Ability to make effective physical databases design decisions.
2. Ability to identify a performance problem and be able to effectively tune the performance of a DBMS.
3. Experience with database tuning.
Engineering/IT Specialisation (Level 4)
4. Understanding of disk-based indexing structures such as B-Trees, extensible hashing and bitmap indexes.
5. Understanding of the principles of query optimization.
6. Understanding of concurrency control algorithms and the underlying logging and recovery mechanisms
Maths/Science Methods and Tools (Level 4)
7. Understanding of different physical data organisations including data partitioning and data replication
8. Understanding of the principles of information retrieval and result ranking.
9. Knowledge of open source programming techniques.
Professional Conduct (Level 4)
10. Experience with production-grade C source code and the corresponding programming environment.
| Assessment Methods: |
|
||||||||||||||||||||||||||||||||||||
| Assessment Description: |
Mid-Semester Quiz: online quiz to be solved in Wk9 by students and marked by tutors; includes electronic review questions on the concepts taught in this unit (Data Storage and Indexing, Query Processing and Optimization, Concurrency Control and Crash Recovery, Information Retrieval) DB Programming Exercises (PASTA): weekly short programming exercises to implement selected database algorithms and data structures, submitted and auto-tested via PASTA (practical) Assignment 1: PostgreSQL Extension implementation of a small extension to PostgreSQL (practical) Assignment 2: Database Performance Tuning and Programming Assignment (practical) Final Exam: Written examination reviewing all concepts covered by the lecture (two hours) |
||||||||||||||||||||||||||||||||||||
| Assessment Feedback: | Mid-Semester Feedback will be auto-graded and returned in subsequent weeks. Presentations will receive verbal or written feedback regarding correctness and peer review of instructiveness. Programming exercises will be assessed according to unit tests, with guidance on failing tests. Multiple submissions can be made. Assignments will receive written feedback according to marking rubric. |
||||||||||||||||||||||||||||||||||||
| Grading: |
|
||||||||||||||||||||||||||||||||||||
| Policies & Procedures: | IMPORTANT: School policy relating to Academic Dishonesty and Plagiarism. In assessing a piece of submitted work, the School of IT may reproduce it entirely, may provide a copy to another member of faculty, and/or to an external plagiarism checking service or in-house computer program and may also maintain a copy of the assignment for future checking purposes and/or allow an external service to do so. Other policies See the policies page of the faculty website at http://sydney.edu.au/engineering/student-policies/ for information regarding university policies and local provisions and procedures within the Faculty of Engineering and Information Technologies. |
| Recommended Reference/s: |
Note: References are provided for guidance purposes only. Students are advised to consult these books in the university library. Purchase is not required.
|
| Online Course Content: | USyd e-Learning (Blackboard) |
| Note on Resources: | Several prescribed readings and further support material will be specified through the unit of study's eLearning site. |
Note that the "Weeks" referred to in this Schedule are those of the official university semester calendar https://web.timetable.usyd.edu.au/calendar.jsp
| Week | Description |
| Week 1 | Architecture of Database Systems |
|
Organisation and Administrativa |
|
| Week 2 | Physical Data Organisation |
| Week 3 | Tree-based Index Structures |
| Week 4 | Hash and Bitmap Indexes |
| Week 5 | Text Indexing and Introduction to Information Retrieval |
| Week 6 | Introduction to Query Processing and External Sorting |
| Week 7 | Query Execution and Join Algorithms |
| Week 8 | Query Optimization |
| Week 9 | Crash Recovery |
| Assessment Due: Mid-Semester Quiz | |
| Week 10 | Transactions and Concurrency Control Schemes |
| Week 11 | Distributed Databases |
| Week 12 | Web Scale Data Management |
| Assessment Due: Assignment 2: DB Tuning | |
| Week 13 | UoS Review |
| Mid-Semester Break | Assessment Due: Assignment 1: PostgreSQL Extension |
| Exam Period | Assessment Due: Final Exam |
Course Relations
The following is a list of courses which have added this Unit to their structure.
Course Goals
This unit contributes to the achievement of the following course goals:
| Attribute | Practiced | Assessed |
| Design (Level 4) | Yes | 20% |
| Engineering/IT Specialisation (Level 4) | Yes | 46.01% |
| Maths/Science Methods and Tools (Level 4) | Yes | 29% |
| Information Seeking (Level 4) | Yes | 0% |
| Professional Conduct (Level 4) | Yes | 5% |
These goals are selected from Engineering & IT Graduate Outcomes Table which defines overall goals for courses where this unit is primarily offered. See Engineering & IT Graduate Outcomes Table for details of the attributes and levels to be developed in the course as a whole. Percentage figures alongside each course goal provide a rough indication of their relative weighting in assessment for this unit. Note that not all goals are necessarily part of assessment. Some may be more about practice activity. See Learning outcomes for details of what is assessed in relation to each goal and Assessment for details of how the outcome is assessed. See Attributes for details of practice provided for each goal.
