[-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-]Q1:
Consider the following relational design. Note that we give some functional
dependencies explicitly, but the design also obeys all dependencies that logically follow from the ones we state. Consider the relational design with 4 relations given as follows:
R1(A,B,C,D) with dependencies A → B, C and A → D;
R2(B,E,F) with dependencies B → F and E → F;
R3(G,H,I) with no dependencies;
R4(F,G,J) with dependencies F → G, also G → J, and G → F.For each relation, indicate all possible candidate keys, choose one primary key, and say whether or not the relation is in BCNF. You must explain your answer; also, for any relation that is not in BCNF, give a decomposition that is in BCNF.[-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-]Q2:
Consider the relational schema
author ( authorname, citizenship, birthyear)
book(isbn, title, authorname)
topic(isbn, subject)
branch(libname, city)
instock(isbn, libname, quantity)Q2a) Give a relational algebra expression to compute the answer to the following
instruction: Give the birthyear of every Australian author who has written a book which is in stock in a library in Sydney.Q2b) Write SQL to produce the views and permissions so that user Andrews can
see information about the books’ isbn, title, authorname and authors’ citizenship and birthyear, but only for books held in ‘Fisher’ library (and they can’t see information about books that are not held in that library). This user can also change the quantity of such books that are instock in Fisher library, but they can’t change any other information in the database.[-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-]Q3:
This question refers to a database containing (among others) two relations
Rel1(A,B,C) and Rel2(B,D). Each A field occupies 4 bytes, each B field occupies 8 bytes, each C field occupies 8 bytes, and each D field occupies 12 bytes. Rel1 contains 20,000 records and Rel2 contains 10,000 records. The files structures are as follows: Rel1 has a primary B+-tree index on A, with a dense B+-tree secondary index on B. Rel2 is an unstructured heap with no index. Assume that a block contains 1024 bytes, of which 24 bytes are used for header information.Q3a) Calculate the space required for each relation (including the indices).Q3b) Calculate the number of blocks of I/O needed to find the records of Rel1 with
a given value for the attribute B.[-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-]Q4:
Suppose our database contains three data items A, B and C each containing a
real number. Initially A=0, B=4, C=6. There are two transactions. T1 is Read(A); Read(B); Read(C); C := A+B+2; Write(C). T2 is Read(A); Read(B); temp := A; A := B; B := temp; Write(A); Write(B). The data is supposed to satisfy the undeclared integrity constraint C≥A+B. Note that each transaction, when run by itself, takes the database from a state where this constraint holds, to another state where it still holds.Q4a) Suppose no locking is done. Give an interleaved schedule that violates the integrity constraint, and state what is the final value of the data items.Q4b) Draw the serialization graph for the schedule you gave in Q4a) above.Q4c) Suppose that each transaction uses strict two-phase locking. Place lock and unlock operations in the correct places in the schedule you gave for 45a) above. Show the first operation where the schedule will be blocked, when a transaction must wait for a lock it requests, and say which transaction holds the lock at that time.
Consider the following relational design. Note that we give some functional
dependencies explicitly, but the design also obeys all dependencies that logically follow from the ones we state. Consider the relational design with 4 relations given as follows:
R1(A,B,C,D) with dependencies A → B, C and A → D;
R2(B,E,F) with dependencies B → F and E → F;
R3(G,H,I) with no dependencies;
R4(F,G,J) with dependencies F → G, also G → J, and G → F.For each relation, indicate all possible candidate keys, choose one primary key, and say whether or not the relation is in BCNF. You must explain your answer; also, for any relation that is not in BCNF, give a decomposition that is in BCNF.[-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-]Q2:
Consider the relational schema
author ( authorname, citizenship, birthyear)
book(isbn, title, authorname)
topic(isbn, subject)
branch(libname, city)
instock(isbn, libname, quantity)Q2a) Give a relational algebra expression to compute the answer to the following
instruction: Give the birthyear of every Australian author who has written a book which is in stock in a library in Sydney.Q2b) Write SQL to produce the views and permissions so that user Andrews can
see information about the books’ isbn, title, authorname and authors’ citizenship and birthyear, but only for books held in ‘Fisher’ library (and they can’t see information about books that are not held in that library). This user can also change the quantity of such books that are instock in Fisher library, but they can’t change any other information in the database.[-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-]Q3:
This question refers to a database containing (among others) two relations
Rel1(A,B,C) and Rel2(B,D). Each A field occupies 4 bytes, each B field occupies 8 bytes, each C field occupies 8 bytes, and each D field occupies 12 bytes. Rel1 contains 20,000 records and Rel2 contains 10,000 records. The files structures are as follows: Rel1 has a primary B+-tree index on A, with a dense B+-tree secondary index on B. Rel2 is an unstructured heap with no index. Assume that a block contains 1024 bytes, of which 24 bytes are used for header information.Q3a) Calculate the space required for each relation (including the indices).Q3b) Calculate the number of blocks of I/O needed to find the records of Rel1 with
a given value for the attribute B.[-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-]Q4:
Suppose our database contains three data items A, B and C each containing a
real number. Initially A=0, B=4, C=6. There are two transactions. T1 is Read(A); Read(B); Read(C); C := A+B+2; Write(C). T2 is Read(A); Read(B); temp := A; A := B; B := temp; Write(A); Write(B). The data is supposed to satisfy the undeclared integrity constraint C≥A+B. Note that each transaction, when run by itself, takes the database from a state where this constraint holds, to another state where it still holds.Q4a) Suppose no locking is done. Give an interleaved schedule that violates the integrity constraint, and state what is the final value of the data items.Q4b) Draw the serialization graph for the schedule you gave in Q4a) above.Q4c) Suppose that each transaction uses strict two-phase locking. Place lock and unlock operations in the correct places in the schedule you gave for 45a) above. Show the first operation where the schedule will be blocked, when a transaction must wait for a lock it requests, and say which transaction holds the lock at that time.
解决方案 »
- 这句sql语句怎么错了,在线等答案
- 存储过程中怎样删除临时表中的列
- 查询记录的时候,如何自动增加一个编号呢
- 对象名 ‘xxxx’ 无效的问题
- 如何设置master庫sysprocesses表program_name的值?
- 关于将SQL Server CE 安装到Pocket PC的问题
- 如何知道日志1天之内增加过快的原因
- 只有MSDBData.mdf/MSDBLog.ldf怎么恢复msdb数据库?
- 为什么说对象无效?
- 一个让我苦恼不已的问题:用VB写成的数据库程序,在我的机器里运行很好,但是在客户机器里却出现不支持ODBC属性的错误,那位大虾遇到过类似的情况?又是如何解决的?哎,真是郁闷死了
- 我把数据库表中的数据误删了,有什么办法可以找回来
- 如何在 restore database时使用系统当前的路径?
候选码 A,bcnf
R2(B,E,F) with dependencies B → F and E → F;
候选码 B,E,不是bcnf,改为R21(B,F),R22(E,F)
R3(G,H,I) with no dependencies;
候选码 G,H,I bcnf
R4(F,G,J) with dependencies F → G, also G → J, and G → F.
候选码G或F,是bcnf
Consider the relational schema
author ( authorname, citizenship, birthyear)
book(isbn, title, authorname)
topic(isbn, subject)
branch(libname, city)
instock(isbn, libname, quantity) Q2a) Give a relational algebra expression to compute the answer to the following
instruction: Give the birthyear of every Australian author who has written a book which is in stock in a library in Sydney.
select birthyear,authorname from author
left join book
on author.authorname = book.authorname
left join instock
on book.isbn = instock.isbn
left join branch
on instock.libname = branch.libname
where branch.city = 'Sydney'Q2b) Write SQL to produce the views and permissions so that user Andrews can
see information about the books’ isbn, title, authorname and authors’ citizenship and birthyear, but only for books held in ‘Fisher’ library (and they can’t see information about books that are not held in that library). This user can also change the quantity of such books that are instock in Fisher library, but they can’t change any other information in the database. 给用户Andres授予查看BOOK'ISBN,TITLE,AUTHORNAME AND AUTHORS'CITIZENSHIP,BIRTHYEAR且BOOK在FISHER LIBRARY,此用户还可以改变fisher library的库存量。
grant select on book,authors to Anders
grant select,alter on branch [libname,quantity ]where branch.libname=fisher library to Anders