Could anyone tell me the exact commands to put for each question on here please?

Question

Could anyone tell me the exact commands to put for each question on here please? This is the code of the nba.sql that it is speaking about from the problem. Not sure if it is used really or not.. Thank you if you can help. BY THE WAY (part 2 is possibly an er diagram)

SET ECHO ON

-- Drop the tables if they are already there.
-- OK to get an Oracle error if the table(s) not found (if running script for the first time)
drop table TEAM_PLAYER_ARENA;
drop table TEAM;
drop table PLAYER;
drop table ARENA;

-- Create the tables, views, constraints, etc..

-- Create the TEAM table
CREATE TABLE TEAM (    Team_ID        INTEGER,
                       Tname             VARCHAR(15),
                       Rank            INTEGER,
                       City             VARCHAR(20),
                       Championships   INTEGER,
                       PRIMARY KEY (Team_id)
                     );

-- Create the PLAYER table
CREATE TABLE PLAYER(     Player_ID    INTEGER,
                       Pname        VARCHAR(30),
                       Position    VARCHAR(20),
                       Draft_year    INTEGER,
                        Retire_year INTEGER,
                       Education    VARCHAR(30),
                       PRIMARY KEY(Player_ID)
                    );

-- Create the ARENA table
CREATE TABLE ARENA(   ARENA_ID    INTEGER,
                       Aname        VARCHAR(20),
                       City        VARCHAR(20),
                       PRIMARY KEY(ARENA_ID)
                   );

-- Create the TEAM_PLAYER_ARENA table
CREATE TABLE TEAM_PLAYER_ARENA (   Team_ID     INTEGER,
                                   Player_ID    INTEGER,
                                   ARENA_ID    INTEGER,
                                   Points        INTEGER,
                                   PRIMARY KEY(Team_ID, Player_ID, ARENA_ID),
                                   FOREIGN KEY(Team_ID)    REFERENCES TEAM,
                                   FOREIGN KEY(Player_ID)    REFERENCES PLAYER,
                                   FOREIGN KEY(ARENA_ID)    REFERENCES ARENA
                                );

-- Ensure all data is removed from the tables
truncate table TEAM_PLAYER_ARENA;
delete from TEAM;
delete from PLAYER;
delete from ARENA;

-- INSERT THE DATA WITH NAMED FIELDS (notice that the fields are not in the order the table was created)

insert into    Team    (Tname,    Rank , City, Championships, Team_ID)
values( 'Clippers' , 6 ,    'LA'           , 0 , 1 );
insert into    Team    (Tname,    Rank , City, Championships, Team_ID)   
values( 'Bulls'     , 5 ,    'Chicago'     , 6,    2 );
insert into    Team    (Tname,    Rank , City, Championships, Team_ID)   
values('Hornets'   , 9 ,    'Charlotte'   , 0,    3 );
insert into    Team     (Tname,    Rank , City, Championships, Team_ID)  
values('Blazers'   , 2 ,    'Portland'    , 0,   4 );
insert into    Team     (Tname,    Rank , City, Championships, Team_ID)  
values('Spurs'        , 7 ,    'San Antonio' , 5,   5 );
insert into    Team     (Tname,    Rank , City, Championships, Team_ID)  
values('Mavericks' , 4 ,    'Dallas'      , 1,    6 );

-- INSERT THE DATA WITH ORDER OF FIELDS
insert into    PLAYER    values( 1 , 'Chris Paul'        ,   'Small Forward'   , 2005, null, 'Wake Forest'         );
insert into    PLAYER    values( 2 , 'Tony Parker'        ,    'Point guard'   , 2001, null, 'INSEP'                );
insert into    PLAYER    values( 3 , 'Marco Belinelli'   ,    'Shooting guard', 2007, null, 'San Giovanni'         );
insert into    PLAYER    values( 4 , 'Gary Neal'        ,    'Power Forward'   , 2007, null, 'La Salle University');
insert into    PLAYER    values( 5 , 'Kawhi Leonard'   ,    'Forward'        , 2011, null, 'San Diego State'    );
insert into    PLAYER    values( 6 , 'Patty Mills'       ,    'Point guard'    , 2009, null, 'Marist College'     );
insert into    PLAYER    values( 7 , 'Tyson Chandler'   ,    'Center'       , 2001, null, 'Dominguez'          );
insert into    PLAYER    values( 8 , 'Derek Fisher'       ,    'Shooting guard', 1996, 2014, 'Arkansas'            );

