Database Development using Microsoft Access CMPCP0015

1
Database Development using Microsoft
AccessCMPCP0015

• Lecture 3
• Normalization Part 1
• Functional Dependency 1NF
• Mengjie Yu
• M.Yu_at_2001.ljmu.ac.uk

2
Recommend Books

• Connolly, T., Begg, C., Strachan, A. Database
  Systems, A Practical Approach to Design,
  Implementation, and Management, 3rd ,
  Addison-Wesley.
• Date, C. J. Database Systems Fifth Edition
  Addison-Wesley, 2004. ISBN 0-321-18956-6.
• Beynon-Davies P., 2nd Edition, Database Systems,
  Macmillan Press 2000, ISBN 0-333-79227-0.
• Atzeni, P., Ceri, S., Paraboschi, S., and
  Torlone, R. Database Systems, McGraw Hill 1999,
  ISBN 0-07-709500-6.

3
A Copy of Microsoft Access2003

• www.cms.livjm.ac.uk/msdnaa
• With a valid student account,
• And password assigned by the technicians.

4
Recap

• In the last lecture
• Conceptual Database Design
• - Relational Data Model
• - Entity Relationship Modelling
• Entities Entity occurrence
• Attributes Attribute occurrence
• Relations Cardinality of Relationship
• Candidate Key, Primary Key, Composite Key
• Logical and Global Conceptual data model

5
In this Lecture

• We will learn about-
• The purpose of Normalization
• The problems associated with Redundant Data
• The concept of Functional Dependence, the main
  tool for measuring the appropriateness of
  attribute groupings in relations.
• How Functional Dependences can be used to group
  attributes into relations that are in a known
  normal form.
• The processes of Normalization
• First Normal Form (1NF)

6
Introduction to Normalization

• Is a technique that developed to transfer the
  conceptual data model into a structure accepted
  by the Relational Database.
• It gives us a method for identifying the
  existence of potential problems in the design, as
  well as supplying a method for correcting these
  problems.
• Additional Information-
• Normalisation introduced to the Relational
  database, but applicable to any type of database
  for example (Hierarchical database, Network
  database).

7
Recap

• Relational Database holds information as-
• Data
• Entities, attributes of each entity from problem
  descriptions
• The relations of data
• The relations of entities ER-modelling (entity
  relationships)
• The relations of attributes Candidate keys,
  primary key.

8
Introduction to Normalization

• One Old concept
• The use of Primary Key (PK) What is PK?-
  An attribute or a set of smallest combination of
  attributes that can uniquely identify a single
  entity occurrence.- PK is one of the candidate
  keys of an entity.
• Need some new concepts-
• Duplicate (OK) V.S. Redundant (not OK) data
• Functional Dependency
• Repeating groups

9
The Purpose of Normalization

• The goal of the process is to allow us to start
  with a relation or a collection of relation and
  produce a new collection of relations that is
  equivalent to the original collection (i.e
  represent the same information) but is free of
  problems.
• - Remove the Data Redundancy- Produce new
  relations that still represent the same
  information
• Four most commonly used normal forms are first
  (1NF), second (2NF), third (3NF) and Boyce-Codd
  (BCNF) normal forms.

10
Data Redundancy- An Example

BOOK

• The table represents the following information
  A book (book 2), entitled Fish has two
  authors and was published by Microsoft in GB.
• Note the PK. (It is correct.)

11
Redundancy V.S. Duplication

• Duplicated data (data cannot be removed without
  loss of information because it can be inferred)
  is acceptable
• Redundant data (data can be removed without loss
  of information) makes the design bad.

• Question on the previous slide, please
  identify-
• The redundant data which single item of data, if
  removed, doesnt infer other data.
• 2) The duplication data which can be removed
  without affecting other data

12
Update Anomalies

• Insert anomaly. Cant insert a publisher without
  inserting a book.
• Update anomaly. One title could be updated and
  the other not.
• Delete anomaly. By deleting a book, you may also
  delete the information of a publisher if that
  book represents the last publication of that
  publisher

Why not? Clue - consider the PK and the fact that
PKs cannot have null values
E.g. if the books title were really Fish and
frogs
What information about publishers would be lost
if we deleted this book?
13
Update Anomalies

• Modification Anomalies
• We discuss how the process of normalization can
  be used to design / structure well-formed
  relations.
• However, we first introduce the concepts of
  functional dependencies, which are fundamental to
  the process of normalization.

14
Functional Dependency
Not the same relationshipsbetween entities

• What Functional Dependency is?
• Describes the relationship between attributes in
  a relation
• a property of the meaning or semantics of the
  attributes in a relation.
• What Functional Dependency does?
• Associates with the process of normalization.
• Examines the relationships between attributes
• Minimizes the data redundancy
• Derives the well-formed relations

15
Functional Dependency

• Note !!!For the purposes of normalization we are
  interested in identifying functional dependencies
  between attributes of a relation that have a
  one-to-one relationship.
