Transcript referential integrity

INFO1408
Database Design Concepts
Week 15: Introduction to
Database Management Systems
• This lecture:
– Why do so many organisations use
databases?
• Next week:
– Special features of databases
• Exam:
– You need to know and understand these two
lectures for the Exam in MAY.
2
• Managing a database system is complex
• Fortunately this is handled by software
known as a Database Management
System ( DBMS).
3
Some important features of a
DBMS
• Moves data to and from physical data files
• Manages multiple users - sharing data
• Manages transactions as ‘all or nothing’ units of
work
• Supports a Query Language to retrieve data
from the database
• Manages back ups
• Provides security.
4
The three tier architecture model
for databases
• Makes it easier for people to understand the
database structure
• The database is considered as three levels or
tiers
• As database designers we are interested in the
top two tiers. We know what the bottom tier does
but we don’t need to know how it works.
5
External
layer
Logical
layer
Physical
layer
What the user sees
(screens and reports)
How the database
organises the data for
people to view
How the computer’s
operating system stores
the data
(on disk)
6
IMPORTANT DIAGRAM - LEARN IT!
External
layer
User
View 2
User
View 1
User
View ….
Logical Data
independence
Internal
Schema
Logical
layer
Physical Data
independence
Physical
layer
Database
File
Database
File
Database
File
Database
File
Database
File
7
Physical Layer
• The files that actually contain the data
• Data could be stored over multiple disc drives –
maximise performance by running in parallel
• The DBMS works with the computer’s operating system
(OS) to efficiently manage the file storage
• Users do not need to know how this physical layer works
- neither do we at this point
• (Microsoft Access is unusual in that everything is stored
in one file – this limits the number of users, so it is not
used for large enterprises).
8
Logical Layer
• The logical layer transforms the physical data
into a common structure
• This ‘abstracted’ format is known as a schema
• We can visualise this as all the table types.
9
External Layer
• This is where users interact with the database
through forms, reports and queries
• The DBMS handles the communication between
users and the computer system through SQL
• Different users can be shown different forms and
reports, depending on what is needed for their
department of the organisation, or their job
• Special ‘views’ can be created so that users only
see what they need (more next week). They will
only know about the parts of the database they
use.
10
Advantages of database
processing
• Getting more information from the same
amount of data
– The data is stored in one place (the database)
and the DBMS displays it to users in helpful
ways
• Sharing Data
– Data can be shared by many users. If one user
changes the address of a customer it is
instantly available to other users. DBMS
prevents possible problems through locking
11
(see next week).
More advantages of database processing
• Users do not have to be physically close to
the data (unlike a paper system)
• Controlling redundancy
– Organisation only needs one copy of each
data item
– Cannot have different versions of one data
item (eg different spellings of a customer’s
name)
• Data is more reliable and less confusing known as data integrity.
12
More advantages of database processing
• Data Independence
– Easy to make changes to the database when
required - programmes that access the
database do not need to know its structure
– Eg we want to add e-mail addresses to the
Customer file:
• in old systems you had to change all the
programmes that used the Customer file - lots of
work
• data independence lets us add a new field to the
Customer table without changing all the
programmes that use it .
13
More advantages of database processing
• Improved security
– Give users permission to:
• Access data
• Change data
– Views can hide things from users.
14
Referential Integrity
• Referential integrity controls the links between
records
• You can set referential integrity when related
fields have the same data type (an
AutoNumber field can be related to a Number
field).
15
How referential integrity works
• You can't enter a value in the foreign key field of a table
if it doesn't exist in the primary key of the related table
– For example, you can't assign an order to a customer
who is not in the Customer table
• You can't delete a record from a table if matching
records exist in a related table
– For example, you can't delete an employee if there
are orders assigned to the employee in the Order
table
• You can't change a primary key value for a record that
has related records
– For example, you can't change an employee's ID in
the Employee table if there are orders assigned to
that employee in the Orders table.
16
Cascade Update and Delete
• For relationships with referential integrity enforced, you
can say whether you want Microsoft Access to
automatically cascade update and cascade delete
related records
• When you try to delete records or update linked primary
key values, Microsoft Access changes the related tables
to allow your updates to go ahead, preserving referential
integrity
• Example of cascade update:
– if you change a customer's ID in the Customer table,
the CustomerID field in the Orders table is
automatically updated for that customer's orders so
that the relationship isn't broken
• Microsoft Access cascades updates without displaying
any message.
17
• Example of cascade delete:
– If you delete a customer from the Customer table, all
the customer's orders are automatically deleted from
the Orders table
– Records in the Order Details table that are related to
the Orders records are also automatically deleted
• When you delete records with the Cascade Delete
Related Records check box selected, Microsoft Access
does not always warn you that the related records will be
deleted too.
18
Disadvantages of Database
Processing
•
•
•
•
Larger file size
Increased complexity
Greater impact of failure
More difficult recovery.
19
Summary
•
•
•
•
•
•
Important features of a DBMS
The three tier architecture
Advantages of database processing
Referential integrity
Cascade Update and Cascade Delete
Disadvantages of database processing.
20