UNIT FOUR

LOGICAL DATABASE DESIGN

AND RELATIONAL MODEL

1
 3. Logical Database Design
• The phase to choose a DBMS to implement our database
design, and convert the conceptual database design into a
database schema.
• Consider relational DBMSs, and therefore, the task in the
logical design phase is to convert an ER schema into a
relational database schema.
• It is the phase to create the database in a given DBMS
• The following are the major activities performed in the
logical design phase:
1. Transform conceptual design criteria into relational form.
2. Transform entities into tables.
3. Transform entity attributes into table columns.
4. Transform tables and columns using rules of functional
dependencies and keys or normalization.
2
 Objectives of Logical Design...
 Translate the conceptual design into a logical database
design that can be implemented on a chosen DBMS
 Input: conceptual model (ERD)
 Output: relational schema, normalized relations
• Normalization
– Analysis of functional dependencies between data items
to result in a structure of data that is simple, stable, and
fundamental
 Resulting database must meet user needs for:
 Data sharing,
 Ease of access and
 Flexibility

3
 Logical Design: Based upon the conceptual data model,
four key steps
1. Develop a logical data model for each known user
interface for the application using normalization
principles.
2. Combine one consolidated logical database model
3. Translate the conceptual E-R data model for the
application into normalized data requirements
4. Compare the consolidated logical database design with
the translated E-R normalized data requirements from all
user interfaces into model and produce one final logical
database model for the application

4
 Logical Database Design

Relational Data Model

E-R Diagram

5
 Relational Data Model
• Data represented as a set of related tables or relations.
• A named, two-dimensional table of data.
• Each relation consists of a set of named columns and an
arbitrary number of unnamed rows.
• Relational DBMS (RDBMS) – dominant technology
• Three components of Relational data Model are:
 Data structure
– Data organized into tables
 Data manipulation
– Add, delete, modify, and retrieve using SQL
 Data integrity-Relationship
– Maintained using business rules

6
 Relation
• A relation is a named, two-dimensional table of data
• Table consists of rows (records), and columns (attribute or
field)
• Requirements for a table to qualify as a relation:
 It must have a unique name.
 Every attribute value must be atomic (not multivalued,
not composite)
 Every row must be unique (can’t have two rows with
exactly the same values for all their fields)
 Attributes (columns) in tables must have unique names
 The order of the columns must be irrelevant
 The order of the rows must be irrelevant
 NOTE: The word relation (in relational database) is
NOT the same as the word relationship (in E-R
model)
7
 Key Fields
• Relational modeling uses primary keys and
foreign keys to maintain relationships
– Primary keys are unique identifiers of the relation in
question.
 Examples include employee numbers, social security
numbers, etc. This is how we can guarantee that all rows
are unique
– Foreign keys are identifiers that enable a dependent
relation (on the many side of a relationship) to refer to
its parent relation (on the one side of the relationship)
• Keys can be simple (a single field) or composite
(more than one field).
• However in logical database design the key must
be atomic.
8
Primary Key

Foreign Key (implements

1:N relationship between
customer and order)

Combined, these are a composite primary

key (uniquely identifies the order line)…
individually they are foreign keys
(implement M:N relationship between
order and product)

9
 Constraints
 Domain constraints
– Allowable values for an attribute as defined in the
domain
 Entity integrity constraints
– No primary key attribute may be null
 Operational constraints
– Business rules
 Referential integrity constraints

10
 Integrity Constraints
• Referential Integrity – rule that states that any
foreign key value (on the relation of the many
side) MUST match a primary key value in the
relation of the one side. (Or the foreign key can be
null)
– For example: Delete Rules
• Restrict – don’t allow delete of “parent” side if
related rows exist in “dependent” side
• Cascade – automatically delete “dependent” side
rows that correspond with the “parent” side row to
be deleted
• Set-to-Null – set the foreign key in the dependent
side to null if deleting from the parent side  11not
Figure 4.5 Examples of Referential integrity constraints

 Referential integrity
