- Case Study Ikhlas Terbang Helicopter Corporation Sdn Bhd S Ithcsb Main Office Is Located In Peninsular Malaysia At The 1 (103.91 KiB) Viewed 59 times
- Case Study Ikhlas Terbang Helicopter Corporation Sdn Bhd S Ithcsb Main Office Is Located In Peninsular Malaysia At The 2 (68.27 KiB) Viewed 59 times
Case Study Ikhlas Terbang Helicopter Corporation Sdn Bhd's (ITHCSB) main office is located in Peninsular Malaysia at the Sultan Abdul Aziz Shah Airport, Subang. They provide a wide range of services including tours, VIP transfers, and private helicopter charters. They have a current Air Operator Certificate (AOC) from the Department of Civil Aviation, Malaysia (DCAM) for Public Transport. ITHCSB officials decided that all information related to ITHCSB helicopter maintenance/testing operation is to be organized using a Database Management System (DBMS) and you have been hired as the consultant to design the related database based on the following descriptions: • Every helicopter has a registration number, a registration date, a current status, and each helicopter is of a specific model. The ITHCSB hangar accommodates several helicopter models and each model is identified by a model number and has a name, a maximum capacity, a maximum passenger weight, a cruise speed, a weight, an engine type, a range, and a configuration. • Some engineers work at the hangar. It is necessary to store the engineer ID, full name, gender, IC number, passport number (if any), home address, email address, phone number, current status, and salary of each engineer. Each engineer is an expert on one or multiple helicopter model(s), and his or her expertise may overlap with that of other engineers. The engineer's expertise level must be recorded. All engineers also must have an annual medical examination. For each engineer, it is necessary to store the result and the date of the most recent exam. An engineer (supervisor) may supervise many engineers, and each engineer is supervised by one engineer (supervisor). • The hangar has several professional DCAM tests that are used periodically to ensure that helicopters are still airworthy. Each professional DCAM test has a test ID, a name, a type, and a maximum possible score. The DCAM requires the company to keep track of each time a given helicopter is tested by a given engineer using a given test. For each testing event, the information needed is the date, the number of hours the engineer spent doing the test the problems (if any), and the score the helicopter received on the test. . Some testing events involve only labour activities and no parts are required, but most testing events require the replacement of one or more parts. The status and quantity of each part required in the performance of each testing event should also be recorded. The part number, name, description, quantity in stock, and cost for each part are recorded in the system. The cost indicated is the amount that ITHCSB pays for the part. Some parts may be used in more than one testing event.
1. Based on the given case study, you are required to design and develop a relational database using Microsoft Visio / Diagrams.net / Oracle or any tool that you are familiar with. a) Chapter 1 Introduction i) Introduction ) Problem Statements ii) Objectives iv) Scopes b) Chapter 2 Database Design i) Entity Relationship Diagram (ERD) ii) Business Rule ii) Data Dictionary c) Chapter 3 Database Implementation 1) SQL Data Definition Language (DDL): CREATE TABLE statements ii) SQL Data Manipulation Language (DML): INSERT, UPDATE, and DELETE statements INSERT - 3 insert queries, and SQL statements (Insert at least 5 rows of data for each table) • UPDATE - 3 update queries, and SQL statements (Simple and complex statements) • DELETE- 3 delete queries, and SQL statements (Simple and complex statements) ii) SQL Data Manipulation Language (DML): SELECT statements • 3 data queries relational algebra, and SQL statements (Simple and complex statements) that involves the Unary operations • 3 data queries relational algebra, and SQL statements (Simple and complex statements) that involves the Aggregation and Grouping operations • 3 data queries relational algebra, and SQL statements (Simple and complex statements) that involves the Inner and Outer Join operations • 3 data queries, relational algebra, and SQL statements (Simple and complex statements) that involves the Nested/Subquery and SET operations d. Chapter 4 Conclusion i) System Contribution ii) System Drawbacks iii) Suggestions for Improvement