Administration; Introduction

ISYS2120 Data and Information Management

Dr. Matloob Khushi

University of Sydney

Acknowledge: slides from Prof Alan Fekete and Uwe Roehm, and from the
materials associated with reference books (c) McGraw-Hill, Pearson
 COMMONWEALTH OF AUSTRALIA

Copyright Regulations 1969

WARNING

This material has been reproduced and communicated to you by or on

behalf of the University of Sydney pursuant to Part VB of the Copyright Act
1968 (the Act). The material in this communication may be subject to
copyright under the Act. Any further copying or communication of this
material by you may be the subject of copyright protection under the Act.

Do not remove this notice.

2
 Acknowledgement of Country
Before we begin the proceedings, I would like to
acknowledge and pay respect to the traditional
owners of the land on which we meet; the Gadigal
people of the Eora Nation. It is upon their ancestral
lands that the University of Sydney is built.
As we share our own knowledge, teaching, learning
and research practices within this university may we
also pay respect to the knowledge embedded
forever within the Aboriginal Custodianship of
Country.
 Role of the unit
• “The ubiquitous use of information technology leaves us facing a tsunami of data
produced by users, IT systems and mobile devices. The proper management of
data is hence essential for all applications and for effective decision making within
organizations. This unit of study will introduce the basic concepts of database
designs at the conceptual, logical and physical levels. We will place particular
emphasis 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 the SQL
database query languages, which is industry standard. Other topics covered will
include the important concept of transaction management, application
development with a backend database, and an overview of data warehousing and
OLAP.” [from UoS outline]
• 2018 curriculum: Required for BAdvComp, 2018 BSc and shared pool Information
Systems major
• Replaces INFO2120, so required for BCST, BIT, BE(Software), previous years BSc
Information Systems major
 ISYS2120 times and places
• 2 hrs/week of lecture, every Tuesday 4-6pm
– we aim to record lectures, but technology can fail
– slides will be posted
• 2 hrs/week of Lab: in a block, depends on your
timetable
– try to go to the lab you are scheduled for
– If necessary, you can ask to attend another lab session if
there is space and the tutor agrees, but ask the tutor
before taking a seat
– You can work on your own device
– Note that weekly quizzes and SQL quiz happen in lab time
– Do not miss lab, except for illness, emergencies, etc
• Get help from staff if you feel you are falling behind
 ISYS2120 people
Unit coordinator, and also lecturer Dr Matloob Khushi
([Link]@[Link])
› This is the person who deals with all paperwork and admin
issues
› Illness or misadventure
› Rules and policies
› Best contacted by email (make sure the subject
mentions the UoS code)
Teaching Assistance: Harshana
sran6646@[Link]
Tutors: depends on your timetable list on Canvas
 Class Representative
• The role of (B/G)CR is to communicate
anonymously concerns/issues of the students
to teaching team.
• This will enable a back & forth communication
instead of one-way.
• Call for volunteers!
 ISYS2120 resources
• Canvas
– [Link]
– Login using Unikey and password
– within this, the unit-specific subsite
– Link to Unit Outline
• Official assessment schedule, list of learning outcomes, etc
– Copies of slides
– Instructions for labs and assignments
– Quizzes
– Lecture videos
• We intend to record the lectures
• Let us know of difficulties, so we can get recordings fixed!
– Submit text-based official assignment work here;
– Link to discussion forums on Edstem
– see your grades; etc
– Link to GrokLearning site where SQL-related explanations and tasks are run
 ISYS2120 resources
• GrokLearning site, teaching SQL
– Access from Canvas
– Always follow link from Canvas to a particular
Grok module, to work on it
• do NOT move onto other modules within Grok; instead
return to Canvas first
– module includes explanations “circles” (with
runnable modifiable code for experiments)
– module also includes tasks “diamonds” which
require you to write code
• includes some automatic testing (some public tests;
also perhaps hidden tests)
 ISYS2120 Reference books
• lots of choice,
from SciTech
library
• see if one helps,
if so, consult it
regularly
– alternate
explanations,
– more detail than
lectures
– but notations
differ
acknowledge: book cover images (c)
McGraw Hill, Pearson.
ISYS2120 External online resources
• There are lots of Database classes online
• Many are free, but often they are aimed at
more CS focus (eg platform internals)
• One set (linked to Garcia-Molina textbook)
[Link]
[Link]
• [Link]
 General Expectations
