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

INFO3404: Database Systems 2 (2017 - Semester 2)

Download UoS Outline

Unit: INFO3404: Database Systems 2 (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: None.
Prohibitions: INFO3504.
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 INFO2120 – 'Database Systems 1' 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 processing.

The unit will be of interest to students seeking an introduction to database tuning, disk-based data structures and algorithms, and information retrieval. It will be valuable to those pursuing such careers as Software Engineers, Database Experts, Database Administrators, and e-Business Consultants.
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. Familiarity with a programming language (e.g. Java or C) is also expected.
Lecturer/s: Dr Jeffries, Bryn
A/Prof Roehm, Uwe
Tutor/s: cf. the unit's eLearning site
Timetable: INFO3404 Timetable
Time Commitment:
# Activity Name Hours per Week Sessions per Week Weeks per Semester
1 Lecture 2.00 1 13
2 Tutorial 1.00 1 12
3 Independent Study 3.00 1 13
4 Practical assignment on database tuning. 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 reading, to submit the weekly homework, to carry out exercises and laboratory tasks and to submit selected work for assessment as directed. It should be realised that some laboratory exercises can take longer than just the time scheduled for classes.
  • Students are expected to be able to work independently and to make effective use of a range of resources including the library, eLearning, the Internet and relevant on-line help facilities.
  • Students are expected to check their progressive results regularly. Results will be published through eLearning. 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. Design (Level 3)
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 3)
Students will study the fundamental principles for efficient large-scale data management. Maths/Science Methods and Tools (Level 3)
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. Information Seeking (Level 3)

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 3)
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 3)
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 3)
7. Understanding of different physical data organisations including data partitioning and data replication
8. Understanding of the principles of information retrieval and result ranking.
Assessment Methods:
# Name Group Weight Due Week Outcomes
1 Mid-Semester Quiz No 10.00 Week 9 4, 5, 6, 7, 8,
2 Presentation of DB Concepts Yes 5.00 Multiple Weeks 1, 2, 3, 4, 5, 6, 7, 8,
3 DB Programming (PASTA) No 10.00 Multiple Weeks 1, 4, 5,
4 Assignment Yes 15.00 Week 12 1, 2, 3,
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)

Presentation of DB Concepts: create introductory video on a database concept that is shared with the tutorial class, to be done by pairs of students

DB Programming Exercises (PASTA): weekly short programming exercises to implement selected database algorithms and data structures, submitted and auto-tested via PASTA (practical)

Assignment: Database Performance Tuning and Programming Assignment (practical)

Final Exam: Written Written examination reviewing the unit`s material (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.
Assignment 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
Note on Resources: Other material and prescribed readings may be specified through the unit of study web page in eLearning.

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 Retrieval and Web Scale Data Management
Assessment Due: Assignment
Week 13 UoS Review
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 (Information Systems) 2014 and earlier 2010, 2011, 2012, 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 Engineering 2016
Biomedical /Science 2015, 2016, 2017
Biomedical - Information Technology Major 2015
Chemical & Biomolecular / Science 2015
Civil / Science 2015
Electrical / 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
Software/ Project Management 2019
Software 2015, 2016, 2017, 2018, 2019
Software / Arts 2016, 2017, 2018, 2019
Software / Commerce 2016, 2017, 2018, 2019
Software / Music Studies 2016, 2017
Software / Project Management 2016, 2017, 2018
Software / Science 2016, 2017, 2018, 2019
Software / Science (Health) 2018, 2019
Software / Law 2016, 2017, 2018, 2019
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 / 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/Bachelor of Laws 2015, 2016
Software / Science (Medical Science Stream) 2018, 2019
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 3) Yes 28.25%
Engineering/IT Specialisation (Level 3) Yes 46.26%
Maths/Science Methods and Tools (Level 3) Yes 25.5%
Information Seeking (Level 3) Yes 0%

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.