Logical and Physical Database modelling
Budget: $30 – $250 AUD
Hi, I have attached am image of the fastCabs erd model. Please see it to understand this case:
A private taxi company called FastCabs was established in Hobart in 1992. Since then, the company has grown steadily and now has offices in most of the main cities of Australia. However, the company is now so large that more and more administrative staff are being employed to cope with the ever-increasing amount of paperwork. Furthermore, the communication and sharing of information within the company is poor. The Director of the company, Liam Andrew feels that too many mistakes are being made and that the success of his company will be short-lived if he does not do something to remedy the situation. He knows that a database could help in part to solve the problem and has approached you and your team to help in creating a database application to support the running of FastCabs.
The Director has provided the following brief description of how FastCabs operates. Each office has a Manager; several taxi owners, drivers and administrative staff. The Manager is responsible for the day-to-day running of the office. An owner provides one or more taxis to FastCabs and each taxi is allocated for use to a number of drivers. The majority of owners are also drivers. FastCab taxis are not available for hire by the public hailing a taxi in the street but must be requested by first phoning the company to attend a given address. There are two kinds of clients, namely private and business. The business provided by private clients is on an ad hoc basis. The details of private clients are collected on the first booking of a taxi. However, the business provided by business clients is more formal and involves agreeing a contract of work with the business. A contract stipulates the number of jobs that FastCabs will undertake for a fixed fee. When a job comes into FastCabs the name, phone number and contract number (when appropriate) of the client is taken and then the pick-up date/time and pick-up/drop-off addresses are noted. Each job is allocated a unique jobID. The nearest driver to the pick-up address is called by radio and is informed of the details of the job. When a job is completed the driver should note the mileage used and the charge made (for private clients only). If a job is not complete, the reason for the failed job should be noted.
1. Given the sample ER Model for the FastCabs case study (on page 4), draw the corresponding GRD, exhibiting all the primary keys and foreign keys. For simplicity, no other attributes nor multiplicity constraints are required. Please note, for the ER diagram you created in Assignment 1, the artefact of the conceptual database design, map the ER model into the relational model according to how it was designed in the ER diagram. An updated ERD is required as part of this question.
2. Create the database tables in SQL for all the relations in your GRD and enforce all the relevant constraints including primary and foreign keys. Provide screenshots of the SQL statements and executions.
A private taxi company called FastCabs was established in Hobart in 1992. Since then, the company has grown steadily and now has offices in most of the main cities of Australia. However, the company is now so large that more and more administrative staff are being employed to cope with the ever-increasing amount of paperwork. Furthermore, the communication and sharing of information within the company is poor. The Director of the company, Liam Andrew feels that too many mistakes are being made and that the success of his company will be short-lived if he does not do something to remedy the situation. He knows that a database could help in part to solve the problem and has approached you and your team to help in creating a database application to support the running of FastCabs.
The Director has provided the following brief description of how FastCabs operates. Each office has a Manager; several taxi owners, drivers and administrative staff. The Manager is responsible for the day-to-day running of the office. An owner provides one or more taxis to FastCabs and each taxi is allocated for use to a number of drivers. The majority of owners are also drivers. FastCab taxis are not available for hire by the public hailing a taxi in the street but must be requested by first phoning the company to attend a given address. There are two kinds of clients, namely private and business. The business provided by private clients is on an ad hoc basis. The details of private clients are collected on the first booking of a taxi. However, the business provided by business clients is more formal and involves agreeing a contract of work with the business. A contract stipulates the number of jobs that FastCabs will undertake for a fixed fee. When a job comes into FastCabs the name, phone number and contract number (when appropriate) of the client is taken and then the pick-up date/time and pick-up/drop-off addresses are noted. Each job is allocated a unique jobID. The nearest driver to the pick-up address is called by radio and is informed of the details of the job. When a job is completed the driver should note the mileage used and the charge made (for private clients only). If a job is not complete, the reason for the failed job should be noted.
1. Given the sample ER Model for the FastCabs case study (on page 4), draw the corresponding GRD, exhibiting all the primary keys and foreign keys. For simplicity, no other attributes nor multiplicity constraints are required. Please note, for the ER diagram you created in Assignment 1, the artefact of the conceptual database design, map the ER model into the relational model according to how it was designed in the ER diagram. An updated ERD is required as part of this question.
2. Create the database tables in SQL for all the relations in your GRD and enforce all the relevant constraints including primary and foreign keys. Provide screenshots of the SQL statements and executions.
Related categories:
SQL
Concept Design
Database Administration
Database Programming
Database Development