• Students attend scheduled classes(*), and devote an extra 8 hrs per
week
– (*) carefully watch videos if not able to attend lectures
– doing assessments
– preparing and reviewing for classes
– revising and integrating the ideas
– practice and self-assess
• Students are responsible learners
– Participate in classes, constructively
• Respect for one another (criticize ideas, not people)
• Humility: none of us knows it all; each of us knows valuable things
– Check your Uni email daily
– Check Canvas site at least once a week
– Work on GrokLearning platform every week
– Notify academics whenever there are difficulties
– Know and adhere to University policies
 ISYS2120 Expectations
• Prerequisites:
– one of a range of units giving programming skills
– we will use Python and Java in examples
– Also, learning skills, time management etc as
needed in second year Uni!
• If you find you are missing any aspect of what
is needed for following lectures or labs,
please contact the coordinator immediately
 ISYS2120 Assessments
Assessment Week Due Weight (%)
Weekly in-tutorial Weekly 10
quizzes
SQL quiz Week 7 10
Assignment 1 Week 5 10
Assignment 2 Week 7 5
Assignment 3 Week 12 15
Exam Exam Weeks 50
 ISYS2120 Weekly Quizzes
• Done during scheduled lab sessions
(usually in last hour of the lab)
• one quiz every week (week 1-6, 8-11)
• Worth 1% each, but total capped at 9
• Answer multi-choice questions about the
material of the previous week
• Quizzes provide practice and feedback,
especially on the conceptual material, to help
students prepare for the written exam
 ISYS2120 SQL quiz
• Done during scheduled lab session in
week 7
• Worth 10%
• Like several of the Grok-based diamonds, but
done under some time pressure
– autograded, also using some hidden data that
you don’t know
– you may use Grok resources from the unit during
test
• To check that you have gained competence
in writing SQL queries
 ISYS2120 Asst1
• Worth 10%
• Due week 5
• Work in small groups (3-4 students)
– group members must all be attending same lab
session
– group members get same score unless someone
found to be non-contributing member
• DB Schema Design
– start from E-R diagram, hand in SQL code to
create tables
 ISYS2120 Asst2
• Worth 5%
• Due week 7
• Work in small groups (3-4 students)
– group members must all be attending same lab
session
– different group from asst1
– group members get same score unless someone
found to be non-contributing member
• DB Conceptual Design
– start from textual description, hand in E-R
diagram
 ISYS2120 Asst3
• Worth 15%
• Due week 12
• Work in small groups (3-4 students)
– group members must all be attending same lab
session
– different group from asst1 and from asst2
– group members get same score unless someone
found to be non-contributing member
• DB Programming
– complete a given code skeleton, so it acts as a
database application for a given database. Also, write
a report on the decisions taken
– Interview / Viva Voce
 ISYS2120 Exam
• Worth 50% of the unit
• Written questions will cover the content of
lectures and labs and assignments.
• School of Computer Science policy: you
must get at least 40% of the marks
available on the exam, in order to pass the
unit (as well as getting at least 50% overall
mark)
 Two-way Assessment

• Teaching Team – Students

Via Unit of 85% Mark High Distinction
Sydney Survey 75% Mark Distinction
We aim to win your
opinion to ‘Strongly 65% Mark Credit
Agree’ in all of the 50% Mark Pass
teaching criteria.
 Special Consideration (University
policy)
• If your performance on assessments is affected by illness or
misadventure
• Follow proper bureaucratic procedures
– Have professional practitioner sign special USyd form
– Submit application for special consideration online, upload scans
– Note you have only a quite short deadline for applying
– [Link]
– Be careful to use “CUSP name” for the task
– If request is denied, consult coordinator immediately (perhaps you can
resubmit with better documentation)
• Also, notify coordinator by email as soon as anything begins to go
wrong
• There is a similar process if you need special arrangements eg for
religious observance, military service, representative sports
 Late assessments in ISYS2120
