中南大學(xué)數(shù)據(jù)庫(kù)實(shí)驗(yàn)報(bào)告

1、中 南 大 學(xué)數(shù)據(jù)庫(kù)實(shí)驗(yàn) 實(shí)驗(yàn)題目 班 級(jí) 姓 名學(xué) 號(hào) 一、實(shí)驗(yàn)內(nèi)容實(shí)驗(yàn)一：創(chuàng)建表、更新表和實(shí)施數(shù)據(jù)完整性1 運(yùn)行給定的SQL Script，建立數(shù)據(jù)庫(kù)GlobalToyz。2 創(chuàng)建所有表的關(guān)系圖。3 列出所有表中出現(xiàn)的約束（包括Primary key, Foreign key, check constraint, default, unique）4 對(duì)Recipient表和Country表中的cCountryId屬性定義一個(gè)用戶(hù)自定義數(shù)據(jù)類(lèi)型，并將該屬性的類(lèi)型定義為這個(gè)自定義數(shù)據(jù)類(lèi)型。5 把價(jià)格在$20以上的所有玩具的材料拷貝到稱(chēng)為PremiumToys的新表中。6 對(duì)表Toys實(shí)施下面數(shù)

2、據(jù)完整性規(guī)則：（1）玩具的現(xiàn)有數(shù)量應(yīng)在0到200之間；（2）玩具適宜的最低年齡缺省為1。7 不修改已創(chuàng)建的Toys表，利用規(guī)則實(shí)現(xiàn)以下數(shù)據(jù)完整性：（1）玩具的價(jià)格應(yīng)大于0；（2）玩具的重量應(yīng)缺省為1。8 給id為000001玩具的價(jià)格增加$1。實(shí)驗(yàn)二：查詢(xún)數(shù)據(jù)庫(kù)1. 顯示屬于California和Illinoi州的顧客的名、姓和emailID。2. 顯示定單號(hào)碼、商店ID，定單的總價(jià)值，并以定單的總價(jià)值的升序排列。3. 顯示在orderDetail表中vMessage為空值的行。4. 顯示玩具名字中有“Racer”字樣的所有玩具的材料。5. 根據(jù)2000年的玩具銷(xiāo)售總數(shù)，顯示“Pick of

3、the Month”玩具的前五名玩具的ID。6. 根據(jù)OrderDetail表，顯示玩具總價(jià)值大于￥50的定單的號(hào)碼和玩具總價(jià)值。7. 顯示一份包含所有裝運(yùn)信息的報(bào)表，包括：Order Number, Shipment Date, Actual Delivery Date, Days in Transit. (提示：Days in Transit = Actual Delivery Date Shipment Date)8. 顯示所有玩具的名稱(chēng)、商標(biāo)和種類(lèi)（Toy Name, Brand, Category）。9. 顯示玩具的名稱(chēng)和所有玩具的購(gòu)物車(chē)ID。如果玩具不在購(gòu)物車(chē)中，則顯示NULL值。

4、10. 以下列格式顯示所有購(gòu)物者的名字和他們的簡(jiǎn)稱(chēng)：（Initials, vFirstName, vLastName）,例如Angela Smith的Initials為A.S。11. 顯示所有玩具的平均價(jià)格，并舍入到整數(shù)。 12. 顯示所有購(gòu)買(mǎi)者和收貨人的名、姓、地址和所在城市。13. 顯示沒(méi)有包裝的所有玩具的名稱(chēng)。（要求用子查詢(xún)實(shí)現(xiàn)）14. 顯示已發(fā)貨定單的定單號(hào)碼以及下定單的時(shí)間。（要求用子查詢(xún)實(shí)現(xiàn)）實(shí)驗(yàn)三：視圖與觸發(fā)器1. 定義一個(gè)視圖，包括購(gòu)買(mǎi)者的姓名、所在州和他們所訂購(gòu)?fù)婢叩拿Q(chēng)、價(jià)格和數(shù)量。2. 基于（1）中定義的視圖，查詢(xún)顯示所有California州的購(gòu)買(mǎi)者的姓名和他們所訂購(gòu)?fù)?/p>

