Course Outline

Contents

• Course Details
• Course Aims
• Student Learning Outcomes
• Assumed Knowledge
• Teaching Strategies
• Teaching Rationale
• Student Conduct
• Assessment
• Course Schedule
• Resources for Students
• Course Evaluation and Development

Course Details

Course Aims

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.

Student Learning Outcomes

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

• develop accurate, non-redundant data models;
• realise data models as relational database schemas;
• formulate queries via the full range of SQL constructs;
• use stored procedures and triggers to extend DBMS capabilities;
• write applications in PHP that interact effectively with databases;
• analyze performance issues in relational database applications;
• understand the overall architecture of relational DBMSs;
• understand the concepts behind transactions and concurrency control;
• explain how query and transaction processing techniques work in RDBMSs

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.
• PHP : is an acronym for "PHP: Hypertext Preprocessor" ... the widely-used server-side scripting language designed for web development (but also used as a general-purpose programming language) .

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

Assumed Knowledge

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.

Teaching Strategies

There are four primary modes of learning for this course: lectures, tutorials, exercises, assignments.

Lectures/Tutorials/Exercises

Each week 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 tutorials in this course. Tutorial-type questions and practical 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 tutorials.

Tutorials 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.

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 a database application using PHP. The assignments contribute 30% of the overall mark for this course.

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) before midnight on the due date. Assessment of assignments will be primarily based on how accurately they satisfy the requirements; this means that most of the mark will be based on automatic marking. However, we will 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).

Teaching Rationale

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/or 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 tutorials.

Naturally, sometimes people will "get stuck" on understanding various aspects of the material, so we have 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 Tutorials or using the Forums.

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:

• Plagiarism and Academic Integrity
• UNSW Plagiarism Procedure

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

• UNSW's policy regarding academic honesty and plagiarism

The pages below describe the policies and procedures in more detail:

• Student Code Policy
• Student Misconduct Procedure
• Plagiarism Policy Statement
• Plagiarism Procedure

You should also read the following page which describes your rights and responsibilities in the CSE context:

• Essential Advice for CSE Students

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 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 partial marks are available for all questions on the final exam, and the hurdle will be enforced after any required scaling.

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:

ass1    = mark for assignment 1      (out of 15)
ass2    = mark for assignment 2      (out of 15)
quizzes = mark for on-line quizzes   (out of 10)
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 + quizzes + exam
grade   = HD|DN|CR|PS  if mark >= 50 && okExam
        = FL           if mark <  50 && okExam
        = UF           if !okExam
        

Course Schedule

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.

Resources for Students

Any of the following books is a suitable textbook for this course:

• Fundamentals of Database Systems , Elmasri and Navathe, 7th edition, 2016, Addison-Wesley
• Database System Concepts , Silberschatz, Korth, Sudarshan, 6th edition, 2010, McGraw-Hill
• Database Management Systems , Ramakrishnan and Gehrke, 3rd edition, 2003, McGraw-Hill
• Database Systems: The Complete Book , Garcia-Molina, Ullman, Widom, 2nd edition, 2008, Prentice-Hall
• Database Systems: An Application-Oriented Approach Kifer, Berstein, Lewis, 2nd edition (Complete Version), 2006, Addison-Wesley

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 topics covered in the course. The Lecture Slides are less detailed, but have the advantage that they cover precisely the syllabus (but no more). They also contain examples that will be discussed in detail. All Lecture Slides and solutions to the examples from lectures will be posted on the course web site.

The software systems to be used in this course are PostgreSQL, SQLite, and PHP. The documentation and manuals provided with PostgreSQL and PHP are actually very good, and the SQLite 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 PHP .

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). Because SQLite requires no server, it will be used as the DBMS in the Prac Exam.

For programming interaction with the database, we will be using PHP. During lectures, we may look briefly at some other programming languages which are commonly used for interaction with databases (e.g., Java, Ruby, Python), but these will simply be for illustrative purposes; we won't expect you to use them in any prac work.

Course Evaluation and Development

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 tutorials, where problems with the exercises can be discussed, would be useful for the course. This has been addressed. This is the first time that we are fitting the course into 10 week of the UNSW3+ academic calendar model. Therefore, 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.