闪电代写 -代写CS作业_CS代写_Finance代写_Economic代写_Statistics代写_代码代做_IT代写_加急帮助

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

End of Year Examinations 2019

COSC265

Relational Database Systems

Short questions (40 marks)

Question 1 [6 marks] Discuss the difference between correlated and non-correlated nested queries in the WHERE clause of the SQL SELECT statement, and explain what happens when such nested queries are executed.

Question 2 [4 marks] Discuss what happens within the database system when a view is defined, and when a view is used in a query.

Question 3 [3marks] Define the entity integrity rule of the relational data model. State how it is implemented in SQL.

Question 4 [5 marks] Define a transaction, and explain the ACID properties.

Question 5 [5 marks] What are the factors contributing to query cost?

Question 6 [4 marks] Explain the difference between a data manipulation language and a data definition language. Give two examples of statements that belong to each language.

Question 7 [4 marks] Define primary keys, candidate keys and foreign keys. How do we represent

them in ER schemas? What constructs of SQL allow us to declare such keys? Question 8 [2 marks] What does the phrase “union compatible relations” mean?

Question 9 [3 marks] List three possible uses of triggers, and describe what the trigger will achieve in each case.

Question 10 [4 marks] Explain two significant advantages of storing data in databases versus storing data in files.

[30 marks for the whole question] The following table is given:

ORDERS

OrderNo	OrderDate	CustomerNo	PartNo	Quantity	Price
51608	10/10/2019	126	CD33	5	$86.99
51614	1/10/2019	334	FS11	25	$10.95
51608	10/10/2019	126	TR40	10	$13.99
51613	10/10/2019	127	KL78	3	$104.95
51610	8/10/2019	586	TR40	12	$124.95
51623	3/10/2019	126	NL89	4	$36.95
51625	4/10/2019	792	MT03	3	$45.79
51534	5/9/2019	126	CD33	4	$73.99

Question 11 [8 marks] Identify the functional dependencies that exist in this table.

Question 12 [3 marks] Identify all candidate keys for this relation. Justify your answer.

Question 13 [4 marks] Identify the highest normal form that this relation satisfies. Justify your answer.

Question 14 [8 marks] Explain what kind of anomalies can happen when the table is updated.

Question 15 [7 marks] If R is not in BCNF, transform it into a set of BCNF relations. Otherwise, justify why R is already in BCNF.

[20 marks for the whole question] A database hold information about suppliers, products and supplies organized into three tables:

SUPPLIER(SuppNo, SuppName, Address, City)

PRODUCT(ProdNo, ProdName, Type, Available_Quantity) SUPPLIES(Supplier, Product, Purchase_Price, Delivery_Date)

Question 16 [3 marks] Identify foreign keys in the given tables.

Question 17 [3 marks] Write the SQL statement to implement the SUPPLIES relation.

[4 marks] For the following statements, specify whether they are true or false. If a statement is false, explain why it is false.

Question 18 A supplier can only supply one type of product.

Question 19 This database schema allows one type of product to be supplied by different suppliers. Question 20 A supplier can supply more than one product.

Question 21 A product can have different purchase prices.

[10 marks] The following SQL statement is given:

SELECT SuppName, ProdName

FROM Supplier, Product, Supplies

WHERE Supplier=SuppNo and Product = ProdName and Delivery_Date

between ’01-OCT-2019’ and ’1-NOV-2019’;

Question 22 [1 mark] Fix a mistake in this statement.

Question 23 [6 marks] Write two relational algebra queries that achieve the same result as the corrected statement. One query should correspond to the canonical tree, while the other should be more efficient.

Question 24 [3 marks] Explain why the canonical tree is less efficient to execute than a non- canonical tree.

Question 25 [4 marks] A particular B+-tree index of order 50 has 4 levels, including the level containing pointers into the data file. What is the maximum number of disk blocks that would have to be read to get a record from the data file?

Question 26 [6 marks] If a data file has 1 billion (1,000,000,000) records and a new B+-index is created with order 50, how many levels are necessary for this B+tree index?