An efficient data structure design requires a thorough analysis of both the rules a business follows and the relational business objects that allow the production of output for the organization. An efficient model of data should as well require little maintenance and oversight if built properly. Designing a data model promoting data redundancy and normalization provides a design that requires minimal modification to handle. (Tupper 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
2. For defining the primary keys I split the CustomerTour 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 containing cost information to TourCost and named the other table containing the TourSelected, NumberInTour and SoldBy 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
4. For the TourCost 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 TourName,TourDate,TourSeason for the TourCost 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 CustomerID,EmployeeID,TourName,TourDate,TourSeason for the Tour table allows this table to almost act like an invoice for each tour sale. Figure 5
5. My tables were in 1NF and because I had no partial dependencies my tables were now in 2NF Proposed Naming Convention and Entity Attributes
The table names “Employee”, “Customer”, “Tour”, and “TourCost” accurately define the data represented within each table. To better associate the data in the Tour with its operational purpose I will rename to TourInvoice. For this organization I’ve gone back and renamed several entities to better associate the entity to the table it belongs to. For example the FirstName attribute in the EMPLOYEE table was renamed to EMPFirstName to associate this first name attribute with the EMPLOYEE FirstName and not Customer First Name which was changed as well. See the captures below for entity and attribute names and their data types.
Naming Convention for Employee and Customer Table
Naming Convention for TourCost and TourInvoice Table
Proposed Entity Relationship Model
Many Customers complete one invoice (TourInvoice) at the sale of a tour. One TourInvoice is assigned a PricePerson contained within the TourCost Table. PricePerson is dependent on the TourSeason. Many TourInvoices are advertised by one Employee and this number is associated to the EmpToursSold attribute.