5、具的名稱(chēng)及數(shù)量。3. 視圖定義如下： CREATE VIEW vwOrderWrapper AS SELECT cOrderNo, cToyId, siQty, vDescription, mWrapperRate FROM OrderDetail JOIN Wrapper ON OrderDetail.cWrapperId = Wrapper.cWrapperId以下更新命令，在更新siQty和mWrapperRate屬性使用了以下更新命令時(shí)出現(xiàn)錯(cuò)誤： UPDATE vwOrderWrapper SET siQty = 2, mWrapperRate = mWrapperRate + 1 F

6、ROM vwOrderWrapper WHERE cOrderNo = 000001修改更新命令，以更新基表中的值。4. 在OrderDetail上定義一個(gè)觸發(fā)器，如果購(gòu)物者改變了定單的數(shù)量，玩具的成本也自動(dòng)地改變。（提示：Toy cost = Quantity * Toy Rate）實(shí)驗(yàn)四：存儲(chǔ)過(guò)程1. 編寫(xiě)一段程序，將每種玩具的價(jià)格提高￥0.5，直到玩具的平均價(jià)格接近$24.5為止。此外，任何玩具的最大價(jià)格不應(yīng)超過(guò)$53。2. 創(chuàng)建一個(gè)稱(chēng)為prcCharges的存儲(chǔ)過(guò)程，它返回某個(gè)定單號(hào)的裝運(yùn)費(fèi)用和包裝費(fèi)用。3. 創(chuàng)建一個(gè)稱(chēng)為prcHandlingCharges的過(guò)程，它接收定單號(hào)并顯示經(jīng)

7、營(yíng)費(fèi)用。PrchandlingCharges過(guò)程應(yīng)使用prcCharges過(guò)程來(lái)得到裝運(yùn)費(fèi)和禮品包裝費(fèi)。提示：經(jīng)營(yíng)費(fèi)用=裝運(yùn)費(fèi)+禮品包裝費(fèi) 實(shí)驗(yàn)五：事務(wù)與游標(biāo)1. 名為prcGenOrder的存儲(chǔ)過(guò)程產(chǎn)生存在于數(shù)據(jù)庫(kù)中的定單號(hào)： CREATE PROCEDURE prcGenOrder OrderNo char(6) OUTPUT as SELECT OrderNo=Max(cOrderNo) FROM Orders SELECT OrderNo= CASE WHEN OrderNo=0 and OrderNo=9 and OrderNo=99 and OrderNo=999 and Orde

8、rNo=9999 and OrderNo=99999 Then Convert(char,OrderNo+1) END RETURN當(dāng)購(gòu)物者確認(rèn)定單時(shí)，應(yīng)該出現(xiàn)下面的步驟：（1）用上面的過(guò)程產(chǎn)生定單號(hào)。（2）定單號(hào)，當(dāng)前日期，購(gòu)物車(chē)ID，和購(gòu)物者ID應(yīng)該加到Orders表中。（3）定單號(hào)，玩具ID，和數(shù)量應(yīng)加到OrderDetail表中。（4）在OrderDetail表中更新玩具成本。（提示：Toy cost = Quantity * Toy Rate）.將上述步驟定義為一個(gè)事務(wù)。編寫(xiě)一個(gè)過(guò)程以購(gòu)物車(chē)ID和購(gòu)物者ID為參數(shù)，實(shí)現(xiàn)這個(gè)事務(wù)。2. 編寫(xiě)一個(gè)程序顯示每天的定單狀態(tài)。如果當(dāng)天的定單值總

