Draw the Crow’s Foot ERD for the St. Louis Cardinals Stadium application described below.
St. Louis Cardinals Stadium
The stadium where the St. Louis Cardinals play their home games needs a better system for managing the vendors and concession sales. The management has asked you to design a database to help them keep track of this component of managing a large stadium.
The stadium is divided into levels (1 – 4) and each level is divided into sections. A Section consists of the Section number, the number of seats, and the ticket price for the seat in that section. The level is the first number of the section number, that is, section 134 is on Level 1, Section 256 is on Level 2, etc. No two sections have the same number.
A Vendor is a business that prepares and sells food and beverages. A vendor has a name, such as “Right Field Grill”, and section where it’s located. A vendor is located in just one section, however, a section may have a food court, which will contain one or more vendors.
Food Item – this is any food or beverage item. It will have a description, such as “Hot Dog” or “Soda”, the size (16 ounce, Medium, etc.), and the price that the vendor is permitted to charge for the item. All vendors must charge the same price for the same item. They also need to know whether the item is Food or Beverage. Each vendor sells multiple items, and an item can be sold by many vendors.
Vendors not only have a fixed location, but they are also permitted to sell in certain sections using “roamers”. A roamer is a person who works for one vendor, carries a food or beverage item on a tray, and wanders throughout the section selling that particular item. A roamer is permitted to sell only one particular food item and only roam one section. A vendor supplies food items for one or more roamers. The management needs to know the name and hire date of the roamer, along with his/her vendor, which section he/she roams and which item he/she sells.
There are also VIP Suites that are leased for the entire season by an individual or a business. Each suite has a suite name, a section number, and a caterer. A section may contain one or more suites, and a suite belongs to only one section. One vendor might cater several suites, for example, “Triple Play” vendor caters both the Ford VIP Suite, and the Cola-Cola Patio suites. Each suite is catered by at most one vendor.
The stadium management wants to eliminate paper forms and store all of this data in the database, so that they can conveniently call up information when they need it. You may specify surrogate primary keys as needed to simplify the database design.