Homework Assignment 1 In this homework assignment, you are expected to design and implement a spatial database for a taxi service company TaxiGoes in a city. You will not be given a dataset to populate this database as the given data might impact your answers. Therefore, consider this database to be the one used for collecting the data. Below you will find the requirements for this database. Please read carefully and identify the requirements coming from this mini-world. TaxiGoes has two types of customers: individuals and businesses. Both of these customer types can use the service from their mobile phones, but business accounts require an accreditation code from their company. Each company is given one code to be shared across the TaxiGoes business customers. Companies need to register with TaxiGoes using the company name, and address. The customers are identified by their mobile phone numbers. TaxiGoes stores information about customer names (first name, last name), their home address, and TaxiGoes payment identifier. Each company gets a different discount rate, which is specific to the business account. TaxiGoes payment identifier is an internal identification number that is connected to a company-specific payment system that can use credit cards and bank accounts and other payment methods (you do not need to store information about the payment other than TaxiGoes payment identifier). TaxiGoes utilizes over a thousand cars that serve as taxis. The cars may be driven by drivers or they might be self-driving in certain areas. Cars are uniquely identified with their plate number. Each taxi has a source district. The source districts can be considered as regions where these cars often start their work and for business reasons taxis are expected to serve close-by areas to their source district. The city is currently divided into 12 districts but TaxiGoes plans to expand. The districts are identified by their district numbers (identifier) or a human-understandable name. The regions of the districts are important for many queries and they should be stored as complex polygons. Additionally, the database stores information about the color of the car, year, make, model, expected miles per gallon (MPG), number of seats the car has, and how many pieces of luggage it can carry. Drivers are identified by their drivers license number and their name, years of experience, and TaxiGoes payment identifier (to pay the drivers) are also stored. A customer can call a taxi from the TaxiGoes app using their location. Therefore, customer locations and the call time need to be stored whenever they call a taxi (or whenever the app has access to it). The customers are expected to provide a destination for their trip but they can choose to go elsewhere when the trip starts. TaxiGoes matches a taxi with the customer and directs the taxi to the pickup location provided by the customer. The taxi locations are stored whenever a taxi is idle to find the closest taxis to the customer. When a taxi arrives at the pickup location, the actual taxi trip starts. In regular intervals, information about the location of the taxi (during the trip) is stored. Note that the locations are timestamped. So, the locations of the taxis are stored both when they are idle and during the trip, but the status of the taxi should be identified. When the trip ends, the spatio-temporal summary information about the trip is stored as trip start and end times, the route (as a linestring), the fare multiplier, and the billed amount. Make sure you submit all the deliverables required. Part 1 Design an EER diagram with pictograms for your conceptual design. Add your assumptions. Before you conclude this part, please take a look into Part 4 to understand whether your model can answer the queries given. Please create a pdf document for your EER diagram (along with the notes). Make sure the name of the file is Part1.pdf.

Part 2 Create an object-relational mapping from the pictogram-enhanced ER model you created in Part 1. In the end of this part, you will have the logical model of your database; i.e., your relational design, primary key and foreign key relationships. If your database needs normalization, apply normalization (you can add as a comment why you choose to normalize your relations). Explicitly state the spatial data types (e.g., district_region (multipolygon)). You are expected to submit a pdf file named Part2.pdf.

Part 3 In this part, you are expected to use PostGIS with Postgresql. As you may have different operating systems or choose to run this on a container, the installation instructions may differ. If you do not have access to a computer, please let me know immediately. Otherwise, please download and install the following (preferably in your local). Full version of postgresql. pgAdmin4 (if it is not already included in the previous installation) PostGIS Please make sure you use the latest stable versions to avoid possible delays. Map the logical design (relational model) you have created to the physical model using DDL statements. These include but are not limited to CREATE TABLE or ALTER TABLE statements. You are expected to submit a single sql file that includes statements. Please name your file as Part3.sql.

Part 4 Based on the implemented database you have generated, please write queries (preferably SELECT statements, but you can create views or write stored procedures/functions) that will answer the following: a. For each customer who is not currently on a ride, list five closest taxis that are available (idle). Your result should include whether these taxis are from the same source region (based on the customers current location). b. For each district (region) and for each part of the day (morning, afternoon, evening, and night), list the total number of pickups and dropoffs. (You are expected to use the following definitions for parts of the day: Morning 5 am to 12 pm (noon), Afternoon 12 pm to 5 pm., Evening 5 pm to 10 pm. Night 10 pm to 5 am.) c. List the number of trips for each customer in the last two weeks. d. List the number of trips each driver had in the last 3 days, and find the part of the day they have worked the most frequently. You are expected to submit four sql files (Part4a.sql, Part4b.sql, Part4c.sql, and Part4d.sql). Please make sure you comment them accordingly.