• Suppose you hand in work after the deadline
– No late submission allowed for weekly quizzes or SQL challenge, or for SQL test
• For assignments (If you have not been granted special consideration or
arrangements, or a “simple extension” by email)
– A penalty of 5% of the available marks will be taken, each calendar day (or part) late
– No work is accepted more than 10 calendar days after the due date.
• Eg your work would have scored 60% and is 2 hours late (1am next day)
– you get 55%
• Eg your work would have scored 60% and is 28 hours late
– you get 50%
• Warning: submission sites get very slow near deadlines
• Get something done early and submit it early; you can try to improve it and
resubmit if there is time before the deadline
 Academic honesty
• Please read the University policy on Academic Honesty carefully, from
[Link]
“As a student of the University, you are responsible for taking part in your education
in an honest and authentic manner.”
• All cases of academic dishonesty and plagiarism will be investigated
• There is a process and a centralized University system and database
• Offenses include:
• Plagiarism – when you copy from another student, website or other source (without
explicitly indicating that the work is a copy). This includes copying a small amount.
• Academic dishonesty – when you make your work available to another student to copy.
There are other examples of academic dishonesty.
• Misconduct - when you engage another person to complete your assignment (or a part
of it), for payment or not. This is a very serious matter and the Policy requires that
your case is forwarded to the University Registrar for investigation.
2
8
 Example
Source of Help
Lecturer Teaching Classmates Private Online Students Hired coders Relatives Othe
Assistants / tutors forums/ outside r
Tutors Online course/Uo Tutorial Company
tutors S outside University

• Individual assessment
• A student needs to gain an understanding of high level knowledge/skills
• A student needs to gain skills to find, evaluate and apply existing knowledge/solutions
Encouraged
Attribution required
Not acceptable
Ask Lecturer/Coordinator
 Penalties
• The penalties for violation of academic integrity are severe and include:
1) a permanent record of academic dishonesty, plagiarism and misconduct in the University
database and on your student file
2) mark deduction, ranging from 0 for the assignment to Fail for the unit
3) expulsion from the University and (for international students) cancelling of your student
visa

3
0
 Advice, especially for SQL assessments
• Do not confuse legitimate co-operation and cheating! You can discuss the question
and your intended approach with another student, this is a legitimate
collaboration; but you cannot write the query together, nor can either of you make
use of the other’s actual code (not even part of it) – everyone must write their own
code and acknowledge sources of ideas.
• When there is copying between students, note that both students are penalised –
the student who copies and the student who makes his/her work available for
copying
• A lot of cheating occurs when students feel pressure as deadlines approach,
especially with autograding when you see that your work is not passing the tests.
Resist the temptation!
• It is much better for everyone if you submit whatever you can do yourself, even if
it isn’t complete or perfect.
• You learn by trying, and we can help you more effectively if we see your situation
accurately.
3
1
 Detection
• We will use the similarity detection software TurnItIn and MOSS to compare your
assignments with these of other students (current and previous) and the Internet
• Turnitin is for text documents: [Link]
• MOSS is for programming code: [Link]
• These tools are extremely good!
• e.g. MOSS cannot be fooled by changing the names of the variables or changing the
order of the conditions in if-else statements

3
2
 Student excuses
• All these are cases of plagiarism and academic dishonesty we have seen in our
school
• The student excuses are not acceptable:
• I sat the test and then posted the questions and solutions to my friends whose test was
later in the week. I only wanted to help them understand the concepts that are
examinable.
• I posted parts of my code on my web page (or the group discussion forum) because my
solution was cool (or I wanted to help them). I didn’t expect them to copy it.
• I tried to do the assignment on my own but I had problems with the extension part that I
couldn’t fix, so I submitted my core part and his extension part. I didn’t cheat.
• I finished my assignment but my friend had family problems. I felt sorry for her, so I gave
her my assignment as an example. She said she only wanted to have a look and
promised not to copy it.
• The test has finished but the tutor hasn’t collected the papers yet. I showed my answer
to my friend. I didn’t expect him to copy it.
• He is my best friend. I had no choice but to let him copy my assignment. 3
3
 Key message
• Plagiarism and any form of academic dishonesty will be dealt with, and the
penalties are severe
• We use plagiarism detection systems such as MOSS that are extremely good. If you
cheat, the chances you will be caught are very high.
• If someone asks you to see or copy your assignment, or asks you to complete the
assignment instead of them, just say: I can’t do this - we can both be thrown out of
the University. I will not risk my future by doing this.