-- -------------------------------------

insert into    ARENA    values( 1 ,    'American Airlines' ,     'Miami'      );
insert into    ARENA    values( 2 ,    'Moda Center'       ,     'Portland'   );
insert into    ARENA    values( 3 ,    'Staples Center'    ,     'LA'         );
insert into    ARENA    values( 4 ,    'United Center'     ,     'Chicago'    );
insert into    ARENA    values( 5 ,    'TD Garden'         ,     'Boston'     );
insert into    ARENA    values( 6 ,    'ATT Center'        ,     'San Antonio');
insert into    ARENA    values( 7 ,    'Philips Arena'     ,     'Atlanta'    );

-- ---------------------------------------------------------------------

insert into    TEAM_PLAYER_ARENA   values( 1 ,    1 ,    1 ,     20 );
insert into    TEAM_PLAYER_ARENA    values( 1 ,    1 ,    4 ,     17 );
insert into    TEAM_PLAYER_ARENA    values( 2 ,    3 ,    1 ,     32 );
insert into    TEAM_PLAYER_ARENA    values( 2 ,    3 ,    2 ,     22 );
insert into    TEAM_PLAYER_ARENA    values( 2 ,    3 ,    3 ,     13 );
insert into    TEAM_PLAYER_ARENA    values( 2 ,    3 ,    4 ,     25 );
insert into    TEAM_PLAYER_ARENA    values( 2 ,    3 ,    5 ,    06 );
insert into    TEAM_PLAYER_ARENA    values( 2 ,    3 ,    6 ,    14 );
insert into    TEAM_PLAYER_ARENA    values( 2 ,    3 ,    7 ,    18 );
insert into    TEAM_PLAYER_ARENA    values( 2 ,    5 ,    2 ,    11 );
insert into    TEAM_PLAYER_ARENA    values( 3 ,    3 ,    1 ,    20 );
insert into    TEAM_PLAYER_ARENA    values( 3 ,    4 ,    2 ,    05 );
insert into    TEAM_PLAYER_ARENA    values( 4 ,    6 ,    3 ,    23 );
insert into    TEAM_PLAYER_ARENA    values( 4 ,    6 ,    7 ,    18 );
insert into    TEAM_PLAYER_ARENA    values( 5 ,    2 ,    2 ,    24 );
insert into    TEAM_PLAYER_ARENA    values( 5 ,    2 ,    4 ,    10 );
insert into    TEAM_PLAYER_ARENA    values( 5 ,    5 ,    5 ,    25 );
insert into    TEAM_PLAYER_ARENA    values( 5 ,    5 ,    7 ,    16 );
insert into    TEAM_PLAYER_ARENA    values( 5 ,    6 ,    2 ,    24 );
insert into    TEAM_PLAYER_ARENA    values( 5 ,    1 ,    4 ,    33 );
insert into    TEAM_PLAYER_ARENA    values( 5 ,    3 ,    4 ,    31 );
insert into    TEAM_PLAYER_ARENA    values( 5 ,    4 ,    4 ,    26 );
insert into    TEAM_PLAYER_ARENA    values( 5 ,    5 ,    4 ,    14 );
insert into    TEAM_PLAYER_ARENA    values( 5 ,    6 ,    4 ,    05 );

-- COMMIT and save data
COMMIT;      

Topics: Relational Algebra and SQL Queries Section 1 This section covers the practical implementation of a database schema using DDL and SQL queries (50 points) Login in to the Linux Oracle server and access the database using sqlplus (refer to Oracle Linux Server Instructions) Display the SQL commands using: SET ECHO ON Create your homework submission log file using: SPOOL 2-FirstLastName-Lab.txt command To terminate log file after working on parts A) and B) use: SPOOL OFF command A) Using the nba.sql script file provided, create the below NBA schema. (5 points) team team ID tname rank INTEGER VARCHAR(15) INTEGER VARCHAR(20) championships INTEGER player team_player arena team ID INTEGER player ID pname position draft year INTEGER retire year INTEGER education VARCHAR(30) INTEGER VARCHAR30) VARCHAR(20) player ID INTEGER arena ID INTEGER points INTEGER arena ID INTEGER aname VARCHAR(20) city VARCHAR(20) The team table has a team_id primary key The player table has a player_id primary key The arena table has an arena_id primary key

