Database Design - Download Now DOC

Document Sample
Database Design - Download Now DOC Powered By Docstoc
					 Database Design
        Lesson book 2

Logical Data Modelling



Name:    _____________

Group: _____________

Date:    _____________
Bournemouth and Poole College
Centre for Business, Computing and Tourism



                         Logical Data Modelling
Up to now we have been deciding on the entities and attributes in our
database using common sense and an understanding of the types of data that
a user might like to hold on their computer system.

However, there are two more formal methods of database design, known as
logical data modelling, and these methods are:

   1) Entity Relationship Diagrams – known as a top-down approach. This
      means that the data is looked at at a high level and then broken down
      into more detail. In database design a top-down approach looks at
      entities and then attributes.

   2) Normalisation – known as a bottom-up approach. This means that the
      data is looked at in all its detail and then analysed at higher levels. In
      database design a bottom-up approach looks at attributes and then
      entities.

If both of these methods are used to design a database then the same result
should be produced by each method. The normalisation method is more
difficult to do but ends up with a model containing more detail.

The Entity Relationship Diagram (ERD)

Stage 1
To draw an ERD, you must first assess which entities are likely to be required
in the database. You then draw an entity box on your diagram for each entity
in your system. An entity is shown on an ERD as a labelled round box




                             ENTITY NAME




                                                                              2
Bournemouth and Poole College
Centre for Business, Computing and Tourism

Stage 2
A relationship between two entities is shown by a line. Remember - Entities
have a relationship if they have one or more attributes in common, where the
common attribute is a key field for one or both of the entities.


                                             Relationship Line

         ENTITY A




                                             ENTITY B



Stage 3
A relationship can take a number of forms, known as the degree of the
relationship.

One to Many: One record in a table can have a relationship with many
records in the other table. An example of this type of relationship is a hospital
ward. A ward has many patients but a patient belongs to one ward. This is the
main type of relationship used in a relational database.

The many component of the relationship line is known as a crow’s foot. The
relationship notation is 1:m



                                                     1:m

One to One: One record in a table can only have a relationship with one
record in the other table. The relationship is shown by a straight line with no
crows foot and the notation is 1:1. An example of this type of relationship is a
blind person and a guide dog. A blind person can have only one guide dog
and the guide dog belongs to only one person. This type of relationship can
be used in a relational database but is not common.



                                                        1:1

                                                                               3
Bournemouth and Poole College
Centre for Business, Computing and Tourism

Many to many: Many records in a table can have relationships with many
other records in the other table. The line has a crow’s foot on both ends and
the notation is m:n.. An example of this type of relationship is exams and
students. A student takes many exams and the same exam is taken by many
students. This type of relationship exists in real life but cannot be created in a
relational database.

                                                      m:n

                 Exercise – Creating ‘First Draft’ ERDs

Decide on the relationships between the following entities and draw the
resulting ERDs.

       An MP and her constituency
       Students and college courses
       A teacher and a class of pupils
       A parent and his children
       A husband and wife
       CDs and artists

Refining the ERD – Adding Key information

Once you have created the ERD you should add the key information to each
entity box.

The primary key for the entity should be written directly under the Entity name
and should be underlined.

A foreign key should be added to the entity if it is in a One to Many
relationship with another table and it is on the Many side of the relationship.
The foreign key should have an asterisk (*) placed before it.



                                                   Rounded Box
       ENTITY NAME
                                                   Name of Data Entity
       Primary Key
                                                   Primary key(s) underlined
       * Foreign Key
                                                   Foreign key(s) shown with an
                                                   “*”



                                                                                4
Bournemouth and Poole College
Centre for Business, Computing and Tourism

Resolving Many to Many and One to One relationships
An ERD is generally developed over a number of stages. A first draft ERD is
allowed to have One to One and Many to Many relationships. However, by the
time the final ERD is developed, it is necessary that all Many to many
relationships are resolved and the number of One to One relationships is
minimised.

Many to Many relationships are undesirable because it is difficult to construct
databases that are based on this data model. This is because each of the
entities in the relationship will have to hold the all keys of the other entities to
which they are related. In our book/ author example, each book would need
room to hold the ID of several authors and each author would have to have
room to hold the IDs of several books.

