The Relational Model and Normalization - Database Processing: Fundamentals, Design, and Implementation - page 143

ORDER_ITEM_2

Figure 3-23
the Normalized OrDEr_
ItEM_2 relation

know ExtendedPrice we will just compute it. In fact, we can define this formula to the DBMS
and let the DBMS compute the value of ExtendedPrice when necessary. You will see how to
do this with Microsoft SQL Server 2012, Oracle Database 11 g Release 2, and MySQL 5.6 in
Chapters 10, 10A, 10B, and 10C respectively.
Using the formula, we can remove ExtendedPrice from the table. The resulting table is in
BCNF:

ORDER_ITEM_2 ( OrderNumber , SKU , Quantity, Price)

Note that Quantity and Price are no longer foreign keys. The ORDER_ITEM_2 table with
sample data now appears as shown in Figure 3-23.

Normalization example 4
Consider the following table that stores data about student activities:

STUDENT_ACTIVITY (StudentID, StudentName, Activity, ActivityFee, AmountPaid)

where StudentID is a student identifier, StudentName is student name, Activity is the name
of a club or other organized student activity, ActivityFee is the cost of joining the club or
participating in the activity, and AmountPaid is the amount the student has paid toward the
ActivityFee. Figure 3-24 shows sample data for this table.
StudentID is a unique student identifier, so we know that:

StudentID S StudentName

However, does the following functional dependency exist?

StudentID S Activity

STUDENT_ACTIVITY

Figure 3-24
Sample Data for the
StUDENt_aCtIVItY
relation