Summer Semester 2004/2005

1
IMS1907 Database Systems

• Summer Semester 2004/2005
• Lecture 7
• Data Modelling and Normalisation

2
Transforming ER Models to Relations

• Transforming an ER diagram into normalised
  relations, and then merging all the relations
  into one final, consolidated set of relations can
  be accomplished in four steps
• Represent entities as relations
• Represent relationships as relations
• Normalise each relation
• Merge the relations with a common PK

3
Representing Entities as Relations

• Each entity in the ER diagram is transformed into
  a relation

CUSTOMER
Customer (Customer-no, Name, Address, City,
State, Postcode,
Discount)
4
Multi-valued Attributes as Relations

• The multi-valued attribute becomes a relation
  with a composite key consisting of the primary
  key of the entity and the multi-valued
  attributes partial identifier

EMPLOYEE
Skill
Emp_ID
Customer (Emp_ID, Name, Address) Employee Skill
(Emp_ID, Skill)
5
Representing Weak Entities as Relations

• The weak entity becomes a relation with a
  composite key consisting of the primary key of
  the base entity and the weak entitys partial
  identifier

Movie (Movie-ID, Title, Release-date) Videotape
Copy (Movie-ID, Copy-No, Condition)
6
Representing Relationships as Relations

• Binary Relationships (1N, 11)
• For 1N, add the primary key of the entity on the
  one side of the relationship as a foreign key
  in the relation that is on the many side
• For 11 relationship involving entities A and B,
  choose from
• add the primary key of A as a foreign key of B
• add the primary key of B as a foreign key of A
• both of the above at a cost!
• use business rules and good sense to guide choice

7
Representing Relationships as Relations

• Binary Relationships (1N)

CUSTOMER
ORDER
places
Customer (Customer-no, Name, Address, City,
State, Postcode, Discount) Order
(Order-no, Order-date, Promised-date, Customer-no)
8
Representing Relationships as Relations

• Binary Relationships (11)

CARE CENTRE
NURSE
manages
Nurse (Nurse-no, Name, Address,
Qualification) Care Centre (Care-Centre-No,
Location, Type, Nurse-no)
9
Representing Relationships as Relations

• Binary and Higher Degree Relationships (MN)

• If we wish to know the quantity of a product on
  an order, this attribute is an attribute of the
  relationship ordered on

10
Representing Relationships as Relations

• For MN, first create a relation for each for
  each of the entity types, then create a relation
  for the relationship, with a composite primary
  key formed from the primary keys of the
  participating entity types

PRODUCT
ORDER
ORDER-LINE
Order (Order-no, Order-date, Promised-date) Order
Line (Order-no, Product-no, Quantity-ordered) Prod
uct (Product-no, description, (other attributes))
11
Representing Relationships as Relations

• Unary or Recursive Relationships (MN)

ITEM
is part of
Item (Item-no, Name, Cost) Item-Bill (Item-no,
Component-no, Quantity)
12
Representing Relationships as Relations

• Unary or Recursive Relationships (1N)

EMPLOYEE
supervises
Employee (Emp-id, Name, Birthdate, Manager-id)
13
Representing Relationships as Relations

• Ternary or Higher Relationships

DOCTOR
Patient (Patient-id, Patient-name) Doctor
(Doctor-id, Doctor-name) Treatment
(Treatment-code, Description)
PATIENT
treats
TREATMENT
Patient-Treatment (Patient-id, Doctor-id,
Treatment-code, Date, Time, Result)
14
Representing Relationships as Relations

• Supertype-subtype relationships

PROPERTY
BEACH PROPERTY
MOUNTAIN PROPERTY
Property (Property-ID, Address, City, Postcode,
No-rooms, Rent) Beach (Property-ID,
Nearest-beach, Distance-to-beach) Mountain
(Property-ID, Skiing)
15
Normalisation

• Normalisation is a process for converting complex
  data structures into simple, stable data
  structures in the form of relations
• Data models consisting of normalised relations
• are robust, stable flexible and have minimum
  redundancy
• simplify enforcement of referential integrity
  constraints
• make data maintenance easier
• are an improved representation of real world
  objects

16
Normalisation

• Normalisation ensures that each attribute is
  attached to the appropriate relation
