Note: This unit is an archived version! See Overview tab for delivered versions.

INFO3504: Database Systems 2 (Adv) (2017 - Semester 2)

Download UoS Outline

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:
# Activity Name Hours per Week Sessions per Week Weeks per Semester
1 Lecture 2.00 1 13
2 Advanced Seminar 1.00 1 12
3 Tutorial/lab after advanced seminar 1.00 1 12
4 Independent Study 2.00 1 13
5 Project Work - own time 6.00 1 4
T&L Activities:
  • Students are expected to attend all scheduled lectures, and laboratory classes. You should expect to spend a minimum of twelve hours per week including scheduled lectures and laboratory times.
  • Students are expected to undertake any prescribed readings, to submit the weekly homework, to carry out exercises and laboratory tasks and to submit selected work for assessment as directed.
  • Students are expected to be able to work independently and to make effective use of a range of resources including the library, eLearning (former WebCT), the Internet and relevant on-line help facilities.
  • Advanced students are expected to do independent source code studies and to have previous knowledge in C programming.
  • Students are expected to check their progressive results regularly. Results will be published through WebCT. Any errors or omissions must be reported to the unit coordinator, with appropriate evidence, as soon as possible. Please note: Marks are considered to have been confirmed ten days after being published and will not subsequently be altered.

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:
# Name Group Weight Due Week Outcomes
1 Mid-Semester Quiz No 10.00 Week 9 4, 5, 6, 7, 8,
2 DB Programming Exercises No 10.00 Multiple Weeks 1, 4, 5,
3 Assignment 1: PostgreSQL Extension No 10.00 Mid-Semester Break 4, 9, 10,
4 Assignment 2: DB Tuning No 10.00 Week 12 1, 2, 3, 9, 10,
5 Final Exam No 60.00 Exam Period 2, 4, 5, 6, 7, 8,
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:
Grade Type Description
Standards Based Assessment Final grades in this unit are awarded at levels of HD for High Distinction, DI (previously D) for Distinction, CR for Credit, PS (previously P) for Pass and FA (previously F) for Fail as defined by University of Sydney Assessment Policy. Details of the Assessment Policy are available on the Policies website at http://sydney.edu.au/policies . Standards for grades in individual assessment tasks and the summative method for obtaining a final mark in the unit will be set out in a marking guide supplied by the unit coordinator.
Special Conditions to Pass UoS It is a policy of the School of Information Technologies that in order to pass this unit, a student must achieve at least 40% in the written examination, as well as at least 40% of the total of the progressive marks. A student must also achieve an overall final mark of 50 or more. Any student not meeting these requirements may be given a maximum final mark of no more than 45 regardless of their average.
Grading Schema A Student's final result cannot exceed the student's result in the final exam by more than 10 marks.
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 Year(s) Offered
Bachelor of Computer Science and Technology 2015, 2016
Bachelor of Computer Science and Technology (Advanced) 2015, 2016, 2017
Bachelor of Computer Science and Technology (Computer Science) 2014 and earlier 2009, 2010, 2011, 2012, 2013, 2014
Bachelor of Computer Science and Technology (Computer Science)(Advanced) 2014 and earlier 2013, 2014
Bachelor of Computer Science and Technology (Information Systems) 2014 and earlier 2010, 2011, 2012, 2013, 2014
Bachelor of Computer Science and Technology (Information Systems)(Advanced) 2014 and earlier 2013, 2014
Bachelor of Computer Science & Tech. Mid-Year 2016, 2017
Aeronautical Engineering / Science 2011, 2012, 2013, 2014
Aeronautical Engineering (Space) / Science 2011, 2012, 2013, 2014
Biomedical Engineering / Science 2013, 2014
Chemical & Biomolecular Engineering / Science 2011, 2012, 2013, 2014
Civil Engineering / Science 2011, 2012, 2013, 2014
Electrical Engineering (Bioelectronics) / Science 2011, 2012
Electrical Engineering / Science 2011, 2012, 2013, 2014
Electrical Engineering (Computer) / Science 2014
Electrical Engineering (Power) / Science 2011, 2012, 2013, 2014
Electrical Engineering (Telecommunications) / Science 2011, 2012, 2013, 2014
Aeronautical / Science 2015, 2016, 2017
Aeronautical (Space) / Science 2015
Biomedical Mid-Year 2016
Biomedical 2016
Biomedical /Science 2015, 2016, 2017
Chemical & Biomolecular / Science 2015
Civil / Science 2015
Electrical / Science 2015
Electrical (Computer) / Science 2015
Electrical (Power) / Science 2015
Electrical (Telecommunications) / Science 2015
Mechanical / Science 2015, 2016, 2017
Mechanical (Space) / Science 2015
Mechatronic / Science 2015, 2016, 2017
Mechatronic (Space) / Science 2015
Software Mid-Year 2016, 2017, 2018, 2019, 2020
Software/ Project Management 2019, 2020
Software 2015, 2016, 2017, 2018, 2019
Software / Arts 2016, 2017, 2018, 2019, 2020
Software / Commerce 2016, 2017, 2018, 2019, 2020
Software / Medical Science 2016, 2017
Software / Music Studies 2016, 2017
Software / Project Management 2016, 2017, 2018
Software / Science 2016, 2017, 2018, 2019, 2020
Software/Science (Health) 2018, 2019, 2020
Software / Law 2016, 2017, 2018, 2019, 2020
Mechanical Engineering (Biomedical) / Science 2011, 2012
Mechanical Engineering / Science 2011, 2012, 2013, 2014
Mechanical Engineering (Space) / Science 2011, 2012, 2013, 2014
Mechatronic Engineering / Science 2011, 2012, 2013, 2014
Mechatronic Engineering (Space) / Science 2011, 2012, 2013, 2014
Project Engineering and Management (Civil) / Science 2011
Software Engineering (till 2014) 2010, 2011, 2012, 2013, 2014
Software Engineering / Arts 2011, 2012, 2013, 2014
Software Engineering / Commerce 2010, 2011, 2012, 2013, 2014
Software Engineering / Medical Science 2011, 2012, 2013, 2014
Software Engineering / Project Management 2012, 2013, 2014
Software Engineering / Science 2011, 2012, 2013, 2014
Bachelor of Information Technology 2015, 2016
Bachelor of Information Technology/Bachelor of Arts 2015, 2016
Bachelor of Information Technology/Bachelor of Commerce 2015, 2016
Bachelor of Information Technology/Bachelor of Medical Science 2015, 2016
Bachelor of Information Technology/Bachelor of Science 2015, 2016
Bachelor of Information Technology (Computer Science) 2014 and earlier 2009, 2010, 2011, 2012, 2013, 2014
Information Technology (Computer Science)/Arts 2012
Information Technology (Computer Science) / Commerce 2012
Information Technology (Computer Science) / Medical Science 2012
Information Technology (Computer Science) / Science 2012
Information Technology (Computer Science) / Law 2012
Bachelor of Information Technology (Information Systems) 2014 and earlier 2010, 2011, 2012, 2013, 2014
Information Technology (Information Systems)/Arts 2012, 2013, 2014
Information Technology (Information Systems) / Commerce 2012, 2013, 2014
Information Technology (Information Systems) / Medical Science 2012, 2013, 2014
Information Technology (Information Systems) / Science 2012, 2013, 2014
Information Technology (Information Systems) / Law 2012, 2013, 2014
Bachelor of Information Technology/Bachelor of Laws 2015, 2016
Software/Science (Medical Science Stream) 2018, 2019, 2020
Flexible First Year (Stream A) / Science 2012

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.