【正文】 ), score int not null, /*分?jǐn)?shù) */ constraint EvaluatePK primary key clustered(tableNo,dishesNo), constraint EvaluateFK1 foreign key(tableNo) references Table1(tableNo), constraint EvaluateFK2 foreign key(dishesNo) references Dishes(dishesNo) ) go grant all on。 go create table Evaluate( tableNo char(4) not null , /*桌臺(tái)編號(hào) */ dishesNo char(7) not null /*菜品編號(hào) */ check(dishesNo like 39。), price numeric(7,2) not null, /*菜品價(jià)格 */ quantity int not null, /*數(shù)量 */ constraint MenuDetailPK primary key clustered(menuNo,dishesNo), constraint MenuDetailFK1 foreign key(menuNo) references Menu(menuNo), constraint MenuDetailFK2 foreign key(dishesNo) references Dishes(dishesNo) ) go grant all on Menudetail to user01 grant delete,insert on MenuDetail to customer grant select on MenuDetail to customer,qiantai go print 39。 go create table MenuDetail( menuNo char(12) not null, /*菜單編號(hào) */ dishesNo char(7) not null /*菜品編號(hào) */ check(dishesNo like 39。), price numeric(7,2) not null, /*菜品價(jià)格 */ constraint SmealDetailPK primary key clustered(smealNo,dishesNo), constraint SmealDetailFK1 foreign key(smealNo) references Smeal(smealNo), constraint SmealDetailFK2 foreign key(dishesNo ) references Dishes(dishesNo ) ) go grant all on SmealDetail to user01 go print 39。 go create table SmealDetail( smealNo char(4) not null , /*套餐編號(hào) */ dishesNo char(7) not null /*菜品編號(hào) */ 29 check(dishesNo like 39。 go create table Smeal( smealNo char(4) not null , /*套餐編號(hào) */ sprice numeric(7,2) not null, /*套餐價(jià)格 */ constraint SmealPK primary key (SmealNo) ) go grant all on Smeal to user01 go print 39。 go create table Table1( tableNo char(4) not null , /*桌臺(tái)編號(hào) */ place int not null, /*容納人數(shù) */ tableLevel char(10) not null, /*桌臺(tái)級(jí)別 */ constraint Table1PK primary key (tableNo) ) go grant all on Table1 to user01 go print 39。), tableNo char(4) not null, /*桌臺(tái)編號(hào) */ meandate datetime not null, /*時(shí)間 */ meanSum numeric(7,2) not null, /*菜單總價(jià) */ constraint MenuPK primary key (menuNo), constraint MenuFK1 foreign key(tableNo) references Table1(tableNo), constraint MenuFK2 foreign key(memberNo) references Member(memberNo) ) go grant all on Table1 to user01 go print 39。 go create table Menu( menuNo char(12) not null , /*菜單編號(hào) */ memberNo char(7) not null, /*顧客號(hào) */ check(memberNo like 39。), memberName varchar(40) not null, /*顧客姓名 */ sex char(1) not null, /*性別 */ memberLevel varchar(12) not null, /*顧客等級(jí) */ memberCount int not null, /*顧客積分 */ birthday datetime null, /*顧客生日 */ constraint MemberPK primary key (memberNo) ) go 28 grant all on Member to user01 go print 39。 go create table Member( memberNo char(7) not null, /*顧客號(hào) */ check(memberNo like 39。), dishesName varchar(10) not null, /*菜品編號(hào) */ price numeric(7,2) not null, /*菜品價(jià)格 */ caloric numeric(7,2) not null, /*熱量 */ sugers numeric(7,2) not null, /*糖分 */ protein numeric(7,2) not null, /*蛋白質(zhì) */ constraint DishesPK primary key (dishesNo) ) go grant all on Dishes to user01 go print 39。 go CREATE TABLE Dishes( dishesNo char(7) not null /*菜品編號(hào) */ check(dishesNo like 39。 go print39。,39。 exec sp_adduser 39。,39。,39。 exec sp_addlogin 39。,39。 exec sp_adduser 39。,39。,39。, 27 size=1, maxsize=5, filegrowth=1) go /*data mydatabase*/ use MenuDB1 go /* add my user*/ exec sp_adduser user01,user01 exec sp_addlogin 39。, filename=39。, size=1, maxsize=5, filegrowth=1) log on (name=39。, filename=39。) drop database MenuDB1 go create database MenuDB1 on primary (name=39。) exec sp_addlogin user01,user01 go /*create database*/ if exists(select *from sysdatabases where name=39。 set nocount on set dateformat ymd use master go if not exists(select * from syslogins where name=39。f:\myWork\39。MenuDB1Log39。f:\myWork\39。MenuDB139。OrderDB8139。user0139。 數(shù)據(jù)庫(kù)的存放路徑 在目前實(shí)驗(yàn)條件下，根據(jù)實(shí)驗(yàn)的實(shí)際需求，將各數(shù)據(jù)文件存儲(chǔ)在以下路徑中。 數(shù)據(jù)庫(kù)的存儲(chǔ)結(jié)構(gòu) 為了提高系統(tǒng)整體性能，將數(shù)據(jù)庫(kù)中的數(shù)據(jù)組織分為多個(gè)文件，分開進(jìn)行存儲(chǔ)。 2） 顧客表（ Member）中的積分（ memberCount）以及菜單表（ Menu）中的總價(jià)（ menuSum）經(jīng)常在連接中出現(xiàn)，所以建立 B+樹索引。 create procedure p_score as begin declare menusum numeric(7,2),memberno char(7),menuno char(12) declare cur_score cursor for select menusum,memberno from menu open cur_score fetch next from cur_score into menusum,memberno,menuno while(fetch_status=0) begin update member set membercount=membercount+convert(int,menusum) from member a,menu b where =memberno and =menuno fetch next from cur_score into menusum,memberno,menuno end close cur_score deallocate cur_score end 25 5 物理設(shè)計(jì) 關(guān)系模式存儲(chǔ)方式選擇 為提高數(shù)據(jù)庫(kù)數(shù)據(jù)的存取速度，而需要設(shè)置合適的存取方式，主要采用以下兩類存取方式。 create procedure p_tuijian as begin declare caloric numeric(7,2),protein numeric(7,2),dishesno char(7) declare cur_tuijian scroll cursor for select dishesno,caloric,protein from menudetail a,dishes b where = open cur_tuijian fetch next from cur_tuijian into dishesno,caloric,protein while(fetch_status=0) begin select dishesno,di