• Each attribute is contained in the relation which
  represents the real world system object or
  concept that the attribute describes or is a
  property of
• the attribute Student-name should be in the
  relation STUDENT which represents the real world
  object student of interest to a student records
  system

17
Normalisation

• Originally developed as part of relational
  database theory
• Accomplished in stages, each of which corresponds
  to a normal form
• Codd defined first, second and third normal forms
• third normal form is adequate for most business
  applications
• Later extensions include Boyce-Codd, 4th, 5th and
  domain-key normal forms

18
Functional Dependency

• Central to normalisation and well-structured
  relations
• A functional dependency is a particular
  relationship between attributes in a relation
• For any relation R, with attributes a and b,
  if for each value of a there is only ever one
  value of attribute b associated with it,
  attribute b is said to be functionally
  dependent on attribute a

19
Functional Dependency

• If for every valid instance of A, that value of A
  uniquely determines the value of B, B is
  functionally dependent on A

A identifies B A
B Emp Emp-name Emp Salary
20
Normalisation

• Normalisation to third normal form is
  accomplished in three steps each corresponding to
  a basic normal form
• A normal form is a state of a relation that can
  be determined by applying simple rules concerning
  dependencies within that relation
• Each step of the normalisation process is applied
  to a single relation in sequence so that the
  relation is converted to third normal form
• All identified relations are normalised

21
Steps in Normalisation
Unnormalised table
Remove repeating groups
First Normal Form 1NF
Remove partial dependencies
Second Normal Form 2NF
Remove transitive dependencies
Third Normal Form 3NF
22
First Normal Form 1NF

• A relation is in first normal form if it contains
  no repeating data
• the value of the data at the intersection of each
  row and column must be single-valued
• Remove any repeating groups of attributes to
  convert a relation to 1NF
• key of the removed group will be a composite key

23
First Normal Form 1NF

• Identify primary key
• Identify repeating groups
• Remove repeating groups

Order (Order, Date, Customer, Name, (Item,
Desc, Qty)) Order (Order, Date, Customer,
Name) Order-Item (Order, Item, Desc)
24
Second Normal Form 2NF

• A relation is in 2NF
• if it is in 1NF
• no part of the primary key is dependent on any
  other part of the primary key
• no non-key attribute is dependent on only part of
  the primary key

25
Second Normal Form 2NF

• Converting a relation to 2NF involves removing
  partial dependencies
• A partial dependency exists
• if one part of a composite primary key is
  dependent on another part of the key
• if one or more non-key attributes are dependent
  on only part of a composite primary key

26
Second Normal Form 2NF

• Remove any partial dependencies to convert a 1NF
  relation to 2NF
• If the primary key consists of only one attribute
  then a 1NF relation is automatically in 2NF
• If a 1NF relation has no non-key attributes, as
  long as there are no dependencies between the
  parts of the primary key, it is automatically in
  2NF

27
Second Normal Form 2NF

• Remove partial dependencies
• A non-key attribute cannot be identified by part
  of a composite key

Order (Order, Item, Desc, Qty-ordered) Order-
Item (Order, Item, Qty-ordered) Item (Item,
Desc)
28
Dependencies Within the Primary Key

• Part of a composite primary key cannot be
  identified by another part of the primary key
• DEPT (Dept, Dept-name, (Emp, Emp-name))
• DEPT (Dept, Dept-name)
• DEPT-EMP (Dept, Emp, Emp-name)
• But
• DEPT-EMP (Dept, Emp, Emp-name)
• Remove Dept from the key
• EMP (Emp ,Emp-name, Dept)

29
Partial Dependency Anomalies
Order-Item
Order
Item
Item-desc
Qty
2
27
873
nut
1
28
402
bolt
10
28
873
nut
50
30
495
washer

• UPDATE - change item-desc in many places
• DELETE - data for last item lost when last order
  for that item is deleted
• CREATE - cannot add new item until it is ordered

30
Solution to Anomalies 2NF
Order
delete last order for item, but item remains
Order
Item
Qty
27
873
2
28
402
1
28
873
10
Item
30
495
50
Item
Desc
add new item at any time
873
nut
change item description in one place only
402
bolt
495
washer
31
Third Normal Form 3NF

• A relation is in 3NF if it is in 2NF and no
  transitive dependencies exist
