數(shù)據(jù)庫模擬試題及答案(英文)

1、Exam of Database Technology & Applications1. Describe the three levels and data independence.2. What are key constraints and foreign constraints?3. Explain LEFT JOIN, OUTER JOIN and INNER JOIN.4. For the following relation schema and sets of FD s: R is (A, B, C, D,GE), wFith FD sA-> B, B->

2、 C, A-> E, CD->G.1) Identify the candidate key(s) for R.2) Identify the best normal form that R satisfies3) Decompose it in 3NF if necessary.75. Explain the ACID properties.6. Notown Records has decided to store information about musicians who perform on its albums (as well as other company da

3、ta) in a database. The company has chosen to hire you as a database designer.Each musician that records at Notown has an SSN, a name, an address, and aphone number.Each instrument used in songs recorded at Notown has a name (e.g., guitar, synthesizer,flute) and a musical key (e.g., C, B-flat, E-flat

4、).Each album recorded on the Notown label has a title, a copyright date, a format(e.g.,CD or MC), and an album identifier.Each song recorded at Notown has a title and an author.Each musician may play several instruments, and a given instrument may be played by several musicians.Each album has a numb

5、er of songs on it, but no song may appear on more thanone album.Each song is performed by one or more musicians, and a musician may perform a number of songs.Each album has exactly one musician who acts as its producer. A musician mayproduce several albums, of course.1) Defining the completed E-R di

6、agram.2) Defining information for each relation.7. Consider the followingrelational schema and give T-SQL expressions for the following queries.Sailors(sid, sname, age) Boats(bid, bname, color) Reservers(sid, bid , day)1）Create the table Sailors (sid, sname , age). It includes the domain of valuesas

7、sociated with each attribute and integrity constraints.sidsnameINTVARCHAR(10)NOT NULLNOT NULLPRIMARY KEYageINTNULL0<age<1002) Change the attribute sname AVRCHAR(12).3) Delete all tuples in the Sailors relation for sailors whose age is less than 18.4) Find the names of sailors who have reserved

8、 a boat on2010-1-1.5) Find the names of sailors who have reserved a red boat.6) Find the names of sailors who have reserved at least one boat.7) Find the sid of sailors who have reserved a red boat and a green boat.8) Find the names of sailors who have reserved all boats.8. Consider the Buys_compute

9、r Relation shown in Figure 1. The first four columns show the age and salary of a potential customer and the Buys_computer column shows whether the person buys a computer. We want to use this data to construct a decision tree that helps predict whether a person will buy a computer.AgeSalarySubscript

10、ionyouthhighnoyouthhighnomiddle_agedhighyesseniormediumyesseniorlowyesseniorlownomiddle_agedlowyesyouthmediumnoyouthlowyesseniormediumyesyouthmediumyesmiddle_agedmediumyesmiddle_agedhighyesseniormediumnoFigure 1 The Buys_computer RelationANSWER1. The three levels are physical level, logical level an

11、d view level. Physical level describes all relations that are stored in the database. Logical level summarizes how the relations are actually stored on secondary storage devices. Each view level consists of a collection of one or more views and relations from the conceptual level.There are actually

12、twomappings: the conceptual/internal mapping and the external/conceptual mapping. The conceptual/internal mapping lies between the conceptual and internal levels. If the structure of the stored database is changed, then the conceptual/ internal mapping must also be changed accordinglyso that the vie

13、w fromthe conceptual level remains constant. Itis thismapping that provides physical data independence for the database.The external/conceptual view lies between the external and conceptual levels. If the structure ofthe database at the conceptual level is changed, then the external/conceptual mappi

14、ng must change accordingly so the view from the external level remains constant. It is this mapping that provides logical data independence for the database.2. Primary key constraints assure that the keys of any two records are not same in a table. The primary key constraints enforce the entity inte

15、grity of the table.Foreign key constraints control the data that can be stored in the foreign key table, it also controls changes to data in the primary key table. This constraint enforces referential integrity by ensuring that changes cannot be made to data in the primary key table if those changes

16、 invalidate the link to data in the foreign key table.3. The LEFT OUTER JOIN includes all rows in the left table in the results, whether or not there is a match on the join column in the right table.FULL OUTER JOIN includes all rows from both tables, regardless of whether or not the othertable has a

17、 matching value.This INNER JOIN is known as an equi-join. It returns all the columns in both tables, and returns only the rows for which there is an equal value in the join column.4.1） (A,D) is the primary key for R2) R 1NF3) R1(A,B,E) ,R2(B,C,F),R3(C,D,G)5. Atomicity:This property guarantees that a

18、 set ofrecords that are part of a transaction is indivisible. Thus either all operations of the transaction are properly reflected in the database or none are.Consistency: Database consistency is the property that every transaction sees a consistent database instance. Database consistency follows fr

19、om transaction atomicity, isolation, and transaction consistency.Isolation: Although multiple transactions may execute concurrently, each transaction must beunaware of other concurrently executing transactions. Intermediate transaction results must be hidden from other concurrently executed transact

20、ions.Durability: After a transaction completes successfully, the changes it has made to the database persist, even if there are system failures.6.1)pruducemalbum11musiciancontainmmnmplayperformsongninstrument2)musician (SSN, m_name, address, phone number.) instrument ( i_name, musical key)album (a_t

21、itle, copyright date, format , album identifier, SSN)song ( s_title, author, a_title) play(SSN,i_name) produce(SSN, s_title)7.1) CREATE TABLE Sailors( sid INT PRIMARY KEY,sname VARCHAR(10) NOT NULL,age INT CHECK( age BETWWEN 0 AND 100)2) ALTER TABLE Sailors ALTER COLUMN sname VARCHAR(12)3) DELETE FR

22、OM Sailors WHERE age<184) SELECT sname FROM Sailors S JOIN Reserves R ON S.sid=R.sid WHERE rday='2010-1-1'5) SELECT snameFROM Sailors S JOIN Reserves R ON S.sid=R.sid JOIN Boats B ON B.bid=R.bidWHERE color='red'6) SELECT snameFROM Sailors S JOIN Reserves R ON S.sid=R.sid7) SELECT snameFROM Sailors S1 JOIN Reserves R1 ON S1.sid=R1.sid JOIN Boats B1 ON B1.bid=R1.bidWHERE B1.color='red' AND sid IN(SELECT sid FROM Sailors S2 JOIN Reserves R2