Teaching Concept of Organizing Data In A Relational Database / Microsoft Access - Essay Example

A suppliers table would include fields such as supplier Name, Supplier Number and Address. The field that contains a unique data value to identify each record in a table is referred to as the primary key. Where the suppliers’ names are similar a special identifier is assigned to eliminate confusion when identifying a particular supplier. Till now the SUPPLIER table does not give information about the parts that different suppliers provide. There is need to create a part table being a separate entity from the supplier, creating fields with information relating to parts in the PART table.

Keeping information on parts in the same table as suppliers would result to redundancy on data relating to suppliers making it difficult to retrieve information on particular parts supplier by particular suppliers. The PART table fields include Part Number, Part Name and Unit Price. Include a forth field for Supplier Number to identify the supplier for each part. In both the SUPPLIER and PART tables the Supplier Number is common. In the PART table the Supplier_ Number is the foreign key a look up field identifying data relating to supplier of a specific part and Part_ Number being the primary key.

When organizing data into tables; ensure all the attributes of an entity apply only to that entity (Snell, 2010). Defining data attributes Specific characteristics of entities are referred to as attributes. When operating a business one should consider the specific sets of data involved such as information on suppliers, employees, customers’ orders, products and parts. These data categories are referred to as entities. The next step is to define attributes. For example in the supplier entity attributes include Supplier Name, Supplier Number and address.

The Part entity attributes include Part Name, Unit Price and the supplier of that part (Snell, 2010). Defining relationships between tables Understanding the relationships between different entity tables is important. A representation referred to as entity relational diagram is used to simplify how tables relate in a relational database (Snell, 2010). The three main types of table relationships include one-to-one, one- to- many and many- to- many relationships. One-to-one exists when the primary record has only one related record for instance in a human resource department keeping private information of an employee such as employee name, date of birth, in one table and salary and pension benefits in another table .

With regards to the employee the data stored relates specifically to him or her exhibiting a one-to -one relationship. One-to-many is when primary records have many related records in the related table. For example one supplier can supply more than one part but each part has a specific supplier (Larson, 2009). When we introduce an ORDER table since one supplier provides many orders, one order can be for various parts from a supplier and a series of different orders for a single part this exhibits a many- to many relationship between the PART and ORDER tables.

It’s necessary to create a join table to link the two tables. Minimizing awkward many- to- many relationships and data redundancy through normalization process increases permanence and elasticity of the data (Snell, 2010).