• A transitive dependency is a functional
  dependency between two or more non-key attributes
• if a relation has no or only one non-key
  attribute then by definition, a transitive
  dependency cannot exist
• Remove any transitive dependencies to convert a
  2NF relation to 3NF

32
Third Normal Form 3NF

• Remove Transitive Dependencies
• A non-key attribute cannot be identified by
  another non-key attribute

Employee (Emp, Ename, Dept, Dname) Employee
(Emp, Ename, Dept) Department(Dept, Dname)
(look for foreign keys and their attributes)
33
Transitive Dependency Anomalies
Employee
Emp
Emp-name
Dept
Dname
10
Smith
D5
MIS
20
Jones
D7
Finance
25
Smith
D7
Finance
30
Black
D8
Sales

• UPDATE - change dept name in many places
• DELETE - data for dept lost when last employee
  for that dept is deleted
• CREATE - cannot add new dept until an employee is
  allocated to it

34
Solution to Anomalies 3NF
Employee
delete last emp in dept, but dept remains
Emp
Ename
Dept
10
Smith
D5
20
Jones
D7
25
Smith
D7
30
Black
D8
Item
Dept
Dname
add new dept at any time
D5
MIS
D7
Finance
change dept name in one place
D8
Sales
35
Normalisation to 3NF

• A relation is normalised to 3NF if all attributes
  are fully functionally dependent on the primary
  key
• remove repeating groups
• remove partial dependencies
• remove transitive dependencies

36
Normalisation - Merging Relations

• During the normalisation process two or more
  relations with the same primary key may appear
• The set of 3NF relations must not contain any
  duplicate data
• Relations with the same primary key should be
  merged

37
Normalisation of Relations

• Synonyms
• Two or more attributes may have different names
  but the same meaning
• Either adopt one of the names as a standard or
  choose a third name
• STUDENT1 (Student-id, Name, Phone-no)
• STUDENT2 (VCE-no, Name, Address)
• STUDENT (Student-id, Name, Address, Phone-no)

38
Normalisation of Relations

• Homonyms
• Two or more attributes may have the same name but
  different meanings
• To resolve the conflict, new attribute names need
  to be created
• STUDENT1 (Student-id, Name, Address)
• STUDENT2 (Student-id , Name, Phone, Address)
• STUDENT (Student-id, Name, Phone,
  Campus-address, Permanent-address )

39
Normalisation of Relations

• When two 3NF relations are merged, transitive
  dependencies may result

STUDENT1 (Student-id, Major) STUDENT2
(Student-id , Advisor) STUDENT (Student-id,
Major, Advisor) But MAJOR ADVISOR
(dependency!) STUDENT (Student-id,
Major) MAJOR (Major , Advisor)
40
Data Structure Diagrams (DSD)

• A set of 3NF relations may be converted to a
  simple diagrammatic form to begin physical
  database design
• The conversion is simple
• Draw a named rectangle for each relation
• Draw a relationship line between rectangles
  linked by foreign keys with a many cardinality
  at the foreign key end of the relationship

41
Data Structure Diagrams (DSD)

• CUSTOMER (Cust, Cname,Phone number)
• SALES ORDER (Sord, Sord-date, Cust)
• SALES ORDER-ITEM (Sord, Item, Qty)
• ITEM (Item, Item-desc)

CUSTOMER
ITEM
SALES ORDER
SALES ORDER LINE
42
Data Structure Diagrams (DSD)

• Eliminate redundant relationships

TOUR
(Tourcode, ...)
DEPARTURE
redundant
(Tourcode, depdate, ...)
BOOKING
(Booking, ... , Tourcode, depdate)
43
Detailed Data Modelling - Summary

• Detailed data modelling involves
• collecting detailed attributes for each entity
  and relationship identified
• converting ER models to relations
• normalising the relations
• merging relations from each user viewpoint
• converting the normalised and merged relations to
  create a data structure diagram

44
References
Hoffer, J.A., George, J.F. Valacich, J.S.,
(2002), 3rd ed., Modern Systems Analysis and
Design, Prentice-Hall, New Jersey, Ch
10,12. Whitten, J.L., Bentley, L.D. Dittman,
K.C., (2001), 5th ed., Systems Analysis and
Design Methods, Irwin McGraw-Hill, New York, NY,
Ch 7