9、合大于170，則顯示“High sales”,否則顯示”Low sales”.報(bào)告中要求列出日期、定單狀態(tài)和定單總價(jià)值。二、實(shí)驗(yàn)?zāi)康?熟悉SQL語(yǔ)言，加深對(duì)數(shù)據(jù)庫(kù)知識(shí)的認(rèn)識(shí)。學(xué)會(huì)用SQL語(yǔ)言解決實(shí)際數(shù)據(jù)庫(kù)問(wèn)題。三、實(shí)驗(yàn)源代碼實(shí)驗(yàn)一、-3 /*sp_helpconstraint objname=Categorygo sp_helpconstraint objname=Countrygo sp_helpconstraint objname=OrderDetailgosp_helpconstraint objname=PickOfMonthgo sp_helpconstraint objname=C

10、ategorygo sp_helpconstraint objname=Recipientgo sp_helpconstraint objname=Shipmentgo sp_helpconstraint objname=ShippingModego sp_helpconstraint objname=ShippingRatego sp_helpconstraint objname=Shoppergo sp_helpconstraint objname=ShoppingCartgo sp_helpconstraint objname=ToyBrandgosp_helpconstraint ob

11、jname=Toysgo sp_helpconstraint objname=Wrapper； */ -4 create type xianke from char(3)alter table Recipient alter column cCountryId kkalter table Countryalter column cCountryId kk5.USE GlobalToyzGOCREATE TABLE PremiumToys ( cToyIdchar(6) primary key,vToyNamevarchar(20) not null,vToyDescriptionvarchar

12、(250),cCategoryIdchar(3) ,mToyRatemoney not null,cBrandIdchar(3),imPhoto image,siToyQohsmallint not null,siLowerAgesmallint not null,siUpperAgesmallint not null,siToyWeightsmallint,vToyImgPathvarchar(50) null)INSERT INTO PremiumToysSELECT * FROM Toys WHERE mToyRate206.7.ALTER Table Toys ADD siToyQoh

13、 smallint CHECK（siToyQoh=0 AND siToyQoh0)ALTER TABLE Toys ADD siToyWeight CHECK(siToyWeigh=1)(cToyId char(6) primary key,vToyNamevarchar(20) not null,vToyDescriptionvarchar(250),cCategoryIdchar(3),mToyRatemoney not null(mToyRate0),cBrandIdchar(3),imPhoto image,siLowerAgesmallint not null,siUpperAges

14、mallint not null,siToyWeightsmallint,vToyImgPathvarchar(50) null)8.update ToysSet mToyRate=mToyRate+1Where cToyId=000001實(shí)驗(yàn)二 1. SELECT vFirstName ,vLastName ,vEmailId FROM GlobalToyz.dbo.Shopper WHERE cState = California OR cState = IllinoisGO2. SELECT cOrderNo ,cShopperId ,mTotalCost FROM GlobalToyz

15、.dbo.Orders ORDER BY mTotalCost ASCGO3. SELECT * FROM GlobalToyz.dbo.OrderDetail WHERE vMessage IS NULLGO4. SELECT top 5 cToyId ,siMonth ,iYear ,iTotalSold FROM GlobalToyz.dbo.PickOfMonth WHERE iYear = 2000 ORDER BY iTotalSold DESCGO5. SELECT top 5 cToyId ,siMonth ,iYear ,iTotalSold FROM GlobalToyz.

16、dbo.PickOfMonth WHERE iYear = 2000 ORDER BY iTotalSold DESCGO6. SELECT cOrderNo ,mToyCost FROM GlobalToyz.dbo.OrderDetail WHERE mToyCost 50GO7. SELECT vToyName ,ToyBrand.cBrandId ,ToyBrand.cBrandName ,Category.cCategoryId ,Category.cCategory ,Category.vDescription FROM GlobalToyz.dbo.Toys, GlobalToy

17、z.dbo.ToyBrand, tegory WHERE toys.cBrandId = ToyBrand.cBrandId AND toys.cCategoryId = Category.cCategoryIdGO8. SELECT vToyName ,ToyBrand.cBrandId ,ToyBrand.cBrandName ,Category.cCategoryId ,Category.cCategory ,Category.vDescription FROM GlobalToyz.dbo.Toys, GlobalToyz.dbo.ToyBrand, tegory WHERE toys

