数据库模拟试题及答案(英文)(共7页).doc

精选优质文档-倾情为你奉上Exam of Database Technology & Applications 1. 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 FDs: R is (A, B, C, D, E, F,G) with FDs A-> B, B-> 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.5. Explain the ACID properties.6. Notown Records has decided to store information about musicians who perform on its albums (as well as other company data) in a database. The company has chosen to hire you as a database designer.l Each musician that records at Notown has an SSN, a name, an address, and a phone number. l 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).l Each album recorded on the Notown label has a title, a copyright date, a format (e.g.,CD or MC), and an album identifier.l Each song recorded at Notown has a title and an author.l Each musician may play several instruments, and a given instrument may be played by several musicians.l Each album has a number of songs on it, but no song may appear on more than one album.l Each song is performed by one or more musicians, and a musician may perform a number of songs.l Each album has exactly one musician who acts as its producer. A musician may produce several albums, of course.1) Defining the completed E-R diagram.2) Defining information for each relation.7. Consider the following relational 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 values associated with each attribute and integrity constraints. sidINTNOT NULLPRIMARY KEYsnameVARCHAR(10)NOT NULLageINTNULL0<age<1002) Change the attribute sname VARCHAR(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 a boat on 2010-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_computer 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.AgeSalarySubscriptionyouthhighnoyouthhighnomiddle_agedhighyesseniormediumyesseniorlowyesseniorlownomiddle_agedlowyesyouthmediumnoyouthlowyesseniormediumyesyouthmediumyesmiddle_agedmediumyesmiddle_agedhighyesseniormediumnoFigure 1 The Buys_computer RelationANSWER1. The three levels are physical level, logical level and 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 two mappings: 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 accordingly so that the view from the conceptual level remains constant. It is this mapping that provides physical data independence for the database.The external/conceptual view lies between the external and conceptual levels. If the structure of the database at the conceptual level is changed, then the external/conceptual mapping 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 integrity 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 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 other table has a 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) R1NF3) R1(A,B,E) ,R2(B,C,F),R3(C,D,G)5. Atomicity: This property guarantees that a set of records 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 from transaction atomicity, isolation, and transaction consistency.Isolation: Although multiple transactions may execute concurrently, each transaction must be unaware of other concurrently executing transactions. Intermediate transaction results must be hidden from other concurrently executed transactions. Durability: After a transaction completes successfully, the changes it has made to the database persist, even if there are system failures.6.1)pruducenmm1mm1musicianinstrumentalbumsongperformplaycontainn2)musician (SSN, m_name, address, phone number.)instrument ( i_name, musical key)album (a_title, 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 FROM Sailors WHERE age<184) SELECT sname FROM Sailors S JOIN Reserves R ON S.sid=R.sidWHERE rday='2010-1-1'5) SELECT sname FROM Sailors S JOIN Reserves R ON S.sid=R.sid JOIN Boats B ON B.bid=R.bid WHERE color='red'6) SELECT sname FROM Sailors S JOIN Reserves R ON S.sid=R.sid 7) SELECT sname FROM Sailors S1 JOIN Reserves R1 ON S1.sid=R1.sid JOIN Boats B1 ON B1.bid=R1.bid WHERE B1.color='red' AND sid IN (SELECT sid FROM Sailors S2 JOIN Reserves R2 ON S2.sid=R2.sid JOIN Boats B2 ON B2.bid=R2.bid WHERE B2.color='green' )8) SELECT sname FROM Sailors SWHERE NOT EXISTS (SELECT * FROM Boats B WHERE NOT EXISTS(SELECT * FROM Reserves R WHERE R.bid=B.bid AND R.sid=S.sid)8.Info(D)= log2()log2()=0.940Infoage(D)= ×(log2()log2() + ×（log2()log2()）+×(log2()log2()=0.694Gain（age）= Info(D)Infoage(D)=0.246Infosalary(D)= ×(log2()log2() ) + × (log2()log2() +×(log2()log2()=0.911Gain（salary）= Info(D)Infosalary(D)=0.029seniormiddle_agedyouthagesalarysalaryclassclasshighhighmediummediumlownononoyesnomediummediummediumlowlowyesyesnoyesnosalaryclasshighhighmediumlowyesyesyesyes专心-专注-专业