I need support with this MySQL question so I can learn better.
- DD (data type, constraints)
- Database Diagram
- Tables, Views, Stored Procedures
Required Items: Tables
- You must have at least one Check, Default, Calculated Column.
- Each table must have PK index set to non-clustered.
- Each table must have a clustered index.
- You must have at least 120 players.
- https://usfondemand.hosted.panopto.com/Panopto/Pag… (Links to an external site.)
- You must have at least 10 teams. Each team must have exactly 11 players.
Required Items: Views
- You must have at least 3 views, 1 must be an indexed view.
- You must use ‘as’ to set labels for the columns in your result sets.
- View 1: List of winning teams and their goals scored for each match.
- View 2: All Players and their ages calculated from their DOBs.
- View 3: Total, Most, Least, and Average minutes played.
Required Items: Stored Procedures
- 3 sprocs, 1 with at least 2 parameters.
- Sproc 1: Player full names and ages, @player
- Sproc 2: Head coach name and phone, @coach
- Sproc 3: Player performance in a match, @player @match
- Additional Requirements: Logic checks
You must demonstrate each view and each sproc logic checked.
1. Data dictionary detailing column labels, data types, defaults, rules, checks, index, identity, unique.
2. Database diagram demonstrating connections (PK-FK) between tables.
3. Upload DBDD, MDF, LDF files.
1.SPROC: League needs a current season status listing all players, goals scored, minutes played, and penalties assessed for each, grouped by teams. Players must be listed by full name using concat operator. Teams must be listed by team name. List should be sorted by team, player, goals.
2.View: League needs a listing of all matches played so far this season. The list needs to include the teams playing in each match, the host field and goals scored by each team.
3.View: A separate list needs to be generated of the winning teams, the matches they won, the field they played on, and goal differential.
4.SPROC: Protest report query for a match needs to have teams participating, their respective head coaches, host field, referees assigned to the match, final score differential.
5.View: Listing all teams by name, head coach full name, and contact number.