Please refer to the Course Assessment Amendments due to COVID-19.
| Course Code | COMP3311 | ||||||||
| Course Title | Database Systems | ||||||||
| Convenor | Raymond Wong | ||||||||
| Admin | - | ||||||||
| Classes |
Lectures
:
Timetable for all classes |
||||||||
| Consultations |
Consultation time
|
||||||||
| Units of Credit | 6 | ||||||||
| Course Website | http://cse.unsw.edu.au/~cs3311/ | ||||||||
| Handbook Entry | http://www.handbook.unsw.edu.au/undergraduate/courses/current/COMP3311.html |
This course aims to explore in depth the practice of developing database applications and the theory behind relational database management systems (RDBMSs). It will also give an overview of the technologies used in implementing database management systems and the past, present and future of database systems and database research.
Large data resources are critical to the functioning of just about every significant modern computer application. Hence, knowledge of how to manage them is clearly important to the IT industry. In the context of further study, understanding how to use databases effectively is essential for courses such as COMP9321 Data Services Engineering and COMP9322 Software Service Design and Engineering. COMP3311 also provides a foundation for further study in advanced database topics, such as COMP9315 Database Systems Implementation and COMP9318 Data Warehousing and Data Mining. Database concepts are also relevant in courses such as COMP9319 Web Data Compression and Search and COMP6714 Information Retrieval and Web Search.
By the end of this course, we want you to be capable of building high-quality (correct, efficient, and usable) applications based on relational databases, to have a sound understanding of issues in managing relational database management systems, and an overview of how they work internally.
On successfully completing this course, students should be able to:
Glossary:
This course contributes to the development of the following graduate attributes:
| Graduate Capability | Acquired in |
| Scholars capable of independent and collaborative enquiry, rigorous in their analysis, critique and reflection, and able to innovate by applying their knowledge and skills to the solution of novel as well as routine problems |
Exercises,
Prac work, Assignments |
| Entrepreneurial leaders capable of initiating and embracing innovation and change, as well as engaging and enabling others to contribute to change |
Exercises,
Prac work, Assignments |
| Professionals capable of ethical, self- directed practice and independent lifelong learning |
Exercises,
Prac work, Assignments |
| Global citizens who are culturally adept and capable of respecting diversity and acting in a socially just and responsible way |
Exercises,
Prac work, Assignments |
The official pre-requisite for this course is that students must have taken either COMP2521 or COMP1927 .
Whatever the formal pre-reqs, we assume primarily that students have some experience with procedural programming and some knowledge of elementary data structures.
A perpetual problem for COMP3311 is that around half of the class has already covered basic data modelling techniques (specifically ER diagrams) in courses such as INFS1603 Introduction to Business Databases or COMP1531. On the other hand, half of the class hasn't seen this material before, so we need to cover it. Those who have seen it before should treat this as revision. Some of the other material in the course (e.g., basic SQL) may also be familiar to students who have taken the above courses, but don't make the mistake of thinking "I know all this stuff"; we will definitely cover these areas in more depth than you have seen them previously.
There are four primary modes of learning for this course: lectures, labs / tutorials, exercises, assignments.
Each week (except Week 6) there will be four hours of lectures during which theory, practical demonstrations and case-studies will be presented. You will get maximum benefits from lectures if you read the relevant textbook chapters before attending each lecture.
There will be weekly labs or tutorials in this course. Labs run from Week 2 to Week 5, while tuts run from Week 8 to Week 10. Tutorial-type questions and practical (lab-type) exercises will be made available. You are expected to work through these yourself. Note: none of these are assessable, but we are assuming that you will be interested enough in the topics to actually do them without the need for assessment-based incentives. It is definitely in your own best interest to keep up-to-date with the theory/prac exercises. Selected questions/exercises will be demonstrated/discussed in the weekly labs/tutorials.
Tutorials or labs are the preferred channel to get detailed answers to questions related to the exercises (or any topics covered by the lectures). In addition, for short questions, you may make use of (a) the WebCMS Forums, (b) consultations, (c) email direct to the Lecturer in Charge or Course Admin, in order to get your questions answered.
In the assignments, you will consider the major stages of the database application development process, conceptual design, implementation as a PostgreSQL application, and building a database application using Python. The assignments contribute 40% of the overall mark for this course.
| # | Description | Due (tentative) | Marks |
| 1 | SQL and PLpgSQL | Friday 20 March (end week 5) | 20% |
| 2 | Python and SQL | Friday 17 April (end week 9) | 20% |
An important outcome of doing the assignment work is that you will advance your knowledge and skills in key course objectives: query formulation, use of stored procedures, and interaction of programming languages and DBMSs. Completion of the assignments will also aid in your understanding of database performance issues and interface construction. The assignments also lay the foundation for successful performance in the final exam.
Assignments will be completed individually ; this means that you should do them yourself without assistance from others, except for asking advice from the Lecturer or Course Admin. As noted above, assignments are the primary vehicle for learning the material in this course. If you don't do them, or simply copy and submit someone else's work, you have wasted a valuable learning opportunity.
Assignments are to be submitted on-line (via WebCMS) . Assessment of assignments will be primarily based on how accurately they satisfy the requirements; this means that mostly mark will be based on automatic marking. However, we may also manually examine submitted assignments to determine (a) whether they are written with good style, (b) how closely they satisfied the requirements, if they fail the auto-marking. Late submissions will have marks deducted from the maximum achievable mark at the rate of 0.5% of the total mark per hour that they are late (i.e., 12% per day).
Since this course is about the understanding and effective use of a specific technology (relational database management systems), practical use of the technology is critical to the learning outcomes of the course. Thus, a lot of time will be spent during lectures and labs/tutorials to work through exercises, to illustrate the practice of using various database techniques and technologies. However, the primary learning focus in this course is assignment work, which has been designed to be challenging and relevant (i.e., dealing with real problems and using realistic database schemas).
We will be providing a host of exercises and prac work that we expect you will do in your own time. Selected exercises or the issues that you encountered (from the exercises, from the lecture materials, or from the assignments) will be discussed in the weekly labs/tutorials.
Naturally, sometimes people will "get stuck" on understanding various aspects of the material, so we have Labs/Tutorials and Forums where you can ask questions, and where we will respond relatively promptly. We also run face-to-face consultations each week where you can come if you have a individual or specific problem that can't be handled appropriately in the Labs/Tutorials or using the Forums.
The Student Code of Conduct ( Information , Policy ) sets out what the University expects from students as members of the UNSW community. As well as the learning, teaching and research environment, the University aims to provide an environment that enables students to achieve their full potential and to provide an experience consistent with the University’s values and guiding principles. A condition of enrolment is that students inform themselves of the University’s rules and policies affecting them, and conduct themselves accordingly.
In particular, students have the responsibility to observe standards of equity and respect in dealing with every member of the University community. This applies to all activities on UNSW premises and all external activities related to study and research. This includes behaviour in person as well as behaviour on social media, for example Facebook groups set up for the purpose of discussing UNSW courses or course work. Behaviour that is considered in breach of the Student Code Policy as discriminatory, sexually inappropriate, bullying, harassing, invading another’s privacy or causing any person to fear for their personal safety is serious misconduct and can lead to severe penalties, including suspension or exclusion from UNSW.
If you have any concerns, you may raise them with your Lecturer, or approach the School Ethics Officer , Grievance Officer , or one of the student representatives .
Plagiarism is defined as using the words or ideas of others and presenting them as your own. UNSW and CSE treat plagiarism as academic misconduct, which means that it carries penalties as severe as being excluded from further study at UNSW. There are several on-line sources to help you understand what plagiarism is and how it is dealt with at UNSW:
Make sure that you read and understand these. Ignorance is not accepted as an excuse for plagiarism. In particular, you are also responsible that your assignment files are not accessible by anyone but you by setting the correct permissions in your CSE directory and code repository, if using. Note also that plagiarism includes paying or asking another person to do a piece of work for you and then submitting it as your own work.
UNSW has an ongoing commitment to fostering a culture of learning informed by academic integrity. All UNSW staff and students have a responsibility to adhere to this principle of academic integrity. Plagiarism undermines academic integrity and is not tolerated at UNSW. Plagiarism at UNSW is defined as using the words or ideas of others and passing them off as your own.
If you haven't done so yet, please take the time to read the full text of
The pages below describe the policies and procedures in more detail:
You should also read the following page which describes your rights and responsibilities in the CSE context:
There will be two places where your learning in this course will be assessed: assignments and the final exam.
Much as we dislike conflating the learning aspect of assignments with their assessment aspect, there will be marks for the assignment work. We would rather use the assignments entirely as learning vehicles and have no assessment associated with them, but I suspect that wouldn't result in a satisfactory outcome (i.e., nobody would do them).
The final exam will form the major assessment in this course and aims to test what you learned about databases during the course of the semester. The Final Exam will be conducted in the CSE laboratories, and will involve both a practical component (writing SQL queries on a supplied database) and a theory component (e.g., data modelling, schema normalization, etc.). In order to meet the exam hurdle requirement, you must score better than 40% on both the prac and the theory component. Note that the hurdle will be enforced after any required scaling.
Your final mark in this course will be based on the marks from the above assessment components. Note that the exam is a hurdle, so that if you fail the exam badly enough, you cannot pass the course. The following formula describes precisely how the final mark will be computed and how the hurdle will be enforced:
ass1 = mark for assignment 1 (out of 20)
ass2 = mark for assignment 2 (out of 20)
examP = mark for exam (practical) (out of 30)
examW = mark for exam (written) (out of 30)
exam = examP + examW (out of 60)
okExam = examP > 12 && examW > 12 (after scaling)
mark = ass1 + ass2 + exam
grade = HD|DN|CR|PS if mark >= 50 && okExam
= FL if mark < 50 && okExam
= UF if !okExam
|
The order that topics are covered in this course is probably not the most "natural order" for presenting them. The material is presented in lectures in an order that ensures that you are best prepared for the assignments.
The following is an approximate guide to the sequence of topics in this course. It is subject to change as the semester progresses.
| Week | Lectures | Prac Work | Assignments | Labs/Tuts |
| 1 | Introduction, Data Modelling, ER Model | SQLite: a simple RDBMS | ||
| 2 | Relational Model, ER-Relational Mapping, SQL Schemas | Set up PostgreSQL server | Lab1 | |
| 3 | Data Modification, Simple SQL Queries | Schema definition, data constraints | Assignment 1 Released | Lab2 |
| 4 | Complex SQL Queries, Stored Procedures |
SQL queries, views
PLpgSQL functions |
Lab3 | |
| 5 | PLpgSQL, Aggregates, Triggers | User-defined aggregates, updates & triggers | Assignment 1 Due | Lab4 |
| 6 | No lecture |
|
|
|
| 7 | Programming with Databases, Python |
Python Database programming
|
Assignment 2
Released |
Tut1 |
| 8 | Relational Design Theory, Normalisation |
|
|
Tut2 |
| 9 | Relational Algebra, Query Processing |
|
Assignment 2
Due |
Tut3 |
| 10 | Transaction Processing, Concurrency Control, Database Trends & Course Review |
|
|
Tut4 |
Any of the following books is a suitable textbook for this course:
Choose the one that best suits your learning style and preferences. Also, if you have access to an earlier edition of any of these books (one or two editions less than the one given above), it will be fine for this course.
The textbooks give the greatest detail on the topics covered in the course, but cover many other topics. The Course Notes have less detail, but cover exctly the course syllabus. The Lecture Slides have even less detail, but include the examples discussed in lectures.
The software systems to be used in this course are PostgreSQL, SQLite, Python and psycopg2. The documentation and manuals provided with PostgreSQL and Python are actually very good, and the SQLite and psycopg2 documentation is reasonable, so you don't need to buy textbooks for these. However, if you feel more comfortable with a book, there are references to a range of books on the web sites for PostgreSQL , SQLite , and Python .
A general problem with technology textbooks is that they go out-of-date very quickly. Another problem is that many of them provide a brief introduction with some examples, and then give a summary of the manual. In general, we have found O'Reilly books tend to be better than most.
PostgreSQL will be used in this course because it is a typical example of a full-featured client-server DBMS, and has the added bonuses that (a) it has a simple extensibility model and (b) has the source code available if you want to learn more about how DBMSs work. Commercial alternatives could have been Oracle, DB2, SQL Server, although none of these are available in source code form. (MS Access is not a full-featured relational database.) The only plausible open-source alternative is MySQL, but it was not a full-featured DBMS until version 5, and has a source code base that is largely cobbled together from a number of existing open-source systems. The PostgreSQL code base, on the other hand, is the result of coherent development by a relatively small team (although the developer base has expanded in recent years).
SQLite is a very widely-used example of a serverless RDBMS and will be used for contrast with PostgreSQL. It has a similar (90% compatible) dialect of SQL to PostgreSQL, and, like PostgreSQL, supports the SQL92 standard. As an exercise in portability, we will be expecting whatever SQL statements you write for this course to work on both PostgreSQL and SQLite (although this may not always be possible).
This course is evaluated each session using the myExperience system at the end of the semester.
The evaluation from the previous offering of the course showed that having occasional labs and tutorials, where problems with the prac and written exercises can be discussed, would be useful for the course. To address this, this is the first time that we are running labs in the first half of the course, and tutorials in the second half. For this new arrangement, your feedback is particularly important this semester and will be considered to improve future offerings of this course.
Students are also encouraged to provide informal feedback during the semester, and let the lecturer in charge or the course admin know of any problems, as soon as they arise. Suggestions will be listened to very openly, positively, constructively and thankfully, and every reasonable effort will be made to address them as soon as possible.
Resource created Thursday 16 January 2020, 11:26:12 AM, last modified Friday 01 May 2020, 04:28:18 PM.