Crow's Foot ERD Diagrams

Deadline is 24 hours. max price is $45 Part 1: Sunset Zoo The Sunset Zoo currently tracks its membership using a spreadsheet, and wants to upgrade to using a relational database. You have been tasked with this conversion, and need to convert this flat field design into a relational database design. Sunset Zoo has given you its current spreadsheet design, which contains the following fields (columns). • Member ID • Member Name • Member Phone • Member Email • Membership Level Name • Membership Level Description • Membership Price • Price w/tax • Paid Until Date • Member Since • Event 1 Name • Event 1 Description • Event 1 Attended • Event 2 Name • Event 2 Description • Event 2 Attended • Event 3 Name • Event 3 Description • Event 3 Attended Sunset Zoo has also given you the following business rules, which are incomplete and are not all necessarily structural. • Members purchase a yearly membership. • The memberships are one of three levels: Basic, Zoo Friend, Zoo Star. • Memberships are generally paid once a year. • Three special events are offered each year, and attendance is tracked for each member. 1. To get started, create a complete list of business rules that you will use as a basis for your logical diagram. Your list of business rules should specify all entities, relationships, optionality constraints, and plurality constraints, and must make provision for the data and business rules given to you by the Sunset Zoo. There are many solutions that will correctly make provision for the given data and business rules. 2. Now create a logical entity-relationship diagram (ERD) that illustrates the relational database design described by the business rules in #1. All entities should be normalized to BCNF. Recall that logical ERDs contain SQL-based constraints on the attributes, including primary and foreign key constraints. Part 2: Sports Fans The following incomplete list of business rules describe the relationships between some of the different types of sports fans in New England. 1. Baseball Fans can be Red Sox fans, Yankee Fans, both, or neither. 2. All Baseball Fans are People, but not all People are Baseball Fans. 3. Some Red Sox fans are also Celtics Fans. 4. Celtics Fans are People, but not all people are Celtics Fans. 5. Both Red Sox fans and Celtics Fans are Sports Fans. 6. Sports Fans are People, but not all People are Sports Fans. This is may seem fairly simple, and intuitively obvious to anyone living in Boston, but there are some subtleties nonetheless. "Red Sox" and "Yankees" are names of two rival baseball teams in Boston and New York, and "Celtics" is the name of the basketball team in Boston. I’ve helped you a bit by capitalizing the words for entities. 1. Create a complete list of business rules that are consistent with the incomplete list given above. This list should make provision for all entities and relationships given in the above list, and there are many solutions that do so. 2. Develop an extended entity-relationship diagram (EERD) that illustrates the relational database design described by the business rules in #1. The diagram should include the primary key of each table in order to demonstrate correct implementation of specialization-generalization. While EERDs may well contain other attributes, in this assignment you need not include them because they are not described in the business rules. You should use legal database identifiers such as Yankees_Fan or Sports_Fan in your EERD.