Q3 Consider the following entities and their relationships.Game (game_name, no_of_players, coach_name)Player (pid, pname, address, club_name)Relation between Game and Player is Many to Many.Constraint Primary key, no of players should be > 0.Create a RDB in 3NF and write PL/SQL blocks in Orade for the following:1) Write a function which will retum total number of football players of Sports Club"2) Write a cursor which will display club wise details of players.
Answers
Answer:
cuyoaf76d5qf8tcy8wvy9dwug9wdug0dg0wudw9guwf9udw0ufw0ud2oud2gipwfipwfu0fwupwcu0cwpucwipcwpicwpicw0ice0ibspidbpibdpjvpjvsuo
Answer:
The final schema in 3NF can be represented as:
Game (game_name, no_of_players, coach_name, game_id [PK])
Player (pid [PK], pname, address)
Club (club_name [PK])
Participation (game_id [FK], pid [FK])
PK= Primary Key
FK= Foreign Key
Explanation:
To create a relational database in 3NF for the given entities and their relationships, we can start by identifying the functional dependencies and removing any transitive dependencies.
The Game and Player entities have a many-to-many relationship, which can be represented using a third entity, such as Participation, that contains foreign keys to both Game and Player.
The final schema in 3NF can be represented as:
Game (game_name, no_of_players, coach_name, game_id [PK])
Player (pid [PK], pname, address)
Club (club_name [PK])
Participation (game_id [FK], pid [FK])
PK= Primary Key
FK= Foreign Key
Now, we can write the PL/SQL blocks for the given tasks:
1. Write a function which will return the total number of football players of Sports Club:
CREATE OR REPLACE FUNCTION get_football_player_count(p_club_name IN VARCHAR2)
RETURN NUMBER
IS
v_player_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO v_player_count
FROM Player p
JOIN Participation pa ON p.pid = pa.pid
JOIN Game g ON g.game_id = pa.game_id
WHERE g.game_name = 'Football' AND p.club_name = p_club_name;
RETURN v_player_count;
END;
This function takes a club name as input and returns the count of football players belonging to that club. It does this by joining the Player, Participation, and Game tables and filtering on the input club name and the game name 'Football'.
2. Write a cursor which will display club wise details of players:
DECLARE
CURSOR player_cursor IS
SELECT p.pname, p.address, c.club_name, g.game_name
FROM Player p
JOIN Participation pa ON p.pid = pa.pid
JOIN Game g ON g.game_id = pa.game_id
JOIN Club c ON p.club_name = c.club_name
ORDER BY c.club_name;
v_player_name Player.pname%TYPE;
v_player_address Player.address%TYPE;
v_club_name Club.club_name%TYPE;
v_game_name Game.game_name%TYPE;
BEGIN
OPEN player_cursor;
LOOP
FETCH player_cursor INTO v_player_name, v_player_address, v_club_name, v_game_name;
EXIT WHEN player_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_club_name || ', ' || v_game_name || ', ' || v_player_name || ', ' || v_player_address);
END LOOP;
CLOSE player_cursor;
END;
This cursor retrieves player details joined with the Club, Participation, and Game tables, ordered by club name. It then loops through the results and outputs the club name, game name, player name, and player address concatenated with commas. The output is written to the console using the DBMS_OUTPUT package.
To learn more about database: https://brainly.in/question/15431925
To learn more about schema: https://brainly.in/question/39412532
#SPJ3