Assignment - Alamo City Flights, Inc.
Company Background:
Alamo City Flights, Inc. (ACF) is a specialty sports shop in central Texas that specializes in sales and service of free-flight sports equipment such as hang gliders, paragliders, and powered gliders (also known as powered parachutes). A hang glider is a non-motorized, foot-launched rigid frame maintaining the shape of a wing constructed of rip-stop nylon or Mylar over an aluminum frame, with the pilot suspended in a prone position from a strap connected to the glider's frame. A paraglider is a non-motorized, foot-launched inflatable canopy or wing constructed of rip-stop nylon from which the pilot is suspended by sturdy Kevlar lines called risers, and a harness. In 2017, ACF expanded their sales line adding unmanned aerial vehicles (a.k.a. UAVs, drones) to their line of offerings. An unmanned aerial vehicle is an aircraft that can navigate without a human pilot on board.
ACF's customer base increased, as did customer demand. Many customers now make several purchases and own multiple pieces of free-flight equipment. The company must be able to easily find and identify which parts are needed for repairs by identifying the piece of equipment for which parts are needed.
In this assignment, ACF will not need to track the actual purchase activity of the aircrafts themselves. They would like to record the customer information, the aircraft information, purchases, repair activities, employee information, and vendors' information and relationships. Note that when the customer requires repairs to their aircrafts, they may either purchase parts to do their own repairs, or they may allow ACF to handle the repairs.
You are currently enrolled in an introductory database class this semester. During the semester, you have been offered, and accepted a part-time job with ACF. Your first assignment is to develop a high-level E-R diagram to meet the current needs of this company, prior to developing the actual database.
You conduct interviews with a number of company management and staff to identify the key entity types for the company. After a short time, you have identified the following entity types:
-CUSTOMER -ACF's customers are people. Each customer has a customer ID (identifier), name, address, email address, and phone number. Your diagram should allow for more than one email and more than one phone number.
-AIRCRAFT - Each aircraft has a serial number (identifier), model number, type, manufacture date, and inspector initials.
-EMPLOYEE - Each employee has an employee ID (identifier), name, address, title, email address, and phone number. Your diagram should allow for more than one email and more than one phone number.
-SALES ORDER - Each sales order has an order number (identifier), sales date, and total price.
-SALES ORDER LINE ITEM - Each order line item has a line number (partial identifier), quantity, and extended price.
-PART - Each part has a part number (identifier), description, cost, and sales price
-REPAIR ORDER - Each repair order has a repair number (identifier), repair date, and total cost of the repair.
-REPAIR ORDER LINE ITEM - Each repair line item has a line number (partial identifier), quantity, and extended price
-VENDOR - Vendors are businesses. Each vendor has a vendor ID, name, vendor address, phone, and email. For simplicity, we will allow the vendors to have only one phone number and one email address.
-LOCATION - ACF has several locations across the United States. Each location has a location ID, name, address, and phone.
You recorded the following information concerning relationships:
-A customer may place several sales orders or may not place any sales orders at all. Each order that is placed belongs to exactly one customer.
-Every sales order has order line items, and each sales order must have at least one, but can have several order line items. Each order line item is on exactly one sales order.
-Each order line item contains exactly one type of part. However, a particular part can appear on several lines, or a particular part may not be ordered at all.
-To be considered as a customer, one must purchase (owns) at least one aircraft, and every aircraft manufactured by the company has either been sold, or has not yet been sold.
-An employee repairs aircrafts. Some employees do not repair aircrafts. An employee may repair several aircrafts. Each aircraft is worked on by at least one technician (employee). When an employee repairs an aircraft, the employee's billing rate is recorded.
-When a customer requests a repair, a repair order is created. A customer may request several repairs, or may not need repairs at all. In the event of a repair request, each repair is for exactly one customer.
-Each repair order is for exactly one aircraft, and an aircraft can be repaired many times.
-Each repair order has repair line items, and each repair order must have at least one, but can have several repair line items. Each repair line item is on exactly one repair order.
-Each repair line item contains exactly one part. However, a particular part can appear on several lines, or a particular part may not be ordered at all.
-A given vendor may supply any number of parts to a location, or may not supply any parts to any locations. A part may or may not be supplied by a vendor to a location. A location may or may not have a part supplied by a vendor. When a vendor supplies a part to a location, the shipping mode, shipping date, and the shipping cost are recorded.
Your Assignment:
You must create an entity-relationship diagram of a database for the ACF company.
Set the minimum and maximum cardinalities of the relationships among entities.
Include name and address as composite attributes.
Use weak entities as you see appropriate.
Do not use supertypes or subtypes.
State any assumptions, if you feel it is necessary, to help explain and justify why you constructed the E-R diagram the way that you did.