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

INFO2820: Database Systems 1 (Advanced) (2017 - Semester 1)

Download UoS Outline

Unit: INFO2820: Database Systems 1 (Advanced) (6 CP)
Mode: Normal-Day
On Offer: Yes
Level: Intermediate
Faculty/School: School of Computer Science
Unit Coordinator/s: Dr Jeffries, Bryn
Session options: Semester 1
Versions for this Unit:
Site(s) for this Unit: http://elearning.sydney.edu.au/)
Campus: Camperdown/Darlington
Pre-Requisites: [Distinction-level result in (INFO1003 or INFO1103 or INFO1903 or INFO1105 or INFO1905 or DECO1012) or equivalent].
Prohibitions: INFO2120 OR COMP5138 OR INFO2905.
Brief Handbook Description: The ubiquitous use of information technology comes with immense amounts of data produced by users, IT systems and mobile devices. The proper management of data is essential for all applications, especially new ones that want to make intelligent use of the data, and for effective decision making within organisations.

This unit of study is an advanced alternative to INFO2120 that will introduce the basic concepts of database designs at the conceptual, logical and physical levels. Particular emphasis will be placed on introducing integrity constraints and the concept of data normalization which prevents data from being corrupted or duplicated in different parts of the database. This in turn helps in the data remaining consistent during its lifetime. Once a database design is in place, the emphasis shifts towards querying the data in order to extract useful information. The unit will introduce different query languages with a particular emphasis on SQL and, in INFO2820, querying graph and hierarchical data. Other topics covered will include recursive SQL, graphs in databases, NoSQL databases, transaction management, application development with a backend database, an overview of data warehousing and OLAP.
Assumed Knowledge: None.
Lecturer/s: Dr Jeffries, Bryn
Timetable: INFO2820 Timetable
Time Commitment:
# Activity Name Hours per Week Sessions per Week Weeks per Semester
1 Lecture 1.00 1 13
2 Laboratory 2.00 1 12
3 Independent Study 4.00 1 13
4 Project Work - own time 3.00 1 3
5 Pre-Lecture Preparation 1.00 1 13
6 Advanced Seminar 1.00 1 13
T&L Activities: A variety of learning situations will be employed during the unit of study, including lectures, on-line demos, tutorials, directed computer laboratory exercises, self-learning SQL exercises, assessed assignments and a small practical database project. To benefit fully from this unit it is necessary to participate fully in all aspects of the unit of study.

Pre-Lecture Preparation: Introductory content will be released each week in advance of the main lecture. All students are expected to review this content and answer review questions prior to the main lecture.

Independent Study: Preparatory study including introductory videos, reading reference material, attempting online homework questions and SQL tutorial questions.

Project work: Group activities working on assignment projects, mostly performed outside of classes.

Lectures: Attendance is expected, and students are required to take part in worksheet-based activities and in-class online questionnaires.

Laboratory: Laboratory are facilitated by tutors, and expose students to hands-on use of a DBMS and other practical activities relating to assignment projects.

As compared to INFO2120, there is an additional advanced seminar that covers more advanced aspects of databases each week.

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
Schema design including integrity constraints that capture business rules as part of tutorial work and online quizzes/exam. Design of database-backed application to meet user needs, in the practical assignments. Design of hierarchical / graph data as part of tutorial work. Design (Level 2)
Theory and Practice of Relational and Deductive Database Management Systems, throughout the unit; discussion of current trends in database system technology. Engineering/IT Specialisation (Level 3)
Learning of database systems, SQL editors and advanced data models through online documentation for the lab tasks; topics on information security and integrity as part of the lecture. Information Seeking (Level 2)
The subjects covers basics of best-practices for SQL coding, schema naming conventions, database security, authorization mechanisms, and protection against SQL injection attacks. Professional Conduct (Level 1)
Team assignment (with disparate members), in the practical database project assignments. Project and Team Skills (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 2)
1. Ability to design a schema which says how information about a particular domain will be stored in a relational DBMS (given a conceptual data model); also ability to apply normalization theory to evaluate or improve a relational schema. Ability to capture business rules as integrity constraints in a database schema.
Engineering/IT Specialisation (Level 3)
2. Understanding of the concept of a DBMS: differences from other ways to store and share data; DBMS role in organizations; the types of work done with a DBMS.
3. Understanding of the relational data model
4. Ability to work with data stored in a relational database management system (understand table definitions including integrity constraints, extract information through SQL queries, modify information through SQL queries, use views and permissions for security)
5. Ability to connect general database concepts to both theoretical abstract formulations, and details of specific software platforms.
6. Experience of how application software can use data stored in a DBMS (eg a dynamic content web site) and understand the basic architectural alternatives for data management applications.
7. Understanding of the basic concepts of transaction management.
8. Knowledge of some sophisticated technical content related to relational DBMS, such as storage of tree- and graph-based data, and the use of first-order logic as a data model and as a query language.
Professional Conduct (Level 1)
9. Understand the SQL mechanisms for basic concepts of data security and privacy.
Project and Team Skills (Level 3)
10. Work effectively in a team with members whose skills and interests differ
Assessment Methods:
# Name Group Weight Due Week Outcomes
1 Weekly Homework No 10.00 Multiple Weeks 1, 2, 3, 5, 7, 8,
2 SQL Tutorial No 0.00 Multiple Weeks 3, 4,
3 SQL Quiz No 10.00 Week 9 3, 6,
4 DB Design Yes 10.00 Mid-Semester Break 1, 3, 5, 10,
5 DB Schema Yes 10.00 Week 7 1, 3, 8, 9, 10,
6 DB Programming Yes 10.00 Week 12 5, 6, 7, 8, 9, 10,
7 Final Exam No 50.00 Exam Period 1, 2, 3, 4, 5, 7, 8, 9,
Assessment Description: * indicates an assessment task which must be repeated if a student misses it due to special consideration

