Packages and Compositions
Designing Database Tables – Part 10
Division 2
Forward: In this part of the series, we look at composition relationships in packages as you design your database.
By: Chrysanthus Date Published: 5 Aug 2012
Introduction
We have finished with the analysis of the Purchasing package and that of the Employee Package. In this tutorial, we shall analyze the Motorcycle package. We shall also look at the Components table in the Purchasing package. By the end of this tutorial, you will realize that the Components class (table) can either be in the Motorcycle package or in the Purchasing package. Just put it in any of the packages you find convenient, but all the links (by keys) between the Components tables and its related tables should be respected.
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
Meaning of Composition Association
In a composition you have classes that ultimately become the new class. In other words you have objects that ultimately become the new object. This means that you have tables whose rows (properties), can be described as properties of a bigger table. Composition deals more with physical entities than generalization. Composition examples are usually found in factories.
We saw an example of a generalization relationship in the previous part of the series. In this tutorial we shall look at an example of a composition relationship.
Consider the factory where Motorcycles are manufactured. The ultimate object (class) is a Motorcycle. This Motorcycle is built from wheels, crank, stem, etc. This means that the classes of wheels, crank, stem, etc. ultimately form the Motorcycle class. In other words the Motorcycle object is built from the objects, wheels, crank, stem, etc. Fig 8.1 shows a class diagram for this Motorcycle composition relationship. You have to open the following link in a new browser tab to see the figure.
There are four tables in the diagram of fig10.1. You have the Motorcycle table where each row is for one Motorcycle (finished product). You have the Wheels table where each row is for a wheel (component) in the factory store. You have the Crank table where each row is for a crank (component) in the factory store. You have the Stem table where each row is for a stem in the factory store. In practice, there will be many other component tables, not just three as indicated.
In the diagram the diamonds are filled diamonds, while for the aggregate relationship, the diamond is an open diamond.
The tables for the components are linked to the Motorcycle table by the presence of their primary keys in the Motorcycle table.
Note: the primary key of the motorcycle table is SerialNo; it should be underlined as all keys. The primary keys of the component tables in this table are foreign keys.
Difference between composition and Subtypes
In a composition each small table has its own primary key. In the main table, primary keys of the small tables are attributes (column headings). However, for subtypes, there is one primary key for the main table and the small tables.
Let us look at all the four tables as one table:
Motorcycle(SerialNo, Model, WheelID, Rims, Spokes, CrankID, Weight, StemID, Weight, Size)
Each row of the motorcycle table has all information about the motorcycle and all information of the parts that make up that particular motorcycle (row of the table). When you are designing from scratch the WheelID, CrankID, and StemID IDs may not be there. I have put them there for clarity.
This table as it is, would have some null values in different portions in different rows. Some groups (columns) have to be removed to form smaller tables. The question is, will you remove the groups to form subtypes or you will remove the groups to form a composition? To answer that question, you have to ask another question: Do the groups to be removed add up to form part of a whole object (e.g. motorcycle) or they do not add up to form a whole object. If they add up to form the whole object, then use composition as we do below. If they do not add up, then use subtypes as in one of the previous parts of the series.
In this case the groups to be removed add up to form a whole object. So remove the groups; give each of the new groups a primary key (if it is not in the group). Copy the primary keys as properties in the main table. These copied primary keys in the main table are now foreign keys. Underline them in the main table with broken lines. So we have
Motorcycle(SerialNo, Model, WheelID, CrankID, StemID)
Wheel(WheelID, Rims, Spokes)
Crank(CrankID, Weight)
Stem(StemID, Weight, Size)
SerialNo is the primary key for motorcycle. Motorcycle table has the foreign keys. The small tables have their unique keys. These four tables are now in at least the 3NF.
These four tables are in the Motorcycle package. So the diagram of fig. 10.1 is in the Motorcycle package.
We saw the components table in the purchasing package in one of the previous parts of the series. Components are motorcycle parts. For simplicity, assume that the three parts above, which are the Wheel, Crank and Stem are the only parts (components) that a motorcycle has. We had the components table as,
Components(ItemID, Length, Width, Height, …)
When we were analyzing the Purchasing package we got ItemID as the primary key for Components; that is where we had the Components table. Remember that we did not really complete the Components table. So here, we have to reconcile the Components table with the above three tables. Here, Length, Width and Height in the Components table mean Size and so must be referring to the Size in the component (motorcycle part) called Stem. At this point a smart database designer may realize that even though the Wheel, Crank and Stem classes form a composition with the Motorcycle table, they form a generalization (subtypes) with the components part. Do not worry if you cannot realize that now, because I continue to prove that they form subtypes to the Components class. From the above discussion, the components table should now be:
Components(ItemID, WheelID, Rims, Spokes, CrankID, Weight, StemID, Weight, Size)
I have just place the properties of the components, Wheel, Crank, and Stem in the Components table. Assume that the workers of the motorcycle company who needs the database told us that each component has a category. For example some components are rotational, like the wheels, and some are fixed, like the stem. So, a value for category is rotational or fixed. So the attribute, category is for all components. The Components table becomes,
Components(ItemID, Category, WheelID, Rims, Spokes, CrankID, Weight, StemID, Weight, Size)
Components(ItemID, Category)
Wheel(ItemID, WheelID, Rims, Spokes)
Crank(ItemID, CrankID, Weight)
Stem(ItemID, StemID, Weight, Size)
The Wheel, Crank and Stem tables have been modified with each now having a composite primary key, and the first part of a composite key is ItemID. You can change the word ItemID in all the four tables above to ComponentID, but you will have to make a note and always remember that they are ItemID’s. The above four tables are in at least the 3NF.
You can now go to fig.10.1 and draw the Components table on the right hand side of the other tables. Modify the keys of the small tables, Wheels, Crank and Stem. Then connect the small tables to the Components table as subtypes.
The Motorcycle package has the Motorcycle table and the small tables. We discovered the Components table in the Purchasing package. The Components table is related to the Motorcycle table through the small tables. The Components table also has a relationship with the Item table in the Purchasing package. You can allow the Components table in the Purchasing package or put it in the Motorcycle package; it is your choice, but always maintain the key links.
That is it for this part of the series. We take a break and continue in the next part.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT