1. Resources
  2. Course Outline

Course Outline


Contents

Course Details

Course Code COMP3311
Course Title Database Systems Implementation
Convenor John Shepherd
Admin Dylan Brotherston
Classes There are no on-campus classes in COMP3311 in 20T3.
The lectures will run as online problem-solving sessions.
All tutorials will be held online via Blackboard Collaborate.
Timetable for all classes
Consultations TBA
Units of Credit 6
Course Website https://webcms3.cse.unsw.edu.au/COMP3311/20T3/ or http://cgi.cse.unsw.edu.au/~cs3311/20T3/
Handbook Entry http://www.handbook.unsw.edu.au/undergraduate/courses/current/COMP3311.html
Issues email cs3311@cse.unsw.edu.au or stureps@cse.unsw.edu.au

If you encounter any problems with the course or the way it's being run, contact the class account and we will try to fix the problem. If you'd rather not contact us directly, then contact the Student Reps.

Course Summary

This course aims to explore in depth the practice of developing database applications and the theory behind relational database systems. It will also give a very brief overview of the technologies used in implementing database management systems and the past, present and future of database systems.

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 and efficient) 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.

Assumed Knowledge

The official pre-requisite for this course is that students 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 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 Software Engineering Fundamentals. 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.

Student Learning Outcomes

On successfully completing this course, students should be able to:

  1. develop accurate, non-redundant data models;
  2. realise data models as relational database schemas;
  3. formulate queries via the full range of SQL constructs;
  4. use stored procedures and triggers to extend DBMS capabilities;
  5. write applications in Python that interact effectively with databases;
  6. analyze performance issues in relational database applications;
  7. understand the overall architecture of relational DBMSs;
  8. understand the concepts behind transactions and concurrency control;

Glossary:

  • DBMS : DataBase Management System ... software system to support database manipulation.
  • RDBMS : Relational DBMS ... the most popular style of DBMS (refers to underlying data model) .
  • SQL : Structured Query Language ... the ANSI standard language for manipulating RDBMSs.

This course contributes to the development of the following graduate capabilities:

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 assignments, prac work
Entrepreneurial leaders capable of initiating and embracing innovation and change, as well as engaging and enabling others to contribute to change -
Professionals capable of ethical, self- directed practice and independent lifelong learning assignments, prac work
Global citizens who are culturally adept and capable of respecting diversity and acting in a socially just and responsible way -

Teaching/Assessment Strategies

Summary of teaching strategies in COMP3311 20T3. Everything in online.

  • Videos + Slides ... introduce concepts
  • Online problem-solving sessions ... work through examples
  • Tutorials ... reinforce concepts and provide additional examples
  • Prac Work ... introduce technology required for the assignments
  • Assignments .. allow students to solve significant problems
  • Quizzes ... review concepts from previous week
  • Forums ... ask questions, get answers
  • Consultations ... ask questions (live), get answers

Videos

Most course content will be made available via pre-recorded videos and accompanying slides. Each video will be 20-40 minutes in duration and will cover a single topic. Videos will be available at the start of each week. Also at the start of each week, there will be a Weekly Preview video outlining what topics will be covered during that week, and what you should be doing during the week.

Online Problem-solving Sessions

In the timetabled lecture slots, we will be running online problem-solving sessions where we will work through problems related to the content videos for that week. The sessions are intended to be interactive, so please ask questions or pose alternative solutions. These problem-solving sessions will be recorded.

Tutorials

Each week, you will have a 90-minute online session with a tutor. There will be a set of tutorial problems available each week, covering topics from the previous week. During the tute, you can either run a Q&A with the tutor or work through some of the problems together. Discussion is encouraged. Solutions will be made available to the tutorial problems in the weeks following the tute.

Prac Work

Prac exercises will be made available periodically on the course web site. You should work through each exercise when it becomes available, since exercises are often a pre-cursor to the assignment work that follows.

Assignments

In the assignments, you will consider the major stages of the database application development process, conceptual design, implementation as a PostgreSQL application, and building components of a database application using Python. The assignments contribute 42% of the overall mark for this course.

Assignments are completed individually ; this means that you should do them yourself without assistance from others, except by asking advice from the Lecturer or Course Admin or your Tutor. Assignments are a 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. The assignments also lay the foundation for successful performance in the final exam.

Assignments are to be submitted on-line (via give or WebCMS) before midnight on the due date. Assessment of assignments will be based on how accurately they satisfy the requirements, as determined by automatic marking. We provide testing harnesses for the second and third assignments so that you can determine whether your code is working correctly. The supplied tests will use one instance of the database; the auto-marking will use these same tests, but will also run tests using one or more different database instances.

Late submissions will have marks deducted from the maximum achievable mark at the rate of 0.08 of the total mark per hour that they are late (i.e., approx 2 marks off the maximum assignment mark per day).

Quizzes

Each week, starting in Week 2, there will be a quiz on topics from the previous week. This gives you a chance to review what you've learned on those topics. Quizzes are released on Monday mornings and are due before midnight on the following Friday (i.e. 5 days later). Each quiz will have 4 questions.

