数据库模拟试题及答案（英⽂）
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 an d 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.
2) Change the attribute sname V ARCHAR(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 comp
uter. We want to use this data to construct a
ANSWER
1. 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 seco
ndary 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 R
2) R∈1NF
3) 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)
2)
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 V ARCHAR(12)
3) DELETE FROM Sailors WHERE age<18
4) SELECT sname FROM Sailors S JOIN Reserves R ON S.sid=R.sid WHERE 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 lor='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 lor='green' )
8) SELECT sname FROM Sailors S WHERE 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)= —
149log 2(149)—145log 2(145)=0.940 Info age (D)= 145×(—52log 2(52)—53log 2(53)) + 144×（—44log 2(44)—40log 2(40 )）+
145×(—53log 2(53)—52log 2(5
2
))=0.694 Gain （age ）= Info(D)—Info age (D)=0.246 Info salary (D)=
154 ×(—43log 2(43)—41log 2(41) ) + 156× (—64log 2(64)—62log 2(62)) + 154×(—42log 2(4
2
)—42log 2(42))=0.911 Gain （salary ）= Info(D)—Info salary (D)=0.029

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。