Case study You are employed as a Database Developer for a large IT development company. The company

Fantastic news! We've Found the answer you've been seeking!

Question:

Case study
You are employed as a Database Developer for a large IT development company. The company has been approached by a transport company called SmartMovers which has grown to a level where need to have an automated system to handle day today operational activities to meet customer demands. You are given the job to design and develop a database solution for SmartMovers to meet their business requirements.
The company transports chemical products from one location to another on behalf of registered customers. Customers are of three types namely Catogory1, 2 and 3. Each customer is given a unique customer number and his or her details have to be recorded in the system.
Each transport operation is called a job, which involves picking up one or more loads of the products from a customer requested start location and delivers it to a customer requested destination. A unique number is given for each job and for each load when they are created.
A Load is transported using a particular transport unit, which consists of a lorry, a driver, assistant, and a container (for carrying the product) A container is fixed to a trailer so it needs to be coupled to a lorry at the start of a Job. (Single container can transport only one chemical product due to safety requirements)
Payments for a job depends on customer type, product type (No Risk, High risk) and the load type (small, medium and large). Transport operations are run from 9 regional depots. Jobs are allocated to individual depots to service. Depots hold, manage and maintain their own transport units which are permanently allocated to them to service Jobs. Normally transport units are held at the base Depot they belong.
The final system should be able to provide the required information for management decision making and to handle daily operations efficiently and effectively.

1) Identify the user and system requirements to design a database for the above scenario.
2) Identify entities and attributes of the given scenario and design a relational database system using conceptual design (ER Model) by including identifiers (primary Key) of entities and cardinalities, participations of relationships.

3) Develop a relational database system according to the ER diagram you have created (Use SQL DDL statements).
Examples where SQL DDL statements used in relational database,