中南大学数据库原理与技术实验任务书

数据库原理与技术实验任务书一、实验报告要求1.列出所有的SQL语句和源代码；2.程序要求有适当的注释；3.对数据完整性约束实施要求给出相应的测试用例。

4.实验报告提交电子档。

二、实验内容实验1：创建表和实施数据完整性1．运行给定的SQL Script，建立数据库GlobalToyz。

2．了解表的结构。

3．利用系统预定义的存储过程sp_helpdb查看数据库的相关信息，例如所有者、大小、创建日期等。

4．利用系统预定义的存储过程sp_helpconstraint查看所有表中出现的约束（包括Primary key, Foreign key, check constraint, default, unique）5．对表Toys实施下面数据完整性规则：（1）玩具的现有数量应在0到200之间；（2）玩具适宜的最低年龄缺省为1。

查询、更新数据库1.显示属于California和Illinoi州的顾客的名、姓和emailID。

2.显示定单号码、顾客ID，定单的总价值，并以定单的总价值的升序排列。

3.显示在orderDetail表中vMessage为空值的行。

4.显示玩具名字中有“Racer”字样的所有玩具的基本资料。

5.列出表PickofMonth中的所有记录，并显示中文列标题。

6.根据2000年的玩具销售总数，显示“Pick of the Month”玩具的前五名玩具的ID。

7.根据OrderDetail表，显示玩具总价值大于￥50的定单的号码和玩具总价值。

8.显示一份包含所有装运信息的报表，包括：Order Number, Shipment Date, Actual DeliveryDate, Days in Transit. (提示：Days in Transit = Actual Delivery Date – Shipment Date)9.显示所有玩具的名称、商标和种类（Toy Name, Brand, Category）。

10.以下列格式显示所有购物者的名字和他们的简称：（Initials, vFirstName, vLastName）,例如Angela Smith的Initials为A.S。

11.显示所有玩具的平均价格，并舍入到整数。

12.显示所有购买者和收货人的名、姓、地址和所在城市，要求显示结果中的重复记录。

13.显示没有包装的所有玩具的名称。

（要求用子查询实现）14.显示已收货定单的定单号码以及下定单的时间。

（要求用子查询实现）15.显示一份基于Orderdetail的报表，包括cOrderNo,cToyId和mToyCost，记录以cOrderNo升序排列，并计算每一笔定单的玩具总价值。

（提示：使用运算符COMPUTE BY）。

16.把价格在$20以上的所有玩具的信息拷贝到称为PremiumToys的新表中。

17.给id为‘000001’玩具的价格增加$1。

18.删除“Largo”牌的所有玩具。

实验2：存储过程与触发器1.编写一段程序，将每种玩具的价格提高￥0.5，直到玩具的平均价格接近$24.5为止。

此外，任何玩具的最大价格不应超过$53。

2.创建一个称为prcCharges的存储过程，它返回某个定单号的装运费用和包装费用。

3.创建一个称为prcHandlingCharges的过程，它接收定单号并显示经营费用。

PrchandlingCharges过程应使用prcCharges过程来得到装运费和礼品包装费。

提示：经营费用=装运费+礼品包装费4.在OrderDetail上定义一个触发器，如果购物者改变了定单的数量，玩具的成本也自动地改变。

（提示：Toy cost = Quantity * Toy Rate）实验3：视图、事务与游标1.定义一个视图，包括购买者的姓名、所在州和他们所订购玩具的名称、价格和数量。

2.基于（1）中定义的视图，查询显示所有California州的购买者的姓名和他们所订购玩具的名称及数量。

3.名为prcGenOrder的存储过程产生存在于数据库中的定单号：CREATE PROCEDURE prcGenOrder@OrderNo char(6) OUTPUTasSELECT @OrderNo=Max(cOrderNo) FROM OrdersSELECT @OrderNo=CASEWHEN @OrderNo>=0 and @OrderNo<9 Then‘00000’+Convert(char,@OrderNo+1)WHEN @OrderNo>=9 and @OrderNo<99 Then‘0000’+Convert(char,@OrderNo+1)WHEN @OrderNo>=99 and @OrderNo<999 Then‘000’+Convert(char,@OrderNo+1)WHEN @OrderNo>=999 and @OrderNo<9999 Then‘00’+Convert(char,@OrderNo+1)WHEN @OrderNo>=9999 and @OrderNo<99999 Then‘0’+Convert(char,@OrderNo+1)WHEN @OrderNo>=99999 Then Convert(char,@OrderNo+1)ENDRETURN当购物者确认定单时，应该出现下面的步骤：（1）用上面的过程产生定单号。

