A rural region of the country has a bus Cooperative that owns a number of buses and provides transportation service to small towns within a rural area. Each bus is assigned to a particular route through these small towns, although some routes may have multiple buses. Each of these bus route passes through a number of small towns. One or more drivers are assigned to each stage of a route, where a stage corresponds to a journey through some or all of the towns on a route. Some of the small towns have a garage where buses are stored overnight and each of the buses are identified by the registration number and can carry different numbers of passengers, since the vehicles vary in size and can be single or double-decked. Each route is identified by a route number and information is available on the average number of passengers carried per day for each route. Drivers have an employee number, name, address, and sometimes a telephone number. Your task is the following:

Develop a conceptual data model for a database that can be used by the cooperative for running its transportation service. You will need to • Identify the entities needed in the database. For each entity you will need to identify a small but reasonably chosen set of attributes. Some attributes are mentioned, others will need to be created • Identify the relationships among the entities and their cardinalities, based on the narrative above. Make reasonable assumptions about the business operation wherever needed. (a) Develop an ER diagram using the Crow’s foot template in Visio. Be sure to specify all assumptions you have made in drawing the diagram. (b) Convert the ER diagram into a relational database design. Show the diagram using the Database Model template of Visio.