Tour Operator Agency Database
Tour Operator Agency Database
Jeremy Clark
Dr. Larisa Bulysheva
CIS 515 – Strategic Plan For Database Systems
August 14, 2013
Tour Operator Agency Database
Week 6 Assignment 5 Mike Jennings
Enhanced Database Management Strategy Proposal 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
Figure 2
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
Figure 4
TourCost 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
Jeremy Clark
Dr. Larisa Bulysheva
CIS 515 – Strategic Plan For Database Systems
August 14, 2013
Tour Operator Agency Database
Week 6 Assignment 5 Mike Jennings
Enhanced Database Management Strategy Proposal 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
Figure 2
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
Figure 4
TourCost 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