Transcript Relational databases

2.3 Organising Data for Effective
Retrieval
• Describe the nature and purpose of a relational
database, and how it works
• Understand how data can be retrieved to produce
meaningful information
• Recall the advantages of relational database over
flat-file systems
• Select and justify appropriate file and database
structures for particular applications
Relational Database
• A relational database is a collection of data
items organised as a set of formally-described
tables from which data can be accessed or
reassembled in many different ways without
having to reorganise the database tables
(whatis.com definition)
• Or, in other words, a relational database is a
database that consists of multiple tables joined
together by relationships
Meaningful Information
• Data are stored in databases so that they can
be sorted and searched – you could just store
data in Word!
• You can use the data in the database to derive
meaningful information – for example, there
isn’t any data in a library database to tell us
which books are overdue, but we can create a
query to tell us
• Relational databases also allow things called
aggregate functions that work on groups of
records and can be used for statistical analysis
Advantages of Relational Databases
• Data about each of the entities is only entered
once:
– There is therefore less duplication (also known
as redundancy) of data
– There will be no inconsistencies across the
duplicates (as they won’t exist!)
– Less disc space will be required for storing the
data
• It can actually make searching easier.
Database Management Systems
A databases management system, such as Access
should be able to:
• Store, retrieve and update information in the
database in a transparent manner and display it
to multiple users
• Implement file and/or record locking to prevent
conflicts when data are changed.
• Look after security - at field, table, form, query,
etc., level, to prevent unauthorised access
DBMS continued…
The DBMS will:
• Perform back-up and recovery procedures - e.g.
repairing or copying database structure
• Allow control of the user interface through
forms, reports, buttons and macros
• Queries - “query by example” (e.g. Access
queries) or “query language” (e.g. SQL structured query language)
Standard Database Notation
There is a standard form for describing tables:
• The NAME of the table appears in upper case
• A list of the fields, separated by commas,
appears in brackets after the table name
• The key fields are underlined
For example:
• CAR (registration, make, model, colour)
Relationships
• Tables are joined together by relationships
• A field that is linked to another table is called a
foreign key
• Relationships have a degree:
• One-to-One
• One-to-Many
• Many-to-Many
• We can show these on an entity-relationship
diagram!
Entity-Relationship Diagrams
ER diagrams show entities and relationships:
• The entities are shown as rectangles. They are
named, and will probably represent the tables
you will have in your database
• The relationships are shown as lines, with text
above them to describe the relationship
• The degree of the relationship is indicated by
the end of the line – plain for “1” and forked for
“many”.
Entity-Relationship Diagrams
For example:
Book
ISBN
title
author
publisher
Loan
borrower
book
date taken
date due
date returned
Borrower
borrower_id
forename
surname
address
telephone
date of birth
Entity-Relationship Diagrams
Some people also show whether records are
required:
Book
ISBN
title
author
publisher
Loan
borrower
book
date taken
date due
date returned
Optional
Mandatory
Borrower
borrower_id
forename
surname
address
telephone
date of birth