You are in the process to create a database that contains all information about different Taxi...

 

Transcribed Image Text:

You are in the process to create a database that contains all information about different Taxi companies in your country. This will help the customers to select their desired company, and reserve the appropriate car, and driver accordingly. For that reason, your TAXI RESERVATION Database should be composed as following: ➤ The Taxi Company has a unique name, a unique company registration number, a founding date, headquarter manager, headquarter address, and headquarter phone number. Each company can have many branches. ► Each branch has a name, an address, a phone number, a manager, two administrative staffs, taxi car owners, and drivers. It is not necessary that the number of cars is equal to the number of drivers. > Each of the manager and the administrative staffs has a name, a social security number, office number, address, birth date (The age should be between 18 and 65), and personal phone number (can have many phone numbers). ➤ The car owner can provide one or more cars to the company. Each owner has a name, id, date of birth, an address, and a phone number. ► Each taxi car has a unique plate number, model, and number of seats. A taxi car can be driven by any driver. > Each driver, which can be an owner of a car, has an id, name, age (less than 60), an address, phone number, and net salary. Each driver is assigned to a specific branch. ➤ All these taxis are only available for private clients. The client information is collected through phone calls. Each client has a name, an id, an address, and a phone number. ➤When a reservation is selected, the name and phone number of the client are taken, then the pick-up date/time and pick- up/drop-off address are noted, in addition to the chosen company and car. Each reservation is allocated a unique reservation_id. The reservation is assigned to a driver, start/end time of the trip, and fees. Note that if the driver makes more than three trips per a day, he will receive an extra of 5% of his net salary. ➤ You can add any assumption and suggestion if needed. Steps to Be Done Step 1: Data Modeling - Create an ER diagram, Step 2: Map ER diagram into a relational model, Step 4: Create the tables and fill them with appropriate records, Step 5: SQL Queries according to the requirements below SQL Queries: You should get the following information from your database: > Update the name of 2 branches of one company. ➤ List the name of drivers associated with a specified branch. List the name of drivers who are over 55 years old in all companies. The names and phone numbers of the Managers for a specific company. ➤ The names of all drivers in each city. The details of all cars in each branch for a specific company. > The total number of car owners in each branch for all companies listed in group in an alphabetically order. The total number of cars for each company. > The name of owners who have more than two cars. > The total number of private clients in each branch for a specific company. The total reservation undertaken by a driver on a given day. ➤ The total daily fee in each branch on a given date. ➤ The daily salary of all drivers. Write 5 additional queries that you find useful for this application. They should be complex queries (e.g. union, intersection, minus, nested queries...) You are in the process to create a database that contains all information about different Taxi companies in your country. This will help the customers to select their desired company, and reserve the appropriate car, and driver accordingly. For that reason, your TAXI RESERVATION Database should be composed as following: ➤ The Taxi Company has a unique name, a unique company registration number, a founding date, headquarter manager, headquarter address, and headquarter phone number. Each company can have many branches. ► Each branch has a name, an address, a phone number, a manager, two administrative staffs, taxi car owners, and drivers. It is not necessary that the number of cars is equal to the number of drivers. > Each of the manager and the administrative staffs has a name, a social security number, office number, address, birth date (The age should be between 18 and 65), and personal phone number (can have many phone numbers). ➤ The car owner can provide one or more cars to the company. Each owner has a name, id, date of birth, an address, and a phone number. ► Each taxi car has a unique plate number, model, and number of seats. A taxi car can be driven by any driver. > Each driver, which can be an owner of a car, has an id, name, age (less than 60), an address, phone number, and net salary. Each driver is assigned to a specific branch. ➤ All these taxis are only available for private clients. The client information is collected through phone calls. Each client has a name, an id, an address, and a phone number. ➤When a reservation is selected, the name and phone number of the client are taken, then the pick-up date/time and pick- up/drop-off address are noted, in addition to the chosen company and car. Each reservation is allocated a unique reservation_id. The reservation is assigned to a driver, start/end time of the trip, and fees. Note that if the driver makes more than three trips per a day, he will receive an extra of 5% of his net salary. ➤ You can add any assumption and suggestion if needed. Steps to Be Done Step 1: Data Modeling - Create an ER diagram, Step 2: Map ER diagram into a relational model, Step 4: Create the tables and fill them with appropriate records, Step 5: SQL Queries according to the requirements below SQL Queries: You should get the following information from your database: > Update the name of 2 branches of one company. ➤ List the name of drivers associated with a specified branch. List the name of drivers who are over 55 years old in all companies. The names and phone numbers of the Managers for a specific company. ➤ The names of all drivers in each city. The details of all cars in each branch for a specific company. > The total number of car owners in each branch for all companies listed in group in an alphabetically order. The total number of cars for each company. > The name of owners who have more than two cars. > The total number of private clients in each branch for a specific company. The total reservation undertaken by a driver on a given day. ➤ The total daily fee in each branch on a given date. ➤ The daily salary of all drivers. Write 5 additional queries that you find useful for this application. They should be complex queries (e.g. union, intersection, minus, nested queries...)

Answer rating: 100% (QA)

taxicompany registernumber INT companyname VARCHAR10 foundingdate VARCHAR10 headquarteraddress VARCH... View the full answer