In the CustomerPart table, which of the following are examples of redundant
duplication of data values? Explain briefly your reasoning.
(a) The two occurrences of the customerNo value C4.
(b) The two occurrences of Carter in rows 1 and 2.
(c) The two occurrences of Carter in rows 1 and 3.
(d) The two occurrences of nut in rows 3 and 4.
(e) The two occurrences of 5 in rows 1 and 4. (2 min)
6. Under what circumstances would it not be true that the missing partDescription in
Fig. 4.2b could be deduced from the other data in this table? (2 min)
Although the redundancy in Fig. 4.2a has apparently been eliminated in the Fig. 4.2b
version, the latter is still an unsatisfactory way of structuring the data. The dash in the
fourth row means ‘I’m not going to tell you what the partDescription is, but you can
find out from somewhere else in the table’. A structure which requires such a
cumbersome interpretation is not consistent with our aim of designing simple
conceptual models.
A much more satisfactory solution is to split the table into two as in Fig. 4.3. One
table, SupplierPart1, shows which supplier supplies which part, and the other table,
Part1, contains the description of each part. Two points should be noted. Firstly, the
connection between the two tables is made by including partNo in each table. Secondly,
the row P1 bolt, which appears twice in Fig. 4.2a, can appear only once in table Part1
because of the rule that identical rows are not allowed. Convince yourself that no
information has been lost by discarding the duplicate row.
For the present, try to see intuitively how a table which can contain redundant data
should be split into tables which do not contain redundant data. Later on we will present
rules that will help you in this process.
1. The table below shows the names and qualifications of employees. Each
employee has precisely one employee number, and any given employee number is
assigned to precisely one employee (i.e. employees cannot share the same employee
Redundant vs duplicated data 41
Fig. 4.3 Elimination of redundancy by table splitting

Get Data Analysis for Database Design, 3rd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.