The purpose of this assignment is to develop skills in managing data in databases and to gain understanding of data model development and implementation using a commercially available database management system development tool.
On completion of this assignment students will be able to:
a. Model organisational information requirements using conceptual data modelling techniques.
b. Convert the conceptual data models into relational data model
Assignment Description
The officials at Frankston Airport have decided that all information related to the airport should be organized using a DBMS, and you've been hired to design the database. Your first task is to organize the information about all the airplanes that are stationed and maintained at the airport. The relevant information is as follows:
User view 1 requirement:Every airplane has a registration number, and each airplane is of a specific model.
User view 2 requirement:The airport accommodates a number of airplane models, and each model is identified by a model number (e.g., DC-10) and has a capacity and a weight.
User view 3 requirement:A number of technicians work at the airport. You need to store the name, SSN, address, phone number, and salary of each technician.
User view 4 requirement:Each technician is an expert on one or more plane model(s), and his or her expertise may overlap with that of other technicians. This information about technicians must also be recorded.
User view 5 requirement: Engine tests on a plane must be conducted by a technician who is an expert on that model.
User view 6 Requirement :Traffic controllers must have an annual medical examination. For each traffic controller, you must store the date of the most recent exam.
User view 7 requirement:All airport employees (including technicians) belong to a union. You must store the union membership number of each employee. You can assume that each employee is uniquely identified by the social security number.
Question 1.
a. Identify the entities in given business case above.
b. Identify suitable attributes for all entities identified in the above (a)
c. What are the statements whichdescribe the relationships between entities? List all the statements in the business case.
d. Represent the structure of your database visually by using the entity-relationship (E-R) diagram.
Question 2. Build this model using MS Access by creating these tables and Relationships in Access. Populate these tables with appropriate data, at least 3 records in each table. Include:
a. Relationship diagram created in Access. (Select database tools → Relationships in the Access menu)
b. Data sheet view and design view of your tables.
Question 3. Determine the functional Dependencies that exist in the following table and convert it to an equivalent collection of tables that are in third normal form.
Patient (PatientNum, HouseholdNum, PatientName, Street, City, State,
PostalCode, Balance,(ServiceCode, Description, Fee, date)