18、.cBrandId = ToyBrand.cBrandId AND toys.cCategoryId = Category.cCategoryIdGO9. SELECT vToyName ,ShoppingCart.cCartId FROM GlobalToyz.dbo.Toys, GlobalToyz.dbo.ShoppingCart WHERE Toys.cToyId = ShoppingCart.cToyIdGO10. SELECT FLOOR(AVG(mToyRate) FROM GlobalToyz.dbo.ToysGO11. SELECT FLOOR(AVG(mToyRate) F

19、ROM GlobalToyz.dbo.ToysGO12. SELECT Shopper.vFirstName ,Shopper.vLastName ,Shopper.vAddress ,Shopper.cCity ,Recipient.vFirstName ,Recipient.vLastName ,Recipient.vAddress ,Recipient.cCity FROM GlobalToyz.dbo.Shopper, GlobalToyz.dbo.RecipientGO13. SELECT vToyName FROM GlobalToyz.dbo.Toys WHERE cToyId

20、IN ( SELECT cToyId FROM GlobalToyz.dbo.OrderDetail WHERE cWrapperId IS NULL )GO14. SELECT cOrderNo ,dOrderDate FROM GlobalToyz.dbo.Orders WHERE cOrderNo IN ( SELECT cOrderNo FROM GlobalToyz.dbo.Shipment WHERE cDeliveryStatus = d )GO實(shí)驗(yàn)三1. CREATE VIEW IS_CHAXUNASSELECT Shopper.vFirstName,Shopper.vLast

21、Name,Shopper.cCity,Toys.vToyName,Toys.mToyRate,OrderDetail.siQtyFROM Shopper,Orders,OrderDetail,ToysWHERE Orders.cOrderNo=OrderDetail.cOrderNo AND Shopper.cShopperId=Orders.cShopperId AND OrderDetail.cToyId=Toys.cToyId;2. SELECT vFirstName,vLastName,vToyName,siQtyFROM IS_CHAXUNWHERE cCity=California

22、;3. UPDATE vwOrderWrapperSET siQty = 2, mWrapperRate = mWrapperRate + 1 FROM vwOrderWrapperWHERE cOrderNo = 0000014. Create Trigger truOD ON OrderDetailFOR UPDATEASIF UPDATE(siQty) BEGIN DECLARE siQty intSELECT siQty=deleted.siQtyFROM deletedUPDATE OrderDetail SET mToyCost=mToyCost/siQty*siQty WHERE

23、 cOrderNo IN (SELECT cOrderNoFROM deleted) END實(shí)驗(yàn)四1. SELECT cOrderNo ,dOrderDate FROM GlobalToyz.dbo.Orders WHERE cOrderNo IN ( SELECT cOrderNo FROM GlobalToyz.dbo.Shipment WHERE cDeliveryStatus = d )GO2. CREATE PROCEDURE prcCharges cOrderNo int,mShippingCharges money OUTPUT, mGiftWrapCharges money O

24、UTPUTASBEGINSELECT cOrderNo=cOrderNo, mShippingCharges =mShippingCharges, mGiftWrapCharges =mGiftWrapChargesFROM OrdersWHERE cOrderNo=cOrderNoEND3. CREATE PROCEDURE prcHandlingCharges cOrderNo intASBEGINDECLARE mScharges money DECLARE mGWcharges moneyEXEC prcCharges cOrderNo,mScharges output, mGWcharges outputRETURN mScharges+mGWchargesEND實(shí)驗(yàn)五1. CREATE PROCEDURE tianjia cCartId char(6),cShopperId char(6),cToyId char(6),siQty smallintASBEGINBEGIN TRANSACTIONDECLARE date datetime;DECLARE mToyRate moneyDECLARE cOrderNo char(6)SELECT da