软件工程综合实践专题第一次作业-CSDN博客

软件工程综合实践专题第一次作业

题目：NBA数据库程序分析

来源：SQL SERVER数据库课程同学的小组大作业

create table Coach_information(

cname char(20) primary key,

tname char(20),

age int,

)

insert into Coach_information values('卢克沃顿','洛杉矶湖人','38')

insert into Coach_information values('史蒂夫科尔','金州勇士','50')

insert into Coach_information values('迈克德安东尼','休斯敦火箭','67')

insert into Coach_information values('布拉德史蒂文斯','波士顿凯尔特人','42')

select * from Coach_information

create table Team_information(

tname char(20) primary key,

qiuguan_name char(20),

cname char(20) foreign key references Coach_information(cname)

)

insert into Team_information values('洛杉矶湖人','斯台普斯中心','卢克沃顿')

insert into Team_information values('金州勇士','甲骨文球馆','史蒂夫科尔')

insert into Team_information values('休斯敦火箭','丰田中心','迈克德安东尼')

insert into Team_information values('波士顿凯尔特人','TD花园','布拉德史蒂文斯')

select * from Team_information

create table Match_information(

match_time char(20) unique,

tname char(20) primary key,

vname char(20)

)

insert into Match_information values('2019.6.11_9:00','洛杉矶湖人','波士顿凯尔特人')

insert into Match_information values('2019.4.28_8:00','金州勇士','休斯敦火箭')

select * from Match_information

create table Player_information(

pname char(50) primary key,

age int,

height float,

weighet float,

weizhi char(20),

number int,

tname char(20),

foreign key (tname)references Team_information(tname)

)

insert into Player_information values('勒布朗詹姆斯',33,2.03,113,'SF/PF',23,'洛杉矶湖人')

insert into Player_information values('布兰登英格拉姆',21,2.06,86,'SF',14,'洛杉矶湖人')

insert into Player_information values('朗佐鲍尔',21,1.98,86,'PG',2,'洛杉矶湖人')

insert into Player_information values('凯尔库兹马',23,2.06,100,'SF',0,'洛杉矶湖人')

insert into Player_information values('贾维尔麦基',30,2.13,122,'C',7,'洛杉矶湖人')

insert into Player_information values('斯蒂芬库里',30,1.91,86,'PG',30,'金州勇士')

insert into Player_information values('凯文杜兰特',30,2.06,109,'SF',35,'金州勇士')

insert into Player_information values('克莱汤普森',28,2.03,98,'PG',11,'金州勇士')

insert into Player_information values('德雷蒙德格林',28,2.01,104,'PF',23,'金州勇士')

insert into Player_information values('德马库斯考辛斯',28,2.11,122,'C',0,'金州勇士')

insert into Player_information values('克里斯保罗',33,1.83,79,'PG',3,'休斯敦火箭')

insert into Player_information values('詹姆斯哈登',29,1.96,100,'PG',13,'休斯敦火箭')

insert into Player_information values('克林特卡佩拉',24,2.11,109,'C',15,'休斯敦火箭')

insert into Player_information values('埃里克戈登',29,1.93,98,'PF',10,'休斯敦火箭')

insert into Player_information values('PJ塔克',33,1.98,111,'PF',17,'休斯敦火箭')

insert into Player_information values('戈登海沃德',28,2.03,102,'SF',20,'波士顿凯尔特人')

insert into Player_information values('凯里欧文',26,1.91,88,'PG',11,'波士顿凯尔特人')

insert into Player_information values('杰森塔图姆',20,2.03,93,'SF',0,'波士顿凯尔特人')

insert into Player_information values('艾尔霍福德',32,2.08,111,'C/PF',42,'波士顿凯尔特人')

insert into Player_information values('马库斯斯马特',24,1.93,100,'PG',36,'波士顿凯尔特人')

create table Player_data

(

pname char(20) primary key,

time float,

score int,

backboard int,

assist int,

steal int,

block_shot int,

match_time char(20) foreign key references Match_information(match_time)

)

insert into Player_data values('勒布朗詹姆斯',43,34,10,2,1,0,'2019.6.11_9:00')

insert into Player_data values('布兰登英格拉姆',35,20,5,6,2,1,'2019.6.11_9:00')

insert into Player_data values('朗佐鲍尔',36,25,6,4,6,0,'2019.6.11_9:00')

insert into Player_data values('凯尔库兹马',34,20,10,3,5,0,'2019.6.11_9:00')

insert into Player_data values('贾维尔麦基',30,9,10,10,3,8,'2019.6.11_9:00')

insert into Player_data values('斯蒂芬库里',39,28,5,7,0,0,'2019.4.28_8:00')

insert into Player_data values('凯文杜兰特',40,20,7,5,1,1,'2019.4.28_8:00')

insert into Player_data values('克莱汤普森',36,20,2,3,0,0,'2019.4.28_8:00')

insert into Player_data values('德雷蒙德格林',38,15,9,11,4,0,'2019.4.28_8:00')

insert into Player_data values('德马库斯考辛斯',35,30,10,5,11,6,'2019.4.28_8:00')

insert into Player_data values('克里斯保罗',40,28,10,4,2,0,'2019.4.28_8:00')

insert into Player_data values('詹姆斯哈登',39,30,8,3,4,1,'2019.4.28_8:00')

insert into Player_data values('克林特卡佩拉',30,9,2,3,4,5,'2019.4.28_8:00')