• For example A? B
• each value of A is associated with exactly
  one value of B.

11
1 many
16
Functional Dependency

• Describes the relationship between attributes in
  a relation
• If knows the value of A, the dependency of this
  relation R will find only one value of B
• Conclusion
• Attribute B is functionally dependent on
  attribute A

17
Identify Functional Dependency
Revised direction
15
18
Identify Functional Dependency

• Think about the functional dependency between
  staffNo ?sName, and staffNo -gt position???
• Can we consider the functional dependency in a
  revised direction as sName ?staffNo and
  position-gt staffNo???

19
Identify Functional Dependency

• What kind of functional dependencies of
  attributes
• are we actually interested?
• Not all the functional dependencies identified
  between attributes.
• Functional dependency that can identify all the
  possible values of attributes in a relation.
• One-to-One (11) relationship

20
Identify Functional Dependency

For example staffNo? sName, position,
salary, branchNostaffNo sName position salary bra
nchNo SL21 John White Manager
30000 B005 SG37 Ann Beech Assistant
12000 B003 SF23 John White
Supporter 3000 B004 In the slide 16, we
can choose sName as the primary key. Why should
we select staffNo as the primary key instead ?
We consider two staff sharing the same name may
happen in the branch.
21
Identify Primary Key
The main purpose of identifying a set of
functional dependencies -

• Not all the functional dependencies of attributes
  that we are interested in.
• Only those functional dependencies that can
  uniquely identify all the possible values of
  attributes in a relation.
• Use this functional dependency to identify /
  verify primary key

!!! Primary Key an attribute or a set of
attributes which can uniquely identify the
individual occurrence of an entity.
22
The Process of Normalization

• Formal technique for analysing a relation based
  on its primary key and the functional
  dependencies between the attributes of that
  relation.
• It normally consists of a series of steps. Each
  step corresponds to a specific normal form, which
  has known properties.
• As normalisation proceeds, the relations become
  progressively more restricted (stronger) in
  format and also less vulnerable to update
  anomalies.

23
Relationship between Normal Forms
!!! It is normally recommended that we proceed
the normalization to at least third normal form
3NF.
24
Unnormalised Form (UNF)

• Unnormalised Form A table that contains one or
  more Repeating Groups.
• To create an unnormalised table
• Transform the data from the information
  source (e.g. form) into table format with
  columns and rows.

25
First Normal Form (1NF)

• A relation is in 1NF if it does not contain
  repeating group.
• 1NF A relation in which the intersection of
  each row and column contains one and only one
  value!!!.
• Start with one entity with its primary key
  underlined and put brackets round groups of
  attributes which repeat.
• 1NF - Remove repeating group(s) into separate
  entity(s). Include the original primary key.

26
Repeating Group

• A repeating group an attribute (or set of
  attributes) which has more than one value for a
  primary key value.
• E.g. ServiceDate in the entity / table
  CAR(Regno, ServiceDate) is a repeating group,
  since a car may have had a number of services on
  different dates.

Car
27
Repeating Group

• Repeating groups (RGs) are not allowed in a
  relational design, since there can only be one
  value per cell in a table.
• Stage 1 will be to remove RGs.
• This will put the entities in first normal form
  (1NF)

28
Repeating Group

• Repeating groups (RGs) are not allowed in a
  relational design, since there can only be one
  value per cell in a table.
• Stage 1 will be to remove RGs.
• This will put the entities in first normal form
  (1NF)

29
First Normal Form (Example)
Repeating Group
Unnormalised form
INVOICE (Invoice-No., Customer_No.,Customer_name
, Cust._Add., Date, (Item_No,
Desc,Unit_Price,Qty, Price), Total )
--------------------------------------------------
---------------------------------- First Normal
Form INVOICE-1 ( Invoice_Number,
Customer_No., Customer_Name, Customer_Address,
Data, Total ). INVOICE_ITEMs (Invoice_Number,
Item_No, Desc,Unit_Price,
Qty, Price)
Keep the primary key with repeating group
Look for the key(s) in the new entities. It might
be a compositekey including the original key.
27
30
Notes on 1NF

• The names have -1 to indicate the entities are in
  1NF.
• Dont let the choice of name mislead you.
• If the original entity has nested repeating
  groups, you wont reach 1NF in one step.
• Remove the outer group first, then remove its
  repeating groups.
• Every attribute is Functionally Dependent on the
  primary key, in 1NF

31
Summary

• Concepts of Normalization
• The purpose of Normalization
• Data Redundancy V.S. Duplication
• Functional Dependency
• Functional dependency identifies the Candidate
  key and Primary keys in the entities.
• The processes of Normalization
• First Normal Form (1NF)
• the purpose of 1NF is to remove the Repeating
  Group

32
Next Lecture

• In the next lecture, Normalization Part 2-
• 2nd Normal Form
• 3rd Normal Form
• The examples of normalization
• 1NF, 2NF, 3NF etc.