会员(会员帐号PK
賣方評比
拍賣編號(PK)(FK)
賣方帳號(PK)(FK)
時間(PK)
評比
意見 ER-D
買方評比 拍賣品 拍賣編號(PK)(FK) 拍賣編號(PK) 買方帳號(PK)(FK) 商品名稱 時間(PK) 直接購買價 會員 評比 起標價格 買方 意見 出價增額 會員帳號(PK) 商品數量 密碼 所在地區 姓名 開始時間 賣方 性別 結束時間 出價 地址 分類代號(FK) 電話 拍賣編號(PK)(FK) 會員帳號(FK) 買方帳號(PK)(FK) 商品新舊 出價時間(PK) 付款方式 出價金額 交貨方式 數量
意見
問與答 分類
分類代號(PK) 流水號(PK) 分類主題 拍賣編號(FK)
父親分類(FK) 買方帳號(FK)
買方問題
賣方回答
問題時間
回答時間
1
Schema
會員(會員帳號PK,密碼,姓名,性別,地址,電話,買評價,賣評價) 分類(分類代號PK,分類主題,父親分類FK)
拍賣品(拍賣編號PK,商品名稱,直接購買價,起標價格,出價增額,商品數量,所在地區,開始時間,結束時間,分類代號FK,會員帳號FK,商品新舊,付款方式,交貨方式,目前出價次數,得標者編號)
賣方評比(拍賣編號PK.FK,賣方帳號PK.FK,時間PK,評比,意見)
買方評比(拍賣編號PK.FK,買方帳號PK.FK,時間PK,評比,意見)
出價記錄(拍賣編號PK.FK,買方帳號PK.FK,出價時間PK,出價金額,數量,意見,狀態)
問與答(流水號PK,拍賣編號FK,買方帳號FK,買方問題,賣方回答,問題時間,回答時間)
2
◎建立資料表
<<建立會員>>
create table 會員(
會員帳號 char(10) primary key, 密碼 char(10),
姓名 char(10),
性別 char(1), --1男 2女
地址 varchar(30),
電話 char(13),
買評價 int default 0,
賣評價 int default 0
)
--輸入會員資料
Insert into 會員(會員帳號,密碼,姓名,性別) values('M001','1234','王仁甫',1) Insert into 會員(會員帳號,密碼,姓名,性別) values('M002','2345','孫協志',1) Insert into 會員(會員帳號,密碼,姓名,性別) values('M003','3456','梁靜茹',2) Insert into 會員(會員帳號,密碼,姓名,性別) values('M004','4567','張惠妹',2)
<<建立分類>>
Create Table 分類(
分類代號 int primary key ,
分類主題 char(10),
父親分類 int
)
--輸入分類資料
Insert into 分類(分類代號,分類主題) values(1,'電腦') Insert into 分類(分類代號,分類主題) values(2,'圖書') Insert into 分類(分類代號,分類主題) values(3,' 服飾 ') Insert into 分類(分類代號,分類主題) values(4,'汽車') Insert into 分類(分類代號,分類主題,父親分類) values(5,'桌上型',1)
3
Insert into 分類(分類代號,分類主題,父親分類) values(6,'女裝',3) Insert into 分類(分類代號,分類主題) values(7,'其他')
<<建立拍賣品>>
create table 拍賣品(
拍賣編號 char(10) primary key, 商品名稱 char(10),
直接購買價 money default 9999, 起標價格 money default 1,
出價增額 money default 10,
商品數量 int default 1,
所在地區 char(10),
開始時間 datetime default getdate(), 結束時間 datetime default getdate()+10, 分類代號 int default 7,
會員帳號 char(10),
商品新舊 char(1) default 1, --1新 2舊
付款方式 char(1) default 'A',--A轉帳 B現金
交貨方式 char(1) default 'A',--A寄送 B面交
目前出價次數 int default 0,
得標者編號 char(10) null,
)
--輸入拍賣品資料
Insert into 拍賣品(拍賣編號,商品名稱,所在地區,會員帳號,分類代號,得標者編號) values('S001','電腦','台北市','M001',1,'M002') Insert into 拍賣品(拍賣編號,商品名稱,所在地區,會員帳號,分類代號) values('S002','隨身聽','台北市','M001',1)
Insert into 拍賣品(拍賣編號,商品名稱,所在地區,會員帳號,分類代號,得標者編號) values('S003','法拉利','高雄市','M003',4,'M001') Insert into 拍賣品(拍賣編號,商品名稱,所在地區,會員帳號,分類代號)
4
values('S004','電扇','新竹市','M003',7)
Insert into 拍賣品(拍賣編號,商品名稱,所在地區,會員帳號,分類代號) values('S005','娃娃','花蓮縣','M004',3)
Insert into 拍賣品(拍賣編號,商品名稱,所在地區,會員帳號,分類代號) values('S006','牛仔褲','台北市','M004',3)
Insert into 拍賣品(拍賣編號,商品名稱,所在地區,會員帳號,分類代號,得標者編號) values('S007','背包','新竹市','M004',3,'M001')
<<建立賣方評比>>
create table 賣方評比(
拍賣編號 char(10),
賣方帳號 char(10),
時間 datetime default getdate(), 評比 int default 0, --1差2普通3良好
意見 char(100),
constraint pk_salecon primary key(拍賣編號,賣方帳號,時間) )
--輸入賣方評比
Insert into 賣方評比(拍賣編號,賣方帳號,評比) values('s001','m001',3)
Insert into 賣方評比(拍賣編號,賣方帳號,評比) values('s003','m003',3)
5
<<建立買方評比>>
create table 買方評比(
拍賣編號 char(10),
買方帳號 char(10),
時間 datetime default getdate(), 評比 int default 0,
意見 char(100),
constraint pk_buycon primary key(拍賣編號,買方帳號,時間) )
--輸入買方評比
Insert into 買方評比(拍賣編號,買方帳號,評比) values('s001','m002',3)
Insert into 買方評比(拍賣編號,買方帳號,評比) values('S003','M001',3)
<<建立出價記錄>>
create table 出價記錄(
拍賣編號 char(10),
買方帳號 char(10),
出價時間 datetime default getdate(), 出價金額 money,
數量 int default 1,
意見 char(100),
狀態 char(1) default 'N',--N未得標 S已得標
constraint pk_gore primary key(出價時間,買方帳號,拍賣編號) )
--輸入出價記錄資料
Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額) values('S002','M003','2004/12/1',100) Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額) values('S002','M002','2004/12/15',200) Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額) values('S003','M001','2004/12/1',100) Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額)
6
values('S003','M002','2004/12/3',120) Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額)
values('S003','M001','2004/12/5',150) Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額)
values('S003','M004','2004/12/6',1000) Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額)
values('S003','M001','2004/12/7',5000) Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額)
values('S003','M004','2004/12/6',15000) Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額)
values('S004','M001','2004/12/6',200) Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額)
values('S004','M002','2004/12/7',300) Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額,狀態)
values('S001','M002','2004/12/19',100,'S') Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額,狀態)
values('S003','M001','2004/12/13',22000,'S') Insert into 出價記錄(拍賣編號,買方帳號,出價時間,出價金額,狀態)
values('S007','M001','2004/12/15',200,'S')
<<建立問與答>>
Create Table 問與答(
流水號 int primary key identity(1,1), 拍賣編號 char(10),
買方帳號 char(10),
買方問題 char(100),
7
賣方回答 char(100),
問題時間 datetime default getdate(), 回答時間 datetime
)
--輸入問與答資料
insert into 問與答 values ('S003','M001','可以便宜一點嗎??','很抱歉,沒辦法唷!!^^','2004/11/29','2004/11/30')
◎建立View
1.列出各個商品之出價記錄,vw_出價記錄(拍賣編號,商品名稱,直接購買價,起標價格,出價金額,狀態,出價者編號)
create view vw_出價記錄
as
select a.拍賣編號,
商品名稱,
直接購買價,
起標價格,
a.出價金額,
狀態=case a.狀態
when 'S' then '得標'
when 'N' then '未得'
end,
a.買方帳號 as 出價者編號
from 出價記錄 a join 拍賣品 b on a.拍賣編號=b.拍賣編號
8
2.列出各個商品出價最高價,vw_商品出價最高價(拍賣編號,商品名稱,出價次數,目前最高價)
create view vw_商品出價最高價
as
select 拍賣編號,商品名稱,count(拍賣編號) as 出價次數,max(出價金額) as 目前最高價
from vw_出價記錄
group by 拍賣編號,商品名稱
3.求出各個商品之最高出價者是誰,vw_商品最高出價者(拍賣編號,商品名稱,最高價,出價者編號)
Create View vw_商品最高出價者
as
select 拍賣編號,商品名稱,出價金額 as 最高價,出價者編號
from vw_出價記錄 x
where 出價金額=( select max(出價金額)
from vw_出價記錄
where x.拍賣編號=拍賣編號 )
4.那此"分類"拍賣品(以出價次數)和性別之比較,vw_分類性別百分比(分類主題,男,女)
Create View vw_分類性別百分比
As
Select 分類主題,
男 = 100 * (Select count(*)
From 出價記錄
9
Where 拍賣編號 in (Select 拍賣編號 From 拍賣品 Where 分類代號=R1.分類代號)
and 買方帳號 in (Select 會員帳號 From 會員 Where 性別 = 1))/
(Select count(*) From 出價記錄 Where 拍賣編號 in (Select 拍賣編號 From 拍賣品 Where 分類代號=R1.分類代號)),
女 = 100 * (Select count(*)
From 出價記錄
Where 拍賣編號 in (Select 拍賣編號 From 拍賣品 Where 分類代號=R1.分類代號)
and 買方帳號 in (Select 會員帳號 From 會員 Where 性別 = 2))/ (Select count(*) From 出價記錄 Where 拍賣編號 in (Select 拍賣編號 From 拍賣品 Where 分類代號=R1.分類代號))
From 分類 R1
Where (Select count(*) From 出價記錄 Where 拍賣編號 in (Select 拍賣編號 From 拍賣品 Where 分類代號=R1.分類代號)) <> 0
Union
Select 分類主題,男='0',女='0'
From 分類 R1
Where (Select count(*) From 出價記錄 Where 拍賣編號 in (Select 拍賣編號 From 拍賣品 Where 分類代號=R1.分類代號)) = 0
5. 所有的分類與他的父分類的顯示,vw_分類一覽表(分類代號,分類主題,父親分類主題)
Create View vw_分類一覽表
As
Select 分類代號,
分類主題,
父親分類主題 = (Select 分類主題 From 分類 Where 分類代號=R1.父親分類)
From 分類 R1
10
6.找出購買最多商品的會員,前10名,vw_購買最多商品的會員 (會員帳號,得標數量)
Create View vw_購買最多商品的會員
As
Select Top 10
會員帳號 = 買方帳號,
得標數量 = Count(*)
From 出價記錄
Where 狀態 = 'S'
Group By 買方帳號
7.查出評比,vw_會員評比(會員帳號,評比次數,被評比次數,分數) Create View vw_會員評比
As
Select 會員帳號,
評比次數 = isnull((Select count(*) From 賣方評比 Where 賣方帳號 = R1.
會員帳號),0) + isnull((Select count(*) From 買方評比 Where 買方帳號 =
R1.會員帳號),0),
被評比次數= isnull((Select count(*) From 賣方評比 R2 Join 拍賣品 R3
On R2.拍賣編號 = R3.拍賣編號Where R3.會員帳號 = R1.會員帳號),0) +
isnull((Select count(*) From 買方評比 R2 Join 拍賣品 R3 On R2.拍賣編號
= R3.拍賣編號Where R3.會員帳號 = R1.會員帳號),0),
分數 = isnull((Select sum(評比) From 賣方評比 R2 Join 拍賣品 R3 On R2.
拍賣編號 = R3.拍賣編號Where R3.會員帳號 = R1.會員帳號),0) +
isnull((Select sum(評比) From 買方評比 R2 Join 拍賣品 R3 On R2.拍賣編
號 = R3.拍賣編號Where R3.會員帳號 = R1.會員帳號),0) From 會員 R1
11
8.商品的出價數量,vw_商品出價數量(拍賣編號,商品名稱,出價數量) Create View vw_商品出價數量
As
Select 拍賣編號,
商品名稱,
出價數量 = isnull((Select count(*) From 出價記錄 where 拍賣編號 = R1.拍賣編號),0)
From 拍賣品 R1
9.每月熱門出價商品,看出價數量,vw_每月熱門出價商品(年,月,拍賣編號,商品名稱,出價數量)
Create View vw_每月熱門出價商品
As
Select 年 = datepart(year,R1.出價時間),
月 = datepart(month,R1.出價時間),
R1.拍賣編號,
R2.商品名稱,
出價數量 = count(*)
From 出價記錄 R1 Join 拍賣品 R2
On R1.拍賣編號=R2.拍賣編號
Group By datepart(year,R1.出價時間),datepart(month,R1.出價時間),R1.拍賣編號,R2.商品名稱
12
10. 得標後買家未評比次數,vw_得標後買家未評比次數(會員帳號,未評比次數)
Create View vw_得標後買家未評比次數
As
Select 會員帳號 = R1.買方帳號,
未評比次數 = count(*) From 出價記錄 R1 Left Join 買方評比 R2
On R1.買方帳號 = R2.買方帳號 And R1.拍賣編號 = R2.拍賣編號 Where R1.狀態 = 'S' and R2.評比 is Null
Group By R1.買方帳號
◎建立Trigger
1.建立一個Trigger,當會員資料表裡的會員資料刪除,則出價記錄裡的買方出價資料也會被刪除
Create Trigger trDelVIPdata on 會員 for delete as
declare @id varchar(10) select @id=會員帳號 from deleted
delete 出價記錄 where 買方帳號=@id
刪除前:
13
刪除後:
2.建立一個Trigger,當刪除一筆[出價記錄]時,會至[拍賣品]中的<目前出價次數>-1
Create Trigger trDelBidCount on 出價記錄 for delete as
declare @oid varchar(10)
select @oid=拍賣編號 from deleted
update 拍賣品 set 目前出價次數 = 目前出價次數 - 1 where 拍賣編號=@oid
刪除前:
14
刪除後:
15
3.建立一個Trigger,當新增一筆[出價記錄]時,會至[拍賣品]中的<目前出價次數>+1
Create Trigger trInsBidCount on 出價記錄 for insert as
declare @oid varchar(10)
select @oid=拍賣編號 from inserted
update 拍賣品 set 目前出價次數 = 目前出價次數 + 1 where 拍賣編號=@oid
新增前:
新增後:
16
4.建立一個Trigger,當會員資料表裡的會員資料刪除,則問與答裡的買方問題
資料也會被刪除
Create Trigger trDelQandA on 會員 for delete as
declare @id varchar(10) select @id=會員帳號 from deleted delete 問與答 where 買方帳號=@id
刪除前:
17
刪除後:
5.建立一個Trigger,當刪除父親類別ID時,其父親類別所屬的子類別也會跟
著被刪除
Create Trigger trDelkind on 分類 for delete as
declare @kid int
select @kid=分類代號 from deleted delete 分類 where 父親分類=@kid
刪除前:
刪除後:
18
◎建立Stored Procedure
1.輸入拍賣編號,將狀態設為已結標,拍賣品的得標者編號改為最高出價者編號
Create proc sp_SetState @sno char(10)
as
Update 出價記錄
Set 狀態='S'
Where 拍賣編號=@sno And 出價金額 = (Select Max( 出價金額) From 出價記錄 Where 拍賣編號=@sno )
Update 拍賣品
Where 狀 Set 得標者編號 = (Select 買方帳號 From 出價記錄態='S' And 拍賣編號=@sno)
Where 拍賣編號=@sno
輸入前:
輸入後:
sp_SetState ‘S002’
19
2.自動將所有拍賣品符合結標時間的商品結標
Create proc sp_AutoState
As
Update 出價記錄
Set 狀態='S'
From 出價記錄 R1
Where 拍賣編號 in (Select 拍賣編號
From 拍賣品
Where 得標者編號 is Null And 結束時間 <= getdate())
And 出價金額 = (Select Max( 出價金額)
From 出價記錄
Where 拍賣編號 = R1.拍賣編號 )
Update 拍賣品
Set 得標者編號 = (Select 買方帳號 From 出價記錄 Where 狀態='S' and 拍賣編號=R1.拍賣編號)
From 拍賣品 R1
Where 拍賣編號 in (Select 拍賣編號 From 拍賣品 Where 得標者編號 is Null And 結束時間 <= getdate())
執行前:
20
執行後:
sp_AutoState
21
3.查詢會員資料
Create proc sp_QueryNumber @mid char(10) As
Select *
From 會員
Where 會員帳號 = @mid
測試:
sp_QueryNumber ‘M001’
4.驗正會員帳號密碼 (正確回傳1 否則回傳0)
Create proc sp_CheckPassword @mid char(10) , @pwd char(10)
As
Select 會員帳號 From 會員 Where 會員帳號 = @mid and 密碼 = @pwd If @@rowcount > 0
return 1
else
return 0
測試:
declare @t int
exec @t = sp_CheckPassword 'M001','1234' select @t
5.查詢會員有那些拍賣品
Create proc sp_NumberHasProduct @mid char(10)
As
Select 拍賣編號,商品名稱,會員帳號,R1.分類代號,R2.分類主題,開始時間,結束時間,
isnull(得標者編號,'尚無得標者')
From 拍賣品 R1 Join 分類 R2
On R1.分類代號 = R2.分類代號
Where 會員帳號=@mid
22
測試:
sp_NumberHasProduct 'M001'
23