Get documents about "ICS week 10b
Document Sample
Introduction to Computer Systems
Lecturer: Steve Maybank
Department of Computer Science and Information Systems
sjmaybank@dcs.bbk.ac.uk
Autumn 2011
Week 10b: Relational Databases
6th December 2011 Birkbeck College, U. London 1
Definition of a Database
A collection of data with internal links
that make the information accessible
from a variety of perspectives.
6th December 2011 Brookshear, Section 9.1 2
Different Perspectives
In an employee database, list
All employees in personnel
All job titles in Accounts
All employees under 30
All departments with 50 or more
employees
…
6th December 2011 Brookshear, Section 9.2 3
Large Databases
World Data Centre for Climate 220 TB web 1PB=1015 Bytes
data+6 PB = 0.15MB for
every person on
National Energy Research 2.8 PB Earth
Scientific Computing Centre
ChoicePoint (data on US citizens) 250 TB
In future: high
AT&T (telecoms) 323 TB resolution film
Google > 200 TB ? archive, 8 TB/film
YouTube 45 TB of videos
Amazon > 42 TB
See http://www.focus.com/fyi/10-largest-databases-in-the-world/
6th December 2011 Birkbeck College 4
Database Model
User’s concept of the database.
Eg. in a relational database the user
sees all the data in tables.
The actual storage and management of
the data may not correspond to the
database model.
6th December 2011 Brookshear, Section 9.1 5
Layers in a Database Implementation
Applications Actual
User DBMS
Software data
Applications Software: user interface, including
implementation of the database model.
Database Management System: updates the
database and supplies data to the applications
software.
6th December 2011 Brookshear, Section 9.1 6
Advantages of Splitting AS from DBMS
The user sees only the database model. The true
complexity of the database is hidden.
The DBMS can be changed without affecting users.
The AS can be tailored for different groups of users.
Easier access control because all access is through a
single DBMS.
7th December 2010 Brookshire, Section 9.1 7
Database Consisting of a Single File
Order Customer Customer Price Due Product
num name address date
37 Sperry 1 The Lane £1000 1.7.06 Plate
43 Univac 15 Retail Road £2000 5.5.06 Case
20 Honeywell 205 North £3400 2.4.06 Panel
Street
If a customer has no orders, then all information about that
customer is lost.
6th December 2011 cf. Brookshear, Section 9.1 8
Relational Model
Customer Name Address
Table Num
heading
102 Sperry 1 The Lane
Table 103 Univac 15 Retail Road
54 Honeywell 205 North Street
All data is organised into rectangular tables called relations.
Each row (or tuple) is a single data item.
Each column is an attribute, taking values in a specified
domain.
The table heading is not a row in the table.
6th December 2011 Brookshear, Section 9.2 9
Advantages and Disadvantages
Advantages:
When a table is updated, the information in the other
tables is retained.
Information is not duplicated.
Disadvantage:
Once the relations are established they cannot be
easily changed.
6th December 2011 Brookshear, Section 9.2 10
Relational Database
Order Customer Price Due Product Customer Name Address
Num Num date Num
102 Sperry 1 The Lane
37 102 £1000 1.7.06 Plate
103 Univac 15 Retail
43 103 £2000 5.5.06 Case Road
20 54 £3400 2.4.06 Panel 54 Honeywell 205 North
Street
Order file
Customer file
What is the address of Sperry?
What is the total value of the orders placed with Honeywell?
How many different products are there on order?
How many different products are there?
6th December 2011 Birkbeck College 11
Table Structure
Each table should correspond to a single
concept or task.
Each row of a table should be uniquely
identified by a key.
The table design should avoid multiple copies
of information.
6th December 2011 Brookshear, Section 9.2 12
Keys
Primary key: an attribute whose value
uniquely identifies a tuple.
Composite key: a minimal set of
attributes whose values together
uniquely identify a tuple
Foreign key: set of attributes pointing
to a primary key or a composite key in
another table.
6th December 2011 cf. Brookshear, Section 9.2 13
Examples of Keys
Customer Name Address
Order Customer Price Due Product
Num Num date Num
102 Sperry 1 The Lane
37 102 £1000 1.7.06 Plate 103 Univac 15 Retail
Road
43 103 £2000 5.5.06 Case
54 Honeywell 205 North
20 54 £3400 2.4.06 Panel Street
Order file Customer file
Primary keys: Order Num in the Order file,
Customer Num in the Customer file
Foreign key: Customer Num in the Order file
6th December 2011 Birkbeck College 14
Problem
Find the names of all customers who have ordered products
with a total value of £3000 or less.
6th December 2011 Birkbeck College 15
Lossless Decomposition
In lossless decomposition a relation
(table) is decomposed into smaller
relations without loss of information.
Aim: to produce a better table
structure.
6th December 2011 Brookshear, Section 9.2 16
Original Relation
Order Customer Price Due Product Name Address
no. no. date
37 102 £1000 1.7.06 Plate Sperry 1 The
Lane
43 103 £2000 5.5.06 Case Univac 15 Retail
Road
20 54 £3400 2.4.06 Panel Honeywell 205
North
Street
Original table
6th December 2011 cf Brookshear, Section 9.2 17
Example of a Lossless Decomposition
Order Custo Price Due Product Customer Name Address
no. mer date no.
no.
102 Sperry 1 The
37 102 £1000 1.7.06 Plate Lane
103 Univac 15 Retail
43 103 £2000 5.5.06 Case Road
54 Honeywell 205
20 54 £3400 2.4.06 Panel North
Street
Order file Customer file
6th December 2011 Brookshear, Section 9.2 18
Original Relation
Empl Job Dept
Id Title
203 Assistant Finance
17 Manager Finance
18 Manager Planning
6th December 2011 Brookshear, Section 9.2 19
Example of a Lossy Decomposition
Empl Job Title Job Title Dept
Id
203 Assistant Assistant Finance
17 Manager Manager Finance
18 Manager Manager Planning
6th December 2011 Brookshear, Section 9.2 20
Problem 2
Name Department Tel.No.
What is the difference in the
information supplied by the single Jones Sales 555-2222
relation Smith Sales 555-3333
Baker Personnel 555-4444
and the two relations
Name Department Department Tel.No.
Jones Sales Sales 555-2222
Smith Sales Sales 555-3333
Baker Personnel Personnel 555-4444
6th December 2011 Brookshear, Ch. 9, Problem 28 21
Problem 3
Let R(X,Y,Z) be a relation with
attributes X, Y, Z, and let R(X,Y) and
R(Y,Z) be relations obtained by
decomposing R(X,Y,Z).
Prove that if Y is a key for R(X,Y), then
the decomposition is lossless.
See S. Sumathi and S. Esakkirajan, Fundamentals of relational
database management systems.
6th December 2011 Birkbeck College 22
