Project Description:
Oak Creek Stadium is a new sports arena that has recently been built in the Greensboro area. Your team has been contracted to develop a database for Oak Creek Stadium to keep track of its operations. The most important commodity in its operation are the customers that attend the arena. Management wants to capture customers' first and last name. They also want to record if the customers are season pass holders and if so, management wants to capture the start date of that pass in order to validate if their membership is current on any given day. For the issue of safety, the arena wants to keep a record of the customer's height. Management also wants to track which sporting event each customer attended and on what dates and times. Please be aware that customers will attend many sporting events and some attend multiple sporting events in one day (i.e. multiple basketball games back-to-back in one day 1:30P, 4:00P, etc.).
Oak Creek Stadium will also want to manage the data on its sporting events. Management will want to keep track of the sporting event type, the date and time of the sporting event, and how many seats will be available for each event. They also want to track the teams that are playing, the originating city and state of the teams, and the head coach for each team. The type of sporting event should also be tracked and will include: basketball, hockey, football, or soccer. Oak Creek Stadium has many employees that perform numerous tasks. Management keeps track of its employees through the Department that they belong to, including: Ticketing, Food Service, Gift Shop, Maintenance, and Security.
Management also wants to keep track of employee first and last names, hire dates, birth dates, and salary. Please remember that employees can perform multiple tasks for their department while employed at Oak Creek Stadium. Thus, each Department will also want to keep track of the job history for each employee including the type of task performed, the time and date of the task, and who performed the task.
In addition to sporting events, Oak Creek Stadium has several restaurants on its grounds. Management wants to keep track of what food items are purchased by their customers and at which restaurants. In addition, it wants to keep records of quantity purchased, date and time ofpurchase, name of the item, price, and type of food item which will be one of the following: drink, snack, or main dish. Some items can be found in multiple restaurants so the names and prices are standardized throughout the park. Restaurants should also have records and should include the restaurant name, location, type (concession stand or sit down restaurant), and if it's a sit down then record its capacity.
Oak Creek Stadium has put this request for proposal (RFP) out to bid with various consulting companies. Your consulting team is eager to win the bid and earn the substantial financial bonus that comes along with the project. Oak Creek Stadium is looking for a complete solution that will serve the needs of their company and they are eager to implement the system as soon as possible.
Phase I report should include:
1. Cover Page
-The cover page should include the course number, the semester, the team name, and the full names of all team members.
2. Table of Contents (including page numbers)
3. Executive Summary (less than one page)
In a concise, clear, and effective manner, explain to an executive the following points as listed below:
- the business need for a database system
- the proposed database system to address this need
- the goals of the database system
- the benefits of the database to the organization
4. Conceptual Database Design: ER Diagram
- Draw a complete entity relationship diagram that depicts the project described above. Your team can use any software to draw the ER diagram (ie. Microsoft Visio, Microsoft Access, etc.).
- Describe the entities, relationships, their respective attributes, the keys (primary and foreign) and constraints. State clearly any additional assumptions you have made.
- The ER diagram should have at least 10 entities.
5. Logical database design
- Convert the above ER diagram into relations (tables). Show all attributes in each relation.
Decompose the relations into 3NF. Here is an example of a table in 3NF: Faculty (FID, LName, Phone, Salary)
6. Database Implementation
- Develop instance charts for all tables in your database. Here is an example of an Instance Chart:
Faculty (FID, LName, Phone, Salary)
| Column Name |
Key Type |
Null/ unique |
FK Table |
FK Column |
Data type |
Max length |
| FID |
PK |
NN, U |
|
|
INT |
9 |
| LName |
|
NN |
|
|
VARCHAR |
25 |
| Phone |
|
NN |
|
|
VARCHAR |
10 |
| Salary |
|
NN |
|
|
DEC |
6,2 |
7. Peer Evaluation
Completed peer evaluation forms for each team member (template attached).
Group Project Peer Evaluation
Instruction: Please rate each team member, INCLUDING YOURSELF, on two scales.
1. How much effort everyone put in: Please put a percentage for each team member. The total percentage must add up to 100% for the team.
2. How cooperative each team member was: Please rate on 1-5 scale. 1 is not cooperative, 3 is average, and 5 is very cooperative. Things such as being helpful in coming up with ideas, timely email exchange, actively research the topic and share findings are viewed positive.
3. How much everyone contributed to the project: Please put a percentage for each team member. The total percentage must add up to 100% for the team. Note that the percentage of effort (input) may not equal the percentage of contribution (output).