*MUST BE DONE ON Microsoft SQL Server Express AND SCREEN CAPTURES TO ALL BE IN O
*MUST BE DONE ON Microsoft SQL Server Express AND SCREEN CAPTURES TO ALL BE IN ONE WORD DOCUMENT*
*Deliveries/Criteria must be perfectly met*
Deliveries:
1. MDF and LDF files for database table build matching data dictionary.
2. database diagram in PDF or Word document.
3. Screen captures of your expanded navigation panel showing your CKs, DFs, Indexes and UN Key for each table.
4. Screen capture of your index on your view, showing columns of index.
5. Screen capture of your deleted and updated rows in your history tables.
Include the following constraints:
1. Check Coach Assignment Role: VC, AC, HC.
2. Check State 2 letters for any table with a state column.
3. Check phone and check zip for any table with a phone or zip column.
4. Default null phone to ”none provided” for any table with a phone.
5. Default state to ‘FL’ for any table with a state column.
Include the following views:
1. list all teams, captains, and coaches.
2. list all teams with their coaches and contact numbers.
Include the following SPROCs:
1. Player roster for a single team using team name as parameter.
2. Teams and their coaches using 2 parameters, filtered for team name and for coach role. (must demonstrate logic check).
Include the following triggers:
1. For delete player, insert into Player history table.
2. Instead of delete team, print “you do not have permission to delete a team record.”
3. For update player, insert into Player history table.
4. History table must have update/deleted column.
Include the following Indexes:
1. Player Table: Composite index of any 3 columns of your choice
2. Coach Assignment Table: Filtered index for CoachRole Column. Must include where clause in screen capture.
3. Coach Table: Phone Column set to Unique Key.
4. One of your views must be an indexed view with “_index” at end of view name.