NOTE: All text-based written assignments make use of text-based similarity detecting software (Turnitin)

DB concept exercises: Each week a set of questions will be posed on that week`s topic, based on the recommended reading and supplied resources. Answers are submitted online and are mostly limited to simple formats such as multiple choice. Students can review their answers and will receive an overall score based upon their best 8 out of 10 weeks` submissions.

DB design assignment: Students work together in small groups to model a database based upon a brief scenario description, generating an conceptual Entity-Relationship diagram and then mapping this to a relational model. Students then implement this database in PostgreSQL.

DB application development assignment: The theme of the previous assignment is extended with the development of a client interface for the database, and the implementation of more advanced back-end features such as stored procedures and indexes.

SQL: Students work through weekly online tutorials introducing increasingly sophisticated usage of SQL. Solutions are provided for each week, and the topics are assessed in an SQL quiz.

Final Exam: Understanding of all of this unit`s material is reviewed in a written examination.
Assessment Feedback: SQL tutorials provide simple feedback and allow multiple attempts, and example solutions are available after the submission deadline has passed.

Homework exercises include solutions after the submission deadline.

Design and Application assignments are worked on progressively and draft submissions can receive formative feedback that can be used to make an improved final submission.
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.
Minimum Pass Requirement It is a policy of the School of Computer Science that in order to pass this unit, a student must achieve at least 40% in the written examination. For subjects without a final exam, the 40% minimum requirement applies to the corresponding major assessment component specified by the lecturer. 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.
Special Conditions to Pass UoS Students are required to pass the SQL Challenge milestone activities between Week 4 and Week 10 in order to pass this subject. Students with less than 40% in SQL Challenge will be assessed individually on their SQL skills by the lecturer whether they pass this milestone.
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.
  • 1. Database Systems: An Application-Oriented Approach (Complete Version)
  • 2. Database Management Systems
  • 3. First Course in Database Systems
  • 4. Database Systems Concepts
Online Course Content: University of Sydney LMS ( http://elearning.sydney.edu.au/) will be used as the main gateway to all resources, including:

- Lecture slides

- Lecture recordings

- Introductory videos

- Activity Worksheets and solutions

Discussion forums and SQL tutorials are run through separate systems, but linked from the eLearning site.
Note on Resources: Some in-class activities may make use of students' own devices (smart-phones, tablets or laptops). It is not required to have a device, but students are encouraged to bring one to classes if they have one.

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 Introduction and Administrative
Week 2 Conceptual Data Modeling
Adv: Introduction to Datalog
Week 3 The Relational Data Model
Adv: Datalog and Recursion
Week 4 Relational Algebra and SQL
Adv: Trees and Hierarchical Data in Relational Databases
Week 5 Complex SQL: Nested Queries and Grouping
Adv: Recursive SQL
Week 6 Schema Refinement and Data Normalization
Adv: Multi-Valued Dependencies and 4NF
Week 7 Database Security, Data Integrity and Triggers
Adv: Trigger Execution Details
Assessment Due: DB Schema
Week 8 DB Application Development
Adv: Embedded SQL ; Cursor in Depth ; LINQ
Week 9 Transaction Management
Adv: Serializability and Snapshot Isolation
Assessment Due: SQL Quiz
Week 10 Indexing and Tuning
Adv: Main Memory DBs and Materialization
Week 11 Data Warehousing and OLAP
Adv: Distributed Databases and 2-Phase-Commit
Week 12 Introduction to XML
Adv: NoSQL Databases
Assessment Due: DB Programming
Week 13 UoS Review
Mid-Semester Break Assessment Due: DB Design
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 (Advanced) 2015, 2016, 2017
Bachelor of Computer Science and Technology (Computer Science)(Advanced) 2014 and earlier 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
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 / Science 2011, 2012, 2013
Bachelor of Computer Science and Technology 2015, 2016
Bachelor of Computer Science and Technology (Computer Science) 2014 and earlier 2013, 2014
Bachelor of Computer Science and Technology (Information Systems) 2014 and earlier 2013, 2014
Bachelor of Information Technology/Bachelor of Medical Science 2015
Bachelor of Information Technology (Computer Science) 2014 and earlier 2010, 2011, 2012, 2013, 2014
Information Technology (Computer Science)/Arts 2012, 2013, 2014
Information Technology (Computer Science) / Commerce 2012, 2013, 2014
Information Technology (Computer Science) / Medical Science 2012, 2013, 2014
Information Technology (Computer Science) / Science 2012, 2013, 2014
Information Technology (Computer Science) / Law 2012, 2013, 2014
Bachelor of Information Technology (Information Systems) 2014 and earlier 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
Flexible First Year (Stream A) / Science 2012

Course Goals

This unit contributes to the achievement of the following course goals:

Attribute Practiced Assessed
Maths/Science Methods and Tools (Level 3) No 0%
Design (Level 2) Yes 17.85%
Engineering/IT Specialisation (Level 3) Yes 67.9%
Information Seeking (Level 2) Yes 0%
Professional Conduct (Level 1) Yes 10.25%
Project and Team Skills (Level 3) Yes 4%

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.