Flight Instance - Country Pilot - Assessment Answer

Task 1: Pet Walk Management

Assumptions

• An owner must have at least one pet.
• More than one pet can be given the same walk.

0NF

R1(Owner_num, Owner_name, Owner_phone, Pet_num, Walk_date, Walk_duration, Paid, Walk_notes)

1NF

R1(Owner_num, Owner_name, Owner_phone, Pet_num, Walk_date, Walk_duration, Paid, Walk_notes)

Decomposition:

• R11(Owner_num, Pet_num, Owner_name, Owner_phone)
• R12(Pet_num, Pet_name, Walk_date, Walk_duration, Paid, Walk_notes)

2NF & 3NF

• R11(Owner_num, Pet_num, Owner_name, Owner_phone)
• R12(Pet_num, Pet_name, Walk_date, Walk_duration, Paid, Walk_notes)
• R121(Pet_num, Pet_name)
• R122(Pet_num, Walk_date, Walk_duration, Paid, Walk_notes)

Note: Walk ID can be used to handle walks involving one pet as an identifier, as suggested previously.

Task 2: Order and Invoice Management

Assumptions

• A category can have multiple items.
• An item can be part of more than one order.
• Email is unique and treated as the primary key for a customer.
• Delivery instructions attribute is included.
• Invoice to customer is 1:1, Order to Invoice is 1:1.

0NF

R1(Invoice_num, Invoice_date, delivery_address, delivery_instructions, email, name, phone, Item_code, Item_name, Cat_code, Cat_name, Cost_each, quantity, Subtotal)

1NF

R1(Invoice_num, Invoice_date, delivery_address, delivery_instructions, email, name, phone, Item_code, Item_name, Cat_code, Cat_name, Cost_each, quantity, Subtotal)

Decomposition:

• R11(Item_code, Cat_code, Item_name, Cost_each, Qty, Subtotal)
• R12(Cat_code, Cat_name)
• R13(Invoice_num, Item_code, Invoice_date, delivery_address, delivery_instructions, email, name, phone)

2NF & 3NF

• R11(Item_code, Cat_code, Item_name, Cost_each, Qty, Subtotal)
• R121(Item_code, Cat_code, Item_name, Cost_each)
• R122(Order_id, Item_code, Quantity, Subtotal)
• R13(Invoice_num, Item_code, Invoice_date, delivery_address, delivery_instructions, email, name, phone)
• R211(Invoice_num, Item_code, Invoice_date, delivery_address, delivery_instructions, email)
• R22(email, name, phone)

Task 3: Flight and Airport Management

Assumptions

• A plane should have a model ID.
• A flight starts and ends at two airports only.
• An airport must belong to a country.
• Each flight instance must have a flight path (distance, starting point, end point).
• Flight instance has unique date and time of departure and arrival.
• Airport departure and arrival codes are foreign keys to the airport table.

3NF

• Planes(plane_registration_id, year, model_id)
• Model(model_id, manufacturer_name, travel_range, speed)
• FlightPath(flightpath_id, airport_depart_num, airport_arrival_num, distance)
• FlightInstance(flightInstance_id, plane_registration_id, flightpath_id, dateTime_departure, dateTime_arrival)
• Airports(airport_code, country_code, name, latitude, longitude)
• Countries(country_code, name)

Task 4: Flight Crew Management

Assumptions

• A flight instance must have two pilots and more than one attendant, including one FSM (Flight Service Manager).
• Pilots and attendants are mapped appropriately to flight instances and models.

3NF

• Pilots(pilot_id, first_name, last_name, hire_date)
• PilotModelMap(pilot_id, model_id)
• PilotFlightInstanceMap(flightinstance_id, pilot_id_1, pilot_id_2)
• Attendants(attendant_id, language_id, first_name, last_name, mentor_attendant_id)
• AttendantFlightInstanceMap(flightinstance_id, attendant_id, isFSM)
• Languages(language_id, name)

You Might Also Like:

CSE2DBF Database Management System Assignment Sample

BISY2001 Database Management System Security Issues Assignment Sample

Looking For Complete Assignment Sample Get It On WhatsApp