数据库期末重要题型总结.doc

题型：1 E-R图/文字描述/伪代码(实体-属性)/真实代码（create table）2关系代数表达式书写，画自然连接表格3 select语句书写 （3部分）题型一 E-R图问题212标记：考虑下图描述的发票（发票）数据库的ER图。（1）给出的从ER图的要求和约束的精确说明。 5商标（2）转换图转换成关系模式，通过提供相应的CREATE TABLE语句。指定所有键和外键约束。1.5CMQuestion 2 12 marks: Consider the following figure that describes the E-R diagram of an invoice(发票) database. (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矩形：实体。多个实体的集合是实体集。椭圆形：属性（带有下划线是主码）。菱形：联系集。有箭头的一方是“一”，没有箭头是“多”，从有箭头的开始分析：A（有箭头）对B，一个A对多个B，一个B只有一个A。联系集有没有属性跟要不要描述联系集是没有关系的！但是一般没有属性就不写，有属性就写。联系集有写时，实体集不需要写上对方的主码，有属性的联系集，多对多或多对一时，两边的实体集的主码都作为外码写进此联系集。一对一时，任选一个实体集的主码；没写时，实体集多的一方要写上一的一方的主码，如果联系集有属性，还要把属性写到多的一方。7商标1.5CMQuestion 2 12 marks: 化E-R图为文字描述1分别写每个主体集的主体名和属性以及主码（带下划线的）2根据是否有箭头和箭头方向描述主体间的关系3联系集的属性描述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), StandardPrice, 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 one product can appear in different invoices. The relationship between invoice and product has two attributes: SellPrice and Quantity.发票有属性：发票（主键），TotalOrderAmt，日期，条款，SHIPVIA。一位顾客有属性：卡斯特（主键），CNAME，街道，城市，州，邮编，电话。一个产品有属性：PROD（主键），标准价格，说明。发票和顾客之间的关系是多对1。一张发票可以涉及仅一个客户，而一个客户可以涉及任何数量的发票。发票和产品之间的关系是许多对多。任何数量的产品可以被放置在一张发票，和一个产品可以出现在不同的发票。发票和产品之间的关系有两个属性：SellPrice和数量。化E-R为代码1除了日期是date类型，其他都可以用varchar（20），类型写在后2 #叫_Number3注意格式，括号，逗号4主码一定要前面罗列一次，后面再总结是主码一次。外码也是。有一种情况是描述联系集时，来自两个实体集的主码同时也是外码。 (2) create table Invoice (Invoice_Number char(10), TotalOrderAmt integer, Invoice_Date date, Terms char(30), ShipVia 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 table 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)（主码是来自两个实体集的主码，同时也是外码） Foreign key(Invoice_Number) references 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, specification 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, there 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 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标记有3个实体到一个企业数据库。实体店与店号，店名和地址等实体商品与商品号，商品名称，规格和价格的关联。实体员工与员工没有，员工姓名，性别和性能有关。每种商品可以在很多商店销售，每家店可能销售的多种商品。对于在某些商店每一件商品，销售数量必须每月进行记录。对于每一个商店，有很多员工。对于每一个员工，他的作品只有一家门店。公司为每位员工谁在某个店里工作，工资和工作时间必须记录。（1）画出ER图来说明的要求。你可以做，如果任何国家必要的假设。 6商标（2）翻译的ER图到关系数据库模式，背后的每个关系的主键和标签外键，如果有。请务必在使用不同的方式来纪念主键和外键。 6商标Question 3 12 marks (1) Draw ER diagram to illustrate the requirements. And you can make and state necessary assumptions 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 化文字描述为伪代码注意英文词语要规范。根据上文，下划线联系集要写明外码来自于哪里。主码是下划线。Store(Store_number, 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 referring 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.另外同题型题目补充（E-R图）Question 3 (12 points) Consider the following 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 by 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 foreign 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分）考虑以下数据库要求：某医院有一个像ID，名称，位置，等级，能力属性。医生可以通过ID，姓名，年龄，技能描述。病人有一个像ID，姓名，年龄，性别，地址属性。上述三个实体，必须满足一些限制：每个医生会失业或一家医院聘用。如果医生采用，他的工资需要被记录在数据库中。病人可以去很多医院。（1）画出ER图来说明上述数据库要求。 8分（2）翻译的ER图到关系数据库模式，并指出了主键和外键。你可以写你的答案按以下格式：“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, sex, 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)题型二 关系代数表达式问题38标记：编写关系代数表达式满足以下数据库查询的要求。1.5CMQuestion 3 8 marks: Write relational algebra expressions to satisfy query requirements in the following database. （1）求运动次数和安史密斯点。 2商标（2）寻找谁获得每个作业点的最大数量的学生的全名和作业数量。 2商标（3）求所有学生的锻炼次数，章及分他的电子邮件地址属于规模。 2商标（4）写出关系代数表达式的结果2商标 投影选择自然连接：尽量涉及少的表格，减少自然连接(1) Find the exercise number（ENO） and points of Ann Smith. 2 marks(2) Find the full name and homework number（ENO） 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: 描写关系代数表达式(1) CHAPTER,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) FIRSTLASTCHAPTERENOPOINTSAnnSmith1110AnnSmith2112MichaelJones2110 题型三 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 purchases some computers from manufacturers, and then sells them to customers.Write the following queries in SQL.考虑以下关系模式：计算机（CNO，memory_size，DISK_SIZE）制造商（MNO，姓名，曼城）店（SNO，SNAME，市）购买（SNO，MNO，CNO，数量，价格）制造业产生许多类型的计算机。一家商店购买第一某些计算机从厂家，然后将其销售给客户。写在SQL以下查询。(1) Find the cno of computers which have memory size larger than 2GB and hard disk size larger than 500 GB. (2) (1) select cno from computer where memory_size2 and disk_size500(3)(4) （1）找到具有存储容量大于2GB硬盘大小超过500 GB的计算机的CNO。 3商标(5) 3 marks(2) Find the average price of computers with cno LX3000 which are produced in Beijing and sold in Guangzhou. （2）找到电脑的平均价格与CNO“LX3000”这是产于北京和广州销售。 3商标 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 most expensive computer which is sold in shops located in Guangzhou. （3）寻找这是在位于广州的商店出售的最昂贵的计算机的CNO。 3商标(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 manufacturers which havent sold computers to shops. 3 marks(7) （4）找到了其中没有卖电脑的商店厂家MNAME。 3商标(8) (4) select mname from manufacturer(9) where mno not in (select mno from purchase)题型二/三题目补充Question 2: 15 marks Use the following relations to answer questions.Car(serial, model, year, color) Makes(model, maker) Owns(owner, 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 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标记使用下面的关系来回答问题。汽车（系列，型号，年份，颜色）使（型号，制造商）旗下拥有（所有者，串行）在汽车的元组代表一个给定的模型的一个特定的汽车，在某一年制成，具有序列号和颜色。在打造一个元组指定制造商公司生产某型号的汽车。元组在旗下拥有指定所有者拥有车用给定的序列号。（1）收件关系代数表达式来满足上述数据库查询的要求。 2分求每个A.检索在2013年使用的所有车型。B.检索一辆红色轿车，丰田作出的所有业主。C.在2010年取得了至少一辆车的所有公司。(2) Write SQL expressions to satisfy query requirements in the above database. 3marks for eachA. retrieve all owners 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表达式来满足上述数据库查询的要求。 3分求每个A.检索一辆红色轿车，丰田作出的所有业主。二，在2010年取得了至少3辆汽车的所有公司。C.在2012年制造的汽车的所有企业都超过2011年由丰田轿车总量关系代数表达式答案Question 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: owner(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标记使用下面的关系来回答问题。（1）收件关系代数表达式来满足上述数据库查询的要求。 2分求每个A.检索在2013年使用的所有车型。答：model（year= 2013（车）B.检索一辆红色轿车，丰田作出的所有业主。答：owner（旗下拥有color=红（汽车）maker=丰田（使）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 and 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 2011select 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表达式来满足上述数据库查询的要求。 3马克每个 A.检索一辆红色轿车，丰田作出的所有业主。答：选择老板从拥有，车，使其中Owns.serial = Car.serial和Car.model = Makes.model和颜色=红和制造商=丰田二，在2010年取得了至少3辆汽车的所有公司。选择制造商从汽车，使其中Car.model = Makes.model和Car.year = 2010组由制造商拥有数（串行接口） 3C.在2012年制造的汽车的所有企业都超过2011年由丰田轿车总量从汽车制造商中选择，由制造商，使那里Car.model = Makes.model和Car.year = 2012组从汽车具有计数（串行接口）（SELECT COUNT（串口），使其中，Car.model = Makes.model和制造商=丰田和年份=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 (ESSN, DSSN, D_Fname, D_Lname, Sex, Bdate)以下5张表格是公司管理体系：员工（SSN，FNAME，LNAME，Bdate，地址，性别，工资，Dnumber）署（Dnumber，DNAME，MgrSSN）项目（Pnumber，PNAME，Pcity）WORKS_ON（Pnumber，ISSN，Wdate，小时）儿童（ESSN，DSSN，D_name，D_name，性别，日期）关系代数表达式1. Based on the giving relations, Specify the following queries using the relational operators.(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）基于给人关系，利用关系运算符指定下列查询。（3分，每）1）列出所有员工的姓名，生日早于“1970-1-1”和薪水少于$5000。2）名单谁拥有一个孩子相同的名字作为自己的所有员工的姓名。3）列出它们位于同一城市的所有项目的名称和城市。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 the foreign key refer