Transcript database - Teach ICT

RDBMS
(An Introduction)
FatMax 2007. Licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License
Why Relational Databases?

Data is stored in a database so that it
can be sorted and searched to provide
meaningful information

What are the advantages of RDBMS
over flat file systems?
FatMax 2007. Licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License
Flat File Problems

Recall the main problems of flat file databases
•
•
•
•

Redundancy
Inconsistency
Difficult to update
Program/data dependence
The aim of using a RDBMS is to eliminate (as
far as possible) these problems
FatMax 2007. Licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License
RDBMS - Entities

A relational database consists of multiple
tables joined (linked) together by relationships

Each table should contain data about a single
entity
•
•
•
Person – Employee
Object - Book
Event - Project
FatMax 2007. Licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License
Relationships

Entities (tables) are joined together by relationships

Usually joined on
primary/foreign
key

Relationship
have a degree
(i.e. there are
different types):
FatMax 2007. Licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License
Relation Types

3 degrees (types) of relationship
One-to-One
Teacher/Form Group
One-to-Many
Doctor/Patient
Many-to-Many
Students/Classes
FatMax 2007. Licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License
Drawing Relationships

Entity-Relationship Diagram
•
Shows the type of and nature of relationship
between entities in the system
Artist
Albums
FatMax 2007. Licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License
Worked Example 1

Look at the iTunes database on the next slide

Draw an E-RD of the entities you would expect
to see if this was converted into a relational
database
HINT: There are 4 entities. All relationships are one-to-many
FatMax 2007. Licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License
Worked Example 2
FatMax 2007. Licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License
Worked Example 3

Did you get something like this?
Artists
Albums
Tracks
Genres
FatMax 2007. Licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License
RDBMS - Attributes


Attributes describe
the properties of
an entity
It’s easiest to think
of attributes as
fields
FatMax 2007. Licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License
Database Notation

When documenting databases (e.g. for coursework)
use standard database notation
•
•
•
The name of the table (Entity) appears in upper case
The fields (attributes) are shown in brackets
The key field is underlined
COURSE_VENUE (Venue_ID, Venue_Name, Address, Town, County, Telephone, Postcode)
FatMax 2007. Licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License
Database Architecture

WYSINWYG
• What you see is NOT what you get!!!
• The DBMS is an application program that provides an interface
between the data and the user in order to make access to the
data stored as simple as possible.
•
The user interface is
not necessarily a true
reflection of how data
is stored!
FatMax 2007. Licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License
Three Level Architecture

The DBMS is an application program that provides an
interface between the data and the user in order to make
access to the data stored as simple as possible.
EXTERNAL / USER SCHEMA
Schema are ‘views’ – they describe
the structure and how a database
can be accessed.
CONCEPTUAL / LOGICAL SCHEMA
INTERNAL / STORAGE SCHEMA
There are three main schema, which
allow DBMS administrators to look at
the structure and design of a
database in different ways.
FatMax 2007. Licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License
Three Level Architecture 2
EXTERNAL /
USER
This is the individual’s view of the
database – in multi-user databases, there
will be several different external schema
giving different people different access
rights.
CONCEPTUAL /
LOGICAL
This is the overall view of the entire
database, including entities, attributes
and relationships as designed by the
database administrator.
INTERNAL /
STORAGE
How the data will be stored and is
concerned with file organisation and
access methods.
FatMax 2007. Licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License
Internal Storage


In the internal/storage schema data
about each entity is stored only once in
its entirety
Therefore:
• Less duplication (redundancy)
• No inconsistencies across duplicates
•
•
(because there aren’t any duplicates!!!!)
Less disk space required
Easier to update
FatMax 2007. Licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License
Research/Tasks

Make notes from your textbook/web sources about
the benefits of RDBMS over flat files
Complete the revision diagram at
http://fatmax.info/moodle/mod/resource/view.php?id=31

Disclaimer: The slides on database architecture come directly from a presentation
by R. Cawley and are © R. Cawley. If you choose to alter this presentation you
MUST leave this copyright notice intact.
FatMax 2007. Licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License