Transcript Databases - Analysis

Database Systems
A guide to
producing a
fully functional
RDBMS
How Can this Presentation
Help Me?
If you work through this presentation use the terminology
quoted and produce all the diagrams and documentation
mentioned throughout this presentation, you will have
succeeded in meeting most of the requirements necessary
to produce a successful report on the analysis, design and
implementation of a Relational Database.
Do not move on from one
section to the next until you
fully understand and can apply
the concepts described in this
presentation
Contents
Theory and terminology
The Analysis Phase
•
Requirements Definition
•
Functional Dependency & Relationships
•
Normalisation
•
Entity Relationship Diagrams
•
Data Dictionaries & Data Types
•
Functions [Input, Processes & Outputs]
The Design Phase
The Implementation Phase
Some Terminology
• Data –
A collection of raw facts and
figures
• Information – Final product created from
the raw data after processing
• Database – A collection of related
information organised in a
structured way to allow easy
retrieval of any one item
Elements of a database
Table –
A complete set of data. Also
known as an entity or relation
Record – One row of the table
Field –
One column of the table. Also
known as an attribute
Types of Database
Flat file Database – Single table
Relational Database – Multi table
Flat-file Database – Single table
A telephone directory is an
example of a flat file database
Problems with flat-file databases
• Redundant Data
Data is repeated and hence stored many times. This
wastes disk space and slows down query time.
• Typographical Errors
Greater likelihood of errors when data is being entered.
Problems with flat-file databases
• Updating Data
Every occurrence of a piece of data will have to be
updated if its value changes
• Modifying Data
If a record is deleted this may destroy all reference to a
particular piece of data
To overcome these problems it is better to store data
in multiple tables i.e. a Relational database
Characteristics of a
relational database
• Data is stored in a set of tables
• Tables are joined by relational links
• Reduces duplication of data in database
- Normalisation
• Allows greater flexibility and efficiency
Relational Database – Multi table
The data is stored in multiple tables. This is the most
common way of keeping a computerised database
• Each table must have a unique reference for each
record which is called the Primary key
• Replicating these into other tables creates the
Foreign key
• These foreign keys form the Relationships that link
the tables together
Relational Database – Multi table
• Redundant Data
The disk space required to store data is reduced and
queries can be performed more quickly.
• Typographical Errors
As data is only entered once the chances of these errors
occurring are greatly reduced..
Relational Database – Multi table
• Updating Data
Data need only be updated once as it would only have
been entered once
• Modifying Data
Problems of the kind mentioned earlier disappear. New
records will simply have their attributes added to the
relevant table
Database Management System [DBMS]
• DBMS
A computer program or collection of computer programs
that provides the necessary tools to create and
manipulate the data in a database
• RDBMS
Relational Database Management Systems are used to
create and manipulate relational databases e.g.
- Microsoft Access
- Oracle
- Lotus Approach
Database Systems Construct a data model of an
existing, non computerised
system
THE ANALYSIS PHASE
Database Systems
Analysis Phase
•
•
•
•
•
Requirements Definition
Normalisation
Entity-Relationship Diagrams
Data Dictionaries
Functions
Database Systems
Analysis
•
•
•
•
•
Requirements Definition
Normalisation
Entity-Relationship Diagrams
Data Dictionaries
Functions
Requirements Definition
When we normalise any type of database system we
need to know what the data in the system means.
Therefore we either have to talk to the people who
use the existing system or we have to be supplied
with very detailed information about how the
system works and the data stored within it.
The following slide shows an example of some
current data.
Below this are listed some questions that help
establish the requirements of the new system.
Can you think of anything else the analyst might ask?
Requirements Definition
Salesperson
Number
3462
Salesperson
Name
Walters
Sales
Area
West
3593
Dryne
East
Customer
Number
18765
18830
19242
18841
18899
19565
Customer
Name
Delta Systems
A.Levy & Sons
Rainer & Co.
R.W.Flood Inc.
Seward Systems
Stodola's Inc.
Warehouse
Number
4
3
3
2
2
1
Warehouse
Location
Fargo
Bismark
Bismark
Superior
Superior
Plymouth
Sales
Amount
13540
10600
9700
11560
2590
8800
• Does each salesperson have their own sales area?
• Does each customer deal with only one salesperson ?
• Does each customer only receive goods from one warehouse?
The answer to these questions will influence how we structure
the database system.
The answer to these questions and others make up the
Requirements Definition
Database Systems
Analysis
•
•
•
•
•
Requirements Definition
Normalisation
Entity-Relationship Diagrams
Data Dictionaries
Functions
Functional Dependency
Functional dependency means that there must be only a oneto-one dependency for each attribute mapped from a
primary key to that attribute.
It defines a relationship in which the existence of one
entity/attribute is entirely dependent on the existence of
another (one-to-one).
For example, the engine number of a car [Primary Key] can
only have engine capacity as an attribute of that key creating
Functional Dependency
BUT
A particular engine capacity can be associated with the
engine numbers of many makes of car. This does not give rise
to Functional Dependency
Functional Dependency - Practical Example
SALES
Order Number
Acc.No.
Customer
Address
Date
Item
Quantity
Item Price
Total Cost
Order Number is the primary key.
The value for each attribute of SALES,
except Item Price, depends upon the value
of the primary key.
All attributes of SALES, except Item Price,
are Functionally Dependent on the
primary key
Item Price is Functionally Dependent on the
the attribute Item
Relationships
Having established a definition for the term Functional
Dependency we now need to explore the different types
of relationships that can exist between attributes in
different entities:
There are 3 types of relationships that can exist:
• One to One Relationships
• One to Many Relationships
• Many to Many Relationships
Now lets take a closer look at each of these on the next
slides
Entity Relationships
One-to-one
A blind person owns a guide
dog which is exclusively
theirs.
J. Smith
Rover
B. Jones
Pippa
A. Gray
Patch
Entity Relationships
One-to-many
A doctor may have many
patients, but a patient is
assigned to only one doctor.
Grant
Dr Ache
Allan
Mark
Dr Payne
Jane
Amy
Entity Relationships
Many-to-many
A film may have many stars
and each star may act in
many films.
Film X
Star A
Star B
Film Y
Star C
Important Note:
An RDBMS system cannot produce a robust solution
if two or more of the entities have a many to many
relationship
To overcome this problem it is necessary to
introduce another entity thus creating One to
Many relationships between itself and the offending
entities
This can become a common occurrence when you are
working with larger and more complex database
systems
Now you should have the tools and understanding you
require in order to normalise the data in order that a
solution can be produced using an RDBMS [Relational
Database Management System]
Please take your time when working through the next
part of this presentation and revisit it again and
again until you are completely comfortable with the
concepts involved
Normalisation of the data is perhaps the single most
important aspect of developing a robust solution to
any database problem!
Normalisation
• Normalisation is a 'fancy' term for a
set of rules, designed to make sure that
a database is organised in the best way
possible
• This allows the data to be processed
more efficiently and any query to be
processed.
• These rules depend on relationships being
established between the entities to
create a functional dependency between
them.
The normalisation process involves:
• Finding and grouping together all the
entities and their attributes.
• Removing repeating groups of data.
– This is another way of saying that there is no point in
storing a persons' name and details etc. in many
different places within a database.
• Providing unique keys for each entity in the
database system.
– You need to have a way of making sure that when you
delete 'Paul Smith' from the Transactions entity that it
is the correct person.
The Three Major Stages of Normalisation:
• First Normal Form
– 1NF is the first level of normalisation. An entity (table)
is in First Normal form if it contains no repeating
attributes (fields) or groups of attributes.
• Second Normal Form
– An entity is in 2NF if no attribute (not part of the
primary key) is dependent on only part of the primary
key. This only applies to entities with concatenated
primary keys.
• Third Normal Form
– An entity is in 3NF if all attributes are entirely
dependent on the primary key and not on any attribute
that is not part of the primary key.
To produce a set of entities in First Normal
Form (1NF):
• Remove repeating (multiple) groups within the primary
entities (tables) so that each record (row) within the entity
is the same length.
• Repeating groups then become new entities, linked together
by a one-to-many relationship.
• Relationships are created by including a primary key from
one entity as a foreign key in another entity
Charlie’s Baker Shop
Order
No.
7823
Acc.
No.
178
Customer
Address
Date
Item
Daisy's Café
27 Bay Drive,
Cove
16/7
Bakewell
Tart
Danish
Pastry
Apple Pie
Qty.
Item
Total
Price
Cost
20
0.15 12.35
13
0.20
45
0.15
4633
526
Smiths
12 Dee View,
Aberdeen
16/7
Butteries
120
0.20
24.00
2276
167
Sally's
Snacks
3 High Street,
Banchory
17/7
Apple Pie
130
0.15
56.50
Cherry
Pie
Steak Pie
100
0.18
30
0.50
Meringue
Pie
20
0.20
Apple Pie
15
0.15
Danish
Pastry
50
0.20
1788
032
Tasty Bite
17 Wood Place,
Insch
18/7
7.50
Charlie’s Baker Shop
Order
No.
7823
Acc.
No.
178
Customer
Address
Date
Item
Daisy's Café
27 Bay Drive,
Cove
16/7
Bakewell
Tart
Danish
Pastry
Apple Pie
Qty.
Item
Total
Price
Cost
20
0.15 12.35
13
0.20
45
0.15
4633
526
Smiths
12 Dee View,
Aberdeen
16/7
Butteries
120
0.20
24.00
2276
167
Sally's
Snacks
3 High Street,
Banchory
17/7
Apple Pie
130
0.15
56.50
Cherry
Pie
Steak Pie
100
0.18
30
0.50
Meringue
Pie
20
0.20
Apple Pie
15
0.15
Danish
Pastry
50
0.20
1788
032
Tasty Bite
17 Wood Place,
Insch
18/7
7.50
Charlie’s Baker Shop
Order
No.
7823
Acc.
No.
178
Customer
Address
Date
Daisy's Café
27 Bay Drive,
Cove
16/7
Total
Cost
12.35
4633
526
Smiths
12 Dee View,
Aberdeen
16/7
24.00
2276
167
56.50
032
3 High Street,
Banchory
17 Wood Place,
Insch
17/7
1788
Sally's
Snacks
Tasty Bite
18/7
7.50
Order
No.
7823
7823
7823
Item
4633
Butteries
120
0.20
2276
2276
2276
2276
Apple Pie
Cherry Pie
Steak Pie
Meringue Pie
130
100
30
20
0.15
0.18
0.50
0.20
1788
1788
Apple Pie
Danish Pastry
15
50
0.15
0.20
Bakewell Tart
Danish Pastry
Apple Pie
Item
Price
20
0.15
13
0.20
45
0.15
Orders
Qty.
Items Purchased
Charlie’s Baker Shop
Order
No.
7823
Acc.
No.
178
Customer
Address
Date
Daisy's Café
27 Bay Drive,
Cove
16/7
Total
Cost
12.35
4633
526
Smiths
12 Dee View,
Aberdeen
16/7
24.00
2276
167
56.50
032
3 High Street,
Banchory
17 Wood Place,
Insch
17/7
1788
Sally's
Snacks
Tasty Bite
18/7
7.50
Order
No.
7823
7823
7823
Item
4633
Butteries
120
0.20
2276
2276
2276
2276
Apple Pie
Cherry Pie
Steak Pie
Meringue Pie
130
100
30
20
0.15
0.18
0.50
0.20
1788
1788
Apple Pie
Danish Pastry
15
50
0.15
0.20
Bakewell Tart
Danish Pastry
Apple Pie
Item
Price
20
0.15
13
0.20
45
0.15
Orders
Qty.
Items Purchased
Charlie’s Baker Shop
Orders Table:
Order
No.
7823
Acc.
No.
178
Customer
Address
Date
Daisy's Café
27 Bay Drive,
Cove
16/7
Total
Cost
12.35
4633
526
Smiths
12 Dee View,
Aberdeen
16/7
24.00
2276
167
56.50
032
3 High Street,
Banchory
17 Wood Place,
Insch
17/7
1788
Sally's
Snacks
Tasty Bite
18/7
7.50
Order No. can be used to uniquely identify each record and can
therefore be made the primary key.
Orders
(Order No.
Acc. No.
Customer
Address
Date
Total Cost)
Charlie’s Baker Shop
Order
No.
7823
7823
7823
Item
4633
Butteries
120
0.20
2276
2276
2276
2276
Apple Pie
Cherry Pie
Steak Pie
Meringue Pie
130
100
30
20
0.15
0.18
0.50
0.20
1788
1788
Apple Pie
Danish Pastry
15
50
0.15
0.20
Bakewell Tart
Danish Pastry
Apple Pie
Qty.
Item
Price
20
0.15
13
0.20
45
0.15
Items
Purchased
Items Purchased Table:
No one attribute can be used to
uniquely identify a record.
Concatenated key is required
Order No. and Item together can
uniquely identify a record.
(*Order No.
Item
Quantity
Item Price)
Charlie’s Baker Shop
First Normal Form
Orders
(Order No.
Acc. No.
Customer
Address
Date
Total Cost)
Items
Purchased
(*Order No.
Item
Quantity
Item Price
The Three Major Stages of Normalisation:
• First Normal Form
– 1NF is the first level of normalisation. An entity (table)
is in First Normal form if it contains no repeating
attributes (fields) or groups of attributes.
• Second Normal Form
– An entity is in 2NF if no attribute (not part of the
primary key) is dependent on only part of the primary
key. This only applies to entries with concatenated
primary keys.
• Third Normal Form
– An entity is in 3NF if all attributes are entirely
dependent on the primary key and not on any attribute
that is not part of the primary key.
To produce a set of entities in Second Normal
Form (2NF):
• Test for dependency by testing each particular attribute in
turn to check that it can be uniquely identified by making
use of all the primary key. This test need not be completed
unless you have at least one table which requires a
concatenated Primary Key
• Remove all partially dependent attributes to a new entity.
N.B. – A concatenated key occurs when you need two
fields together in order to uniquely identify a record
Charlie’s Baker Shop
First Normal Form
Orders
(Order No.
Acc. No.
Customer
Address
Date
Total Cost)
Items
Purchased
(*Order No.
Item
Quantity
Item Price)
Charlie’s Baker Shop
Orders Table:
Order
No.
7823
Acc.
No.
178
Customer
Address
Date
Daisy's Café
27 Bay Drive,
Cove
16/7
Total
Cost
12.35
4633
526
Smiths
12 Dee View,
Aberdeen
16/7
24.00
2276
167
56.50
032
3 High Street,
Banchory
17 Wood Place,
Insch
17/7
1788
Sally's
Snacks
Tasty Bite
18/7
7.50
Because this entity has a single attribute as the primary key
there can be no partial dependencies and therefore the entity
is already in 2NF.
Charlie’s Baker Shop
(2NF Step 1)
Order
No.
7823
7823
7823
Item
4633
Butteries
120
0.20
2276
2276
2276
2276
Apple Pie
Cherry Pie
Steak Pie
Meringue Pie
130
100
30
20
0.15
0.18
0.50
0.20
1788
1788
Apple Pie
Danish Pastry
15
50
0.15
0.20
Bakewell Tart
Danish Pastry
Apple Pie
Primary Key
Qty.
Item
Price
20
0.15
13
0.20
45
0.15
Attribute
Items Purchased Table:
Primary key is Order No. and Item
Test for dependency by testing each
particular attribute.
Functionally Dependent?
Charlie’s Baker Shop
(2NF Step 1)
Order
No.
7823
7823
7823
Item
4633
Butteries
120
0.20
2276
2276
2276
2276
Apple Pie
Cherry Pie
Steak Pie
Meringue Pie
130
100
30
20
0.15
0.18
0.50
0.20
1788
1788
Apple Pie
Danish Pastry
15
50
0.15
0.20
Bakewell Tart
Danish Pastry
Apple Pie
Qty.
Item
Price
20
0.15
13
0.20
45
0.15
Items Purchased Table:
Primary key is Order No. and Item
Test for dependency by testing each
particular attribute.
Primary Key
Attribute
Functionally Dependent?
Order No
Item
Quantity
YES
Quantity is functionally dependent
on Order No. and Item.
Charlie’s Baker Shop
(2NF Step 1)
Order
No.
7823
7823
7823
Item
4633
Butteries
120
0.20
2276
2276
2276
2276
Apple Pie
Cherry Pie
Steak Pie
Meringue Pie
130
100
30
20
0.15
0.18
0.50
0.20
1788
1788
Apple Pie
Danish Pastry
15
50
0.15
0.20
Bakewell Tart
Danish Pastry
Apple Pie
Qty.
Item
Price
20
0.15
13
0.20
45
0.15
Items Purchased Table:
Primary key is Order No. and Item
Test for dependency by testing each
particular attribute.
Primary Key
Attribute
Functionally Dependent?
Order No
Item
Quantity
YES
Order No
Item
Item Price
NO
Quantity is functionally dependent
on Order No. and Item.
Item price is functionally dependent
Item, but not on Order No. and Item
Charlie’s Baker Shop
(2NF Step 2)
Remove any partially dependent attributes to a new entity
Order
No.
7823
7823
7823
Item
4633
Butteries
120
0.20
0.20
2276
2276
2276
2276
Apple Pie
Cherry Pie
Steak Pie
Meringue Pie
130
100
30
20
0.15
0.18
0.50
0.20
0.15
0.18
0.50
0.20
1788
1788
Apple Pie
Danish Pastry
15
50
0.15
0.20
0.15
0.20
Bakewell Tart
Danish Pastry
Apple Pie
Qty.
Item
Price
20
0.15
13
0.20
45
0.15
Item
Price
0.15
0.20
0.15
Charlie’s Baker Shop
(2NF Step 2)
Remove any partially dependent attributes to a new entity
Order
No.
7823
7823
7823
Item
4633
Butteries
120
2276
2276
2276
2276
Apple Pie
Cherry Pie
Steak Pie
Meringue Pie
130
100
30
20
1788
1788
Apple Pie
Danish Pastry
15
50
Bakewell Tart
Danish Pastry
Apple Pie
Part Order
Qty.
20
13
45
Item
Price
0.15
0.15
0.20
0.18
0.20
0.20
0.50
Price List
Charlie’s Baker Shop
(2NF Step 2)
Create a relationship between the tables
and assign Primary Keys
Order
No.
7823
7823
7823
Item
4633
Butteries
120
2276
2276
2276
2276
Apple Pie
Cherry Pie
Steak Pie
Meringue Pie
130
100
30
20
1788
1788
Apple Pie
Danish Pastry
15
50
Bakewell Tart
Danish Pastry
Apple Pie
Qty.
20
13
45
Part Order
Primary Key: Order No.
and *Item
Item
Apple Pie
Bakewell Tart
Butteries
Cherry Pie
Danish Pastry
Meringue Pie
Steak Pie
Item
Price
0.15
0.15
0.20
0.18
0.20
0.20
0.50
Price List
Primary Key: Item
Charlie’s Baker Shop
Second Normal Form
Orders
(Order No.
Acc. No.
Customer
Address
Date
Total Cost)
Price List
(Item
Item Price)
Part Order
(*Order No.
*Item
Quantity)
The Three Major Stages of Normalisation:
• First Normal Form
– 1NF is the first level of normalisation. An entity (table)
is in First Normal form if it contains no repeating
attributes (fields) or groups of attributes.
• Second Normal Form
– An entity is in 2NF if no attribute (not part of the
primary key) is dependent on only part of the primary
key. This only applies to entries with concatenated
primary keys.
• Third Normal Form
– An entity is in 3NF if all attributes are entirely
dependent on the primary key and not on any attribute
that is not part of the primary key.
To produce a set of entities in Third Normal
Form (3NF):
• Test each attribute in turn to check for dependency on the
primary key.
• Remove all transitive dependencies to a new entity.
– A transitive dependency is where an attribute is
dependent on another attribute (or attributes) that is
(are) NOT the primary key
Charlie’s Baker Shop
Second Normal Form
Orders
(Order No.
Acc. No.
Customer
Address
Date
Total Cost)
Price List
(Item
Item Price)
Part Order
(*Order No.
*Item
Quantity)
Charlie’s Baker Shop - 3NF Step1
Test for
dependency
Primary Key Attribute
Order
No.
7823
Acc.
No.
178
Customer
Address
Date
Daisy's Café
27 Bay Drive,
Cove
16/7
Total
Cost
12.35
4633
526
Smiths
12 Dee View,
Aberdeen
16/7
24.00
2276
167
56.50
032
3 High Street,
Banchory
17 Wood Place,
Insch
17/7
1788
Sally's
Snacks
Tasty Bite
18/7
7.50
Trasnsitive Dependency
Charlie’s Baker Shop - 3NF Step1
Test for
dependency
Order
No.
7823
Acc.
No.
178
Customer
Address
Date
Daisy's Café
27 Bay Drive,
Cove
16/7
Total
Cost
12.35
4633
526
Smiths
12 Dee View,
Aberdeen
16/7
24.00
2276
167
56.50
032
3 High Street,
Banchory
17 Wood Place,
Insch
17/7
1788
Sally's
Snacks
Tasty Bite
18/7
7.50
Primary Key Attribute
Trasnsitive Dependency
Order No.
YES:
Acc.No.
Acc.No can be found if we know either
Customer or Address
Charlie’s Baker Shop - 3NF Step1
Test for
dependency
Order
No.
7823
Acc.
No.
178
Customer
Address
Date
Daisy's Café
27 Bay Drive,
Cove
16/7
Total
Cost
12.35
4633
526
Smiths
12 Dee View,
Aberdeen
16/7
24.00
2276
167
56.50
032
3 High Street,
Banchory
17 Wood Place,
Insch
17/7
1788
Sally's
Snacks
Tasty Bite
18/7
7.50
Primary Key Attribute
Trasnsitive Dependency
Order No.
Acc.No.
YES:
Customer can be found if we know either
Acc.No. or Address
Order No.
Customer
YES
Customer can be found if we know either
Acc.No. or Address
Charlie’s Baker Shop - 3NF Step1
Test for
dependency
Order
No.
7823
Acc.
No.
178
Customer
Address
Date
Daisy's Café
27 Bay Drive,
Cove
16/7
Total
Cost
12.35
4633
526
Smiths
12 Dee View,
Aberdeen
16/7
24.00
2276
167
56.50
032
3 High Street,
Banchory
17 Wood Place,
Insch
17/7
1788
Sally's
Snacks
Tasty Bite
18/7
7.50
Primary Key Attribute
Trasnsitive Dependency
Order No.
Acc.No.
YES:
Customer can be found if we know either
Acc.No. or Address
Order No.
Customer
YES
Acc.No. can be found if we know either
Customer or Address
Order No.
Address
YES
Address can be found if we know either
Customer or Acc.No
Charlie’s Baker Shop - 3NF Step1
Test for
dependency
Order
No.
7823
Acc.
No.
178
Customer
Address
Date
Daisy's Café
27 Bay Drive,
Cove
16/7
Total
Cost
12.35
4633
526
Smiths
12 Dee View,
Aberdeen
16/7
24.00
2276
167
56.50
032
3 High Street,
Banchory
17 Wood Place,
Insch
17/7
1788
Sally's
Snacks
Tasty Bite
18/7
7.50
Primary Key Attribute
Trasnsitive Dependency
Order No.
Acc.No.
YES:
Customer can be found if we know either
Acc.No. or Address
Order No.
Customer
YES
Acc.No. can be found if we know either
Customer or Address
Order No.
Address
YES
Address can be found if we know either
Customer or Acc.No
Order No.
Date
NO
Dependent on Order No.
Charlie’s Baker Shop - 3NF Step1
Test for
dependency
Order
No.
7823
Acc.
No.
178
Customer
Address
Date
Daisy's Café
27 Bay Drive,
Cove
16/7
Total
Cost
12.35
4633
526
Smiths
12 Dee View,
Aberdeen
16/7
24.00
2276
167
56.50
032
3 High Street,
Banchory
17 Wood Place,
Insch
17/7
1788
Sally's
Snacks
Tasty Bite
18/7
7.50
Primary Key Attribute
Trasnsitive Dependency
Order No.
Acc.No.
YES: Customer can be found if we know either
Acc.No. or Address
Order No.
Customer
YES
Acc.No. can be found if we know either
Customer or Address
Order No.
Address
YES
Address can be found if we know either
Customer or Acc.No
Order No.
Date
NO
Dependent on Order No.
Order No.
Total Cost
NO
Dependent on Order No.
Charlie’s Baker Shop - 3NF Step2
Remove transitive dependencies to a new entity
Order
No.
7823
Acc.
No.
178
Customer
Address
Date
Daisy's Café
27 Bay Drive,
Cove
16/7
Total
Cost
12.35
4633
526
Smiths
12 Dee View,
Aberdeen
16/7
24.00
2276
167
Sally's
Snacks
3 High Street,
Banchory
17/7
56.50
1788
032
Tasty Bite
17 Wood Place,
Insch
18/7
7.50
Acc.
No.
178
Customer
Address
Daisy's Café
27 Bay Drive,
Cove
526
Smiths
12 Dee View,
Aberdeen
167
Sally's
Snacks
3 High Street,
Banchory
032
Tasty Bite
17 Wood Place,
Insch
Charlie’s Baker Shop - 3NF Step2
Remove transitive dependencies to a new entity
Orders
Order
No.
7823
Date
16/7
Total
Cost
12.35
4633
16/7
24.00
2276
17/7
56.50
1788
18/7
7.50
Customers
Acc.
No.
178
Customer
Address
Daisy's Café
27 Bay Drive,
Cove
526
Smiths
12 Dee View,
Aberdeen
167
Sally's
Snacks
3 High Street,
Banchory
032
Tasty Bite
17 Wood Place,
Insch
Charlie’s Baker Shop - 3NF Step2
Create a relationship between the tables
and assign Primary Keys
Orders
Customers
Order
No.
7823
Acc.
No.
178
Date
16/7
Total
Cost
12.35
4633
526
16/7
24.00
2276
167
17/7
56.50
1788
032
18/7
7.50
Primary Key:
Order No.
Acc.
No.
178
Customer
Address
Daisy's Café
27 Bay Drive,
Cove
526
Smiths
12 Dee View,
Aberdeen
167
Sally's
Snacks
3 High Street,
Banchory
032
Tasty Bite
17 Wood Place,
Insch
Primary Key:
Acc.No.
Charlie’s Baker Shop
Third Normal Form
Customers
Price List
(Acc. No.
Customer
Address)
(Item
Item Price)
Orders
(Order No.
*Acc. No.
Date
Total Cost)
Part Order
(*Order No.
*Item
Quantity)
Normalisation Complete
Normalisation 1. Remove repeating groups to create a new entity
2. Create a relationship using one of the attributes
that are left [Usually the primary key]
3.‘Check out’ entities with concatenated keys. If any
attribute is not fully dependent on both parts of the
primary key remove it to create a new entity.
4. Create a relationship using one of the attributes
that are left [Usually the primary key]
5.‘Check out’ every entity. If any attribute is
dependent on any attribute other than the primary
key, remove it into a new entity.
6. Create a relationship using one of the attributes
Database Systems
Analysis
•
•
•
•
•
Requirements Definition
Normalisation
Entity-Relationship Diagrams
Data Dictionaries
Functions
Entity - Relationship Diagrams
• E-R diagrams show the structure of the data to be
modelled.
• They are a convenient method for representing
the relationships, entities and attributes in a
system.
• This is done by illustrating a system with a
diagram to produce a detailed specification for
each entity and the attributes within them.
Symbols used in an E – R diagram
ENTITY
Entity – a collection
of data [table]
ATTRIBUTE
Attribute – a data item
in an entity
[field]
Symbols used in an E – R diagram
KEY ATTRIBUTE
RELATIONSHIP
Key Attribute – a data item
which makes up the
primary key or part of it.
Relationship – a
description of the
link between two
entities.
Simple Examples
1. A blind person owns a guide dog
Blind Person
1
owns
1
Guide Dog
2. A doctor treats many patients
Doctor
1
treats
M
Patients
Simple Examples
3. A film may have many stars and
each star may be in many films.
Film
M
has
N
Stars
Charlie’s Baker Shop
Third Normal Form
Customers
Price List
(Acc. No.
Customer
Address)
(Item
Item Price)
Orders
(Order No.
*Acc. No.
Date
Total Cost)
Part Order
(*Order No.
*Item
Quantity)
1. Determine the relationships between the
entities.
Customers
1
places
M
Orders
1
Charlie’s
Baker Shop
contains
M
Price List
1
has
M
Part Order
Charlie’s Baker Shop
Third Normal Form
Customers
Price List
(Acc. No.
Customer
Address)
(Item
Item Price)
Orders
(Order No.
*Acc. No.
Date
Total Cost)
Part Order
(*Order No.
*Item
Quantity)
Order No
2. List the attributes alongside
*Acc. No
Total Cost
each entity
Customers
1
places
M
Acc.No
Address
Price List
Item
Item Price
1
Charlie’s
Baker Shop
Customer
1
has
Orders
Date
contains
M
M
Part Order
*Order No
*Item
Quantity
Database Systems
Analysis
•
•
•
•
•
Requirements Definition
Normalisation
Entity-Relationship Diagrams
Data Dictionaries
Functions
Data Dictionary
• A data dictionary is a database about a
database and is often called metadata [data about
data]
• It is created at the analysis stage to provide a
record of the attributes and their properties:
- Data type
- Required [or not] I.e. Can this field be left
blank ?
- Range
- Format
• When the database is up and running it acts as
a useful reference
Data Types
Text
A text attribute only stores letters from the
standard 26 character alphabet
Number
Used to hold numeric data. Remember to show the
range of numbers to be stored [ Use common sense]
Text and Number
Used for alphanumeric data e.g. An address that
may contain a house number and street name.
Data Types
Date
Dates can be laid out using various formats. Two
common formats are short (dd/mm/yy) or
(dd/mm/yyyy) and long (month day, year)
Time
Various formats can be used here. A time of
day, a duration, 24-Hr clock, am/pm etc.
Boolean
These are binary values only having two states.
True/false, yes/no or off/on.
Data Types
The data types and formats used at this
stage will be general and will be mapped
on to those available in the RDBMS
[Relational Database Management System]
in the design phase.
Data Dictionary for Charlie’s
Baker Shop
Entity
Orders
Attribute
Order No
Acc.No
Date
Total Cost
Customers
Price List
Part Order
Description
Unique Identifier. Numbers in the range 199999 required
Unique Identifier. Numbers in the range 199999 required
Short Form dd/mm/yy
Acc.No
Cost of total order; Value up to £9999.99
required.
Unique Identifier. See Orders entity
Customer
Company Name required; text only
Address
All address details for customer incl postcode
required. Text & Numbers
Item
Item Price
Unique Identifier. Numbers in the range 19999required
Cost of item; Value up to £99.99 required
Order No
Unique Identifier. See Orders entity
Item
Unique Identifier. See Item entity
Quantity
Numbers in the range 1-999 required
Database Systems
Analysis
•
•
•
•
•
Requirements Definition
Normalisation
Entity-Relationship Diagrams
Data Dictionaries
Functions
Functions
A function is anything that acts on the
data in a database system and is classified
as input, process or output.
• Input
An input is a function that places new data in the
system such as creating, updating or deleting data
• Process
An automated function within the database system
e.g. automatic update of an attribute, checking the
particular value of an attribute, changing or
calculating new values from the data
Functions
• Output
A function that produces data from the
database system e.g. a report, letter or
summary of the data
Beyond these Operational functions modern
computerised database systems can provide a wide
range of Strategic and Tactical information which
can assist with planning and organising e.g. queries
and ordered lists can provide the Company with
invaluable marketing information
Listed Functions that affect each entity of
the Charlie’s Baker Shop database
Entity
Orders
Customers
Price List
Part
Order
Input
Process
List all orders
Add a new order.
Update an order.
Add a new customer
Update a customers
details
Delete a customers
details
Add a new item
Delete an item
Add quantity of an
item ordered
Amend the quantity of
an item ordered
Output
List all customers
with outstanding
orders
Automatically
update total cost
of an order
List items and
corresponding
prices
List all orders
containing a
particular item
List all customers
ordering a
particular item
Database Systems Design database structures
to represent the data
model
THE DESIGN PHASE
Database Systems-Design Phase





Database Structure and item names
relate to the data model
Unique keys are chosen for all
Database structures
Validity checks represent the data
model
Data item characteristics represent the
data model
Designed structures are appropriate to the
intended implementation method
Database Systems-Design Phase





Database Structure and item names
relate to the data model
Unique keys are chosen for all
Database structures
Validity checks represent the data
model
Data item characteristics represent the
data model
Designed structures are appropriate to the
intended implementation method
 The conceptual design [Data Model or E- R
Diagram] has already been created in the
analysis phase using Entities, Relationships and
Attributes
The entities now become tables and the
relationships between these entities are
preserved between the tables
Most RDBMS have syntactical rules that
govern how fields and tables are named. This
may affect how we construct the field and
table names.
Database Systems-Design Phase





Database Structure and item names
relate to the data model
Unique keys are chosen for all
Database structures
Validity checks represent the data
model
Data item characteristics represent the
data model
Designed structures are appropriate to the
intended implementation method
 Indexes
The keys chosen for the database structures are
the same as those identified in the data model.
These remember were established during the
normalisation process
Some RDBMS include tools that create
indexes of fields to speed up searches within
the database.
However, only Primary and Foreign keys
should normally be indexed to help speed up
this process.
Database Systems-Design Phase





Database Structure and item names
relate to the data model
Unique keys are chosen for all
Database structures
Validity checks represent the data
model
Data item characteristics represent the
data model
Designed structures are appropriate to the
intended implementation method
Data entered into the database should be valid
and correct. Most RDBMS contain tools which
will offer 3 types of validity check
PRESENCE
Checks to see if a value is present or not. E.g. if a field
value must be set, a presence check would be carried out
on it.
RANGE
Checks to see if a value entered is within a prescribed
range.
RESTRICTED CHOICE
This would ensure that the user only entered data that
corresponded to a list of pre-set values e.g. N S E or W
Database Systems-Design Phase





Database Structure and item names
relate to the data model
Unique keys are chosen for all
Database structures
Validity checks represent the data
model
Data item characteristics represent the
data model
Designed structures are appropriate to the
intended implementation method
Every RDBMS has its own set of data
types that it can implement.
In the design phase we create a more detailed
data dictionary that identifies the field
types, sizes, keys and validity checks that can
be implemented in the RDBMS
Database : Charlie’s Baker Shop
Table
Fieldname
: Orders
Type
Size
Order No
Autonumber
Acc. No
Customer
Address1
Address2
Address3
Postcode
Date
Total Cost
Number
Text
Text
Text
Text
Text
Date/Time
Currency
Validation
Req'd
Index
Size
Yes
Auto
35
50
50
50
8
Auto
Auto
Yes
Yes
Yes
No
Yes
Yes
Yes
Yes
Yes (Unique)
Primary Key
No
No
No
No
No
No
No
No
>=£10.00
Format
XX9?_9XX
Short Date
£99.99
Also refers to tables
that foreign keys
have come from
Database Systems-Design Phase





Database Structure and item names
relate to the data model
Unique keys are chosen for all
Database structures
Validity checks represent the data
model
Data item characteristics represent
the data model
Designed structures are appropriate to
the intended implementation method
 Relationships
State the relationships that will be
implemented using the RDBMS. These
should be the same as for the E-R
diagram.
However, in the design phase we show the
relationships between the primary and
foreign key fields in each table.
Charlie’s Baker Shop
Customers
Price List
(Acc. No.
Customer
Address)
(Item
Item Price)
Orders
(Order No.
*Acc. No.
Date
Total Cost)
Part Order
(*Order No.
*Item
Quantity)
[Customers.Acc.No.]
1: M
[Orders.Acc.No.]
[Orders.Order No]
1: M
[Part Order.Order No]
[Price List.Item]
1: M
[Part Order.Item]
When you are designing the database
structures for your implementation you
must always consider what your RDBMS is
capable of doing.
There is no point designing a system that
cannot be implemented with the software
that you have available.
Database Systems Implement a designed database
structure based on an existing
system
THE IMPLEMENTATION PHASE
Database Systems
Implementation Phase

Correctly implement database structures from
design


Perform data manipulations effectively

Produce reports to meet given specifications

Evaluate the database structure against the
existing system

Ensure data is well managed and secure
Carry out queries to meet given criteria efficiently
Database Systems
Implementation Phase

Correctly implement database structures from
design


Perform data manipulations effectively

Produce reports to meet given specifications

Evaluate the database structure against the
existing system

Ensure data is well managed and secure
Carry out queries to meet given criteria efficiently
Using the chosen RDBMS each of the tables within
your design including all the relevant fields will be
specified and established.
Implement all the features of your detailed data
dictionary i.e.




Table names
Fieldnames and whether indexed or not
Validity checks
Primary/foreign keys
Once and only once the structure has been
established, is it possible to enter data into the tables
Database Systems
Implementation Phase

Correctly implement database structures from
design


Perform data manipulations effectively

Produce reports to meet given specifications

Evaluate the database structure against the
existing system

Ensure data is well managed and secure
Carry out queries to meet given criteria efficiently
 EDIT
An edit manipulation involves the user changing data
already present in the database
 SORT
A sort manipulation takes a range of data from the
database and sorts it on the values in one or more fields
 INSERT
An insert manipulation involves the user adding new
data to tables within the database
 DELETE
A delete manipulation involves the user removing data
from tables within the database
Database Systems
Implementation Phase

Correctly implement database structures from
design


Perform data manipulations effectively

Produce reports to meet given specifications

Evaluate the database structure against the
existing system

Ensure data is well managed and secure
Carry out queries to meet given criteria efficiently





Queries are questions you can ask of the data in tables
within the database. They are similar to searches you
would perform within a flat-file database
Queries create subsets of the data within the database by
field, record or both
A query performed on a relational database will always
produce a table
A query can be performed on more than one table because
the relationships between primary and foreign keys link
the tables together
Queries not only produce subsets of the data, but can be
used to perform mathematical operations on this data
Base Tables
– The original table that the query acted upon
Answer Tables – The table created by the query. This contains a
subset of the data from the base table/s
Styles of Query

Textual

Numeric

Exact

Inexact

Combinations of search criteria
Queries that act on fields of the text data type
Queries that act on fields of the numeric data type
Finds a specific match on a field for particular criteria
e.g. customers with surname ‘Smith’.
Looks for values within a range in a particular field
e.g. customers with names beginning with 'S'
Uses a combination of criteria e.g. All customers who
ordered items costing <£1.50
Different tools have been developed to allow us to query
a relational database through the RDBMS
 Graphical Tools
Allow us to select and enter the details for the query graphically
e.g. The tool you are using in Microsoft Access
 Structured Query Language [SQL]
A recognised international standard for creating queries for relational
databases e.g.
SELECT DISTINCT [Orders].[Acc.No.],
Sum([Orders].[Total Cost]) AS [SumOfTotal Cost]
FROM [Orders]
GROUP BY [Orders].[Acc.No.];

Often, an RDBMS will use both types of tool.
Inexperienced users would use the graphical tool
 Experienced users would use SQL
Database Systems
Implementation Phase

Correctly implement database structures from
design


Perform data manipulations effectively

Produce reports to meet given specifications

Evaluate the database structure against the
existing system

Ensure data is well managed and secure
Carry out queries to meet given criteria efficiently
Reports
A report is like a query in that it can summarise
the data in the database. However reports are
used to produce printed output [ Hard Copy]
e.g. The user may require a listing of all
customers and their addresses from the database
N.B. When producing reports using MS Access
it is possible to perform some basic statistical
analysis on the data if the data type is numeric
Database Systems
Implementation Phase

Correctly implement database structures from
design


Perform data manipulations effectively

Produce reports to meet given specifications

Evaluate the database structure against the
existing system

Ensure data is well managed and secure
Carry out queries to meet given criteria efficiently
When the relational database has been produced an evaluation
should be performed against the existing system to identify any
improvements and a report compiled to include the following:
 Information summary of how the existing system is used
outlining restrictions of this system and limited usage
 Improvements in the efficiency and effectiveness of the new
system e.g. Reduction in entry and storage of duplicate data
 Automation of operations such as producing reports
 Saving in man hours for entering data and reduction in data
entry errors
 New facilities available through the new system such as reports
that analyse the data statistically or queries that automate the
production of summary data
Database Systems
Implementation Phase

Correctly implement database structures from
design


Perform data manipulations effectively

Produce reports to meet given specifications

Evaluate the database structure against the
existing system

Ensure data is well managed and secure
Carry out queries to meet given criteria efficiently
Legislation – Your legal responsibilities
 Data Protection Act (1998)
Ensures that people who keep information on computer (Data Users) give
rights to individuals (Data Subjects) who have information recorded about
them on computer
 Copyright, Design & Patents Act (1988)
Most published material is protected by this act i.e. there are restrictions
to the use you can make of such material in respect of copying selling and
adapting it.
 Computer Misuse Act (1990)
This act provides regulations that govern the access to and use of
computer systems and the information within them e.g. unauthorised use of
another person's username and password.