Welcome to Relational Databases 2021!
COMP2400/COMP6240
Data & Databases
Structured data
have the same defined format
e.g., relational databases
Semi-structured data
have certain structure
but do not have an identical structure
e.g., XML
text
= ”figures/jerry.jpg”
text
= ” .nz”
text
= ”Jerry”
Employee
Name Position Email Phones Qualification Skills Photo
Phone Phone Phone Skill Skill Skill
text
= ”Mouse”
Since
= ”2000”
text
= ”Master of Arts”
text
= ”Hiding”
text
= ”Running”
text
= ”Teasing”
text
= ”211 3333”
Kind
= “mobile”
text
= ”354 1112”
Kind
= “work”
text
= ”350 1111”
Kind
= “work”
Unstructured data
do not have any predefined format
e.g., plain text
datainsights.de/types-of-data-and-their-importance/
Motivations for Studying Databases
Current trends:
“small data” ↪→ “big data”
“data” ↪→ “knowledge”
Databases can support:
data management
data analysis
…
Motivations for Studying Databases
Major public databases fuel personalized medicine1
1
http://bioinfo.cipf.es
Motivations for Studying Databases
Google’s knowledge graph2
2
https://www.google.com/insidesearch/features/search/knowledge.html
Database research
Three database researchers have received the ACM Turing Award (Nobel
Prize of Computing).
Codd (1981) Gray (1998) Stonebraker (2014)
What will you learn?
Weeks Topics
1 Introduction to database systems
2 Relational data model and SQL (1)
3 Relational data model and SQL (2)
4 Entity-relationship model
5 Functional dependencies
6 Normalisation
7 Relational algebra
8 Query processing and optimisation
9 Database security
10 Database transactions
11 NoSQL Databases
12 Revision Sessions
Both theory and practice are important!
Course site
Wattle (UID and password):
http://wattle.anu.edu.au
Online lecture videos and slides
Self-assessment quizzes (after watching online lectures)
Workshop videos and slides
Tutorial/lab exercises
Assignment specifications, submissions and marks
Supplementary reading materials
Course news and discussion forum
Lab group sign-up
Useful links
. . .
http://wattle.anu.edu.au
Textbook
Only recommended (not required)
Fundamentals of Database Systems, 7th Edition (Global Edition), R.
Elmasri and S. Navathe, 2017
Other editions (4th, 5th or 6th editions) are also fine.
Page 1 of 1
24/06/2013http://images.amazon.com/images/P/0132144980.01.MZZZZZZZ.jpg
Several copies in the reserve section of the Hancock library.
Available at the Co-op bookshop.
In this course, lecture slides and other course materials will be your best
reference.
Lab Environment
We will use the open source relational database management system
Postgres.
1 The PostgreSQL documentation is recommended for additional
reading. It includes an excellent SQL reference.
https://www.postgresql.org/docs/14/index.html
2 The PostgreSQL software and other resources is available from the
PostgreSQL website.
http://www.postgresql.org
https://www.postgresql.org/docs/14/index.html
http://www.postgresql.org
How will you learn?
Online lectures (pre-recorded): motivation and formal concepts
Workshops(Fri 1-3pm): practical examples and active communication
on campus, Manning Clark Hall at Kambri
online Zoom live
Labs (live) practice by yourself and bridge your learning gaps
Note: Lab group sign-up ↪→ Open from 12pm 28th July to 11:59pm 31
July (Saturday)!
Wattle discussion forum: ask questions, discuss with peers, etc.
Suggested readings: textbook and supplementary materials.
Who are we?
Lecturers
Yu Lin (Course Convener)
R4.25, Hanna Neumann Building (#145), yu. .au
Drop-in Session: Tue 2pm-3pm (Week 1 – Week 9)
Qing Wang
R4.26, Hanna Neumann Building (#145), qing. .au
Drop-in Session: Tue 2pm-3pm (Week 10 – Week 12)
mailto:yu. .au
mailto:qing. .au
Who are the students?
We have a wide range of students in the class.
First year, later year, post-grad
Domestic and international students
Computing, engineering, science, information systems/technology,
commerce, finance, arts, “unknown”
Varied computing and math skills
As of today, we have 650+ students in total!
Who are the students?
We will cater for individual needs in this course.
For new students and non CS students
Lab 1 in Week 2 on computing environment (important)
For CS students
Lab 8 in Week 10 on database programming (requested by
previous CS students)
For postgraduate students
Related research readings
For students who are struggling
Most marks are for core material, you can ignore the advanced
stuff and still pass
For students who are looking for challenges
Some difficult questions to try on
Course Policy on Communication
Each student is required to respect and comply with the following policy:
Students should post course-related questions on Wattle instead of
emailing lecturers and tutors directly.
Our course lecturers and other tutors will be monitoring and answering
Wattle questions.
Course-related questions which are directly sent to our lecturers and
tutors may be re-directed to Wattle so the answers can benefit the
entire class.
Students must not post any hints or solutions to
assignments/quizzes/test on the class discussion forum.
If you have questions regarding your specific personal circumstances,
you can send your email to yu. .au.
mailto:yu. .au
What are you expected to do?
Watch online lectures, complete quizzes and provide feedback
Join workshops for more running examples and explanations
Engage in labs for more exercises
Regularly check the Wattle course site
Submit assignments and test on or before the due dates
Attend the examinations
Complete the required readings
feedback Think critically and be active!
If you are having any difficulty, talk to us!
How will our students be assessed?
The assessment weighting for COMP2400/6240 students:
5% Quizzes and Lab Best 6 out of 10 quizzes (0.5% × 6 = 3%) and
engaging 4 out of 8 labs (0.5% × 4 = 2%, at your
own choice).
20% Assessment (Individual) Assessment on SQL.
15% Assignment (Individual) Assignment on database theory.
5% NoSQL test An online test on Wattle about NoSQL databases
will be held in Week 12.
55% Final exam A final exam will be held at the end of semester.
Any appeals regarding an assessment must be submitted in writing within
two weeks of the assessment results being released on Wattle or by email.
How will you be assessed?
To pass the course,
Obtain at least 40% in the final exam, i.e., 22 marks out of 55;
Obtain at least 50% as a combined total of quizzes, labs, assignments,
test and exams, i.e., 50 marks out of 100;
To get a HD in this course,
Obtain a final mark above 80.
Final marks may be scaled as a result of the moderation at the examiners’
meeting.
Time Commitment
You’re expected to spend approximately 120 hours work on this course.
Activities Hours
Online lecture/workshop/classroom 36
Lab engagement 16
Homework/readings 32
Assignments/Tests 36
Total 120
The amount of time you will need to spend on study also depends on other
factors such as your prior knowledge and learning style.
These hours do not include the time you spend studying for your final exam.
CECS Class Representatives
Why become a course representative?
Develop skills sought by employers, including interpersonal, dispute
resolution, leadership and communication skills.
Become empowered. Play an active role in determining the direction of
your education.
Become more aware of issues influencing your University and current
issues in higher education.
Ensure students have a voice to their course convener, lecturer, tutors,
and college.
Course design and delivery help shape the delivery of your current
courses as well as future improvements for following years.
Refer to more information on the Wattle site. Please nominate yourself by
sending an email to yu. .au with the title “Course
Representatives for COMP2400/6240” by midday 2 August 2021.
Student Feedback
Your feedback on this course will be highly valued!
Feel free to send your feedback to Yu and Qing.
Feel free to send your feedback to your tutor.
Feel free to contact your class reps.
Feel free to provide your feedback in online quizzes.
We will also have two anonymous feedback surveys:
1 Week 6 Survey on teaching pace, teaching material and tutors;
2 Student Evaluations of Learning and Teaching (SELT) survey at
the end of the teaching on all issues of teaching and learning.
Cooperation and Plagiarism
The ANU has guidelines and formal policies on academic honesty which
you must adhere to:
http://academichonesty.anu.edu.au.
1 Discussing the course content and activities with other students is a
great way to learn, and we encourage it.
2 However, work you hand in for assessment must be your own.
3 Assignments submitted electronically, may be run through plagiarism
detecting software, and tutors will be asked to look out for copied work.
4 Incidents of plagiarism will be taken seriously and can result in
disciplinary action.
http://academichonesty.anu.edu.au
Manage your own learning
Always ask yourself “am I getting this?”
Test your understanding, try things out, do exercises.
If you don’t get it, do something about it.
Re-read the relevant lecture notes or sections of the textbook.
Post a message on the discussion forum on Wattle.
Find alternative sources in the library or on the internet.
Ask your classmates.
Talk to your tutor or the lecturers.
Tentative course schedule
Weeks Lectures Labs Tasks
1 Introduction to Database Systems No labs –
2 Relational Data Model and SQL (1) Lab 1: Lab Environment –
3 Relational Data Model and SQL (2) Lab 2: Basic SQL –
4 Entity-Relationship Model Lab 3: Advanced SQL –
5 Functional Dependencies Lab 4: Entity-Relationship Model –
6 Normalisation No labs Assignment 1 due
Mid-semester Break
7 Relational Algebra Lab 5: Functional Dependencies –
8 Query Processing and Optimization Lab 6: Normalisation –
9 Database Security Lab 7: Relational Algebra and –
Query Optimization
10 Database Transactions Lab 8: Database Programming Assignment 2 due
11 NoSQL Databases No labs –
12 Revision sessions No labs NoSQL test due
Final Examination Period
Work to do in Weeks 1 and 2
Week 1
Read the course outline (available on the Wattle course site).
Watch the on-line lectures (on general concepts and math concepts).
Sign up a lab group on Wattle.
Attend the workshop for an overview of this course.
Week 2
Watch the online lectures and complete the quiz before Thursday.
Join the workshop on Friday.
Attend Lab 1 to get familiar with the lab environment, especially for
students who are not familiar with Unix/Linux system. More
information is available on Wattle.
Join the drop-in session if you have any questions.
Contact Yu Lin yu. .au if you need assistance in undertaking
the course because of disability or other circumstances.
mailto:yu. .au
Lab 1: Lab Environment
Log in to STREAMS at https://cs.anu.edu.au/streams/ to activate your
account in the CSIT computing environment. Log in with your ANU ID and
password.
Option 1: Connecting to ANU VDI environment from your computer
Preferred method for students with a fast and stable internet
connection to the ANU computing facilities.
Students must first use GlobalProtect to enter the ANU VPN.
Option 2: Setting up a virtual machine on your computer
Allow students to set up the lab environment locally on their own
computer
Students need to download a large file and set up the virtual machine.
In addition to your lab in Week 2, you are very welcome to bring your
questions to the following ad-hoc drop-in sessions (Aug 3, Tue 3-4pm and
Aug 6, Fri 3-5pm) in Week 2.
https://cs.anu.edu.au/streams/
Databases in Our Lives
Name a business which uses databases:
What kind of data it needs to store?
What kind of queries it needs to answer?
What is the role of databases in it?
Why do we need to organise data in databases?
Databases in Our Lives
Australian Bureau of Statistics3
3
http://www.abs.gov.au/websitedbs/censushome.nsf/home/2016
Databases in Our Lives
Australian Transactions Reports and Analysis Centre (AUSTRAC) 4
AUSTRAC receives and stores transaction reports from various
reporting entities in Australia, including banks, casinos and
uperannuation funds, to help combat money-laundering and
terrorism financing activities.
4
http://www.austrac.gov.au/
Databases vs. Spreadsheets
Databases vs. Spreadsheet
Storing all enrolment information into a single spreadsheet
ENROL
StudentID Name Address CourseNo Semester
101 Bernard 25 Edinburgh Ave, Acton 2601 COMP1130 2018 S1
102 Teddy 10, Gould Street, Turner 2612 COMP1130 2018 S1
103 Elsie 9 Watkin Street, Bruce 2617 COMP2400 2018 S2
102 Teddy 10, Gould Street, Turner 2612 COMP2400 2018 S2
… … … … …
… … … … …
Question: Can you identify any issues in doing this?
.
.
.
Databases vs. Spreadsheet
Storing all enrolment information into a single spreadsheet
ENROL
StudentID Name Address CourseNo Semester
101 Bernard 25 Edinburgh Ave, Acton 2601 COMP1130 2018 S1
102 Teddy 10, Gould Street, Turner 2612 COMP1130 2018 S1
103 Elsie 9 Watkin Street, Bruce 2617 COMP2400 2018 S2
102 Teddy 10, Gould Street, Turner 2612 COMP2400 2018 S2
103 Elsie 9 Watkin Street, Bruce 2617 COMP2400 2018 S2
… … … … …
Question 1: How can we ensure that the same student does not
enrol in the same course in the same semester twice?
Please refer to the Relational Data Model in Week 2.
.
Databases vs. Spreadsheet
Storing all enrolment information into a single spreadsheet
ENROL
StudentID Name Address CourseNo Semester
101 Bernard 25 Edinburgh Ave, Acton 2601 COMP1130 2018 S1
102 Teddy 10, Gould Street, Turner 2612 COMP1130 2018 S1
103 Elsie 9 Watkin Street, Bruce 2617 COMP2400 2018 S2
102 Teddy 10, Gould Street, Turner 2612 COMP2400 2018 S2
… … … … …
… … … … …
Question 2: How can we print out all the courses in S2 2018 along
with the number of enrolled students?
Please refer to SQL in Week 3.
.
Databases vs. Spreadsheet
Storing all enrolment information into a single spreadsheet
ENROL
StudentID Name Address CourseNo Semester
101 Bernard 25 Edinburgh Ave, Acton 2601 COMP1130 2018 S1
… … … … …
Question 3: How can we model students and courses as well as
the relationship between them?
Please refer to Entity-Relationship Model in Week 4.
.
Databases vs. Spreadsheet
Storing all enrolment information into a single spreadsheet
ENROL
StudentID Name Address CourseNo Semester
101 Bernard 25 Edinburgh Ave, Acton 2601 COMP1130 2018 S1
102 Teddy 10, Gould Street, Turner 2612 COMP1130 2018 S1
103 Elsie 9 Watkin Street, Bruce 2617 COMP2400 2018 S2
102 Teddy 10, Gould Street, Turner 2612 COMP2400 2018 S2
… … … … …
… … … … …
Question 4: Would “Name” and “Address” be redundant
information in the above table with respect to “StudentID”?
Please refer to Functional Dependencies in Week 5.
.
Databases vs. Spreadsheet
Storing all enrolment information into a single spreadsheet
ENROL
StudentID Name Address CourseNo Semester
101 Bernard 25 Edinburgh Ave, Acton 2601 COMP1130 2018 S1
102 Teddy 10, Gould Street, Turner 2612 COMP1130 2018 S1
103 Elsie 9 Watkin Street, Bruce 2617 COMP2400 2018 S2
102 Teddy 10, Gould Street, Turner 2612 COMP2400 2018 S2
… … … … …
… … … … …
Question 5: Should we split the above table into multiple smaller
ones to reduce the redundancy?
Please refer to Normalisation in Week 6.
.
Databases w.r.t. Mathematics
Question 6: What is a computer’s point of view on creating,
querying and updating databases?
Please refer to Relational Algebra in Week 7.
.
Databases w.r.t. Computers
Question 7: How will a computer process and execute your input
command?
Please refer to Query Processing and Optimisation in Week 8.
.
Databases Security
Question 8: How can we protect the databases from hackers?
Please refer to Databases Security in Week 9.
.
Databases Transactions
Question 9: How can we keep a database consistent even in
cases of system failure?
Please refer to Databases Transactions in Week 10.
.
Relational Databases vs. NoSQL Databases
Question 10: What are the opportunities and challenges of
databases in the big data era?
Please refer to NoSQL Databases in Week 11.
.
(credit cookie) Set Notation and Russell’s Paradox
Two ways of specifying a set
1 {x1, . . . , xn} (i.e., list all the elements in a set)
S = {1, 2, 3, {4, 5}}
1 ∈ S, 2 ∈ S, 3 ∈ S, 4 6∈ S, {4, 5} ∈ S
2 {x |ϕ} (i.e., describe the elements that satisfy a property ϕ)
{students | currently enrolled in COMP6240 };
{x |x ∈ N}, i.e., the set of natural numbers.
{x|x 6∈ x}.
(credit cookie) Set Notation and Russell’s Paradox
Let R = {x|x 6∈ x}, i.e., the set of all sets that do not contain themselves
as members.
R ∈ R =⇒ R contains itself as its members =⇒ R 6∈ R
R 6∈ R =⇒ R does not contain itself as its members =⇒ R ∈ R
The barber is the “one who shaves all those, and those only, who do not
shave themselves”. The question is, does the barber shave himself?
Bertrand Russell was awarded the Nobel Prize for Literature in 1950.
Course Information