課程名稱︰資料庫系統 課程性質︰資工系大四必修 課程教師︰朱浩華 開課學院：電資學院 開課系所︰資訊工程 考試日期（年月日）︰ 考試時限（分鐘）： 是否需發放獎勵金：是 （如未明確表示，則不予發放） 試題 : (1)(12 points) In the space below,draw an E/R diagram that best represents the following scenario. Indicate keys, many-one relationships, wek entity sets, and other features of E/R diagrams covered in class, as approprate. There are hospitals, with a name (unique identifier) and address. There are rooms in hospitals, with a room number. There are people, with a name and social-security number. Some people are doctors, and doctors have a specialty; some people are patients, and patients have an age. An operation involves a patient and a doctor, and takes place in a hospital room. An operation has a date and time. For full credit, do not make operations an entity set; rather. Make it a relationship. (2)(12points) Translate the above ER diagram into relational tables. Use SQL create_tabe PART B:Relational Algebra(28 points) Consider the following four relations to store data about animals in a zoo: Animals(idNumber, type, cageNumber) Cages(cageNumber, maxAnimals) TypeKeepers(nume, type) CageKeepers(name, cageNumber) idNumber is a key for Animals, and cageNumber is a key for Cages. Cages.maxAnimals specifies the maximum nunmber of animals allowed in that cage. TypeKeepers lists people responsible for caring for a particular type of animal, while CageKeepers lists people responsible for caring for a certain cage. For each of these two, both attributes form the key; i.e. it is possible for the same person to care for multiple types/cages, and it is possible for the same type/cage to be cared for by several people. (1)(6points)Consider the following sequence of relational algebra statement: Result = ( Animals∞CageKeepers)∩(Animals∞TypeKeepers) Given the following sets of data for each of the relations, show the relation that will be stored into Result table(show the schema and all the tuples that will be in the relation). Animals idNumber Type cageNumber 1 Zebra 10 2 Monkey 20 3 Monkey 10 4 Kangaroo 30 5 Zebra 40 6 Monkey 40 CageKeepers Name cageNumber Ann 10 Chris 20 Chris 30 Bob 40 Bob 10 TypeKeepers Name type Ann Zebra Bob Monkey Ann Kangaroo Chris Kangaroo Result [大表格] (2)(6points)State briefly in English what the query from (1) is asking. (3)(6points)Write a relational algebra expression to find all pairs of animals of two different types in the same cage. For each pair of animals satisfying this requirement, your expression should return each animal's idNumber and type, as well as the cageNumber of the cage they are in. For example, if Animal 1 of type Zebra and Animal 2 of type Monkey are both in cage 10, then the result of your expression should include a tuple(1,Zebra, 2,Monkey, 10)(the attributes could be in a different order). The same animal pair should not appear twice in your result. (4)(10points)Consider a relation Took(name,class,quarter)whose tuples record that a student took a given class in a given quarter. You may assume that no two students have the same name, and there is no key for this relation except all three attributes together. Write a relational algebra expression to find all pairs of students who have never taken a class together -i.e., have never taken the same class in the same quarter. Make sure to return each pair of students only once.(For example, if your expression returns <Mary,Fred> then it should not also return<Fred,Mary> or any additional copies of<Mary,Fred>.) Your expression will be graded on simplicity as well as coreectness. PART C: SQL(27 points) The relations below represent information about the 15 members of the UN Security Council and the resolutions(res)that they voted on. Members(name, status) Vote(res,country,voe) The status of a country is either 'permanent' or 'elected'. A vote can be either 'yes','no',or'abstain'. (1)(8points)In order for a resolution to pass, there must be at least 9 'yes' votes from among the 15 members of the council. Inaddition, there must be no veto; that is ,none of the five permanent members of the council vote 'no' on that resolution. We wish to write a query to find the set of resolutions that passed. Enter sub-queries in the spaces below that will make the query work: SELECT res FROM Votes vv WHERE 9<-(/*SQ1*/ ) AND NOT EXISTS(/*SQ2*/ ) (2)(2points)Explain in English what your SQ1 from (1) does. (3)(2points)Explain in English what your SQ2 from (1) does. (4)(15poitns)write a query to list each permanent member of the council, along with the number of vetoes ('no'votes)they have cast. We must no forget to consider the possibility that a permanent member cast no vetoes. PART D: Storage(20points); (1)(5points)Assume you have a buffer pool with five buffer frames. Five an example set of calls to Pin(pid)and Unpin(pid)such that the final state of the buffer pool is different for LRU versus CLOCK. You can assume that you can assume that your database has 20 pages with page ids 1,2,...,20. An example (incorrect)sequence is: Pin(1),Pin(2),Pin(3),Pin(4),Pin(5),Unpin(1),Pin(6), A RAID 50(RAID 5+0) combines the block-level striping with distributed parity of RAID 5,with the straght block-level striping of RAID 0. Below is an example of RAID 50. RAID 50 Disk1 Disk2 Disk3 Disk4 Disk5 Disk6 Blick1 Block2 Parity1-2 Block3 Block4 Parity3-4 BLock5 Parity5-6 Block6 Block7 Parity7-8 Block8 Parity9-10 Block9 Block10 Parity11-12 Block11 Block12 To see how RAID 50 differs from RAID 5, below is an example of RAID 5 RAID5 Disk1 Disk2 Disk3 Disk4 Disk5 Block1 Block2 Block3 Block4 Parity1-4 Block5 Block6 Block7 Parity5-8 Block8 Block9 Block10 Parity9-12 Block11 Block12 Block13 Parity13-16 Block14 Block15 Block16 Parity17-20 Block17 Block18 Block19 Block20 (2)(10points)What are (if any) the read/write performance improvements of RAID 50 over RAID 5? (3)(5points) What is (if any) the reliability improvement of RAID 50 over RAID 5? -- --

※ 發信站: 批踢踢實業坊(ptt.cc) ◆ From: 140.112.30.98