Do not submit your quiz answers late (there are heavy late penalties), or forget to submit a quiz.

Forums/Consultations

You should make use of the forums to help you with prac work and assignments. If you want your question answered, you need to provide details (e.g. what you tried, what you observed, the context where you tried it). It is difficult to provide assistance without enough information on what you're doing. In particular, problems in setting up and running your PostgreSQL server are very difficult to diagnose with a lot of context. Such questions are probably best handled in a consultation, where you can share your screen.

One thing you should not post on the forum are examples of assignment work (e.g. "Tried this query for Q3, but it gave me this unexpected output"). Save such questions for consultations.

Final Exam

The Final Exam will be held online. The paper will be made available via Webcms3 and submission will be via Give (and, yes, we've fixed the problem that caused Give to flag up at the end of the exam). The paper itself will be primarily based on SQL and PLpgSQL. It should take approximately 3-hours to complete. The exam will be open for a 6-hour time-period.

Note: anyone caught colluding, collaborating, posting exam solutions onto any internet platform, reading solutions from any internet platform, will be reported to the Student Conduct Investigation Unit, and will most likely end up with a mark of 00FL for the course, if not exclusion from further study at UNSW.

Teaching Rationale

COVID has thrown up some challenges in running CSE courses. We have adopted the above strategies because we believe they are the best way for helping you to learn in a completely online environment.

Student Conduct

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:

Assessment

There will be three places where your learning in this course will be assessed: assignments, quizzes 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).

There will also be a number of on-line quizzes during the semester, to be taken in your own time (but before indicated due dates), primarily as a way for you to gauge your progress in the course, but also worth some marks.

Lastly, 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 held online, 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 at least 45% (18/40) on the exam. Note that partial marks are available for all questions on the final exam, and the hurdle will be enforced after any required scaling.

Item Topics Due Marks Contributes to
Quizzes All topics Weeks 2-10 18% 1,2,3,4,5,6,7,8
Assignment 1 SQL Schema Week 4 12% 2
Assignment 2 SQL/PLpgSQL Week 7 16% 3,4
Assignment 3 Python/SQL Week 10 14% 5
Final Exam All topics Exam period 40% 1,2,3,4,5,6,7,8

Your final mark in this course will be based on the marks from the above three 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: 

quizzes = mark for on-line quizzes   (out of 18)
ass1    = mark for assignment 1      (out of 12)
ass2    = mark for assignment 2      (out of 16)
ass3    = mark for assignment 3      (out of 14)
exam    = mark for final exam        (out of 40)
okExam  = exam >= 18                 (after scaling)
mark    = ass1 + ass2 + ass3 + quizzes + exam
grade   = HD|DN|CR|PS  if mark >= 50 && okExam
        = FL           if mark <  50 && okExam
        = UF           if !okExam

Course Schedule

Week Lectures Tutes Prac Work Assignments Quizzes Notes
1 Course intro, Data Modelling, ER diagrams Welcome Set up PostgreSQL server - - -
2 Relational Model, SQL DDL, Mapping ER to SQL Data modelling, ER Defining a database - Quiz 1 -
3 SQL (i) ER -> SQL Simple SQL - Quiz 2 -
4 SQL (ii), PLpgSQL(i) SQL More interesting SQL Ass1 due Quiz 3 -
5 PLpgSQL (ii), Triggers, Aggregates PLpgSQL functions PLpgSQL functions - Quiz 4 -
6 Flexibility Week - - - - -
7 DB/PL interaction, Python, Psycopg2 Triggers Aggregates Ass 2 due Quiz 5 -
8 Functional dependencies, Normalization Psycopg2 Python meets SQL - Quiz 6 -
9 Relational Algebra, Query execution, Tuning Normalization - - Quiz 7 -
10 Transactions, Concurrency control, Database futures, Course review Relational algebra Sample exam Ass 3 due
Quiz 8 -

Resources for Students

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 exactly 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 as the primary DBMS 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).

For programming interaction with the database, we will be using Python via psycopg2.

Course Evaluation and Development

This course is evaluated each session using the myExperience system.

In the previous offering of this courses, students noted that the Webcms3 forums were less than optimal and that it was difficult to get engaged (with other students) online.

We have re-written the Webcms3 forums (coming soon) to incorporate the requested features from other forum platforms like Piazza. This term will be the first time the new forum is used, so feedback is welcome.

Tute classes are longer this term, to give more chance to interact with tutors. Also, we will try to work out ways to make online tutorials more engaging. Let us know how they're going.

Finally, to try build a sense of community, have created an "Amusing Musings" page, where you can post any old (preferably database-related) ideas that you think others might find interesting or amusing.

Resource created Thursday 27 August 2020, 03:20:24 PM, last modified Friday 11 September 2020, 10:25:00 AM.

Back to top

COMP3311 20T3 (Database Systems) is powered by WebCMS3
CRICOS Provider No. 00098G