Explanation / Answer

Answer A

+set echo on

+--

+-- Drop the tables if they are already there. Ok to get an Oracle error if the table(s) not found (if running script for the first time)

+drop table TEAM_PLAYER_ARENA;

+drop table TEAM;

+drop table PLAYER;

+drop table ARENA;

+--

+--

+--

+-- Create the tables, views, constraints, etc..

+--

+-- Create the TEAM table

+create table TEAM( Team_ID integer ,

+ Tname varchar(15) ,

+ Rank integer ,

+ City varchar(20) ,

+ Championships integer ,

+--

+ primary key(TEAM_ID)

+ );

+--

+--

+-- Create the PLAYER table

+-- create table PLAYER(Player_ID varchar(5), Pname varchar(30), Position varchar(20), draft_year integer, Education varchar(30), primary key(Player_ID));

+--

+--

+create table PLAYER( Player_ID integer ,

+ Pname varchar(30) ,

+ Position varchar(20) ,

+ draft_year integer ,

+ retire_year integer ,

+ Education varchar(30) ,

+--

+ primary key(Player_ID)

+ );

+--

+--

+--

+-- Create the ARENA table

+create table ARENA( ARENA_ID integer ,

+ Aname varchar(20) ,

+ city varchar(20) ,

+--

+ primary key(ARENA_ID)

+ );

+--

+--

+--

+-- Create the TEAM_PLAYER_ARENA table

+create table TEAM_PLAYER_ARENA ( Team_ID integer ,

+ Player_ID integer ,

+ ARENA_ID integer ,

+ points integer ,

+--

+ primary key(Team_ID, Player_ID, ARENA_ID) ,

+ foreign key(Team_ID) references TEAM ,

+ foreign key(Player_ID) references PLAYER ,

+ foreign key(ARENA_ID) references ARENA

+ );

+--

+--

+-- Ensure all data is removed from the tables

+truncate table TEAM_PLAYER_ARENA;

+delete from TEAM;

+delete from PLAYER;

+delete from ARENA;

+--

+--

+--

+--

+-- INSERT THE DATA --

+--

+--

+insert into Team values( 1 , 'Clippers' , 6 , 'LA' , 0 );

+insert into Team values( 2 , 'Bulls' , 5 , 'Chicago' , 6 );

+insert into Team values( 3 , 'Hornets' , 9 , 'Charlotte' , 0 );

+insert into Team values( 4 , 'Blazers' , 2 , 'Portland' , 0 );

+insert into Team values( 5 , 'Spurs' , 7 , 'San Antonio' , 5 );

+insert into Team values( 6 , 'Mavericks' , 4 , 'Dallas' , 1 );

+--

+--------------------------------------------------

+--

+insert into PLAYER values( 1 , 'Chris Paul' , 'Small Forward' , 2005, null, 'Wake Forest' );

+insert into PLAYER values( 2 , 'Tony Parker' , 'Point guard' , 2001, null, 'INSEP' );

+insert into PLAYER values( 3 , 'Marco Belinelli' , 'Shooting guard', 2007, null, 'San Giovanni' );

+insert into PLAYER values( 4 , 'Gary Neal' , 'Power Forward' , 2007, null, 'La Salle University');

+insert into PLAYER values( 5 , 'Kawhi Leonard' , 'Forward' , 2011, null, 'San Diego State' );

+insert into PLAYER values( 6 , 'Patty Mills' , 'Point guard' , 2009, null, 'Marist College' );

+insert into PLAYER values( 7 , 'Tyson Chandler' , 'Center' , 2001, null, 'Dominguez' );

+insert into PLAYER values( 8 , 'Derek Fisher' , 'Shooting guard', 1996, 2014, 'Arkansas' );

+--

+-- -------------------------------------

+--

+--

+--

+insert into ARENA values( 1 , 'American Airlines' , 'Miami' );

+insert into ARENA values( 2 , 'Moda Center' , 'Portland' );

+insert into ARENA values( 3 , 'Staples Center' , 'LA' );

