The writer is very fast, professional and responded to the review request fast also. Thank you.
Book/movie review
22/08/2022
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 Submit this design as a word document justying your design.
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. (You must submit a text file of create table statements).
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.
Hints:
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_ID, CONFERENCE_NAME)
CONF-STATISTICS (CONFERENCE_ID, YEAR, WINNING_PCT, AVG_ATTENDANCE, RECRUITING_INCIDENT_COUNT)
SCHOOL (SCHOOL_ID, SCHOOL_NAME, S_ADDRESS, S_CITY, S_STATE, S_ZIP, CONFERENCE_ID)
SCHOOL_DATA (SCHOOL_ID, YEAR, INSTATE_PLAYERS, OUTSTATE_PLAYERS, TOTAL_SCHOLARSHIP, TOTAL_GRADUATES, PERCENTAGE_GRADUATED)
STADIUM (STADIUM_ID, STADIUM_LOCATION, STADIUM_SIZE, STADIUM_NAME, AVG_TICKET_PRICE, SCHOOL_ID)
TEAM (TEAM_ID, TEAM_NAME, NO_GAMES_WON, NO_GAMES_LOST, RANK, SCHOOL_ID)
HIGH-SCHOOL (HIGH_SCHOOL_ID, HIGH_SCHOOL_NAME, NO_ACTIVE_ATHLETES)
PLAYER (PLAYER_ID, PLAYER_NAME, YEAR_ENTERED, SCHOLARSHIP_COUNT, RATING, STATUS, GPA, YEAR_ENTERED, TEAM_ID, SCHOOL_ID, POSITION, HIGH-SCHOOL_ID)
INJURY (INJURY_CODE, INJURY_DESC)
PLAYER_INJURY (PLAYER_ID, INJURY_CODE, GAME_ID, INJURY_DATE)
COACH (COACH_ID, COACH_NAME, CURRENT_TITLE, SALARY, BEGIN_DATE, TEAM_ID)
COACH_EMP_HISTORY (COACH_ID, TEAM_ID, FROM_DATE, TO_DATE, JOB_TITLE, SALARY)
RECRUITMENT_ INCIDENT (INCIDENT_ID, TEAM_ID, PLAYER_ID, INCIDENT_DATE, INCIDENT_CODE)
INCIDENT (INCIDENT_ID, INCIDENT-DESC)
COURSE (COURSE_ID, SCHOOL_ID, COURSE-NAME)
PLAYER-COURSE (PLAYER_ID, COURSE_ID, SCHOOL_ID, GRADE)
GAME (GAME_ID, SCORE-DIFF, GAME-DATE, ATTENDANCE, START-TIME, TEAM1_SCORE, TEAM2-SCORE, TEAM1-INJURY-COUNT, TEAM2-INJURY-COUNT, TEAM1-ID, TEAM2-ID, STADIUM-ID)
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 id’s 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).
The writer is very fast, professional and responded to the review request fast also. Thank you.
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.
Read moreEach paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.
Read moreThanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.
Read moreYour email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.
Read moreBy sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.
Read more