Databases
Lecture 8 – Anomalies by bad design
Bernhard Reus
1
staffNo
lname
jobTitle
salary
branchNo
branchAddress
005
Smith
Manager
36,000
003
163 Main Street, Glasgow
007
Nail
Agent
25,200
004
22 Deer Road, London
003
Gordon
Manager
NULL
005
82 Oxford Street, London
004
Miller
Agent
20,400
003
163 Main Street, Glasgow
Data may contain redundancy caused by “bad” table design. “Anomalies” arise for DBMS operations.
Anomalies
[C&B, Ch. 13]
In this lecture:
• What are those anomalies?
• How to express redundancy via functional dependency.
2
© Bernhard Reus, University of Sussex, 2004-16
Relation “Client Rents Property”
Which client (clientNo) rents which property (propNo)
from which date (starteDate) at which address (propAddress) from which owner (ownerNo & ownerName).
3
Rents
clientNo
56
12
19
propNo
PG36
PG36
PG24
startDate
1-Sep-99
15-Oct-00
1-Aug-03
propAddress
5 Novar Rd, Glasgow
3 London Rd, Brighton
3 London Rd, Brighton
12 Ship St, Brighton
ownerNo
CO20
CO20
CO40
ownerName
76
76
PG4
PG16
1-Jul-00
1-Sep-01
6 Lawrence St, Glasgow
CO93
CO93
Tina Murphy
Tina Murphy
Chris Newbank
Chris Newbank
David Brent
Insertion Anomalies
Insert new contract for client 23 and property PG36
Insert new property with owner CO95
violates entity integrity of primary key
clientNo
propNo
startDate
propAddress
ownerNo
ownerName
76
PG4
1-Jul-00
6 Lawrence St, Glasgow
CO93
Tina Murphy
76
PG16
1-Sep-01
5 Novar Rd, Glasgow
CO93
Tina Murphy
56
PG36
1-Sep-99
3 London Rd, Brighton
CO20
Chris Newbank
12
PG36
15-Oct-00
3 London Rd, Brighton
CO20
Chris Newbank
19
PG24
1-Aug-03
12 Ship St, Brighton
CO40
David Brent
23
PG36
1-Jan-02
13 London Rd, Brighton
CO20
Chris Newbank
NULL
PG200
NULL
55 Kingsway Road
CO95
Zoe Ball
4
© Bernhard Reus, University of Sussex, 2004-16
inconsistency
Deletion Anomalies
Rents
clientNo
56
12
19
propNo
PG36
PG36
PG24
startDate
1-Sep-99
15-Oct-00
1-Aug-03
propAddress
5 Novar Rd, Glasgow
3 London Rd, Brighton
3 London Rd, Brighton
12, Ship St, Brighton
ownerNo
CO20
CO20
CO40
ownerName
76
76
PG4
PG16
1-Jul-00
1-Sep-01
6 Lawrence St, Glasgow
CO93
CO93
Tina Murphy
Tina Murphy
Chris Newbank
Chris Newbank
David Brent
Delete last rental contract of owner CO40:
Owner CO40 disappears too.
5
Modification Anomalies
Update address of Property PG36:
Have to update several rows (due to data duplication).
Rents
clientNo
56
12
19
propNo
PG36
PG36
PG24
startDate
1-Sep-99
15-Oct-00
1-Aug-03
propAddress
5 Novar Rd, Glasgow
3 London Rd, Brighton
3 London Rd, Brighton
12 Ship St, Brighton
ownerNo
CO20
CO20
CO40
ownerName
76
76
PG4
PG16
1-Jul-00
1-Sep-01
6 Lawrence St, Glasgow
CO93
CO93
Tina Murphy
Tina Murphy
Chris Newbank
Chris Newbank
David Brent
6
© Bernhard Reus, University of Sussex, 2004-16
duplication
Anomalies Summary
• Insertion
– duplication of data enforced (source of
inconsistency)
– impossible due to entity integrity
• Deletion
– leads to undesired loss of data
• Modification
– need to modify several rows (source of inconsistency)
7
Functional Dependency
“Describes the relationship between attributes in a relation. For example, if A and B are attributes of a relation, B is functionally dependent on A if each value of A is associated with exactly one value of B. (A and B may consist of one or several attributes)”
• Functional Dependency is a property of the meaning of the attributes in a relation (semantics).
8
© Bernhard Reus, University of Sussex, 2004-16
Functional Dependency
• All attributes of a schema are functionally dependent on any key of the schema.
• Dependencies are on the level of one particular Relation Schema.
• A®B is short for a functional dependency of B on A.
9
Functional Dependency Example
• For every row where (clientNo,propNo) is (76,PG4), the start date is unique, namely 1-Jul-00; for every row where (clientNo,propNo) is (56,PG36), the start date is unique, namely 1-Sep-99 and so on.
• So every client rents any given property only once . Dependency: {clientNo,propNo} ® startDate
clientNo
propNo
startDate
propAddress
ownerNo
ownerName
76
PG4
1-Jul-00
6 Lawrence St, Glasgow
CO93
Tina Murphy
76
PG16
1-Sep-01
5 Novar Rd, Glasgow
CO93
Tina Murphy
56
PG36
1-Sep-99
3 London Rd, Brighton
CO20
Chris Newbank
12
PG36
15-Oct-00
3 London Rd, Brighton
CO20
Chris Newbank
19
PG24
1-Aug-03
12 Ship St, Brighton
CO40
David Brent
10
© Bernhard Reus, University of Sussex, 2004-16
Functional Dependency (cont’d)
• Is there a functional dependency
clientNo ® startDate
stating that every client will rent only once?
• The table says we do not have this functional dependency as there are two different startDates
(1-Jul-00 & 1-Sept-01) for clientNo 76.
clientNo
propNo
startDate
propAddress
ownerNo
ownerName
76
PG4
1-Jul-00
6 Lawrence St, Glasgow
CO93
Tina Murphy
76
PG16
1-Sep-01
5 Novar Rd, Glasgow
CO93
Tina Murphy
56
PG36
1-Sep-99
3 London Rd, Brighton
CO20
Chris Newbank
12
PG36
15-Oct-00
3 London Rd, Brighton
CO20
Chris Newbank
19
PG24
1-Aug-03
12 Ship St, Brighton
CO40
David Brent
11
Functional Dependency (cont’d)
• Does a functional dependency propNo ® startDate exist according to this table?
• For propNo PG36 there are two different start dates (1-Sep-99 and 15-Oct-00) so there is no such functional dependency.
12
clientNo
propNo
startDate
propAddress
ownerNo
ownerName
76
PG4
1-Jul-00
6 Lawrence St, Glasgow
CO93
Tina Murphy
76
PG16
1-Sep-01
5 Novar Rd, Glasgow
CO93
Tina Murphy
56
PG36
1-Sep-99
3 London Rd, Brighton
CO20
Chris Newbank
12
PG36
15-Oct-00
3 London Rd, Brighton
CO20
Chris Newbank
19
PG24
1-Aug-03
12 Ship St, Brighton
CO40
David Brent
© Bernhard Reus, University of Sussex, 2004-16
Full Functional Dependency
full dependency:
A ® B is full,
if B does not functionally depend on any proper subset of A.
{clientNo,propNo} ® startDate
clientNo ® startDate propNo ® startDate
full functional dependency
13
More Functional Dependencies
Rents
clientNo
56
12
19
propNo
PG36
PG36
PG24
startDate
1-Sep-99
15-Oct-00
1-Aug-03
propAddress
5 Novar Rd, Glasgow
3 London Rd, Brighton
3 London Rd, Brighton
12 Ship St, Brighton
ownerNo
CO20
CO20
CO40
ownerName
76
76
PG4
PG16
1-Jul-00
1-Sep-01
6 Lawrence St, Glasgow
CO93
CO93
Tina Murphy
Tina Murphy
Chris Newbank
Chris Newbank
David Brent
{clientNo,propNo} ® startDate propNo ® propAddress ownerNo ® ownerName propNo ® ownerNo
Note that all are full functional dependencies. Why?
14
© Bernhard Reus, University of Sussex, 2004-16
Full Dependencies Diagram
{clientNo,propNo} ® startDate
propNo ® {propAddress,ownerNo,ownerName}
ownerNo ® ownerName
{clientNo,startDate} ® {propNo,propAddress,ownerNo,ownerName} {propNo,startDate} ® clientNo
clientNo
propNo
startDate
propAddress
ownerNo
ownerName
Responsible for anomalies shown
in examples
15
Full Dependencies Diagram
• In the above example we have a (minimal) set of full functional dependencies from which all functional dependencies can be derived.
• By merging left and right hand sides: (see next slide)
16
© Bernhard Reus, University of Sussex, 2004-16
Derive Functional Dependencies From full functional dependencies:
{clientNo,propNo} ® startDate
propNo ® {propAddress,ownerNo,ownerName} {propNo,startDate} ® clientNo
we can derive more functional (not necessarily full) dependencies merging left hand sides and right hand sides, respectively:
{clientNo,propNo } ® {startDate,propAddress,ownerNo,ownerName} {propNo,startDate} ® {clientNo,propAddress,ownerNo,ownerName}
Attention: now not all attributes are fully functionally dependent.
17
Quiz
• What are the (full) functional dependencies for schema BranchStaff below which describes which staff works at which branch?
• What is a good primary key for BranchStaff? BranchStaff (staffNo, brNo, brAddress, name, position, salary,
employment_date)
primary key {staffNo,brNo}
staffNo ® {name, position(?), salary(?), employment_date(?)}
brNo ® {brAddress}
{staffNo,brNo} ® { position , employment_date } (?) position® salary 18
© Bernhard Reus, University of Sussex, 2004-16
Quiz
• How can the functional dependencies identify redundancy which leads to anomalies? {discussed next lecture}
BranchStaff (staffNo ,brNo, brAddress, name, position, salary, employment_date)
primary key {staffNo,brNo}
staffNo ® {name, position, salary ,employment_date} brNo ® {brAddress}
{staffNo,brNo} ® { position , employment_date}
Suggests that tables should be split
19
© Bernhard Reus, University of Sussex, 2004-16