有關資料庫 SQL 與 functional dependencis, lossless join |
尚未結案
|
little girl
一般會員 發表:1 回覆:0 積分:0 註冊:2005-05-12 發送簡訊給我 |
請各位高手救救我吧
小女子不勝感激阿 A. Given universal schema R(A,B,C,D,E,F,G,H,I} and a set of functional dependencies as follows:
AB ->? CD
A ->? E
B ->? FH
C ->? G
D ->? B
G ->? C
H ->? I
a. What is the key for the universal schema R?
b. What is the highest normal form of the universal schema R? Please explain.
c. If the universal schema R is not in 3NF (the third normal form), please make lossless join decomposition of schema R into a set of 3NF schemas with the property of functional dependency preservation.
d. List all the keys (include candidate keys) in all the schemas (tables) achieved in c.
e. List all the foreign keys in each schema (table) if any. ()
f. Prove that the decomposition in c. is lossless join decomposition.
g. Is the decomposition you have created a BCNF decomposition? (That is, is each of the schemas (tables) achieved in c. in BCNF? B. Given the database schema on the relation as follows: LANCAP(NAME, LANGUAGE) The schema specifies the languages a person speaks. The data set is given as follows: LANCAP table data
-----------------
NAME LANGUAGE
----------------
Brenda French
Brenda English
Brenda Spanish
Joe English
Kent English
Lujack French
Lujack German
Lujack English
Lujack Spanish
Mary German
Mary English
Mary French
Melanie French
Melanie English
Richard German
Richard Spanish
Richard English
Richard French 1. Please give the results for each of the following queries based on the given data table a. List the language(s) that is(are) spoken by everyone.
b. List the person (people) who only speaks (speak) English.
c. List the person (people) who speaks (speak) all of the languages Brenda speaks. 2. Please write the following queries in SQL.
(You are not supposed to use any aggregation functions such as count(), sum(), etc.; you are assumed that you do not know what languages people speak in the database table when you query the database table; you are assumed that you do not know anyone’s name in the database table unless you are explicitly told in a given query; you are assumed that you do not know what languages Brenda speaks when you query the database table). a. List the language(s) that is(are) spoken by everyone.
b. List the person (people) who only speaks (speak) English.
c. List the person (people) who speaks (speak) all of the languages Brenda speaks.
|
本站聲明 |
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |