數(shù)據(jù)庫期末重要題型總結(jié)

1、題型：1 E-R圖/文字描述/偽代碼(實(shí)體-屬性)/真實(shí)代碼（create table）2關(guān)系代數(shù)表達(dá)式書寫，畫自然連接表格3 select語句書寫 （3部分）題型一 E-R圖問題212標(biāo)記：考慮下圖描述的發(fā)票（發(fā)票）數(shù)據(jù)庫的ER圖。（1）給出的從ER圖的要求和約束的精確說明。 5商標(biāo)（2）轉(zhuǎn)換圖轉(zhuǎn)換成關(guān)系模式，通過提供相應(yīng)的CREATE TABLE語句。指定所有鍵和外鍵約束。1.5CMQuestion 2 12 marks: Consider the following figure that describes the E-R diagram of an invoice(發(fā)票) datab

2、ase. (1) Give a precise specification of the requirements and constraints from the E-R diagram. 5 marks(2) Translate the diagram into relational model by supplying the appropriate CREATE TABLE statements. Specify all the key and foreign key constraints. 7 marks矩形：實(shí)體。多個實(shí)體的集合是實(shí)體集。橢圓形：屬性（帶有下劃線是主碼）。菱形：聯(lián)

3、系集。有箭頭的一方是“一”，沒有箭頭是“多”，從有箭頭的開始分析：A（有箭頭）對B，一個A對多個B，一個B只有一個A。聯(lián)系集有沒有屬性跟要不要描述聯(lián)系集是沒有關(guān)系的！但是一般沒有屬性就不寫，有屬性就寫。聯(lián)系集有寫時，實(shí)體集不需要寫上對方的主碼，有屬性的聯(lián)系集，多對多或多對一時，兩邊的實(shí)體集的主碼都作為外碼寫進(jìn)此聯(lián)系集。一對一時，任選一個實(shí)體集的主碼；沒寫時，實(shí)體集多的一方要寫上一的一方的主碼，如果聯(lián)系集有屬性，還要把屬性寫到多的一方。             

4、60;                                             7商標(biāo)1.5CMQuestion 2 12 marks

5、: 化E-R圖為文字描述1分別寫每個主體集的主體名和屬性以及主碼（帶下劃線的）2根據(jù)是否有箭頭和箭頭方向描述主體間的關(guān)系3聯(lián)系集的屬性描述An invoice has attributes: Invoice#(primary key), TotalOrderAmt, Date, Terms, ShipVia. A customer has attributes: Cust#(primary key), CName, Street, City, State, Zip, Phone. A product has attributes: Prod#(primary key), StandardPri

6、ce, Description. The relationship between invoice and customer is many-to-one. One invoice can relate to only one customer, while one customer can relate to any number of invoices. The relationship between invoice and product is many-to-many. Any number of products can be placed in one invoice, and

7、one product can appear in different invoices. The relationship between invoice and product has two attributes: SellPrice and Quantity.發(fā)票有屬性：發(fā)票（主鍵），TotalOrderAmt，日期，條款，SHIPVIA。一位顧客有屬性：卡斯特（主鍵），CNAME，街道，城市，州，郵編，電話。一個產(chǎn)品有屬性：PROD（主鍵），標(biāo)準(zhǔn)價格，說明。發(fā)票和顧客之間的關(guān)系是多對1。一張發(fā)票可以涉及僅一個客戶，而一個客戶可以涉及任何數(shù)量的發(fā)票。發(fā)票和產(chǎn)品之間的關(guān)系是許多對多。任何

8、數(shù)量的產(chǎn)品可以被放置在一張發(fā)票，和一個產(chǎn)品可以出現(xiàn)在不同的發(fā)票。發(fā)票和產(chǎn)品之間的關(guān)系有兩個屬性：SellPrice和數(shù)量?；疎-R為代碼1除了日期是date類型，其他都可以用varchar（20），類型寫在后2 #叫_Number3注意格式，括號，逗號4主碼一定要前面羅列一次，后面再總結(jié)是主碼一次。外碼也是。有一種情況是描述聯(lián)系集時，來自兩個實(shí)體集的主碼同時也是外碼。 (2) create table Invoice (Invoice_Number char(10), TotalOrderAmt integer, Invoice_Date date, Terms char(30), ShipV

9、ia char(20), Cust_Number char(10),（對方的主碼外碼） Primary key(Invoice_Number),（主碼描述） Foreign key(Cust_Number) references Customer)（外碼描述）Create table Customer (Cust_Number char(10), CName char(10), Street char(30), City char(10), State char(10), Zip char(10), Phone char(20),Primary key(Cust_Number)Create t

10、able Product (Prod_Number char(10), StandardPrice number, Description char(30), Primary key(Prod_Number)Create table LineItem (Invoice_Number char(10), Prod_Number char(10),SellPrice number, Quantity number, Primary key(Invoice_Number, Prod_Number)（主碼是來自兩個實(shí)體集的主碼，同時也是外碼） Foreign key(Invoice_Number) r

11、eferences Invoice, Foreign key(Prod_Number) references Product)化文字描述為E-R圖Question 3 12 marks There are 3 entities into database of an enterprise. Entity Store is associated with store number, store name and address etc. Entity commodity(商品) is associated with commodity number, commodity name, specif

12、ication and price. Entity employee（員工） is associated with employee no, employee name, sex and performance. Each commodity can be sold in many stores and each store may sold many kinds of commodity. For each commodity in a certain store, the sale quantity must be recorded per month. For each store, t

13、here are many employees. For each employee, he can works for only one store. For each employee who works in a certain store, salary and work period must be recorded.(1) Draw ER diagram to illustrate the requirements. And you can make and state necessary assumptions if any. 6 marks(2) Translate your

14、ER diagram into relational database schemas, underline primary keys of each relation, and label foreign keys if it has. Be sure using different way to mark primary keys and foreign keys. 6 marks問題3 12標(biāo)記有3個實(shí)體到一個企業(yè)數(shù)據(jù)庫。實(shí)體店與店號，店名和地址等實(shí)體商品與商品號，商品名稱，規(guī)格和價格的關(guān)聯(lián)。實(shí)體員工與員工沒有，員工姓名，性別和性能有關(guān)。每種商品可以在很多商店銷售，每家店可能銷售的多種商

15、品。對于在某些商店每一件商品，銷售數(shù)量必須每月進(jìn)行記錄。對于每一個商店，有很多員工。對于每一個員工，他的作品只有一家門店。公司為每位員工誰在某個店里工作，工資和工作時間必須記錄。（1）畫出ER圖來說明的要求。你可以做，如果任何國家必要的假設(shè)。 6商標(biāo)（2）翻譯的ER圖到關(guān)系數(shù)據(jù)庫模式，背后的每個關(guān)系的主鍵和標(biāo)簽外鍵，如果有。請務(wù)必在使用不同的方式來紀(jì)念主鍵和外鍵。 6商標(biāo)Question 3 12 marks (1) Draw ER diagram to illustrate the requirements. And you can make and state necessary assu

16、mptions if any. 6 marks(2) Translate your ER diagram into relational database schemas, underline primary keys of each relation, and label foreign keys if it has. Be sure using different way to mark primary keys and foreign keys. 6 marks 化文字描述為偽代碼注意英文詞語要規(guī)范。根據(jù)上文，下劃線聯(lián)系集要寫明外碼來自于哪里。主碼是下劃線。Store(Store_num

17、ber, store_name , address) Employee(employee_no, employee_name, sex, performance)commodity (commodity_number, commodity_name, specification , Price)sale (Store_number,commodity_number, MonthQuatity) Store_number is a foreign key referring store(store_number). commodity_number is a foreign key referr

18、ing commodity(commodity_number) .Hire(employee_no,Store_number, During, salary) Store_number is a foreign key referring store(store_number) . employee_no is a foreign key referring employee(employee_no) .Note: all primary keys are underlined.另外同題型題目補(bǔ)充（E-R圖）Question 3 (12 points) Consider the followi

19、ng database requirement:A hospital has properties like ID, name, location, rank, capacity. A doctor can be described by ID, name, age, skill. A patient has properties like ID, name, age, sex, address. The above three entities must satisfy some constraints: Each doctor can be unemployed or employed b

20、y one hospital. If a doctor is employed, his salary needs to be recorded in the database. A patient can go to many hospitals.(1) Draw ER diagram to illustrate the above database requirement. 8 points(2) Translate your ER diagram into relational database schemas, and point out the primary keys and fo

21、reign keys. You can write your answers in the following format: “R(a1, a2, a3, a4), primary key: a1, foreign key: a4”. 4 points問題3（12分）考慮以下數(shù)據(jù)庫要求：某醫(yī)院有一個像ID，名稱，位置，等級，能力屬性。醫(yī)生可以通過ID，姓名，年齡，技能描述。病人有一個像ID，姓名，年齡，性別，地址屬性。上述三個實(shí)體，必須滿足一些限制：每個醫(yī)生會失業(yè)或一家醫(yī)院聘用。如果醫(yī)生采用，他的工資需要被記錄在數(shù)據(jù)庫中。病人可以去很多醫(yī)院。（1）畫出ER圖來說明上述數(shù)據(jù)庫要求。 8分（2）

22、翻譯的ER圖到關(guān)系數(shù)據(jù)庫模式，并指出了主鍵和外鍵。你可以寫你的答案按以下格式：“R（A1，A2，A3，A4），主鍵：A1，外鍵：A4”。 4分Question 3 (12points)（live in）hospital(ID, name, location, rank, capacity), primary key: ID,doctor(ID, name, age, skill, hospitalID, salary), primary key: ID, foreign key: hospitalID refer to hospital(ID)patient (ID, name, age, s

23、ex, address), primary key: ID,（不需寫Hid了）livein(patientID, hospitalID) primary key: (hospitalID, PatientID), foreign key hospitalID refer to hospital(ID)，foreign key patientID refer to patient (ID)題型二 關(guān)系代數(shù)表達(dá)式問題38標(biāo)記：編寫關(guān)系代數(shù)表達(dá)式滿足以下數(shù)據(jù)庫查詢的要求。1.5CMQuestion 3 8 marks: Write relational algebra expressions to

24、satisfy query requirements in the following database. （1）求運(yùn)動次數(shù)和安·史密斯點(diǎn)。 2商標(biāo)（2）尋找誰獲得每個作業(yè)點(diǎn)的最大數(shù)量的學(xué)生的全名和作業(yè)數(shù)量。 2商標(biāo)（3）求所有學(xué)生的鍛煉次數(shù)，章及分他的電子郵件地址屬于規(guī)模。 2商標(biāo)（4）寫出關(guān)系代數(shù)表達(dá)式的結(jié)果2商標(biāo) 投影選擇自然連接：盡量涉及少的表格，減少自然連接(1) Find the exercise number（ENO） and points of Ann Smith. 2 marks(2) Find the full name and homework number（E

25、NO） of students who get the maximum number of points for each homework. 2 marks(3) Find all exercise number, chapter and points of students whose email address belongs to scau. 2 marks(4) Write out the result of the relational algebra expression 2 marks 1.5CMQuestion 3 8 marks: 描寫關(guān)系代數(shù)表達(dá)式(1) CHAPTER,

26、ENO,POINTSFIRST=Ann LAST=Smith(STUDENTS RESULTS)(2) FIRST,LAST,CHAPTER,ENO(STUDENTS RESULTS ( CHAPTER,ENOGMAX(POINTS) RESULTS) or FIRST,LAST,CHAPTER,ENO(STUDENTS POINTS=MAXPOINT (RESULTS EXERCISES)(3) CHAPTER,ENO,POINTS(RESULTS EMAIL=%scau%STUDENTS)(4) FIRSTLASTCHAPTERENOPOINTSAnnSmith1110AnnSmith21

27、12MichaelJones2110 題型三 select語句 1.5CMQuestion 4 12 marks: Consider the following relational schema:Computer (cno, memory_size, disk_size)Manufacturer (mno, mname, mcity)Shop (sno, sname, scity)Purchase (sno, mno, cno, number, price)A manufacture produces many types of computers. A shop first purchas

28、es some computers from manufacturers, and then sells them to customers.Write the following queries in SQL.考慮以下關(guān)系模式：計算機(jī)（CNO，memory_size，DISK_SIZE）制造商（MNO，姓名，曼城）店（SNO，SNAME，市）購買（SNO，MNO，CNO，數(shù)量，價格）制造業(yè)產(chǎn)生許多類型的計算機(jī)。一家商店購買第一某些計算機(jī)從廠家，然后將其銷售給客戶。寫在SQL以下查詢。(1) Find the cno of computers which have memory size la

29、rger than 2GB and hard disk size larger than 500 GB. (2) (1) select cno from computer where memory_size>2 and disk_size>500(3)(4) （1）找到具有存儲容量大于2GB硬盤大小超過500 GB的計算機(jī)的CNO。 3商標(biāo)(5) 3 marks(2) Find the average price of computers with cno LX3000 which are produced in Beijing and sold in Guangzhou. （2）

30、找到電腦的平均價格與CNO“LX3000”這是產(chǎn)于北京和廣州銷售。 3商標(biāo) 3 marks(2) select avg(price) from manufacturer, shop, purchase where purchase.sno=shop.sno and shop.scity=Guangzhou and purchase.mno=manufacturer.mno and manufacturer.mcity=Beijing and o=LX3000（制造商manufacturer，商店所在地shop，銷售給客戶purchase.）(3) Find the cno of the mos

31、t expensive computer which is sold in shops located in Guangzhou. （3）尋找這是在位于廣州的商店出售的最昂貴的計算機(jī)的CNO。 3商標(biāo)(3) select cno from purchase where shop.scity=Guangzhou and price = select max(price) from purchase, shop where purchase.sno=shop.sno and shop.scity=Guangzhou 3 marks(6) Find the mname of manufacturer

32、s which havent sold computers to shops. 3 marks(7) （4）找到了其中沒有賣電腦的商店廠家MNAME。 3商標(biāo)(8) (4) select mname from manufacturer(9) where mno not in (select mno from purchase)題型二/三題目補(bǔ)充Question 2: 15 marks Use the following relations to answer questions.Car(serial, model, year, color) Makes(model, maker) Owns(o

33、wner, serial)1.5CMA tuple in Car represents a specific car of a given model, made in a given year, with a serial number and color. A tuple in Makes specifies that a maker company makes cars of a certain model. A tuple in Owns specifies that an owner owns the car with a given serial number.(1) Write

34、relational algebra expressions to satisfy query requirements in the above database. 2 marks for eachA. retrieve all models used in 2013.B. retrieve all owners of a red car made by Toyota.C. all companies that made at least one car in the year 2010.問題2：15標(biāo)記使用下面的關(guān)系來回答問題。汽車（系列，型號，年份，顏色）使（型號，制造商）旗下?lián)碛校ㄋ?/p>

35、者，串行）在汽車的元組代表一個給定的模型的一個特定的汽車，在某一年制成，具有序列號和顏色。在打造一個元組指定制造商公司生產(chǎn)某型號的汽車。元組在旗下?lián)碛兄付ㄋ姓邠碛熊囉媒o定的序列號。（1）收件關(guān)系代數(shù)表達(dá)式來滿足上述數(shù)據(jù)庫查詢的要求。 2分求每個A.檢索在2013年使用的所有車型。B.檢索一輛紅色轎車，豐田作出的所有業(yè)主。C.在2010年取得了至少一輛車的所有公司。(2) Write SQL expressions to satisfy query requirements in the above database. 3marks for eachA. retrieve all owners

36、 of a red car made by Toyota.B. all companies that made at least 3 cars in the year 2010.C. all companies that made cars in 2012 are more than the total cars made by Toyota in 2011（2）編寫SQL表達(dá)式來滿足上述數(shù)據(jù)庫查詢的要求。 3分求每個A.檢索一輛紅色轎車，豐田作出的所有業(yè)主。二，在2010年取得了至少3輛汽車的所有公司。C.在2012年制造的汽車的所有企業(yè)都超過2011年由豐田轎車總量關(guān)系代數(shù)表達(dá)式答案Que

37、stion 2: 15 marks Use the following relations to answer questions.(1) Write relational algebra expressions to satisfy query requirements in the above database. 2 marks for eachA. retrieve all models used in 2013.Answer: model(year=2013(Car)B. retrieve all owners of a red car made by Toyota.Answer: o

38、wner(Owns color=red(Car) maker=Toyota(Makes)C. all companies that made at least one car in the year 2010.Answer: maker(year=2010（Car） Makes)答案問題2：15標(biāo)記使用下面的關(guān)系來回答問題。（1）收件關(guān)系代數(shù)表達(dá)式來滿足上述數(shù)據(jù)庫查詢的要求。 2分求每個A.檢索在2013年使用的所有車型。答：model（year= 2013（車）B.檢索一輛紅色轎車，豐田作出的所有業(yè)主。答：owner（旗下?lián)碛衏olor='紅'（汽車）maker='豐

39、田'（使）C.在2010年取得了至少一輛車的所有公司。答：maker（year= 2010（汽車）讓）(2) Write SQL expressions to satisfy query requirements in the above database. 3marks for each A. retrieve all owners of a red car made by Toyota.Answer: Select owner From Owns, Car, MakesWhere Owns.serial=Car.serial and Car.model=Makes.model an

40、d color=red and maker =ToyotaB. all companies that made at least 3 cars in the year 2010.Select maker From Car, Makes Where Car.model=Makes.model and Car.year=2010 Group by maker Having count(serial)>3C. all companies that made cars in 2012 are more than the total cars made by Toyota in 2011selec

41、t maker from Car, Makes where Car.model=Makes.model and Car.year=2012 group by Makerhaving count(serial)>(select count(serial) from Car, Makeswhere Car.model=Makes.model and maker=Toyota and year=2011) （2）編寫SQL表達(dá)式來滿足上述數(shù)據(jù)庫查詢的要求。 3馬克每個 A.檢索一輛紅色轎車，豐田作出的所有業(yè)主。答：選擇老板從擁有，車，使其中Owns.serial = Car.serial和Ca

42、r.model = Makes.model和顏色='紅'和制造商='豐田'二，在2010年取得了至少3輛汽車的所有公司。選擇制造商從汽車，使其中Car.model = Makes.model和Car.year = 2010組由制造商擁有數(shù)（串行接口）> 3C.在2012年制造的汽車的所有企業(yè)都超過2011年由豐田轎車總量從汽車制造商中選擇，由制造商，使那里Car.model = Makes.model和Car.year = 2012組從汽車具有計數(shù)（串行接口）>（SELECT COUNT（串口），使其中，Car.model = Makes.model

43、和制造商='豐田'和年份='2011'）Question 4. (24 points) The following five tables are for a company management system:EMPLOYEE (SSN, Fname, Lname, Bdate, Address, Sex, Salary, Dnumber)DEPARTMENT (Dnumber, Dname, MgrSSN )PROJECT (Pnumber, Pname, Pcity)WORKS_ON (Pnumber, ESSN, Wdate, Hours)CHILD (

44、ESSN, DSSN, D_Fname, D_Lname, Sex, Bdate)以下5張表格是公司管理體系：員工（SSN，F(xiàn)NAME，LNAME，Bdate，地址，性別，工資，Dnumber）署（Dnumber，DNAME，MgrSSN）項(xiàng)目（Pnumber，PNAME，Pcity）WORKS_ON（Pnumber，ISSN，Wdate，小時）兒童（ESSN，DSSN，D_name，D_name，性別，日期）關(guān)系代數(shù)表達(dá)式1. Based on the giving relations, Specify the following queries using the relational o

45、perators.(3 points for each)1)List the names of all employees with birthday earlier than 1970-1-1 and salary less than $5000. 2)List the names of all employees who have a child with the same first name as themselves. 3)List the names and cities of all projects which are located on same city. （1）基于給人

46、關(guān)系，利用關(guān)系運(yùn)算符指定下列查詢。（3分，每）1）列出所有員工的姓名，生日早于“1970-1-1”和薪水少于$5000。2）名單誰擁有一個孩子相同的名字作為自己的所有員工的姓名。3）列出它們位于同一城市的所有項(xiàng)目的名稱和城市。1：1)2)3) 2.Specify the following operations in SQL(3 points for each). 1)Define the table WORKS_ON, declare Pnumber, ESSN, Wdate as the primary key, Pnumber as the foreign key referencing the primary key of project, ESSN as