Be smart and don’t risk your future by engaging in plagiarism and academic
dishonesty!
Make this class (and every class) a supportive learning community, by having academic
honesty as a core value.

3
4
 WHS INDUCTION

School of Computer Science

 General Housekeeping – Use of
Labs
• Keep work area clean and orderly
• Remove trip hazards around desk area
• No food and drink near machines
• No smoking permitted within University buildings
• Do not unplug or move equipment without permission
EMERGENCIES – Be prepared
[Link]/whs/emergency

3
7
EMERGENCIES

3
8
EMERGENCIES

3
9
 EVACUATION ASSEMBLY POINT
Follow directions of wardens

40
l

MEDICAL EMERGENCY
• If a person is seriously ill/injured:
1. call an ambulance 0-000
2. notify the closest Nominated First Aid Officer
If unconscious– send for Automated External Defibrillator (AED)
AED locations.
NEAREST to SIT Building (J12)
- Electrical Engineering Building, L2 (ground) near lifts
- Seymour Centre, left of box office
- Carried by all Security Patrol vehicles
3. call Security - 9351-3333
4. Facilitate the arrival of Ambulance Staff (via Security)

Nearest Medical Facility

University Health Service in Level 3, Wentworth Building

First Aid kit – SIT Building (J12)

kitchen area adjacent to Lab 110
School of Computer Science Safety
CHIEF WARDEN
Greg Ryan
Contacts
Level 1W 103
9351 4360
0411 406 322

FIRST AID OFFICERS

Julia Ashworth
Level 2E Reception
9351 3423 OR

Undergraduates: to Katie Yang

Will Calleja
Level 1W 103
9351 4918
9036 9706 Coursework
0422 001 964 Postgraduates: to Cecille Faraizi
9351 6060
Katie Yang
Level 2E 237
9351 4918
CS School Manager: Priyaka
9351 4158
 University of Sydney
helpline for students

If you're feeling unsafe on campus

or you're concerned for the safety of
others, call Campus Security on
02 9351 3333 24 hours a day.
 Assistance
• There are a wide range of support services
available for students
• Please make contact, and get help
• You are not required to tell anyone else about
this
• If you are willing to inform the unit coordinator,
they may be able to work with other support to
reduce the impact on this unit
– eg provide advice on which tasks are most
significant
 DISABILITY SERVICES
Do you have a disability?
• You may not think of yourself as having a ‘disability’ but the definition under the Disability
Discrimination Act is broad and includes temporary or chronic medical conditions, physical or
sensory disabilities, psychological conditions and learning disabilities.

• The types of disabilities we see include:

• anxiety, arthritis, asthma, asperger's disorder, ADHD, bipolar disorder, broken bones, cancer, cerebral palsy, chronic fatigue
syndrome, crohn’s disease, cystic fibrosis, depression, diabetes, dyslexia, epilepsy, hearing impairment, learning disability, mobility
impairment, multiple sclerosis, post traumatic stress, schizophrenia , vision impairment, and much more.

• Students needing assistance must register with Disability

Services –
– it is advisable to do this as early as possible.

• [Link]
 Do you have a disability?
You may not think of yourself as having a ‘disability’
but the definition under the Disability
Discrimination Act (1992) is broad and includes
temporary or chronic medical conditions, physical
or sensory disabilities, psychological conditions
and learning disabilities.

The types of disabilities we see include:

Anxiety // Arthritis // Asthma // Autism // ADHD
Bipolar disorder // Broken bones // Cancer
Cerebral palsy // Chronic fatigue syndrome
Crohn’s disease // Cystic fibrosis // Depression
Diabetes // Dyslexia // Epilepsy // Hearing
impairment // Learning disability // Mobility
impairment // Multiple sclerosis // Post-traumatic
stress // Schizophrenia // Vision impairment
and much more.

Students needing assistance must register with

Disability Services. It is advisable to do this as
early as possible. Please contact us or review our
website to find out more.

Disability Services Office

[Link]/disability
02-8627-8422
 Other support
