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, we 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. We directed your team to follow best practices to develop a normalized database to manage the scheduling of drivers and buses that is simple, flexible, and non-redundant. For this scenario above, assume that drivers can be assigned to different buses, and multiple buses will run on multiple routes. We may also assume the bus service is free, so no ticket purchases are required. Hint: 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. We can either use MS Access to describe the data table designs. OR, we can also use the standard (textual) or graphical notations. 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, we 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. We directed your team to follow best practices to develop a normalized database to manage the scheduling of drivers and buses that is simple, flexible, and non-redundant. For this scenario above, assume that drivers can be assigned to different buses, and multiple buses will run on multiple routes. We may also assume the bus service is free, so no ticket purchases are required. Hint: 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. We can either use MS Access to describe the data table designs. OR, we can also use the standard (textual) or graphical notations. 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
Fundamentals of Information Systems
9th Edition
ISBN:9781337097536
Author:Ralph Stair, George Reynolds
Publisher:Ralph Stair, George Reynolds
Chapter3: Database Systems And Big Data
Section: Chapter Questions
Problem 4PSE
Related questions
Question
100%
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, we have been tasked to set up an
- For this scenario above, assume that drivers can be assigned to different buses, and multiple buses will run on multiple routes. We may also assume the bus service is free, so no ticket purchases are required.
Hint: 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. We can either use MS Access to describe the data table designs. OR, we can also use the standard (textual) or graphical notations.
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
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 1 images
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Recommended textbooks for you
Fundamentals of Information Systems
Computer Science
ISBN:
9781337097536
Author:
Ralph Stair, George Reynolds
Publisher:
Cengage Learning
Principles of Information Systems (MindTap Course…
Computer Science
ISBN:
9781305971776
Author:
Ralph Stair, George Reynolds
Publisher:
Cengage Learning
Principles of Information Systems (MindTap Course…
Computer Science
ISBN:
9781285867168
Author:
Ralph Stair, George Reynolds
Publisher:
Cengage Learning
Fundamentals of Information Systems
Computer Science
ISBN:
9781337097536
Author:
Ralph Stair, George Reynolds
Publisher:
Cengage Learning
Principles of Information Systems (MindTap Course…
Computer Science
ISBN:
9781305971776
Author:
Ralph Stair, George Reynolds
Publisher:
Cengage Learning
Principles of Information Systems (MindTap Course…
Computer Science
ISBN:
9781285867168
Author:
Ralph Stair, George Reynolds
Publisher:
Cengage Learning
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781305627482
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781285196145
Author:
Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:
Cengage Learning