Designing a data model promoting data redundancy and normalization provides a design that requires minimal modification o handle. (Tipper 2011) For your organization I propose the following normalization steps using the table information provided to achieve normalized data table structures. 1. Figure 1 and Figure 2 represent the data in tabular format with no cells having a null value and no repeating groups. The primary keys haven’t yet been identified. Figure 1 Figure 2 2. For defining the primary keys I split the Customers table into two tables Customer and Tour respectively.
But I already had a Tour table at this time containing Tour cost information. Because of this I renamed this Tour table intonating cost information to Tourist and named the other table containing the Turtledoves, Numberless and Solely attributes to Tour as shown in Figures 3, 4, and 5. 3. After the primary keys were defined each attribute for each table was then dependent on the primary key for that table respectively. For the Customer and Employee tables in Figure 3 single attribute primary keys were used to uniquely identify each customer and employee.
Figure 3 Employee and Customer Table Figure 4 Tourist Table 4. For the Tourist table in Figure 4 the primary key that I chose I made composite to uniquely identify the attributes related to pricing. The composite primary key Tournament,Outrage,Treason for the Tourist table allows unique data entry for each tour location, on any date, during any season. For the Tour table in Figure 5 the primary key that I chose I made composite to uniquely identify the attributes related to the purchase of a tour.
The composite primary key Customer,Employed,Tournament,Outrage,Treason for the Tour table allows this table to almost act like an invoice for each tour sale. Figure 5 Tour Table 5. My tables were in INFO and because I had no partial dependencies my tables were now in INFO Proposed Naming Convention and Entity Attributes The table names “Employee”, “Customer”, “Tour”, and “Tourist” accurately define the data represented within each table. To better associate the data in the Tour with its operational purpose I will rename to Turnover.
For this organization Vive gone back and renamed several entities to better associate the entity to the table it belongs to. For example the Firestone attribute in the EMPLOYEE table was renamed to Manifested to associate this first name attribute with the EMPLOYEE Firestone and not Customer First Name which was changed as well. See the captures below for entity and attribute names and their data types. Figure 6 Naming Convention for Employee and Customer Table Figure 7 Naming Convention for Tourist and Turnover Table Proposed Entity Relationship Model Figure 8 Many Customers complete one invoice (Turnover) at the sale of a tour.