Transcript lecture 1 - introducing databasesx

Relational Databases
INTRODUCTION TO DATABASES
Outline of today’s session
• Welcome to the module
• How the module is organized and assessed
• What YOU need to do to pass this module
• Database Fundamentals
• Summary
MODULE DELIVERY

The module is taught with 1hr Lecture and 1 hr
seminars
•
•
•
•
•
•
You must make notes in lecture
There are NO handouts given in the lectures! If you need them print
them out in advance
You are expected to do additional reading between lectures and it is
assumed that you will have done this
You MAY record the lectures provided you ASK, you may NOT video
the classes & you do not have permission to distribute the
recordings.
Seminars must be attended, if you miss the seminar you WILL fall
behind, most tutors will NOT see you outside of a seminar.
SEMINAR’S ARE MONITORED!
If you do not understand ASK!
ASSESSMENT STRATEGY

This module is assessed by Exam and In-class tests (milestones)

1 exam


4 milestones





There is an exam worth 50% of the module grade taken in seminar slot for
week 12 semester 2
2 in each semester (semester 1 weeks 6 and 12, semester 2 weeks 6 and
11)
Each milestone is worth 12.5%
Taken in seminar slots, if you miss the seminar slot you will be awarded 0 for
that milestone
Assessment marks are available immediately but the feedback is not available
till the week after the assessment.
The assessments are taken online during class time. If any assessment
component is failed then the module may not be progressed and a referral will
be required in the summer.
AIM OF THE MODULE
To give students an understanding of the
nature and use of databases within computing
 Demonstrate that data is not necessarily useful
in its raw from, data must be converted to
information
 Give students sufficient technical skills to
interrogate the raw data and convert it to
information

WHAT IS A DATABASE?

Common understanding of databases by
students starting this module
 MS
Access is a database
 Database programming involved dragging and
dropping fields with a bit of tweaking
 Database are not that relevant to my area of
computing

The aim of this course if to rectify these
misconceptions!
WHAT IS A DATABASE?
“a collection of data that is permanently stored on a computer”

A database must be able to:
•
•
•

Hold different types of data in a collection
Record the relationships between the different items in the
collections
Have a ranges of sizes capacity
We need to be able to:
•
•
•
Insert new data, delete obsolete data & amend existing data in the
collection
Retrieve data in the collection in a variety of ways
Manage the collections so that the data can be permanently stored
and protected from corruption or loss.
WHAT DATA CAN BE HELD?

A data collection (databases) should be able to
hold a range of data types
•
•
•
•
•
•
•
•
Images
Audio files
Text
Numerical
Date
Video
Maps
Etc.
26th August 1972

The theory of the database is not always a reality ...
This course will look at the theory AND the
practical, you must understand the difference
DATABASE MANAGEMENT SYSTEMS (DBMS)



There needs to be a special piece of software to manage
the database if the benefits are to be obtained
VERY complex to write so don’t want to have to write own
programs to do this
Therefore, buy a Database management system (DBMS)





Change the DB contents to keep them up to date
Find and retrieve data from database
Protect data for accidents and intruders
Store in an effective way on the computer
During this course we will be using the Oracle DBMS
(version 11g)
The DBMS is covered in later sessions
WHAT IS A RELATIONAL DATABASE?