（2）定单号，当前日期，购物车ID，和购物者ID应该加到Orders表中。

（3）定单号，玩具ID，和数量应加到OrderDetail表中。

（4）在OrderDetail表中更新玩具成本。

（提示：Toy cost = Quantity * Toy Rate）.将上述步骤定义为一个事务。

编写一个过程以购物车ID和购物者ID为参数，实现这个事务。

4.编写一个程序显示每天的定单状态。

如果当天的定单值总合大于170，则显示“High sales”,否则显示”Low sales”.报告中要求列出日期、定单状态和定单总价值。

实验4：数据库设计1、设计一个图书馆数据库，此数据库中对每个借阅者保存记录，包括：读者号、姓名、地址、性别、年龄、单位。

对每本书保存有：书号、书名、作者、出版社。

对每本被借出的书保存有读者号、借出日期和应还日期。

1）、利用一种数据库设计工具（例如Powerdesigner,Erwin）画出ER图；2）、利用该设计工具生成相应的关系模型，并连接到SQL Server上，自动生成数据库；3）、利用SQL语句向数据库中增加5条读者记录，10条书籍记录以及50条借阅记录。

2、利用数据库设计工具的逆向工程功能，将GlobalToyz数据库的ER模型还原出来。

三、数据库表关系图四、建库脚本/* Install GlobalToyz database */ setnocount on USE masterdeclare @dttmvarchar(55)select @dttm=convert(varchar,getdate(),113)CategorycCategoryId cCategory vDescriptionCountrycCountryId cCountryOrderDetailcOrderNo cToyId siQty cGiftWrap cWrapperId vMessage m OrderscOrderNo dOrderDate cCartId cShopperId cShippingModeId m m cOrderProcessed m dExpDelDatePickOfMonthcToyId siMonth iYear iTotalSoldRecipientcOrderNo vFirstNam vLastNam vAddress cCity cState cCountryId cZipCode cPhoneShipmentcOrderNo dShipm cDeliveryStatus dActualDeliveryDateShippingModecModeId cMode iMaxDelDaysShippingRatecCountryID cModeIdm ShoppercShopperId cPassword vFirstNam vLastNam vEm vAddresscCity cState cCountryId cZipCode cPhonecCreditCardNo vCreditCardType dExpiryDateShoppingCartcCartId cToyId siQtyToyBrandcBrandId cBrandNam ToyscToyId vToyNam vToyDescription cCategoryId m cBrandId im siToyQoh siLowerAge siUpperAge siToyWeight vToyIm WrappercWrapperId vDescription m im vWrapperImraiserror('Starting installation of GlobalToyz Database at %s ....',1,1,@dttm) with nowait GOif exists (select * from sysdatabases where name='GlobalToyz')beginraiserror('Dropping existing GlobalToyz database ....',0,1)DROP database GlobalToyzendGOCHECKPOINTgoraiserror('Creating GlobalToyz database....',0,1)goCREATE DATABASE GlobalToyzGOCHECKPOINTGOUSE GlobalToyzGOifdb_name() <> 'GlobalToyz'raiserror('Error in installToy.SQL, ''USE GlobalToyz'' failed! Killing the SPID now.',22,127) with logGOexecutesp_dboption 'GlobalToyz','trunc. log on chkpt.' ,'true'executesp_dboption 'GlobalToyz','SELECT INTO/BULKCOPY', 'true'checkpoint五、建表脚本USE GlobalToyzgo/* creating required data types */executesp_addtype id ,'char(6)' ,'NOT NULL'raiserror('Now at the Create Table section ....',0,1)Goraiserror('Creating Table Category....',0,1)create table Category(cCategoryId char(3) constraint ct_pk primary key,cCategory char(20) not null,vDescription varchar(100))goraiserror('Creating Table Wrapper....',0,1)create Table Wrapper(cWrapperId char(3) constraint w_id primary key clustered, vDescription varchar(20),mWrapperRate money not null,imPhoto image null,vWrapperImgPathvarchar(50) null)goraiserror('Creating Table ToyBrand....',0,1)create table ToyBrand(cBrandId char(3) constraint TB_pk primary key,cBrandName char(20) not null,)go/***************************/raiserror('Creating Table Country....',0,1)create table Country(cCountryId char(3) constraint c_pk primary key,cCountry char(25) not null,)goraiserror('Creating Table ShippingMode....',0,1)create table ShippingMode(cModeId char(2) constraint spm_pk primary key,cMode char(25) not null,iMaxDelDays int,)goraiserror('Creating Table ShippingRate....',0,1)create table ShippingRate(cCountryID char(3) references Country(cCountryId) ,cModeId char(2) references ShippingMode(cModeId),mRatePerPound money not null,constraint SR_PRK primary key(cCountryID,cModeId)/* need to create composite primary key */)raiserror('Creating Table Shopper....',0,1)create table Shopper(cShopperId char(6) constraint s_id primary key CLUSTERED,cPassword char(10) not null,vFirstName varchar(20) not null,vLastName varchar(20) not null,vEmailId varchar(40) not null,vAddress varchar(40) not null,cCity char(15) not null,cState char(15) not null,cCountryId char(3) references Country(cCountryId),cZipCode char(10), /* check(cZipCode like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'), */cPhone char(15) not null ,cCreditCardNo c har(16) not null,vCreditCardType varchar(15) not null,dExpiryDate datetime)goraiserror('Creating Table Toys....',0,1)/* toys table */create table Toys(cToyId char(6) check(cToyId like('[0-9][0-9][0-9][0-9][0-9][0-9]') )constraintt_id primary key clustered,vToyName varchar(20) not null,vToyDescription varchar(250),cCategoryId char(3) references Category(cCategoryId) ,mToyRate money not null,cBrandId char(3)references ToyBrand(cBrandId),imPhoto image,siToyQoh smallint not null,siLowerAge smallint not null,siUpperAge smallint not null,siToyWeight smallint,vToyImgPath varchar(50) null)go/* *//* ShoppingCart */raiserror('Creating Table ShoppingCart....',0,1)create table ShoppingCart(cCartId char(6) not null,cToyId char(6)REFERENCES Toys(cToyId),/* foreign key to Toys table */ siQty smallint not null,constraint SCHP_PK primary key(cCartId,cToyId))go/***********************//* Order */raiserror('Creating Table Order....',0,1)create table Orders(cOrderNo c har(6) constraint CO_PK Primary key,dOrderDate datetime not null,cCartId char(6) not null,cShopperId char(6) not null references Shopper(cShopperId),cShippingModeId char(2) null references ShippingMode(cModeId),mShippingCharges money null,mGiftWrapCharges money null,cOrderProcessed char null ,mTotalCost money null,dExpDelDate DateTime null)go/* OrderDetail table */raiserror('Creating Table OrderDetails....',0,1)create table OrderDetail(cOrderNo c har(6) references Orders(cOrderNo),cToyId char(6) references toys(cToyId),siQty smallint not null,cGiftWrap char null,cWrapperId char(3) references Wrapper(cWrapperId) null,vMessage varchar(256) null,mToyCost money null,constraintz_key primary key(cOrderNo,cToyId))go/* shipping mode *//* Shipment */raiserror('Creating Shippment....',0,1)create table Shipment(cOrderNo char(6) REFERENCES Orders(cOrderNo) constraint SHP_PK primary key, dShipmentDate datetime null, /* cannot be before order date */cDeliveryStatus char null,dActualDeliveryDatedatetime null)go/* Recipient table */raiserror('Creating Table Recipient....',0,1)create table Recipient(cOrderNo char(6) REFERENCES Orders(cOrderNo) constraint RCP_PK primary key, /* foreign key to order table */vFirstName varchar(20) not null,vLastName varchar(20) not null,vAddress varchar(20) not null,cCity char(15) not null,cState char(15) not null,cCountryId char(3) references Country(cCountryId),cZipCode char(10) check(cZipCode like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),cPhone char(15))go/* pick of the month */raiserror('Creating Table PickOfMonth....',0,1)create table PickOfMonth(cToyId char(6)REFERENCES Toys(cToyId), /* foreign key to toy table */siMonth smallint,iYear int,iTotalSold i nt,constraint POM_PK primary key(cToyId,siMonth,iYear))go/*****************************************************/-- Populating data into the tables/********************/insert into Category values('001','Activity','Activity toys encourage the childs social skills and interest in the world around them.')insert into Category values('002','Dolls','A wide range of dolls from all the leading brands.') insert into Category values('003','Arts And Crafts','Encourage children to create masterpieces with these incredible craft kits.')insert into Category values('004','Games','A complete range of new and classic games.') insert into Category values('005','Cars N Racing','Models of all current and vintage cars.') insert into Category values('006','Pretend Play','These games can play an important part in the childs development.') /* Children can use these tools they need to play out their dreams.') */insert into Category values('007','Model Kits','Models to build planes, cars, ships and much more.')insert into Category values('008','Infant','Colorful, interactive toys for babies.')insert into Category values('009','Stuffed Toys','Teddy bears, monkeys and many more soft toys.')insert into Category values('010','Learning','Designed to make learning so much fun that children will forget they are learning at the same time')insert into Category values('011','Science and Nature','Toys which encourage children to explore the world around them.')insert into Category values('012','Musical toys','These toys generate music, sound and songs too.')insert into Category values('013','Electronic','These are electronic toys, which run using batteries.')insert into Category values('014','Puppets','These are puppet toys.')insert into Category values('015','Trains','These trains will be the pride of any railway system.')insert into Category values('016','Construction Toys','Bricks, building and other toys the will encourage children to build model houses and buildings.')/* data for wrapper */insert into Wrapper values('001','Geckos',1,null,null)insert into Wrapper values('002','Baby blocks',1.25,null,null) insert into Wrapper values('003','Stars',1.50,null,null)insert into Wrapper values('004','Bubbles',2,null,null)insert into Wrapper values('005','Sesame street',1.5,null,null) insert into Wrapper values('006','Moon',2.25,null,null) insert into Wrapper values('007','Sea',1,null,null)insert into Wrapper values('008','Sky',1,null,null)/* toy brand */INSERT INTO ToyBrandvalues('001','Bobby ')INSERT INTO ToyBrandvalues('002','Frances-Price') INSERT INTO ToyBrandvalues('003','The Bernie Kids') INSERT INTO ToyBrandvalues('004','Largo')INSERT INTO ToyBrandvalues('005','LAMOBIL')INSERT INTO ToyBrandvalues('006','Crazy World')INSERT INTO ToyBrandvalues('007','Brevet')INSERT INTO ToyBrandvalues('008','Darden')/* data for country */insert into Country values('001','United states of America') insert into Country values('002','Albania')insert into Country values('003','Andorra')insert into Country values('004','Argentina')insert into Country values('005','Austria')insert into Country values('006','Bangladesh')insert into Country values('007','Belgium')insert into Country values('008','Bosnia & Herzegovina') insert into Country values('009','Bulgaria')insert into Country values('010','Czech Republic')insert into Country values('011','Denmark')insert into Country values('012','Yugoslavia ')insert into Country values('013','France')insert into Country values('014','Gibraltar')insert into Country values('015','Greece')insert into Country values('016','Georgia')insert into Country values('017','Croatia')insert into Country values('018','Estonia')insert into Country values('019','Iceland')insert into Country values('020','Italy')insert into Country values('021','Israel')insert into Country values('022','India')insert into Country values('023','Cyprus')insert into Country values('024','Russia')insert into Country values('025','Liechtenstein')insert into Country values('028','Luxembourg')insert into Country values('030','Malta')insert into Country values('031','Moldova')insert into Country values('032','Monaco')insert into Country values('033','Netherlands')insert into Country values('034','Norway')insert into Country values('035','Poland')insert into Country values('036','Portugal')insert into Country values('037','Romania')insert into Country values('038','San Marino')insert into Country values('039','Slovak Republic')insert into Country values('040','Slovenia')insert into Country values('041','Finland')insert into Country values('042','Spain')insert into Country values('043','Switzerland')insert into Country values('044','Turkey')insert into Country values('045','Ukraine')insert into Country values('046','Hungary')insert into Country values('047','Germany')insert into Country values('048','Sweden')insert into Country values('049','Latvia')/* data into shippingmode */insert into ShippingMode values('01','Standard Shipping',4) insert into ShippingMode values('02','World Mail',3)insert into ShippingMode values('03','International Priority',1) /* shipping rate */insert into ShippingRate values('001','01',2)insert into ShippingRate values('001','02',4)insert into ShippingRate values('001','03',10)insert into ShippingRate values('002','01',03)insert into ShippingRate values('002','02',06)insert into ShippingRate values('002','03',10)insert into ShippingRate values('003','01',04)insert into ShippingRate values('003','02',06)insert into ShippingRate values('003','03',12)insert into ShippingRate values('004','01',04)insert into ShippingRate values('004','02',08)insert into ShippingRate values('004','03',12)insert into ShippingRate values('005','01',04)insert into ShippingRate values('005','02',08)insert into ShippingRate values('005','03',12)insert into ShippingRate values('006','01',04)insert into ShippingRate values('006','02',08)insert into ShippingRate values('007','01',04)insert into ShippingRate values('007','02',06)insert into ShippingRate values('007','03',10)insert into ShippingRate values('008','01',04)insert into ShippingRate values('008','02',05)insert into ShippingRate values('008','03',08)insert into ShippingRate values('009','01',10)insert into ShippingRate values('009','02',12)insert into ShippingRate values('009','03',15)insert into ShippingRate values('010','01',10)insert into ShippingRate values('010','02',12)insert into ShippingRate values('010','03',15)/*data into shopper */insert into shopper values('000001','angels','Angela','Smith','angelas@','16223 RadianceCourt','Woodbridge','Virginia','001','22191','227-2344','6947343412896785','MasterCard','08/09/2001')insert into shopper values('000002','guide','Barbara','Johnson','barbaraj@','227 Beach Ave.','Sunnyvale','California','001','94087-1147','123-5673','5345146765854356','Master Card','04/10/2001')insert into shopper values('000003','youbet','Betty','Williams','bettyw@dpeedmil.cm','1 Tread Road','Virginia Beach','Varginia','001','23455','458-3299','4747343412896785','Visa Card','12/12/2001')insert into shopper values('000004','credit','Carol','Jones','carolj@','765 - Furling Road Apt 112 ','Boone','North Carolina','001','28607','678-4544','6344676854335436','Visa Card','10/12/2001')insert into shopper values('000005','prepare','Catherine','Roberts','catheriner@','5508 Aquiline Court','San Jose','California','001','95123','445-2256','3756784562869963','Master Card','09/10/2001')insert into shopper values('000006','Char','Charles','Brown','charlesb@','7822 S. Glitzy Avenue ','Maitland','Florida','001','32751','225-6678','3454678545443344','Visa Card','10/09/2001') insert into shopper values('000007','chris','Christopher','Davis','Christopherd@','4896 11th ST ','Hill Avenue','Utah','001','84056-5410','556-9087','7899887675443322','Visa Card','10/02/2001') insert into shopper values('000008','mills','Cynthia','Miller','cynthiam@qmailcom','98066 Weary Storm Street','Moon Park ','California','001','93021-2930','422-5688','2345566576879900','Master Card','08/09/2001') insert into shopper values('000009','gotcha','Daniel','Wilson','danielw@','4642 Peripheral Drive','Brecksville','Ohio','001','44141','454-2246','2345467890986745','Visa Card','11/02/2001') insert into shopper values('000010','bingo','David','Moore','davidm@','8808 Joviality Drive ','San Ramon','California','001','94583','982-5577','2343556678799674','Visa Card','11/12/2001') insert into shopper values('000011','chubby','Deborah','Taylor','deboraht@','2199- Fairfax Drive ','Libertyville','Illinois','001','60048','889-2235','2345468798078563','MasterCard','11/23/2001')insert into shopper values('000012','smile','Donna','Anderson','Donnaa@ ','7930 Orange St. ','Las Vegas','Nevada','001','89117','845-2323','2314345676568766','Visa Card','11/24/2001')insert into shopper values('000013','benhur','Dorothy','Thomas','dorthyt@','678 East 56th Street- #12','New York','New York','001','10009','696-2278','8765435456678754','Visa Card','12/03/2001')insert into shopper values('000014','benjy','Elizabeth','Jackson','elizabethj@','598 Apex Avenue #2','Saint Paul ','Minnesota','001','55102','545-9078','4576544354567542','Visa Card','10/06/2001')insert into shopper values('000015','sundance','Frances','Turner ','francest@','2562 Eastwood ','Denton','Texas','001','76205-5922','878-6670','5676879007565452','MasterCard','08/30/2001')insert into shopper values('000016','bopeep','Helen','White','helenw@','Fleet Street','Point Pleasant','Pennsylvania','001','18950 ','585-7796','4564564564564565','Visa Card','10/10/2001')insert into shopper values('000017','momma','James','Harris',' helenw@','3456 Mt. Regale Drive ','1509 Alexandria','Virginia','001','22303-2541','335-6678','4657567545344544','MasterCard','10/31/2001')insert into shopper values('000018','grumpy','Jennifer','Martin','jenniferm@','9812 76th Street ','Brooklyn','Maryland','001','21225','569-7789','9775445343233443','VisaCard','09/01/2001')insert into shopper values('000019','patch','Jessica','Thompson','jessicat@','565 Pebble St. ','Arlington','Texas','001','76014','445-6797','9766545343233455','MasterCard','09/19/2001')insert into shopper values('000020','sunshine','John','Garcia','johng@','2234 B King Henry Drive ','Harrisburg','Pennsylvania','001','17109','521-9095','8764554334534567','MasterCard','09/13/2001')insert into shopper values('000021','good','Joseph','Martinez','josephm@','995 Rind Street ','Loma Linda','California','001','92354','456-9032','5687567567435344','Visa Card','05/31/2001')insert into shopper values('000022','nestle','Joyce','Phillips','joycep@','535 Darwin Avenue ','Bridgeville','Pennsylvania','001','15017','789-6905','2345345345645656','MasterCard','09/15/2001')insert into shopper values('000023','hangon','Karen','Robinson','karenr@','2343-A Flowers Ferry Road ','Marietta','Georgia','001','30067','334-5568','4546575675687689','Visa Card','02/26/2000') insert into shopper values('000024','rumble','Kimberly','Clark','kimberlyc@','79 Holler Valley Road ','Little Rock','Arkansas','001','72204','645-9023','2345653757898999','Master Card','05/31/2000')insert into shopper values('000025','noddy','Laura','Rodriguez','laurar@','3242Limestone','WayMarietta','Georgia','001','30062','567-3345','2345345676786543','Visa Card','03/03/2000')insert into shopper values('000026','falcon','Linda','Lewis','lindal@','1524 Patagonia Lane ','Plano','Texas','001','75075','459-4563','8765434523543366','Visa Card','03/14/2000')insert into shopper values('000027','lazer','Lisa','Lee','lisal@','18927 Sandstone Ave N ','Seattle','Washington','001','98133','897-3345','7653534745756567','MasterCard','08/30/2001')insert into shopper values('000028','spartan','Margaret','Walker','margaretw@','405 SE Aden #101 ','Pullman','Washington','001','99163','567-9083','9867865434534467','Master Card','07/09/2001')insert into shopper values('000029','zedi','Maria','Hall','Mariah@','936 Midland Drive ?','Rochester','New York','001','14609','345-8764','9786434564564567','Visa Card','04/06/2000')insert into shopper values('000030','norman','Mary','Allen','marya@','1202 Heathcliff Drive ','Urbana','Illinois','001','61801-5304','749-3096','8674564574574356','MasterCard','04/23/2000')insert into shopper values('000031','denice','Michael','Young','michaely@','440 Allens Trail' ,'Montgomery','Alabama','001','36117','560-9004','3478786786785677','Master Card','02/21/2000')insert into shopper values('000032','kate','Michelle','Hernandez','michelleh@','1353 Realm Lakes ','Naperville','Illinois','001','60563','294-5385','6965753564534554','Visa Card','01/02/2000') insert into shopper values('000033','pirate','Nancy','King','nancyk@','429 Ash Birch Lane ','North Andover','Massachusetts','001','01845','563-2298','3464276587468846','MasterCard','01/01/2000')。