• Learning support
– [Link]
• International students
– [Link]
[Link]
• Aboriginal and Torres Strait Islanders
– [Link]
[Link]
• Student organization (can represent you in academic appeals etc)
– [Link] or [Link]
• You are not required to tell anyone else about this
• If you are willing to inform the unit coordinator, they may be able to work
with other support to reduce the impact on this unit
– eg provide advice on which tasks are most significant
 Complaint mechanisms
Student complaints: see [Link]
• “A complaint is any type of problem or concern about academic or non-
academic matters that you raise with the University, and requires staff
to work with you towards a resolution. It could be to do with your
studies, student life, the University environment or the behaviour of a
student or staff member.”
• “Complaints give us an opportunity to identify areas for improvement.
We approach any experience of unreasonable treatment, disadvantage
or distress seriously and with sensitivity. Our goal is to work with you
towards a timely and effective resolution. If you choose to remain
anonymous, we may be limited in our ability to assist you. If you make a
complaint on behalf of someone else, we will be limited in disclosing
information to you due to privacy provisions.”
• Metacognition
Advice
– Pay attention to the learning outcomes in CUSP
– Self-check that you are achieving each one
– Think how each assessment task relates to these
• Time management
– Watch the due dates
– Start work early, submit early and often [history of your work is excellent evidence that you
didn’t copy!]
– Do not give in to temptation to copy; submit your own attempts even if they do not get
correct results
• Networking and community-formation
– Make friends and discuss ideas with them
– Know your tutor, lecturers, coordinator
– Keep them informed, especially if you fall behind
• Don’t wait to get help

• Enjoy the learning!

 Outline
• Data and DBMS in context
• The relational data model
– Terminology
– Practice understanding relational data
• Careers and people’s roles
• DBMS versus other ways to manage shared
data
• Some big ideas
 Terminology
• Data: Facts that can be recorded
– Important for users
– Need to persist (not just temporary values used during a computation)

• Database: A collection of data

– Usually quite large
– Usually containing all the data needed to operate an organization (or coherent part of an
organization)

• Database Management System (DBMS): software package designed to store and

manage one or more databases
– Allows shared access from many programs

Application
#1
Database
containing
Application
#2 DBMS centralized
shared data
Application
#3
 Categories of organizational data

• Personnel
• Inventory
• Orders
• Partners
• Transactions
• Tasks
 Importance of data
• The day-to-day operations of an organization depend on accurate
data
• The long-term planning in an organization depends on accurate data
• Example: a supermarket chain
– What data is needed to pay workers?
• “Carol Jones worked for 7 hours on Monday July 21, as Deli Manager”
– What data is needed to pay suppliers?
– What data is needed to decide when to offer a discount on tinned peas?
– Think of some other activities…
– Estimate the amount of data…
• Data is a major asset for the organization
– Think of the losses if the data is missing or wrong
– Think of the cost to replace the data
 Managing Data

• The usual way to build an information system today, is to write

many application programs which all use a shared collection of data
– The data is stored in a DBMS
– For core activities in large organizations, the DBMS is usually a
commercial one (Oracle, MS SQLServer, IBM DB2, etc)
– Smaller organizations, or non-mission-critical parts of large
organizations, often use Open Source systems (PostgreSQL, MySQL,
etc)
• also, really huge data in large organizations eg Facebook’s data on likes,
friends, etc
– Users run application programs which access the data through the
DBMS
• Programs can retrieve data and present it to the users
• Programs can update the data based on input from the users
 The Structure of Data

• A key idea in DBMSs is for the database itself to

store descriptions of the format of the data
• This is called the “System Catalogue” or “Data
Dictionary”
• Eg, you can find that each employee has
– Identifier which is integer
– Name which is a string of up to 30 characters
– Address which is string of up to 60 characters
– Salary which is integer
• This sort of information is often called meta-data
 Relational DBMSs
• Most DBMSs today store data that follows a very simple “relational”
structure
• All data is seen by users as tables of related, uniformly structured
information
– No duplicate rows, order is not important
– Each entry is simple: integer, string, etc
– Matching values in different tables indicate connections

