CS 122A: Introduction to Data Management Homework 1

2024-08-03 Admin 写评论

Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due

CS 122A: Introduction to Data Management 
Homework 1: E/R Modeling

Submission

All homework assignments should have the student IDs and names of your team members. Remember that all homework assignments should be done in a group of three. This homework assignment should be submitted on EEE before 11:45 pm on the due date. Only one student in a group should submit the file. Everybody on the team isrequired to have the finally submitted version. Refer to the following table for the submission guidelines. After the 24­hour grace period, no more submission is allowed on EEE. That is, we will not accept assignments after that time. We will publish the solutions at that time for the next assignment. Please get all your work in on time!
Date / Time
 Place
 Remarks
Thursday, April 14 (11:45 PM)
 EEE dropbox
 Due date
Friday, April 15 (11:45 PM)
 EEE dropbox
24­hour grace period 
10 points will be deducted

E­R Schema Design [100 pts]

After taking several database courses at the school of ICS at UCI, you have joined an airline company called “UC Airlines” (UCA). It is a regional airline company that operates passengerairplanes that connects airports in California. As a small company, they recruited you as an IT professional. The company wants to construct its database system from scratch since the current database was purchased a while ago, and the company that designed the originaldatabase closed the business. So, maintaining and upgrading the current database becomes difficult. As an A+ taker in CS122A course, you need to show them you are a qualified database professional for UCA. Your first big goal is to setup a database to support UCA’s business. The first part of your work will require capturing the design requirements and producing an E­R diagram representing a suitable conceptual schema for the database.

Currently, UCA connects cities in California (e.g., between LA and SF). It istrying to provide the lowest flight fare in the region. In order to be profitable, it is critical to properly manage flights to maximize the utilization of its limited number of planes. UCA also operates one or more lounges at the airports with departure flights. Customers with a UCA reservation can use its lounges. In the lounge, there are food menusfor customers, which bring profit to the company.  UCA wants to increase the number of lounges in the future.

You are given the following requirements after an interview with a few managers and employees at UCA.
1. Each airplane has a unique registration number (e.g., “N12345”), a model number (e.g., “B777”), year of purchase, year of being manufactured, and capacity of passengers(e.g., “450”).

2. We can identify a unique flight using a flight number (e.g., “UC949”) and a projected departure datetime together, since there can be only one flight with a specific flight number per day. For example, a flight named “UC949” departs from LAX at 02:34 pm and arrives at SFO at 03:24 pm daily. The projected departure datetime and projected arrival datetime are recorded when a flight instance is created. The actual departure datetime and arrival datetime will be updated later. That is, for each flight, four datetimes are recorded at the end. A flight does not have multiple stops.

3. An airport has an assigned name (e.g., “John Wayne Airport”), a unique IATA code (e.g., “SNA”), and a location (city and state).

4. Each flight includes the IATA codes of its departure/arrival airports.

5. Each flight is initialized with departure/arrival airports, projected departure datetime, and projected arrival datetime. Pilots, flight attendants, and an airplane can be assigned later to finalize a flight.

6. Exactly one airplane should be assigned for each flight.

7. Each employee has a unique ID (e.g., “990001”). Each employee has a phone number (e.g., “949­111­2222”), an address (street, city, state, and zipcode), birthdate, SSN, and job title.

8. A pilot has a year when he/she started flying airplanes. Two or more pilots should operate an airplane on one flight. That is, each flight must have two or more assigned pilots. One or more than one pilot can be assigned on one flight. Also, a pilot can be assigned to multiple flights.

9. Flight attendants ensure the safety and security of passengers during a flight. Thus, a flight must have two or more assigned flight attendants. One or more than one flight attendant can be assigned on one flight. Also, a flight attendant can be assigned to multiple flights. For each attendant, UCA keepsthe overall experience of that attendant in number of service years (e.g., “15”).

10.One type of employeesis maintenance engineers. They check airplanes before and after each flight, and conduct appropriate operations to ensure that all airplanes function properly. UCA keeps the information about each maintenance engineer’sspecialty skills (e.g., “airframes”, “engines”, and “electrical systems”). An airplane must have two or more designated maintenance engineers and a maintenance engineer maintainstwo or more planes.  One or more than one maintenance engineer can be assigned to a plane. Also, a maintenance engineer can be assigned to multiple planes.

11.In addition to pilots, maintenance engineers, and flight attendants, there are also other types of employees classified as operation staff, who have duties not listed above. UCA keeps the current department (e.g., “customer service”) of each operation staff member.

12.Due to the complexity of each employee class, an employee cannot conduct two types of tasks. For example, a pilot cannot act as a maintenance engineer.

13.Customers need to register their current address (street, city, state, and zipcode), gender, SSN, and information about one or more credit cards. A customer also needsto register an e­mail address. UCA assigns a unique ID for each customer.

14.Each credit card consists of a number and an expiration date.

15.A customer reserves one flight by calling UCA customer service. A customer creates a reservation that includes only one flight and quantity of tickets for that flight. A customer can make many reservations. However, a customer can make only one reservation per flight. Each reservation includes a datetime when it is booked, price, and quantity of tickets.

16.UCA has one or more lounges at an airport with a departure flight. A lounge can be identified using a unique ID (e.g., “112”). A lounge also includesthe location information at the airport (e.g., “section D32”).

17.A lounge serves many dishes. Each dish has a name and price. The name is unique within a lounge’s menu, but may not be unique across multiple lounges.

18.Each lounge serves one or more dishes.

19.A customer can place an order that contains one or more dishes from a lounge. They also need to indicate the quantity per dish (e.g., 2 chicken tenders and 3 burgers). Of course, that order can be only served by that lounge.

20.Each order will be assigned a unique ID and datetime of the order.

21.For each order, the total cost for all the dishes will be calculated by adding the prices of the dishes. This total amount must be stored in each order.

Your task:
Design an E­R diagram to represent the required information. Make sure to use the E­R notations from the lectures and textbook. Also make sure that your design captures all of implications of the business model, including:

(a) [50pts] All of the relevant entities and their attributes, including keys. For entities, you can ONLY use “Employee”, “Maintenance Engineer”, “Pilot”, “Flight Attendant”, “Operation Staff”, “Airport”, “Flight”, “Airplane”, “Lounge”, “Customer”, “Dish” and “DishOrder”. Do not create more entities.

(b) [50pts] All the relevant relationships and associated attributes, appropriate cardinality constraints for the relationships, and appropriate participation constraints for the relationships.

发表评论

电子邮件地址不会被公开。 必填项已用*标注