constraints are drawn via
arrows from dependent to
parent table

12
Figure 4.6 SQL Table Definitions

Referential integrity
constraints are
implemented with
foreign key to primary
key references

13
 Transforming E-R Diagrams into Relations
• It is useful to transform the conceptual data
model into a set of normalized relations
• Steps
1. Represent Entities
2. Represent Relationships
3. Normalize the Relations
4. Merge the Relations
 Transforming ER Diagrams into Relations
 Mapping Entities
 Regular entities: simple, composite, and multivalued
attributes
 Weak entities
 Associative entities
 Mapping Relationships
 Unary, binary, ternary
 Supertype / subtype
 One-to-one, one-to-many, many-to-many
1) Mapping Regular Entities to Relations
A) Simple Attributes: E-R attributes map directly onto the
relation
B) Composite Attributes: Use only their simple, component
attributes
C) Multivalued Attribute - Becomes a separate relation with
15
a foreign key taken from the superior entity
4.7(a) Mapping CUSTOMER entity type with simple attributes

4.7(b) CUSTOMER relation

16
 Figure 4-8: Mapping a composite attribute

(a) CUSTOMER
entity type with
composite attribute

(b) CUSTOMER relation with address detail

17
 Figure 4-9: Mapping a Multivalued Attribute
(a)

Multivalued attribute becomes a separate relation with foreign key

(b)

1–to–many relationship between original entity and new relation

18
2) Mapping Weak Entities
– Becomes a separate relation with a foreign key
taken from the superior entity
– Primary key composed of:
• Partial identifier of weak entity
• Primary key of identifying relation (strong entity)

19
Figure 4.10: Examples of mapping a Weak Entity-
Week Entity DEPENDENT

20
Figure:4.11: Examples of Mapping a Weak Entity-
Relations resulting from a Weak Entity

21
3) Mapping Binary Relationships
– One-to-Many - Primary key on the one side
becomes a foreign key on the many side
– Many-to-Many - Create a new relation with the
primary keys of the two entities as its primary
key
– One-to-One - Primary key on the mandatory
side becomes a foreign key on the optional side

22
 Figure 4-12a: Example of mapping a 1:M relationship
Relationship between customers and orders

Note the mandatory one

23
Figure 4-12b Mapping the relationship

Again, no null value in the

foreign key…this is because
of the mandatory minimum
cardinality

Foreign key

24
 Figure 4-13a: Example of mapping an M:N relationship
E-R diagram (M:N)

The Supplies relationship will need to become a separate relation

25
Figure 4-13b Three resulting relations

Composite primary key

New
Foreign key intersection
relation
Foreign key

26
Figure 4-14a: Mapping a binary 1:1 relationship
In_charge relationship

27
Figure 4-14b Resulting relations

28
4) Mapping Associative Entities
 Identifier Not Assigned
• Default primary key for the association relation
is composed of the primary keys of the two
entities (as in M:N relationship)
 Identifier Assigned
• It is natural and familiar to end-users
• Default identifier may not be unique

29
Figure 4.15a : Mapping an Associative Entity-

30
Figure 4.15b:Mapping an Associative Entity: Three
resulting Relations

Identifier Not Assigned

31
Figure 4-16a: Mapping an associative entity with an identifier
Associative entity

32
Figure 4-16b Three resulting relations

Identifier Assigned
33
5) Mapping Unary Relationships
– One-to-Many - Recursive foreign key in the same
relation
– Many-to-Many - Two relations:
• One for the entity type
• One for an associative relation in which the
primary key has two attributes, both taken from
the primary key of the entity

34
 Figure 4-17: Mapping a unary 1:N relationship

(a) EMPLOYEE entity with

Manages relationship

(b) EMPLOYEE relation with recursive foreign key 35

 Figure 4-18: Mapping a unary M:N relationship

(a) Bill-of-materials
relationships (M:N)

(b) ITEM and

COMPONENT relations

36
6) Mapping Ternary (and n-ary) Relationships
 One relation for each entity and one for the
associative entity
 Associative entity has foreign keys to each
