i only need extra credit please help

Homework Instructions

1. Begin this homework by modifying the variable DREXEL_USERNAME in the next cell
   • Set the variable to your Drexel username (e.g., “db59”)
2. Run all cells in this notebook before proceeding with solving problems
   • Select “Cell” -> “Run All” from the top menu bar
3. Follow instructions supplied with each problem
4. Make sure you save your work in this notebook regularly by pressing CTRL+S (CMD+S on macOS)
5. Submit this notebook with your saved answers

In [ ]:

### CHANGE THIS TO YOUR DREXEL USERNAME! DREXEL_USERNAME=”db59″

In [47]:

## This cell installs python (pypi) packages, then imports modules referenced in this notebook ## Make sure this cell is executed first, before you run any cell in this notebook import ipydeps ipydeps.pip([“wand”, “json”, “lxml”, “redis”, “sqlite3”, “pandas”, “numpy”, “pillow”, “bokeh”, “sqlalchemy”]) from IPython.display import IFrame from wand.image import Image as WImage from IPython.display import display import ipywidgets as widgets import numpy as np import pandas as pd from sqlalchemy import create_engine ### we are going to use a jupyter's magick commands %sql or %%sql to run queries ## install pip package for the sql magick: ipydeps.pip([ “ipython-sql”, “jupyter-sql”, “jupyter_contrib_nbextensions”]) ## load sql magick (ipython extension) %reload_ext sql ## %sql and %%sql magics will return pd.DataFrame objects %config SqlMagic.autopandas=True ## display the first 7 rows of pd.DataFrame objects pd.set_option(“max_rows”, 7) json is part of the Python standard library and will be skipped. Remove it from the list to remove this warning. sqlite3 is part of the Python standard library and will be skipped. Remove it from the list to remove this warning. Packages already installed: bokeh, lxml, numpy, pandas, pillow, redis, sqlalchemy, wand No new packages installed Done Packages already installed: ipython-sql, jupyter-sql Running pip to install jupyter_contrib_nbextensions No new packages installed Done

PROBLEM 1 [30 pts]: Design relational schema for scheduling flights

In this exercise you will develop a relational schema that supports an implementation of a requested feature. The requested feature is defined by business rules specified below. In addition to the business rules, some entities and attributes are provided.

In part a., you will draw an Entity Relationship (ER) diagram that defines entity sets and relationship sets needed to support the requested feature. The diagram must clearly specify primary keys for each entity sets and relationship sets. In addition, appropriate key constrains and participation constrains must be included with your design.

IMPORTANT: You do not need to add your drawing to this notebook! Take a picture of the ER diagram and submit the image to BbLearn as a separate file.

In part b., you will convert your design to relational schema and construct SQL statements that create SQL tables for your schema.

You will discuss your choice of key constraints, participation constraints, and integrity constraints in your schema in part c.

Requested Feature: Scheduling flights

You are tasked to design relational schema that extends SQL tables routes and airports in routes.v2.db database. Your schema must support scheduling all flights defined in routes for a given date. In general, on a given date (e.g., '2019-01-21') one flight must be scheduled for every tuple (Source, Destination, Airline) in routes table. For this exercise we are only interested in scheduling flights for a 24 hours window. In other words, we do not need to schedule the time of flight's departure or arrival. In addition, we assume that any employee is available to service any flight unless the person is already scheduled to operate on another flight for that date, regardless of flight's Source or Destination. Similarly, any aircraft can service any route, as long as it is not scheduled for a different route on that date.

Prototype of a python function implementing the requested feature is provided your reference:

def schedule_flights(date): “”” Schedule flights for a given date. Updates SQL tables appropriately. Parameters ———- date : string String representation of a date using '%Y-%m-%d' format e.g., “2019-01-01” Returns ——- (nflights,ncancellations) : tuple Number of scheduled (nflights) and canceled flights (ncancellations) for the day “””

Entities and Attributes

• Passenger capacity is defined for each type of an aircraft. We assume that each aircraft type is operated by all airlines in the same configuration. Attribute Equipment specifies aircraft's type (e.g., '738', '73W', 'CNC', etc). Attribute Capacity is an integer that specifies the number of passengers this aircraft can carry. In this exercise, we assume that there is only one (economy) class of seats in the aircraft. Also, your schema does not need to handle proper seat assignment for the passengers.

  • equipment_capacity(Equipment, Capacity)

• Regular price of each ticket is defined by entity airfare. The final price of a ticket depends on the order in which a passenger booked the ticket (see below).

  • airfare(Airline, Source, Destination, Price)

• Airplanes that are operated by each airline are specified by entity equipment. Aircraft type is defined by the attribute Equipment (e.g., '738', '73W', 'CNC', etc). Attribute EquipmentNo is a serial number of a vessel that is owned by an airline (Airline attribute).

  • equipment(EquipmentNo, Equipment, Airline)