+insert into ARENA values( 4 , 'United Center' , 'Chicago' );

+insert into ARENA values( 5 , 'TD Garden' , 'Boston' );

+insert into ARENA values( 6 , 'ATT Center' , 'San Antonio');

+insert into ARENA values( 7 , 'Philips Arena' , 'Atlanta' );

+--

+-- ---------------------------------------------------------------------

+--

+insert into TEAM_PLAYER_ARENA values( 1 , 1 , 1 , 20 );

+insert into TEAM_PLAYER_ARENA values( 1 , 1 , 4 , 17 );

+insert into TEAM_PLAYER_ARENA values( 2 , 3 , 1 , 32 );

+insert into TEAM_PLAYER_ARENA values( 2 , 3 , 2 , 22 );

+insert into TEAM_PLAYER_ARENA values( 2 , 3 , 3 , 13 );

+insert into TEAM_PLAYER_ARENA values( 2 , 3 , 4 , 25 );

+insert into TEAM_PLAYER_ARENA values( 2 , 3 , 5 , 06 );

+insert into TEAM_PLAYER_ARENA values( 2 , 3 , 6 , 14 );

+insert into TEAM_PLAYER_ARENA values( 2 , 3 , 7 , 18 );

+insert into TEAM_PLAYER_ARENA values( 2 , 5 , 2 , 11 );

+insert into TEAM_PLAYER_ARENA values( 3 , 3 , 1 , 20 );

+insert into TEAM_PLAYER_ARENA values( 3 , 4 , 2 , 05 );

+insert into TEAM_PLAYER_ARENA values( 4 , 6 , 3 , 23 );

+insert into TEAM_PLAYER_ARENA values( 4 , 6 , 7 , 18 );

+insert into TEAM_PLAYER_ARENA values( 5 , 2 , 2 , 24 );

+insert into TEAM_PLAYER_ARENA values( 5 , 2 , 4 , 10 );

+insert into TEAM_PLAYER_ARENA values( 5 , 5 , 5 , 25 );

+insert into TEAM_PLAYER_ARENA values( 5 , 5 , 7 , 16 );

+insert into TEAM_PLAYER_ARENA values( 5 , 6 , 2 , 24 );

+insert into TEAM_PLAYER_ARENA values( 5 , 1 , 4 , 33 );

+insert into TEAM_PLAYER_ARENA values( 5 , 3 , 4 , 31 );

+insert into TEAM_PLAYER_ARENA values( 5 , 4 , 4 , 26 );

+insert into TEAM_PLAYER_ARENA values( 5 , 5 , 4 , 14 );

+insert into TEAM_PLAYER_ARENA values( 5 , 6 , 4 , 05 );

+--

+--

+-- COMMIT and save data

Answer B

Print the player ID and names of players of pount guards drafted after year 2000

DECLARE

team_id_success INTEGER := 2;

team_id_fail INTEGER := 2000;

my_team_record TEAM_pkg.TeamRecord;

BEGIN

--SUCCESS

my_team_record := TEAM_PKG.getTeam(team_id_success);

dbms_output.put_line('Team ID: ' || my_team_record.team_id);

dbms_output.put_line('Team Name: ' || my_team_record.t_name);

dbms_output.put_line('Rank: ' || my_team_record.rank);

dbms_output.put_line('City: ' || my_team_record.city);

dbms_output.put_line('# Championships '|| my_team_record.championships);

--FAIL

my_team_record := TEAM_PKG.getTeam(team_id_fail);

dbms_output.put_line('Team ID: ' || my_team_record.team_id);

dbms_output.put_line('Team Name: ' || my_team_record.t_name);

dbms_output.put_line('Rank: ' || my_team_record.rank);

dbms_output.put_line('City: ' || my_team_record.city);

dbms_output.put_line('# Championships '|| my_team_record.championships);

END;

retired players list

DECLARE

my_player_id INTEGER := 9;

my_retire_year INTEGER := 2014;

fail_player_id INTEGER := 11;

check_id INTEGER;

BEGIN

--SUCCESS

check_id := PLAYER_pkg.retire(my_player_id, my_retire_year);

dbms_output.put_line(check_id);

--FAIL

check_id := PLAYER_pkg.retire(fail_player_id, my_retire_year);

dbms_output.put_line(check_id);

END;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---