PS3_3_FA23_CSGY6083B

.pdf

School

New York University *

*We aren’t endorsed by this school

Course

6083

Subject

Computer Science

Date

Jan 9, 2024

Type

pdf

Pages

14

Uploaded by LieutenantElectron12238

Report
ASSIGNMENT: 3 [100 Points, 5% weight to the Final grades] Please submit your assignment on NYU Brightspace in a single PDF document attachment. Please mention Student ID, Name, Course, Section Number, and date of submission on first page of your submission. Please use SQL file (DDL and DML code) for writing SQLs in Oracle. You may create equivalent DDL and DML code for MySQL and write SQLs in MySQL. All the tables should be created with your initial as a prefix. DO NOT change data in tables. You can write SQL in either Oracle or MySQL. Problem A : 50 points [each question has 10 points] The New York City Subway is a rapid transit system in the New York City boroughs of Manhattan, Brooklyn, Queens, and the Bronx. It is owned by the government of New York City and leased to the New York City Transit Authority, an affiliate agency of the state-run Metropolitan Transportation Authority (MTA). Opened on October 27, 1904, the New York City Subway is one of the world's oldest public transit systems, one of the most used, and the one with the most stations, with 472 stations in operation and 36 train lines. SAMTA (Subway Administration of Metropolitan Transportation Authority) is the NYU affiliated data analytics startup company. SAMTA has undertaken a database project to normalize subway data systems. For relational schema model in figure, write SQL statements to answer following questions. You don’t need to create a relational model. CS-GY 6083 - B, Fall 2023 Principles of Database Systems
i. List all the details of the station which has the highest number of facilities in Elevator. SQL QUERY - WITH BoroughFacilityCounts AS ( SELECT s.station_id, s.station_name, s.station_address, s.borough, s.neighborhood, - f.facility_name, COUNT(sf.facility_id) AS num_facilities, RANK() OVER (ORDER BY COUNT(sf.facility_id) DESC) AS facility_rank FROM ss_station s JOIN ss_station_facility sf ON s.station_id = sf.station_id JOIN ss_facility f ON sf.facility_id = f.facility_id WHERE f.facility_name = 'Elevator' GROUP BY s.station_id, s.station_name, s.station_address, s.borough, s.neighborhood, f.facility_name ) SELECT * FROM BoroughFacilityCounts WHERE facility_rank = 1; RESULT -
ii. List details of each train line and their highest run time. Your result should have TRAIN_ID, LINE_NAME, ARRIVVAL, DEPARTURE, SQL QUERY – WITH TrainLineRuntimes AS ( SELECT ts.train_id, tl.line_name, TO_CHAR(ts.arrival, 'MM/DD/YYYY HH24:MI:SS') AS arrival, TO_CHAR(ts.departure, 'MM/DD/YYYY HH24:MI:SS') AS departure, ts.departure - ts.arrival AS runtime, RANK() OVER (PARTITION BY ts.train_id ORDER BY ts.departure - ts.arrival DESC) AS runtime_rank FROM ss_trainlines_station ts JOIN ss_trainlines tl ON ts.train_id = tl.train_id ) SELECT train_id, line_name, arrival, departure FROM TrainLineRuntimes WHERE runtime_rank = 1; RESULT -
iii. Find the top two boroughs in terms of numbers of station facilities. Your result should have the name of the borough and number of facilities. SQL QUERY - WITH BoroughFacilityCounts AS ( SELECT s.borough, COUNT(sf.facility_id) AS num_facilities, RANK() OVER (ORDER BY COUNT(sf.facility_id) DESC) AS borough_rank FROM ss_station s JOIN ss_station_facility sf ON s.station_id = sf.station_id GROUP BY s.borough ) SELECT borough, num_facilities FROM BoroughFacilityCounts
WHERE borough_rank <= 2; RESULT- iv. List station name, borough, neighborhood, facility name, and number of facilities. Arrange the result in descending order of number of facilities. SQL QUERY - SELECT ss_station.station_name, ss_station.borough, ss_station.neighborhood, ss_facility.facility_name, ss_station_facility.number_of_facility FROM ss_station_facility INNER JOIN ss_station ON ss_station_facility.station_id = ss_station.station_id INNER JOIN ss_facility ON ss_station_facility.facility_id = ss_facility.facility_id ORDER BY ss_station_facility.number_of_facility DESC; RESULT-
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Recommended textbooks for you
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
SEE MORE TEXTBOOKS