Product:
Supplier: ProdID Descr SuppID
SuppID Name Phone
29012 Peas with Mint, 8731
8703 Heinz 0293514287 400g
8731 Edgell 0378301294 30086 Peas and 8703
Carrots, 450g
8927 Kraft 0299412020
31773 Salted Peanuts, 8731
9031 CSR 0720977632 500g
 Instance

• An instance is the contents of the database at

a single time
– Specific values, which describe a specific situation
in the world
– Every update changes the instance
 Schema

• The schema describes the structure of data in a

particular database
– What tables exist
– What the columns are called, the type of each
• The instance at any time must fit the pattern of
the schema
– The schema changes rarely, if at all
• Eg Supplier(SuppID: int, Name:string, Phone:
string)
• The schema can also include integrity constraints,
which restrict the possible instances
– Eg each supplier has a different SuppID
 Data Design

• A key task when creating an information system is to

decide on the schema that will be used for the data
– Unless there is already a DBMS with the data needed
• The process of making this decision is called “data
design”
• It proceeds in stages
– first produce a conceptual or semantic model,
– then translate this into a relational schema,
– then evaluate the schema for quality, and improve it if
needed
• We study this in detail in the first part of the semester.
 Languages

• DDL: Data Definition Language is used to define the schema. It allows one
to say tell the DBMS what tables exist, and what structure they have.
• DML: Data Manipulation Language is used to access the data. It includes
commands to update (change) the contents of the database, and also it
has commands that can retrieve information from the database
(“queries”).
• For a relational DBMS, both DML and DDL are in SQL
– SQL is quite declarative in style (programmer says “what is needed” not “how
to find it”)
– SQL is a standard, but each vendor has variations, so one doesn’t have
complete portability.
– Applications are written to call the DBMS through SQL commands
• We study this in depth through much of the semester
 SQL Example
• The powerful command
SELECT – FROM – WHERE
retrieves data (rows) from one or more tables of a
relational database that fulfill a search condition
• Example 1:
SELECT SuppID, Phone
FROM Supplier
WHERE Name=‘Heinz’
• Example 2:
SELECT COUNT(*)
FROM Product
WHERE SuppID =01-628703
 Levels of Abstraction
• Many views, single View 1 View 2 View n
logical schema
and physical
schema
– A View describes Logical Schema
how a user sees
the data
– Logical schema
defines the Physical Schema
structure of data
as it is shared
among all users
– Physical schema
describes the files DB
and indexes used
for storage on disk
 Data Independence
• Applications are insulated from how data is structured and stored

• Logical data independence: Protection from changes in logical

schema (eg from introducing an extra column in a table)

• Physical data independence: Protection from changes in physical

structure and location of data

• Data independence is one of the most important benefits of using a

DBMS
 Roles with DBMS
• End users
• DB application programmers
• Database administration (DBA)
• DBMS Vendor Staff

• This course should help you appreciate the

approach and concerns of each of these
groups of people
– So you can work in teams with them
– Also, it can provide a foundation for career
development in each of these directions
 End Users

• End users are are people who do something that

advances the organization’s purpose
• They often are unaware that they are dealing with data
in a DBMS
– They simply run applications that present the data to
them, and allow them to make (controlled) changes
• Categories
– Off-line naïve users who get reports etc
• Eg store manager gets weekly profit report
– Parametric naïve users who execute pre-written
applications
• Eg deli manager who runs application to reorder some item
– Ad hoc users who explore the data
• Eg Division manager looking for trends
 Application Programmers
• IT Professionals who produce the applications
that end users can run
– This usually fits into a broader software development
process, with systems analysts, project managers,
testers, etc
• Programmers need to understand how to create
an application that accesses data through a DBMS
– As well, they need a range of software engineering
skills, including quality assurance, SDLC process, user
interfaces, etc
 DBA
• The Database Administrator is responsible to organization’s
management for effective and efficient use of resources in providing
access to data
– Does day-to-day operations, and makes important decisions
• Example tasks
– Design logical/physical schemas
• Make trade-offs between different choices, to get good performance for all users, at
reasonable cost in hardware and software
– Handle security and authorization
• Set up accounts and permissions
– Data availability, crash recovery
• Make sure backups are taken, and used when needed
– Database tuning
• Monitor performance and adjust parameters or redesign schemas as needed
 DBMS Vendor Staff
