A major airline company decided to hire you in order to assist the executive management in analyzing their current business processes and expand the company by discovering new opportunities.Executives decided that the first deliverable should focus on the flight activity in order to ensure a good ongoing business process. -The marketing department wants to:
1-analyze what flights the company’s frequent flyers take,
2- what fare basis they pay
3- how often do they upgrade
4- how they earn and redeem their frequent flyer miles whether they respond to special fare promotions
5- how long their overnight stays are, and
6-what proportion of these frequent flyers have gold, platinum, aluminum, or titanium status?
Your analysis shall also include the reservation process where the finance team will be interested in analyzing the company profit. Note that reservation processes can take place through multiple channels. The airline company also provides customer care interaction before, within, and after your trip in order to handle customer inquiries, and complaints and keep their feedback for business enhancements. Analysis should include interaction type and problem severity (if an issue exists) apply dimensional modeling process to design the logical and physical design to support such kind of analysis for decision support.
-Project Requirements:
- State the dimensional modeling process for each business process
- Using any diagramming tool you like, construct a logical data model for this case study. Output should be an image or PDF. State why did you choose this particular data model design? What does the data represent? (Details about each model component is necessary).
- Translate the logical data model to a physical data model which includes the following: tables, columns (name, data type), primary indexes, and foreign indexes and state why these types of indexes are used for each column. Output should be a Word or Excel file.
- Create the table in oracle DBMS and populate sample data to be used in your queries.
- Construct a sample of SQL queries (5 – 8 queries) using your physical model design which can be used to answer possible questions by the decision maker as described in the case-study above. List the business question with each query. Output should be a Word file