Now that you have successfully completed the database design for NCAA the challenge is to implement this database. The chosen DBMS is Oracle 11G. 1. Use the enclosed recommended relational table design. Complete logical and physical design of the Database consisting of following. o For each of the table determine the appropriate integrity constraints consisting of following. o Appropriate Data Types and size for each attributes o Attributes requiring values (not null) and possible default values o Attributes requiring constraint (do not define too many constraint) o Define Referential Integrity using foreign keys § Whether Null values are allowed in foreign keys o Based on the above complete design of base tables o 2. Using SQL on Oracle creates all the above tables. Implement the constraints (decided above) if supported by ORACLE. Use the same table name and column name as given in enclosed design. ). 3. Enter enough (at least 10 rows) sample data in each tables either through insert commands or through SQL Developer. Remember to enter valid data in terms of constraints and referential integrity. nts: Avoid using on delete cascade. Use the default of set to null. Remember to create tables with no foreign keys first. Also enter data into them first. A Recommended Design: CONFERENCE ( , CONFERENCE_NAME) CONF-STATISTICS ( , WINNING_PCT, AVG_ATTENDANCE, RECRUITING_INCIDENT_COUNT) SCHOOL ( , SCHOOL_NAME, S_ADDRESS, S_CITY, S_STATE, S_ZIP, ) SCHOOL_DATA ( , INSTATE_PLAYERS, OUTSTATE_PLAYERS, TOTAL_SCHOLARSHIP, TOTAL_GRADUATES, PERCENTAGE_GRADUATED) STADIUM ( , STADIUM_LOCATION, STADIUM_SIZE, STADIUM_NAME, AVG_TICKET_PRICE, ) TEAM ( , TEAM_NAME, NO_GAMES_WON, NO_GAMES_LOST, RANK, ) HIGH-SCHOOL ( HIGH_SCHOOL_NAME, NO_ACTIVE_ATHLETES) PLAYER ( , PLAYER_NAME, YEAR_ENTERED, SCHOLARSHIP_COUNT, RATING, STATUS, GPA, YEAR_ENTERED, POSITION, ) INJURY ( , INJURY_DESC) PLAYER_INJURY ( , INJURY_DATE) COACH ( , COACH_NAME, CURRENT_TITLE, SALARY, BEGIN_DATE, ) COACH_EMP_HISTORY ( , TO_DATE, JOB_TITLE, SALARY) RECRUITMENT_ INCIDENT , INCIDENT_DATE, INCIDENT_CODE) INCIDENT ( , INCIDENT-DESC) COURSE ( COURSE-NAME) PLAYER-COURSE ( , SCHOOL_ID, GRADE) GAME ( , SCORE-DIFF, GAME-DATE, ATTENDANCE, START-TIME, TEAM1_SCORE, TEAM2-SCORE, TEAM1-INJURY-COUNT, TEAM2-INJURY-COUNT, Now that you have successfully completed the implementation of the NCAA database, created tables in ORACLE and loaded sample data the challenge is to write queries, reports and make the database easier to use by creating views, functions and stored procedures. 1. Create following views: a. List of players as shown in figure 6-4 of the case study. b. A view providing Team name, percentage of games won, percentage of games lost. c. A view providing school-id, school name, year, ratio of out of state player to instate playes, number of players that did not graduate. d. Run query select * from view_name for each of the above views to display data. 2. Write SQL to support following reports given in the case study. You can use views created above. Fig. 6-1, Fig. 6-7, Fig. 6-10, Fig. 6-11, Fig. 6-12, Fig. 6-14, Fig. 6-15 3. Create following stored procedures a. Find game played at a particular stadium that had maximum total number of enjuries (using stadium_id as input), display ids of two teams, date, attendence, score differencial, and total number of injuries. 4. Create functions for following a. Function to compute average attendace for home games (assume team 1 is the home team) played by a school (school id as input).
Sample references
