Part III ER Mapping: Player(JerseyNumber, SchoolAttended, Name.first, Name.middl
ID: 3809965 • Letter: P
Question
Part III ER Mapping:
Player(JerseyNumber, SchoolAttended, Name.first, Name.middle, Name.last, TeamName)
Student(JerseyNumber, CreditHours)
Athlete(JerseyNumber, AthleteNum, Trainer)
Team(TeamName, Conference, Record.wins, Record.loses)
Head(CoachID, YearsCoached, Name.first, Name.middle, Name.last CoachType, CoachedBefore)
Assistant(CoachID, YearsCoached, Name.first, Name.middle, Name.last CoachType, Position)
Team-Coach(TeamName, CoachID)
Company(TaxIDnum, Name)
Person(ssn, Name, Income)
Owner(Owner_ID, YearsOwned)
Team-Owner(TeamName, Owner_ID)
Arena(Address, Name, Occupancy)
Team-Arena(TeamName, Address)
Referee(EmployeeNum, Name, Hometown, YearsExperience)
Arena-Referee(Address, EmployeeNum)
Create tables according to the mapping. For this part, submit a description of the tables created.
Add at least five records. Submit a copy of all the records inserted.
Write and answer some (at least five) “reasonable” queries for your database. When I say reasonable queries – I mean 3-4 table joins to use most of the tables or group of tables in your database. You should not have tables that are of no use. For this part, first, write the queries in English – that is, telling me what you are finding in the queries. Then, present the query and finally present the output of the query (as you do for your homework assignments).
Explanation / Answer
Create Tables:
BEGIN TRANSACTION;
create table player(JerseyNum integer primary key,schoolAttended text, nameFirst text, nameMiddle text, nameLast text, teamName text);
create table student(jerseyNum integer primary key, creditHours integer);
create table athlete(jerseyNum integer , athleteNum integer primary key, trainer text);
create table team(teamname text primary key, conference text,record_wins integer,record_loss integer);
create table head(coachId integer primary key,yearsCoached integer,Name_first text,Name_last text,coachType text,coachedBefore text);
create table assistant(coachId integer primary key, yearsCoached integer, Name_first text,Name_middle text,Name_Last text,coachType text,position text);
create table teamCoach(TeamName text, coachId integer, primary key(teamName, coachId));
create table company(TaxIdNum integer primary key, Name text);
create table person(ssn integer primary key, Name text,income integer);
create table owner(owner_id integer primary key,yearsOwned integer);
create table team_owner(teamName text,owner_id integer, primary key(teamName, owner_id));
create table arena(address text primary key,Name text,occupancy text);
create table teamArena(teamName text,address text, primary key(teamName,address));
create table referee(employeeNum integer primary key, name text, hometown text,yearsExperience integer);
create table arenaReferee(address text,employeeNum integer,primary key(address,employeeNum));
Insert Values into the tables:
insert into player values(1,'shishuKakali','debstuti','kumari','das','jeebak');
insert into player values(2,'saratkumari','puja','none','das','team1');
insert into player values(3,'kalyaniGovt','deepak','none','roy','team2');
insert into player values(4,'RamkrishnaMission','nil','none','kumar','team1');
insert into player values(5,'saratkumari','raja','none','arora','jeebak');
insert into student values(1,10);
insert into student values(2,20);
insert into student values(3,10);
insert into student values(4,30);
insert into student values(5,40);
insert into athlete values(1,101,'ravi');
insert into athlete values(2,102,'kiran');
insert into athlete values(3,103,'sasi');
insert into athlete values(4,104,'smita');
insert into team values('team1','conf1',10,5);
insert into team values('team2','conf2',4,6);
insert into team values('jeebak', 'conf1',3,2);
insert into team_owner values('team1',201);
insert into team_owner values('team2',202);
insert into team_owner values('jeebak',201);
insert into owner values(201,10);
insert into owner values(202,5);
insert into company values(10,'abc');
insert into company values(11,'def');
insert into company values(12,'ghi');
insert into arena values('Bangalore EGL','Bangalore','1100');
insert into arena values('Pune ETZ','Pune','900');
insert into arena values('Mumbai CTS','Mumbai','1000');
insert into teamArena values('team1','Bangalore EGL');
insert into teamArena values('team2','Pune ETZ');
insert into teamArena values('Jeebak','Mumbai CTS');
insert into referee values(21,'Ashu','Kolkata',10);
insert into referee values(22,'Avnish','Delhi',20);
insert into referee values(23,'Rashi','Chennai',15);
insert into arenaReferee values('Bangalore EGL',21);
insert into arenaReferee values('Mumbai CTS',22);
insert into arenaReferee values('Pune ETZ',23);
Some Queries:
1.Select firstname of players who is an athlete and his/her creditHours is 10:
select nameFirst from player,student,athlete where player.jerseyNum=athlete.jerseyNum and player.jerseyNum=student.jerseyNum and student.creditHours=10;
select player.nameFirst,player.nameLast from player,Team_owner where player.teamName=team_owner.teamName and owner_id=201;
2.select firstname and creditHours of players whose team is owner by an owner for more than 5 years.
select player.nameFirst,student.creditHours from student,player,team_owner,owner where player.jerseyNum=student.jerseyNum and player.teamName=team_owner.teamName
and team_owner.owner_id=owner.owner_id and owner.yearsOwned>5 ;
3. select firstname and creditHours of players whose team is owner by an owner for 5 years.
select player.nameFirst,student.creditHours from student,player,team_owner,owner where player.jerseyNum=student.jerseyNum and player.teamName=team_owner.teamName
and team_owner.owner_id=owner.owner_id and owner.yearsOwned=5 ;
4.select player details whose team arena name is ‘Bangalore EGL’:
select * from player,teamArena,arena where player.teamName=teamArena.teamName and teamArena.address=arena.address and arena.name='Bangalore EGL';
5. Player whose referee will be ‘Avnish’:
select * from player,team,teamArena,arenaReferee,referee where player.teamname=team.teamname and team.teamname=teamArena.teamName and teamArena.address=arenaReferee.address and arenaReferee.employeeNum=referee.employeeNum and referee.name='Avnish';