entity in the relationship

37
Figure 4-19a: Mapping a ternary relationship
Ternary relationship with associative entity

38
Figure 4-19b Mapping the ternary relationship

Remember that the primary

key MUST be unique

39
 7) Mapping Supertype/Subtype Relationships
• One relation for supertype and one for each
subtype
• Supertype attributes (including identifier and
subtype discriminator) go into supertype relation
• Subtype attributes go into each subtype; primary
key of supertype relation also becomes primary
key of subtype relation
• 1:1 relationship established between supertype
and each subtype, with supertype as primary table

40
Figure 4-20:
Supertype/subtype
relationships

41
 These are implemented
as one-to-one
relationships

Fig. 4.21 Mapping Supertype/subtype relationships to relations 42

 Redundant Information
 One of the rules of good database design is that data not
be redundant or not be duplicated in the same database.
 The rationale for this is that:
i) If data appears more than once, then there is reason to
believe that one of the pieces of data is likely to be in
error.
ii) Another thing to watch for is the appearance of too
many columns containing null values.
 When this occurs, the database is probably not designed
properly.
 To alleviate potential table design issues, a process
referred to as normalizing is performed.
 When properly done, this ensures the complete absence
of redundant information in a table. 43
 Redundancy in a relation
 Redundant (i.e., “unnecessary”) information occurs in a
relation if the same fact is repeated in several different tuples.
 One obvious problem with redundant information is that we
use more memory than is necessary.
 Redundancy is an example of an anomaly of the relational
schema.
 Example: Instance of Movies(title, year, length, filmType,
studioName, starName) where the length of a movie is repeated
several times.
 The other principal kinds of unwanted anomalies are:
1) Update Anomalies.
 A problem experienced when one or more data value is
modified on one application program but not one others
containing the same data set..
 (E.g., the length of Star Wars in the Movies relation.) 44
2) Deletion Anomalies.
 A problem encountered where one record set is deleted
from one application but remain untouched in other
application programs. OR
 Occur when deleting a tuple (recording some fact) may
delete another fact from the database.
 (E.g., information on a movie in the Movies relation.)
3) Insertion Anomalies.
 A problem experienced whenever there is a new data item
to be recorded, and the recording is not made in all the
applications.
Anomalies and good design
 As we will see, anomalies are a sign that we tried to
encode several, unrelated types of facts into a single
relation. 45
 Thus, normalization can help improving the design
such that the un relatedness of these facts are
captured by the database schema (and E-R
diagram).
 The anomalies in the example we saw can be
eliminated by splitting (or decomposing) the
relation schema:
 Movies(title, year, length, filmType, studioName,
starName) into two relation schemas

 Movies1(title, year, length, filmType, studioName)

 Movies2(title, year, starName) 46

 Keys
 The relational data model uses keys to define identifiers for a
relation’s tuples.
 The keys are used to enforce rules and/or constraints on database
data.
 Those constraints are essential for maintaining data consistency
and correctness.
 Relational DBMS permits definition of such keys, and starting with
this point the relational database management system is responsible to
verify and maintain the correctness and consistency of database
1) Candidate Keys
 A candidate key is a unique identifier for the tuples of a relation.
 By definition, every relation has at least one candidate key (the first
property of a relation).
 In general, a relation schema may have more than one key.
 In this case, each of the keys is called a candidate key.
 For example, the CAR relation has two candidate keys: 47
 Given the following definition for a candidate key:
 Let R be a relation with attributes A1, A2, …, An.
 The set of K=(Ai, Aj, …, Ak) of R is said to be a candidate key
of R if and only if it satisfies the following two time-
independent properties:
1) Uniqueness
 At any given time, no two distinct tuples of R have the same
value for Ai, the same value for Aj, …, and the same value for
Ak.
2) Minimality
 None of Ai, Aj, …, Ak can be discarded from K without
destroying the uniqueness property.
 Every relation has at least one candidate key, because at least the