Many to Many relationships are resolved by creating a new entity, called a link
entity. The link entity separates the two entities involved in the relationship as
both will now have a One to Many relationship with the link entity.

Resolving Many to Many relationships


Stage 1



             Book                                          Author


Stage 2



             Book                                             Author




                                    Book/
                                  Author Link



                                                                                  5
Bournemouth and Poole College
Centre for Business, Computing and Tourism

Resolving One to One relationships
One to one relationships don’t have to be removed, but you should always
question the necessity for a 1:1. Perhaps the two entities can be held as one
entity only.

Comments on drawing an ERD
1) The relationship lines shouldn’t cross if possible
2) The one end of the relationship line should be higher up the page than the
   many end. Otherwise the relationship is said to have “dead crows”. The
   purpose of this is that the one end is usually a more important entity than
   the many end.

                                   ERD Exercise

You have been asked to draw an entity relationship diagram for a video rental
system. The database entities are supplier, video, actor and customer. The
videos are supplied by a supplier, rented out by customers and actors appear
in the videos.

Task 1: Draw a first draft ERD showing the four entities and their
relationships.




Task 2: Draw a second draft ERD showing how you have resolved any Many
to Many relationships.




Now go and look at the Entity Relationship Diagram Learning Module on
the VLE and check your understanding of ERDs.


                                                                            6
Bournemouth and Poole College
Centre for Business, Computing and Tourism


Normalisation
Normalisation is a process of examining the attributes of the database to
ensure that the database is consistent and as free from error as possible and
will remain error-free and robust when data is added, changed or deleted –
referential integrity. The essence of normalisation is the elimination as far as
possible of duplicate data. This means that data is stored once only.

Creating ERDs is also a way of deciding how the available data should be
split into tables and should be used as a complement to normalisation. At the
end of both processes there should be the same number of tables with the
same entities and primary and foreign keys. The ERD can be used to check
whether normalisation has taken place correctly. The advantage of
normalisation is that all the fields can be identified and put into the correct
tables.

To start the process of normalisation, we begin with the data we are going to
input into the system. The data normally comes from a form that is already
being used by the organisation.

Task 1: Study the Video Rental form to try to identify the attributes we are
going to store on the database.

The form shows us all the data that is currently being held manually so we
know that this is the data that we are going to need to hold on the database.

The first step in normalising is to construct a list of attributes, ensuring that
each attribute is atomic – broken down as far as it can be. For example if we
wanted to store an address on the database, we should break it down into
lines of the address and postcode. If there is any repeating data this is
indented. A candidate key is then selected from the list. It is underlined and
placed at the top of the list. The data is now described as being in
Unnormalised Form (UNF).

Task 2: Create a normalisation table in word and add a list of attributes to the
column headed UNF. Remember to indent any repeating data.

The UNF list of attributes represents our first attempt at creating a table for
the database. We now need to move to First Normal Form (1NF) by moving
repeating groups into separate tables.

We do this by copying the UNF table into a list headed 1NF. At the point
where we encounter a repeating group we move the attributes into a new,
separate, table. For this new table we construct its key by first copying the
candidate key from the UNF table and then identifying a second (or even third
or fourth) attribute of the new table to form a composite key.

1NF rule - is the separation of all repeating groups




                                                                               7
Bournemouth and Poole College
Centre for Business, Computing and Tourism

Task 3: Move the attributes from the UNF column to the 1NF column
remembering to put repeating data into a separate table with a new composite
key.

To refine the table to Second Normal Form (2NF) we consider only tables
with composite keys and move into separate tables any attributes that do not
depend upon the whole of the composite key but only on a part of it. The key
to the new table is just that part of the composite key on which the attributes
in the table depend.

We need to ask the question – would the attribute change its value if only a
part of the key changed its value. If the answer is yes, we copy the part of the
candidate key into a new table and then move the attributes that depend on it
into the new table.

2NF rule - is the separation of part-key dependencies

Task 4: Checking only the tables with composite keys, identify any attributes
with part-key dependencies and move them into a new table. Add primary
keys to the new tables.

