From f27d533f1f774a9043fa631f1938c7e86a1f9087 Mon Sep 17 00:00:00 2001 From: mo khan Date: Thu, 30 Apr 2020 14:06:27 -0600 Subject: Question 2 --- assignments/exam/README.md | 98 ++++++++++++++++++++++++++++++++++++++++++++-- 1 file changed, 94 insertions(+), 4 deletions(-) diff --git a/assignments/exam/README.md b/assignments/exam/README.md index dad52d4..26356c6 100644 --- a/assignments/exam/README.md +++ b/assignments/exam/README.md @@ -1,4 +1,5 @@ -# 1 +# Practice Exam +## 1 A real estate company uses a database to store information about customers, property, and contracts. The following relations are used in the database: @@ -52,11 +53,100 @@ Normal forms: Before: +```text * Customers(_id_, name, mail_address, balance, lawyer_name, discount) * Contract(_id_, customer_id, agent_id, date_of_contract, property_address, property_value, type, number_of_rooms, land_size, built_size, tax_value) +``` After: -* customers(id, name, mail_address, balance, lawyer_name, discount) -* contracts(id, customer_id, agent_id, property_id, date_of_contract) -* properties(id, address, value, type, number_of_rooms, land_size, built_size, tax_value) +```text +* customers(_id_, name, mail_address, balance, lawyer_name, discount) +* contracts(_id_, _customer_id_, _agent_id_, _property_id_, date_of_contract) +* properties(_id_, address, value, type, number_of_rooms, land_size, built_size, tax_value) +``` + +## 2 + +Draw a class diagram for the following situation. + +* An airline company has a number of planes. +* The attributes of a plane include plane_id (unique), name, and vendor. +* The airline company serves many destinations. +* The attributes of a destination include destination_id (unique), name, and flight_date. +* Each plane flies to one or more destinations; + * or it may be used as back-up; + * or it may be under maintenance, and therefore will not fly to any destination. +* For each plane undergoing maintenance, the company records the name of the technician and the type of maintenance performed. +* A destination may be served by one or more planes. +* Each plane’s service charges vary by the number of destinations the plane serves. +* The airline company maintains records of the service charges for each plane when it flies to a certain destination. +* At the end of each year, the airline company applies a depreciation percentage to each plane. +* The airline company calculates each plane’s percent depreciation based on the amount of service charges and the total hours of flying for that plane. + +```text +planes(_id_, name, vendor) +destinations(_id_, name, flight_date) +maintenance(_id_, technician_id, plane_id, type) +``` + +```text +----------- +| Planes | +----------- +| + id | +| + name | +| + vendor |<--------------------- +-----------| | +| + calculateServiceChargeForFlightTo(destination) | +----------- | + | | + has_many | + | | + V | +----------------- | +| maintenance | | +----------------- | +| id | A +| technician_id | | +| plane_id |-- belongs to --| +| type | | +----------------- | +| | | +----------------- | + | | + belongs_to | + | | + V | +----------------- | +| technician | | +----------------- | +| id | | +----------------- | +| | | +----------------- | + | +--------------- | +| destination | | +--------------- A +| id | | +| name | ---belongs to ---| +| plane_id | | +| flight_date | | +--------------- | +| | | +--------------- | + A | + | | + belongs_to | + | | +------------------ | +| service charge | A +------------------ | +| id | | +| plane_id | -- belongs to | +| destination_id | +------------------ +| | +------------------ +``` -- cgit v1.2.3