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

PAGE PAGE 7 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 FD’s: R is (A, B, C, D, E, F,G) with FD’s A- B, B- C, A- E, CD-G. 1) Identify the candidate key(s) for R. 2) Identify the best normal form that R satisfies 3) 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. Each musician that records at Notown has an SSN, a name, an address, and a phone 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). 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 number of songs on it, but no song may appear on more than one 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 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. sid INT NOT NULL PRIMARY KEY sname VARCHAR(10) NOT NULL age INT NULL 0age100 2) Change the attribute sname VARCHAR