• Each vendor (Oracle, IBM, Microsoft etc)
employ staff to help sell more installations, in
particular by helping organizations that have
bought the software
– DBMS Implementors
– Tools Development
– Training Staff
– Sales support staff
– Operational support staff
 A Database without a DBMS?
• Can one have a database without a DBMS to store it in?
• Yes; the information can be stored in files, which are
accessed directly by all the programs that need to use the
data
– Files are good at keeping data for a long time
• This was common for business data in the 1950s, and is still
common in science today
– Eg geologist collects samples, and does analysis of chemical
composition, and produces a file with the information
• See ( Khushi M, JCB, 2015)
– This file can be sent to other scholars as email attachment, or
made available for download
 File vs. DBMS

• Why are DBMSs now the industry-standard approach in business

applications which need to deal with valuable, long-lasting shared data?

• Let’s have an example:

Comparison of an information system implemented on top of the file
system (with C/Java/…) versus a realisation based on a DBMS
– Scenario: Sales system

Data: customers, orders, products, …

Applications: orderings, payments & delivery, marketing,…
Sharing metadata
File vs. DBMS
File System: Database System:
TYPE customer = RECORD CREATE TABLE customers (
cid : INTEGER; cid INTEGER CHECK (cid>0),
name : ARRAY [1..20] OF CHAR; name VARCHAR(20),
address: CHAR; address VARCHAR(50)
END; )
TYPE product = RECORD CREATE TABLE products (
pid INTEGER CHECK (pid>0),
pid : INTEGER; title VARCHAR(30),
title : ARRAY [1..30] of CHAR; weight FLOAT,
weight: REAL; price FLOAT,
price : REAL; stock INTEGER CHECK (stock>=0)
stock : INTEGER; )
END; CREATE TABLE orders (
TYPE order = RECORD pid INTEGER CHECK (pid>0),
cid INTEGER CHECK (cid>0),
pid : INTEGER;
quantity INTEGER,
cid : INTEGER; order_date DATE,
quantity : INTEGER; )
order_date : DATE; Data definitions once in the central data
END; dictionary of a DBMS.
Data definitions repeated in each => Same for all applications
program…
 File vs. DBMS
Programming for access to data
File System: Database System:
VAR o : order;
p : product;
fh1, fh2: file_handle; SELECT pnr, quantity
fh1 = Open(order_file); FROM orders o, products p
WHILE NOT EOF(fh1) DO WHERE [Link] = 10 AND [Link] >= 18
o := getnext(fh1); AND [Link] = [Link]
IF [Link] = 10 AND [Link] >= 18
THEN AND [Link]<100
fh2 = Open(product_file);
WHILE NOT eof(fh2) DO
p := getnext(fh2);
IF [Link]=[Link] AND
[Link]<100 THEN
WriteCard([Link],
[Link]);
END;
END;
close(fh2):
END;
END; close(fh1); Declarative queries
You have to program it by hand, - easy to read and maintain
over and over and over … - usable from different applications
- efficient evaluation due to
automatic optimization
 File vs. DBMS
Ensuring integrity

1. Product IDs and product titles should be unique.

File System: Database System:

Possible violation of data CREATE TABLE products (
integrity due to incorrect input pid INTEGER CHECK (pid>0)
PRIMARY KEY,
or missing synchronisation title VARCHAR(30) UNIQUE,
between different programs… …)
⇒ No integrity violation possible
2. There must be a corresponding product for each order.
File System:
Violation of integrity Database System:
constraints possible, if a CREATE TABLE orders (
record is deleted from the file …,
with the products without FOREIGN KEY(pid) REFERENCES
products ON DELETE NO ACTION)
checking the orders first. Data integrity ensured.
 File vs. DBMS
Evolution of the system?

If you find you also need to know the phone number of each customer

• File System: rewrite every • DBMS: rewrite one table definition;

probably don’t need to change the
program that reads customer queries that access customer, unless
records they need to use the phone number
You need to keep track of which
programs use which files, and You only have to change the
description in the database, not in the
modify all those programs when
programs that use the data
the file structure changes
 File vs. DBMS