combination of all of its attributes has the uniqueness property
(the first property of a relation), but usually exist at least one
other candidate key made of fewer attributes of the relation. 48
Fig: CARS Relation – Header with attributes and body with
tuples 49
 For example, the CARS relation shown in the previous slide
has only one candidate key K=(Type, Producer, Model,
FabricationYear, Color, Fuel) considering that we can have
multiple cars with the same characteristics in the relation.
 Nevertheless, if we create another relation CARS for example
like as the following figure by adding other two attributes like
SerialNumber(engine serial number) and
IdentificationNumber (car identification number) we will
have 3 candidate keys for that relation.
 A candidate key is sometimes called a unique key.
 A unique key can be specified at the Data Definition
Language (DDL) level using the UNIQUE parameter
beside the attribute name.
 If a relation has more than one candidate key, the one that
is chosen to represent the relation is called the primary key,
and the remaining candidate keys are called alternate keys. 50
• Figure – The new CARS Relation and its candidate
keys 51
 2. Primary Keys
 A primary key is a unique identifier of the relation tuples.
 As mentioned already, it is a candidate key that is chosen
to represent the relation in the database and to provide a
way to uniquely identify each tuple of the relation.
 A database relation always has a primary key.
 Relational DBMS allow a primary key to be specified the
moment you create the relation (table).
 The DDL sublanguage usually has a PRIMARY KEY
construct for that.
 For example, for the CARS relation from the previous
figures (figure having 3 candidate keys) the primary key
will be the candidate key IdentificationNumber.
 This attribute values must be “UNIQUE” and “NOT
NULL” for all tuples from all relation instances. 52
 3. Foreign keys
 A foreign key is an attribute (or attribute combination) in
one relation R2 whose values are required to match those
of the primary key of some relation R1 (R1 and R2 not
necessarily distinct).
 Note that a foreign key and the corresponding primary
key should be defined on the same underlying domain.
 For example, in the following figure, we have another
relation called OWNERS which contains the data about
the owners of the cars from relation CARS.
 The IdentificationNumber foreign key from the
OWNERS relation refers to the IdentificationNumber
primary key from CARS relation.
 In this manner, we are able to know which car belongs to
each person. 53
• Figure – The new CARS Relation and its candidate keys

54
 Functional Dependencies

 A functional dependency is a constraint between two sets of

attributes from the database.
 Functional Dependency hold when:
i) The value of one attribute (the determinant) determines the
value of another attribute.
ii) Among the candidate keys in a relational schema, one of the candidate
keys will become the primary key
iii) Each non-key field is functionally dependent on every candidate key.
 The concepts of functional dependencies is the basis for the first
three normal forms.
 A column, Y, of the relational table R is said to be functionally
dependent up on column X of R:
 If and only if each value of X is associated with precisely one value
of Y at any given time. (X identify the values of column Y).
OR
 When values of attribute B can be derived from the attributes
A1, . . . ,An we say that B is functionally dependent on A1, . . . ,An.
 This is written as follows: A1, A2 . . .An → B 55
 Definition: A functional dependency, denoted by X Y, between
two sets of attributes X and Y that are subsets of R specifies a
constraint on the possible tuples that can form a relation state r of
R.
 The constraint is that, for any two tuples tl and t2 in r that have
t1[X] = t2[X], they must also have t1[Y] = t2[y] .
 This means that the values of the Y component of a tuple in r
depend on, or are determined by, the values of the X component;
alternatively, the values of the X component of a tuple uniquely
(or functionally) determine the values of the Y component.
 We also say that there is a functional dependency from X to Y, or
that Y is functionally dependent on X.
 The abbreviation for functional dependency is FD or f.d.
 The set of attributes X is called the left-hand side of the FD, and Y
is called the right-hand side.
 Thus, X functionally determines Y in a relation schema R if, 56and
```````````````````````````````````````~```
only if, whenever two tuples of r(R) agree on their X-value, they
````````````````````````````````````````````
````````````````````````````````````````````
 Note the following:
i) If a constraint on R states that there cannot be more than
one tuple with a given X-value in any relation instance r(R)-
that is, X is a candidate key of R.
 This implies that X Y for any subset of attributes Y of R
(because the key constraint implies that no two tuples in any
legal state r(R) will have the same value of X).

ii) If X Y in R, this does not say whether or not Y X in R.

 A functional dependency is a property of the semantics or
meaning of the attributes.
 Whenever the semantics of two sets of attributes in R
indicate that a functional dependency should hold, we
specify the dependency as a constraint.
 Relation extensions r(R) that satisfy the functional
dependency constraints are called legal relation states (or
57
 Consider the relation schema EMP_PROJ in the
following figure,in the next slide, from the
semantics of the attributes, we know that the
following functional dependencies should hold:

58
Fig 4.22: Two relation schemas Suffering from update
anomalies 59
 The functional dependencies of the above figure specify that:
a) The value of an employee's social security number (SSN)
uniquely determines the employee name (ENAME),
b) The value of a project's number (PNUMBER) uniquely
determines the project name (PNAME) and location
(PLOCATION), and
c) A combination of SSN and PNUMBER values uniquely
determines the number of hours the employee currently works on
the project per week (HOURS).
 Alternatively, we say that ENAME is functionally determined by
(or functionally dependent on) SSN, or "given a value of SSN, we
know the value of ENAME," and so on.
 A functional dependency is a property of the relation schema R,
not of a particular legal relation state r of R.
 Hence, a FD cannot be inferred automatically from a given relation
extension r but must be defined explicitly by someone who knows
60
 Normalization
 Designing an optimal database design is an important element of
database operations.
 It is also critical in achieving maximum performance and flexibility
while working with tables and data.
 Used to minimize errors and duplication of data, database developers
apply a concept called normalization to a logical database design.
 The normalization process generally involves splitting larger
multicolumn tables into two or more smaller tables containing fewer
columns.
 The rationale for doing this is found in a set of data design guidelines
called normal forms.
 The guidelines provide designers with a set of rules for converting one
or two large database tables containing numerous columns into a
normalized database consisting of multiple tables and only those
columns that should be included in each table.
 The normalization process typically consists of no more than five steps
with each succeeding step subscribing to the rules of the previous steps
61
but normalizing up to 3NF is enough.
 Normalizing a database helps to ensure that the database
does not contain redundant information in two or more of
its tables.
 As database designers and analysts proceed through the
normalization process, many are not satisfied unless a
database design is carried out to at least third normal form
(3NF).
 Whatever your preference, you should keep this in mind as
you normalize database tables.
 A fully normalized database often requires a greater
number of joins and adversely affects the speed of
queries.
 As Celko in his book mentions that the process of joining
multiple tables is costly, specifically affecting processing
time and computer resources. 62
 Normalization:
 is a formal process for deciding which attributes should be
grouped together in a relation
 is a process for evaluating and correcting table structures to
minimize data redundancies and helps to eliminate data
anomalies.
 Objective: To validate and improve a logical design so
that it satisfies certain constraints that avoid unnecessary
duplication of data
 Generally, it is the process of decomposing relations with
anomalies to produce smaller, well-structured relations
 It is a two-step process:
i) Puts data in a tabular form
ii) Removes duplicated data from the relational tables
 Normalization theory is based on concepts of normal forms. 63
 A well structured relation is a relation that contains:
i) Minimal data redundancy and
ii) Allows users to insert, delete, and update rows without
causing data inconsistencies
 The goals of normalization is to avoid anomalies:
 Insertion Anomaly – adding new rows forces user to create
duplicate data
 Deletion Anomaly – deleting rows may cause a loss of data
that would be needed for other future rows
 Modification Anomaly – changing data in a row forces
changes to other rows because of duplication
 All tables in the relational database should be in the third
normal form(3NF).
 A relational table is in 3NF if and only all non-key
columns are:
 Mutually independent and
 Fully dependent up on the primary key 64
 The three Normal Forms
 The concepts of functional dependencies is the
