數(shù)據(jù)庫課程設(shè)計--在線網(wǎng)上訂餐系統(tǒng)-在線瀏覽
2025-03-05 16:01本頁面
【正文】 archar(4), foreign key (m_p_mid) references manager (m_id), m_p_pid varchar(9), foreign key (m_p_pid) references provider (p_id), m_p_time smalldatetime, primary key (m_p_mid,m_p_pid,m_p_time), m_c_operation varchar(15) check (m_c_operation in(39。,39。) ))四、完整性、安全性等方面設(shè)計c_id varchar(9) primary key not nullm_id varchar(4) primary key not nullp_id varchar(9) primary key not null1）菜譜表參照關(guān)系菜譜中的商店標(biāo)號參照商店表的主碼：foreign key (d_pid) references provider(p_id)2）訂單表參照關(guān)系訂單中的客戶號參照客戶表的主碼：foreign key (o_cid) references client (c_id),訂單表中的菜譜號參照菜譜表的主碼：foreign key (o_did) references dish (d_id),訂單表中的商店號參照商店表的主碼：foreign key (o_pid) references provider (p_id),3）管理客戶表管理客戶表中的客戶號參照客戶表的主碼：foreign key (m_c_mid) references manager (m_id),管理客戶表中的管理員號參照管理員表的主碼：foreign key (m_c_cid) references client (c_id),4）管理商店表管理商店表的商店號參考商店表的主碼：foreign key (m_p_mid) references manager (m_id),管理商店表的管理員號參照管理員表的主碼：foreign key (m_p_pid) references provider (p_id),1）client表①c_id not null check(c_id like 39。),②c_name c_key not null,③c_tel not null check(c_tel like 39。) unique2）manager表①m_id not null check(m_id like 39。),②m_name，m_key not null,3）provider表①p_id not null check(p_id like 39。),②p_name，p_key， not null,③p_tel not null unique check(p_tel like 39。),④p_add not null unique,⑤p_state check (p_state in (39。,39。,39。))4）dish①d_id，d_name，d_price，d_pid，d_total not null,②d_remain int not null check (d_remain=0),5）ord①o_id，o_cadd not null,②o_num int not null check (o_num0),③o_state varchar(15) check (o_state in(39。,39。))6）manager_clientm_c_operation check (m_c_operation in(39。,39。,39。))7）manager_providerm_c_operation check (m_c_operation in(39。,39。) )1）自動更新時間：create trigger o_o_time on ordfor insertasbegindeclare oid intselect oid=o_id from insertedif exists(select*from ordwhere =oid and is not null)update ordset o_time=(select getdate() as today)end2）對d_remain的觸發(fā)器create trigger o_o_remain on ordfor insertasbegindeclare oid int select oid=o_id from insertedif exists(select*from ordwhere o_id=oid and o_id is not null)declare did intselect did=o_did from ord where o_id=oiddeclare onum intselect onum=o_num from ord where o_id=oidupdate dish set d_remain=d_remainonum where d_id=didend3）評價度增加create trigger o_o_lev on ordfor insertasbegindeclare oid int select oid=o_id from insertedif exists(select*from ordwhere o_id=oid and o_id is not null)declare pid varchar(9)select pid=o_pid from ord where o_id=oidupdate provider set p_lev=p_lev+1where p_id=pidupdate ord set o_state=39。where o_id=oidend、用戶、授權(quán)1）客戶（舉例）CREATE LOGIN c20112511 WITH PASSWORD = 39。create user buyijie for login c20112511。create role c。grant select on dishto c。grant select,insert on ordto c。exec sp_addrolemember 39。,39。2）商店（舉例）CREATE LOGIN p20131001 WITH PASSWORD = 39。create user 東北酒家 for login p20131001。create role p。grant update on dish(d_name,d_price,d_discount,d_total)to p。grant update on provider(p_name,p_tel,p_add,p_state)to p。p39。東北酒家39。1）消費(fèi)記錄:用戶名字，菜名，單價，打折，商店，時間，數(shù)量create view c_ord(c_name,d_name,d_price,d_discount,p_name,o_time,o_num)asselect c_name,d_name,d_price,d_discount,p_name,o_time,o_numfrom client,provider,dish,ordwh
環(huán)評公示相關(guān)推薦
鄂ICP備17016276號-1