We next take our 2NF tables to Third Normal Form (3NF) by checking only
tables which have simple keys and creating new tables for attributes which do
not depend upon their candidate key but which depend on other non-key
attributes.

We need to ask the question – would the attribute change its value if the key
changed its value, or would it only change if some other attribute changed its
value. If the answer is that the attribute is not dependant on the key but on
another attribute in the table, then we must move the attribute to a new table.

We copy the dependent attributes into a new table and then construct a
candidate key for the new table. The key for the new table is the attribute on
which the other attributes are dependant. We then copy this key back into the
original table and mark it with an * as a foreign key.

3NF rule - is the separation of non-key dependencies

Task 5: Checking only the tables with simple keys, identify any attributes with
non-key dependencies and move them into a new table. Add primary keys to
the new tables and indicate the foreign keys in the existing tables.

Once we have added the 3NF data we can decide on names for each of the
tables we have created and add these to the final column of our normalisation
table.

We have finished normalising our data! Now all we need to do is check our
tables and keys against our entity relationship diagram to make sure that both
methods have produced the same results. Check back to your exercise on
page 6 to make sure this is the case.


                                                                              8
Bournemouth and Poole College
Centre for Business, Computing and Tourism


                         Creating a Data Dictionary

A data dictionary is a list of the tables in a database and the fields that are in
each table. It is created when the database is designed and used by the
programmers to create the tables accurately. The list of tables and fields can
be taken from the final two columns of the normalisation table.

The data dictionary does not contain any of the actual database data, but
describes the data types, lengths, any default values (the value that should
appear in the field if no value is entered by the user) and any validation
checks that need to be carried out on the data before it is input onto the
database.

Data Dictionary Layout

 Table      Field      Description      Data        Length     Default   Validation
                                        Type                   Value      checks



Example Data Dictionary entry

  Table        Field      Description        Data    Length    Default   Validation
                                             Type              Value      checks
Customer      Country      Country of        Text      30        UK      Alpha only
                           residence

                       Creating an Output Data Table

The data dictionary shows the data that will be held on each database table.
There will be some data, however, that won’t be held on a table but will be
calculated from the existing data and then displayed to the user. For example,
on the Surgery database the number of patients allocated to each doctor
could be calculated and displayed on a report.

This type of data is known as processed data and should also be shown in a
table as the database is being designed.

Output Data Table Layout

Output Data                  Data required for               Processing required
                             processing


Example Output Data Table entry

Output Data                  Data required for               Processing required
                             processing
Number of patients           Patients per doctor             COUNT patients



                                                                                   9
Bournemouth and Poole College
Centre for Business, Computing and Tourism


    Data Dictionary and Out Data Dictionary Exercise
You have been asked to design a system to hold details of cars in a car showroom.
At present the data is held on a card index. An example of a typical card in the index
is shown below. The car dealer only sells Renault and Peugeot cars.


                         Car Details Card
Registration: _______________        Engine Serial No: __________________

Make: ____________________           Model: _______________

Colour: ________                     Style: Hatchback/Saloon/Coupe

No. of doors: __                     No. of seats: __                            Blank Card

Year of manufacture: ____            Condition: Good/Fair/Poor

No. of previous owners: __           Mileage: ____________

Price (A): £__________               On the road costs (B): £________

Car Tax (C): £_____                  Total Price (D): £_________ (A+B+C above)




                         Car Details Card
Registration: X52 GTG                Engine Serial No: 34XX92990

Make: Renault                        Model: Laguna

Colour: Silver                       Style: Hatchback/Saloon/Coupe
                                                                                 Filled in Card
No. of doors: 5                      No. of seats: 5

Year of manufacture: 2000            Condition: Good/Fair/Poor

No. of previous owners: 1            Mileage: 56450

Price (A): £14000                    On the road costs (B): £500

Car Tax (C): £150                    Total Price (D): £14650 (A+B+C above)



Please complete a data dictionary for the input data to this system and hand it in to
your lecturer.




                                                                                   10

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:36
posted:11/4/2012
language:English
pages:10