• All airline employees are defined by entity personnel. Employee's name and position are defined by attributes Name and Position. Position can be 'pilot', 'attendant', or 'ground crew'. Attribute Equipment defines the type of an aircraft that employee is certified to fly or work on. We assume that an employee (regardless of their position) can be certified for only one type of an aircraft.

  • personnel(Airline, Name, Position, Equipment)

Business Rules

Additional business rules are as follows:

• There is no limit on number of consecutive days an employee can work.

• If Source airport has less than 25 inbound and outbound routes operated by any airline, then any flight between Source and Destination will only occur on Mondays, Wednesdays, and Saturdays. And flights from the Source city will be canceled on Tuesdays, Thursdays, Fridays, and Sundays.

• Every flight must include 2 pilots, at least 3 attendants, and 5 ground crew members. If flight's capacity exceeds 100 person, you will need 1 attendant per 10 additional passengers. If an airline does not have enough free employees to operate a flight, it must be canceled.

• Equipment attribute in table routes can specify multiple aircraft types that can operate on that route. If an airline does not have any free (i.e., not already scheduled for that date) aircraft that can fly a given route, this route must be canceled for that date.

Problem 1.A [5 pts]

Draw Entity Relationship (ER) diagram representing entity sets and relationship sets. Clearly mark all key and participation constraints.

IMPORTANT: You do not need to add your drawing to this notebook! Take a picture of the ER diagram and submit the image to BbLearn as a separate file.

Problem 1.B [10 pts]

Translate ER diagram from part a. into relational schema. Provide all relevant SQL CREATE TABLE statements that define your schema. Use PRIMARY KEY, FOREIGN KEY, UNIQUE, and NOT NULL keywords to enforce integrity constraints, wherever appropriate.

Problem 1.C [15 pts]

Answer the following questions about your schema. Use several sentences to answer each question, when the question is applicable to your design. Or provide justification to why the question does not apply to your answer.

• Describe how key and participation constraints are enforced in your relational schema.
• Describe FOREIGN KEY used to enforce referential integrity constraints
• Describe any other constraints enforced by your schema.
  • What table constraints (e.g., using CHECK or TRIGGER keyword) are enforced?
  • What application-side assertions are needed?

PROBLEM 2 [30 pts]: Implementation for single ticket booking

This problem is currently hidden. It will be distributed as a separate notebook after Midterm. Please add your solution to the other notebook and submit it to BbLearn as a separate file.

PROBLEM 3 [40 pts]: Algorithm for application-level assertion when Codeshare='Y'

In this exercise, you will develop an algorithm that verifies if business rule for Codeshare attribute in routes table. This business rule use attribute Codeshare='Y' to identify routes that must share equipment (i.e., aircraft). For example, if we run this SQL query to select routes with Codeshare='Y',

%sql SELECT * FROM routes WHERE codeshare='Y'

we get:

Airline AirlineID Source SourceID Destination DestinationID Codeshare Equipment 0 7H 16726 ABL 7177 OTZ 3693 Y CNC 1 7H 16726 ABL 7177 SHG 7117 Y CNC 2 7H 16726 AIN 7220 ATK 6714 Y CNC

Codeshare Bussiness Rule

Let ('Source', 'Destination', 'Airline', 'Date') denote a scheduled flight with Codeshare='Y'. Then the same airplane EquipmentNo must be used on a flight scheduled to arrive to city Source one day earlier. In other words, every Codeshare='Y' flight must be scheduled to re-use an airplane (Equipment) that flew into that city the day before.

Problem 3. Part A [10 pts]

In a few sentences, provide a natural language description of an algorithm that returns False when codeshare bussiness rule is violated by at least one flight that is operated by Airline.

Problem 3. Part B [30 pts]

Add pythonic pseudo-code for your algorithm as implementation of python function below.

def verify_codeshare(airline): “”” Verifies codeshare rule. Parameters ———- airline : string Name of an airline Returns ——- rval : bool Returns False if at least one flight operated by airline violates codeshare rule. Otherwise, returns True. “””

In [102]:

%sql SELECT * FROM routes WHERE codeshare='Y' * sqlite:///routes.db Done.

Out[102]:

4622 rows × 8 columns

Extra Credit 1 [30 pts]: Implementation for flight scheduling¶

Implement schedule_flights feature from Problem 1 of this homework.

def schedule_flights(date_string): “”” Schedule flights for a given date. Updates SQL tables appropriately. Parameters ———- date_string : string String representation of a date using '%Y-%m-%d' format e.g., “2019-01-01” Returns ——- replaced : tuple (nflights,ncancellations) Number of scheduled (nflights) and canceled flights (ncancellations) for the day “””