I. Analyzing Information Needs
The types of information that Molly Mackenzie Boat Marina requires in its business operations and decision making include basic customer information, information about the rates, information rental properties, information about payments, and information about property reservations. Basic customer information include customer identification number (ClD), last name, first name, street address, city, state, zip, and phone number. The information about rates includes rate codes, rate descriptions, and discount percentages. The information about rental properties includes property identification number (PID) and rental price. The payment information entails the payment ID, customer identification number, payment date, payment amount, and comments. Some of the rental property reservation information that the business requires includes reservation ID, reservation date, dooking date, end date, pick up date, rate code, and property identification number.
The information described above plays a key role in the business. For instance, tracking information about the frequent customers will enable the company to reward the loyal customers via discounts. Also, keeping information about rental property and reservation enables the company to determine the boats that have been booked and those that are available. Such information is critical because it helps the business to avoid customer dissatisfaction. In light of this, a customer will tend to feel disappointed in case he or she books a specific boat only to arrive at the business and be told that the specific bought he booked has been taken , and therefore, he or she should take another one.
Delegate your assignment to our experts and they will do the rest.
II. Design
The database will be designed using MS Access application. However, it should be noted that the business owners will be exporting data from the MS Access database for analysis using Excel. In this consideration, both the Access and Excel files will comprise of five tables, namely Customer, Rate, Property, Payment, and Reservation tables. The Customer table will store basic customer information such as customer identification number (ClD), last name, first name, street address, city, state, zip, and phone number. As the customer identification number is unique for each customer, it will serve as the primary key for the Customer table.
The Rate table stores information about the rate codes, rate descriptions, and discount percentages. As the rate code is unique for each rate code, the Rate Code field will serve as the primary key. The rate description will provide additional information about the rate code, such as daily, weekend, six-month, special discount, half price, or twelve-month. In the Excel file, the discount percentage will be multiplied by the daily rate to provide a reduced rate for the customer for a given rental property.
The Property table will store information about each of the marina's rental properties. The property identification number (PlD) will be unique for each property. Therefore, the PID will serve as the primary key. The Property table will also include rental price and description fields. The rental price field will store the daily rental price for the property. The description field will further clarify the type of property.
The Payment table will store information about each payment made by a marina customer. The Payment table will comprise of PaymentlD, customer identification number (ClD), PaymentDate, PaymentAmount, and Comment fields. The PaymentlD field will store a unique value for each payment record. As such, it will serve as the primary key for the Payment table. The CID field will associate a given payment record with the customer making the payment. The PaymentDate will store the date the payment was made. The PaymentAmount field will store the amount paid.
The Reservation table will store information about each of the marina's rental property reservations. The Reservation table will contain the following fields ReslD, customer identification number (CID), ResDate, BookingDate, EndDate, PickupTime, RateCode, and property identification number (PlD). The ReslD field will contain a unique value for each reservation and serves as the table's primary key. The CID value will be used to associate a given customer with the reservation. The ResDate field will indicate the starting date of the reservation. The BookingDate will indicate when the reservation was made. The EndDate will indicate the last day of the reservation. The PickUpTime field will indicate the time that the rental property will be picked up. The RateCode field will identify the applicable rate code for the reservation. The PID field will identify the property associated with that particular reservation. Figure 1 below shows the relationship between the tables in the database.
Figure 1 : Relationship between the database tables
III. Implementation of Access Database