Access control
File System: Database System:
provides only a coarse granularity declarative access control
for access control:
- files
with regard to individual
- on many systems, just three database users and user
levels of control: owner / group / groups; views
world CREATE VIEW ordersSYD AS
SELECT * FROM orders
WHERE cid IN ( SELECT
cid FROM customers
WHERE city = ‘Sydney’)
GRANT SELECT ON ordersSYD
TO agentSmith
 Comparison

• Drawbacks of using file systems to store data:

– Data redundancy and inconsistency
• Multiple file formats, duplication of information in different files
– Difficulty in accessing data
• Need to write a new program to carry out each new task
– No central authority: different programmers might want different choices of
files and formats, and there is no easy way to enforce organizational control
over the valuable data
– Integrity problems
• Integrity constraints (e.g. account balance > 0) become part of program code
• Hard to add new constraints or change existing ones
 Comparison
• Drawbacks of using file systems (cont.)
– Atomicity of updates

• either all occur, or nothing occurs

• Failures may leave database in an inconsistent state with partial updates carried out
• E.g. transfer of funds from one account to another should either complete or not happen at all
– Concurrent access by multiple users
• Concurrent accessed needed for performance
• Uncontrolled concurrent accesses can lead to inconsistencies
– E.g. two people reading a balance and updating it at the same time
– Security problems
 Database systems offer solutions to all the above problems
 But, this comes at a price in performance for simple processing
 And also in money, for commercial platforms
 Finally, DBMSs are targeted at the most common sorts of information; they often work poorly
on specialized data (eg gene sequences, images, etc)
 Why organizations use a DBMS?

• Data independence and efficient access

• Reduced application development time

• Data integrity and security

• Uniform data administration

• Concurrent access, recovery from crashes

 Relational Data Model

• The relational model is the most common in today’s

DBMSs
• It does very well for representing typical organizational
data, such as employees and the names and salaries,
products and their descriptions and quantity-in-stock,
etc
– Many facts of each kind, but each kind is simple (a few
strings, integers etc)
• It does not do so well for richer information (images,
designs, time series, instructions, etc)
– There is a very active research community looking at other
data models
 Some big ideas
• There are some powerful ideas that we will see
during the semester; they shape the way data
management is done
– These also apply (with variations) to non-relational
data storage platforms
• Understanding these is a valuable outcome from
this unit, in any career where one deals with
important shared data
– Even if one is working with data in files, try to adopt
these ideas as much as possible
 Meta-data as data
• The schema, and other meta-data, is essential to
working with the data
– Data is useless if one can’t interprete it
– Eg Alan Fekete,162,47,447
• Is 162 weight in pounds, salary in $/hrs, height in cms, room
number, or something else?
– Over time, the people may leave who know what a
value or string means
• Meta-data should be stored with the data it
describes
– And there should be some facilities for asking about
and updating the meta-data
 “What” not “how”
• It is convenient to indicate declaratively what
information is needed, and leave it to the system
to work out how to process through the data to
extract what you need
– Programming is hard, and choosing between different
computations is hard
• Users should be offered a way to express their
requests declaratively
– A query language can be based on logic
– Select…where…
 Administrative services
• The performance that users get, and the security
of the whole system, can be greatly affected by
the way the data is stored, and the settings of
system configurations
• What helps one user may be bad for others
– But some changes have wide benefits
• The needs of the whole organization can be
considered by people who are responsible to the
whole, rather than to individuals or subsets
• A system should offer an interface for
administrators to adjust parameters, structures
etc
– And one for users to provide hints that can help the
administrators
 Summary
• How information systems are built today: a DBMS is used to
maintain and query large datasets, that are shared among many
application programs
– Benefits include recovery from system crashes, concurrent access,
quick application development, data integrity and security
– Data independence
• Overview of the relational model
– Most important ideas and terminology
• The main roles of professionals who need to know about DBMS
• Some big ideas
– Store the schema
– Declarative queries
– System Administration
 References
• UW(3ed) Ch 1.1, 2.1, 2.2
– Missing: comparison with file-based info system,
roles of workers
• KBL(2ed) Ch 1.1-1.3, 2.1, 2.2, 3.1, 3.2
– Missing: comparison with file-based info system
• RG(3ed) Ch 1.1-1.6, 1.9, 3.1
• SKS(6ed) Ch 1.1-1.7, 1.12