In reality databases hold only certain types of data in a true
relational database form
we are looking at the Relational db in this course and
although we need to be aware to the theoretical possibilities
we are mainly focusing on the real world situation.
A relation is just a logical structure that contains related data
(hence the term relation) it is commonly referred to as table
but the correct term is relation (books will use either term)
A relation/table may hold the name, address, date of birth,
enrolment number of a student. All the data in one collection
or row would relate to one student and all the different rows in
the collection would be related to students.
WHAT IS A RELATION/TABLE?
As mentioned, all the data in a database is
held in a cluster of similar data (normally
similar MEANING not DATATYPE)
Name
Address
Esmé
WeatherWax
The cottage,
Lancre
Susan Sto Helit
1 South Hights
Sto Helit
D of B
Customer number
0122522
26/081972
2365565
In the above table, all the data would be the type of data you may find in a
CUSTOMER table, we have the name, address, etc. Note: not all data is
entered, some data may be optional.
First Name
Surname
Location
Job
Marital
Status
Profile
image
Sam
Vines
Ankh-Morpork
Watch
Commander
Married
William
De Worde
Ankh-Morpork
Newspaper
Editor
Single
None
available
Jason
Ogg
Lancre
Blacksmith
Married
None
available
Esmeralda
Weatherwax
Lancre
Witch
Single
Thomas
Silverfish
Ankh-Morpork
Alchemist
Single
None
available
Data can be of different formats or data types in a database, some data though
may be held but not available to have all actions on them for example, a picture
can be added or deleted but not updates (you can not change Esmerelda’s hat for
a bonnet etc) it is more common to find the data above held as .........
Same data but held in an alternative manner
First
Name
Sam
Surname
Location
Job
Vines
AnkhMorpork
William
De Worde AnkhMorpork
Ogg
Lancre
Watch
Command
er
Newspape Single
r Editor
Blacksmit Married
h
Witch
Single
Jason
Esmerald
a
Thomas
Weatherw Lancre
ax
Silverfish AnkhMorpork
Alchemist
Marital
Status
Married
Image location
//pictures/sam.gif
//images/witch/Esmé.jp
g
Single
In the above table we can see that the image has no longer been
embedded into the table, instead the location of the image is displayed,
this data would be used to locate the image if it is needed but the data
itself remains true to the relational principles.
DATABASE TERMS



There are various books or papers that discuss databases, and
it is often confusing to find that 2 different sets of terms are
used, one set RELATIONAL terminology and another SQL
terminology.
Relations and datasets are normally displayed or depicted as
tables so often the term TABLE is used in SQL rather than
RELATION
Tables have columns and rows so most SQL terminology uses
these terms



Table
Column
Row
= Relation
= Attribute
= Tuple
STRUCTURE OF A DATABASE (PAGE 17 OF BOOKLET)
The database is made of tables/relations and relationships.
Student
Programme
Module
3 tables: student hold the student personal data, programme includes data on the
different programme structures and module contains information on the different
modules. The tables each hold data that will stand in isolation, if we need info on the
BIS programme of the Games programme we look in the programme table but also
the tables relate to each other, a student is enrolled on a programme and each
programme has a number of modules associated with it and modules may run on a
number of different programmes
Student
Programme
Module
RELATIONSHIPS
It is important that relationships between data are
clear and understood in order to get the most out
of the database and its data
 There are different types of relationships
1 – many
Many – Many
1–1
 Normally 1 – many are the types of relationships
that we need to foster

TASK (NOT INCLUDED IN YOUR BOOK SO COPY INTO YOUR NOTES!)
A company employs a number of people, the personal details such as name,
address, salary and payroll number etc are held in the works database.
Each employee works for a department and each department is responsible
for a number of different projects.
1.
2.
3.
What are the main clusters of data (this will give you the tables/relation)
What content will be in each of the identified tables/relation (this will give
you the columns/attributes within the table/relation)
Think about how the different data relates to each other
SOLUTION
First name
Surname
Date of Birth
address
postcode
Employee
department
project
Department
number
Department
name
Location
Postal
address
Postal
postcode
project
number
Project
summary
Project
manager
Start date
Completion
date
Employee
number
Note: When you are designing a database you need to identify the attributes/columns
from the existing data or discussions with the client.
•An employee works for one department
•A department has many employees
•A department has many projects
•A project is run by one department
SEMINAR TASKS
In the seminar you are to do Seminar task 1.
 Before next weeks lecture you are expected to
read all the notes in the booklet that relate to
this weeks lecture (up to page 17)
 Next week we are going to look at the
tables/relations in more detail, how they work,
what additional elements we have to include in
addition to the expected attributes and what
limitations they have.