basis for the first three normal forms as we
discussed previously.
 A column, Y, of the relational table R is said to be
functionally dependent up on column X of R if
and only if each value of X is associated with
precisely one value of Y at any given time.(X
identify the values of column Y).
R.x R.y
 Column X functionally determines (defines) column Y
Full functional dependence applies to tables with
composite keys.
65
 Column Y in relational table R is fully functional on X
of R :
i) If it is functionally dependent on X and
ii) Not functionally dependent up on any subset of X.
 Let’s discuss the three types of normal forms based on
the following example: For example,
 A company obtains parts from a number of suppliers.
 Each supplier is located in one city.
 A city can have more than one supplier located there
 And each city has a status code associated with it.
 Each supplier may provide many parts.
 The company creates a simple relational table to store
this information that can be expressed in relational
notations as follows: 66
  FIRST(S#, Status, City,P#, qty), where
S# Supplier identification number (this is
primary key)
Status Status code assigned to city
City Name of city where supplier is located
P# Part number of part supplied
qty Quantity of parts supplied to date

 In order to uniquely associate quantity supplied

(qty) with part (p#) and supplier(s#), a
composite primary key composed of s# and p# is
used. 67
 1. First Normal Form
 First normal form (INF), defined to disallow multivalued
attributes, composite attributes, and their combinations.
 A relational table, by definition, is in the first normal
form:
 If all values of the columns are atomic. That is, they
contain no repeating values.
 The following table (FIRST table) shows in the 1NF.
 Although, the table FIRST is in the 1NF, it contains
redundant data.
 For example, information about the supplier’s location
and the location’s status has to be repeated for every part
supplied.
 Redundancy causes what are called update anomalies.
68
 FIRST TABLE
S# Status City P# qty
S1 20 London P1 300
S1 20 London P2 200
S1 20 London P3 400
S1 20 London P4 200
S1 20 London P5 100
S1 20 London P6 100
S2 10 Paris P1 300
S2 10 Paris P2 400

S3 10 Paris P2 200

S4 20 London P2 200

S4 20 London P4 300

S4 20 London P5 400
69
 Update anomalies are problems that arise when
information is inserted, deleted, or updated.
 For example, the following anomalies could
occur in the FIRST table:
a) INSERT:-The fact that a certain supplier (S5) is
located in particular city(Athens) can not be
added until they supplied a part.
b) DELETE: If a row is deleted, then not only is the
information about quantity and part lost but also
information about the supplier.
c) UPDATE : If supplier s1 moved from London to
New York, then six rows would have to be
updated with this new information. 70
 2. Second Normal Form
 The definition of second normal form states that only tables with
composite primary key can be in 1NF but not in 2NF.
 A relational table is in the 2NF if it is in 1NF and every non-key
column is fully dependent up on the primary key.
 That is, every non-key column must be dependent upon the entire
primary key.
 FIRST is in 1NF but not in 2NF because status and city are
functionally dependent up on only on the columns s# of the
composite key(s#,p#).
 This can be illustrated by listing the functional dependencies in the
table:

 The process for transforming a 1NF table to 2NF is done as

follows: 71
a) Identify any determinants other than the composite key and the
columns they determine.
 For example, the determinant, City and that determine the status of the
city i.e., are outside the composite key
 And S#, P# are composite determinant that determines the quantity that the
suppliers are supplied.
b) Create and name a new table for each determinant and the unique
columns it determines.
c) Move the determined columns from the original table to the new table.
 The determinate becomes the primary key of the new table.
d) Delete the columns you just moved from the original table except for the
determinate which will serve as a foreign key
e) The original table may be renamed to maintain semantic meaning.
 Therefore, to transform FIRST in to 2NF we move the columns s#, status,
and city to a new table called SUPPLIERS.
 The column s# becomes the primary key of this new table.
 As a result the table FIRST is decomposed in to two tables (SUPPLERS
72
and PARTS)
 PARTS
