Test 2 - Databases | Computer Science(CS)

The query selects all attributes of r. Since we have distinct in query, result can be equal to r only if r doesn’t have duplicates.If we do not give any attribute on which we want to join two tables, then the queries like above become equivalent to Cartesian product. Cartisian product of two sets will be empty if any of the two sets is empty. So, s should have atleast one record to get all rows of r.

The database contains the adjacency list of the graph. So relation algebra will face problems while calculating the length of cycle. The query would execute in one tuple only.

The boundary cases are when either all the tuples of Enroll table belong to one roll number, so there can be at most 8 roll number and course number combinations or the other case is when all the tuples belong to different roll no. this also has 8 tuples. So (8,8) = (max,min)

BCNF is a stricter normal form than 3NF. So if we check database for BCNF, then it will definitely be in 3NF.

Name, course no. $\rightarrow$ grade
Roll no. course no. $\rightarrow$ grade
Name, Roll no are candidate keys grade depend upon both super keys.
So it is 2NF , non key attribute grade depend upon super keys fully.
But not in 3NF , since grade is not fully dependent upon all candidate keys.

This query first computes join where marks are less than or equal to marks are less than or equal to max marks, & then selects the name of all the girl students with more marks than all the boys students.

(1) BCNF stricter than 3NF is true.
(2) True, dependency preserving lossless decomposition into 3NF is always possible by removing
transitive functional dependences.
(3) False, lossless dependency preserving decomposition is not possible in all the cases, when all non- 
trivial functional dependencies are not dependent upon super keys.
(4) Certainly relation with two attributes is in BCNF, both will consist of super key.

A functional dependency (FD) is a constraint between two sets of attributes in a relation from a database. A FD X->Y require that the value of X uniquely determines the value of Y where X and Y are set of attributes. FD is a generalization of the notion of a key.Given that X, Y, and Z are sets of attributes in a relation R, one can derive several properties of functional dependencies. Among the most important are Armstrong’s axioms, which are used in database normalization:

• Subset Property (Axiom of Reflexivity): If Y is a subset of X, then X ? Y
• Augmentation (Axiom of Augmentation): If X -> Y, then XZ -> YZ
• Transitivity (Axiom of Transitivity): If X -> Y and Y -> Z, then X -> Z
  From these rules, we can derive these secondary rules:
• Union: If X -> Y and X -> Z, then X -> YZ
• Decomposition: If X -> YZ, then X -> Y and X -> Z
• Pseudotransitivity: If X -> Y and YZ -> W, then XZ -> W

In the above question, Y uniquely determines X and Z, for a given value of Y you can easily find out values of X and Z.
So, Y -> X and Y -> Z hold for above schema.
From rule of augmentation we can say YZ->X. If we understand the notion of FD, we don’t need to apply axioms to find out which option is true, just by looking at the schema and options we can say that (2) is true.

In option (3)    is not safe since no criteria for selection of tuple has been given.

On delete cascade says that deletion of a primary key value should delete its all foreign key references. So in (2,4) 2 is primary key so tuples (5,2) & (7,2) should be deleted, but in (5,2) 5 is also a key so (9,5) also deleted cascade.

A is said to be functionally determined by B. A has the same values as that of B for every corresponding tuple in given the database,

Here if A is 1, then B is necessarily 1 and if A is 2, then B is 3.
So, B is functionally determined by A.
But this relationship is not true for any other pair.

Expressive power is the capacity of formal query languages to express various query statements, so relational algebra is as powerful as relational calculus only if calculus is safe relational calculus.

B+ tree's each node size is kept almost the same to the block size of the system. This causes record data transfer from disk to memory one block at a time.

“Select average (salary) from employee” selects average salary of the company. So the query returns the names of all the male employees of all the departments whose average salary is more than the average salary in the company.

R(A,B,C,D)
In options (1) & (2) there exists transitive functional dependency, so
incorrect for BCNF.
In option (3)
AB $\rightarrow$ C C $\rightarrow$ AD
So        AB $\rightarrow$ AD
B $\rightarrow$ D
In option (4) all the attributes depend on the same key in BCNF.

BCNF (Boyce Cold Normal Form) since R is decomposed in BCNF then no redundancy in the resulting set may occur and all transitive dependencies are removed.

This SQL query selects the titles of the five most expensive books by comparing with each other.

The six basic operators of relational algebra are the selection(σ ), the projection(π), the Cartesian product (x) (also called the cross product or cross join), the set union (U), the set difference (-), and the rename (p). These six operators are fundamental in the sense that none of them can be omitted without losing expressive power. Many other operators have been defined in terms of these six. Among the most important are set intersection, division, and the natural join, but aggregation is not possible with these basic relational algebra operations. So, we cannot run sum of all employees’ salaries with the six operations.