Windsor, a small city with a population of 50,000, recently purchased several buses and hired drivers to serve multiple routes in Windsor and neighboring areas. As the city’s IT manager, you have been tasked to set up an information system and database to manage the scheduling of buses and drivers that can be easily expanded as the city grows. You directed your team to follow best practices to develop a normalized database to manage scheduling of drivers and buses that is simple, flexible and non-redundant. For this scenario, assume that drivers can be assigned to different buses, and multiple buses will run on multiple routes. You may also assume the bus service is free, so no ticket purchases are required. Hint: The purpose of this assignment is to demonstrate an understanding of database modeling and design concepts and techniques. First, follow the examples on pages 282-284, 292 and 295 to build an ERD and decompose a many-to-many relationship into one-to-many relationships using an associative entity to link the entities. Assume a many-to-many (M:N) relationship between drivers and buses. Thus, a driver can drive many buses and a bus can be driven by many drivers. Now, decompose this relationship using an associative entity. Next, assume a M:N relationship between bus and route. A bus can be assigned to multiple routes and a route can have multiple buses assigned. Decompose this relationship using an associative entity, as well. Next, convert the ERD to a normalized table design and include the important data fields. While using MS Access is encouraged to describe the data table designs, it is not required. You can also use the standard (textual) or graphical notations described in chapter 9 (reference p. 285, 294-295). Refer to the Activities Page for links to some short YouTube videos that describe normalization, and how to implement one-to-many and many-to-many relationships using MS Access. Tasks Draw an ERD for the Windsor Transportation System and indicate the cardinality. Create 3NF table designs and include the fields for each of the tables.
Windsor, a small city with a population of 50,000, recently purchased several buses and hired drivers to serve multiple routes in Windsor and neighboring areas. As the city’s IT manager, you have been tasked to set up an
For this scenario, assume that drivers can be assigned to different buses, and multiple buses will run on multiple routes. You may also assume the bus service is free, so no ticket purchases are required.
Hint: The purpose of this assignment is to demonstrate an understanding of database modeling and design concepts and techniques. First, follow the examples on pages 282-284, 292 and 295 to build an ERD and decompose a many-to-many relationship into one-to-many relationships using an associative entity to link the entities. Assume a many-to-many (M:N) relationship between drivers and buses. Thus, a driver can drive many buses and a bus can be driven by many drivers. Now, decompose this relationship using an associative entity. Next, assume a M:N relationship between bus and route. A bus can be assigned to multiple routes and a route can have multiple buses assigned. Decompose this relationship using an associative entity, as well. Next, convert the ERD to a normalized table design and include the important data fields. While using MS Access is encouraged to describe the data table designs, it is not required. You can also use the standard (textual) or graphical notations described in chapter 9 (reference p. 285, 294-295). Refer to the Activities Page for links to some short YouTube videos that describe normalization, and how to implement one-to-many and many-to-many relationships using MS Access.
Tasks
- Draw an ERD for the Windsor Transportation System and indicate the cardinality.
- Create 3NF table designs and include the fields for each of the tables.
Step by step
Solved in 2 steps with 1 images