SUPPLIERS S# P# qty
S# Status City S1 P1 300
S1 20 London S1 P2 200
S2 10 Paris S1 P3 400
S3 10 Paris S1 P4 200
S4 20 London S1 P5 100
S5 30 Athens S1 P6 100
S2 P1 300
S2 P2 400
S3 P2 200
S4 P2 200
S4 P4 300
S4 P5 400

73
 The above Tables are in the 2NF but not in 3NF
still contain modification anomalies.
 In the example of SUPPLIERS, they are:
 INSERT: The fact that a particular city has a
certain status (Rome has a status of 50) can not be
inserted until there is a supplier in the city
 DELETE: Deleting any row in Supplier destroys
the status information about the city as well as the
association between supplier and city.
.

74
 3. Third Normal Form

 The third normal form requires that all columns in a

relational table are dependent only upon the primary
key.
 The more formal definition is: A relational table is in
third normal form if it is already in 2NF and every non-
key column is non transitively dependent upon its
primary key.
 In other words, all non key attributes are functionally
dependent only upon the primary key.
 The Table PARTS is already in the 3NF because non-key
column, qty, is fully dependent up on the primary
key(s#,p#).
 SUPPLIERS is in 2NF but not in 3NF because it
contains a transitive dependency.
 A transitive dependency occurs when a non-key column
that is a determinant of the primary key is the
determinate of other columns. 75
 A relation schema is in 3rd normal form (3NF) if any functional
dependency among its attributes is either unavoidable, or has a member
of some candidate key on the right hand side.
 In other words, a relation is in 3NF if there are no unavoidable
functional dependencies among non-candidate key attributes.
 It is generally a relation in the 2NF PLUS no transitive dependencies
(functional dependencies on non-primary-key attributes).
 Note: A dependency is called transitive, because the primary key is a
determinant for another attribute, which in turn is a determinant for a
third attribute.
 What is the Solution?
 Non-key determinant with transitive dependencies go into a new table;
and
 Non-key determinant becomes primary key in the new table and stays
as foreign key in the old table.

76
 The concept of transitive dependency can be illustrated by showing the
functional dependencies in SUPPLIERS table as follows:
 SUPPLIERS.s# SUPPLIERS .status
 SUPPLIERS.s# SUPPLIERS.city
 SUPPLIERS.city SUPPLIERS .status
 Note that SUPPLIERS.status is determined both by the primary key
s# and the non-key column city.
 The process of transforming a table in to 3NF is:
a) Identify any determinants, other than the primary key and the column
they determine.
b) Create and name a new table for each determinant and the unique
columns it determines
c) Move the determined columns from the original table to the new
table.
d) Delete the column you just moved from the original table except for
the determinate which will serve as a foreign key.
77
e) The determinate becomes the primary key of the new table.
 To transform SUPPLIERS in to 3NF, we create a new table
called CITY_STATUS and move the columns city and
status in to it.
 Status is deleted from the original table, city is left behind
to serve as a foreign key to CITY_STATUS, and the
original table is renamed to SUPPLIER_CITY to reflect its
semantic meaning .
CITY_STATUS
SUPPLIER_CITY City Status
s# City
London 20
S1 London
S2 Paris Paris 10

S3 Paris Athens 30
S4 London
Rome 50
S5 Athens
78
 The result of putting the original table in to 3NF
has created three tables.
 PARTS(s#,P#,qty)-Primary Key(s#,p#), foreign
key(s#) references SUPPLIER_CITY.s#
 SUPPLIER_CITY(s#,city)-primary key(s#),
foreign key(city) references CITY_STATUS.city
 CITY_STATUS(city, status) primary key (city)

79
 SUPPLIER_CITY PARTS
s# City S# P# qty
S1 London S1 P1 300
S2 Paris S1 P2 200
S3 Paris S1 P3 400
S4 London S1 P4 200
S5 Athens S1 P5 100
S1 P6 100
S2 P1 300
S2 P2 400
S3 P2 200
CITY_STATUS
City Status S4 P2 200

London 20 S4 P4 300

Paris 10 S4 P5 400

Athens 30
Rome 50

80