insert into Player_data values('埃里克戈登',32,16,3,5,6,7,'2019.4.28_8:00')

insert into Player_data values('PJ塔克',28,14,4,5,0,0,'2019.4.28_8:00')

insert into Player_data values('戈登海沃德',39,20,4,2,3,5,'2019.6.11_9:00')

insert into Player_data values('凯里欧文',40,30,10,3,4,0,'2019.6.11_9:00')

insert into Player_data values('杰森塔图姆',39,30,9,2,4,0,'2019.6.11_9:00')

insert into Player_data values('艾尔霍福德',30,10,3,4,5,9,'2019.6.11_9:00')

insert into Player_data values('马库斯斯马特',27,18,3,4,0,0,'2019.6.11_9:00')

create table yonghu(

account char(20) primary key,

phone char(20),

password char(20)

pri int

)

insert into yonghu values('1759240','17609396094','heyixuan',0)

insert into yonghu values('1759233','17521577265','xhy',1)

select * from yonghu

create table user_favor(

account char(20) primary key,

flag int,

pname char(20)

create procedure Player

@pname char(50)

as select Player_information.pname 姓名,age 年龄,height 身高,weighet 体重,weizhi 位置,number 号码,tname 所属运动队,上场时间,得分,篮板,助攻,抢断,盖帽

from Player_information join(select pname,avg(time) 上场时间,avg(score) 得分,avg(backboard) 篮板,avg(assist) 助攻,avg(steal) 抢断,avg(block_shot) 盖帽 from Player_data group by pname) A on Player_information.pname=A.pname

where Player_information.pname=@pname

exec Player'詹姆斯哈登'

create view 洛杉矶湖人 as

select team_information.tname,team_information.qiuguan_name,player_information.pname,player_information.age,player_information.height,player_information.weighet,player_information.weizhi,player_information.number

from team_information,player_information

where team_information.tname=player_information.tname and team_information.tname='洛杉矶湖人'

create view 金州勇士 as

from team_information,player_information

where team_information.tname=player_information.tname and team_information.tname='金州勇士'

create view 休斯敦火箭 as

from team_information,player_information

where team_information.tname=player_information.tname and team_information.tname='休斯敦火箭'

create view 波士顿凯尔特人 as

from team_information,player_information

where team_information.tname=player_information.tname and team_information.tname='波士顿凯尔特人'

select * from 金州勇士

select * from 洛杉矶湖人

select * from 波士顿凯尔特人

select * from 休斯敦火箭

delete Player_information where pname='朗佐鲍尔'

create trigger delete_player

on player_information

after delete

begin

declare @pname char(8)

select @pname=pname from deleted

delete player_data where pname=@pname

select @pname as'退役球员'

end

create table score1(

score1 float,

pname1 char(20)

)

declare Score cursor

for

select score,pname from Player_data

open Score

declare @score float

declare @pname char(20)

fetch next from Score into @score,@pname

while @@fetch_status=0

begin

fetch next from Score into @score,@pname

end

begin

insert into score1

values(@score,@pname)

fetch next from Score into @score,@pname

end

close Score

deallocate Score

select pname=pname1,score=max(score1)

from score1

group by pname1

create procedure time_perform(@weizhi char)

select Player_data.pname as ‘球员姓名’, time as ‘上场时间’ from Player_data

join Player_information

on Player_information.pname = Player_data.pname

where weizhi = @weizhi

order by time desc

exec time_perform 'C'

//存储过程

create proc StuProc(@pname char(20),@username char(20))

begin

if(@pname is not null and @username is not null)

begin

insert into user_favor(pname,account)

values(@pname,@username)

end

//对数据进行更新操作

create trigger trg_content_insert

on user_favor

instead of insert

declare @content varchar(20),@c int,@content1 char(20)

select @content=pname from inserted;

select @content1=account from inserted;

select @c=count(1) from user_favor where pname=@content and account=@content1;

if @c>0

update user_favor set flag=flag+1 where pname=@content and account=@content1;

else

begin

insert into user_favor select * from inserted;

update user_favor set flag=1;

end

//测试：

exec StuProc '凯文杜兰特','1759240'

create trigger tri_noedit_name

on Player_data

instead of update

declare @fla int

select @fla=(select pri from user_favor where account='1759240')

if update(score) and @fla=0

begin

print('你没有权限！')

rollback tran

end

//测试：

update Player_data

set score=100

where pname='凯文杜兰特'

功能：该数据库可以对比赛的信息，球员的信息，球队教练信息以及球员在比赛中的表现进行查询。有一个存储过程，可以显示特定球员的球员信息和球员平均数据。有一个触发器，删除球员信息的同时可以删除球员数据。有一个游标，当查询球员数据时显示MVP。有一个存储过程，可以查询特定位置上的球员数据排名。有一个触发器，可以识别用户等级，在更新数据的时候，若等级不够，则无法更改。有一个存储过程，可以记录用户喜好。

心得体会：该程序数据库的表结构科学，关系模式清晰，主键和外键使用正确，用SQL语句能完成一些基本的需求功能。功能明确，涵盖了用户所需要的功能，在此之上也有创新，增加的用户等级识别功能和记录用户喜好功能使程序更人性化，更符合用户的使用习惯。

遇到的问题：该数据库程序没有一个便于用户使用、易于操作的窗口程序。

解决办法：用JDBC把SQL SERVER与JAVA连接开发一个窗口程序。

转载于:https://www.cnblogs.com/consoler/p/10480009.html