Development of a Database System Using Access - Fly Dubai - Case Study Example

Number: Lecturer: Database Management system project (MIS) This report documents the development of a database system using Access. A database management system is essential for the safe storage, processing, retrieval and analysis of organizational data. The paper documents the development of an airline company database. The first part of the report will document the analysis of the business case to identify data management and processing issues and challenges facing the organization. The second part documents the design of a solution that includes the database strategy to define the business rules that govern the relationship between entities and a data dictionary. With the help of an entity relation diagram (ERD), normalized relations are displayed using Crows Foot notation. Further, relational tables and the ERD are translated and displayed in Access with a focus on integrity and referential rules during the design of the tables. Finally, the project will perform querying processes that summarizes the key data and give justifications for them. With the help of screenshots, the database components will be shown in the report to illustrate the functionality of the database. 1. Business analysis a) Business context This is a project that entails the development of a database for an airline company referred as Fly Dubai. The company operates from Dubai and serves regions in Asian Pacific, Middle East, Africa and Europe. The system for an airline company should be up to date, accurate, secure, and responsive to frequent changes and should integrate the use of decision making and management tools for the day to day operation of the company. Airline industry comprise of a number of stakeholders which include passengers, airline employees, airport and crew service providers and government regulators among others. A database is anticipated that documents the details of all the interested parties and meets their needs effectively. This paper documents the creation of a database using database tools such as Access and subsequent management processes that ensure efficient delivery of service. b) Problem definition Fly Dubai is a no-frills airline company that is based in United Arab Emirates. The company serves low income consumers in UAE and other Asian countries. It has also developed partnerships and collaborations with other airlines operating outside UAE and into Asian Pacific regions, Europe, South America and Africa. The airline is headquartered in Dubai and is a vital transport option for UAE residents and beyond. Since the inception of the airline a few years ago, the key challenge has been the booking, scheduling and office management systems. The airline experiences outbursts at some times of the year and low season during other times. The lack of an automation system that allows the company to schedule flights optimally and serve its customers in an unmatched manner is the key problem. An automated system should has in place a reservation system, ticketing system, ticket cancellation system and flight details. Currently, the airline relies on a manual system where all the particulars including customer details flight details, reservations and cancellations are stored in spreadsheets and updated regularly. The attendant has to update the information as received from office visits, telephone conversations or email communications. The system is time consuming and tedious as the attendant has to go through the numerous spreadsheets to update information from customers, flight attendants and other involved parties. With the advent of technology, there is need to develop an integrated system that can update the details in all related systems in real time. This will be beneficial to the organization in reducing the extra operational costs and this may trickle down to the consumer. The technical system anticipated will utilize the latest tools and trends such as internet, computing resources and others to deliver a fast, accurate, reliable and user-friendly solution (Series, 2013). A system is anticipated that is fast, reliable, accurate, informative and with the capability to reserve and cancel reservations from anywhere at any time. For these reasons, the manager of the airline company approached us to develop a database system that will fulfill the needs of the airline company. Fly Dubai requires information about its customers, employees, airlines data and schedules, maintenance, customer service and others related to the operation of the company. All these services are enabled by a database management system that is secure, reliable, and responsive and integrates every aspect of the company operations. The database will be instrumental to the management of the company as they will be able to derive reports essential for decision making purposes. Also, the database will serve systems such as CRM and logistics management systems which are instrumental in developing and preserving the relationship between customers and airline operators. 2. Database model 2.1. Database design fundamentals A database development process follows two design frameworks; top-down approach and bottom-up approach. The difference between the two approaches is that the top-down approach starts from identification of data sets which leads to the subsequent identification of data elements for each of the data sets. This process involves the identification of entities followed by the entity attributes. In contrast, the bottom-up approach starts from the identification of the specific attributes and moves up to define the entities and the data elements. In this case, a top-down approach is emulated. The top down approach identifies the entities likely to be used in the database before individual attributes are defined. In this case, entities include passenger list, fleet information, flight information, concession, fare, reservations, inquiry and cancellations. There are numerous attributes defining these entities and will be presented effectively in the next section. 2.2. Database structure The process of designing the database will follow the normal software design processes. Using Microsoft Access, a database will be developed with the functional areas mapped out as indicated in the problem definition stage to design a database that meets the requirements of the users. The physical design concepts of the database including the attributes, entities and relationships at considered purely in terms of the logical structure. This database will have three compounding elements, that is customers, employees and services. Customers comprise the passengers using the airline service. Services imply the booking processes, payment systems, cancellations and inquiries. Employees are the personnel manning the inquiry offices, booking counters and cancelations (Series, 2013). 2.3. Entities in the database The number of entities apparent in the database includes: i. Passenger Passenger is related to these entities Passenger entity: It helps the airline management know the customers and their diversity so that they can tailor services to fit their needs. i. Issues that the entity support – Improper evaluation of the passenger type since wrong evaluation will lead to unsatisfied customers and vice versa. Booking entity – Booking data will inform the management of the peak and off-peak periods of bookings and help customize offers and promotions to suit the particular needs of the travelers. Booking information will also display the demographics of the travelers which facilitate delivery of customized services. i. Issue that the entity support: Provide a wrong package to a passenger and complains will be imminent ii. Employees Employees are related to the following entities: Employee: This form the core of the airline industry and comprises of all the employee information whether personal or official which are essential to be used in the running of the airline business. Employee entities are essential in scheduling fleets, departures and arrivals and performance evaluation. It also enhances day to day decision making concerning the airline business. i. Issue resolved by the entity: Wrong flight scheduling and passenger bookings. Department entity: This enables determinations of departments within the airline company and the number of employees allocated to each department according to their skills, capabilities and performance. i. Issue resolved by the entity: Inappropriate allocation of employees to certain departments and poor service delivery due to lack of appropriate skills Flight entity – helps allocating employees and resources to various fleets i. Issue solved by the entity: Insufficient flight attendants and flight resources ii. Services Services are related to the following entities: Flight entity - this helps in allocating flights to various destinations and viewing those to be allocated. i. Problem that the entity solves: Allocating a passenger a flight to a different destination Bookings – provides information on all the bookings that are received by the airline at a specific period. i. Issue solved by the entity: Avoid re-booking the same passenger to the same destination and flight Cancellation – this helps in maintaining accurate bookings for the passengers willing to travel and those not yet ready. i. Issue solved by the entity: Avoid having false bookings at a specified day and time Mode of Payment – Allows the management to realize the total revenue collected in terms of cash, check and credit from bookings at a specified period of time. i. Issue resolved by the entity: incorrect tabulation of revenue during a given period 2.4. Business rules A reservation books one seat in a specific flight at a specific date and time and must be reserved by one booking alone. An employee may serve more than one bookings and a booking may be served by a single employee An airline department can have one or more employees but each employee must be employed by only one department A passenger can transact one or more payments for reservations made and every payment must be effected by only one passenger A passenger may provide zero or one evaluations about the airlines operations buut an evaluation must be made by only one customer A travel route may attract an offer or not, and an offer may be taken by a single or many routes A reservation has only one payment in form of cash, check or credit, and a payment may be applicable for one or more reservations. 2.5. Data dictionary TABLE NAME ATTRIBUTE NAME CONTENTS TYPE FORMAT RANGE REQUIRED KEY TYPE FK REF TABLE EMPLOYEE TABLE Empl ID Emply Identity Number 1111 Long integer YES PK Empl M Name Middle Name Text xxxx 255 YES Empl L Name Last Name Text xxxx 255 YES Empl Nat Nationality Text xxxx 255 YES Empl DOB Date of birth Date/month d/m/yyyy YES Empl Gend Gender Number x 50 YES Empl Jb Desc Job description Text xxxx 255 YES Empl J date Join date Date/month d/m/yyyy YES Empl Shift Shify Text xxxx 255 YES Empl W hrs Working Hours Number 1 YES Empl Perf Performance Text 255 YES Empl Salary Salary Currency 999,999 standard YES Empl Mobil Mobile No Number 22222222 Long integer YES Empl Email email address Hyperlink xxx@xx.xxx YES Empl Decode Department code Number 11111 Long integer YES DEPART Payment Table Payment ID Paymnt identity Auto number 1 Long integer YES PK Pay REFNO Pymnt reference No. Number 111111111 Long integer YES PayTOTPAY Total Paid Currency 11,111 Long integer YES Pay TYPE Paymt typ Text xxxx pyment 225 YES BoID Booking Identi Text X1xx11 255 YES Booking PassID Passenger ID Number 1111 Integer YES Passenger BOOKING Bo ID Booking ID Number 1111 YES PK Pass ID Passenger ID Number 1111 Long integer YES Passenger Pay REFNO Pay Reference No Number 225 YES BoAppldate Booking applicable Dt Date/time d/mm/yyyy YES BoCHCKIN Booking Check in Date/time d/mm/yyyy YES BoDUR Booking Duration Number 1 YES BoRESNO Booked No of pass Number 1 225 YES BoAgerange Booking age range Memo YES BoPURP Booking Purpose Text xxx 225 YES BoFLTNO Booking Flight Number Text x111 YES FLTNO Flight Number Number 1111 Integer YES FLT Passenger PassID Passenger ID Number 1111 Long integer YES PK PassFname Passenger First name Text xxxx 225 YES PassLname Passenger Last name Text xxxx 225 YES PassGender Passenger Gender Text x 225 YES PassNAT Passenger nationality Text xxxx 225 YES PassJob Passenger job desr Text xxxx 225 YES PassEmail Passenger email Hyperlink xxx@xxx.com 225 YES PassMobile Passenger mobile Number 111111111 Long integer YES Flight Table FLTNO flight number Number x1111 YES PayREFNO Pay reference no Number 1111111 Long integer YES Payment Pass ID Passenger ID Number 1111 Long integer YES Passenger BoID Booking ID Number 1111 YES Booking FLTTYPE Flight type Text xxxx YES OFID Offer ID Number 1111 integer YES Offer Offer table OF ID Offer ID Number 1111 integer YES OFSEA Offer season Text xxxx 225 YES OF DUR Offer duration number 1 integer YES OFDESCR Offer description Text xxxx 225 YES Department table DeptID Department ID number 111 integer YES Departname Department name Text xxxx 225 YES Deptcode Department code Number 11111 Long integer YES Employee EmplID Employee ID Number 1111 Long integer YES Employee EmplNAT Employee Nationality Text xxxx 255 YES 2.6. Entity relation diagram An entity relation diagram is a graphical manifestation of entities and relationships in a database. While developing an entity relation diagram, some aspects of design such as the major business operations and relationships between them are considered. These definitions provide what is known as an entity. An entity is representation of an object in a database. The second type of definition required is the attributes. Attributes are defined by a variety of tables and storage field in the database (Series). The kind of database that will be developed using Access is synonymous with a relational database system. The entity relation diagram will be used to define processes that are translated into advanced plans that will enable designers build a database. 2.7. Relationship Table 3. Management 3.1. Relations in managing tasks The database development process will take place in a manner that will facilitate the output of a solution for the airline company. With this database, the airline company can track all the information about its customers, clients, employees and partners. Each entity comprise of an attribute and a relationship that links them together. Without creating relationships among the entities in a database, the database would be meaningless. Relationships that define this database are classified as strong and weak and each database comprise of its own primary key which might be a foreign key for another attribute. Booking is the central entity in the relationship connecting other entities such as passenger, mode of payment, flight and employee. Any booking information is relayed to passengers, employees and revenue collection points. This way, administration can know the number of passengers booking for flights on a particular day and schedule them accordingly. Peak and off-peak seasons can be predicted accurately and promotions and offers can be tailor-made to suit the particular needs of all parties. In addition, having up to date and accurate information about bookings and customer preferences aids the company in correcting common complaints and mistakes. Customer suggestion can be acted on in real time to improve service delivery and manage and modify existing strategies. The following reasons answer the question why relationships between entities and corresponding attribute of passengers, airline departments and employees are essential Tracking of flight bookings and scheduling to know exact passenger details, flight number and type, date and time of travel and destination, employees responsible among other details are essential for efficient management of the airline company. The relationship between Information on employees, department and other particulars help track down performance, areas of concern and training activities. The relationship between flight booked and offers illustrate the kind of interaction between the organization and its clients. As a matter of fact, airline offers promotional packages that are tailored at stimulating passenger loyalty. This is especially applicable during low season to entice passengers to take up flight more frequently or during high season to offer rewards. The relationship between passengers and booking offers insight on the level of satisfaction of employees. A passenger who books more than once reveals the level of satisfaction and trust offered to some degree. Another relationship that is important is that of passengers and evaluations. The airliner can avoid most of the inefficiencies by taking passenger complains and compliments seriously to understand them better. A relationship between the mode of payment and bookings reveals lots of information on revenue aggregated from all service bookings. It shows the information on how much each customer is spending on a particular flight package and since a customer can make more than one booking with differing prices, the relationship between payment and booking and passenger and booking are necessary to define payment entity. 4. Tables and attributes 4.1. Attributes The database comprises of eight entities all of which have corresponding attributes. The attributes are dependent on the type of data they hold. Table Name Attribute name Type Primary key Foreign Key Booking Bo-ID Text PK Bo- Appldat Date/time Bo-chckin Number Bo-dur Number Bo-Resno Bo-purpose Text Pass-ID Text FK Payment Pay-Refno Number PK Pay-totpay Currency Pay-type Text PassID Text FK Department Deprtment ID Text PK Depart-name Depart –no of employees Flight Flight No Number PK Flight Type Text Flight Price Currency Employee Emplo-ID Number PK Empl-Lname Text Empl –Job Text Empl-Salary Text Offer Offer-ID Number FK Offer-seas Text 4.2. Tables Booking Table The booking table is critical in this database because it manages the functionality of the airline service. It displays information on flights, their schedules, passenger details, destination and others. As indicated, the purpose, applicable date and checking time are given. This is essential for scheduling. Likewise, information on the purpose, adult age range and flight details are provided. These allow the company to offer promotions on certain flights as well as know their passengers better. Department Employees working for Air belong to various departments. The departmental division helps the management in works scheduling, performance evaluations and avoids duplication of duties. Payment Table This table contains details on payments made for the bookings. Such details as payment type, reference number, flight are provided for easy tracking and management of passenger affairs Employee table The table provides the details concerning all the employees in the organization. Employee details can be known by querying the employees table. Offer table All the offers provided by the airline are provided in this table Flight table All the flight details are captured in the flight table 5. Queries Query (1) Christmas offer The airline offers exciting prices on business passengers who travel during the month of December. The query below shows the list of passengers who booked the flight during the offer period. The query allows the airline company to generate the list of those who qualified for offers and the type of seasonal offer given. It will also help access the response from the offers. SQL view SELECT OF SEA,DATE APPLICABLE,EXECUTIVE OFFERS FROM OFFER WHERE CHRISTMAS OFFER= DE009 Query (2) Mode of payment query This query will help the company analyze the mode of payments made by its passengers. In the case where they want to introduce a credit card, card payments will be preferred and hence there is need to determine how customers are shifting. So thr administrator can query all the payments done in cash or check as follows; SQL query SELECT PAYREFNO, PAYID, PAYTOTALPAY, BOKID FROM PAYMENT WHERE P_PRICE = (CASH) FROM PAYTYPE); SELECT PAYREFNO, PAYID, PAYTOTALPAY, BOKID FROM PAYMENT WHERE P_PRICE = (CHEQUE FROM PAYTYPE); This query is used to provide offers and convince passengers to use certain type of payment. Works Cited Series, S. C. (2013). System Design and Analysis 10th edition. Rosenblat. Read More