Packages and N-ary Association
Designing Database Tables – Part 11
Forward: In this part of the series, we look at n-ary associations in packages as you design your database.
By: Chrysanthus Date Published: 5 Aug 2012
Introduction
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.
Arriving at N-ary Relationship
Open the following link in a tab window. You will be toggling between the window and this as you read.
With the package approach of designing database, you have to break down a package. In the course of breaking down a package, you may arrive at the diagram of fig. 6.1 in the tab window. This is a many-to-many-to-many relationship. That is there are two many-to-many relationships.
Normalization
To have normalized tables from these three classes you have to develop what is called an n-ary structure from them. The tables you get from the n-ary structure are in at least the 3NF. We shall see all that in this tutorial. We shall continue with the motorcycle company example. These three tables are of the Assembly Package.
In fig.6.1, the Employee table is the one we have seen in the previous parts of the series and we already know its primary key. If it did not have a primary key, you have to give one to it. Component table is the Components table we have been seeing. We already know its primary key, which is ItemID. You can call this key the CompID, but make sure you keep a note on that so that you do not forget that ItemID and CompID are the same things. The Product table is the Motorcycle table, which we have seen and we already know its primary key, which is, SerialNo. You can call it MotorcycleID or ProductID, but make a note on that.
Why do we have the many-to-many-to-many relationship? This is because in the assembly of one motorcycle, many employees can fit the parts (components). So many motorcycles are assembled by many employees. That clearly explains the many-to-many relationship between the Employee table and the Motorcycle table. Those are business rules. We have more business rules from the company, which state that one motorcycle can have many parts (components) and the same part (taking into consideration only the type of part and not the particular part) can be found in many motorcycles. There are business rules like this, which you can work out yourself but with others you have to see the employees of the company who ask you for a database.
These three tables are focus on the service of assembly in the company. This is what you do to obtain the normalized tables for the three tables (in fact you will end up with for tables). Create another table called the Assembly table. Place the primary key of each of the three tables into this new table. You should have the following tables:
Motorcycle(SerialNo, Model, WheelID, CrankID, StemID)
Employee(EmpID, Name, Address, Salary, …)
Components(ItemID, Category)
Assembly(SerialNo, EmpID, ItemID, …)
Vocabulary: A one-to-one relationship or a one-to-many relationship is called a binary relationship.
Hey! Talking about columns to add in the Assembly class. One of the columns you are sure to add into the Assembly table is the date-time column, which will show when (last date) the motorcycle was assembled.
That is it for normalization and n-ary relationship. N-ary is the last of the associations I have to treat. We stop here for this tutorial and continue in the next part of the series.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT