Database Management System | Subject-Wise Solved Questions

Given
File contains 1 million records
Order of the B⁺ tree = Number of ptrs per node = p = 100
Maximum number of nodes = ?

So, Consider Minimum Fill factor in order to find Maximum number of nodes to be accessed
Minimum ptrs per node = ⌈p/2⌉ = 50
Number of Node in last level = 10⁶/50 = 2 * 10⁴
Number of  Nodes in 2^nd last level= 2*10⁴/50 =400
Number of Nodes in 3^rd last level= 400/50 =8
Number of Nodes in 4^th last level= 8/50 =1
The maximum number of nodes to be accessed = Number of B+ tree levels = 4

In a Dense Index an index entry appears for every search key value in the file

option(A) : Primary index is maintain for the anchor value of block i,e 1 key value per block maintained in Index file.

option(B) : Clustering index is basically mixed sort of Indexing i,e Dense as it is maintained for unique value (key) and Sparse as it is not maintained for every value.

option(C) : Secondary index is usually dense because Secondary Index is maintained for every key value.

option(D) : There will be repetition of values in Non-Key, So there will be index only for 1 of the repeated values. Since we left some of the values so it is not dense index

-If any changes occur in dominant entity then they are also reflected in sub-ordinate entity but not the vice versa.
-When a tuple from the dominant is deleted then the related tuple from the sub-ordinate also deleted

It is regarded as a form of automatic inspection machine problem. The key is to analyze exactly what each mode on behalf of nature.
<Letter> represents a single letter;
<digit> represent a single digit;
<pairdig> two <digit>, or a recursive <pairdig> and two <digit>, in fact, indicates the even number <digit>, i.e. even figures ;
Similarly, <pairlet> is an even number of letters. So <word> may be: a letter, letters odd or even number of digits and a letter. So the second and third are correct

CANDIDATE KEY is a set of attributes that uniquely identify tuples in a table. Candidate Key is a super key with no repeated attributes.

• CANDIDATE KEY must contain unique values
• CANDIDATE KEY may have multiple attributes
• CANDIDATE KEY Must not contain null values
• CANDIDATE KEY should contain minimum fields to ensure uniqueness
• CANDIDATE KEY Uniquely identify each record in a table

option(A) : In the given Table, Last Name cannot be the Candidate key because as smith value are repeated.

option(B) : Room having duplicate values. So, we can't say that Room is Candidate key. Example 33 value is repeated

option(C) : Shift also having duplicate values as morning is repeated.

option(D) : "Room+shift" having no duplicate values. Every tuple is unique. we can uniquely identify any tuple in a table using Room+shift column

So, option(D) is candidate key

Candidate Key for the above relation is ab.

Checking the FD's :
a→c  (Prime derives Non-Prime.)
b→d (Prime derives Non-Prime.)

Prime attribute { a,b}
non prime attribute {c,d}.

Since all a,b,c,d are atomic so the relation is in 1NF.

The relation will be in 2NF if every Non Prime attribute is fully functional dependent on KEY.

Here {c,d } are not fully functionally dependent, rather they are partially dependent so, it is not in 2NF.

Hence, this relation is in 1NF but not in 2NF

Students : (Roll number, Name, Date of birth)
Courses: (Course number, Course name, instructor)
Grades: (Roll number, Course number, Grade)

Distinct name is to be selected, where
1. Name of the student is selected on the basis of the grade.
2. Instructor of the course in Sriram
3. Courses selected on the basis of grade.
4. Grade should be A.

The Query results a names of the students who have got an A grade in at least one of the courses taught by Sriram.

-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 S is empty RXS becomes empty, so S must be non empty.

Option(A):R has no duplicates and S is non-empty
Incase If R has duplicates in that case due to DISTINC keyword in query will eliminate duplicates in FINAL result and the results in query !=R
So, R cannot have duplicate values
Incase Relation "S" is Empty then RXS becomes empty So, Relation "S" must be non-empty

Option(B): R and S have no duplicates
Assume Relation ''S' is Empty which has no duplicate values then RXS becomes empty So, Relation "S" must be non-empty

Option(C) : S has no duplicates and R is non-empty
S has no duplicate means we can relation "S" as Empty which is discussed in option(B)

Option(D) : R and S have the same number of tuples
Incase If R has duplicates in that case due to DISTINC keyword in query will eliminate duplicates in FINAL result So results in query !=R which is discussed in option(A)

Immunity is when data at one layer is changed, it does not affect the data at another level.
Physical data independence  :- if changes are made in the physical storage of schema then it will not affect the logical schema of the database.
Logical data independence :- if any changes are made in the conceptual schema then it will not affect external schema or the view level of the database.

The term full functional dependency (FFD) is used to indicate the minimum set of attributes in of a functional dependency (FD). In other words, the set of attributes X will be functionally dependent on the set of attributes Y if the following conditions are satisfied:

• X is functionally dependent on Y and
• X is not functionally dependent on any subset of Y.

Not allowed under basic timestamp protocols because T1 is rolled back

Data Control Language (DCL) is a subset of the SQL that allows database administrators to configure security access to relational databases.
It consists of only three commands: GRANT, REVOKE, and DENY.
GRANT  :It allow specified users to perform specified tasks.
REVOKE : Revoke to cancel previously granted or denied permissions.

Aggregate functions in SQL:
1) MIN
2) MAX
3) AVG
4) COUNT
5) SUM
Avg is an aggregate function returns the average of a group of elements.
Syntax:
SELECT AVG(column_name) FROM table_name;

• Schema is the physical arrangement of the data as it appears in the DBMS.
• Sub-schema is the logical view of the data as it appears to the application program.

• To extract a specified row in a table select operation is used
• Project operator is denoted by ∏ symbol and it is used to extract specified columns (or attributes) from a table (or relation).
• BY default project operation removes duplicate data

• We say A relation is in Boyce–Codd Normal Form (BCNF) if all attributes which are determinants are also candidate keys in every relation.
• Transformation into Boyce–Codd Normal Form (BCNF) deals with the problem of overlapping keys and there is no problem with 2 or more Candidate keys .

• A data dictionary is a file or a set of files that contains a database's metadata. The data dictionary contains records about other objects in the database, such as data ownership, data relationships to other objects, and other data.
• The data dictionary is a crucial component of any relational database.
• Data dictionary will identify
  -Field name
  -Field format
  -Field types

Time stamp – ordering concurrency protocol ensures both conflict serializability and freedom from deadlock.

• In Basic 2 phase locking there is a chance for deadlock and starvation but ensure serializability
• Strict 2PL  ensure serializability and recoverability but deadlock and starvation possible
• Conservative 2PL ensure serializability and no deadlock but starvation possible
• Time stamp ordering  ensure serializability and no deadlock and avoid starvation by using wait-die and wound -wait scheme

Natural Join joins two tables based on same attribute name and datatypes. The resulting table will contain all the attributes of both the table but keep only one copy of each common column.

For example consider the below two tables :

Student Table

Roll_No  Name
 1       Arya
 2       Bindu
 3       Cherry

Another table

Marks Table

Roll_No   Marks
 2         70
 3         50
 4         85

Query
SELECT * FROM Student S NATURAL JOIN Marks M;

Output

Roll_No    Name    Marks
 2         Bindu   70
 3         Cherry  50

 


Coming to the question : 

Given table
Overtime_allowance

Employee	Department	OT_allowance
RAMA	Mechanical	5000
GOPI	Electrical	2000
SINDHU	Computer	4000
MAHESH	Civil	1500

Given query : 
select count(*) from ((select Employee, Department from Overtime_allowance) as S natural join (select Department, OT_allowance from Overtime_allowance) as T);



Inner query will executed first
(select Employee, Department from Overtime_allowance) as S natural join (select Department, OT_allowance from Overtime_allowance) as T)

Table S                          Table T
Employee    Department      Department     OT_allowance
Rama        Mechanical      Mechanical      5000
Gopi        Electrical      Electrical      2000
Sindhu      Computer        Computer        4000
Mahesh      Civil           Civil           1500

Now when we apply natural join on S and T, it matches for the common attribute(Department) in both tables and outputs the common tuples.

Department	OT_allowance
Mechanical	5000
Electrical	2000
Computer	4000
Civil	1500

 
Final query : outer query will be executed now

select count(*) from ((select Employee, Department from Overtime_allowance) as S natural join (select Department, OT_allowance from Overtime_allowance) as T);

Output : 4
Natural join will return the table with 4 tuples.

Therefore, the correct option is (B)

-If all nodes are completely full then every node has n−1 keys.
-Insertion of new key is leading to insertion of new node at all 4 levels
-In worst case root node will also be broken into 2 parts.
-Maximum number of nodes that could be created are 5 because tree will be increased with 1 more level

• Join is a combination of a Cartesian product followed by a selection process.
• A Join statement is used to combine rows from 2 or more tables based on the selection of common field between them
• Different types of Joins are :
  -RIGHT JOIN
  -FULL JOIN
  -INNER JOIN
  -LEFT JOIN

Step 1 : Where S.age >= 18
All the tuples with age less than 18 will be eliminated.

Step 3 : GROUP BY S.rating
Group By the remaining tuples after eliminated in step1 will be grouped according to rating

After this, all the group-by tuples having their count more than 1 will be selected and their ratings and average age will be given as output.

• Best indexing mechanism appropriate for the given query is Bit-mapped index.
• To record gender(Male and Female) of customer we require only 2 bits 0 and 1
• we can implement it using Bit-mapped indexing.

Replace : Replaces all occurrences of a specified string value with another string value

Join :

• Join is a combination of a Cartesian product followed by a selection process.
• A Join statement is used to combine rows from 2 or more tables based on the selection of common field between them

UPDATE: Update a record in table

Change : There is no change command in SQL

File locking is a mechanism that restricts access to a computer file by allowing only one user or process access to the file at any specific time.

• A transaction log is a sequential record of all changes made to the database while the actual data is contained in a separate file.
• The transaction log contains enough information to undo all changes made to the data file as part of any individual transaction

• A FOREIGN KEY is a column or columns that references a column (most often the primary key) of another table
• The primary purpose of FOREIGN KEYs is to maintain Referential Integrity (RI).

Option A can be recovered
Option B is also fine as no write operation is involved.
Option C is a normal operation.
Option D Reading uncommitted data is called as dirty read. If transaction reads uncommitted data and commits before the transaction that writes data item leads to non-recoverable schedule if and if transaction that writes data item rolled back

"d" is the foreign key of S that refer to "a" primary key of R

• Insert into R will not cause any violation.
• Insert into S may cause violation if any value is inserted into d of S, which value is not in a of R.
• Delete from S will not cause any violation.
• Delete from R may cause violation because for the deleted entry in R there may be referenced entry in the relation S

The outer query selects all titles from book table. For each title the inner query will be evaluated. For every selected book, the subquery will return the count of books which are more expensive than the selected book. T.price > B.price is evaluated for every B.price.

• For the 1st book (B.price = 65) Inner query gives count =6 (75,85,95,105,115,125).
• For the 2nd book (B.price = 75) Inner query gives count =5 (85,95,105,115,125).
• For the 3rd book (B.price = 85) Inner query gives count =4 (95,105,115,125).
• For the 4th book (B.price = 95) Inner query gives count =3 (105,115,125).
• For the 5th book (B.price = 105) Inner query gives count =2 (115,125).
• For the 6th book (B.price = 115) Inner query gives count =1 (125).
• For the 7th book (B.price = 125) Inner query gives count =0.

Hence the entire query will list out the title of a book when the count is less than 5. Hence as shown above from 3rd book onwards the titles will be listed. GHI, JKL, MNO, PQR, STU will be listed
Source : BtechOnline

A logical schema describes what data are stored in the database, and what relationships exist among those data. The logical level thus describes the entire database in terms of a small number of relatively simple structures.

Goals for the design of the logical scheme include

• Avoiding data inconsistency
• Being able to construct query easily
• Being able to access data efficiently

Statement that is executed automatically by the system as a side effect of a modification of the database
A database Trigger is executed automatically in response to certain events on a particular table or view in a database.

Given
Block size = 1K bytes = 1024 bytes
Data record pointer r = 7 bytes
Value field V= 9 bytes
Block pointer P= 6 bytes
order of the leaf node = ?
Let order of leaf = m
r*m + V*m + p <= 1024
7m + 9m + 6 <= 1024
16m <= 1024-6
16m <= 1018
m =< 63

Primary Index(Sparse)
- It is defined as an ordered data file.
- The data file is ordered on a key field.
- The key field is generally the primary key of the relation.

Secondary Index(Dense)
- This index may be generated from a field which is a candidate key and has a unique value in every record, or a non-key with duplicate values.

Clustering Index(Sparse)
- This index is defined on an ordered data file.
- The data file is ordered on a non-key field.

Selection Sort is an in-place algorithm having minimum number of swaps.
It works on greedy approach and takes O(n) swaps to sort the array of n elements

Number of swaps : Worst case scenario

• Quick sort = O(n²)
• Insertion sort = О(n²)
• Selection sort = O(n)
• Heap sort = O(n logn)

Number of swaps require in Selection Sort :
Best Case : No swaps required as all elements are properly arranged
Worst Case : n-1 swaps required

There are no Loop's So it is conflict serializable
And Serial schedule will is T3-T1-T2

• Object Oriented Systems(OODBMS) provide support for maintaining several versions of the same object.
• An old version of an object in OODBMS that represents a tested and verified design should be retained until the new version is tested and verified.

Second normal form (2NF) is based on the concept of full functional dependency.

Functional Dependency:
The attribute B is fully functionally dependent on the attribute A if each value of A determines one and only one value of B.

Example: EMP_EID → EMP_NAME
In this case, the attribute EMP_EID is known as the determinant attribute and the attribute EMP_NAME is known as the dependent attribute.

Transitive, Augmentation and Reflexive
Armstrong's Axioms are most basic inference rules. These are 3 rules:

• Reflexivity: If Y is a subset of X, then X → Y
• Augmentation: If X → Y, then XZ → YZ
• Transitivity: If X → Y and Y → Z, then X → Z

Using the inference rule, we can derive additional functional dependency from the initial set.

• Union: If X → Y and X → Z, then X → YZ
• Decomposition: If X → YZ, then X → Y and X → Z
• Pseudo transitivity: If X → Y and WY → Z, then WX → Z
• Composition: If X → Y and Z → W, then XZ → YW

• Read Uncommitted – Read Uncommitted is the lowest isolation level. In this level, one transaction may read not yet committed changes made by other transaction, thereby allowing dirty reads. In this level, transactions are not isolated from each other.
• Read Committed – This isolation level guarantees that any data read is committed at the moment it is read. Thus it does not allows dirty read. The transaction holds a read or write lock on the current row, and thus prevent other transactions from reading, updating or deleting it.
• Repeatable Read – This is the most restrictive isolation level. The transaction holds read locks on all rows it references and writes locks on all rows it inserts, updates, or deletes. Since other transaction cannot read, update or delete these rows, consequently it avoids non-repeatable read.
• Serializable – This is the Highest isolation level. A serializable execution is guaranteed to be serializable. Serializable execution is defined to be an execution of operations in which concurrently executing transactions appears to be serially executing.

References : Wikipedia

step 1:
(SELECT Catalog.pid from Suppliers, Catalog
WHERE Suppliers.sid = Catalog.pid)
We are getting pids for all which are supplied.

step 2:
(SELECT Catalog.pid from Suppliers, Catalog
WHERE Suppliers.sname <> 'sachin' and Suppliers.sid = Catalog.sid)
we are getting pids for all parts which are supplied by any other supplier other than Sachin.

step 3:
When we perform MINUS [All pids – pids(all others except sachin) = We get only sachin]

step 4:
We get the pids which are supplied by only Sachin

NOTE : MINUS operator will return only those rows which are unique in only first SELECT query and not those rows which are common to both first and second SELECT queries.

• Given relation is in 1NF because All attribute in the given functional dependencies are atomic values
• Candidate key = ISBN
• A relation is in 2NF if every non-prime attribute of the relation is dependent on the whole of every candidate key. So the given dependencies satisfies the 2NF
•  3NF : Address which is Transitively dependent on ISBN . Hence Transitive dependency exists .
• So the given relation is in 2NF and not in 3NF

Search key field = 12 bytes
Record pointer = 10 bytes
Block pointer = 8 bytes
Block size = 1 KB
For Leaf node
Let order of leaf be 'n'
size of search key * n + record pointer * n + block pointer <=1024
12*n + 10*n+8 <=1024
12n + 10n+8 <=1024
22n+8 <=1024
22n <=1024-8
22n <=1016
n<=1016/22
n=46
For Non-Leaf Node
size of search key * n + block pointer * (n+1) <=1024
12*n + 8*n+8 <=1024
20n+8<=1024
20n <=1024-8
20n <=1016
n=50
∴order of leaf node(p) = 46
∴order of non-leaf node(p_leaf) = 50

Correct option is Hashed File.

Hash File Organization is a file organization technique where a hash function is used to compute the address of a record. It uses the value of an attribute or set of attributes as input and gives the location (page/block/bucket) where the record can be stored.

Embedded Pointer are Pointer Set in a data record and it provides a secondary access path

Association is a relationship between two objects .It defines the multiplicity between objects like 1-1,1-M,M-1,M-M relations.
Aggregation is a special form of association
Composition is a special form of aggregation.

From the given diagram we can write Functional Dependencies(Fd's) as
AB  -> CDE
C     -> B
Candidate keys = AB,AC
So, The given relation is in 3NF but not BCNF because "C" is not a super key

Dependency Preserving.
R(A, B, C, D) is decomposed into R1 (A, B) and R2(C, D) and there are only two FDs A -> B and C -> D. So, the decomposition is dependency preserving

• R1 (A, B) is covered A -> B
• R2(C, D) is covered C -> D

it is functional dependency preserving because both FD's are covered

Lossless-Join Decomposition:
Decomposition of R into R1 and R2 is a lossless-join decomposition if at least one of the following functional dependencies are in F+ (Closure of functional dependencies)
R1 ∩ R2 → R1
OR
R1 ∩ R2 → R2
In the given relation R(A, B, C, D) is decomposed into R1 (A, B) and R2(C, D), and R1 ∩ R2 is empty. So, the decomposition is not lossless

→ represents Functional dependency
→→ represents multivalue dependency
A → B means that the values of B are determined by the value of A
A→→ C means for A ,C has more than one value.
Example for multivalue dependency
A C
1 5
1 6
we have multiple value of c but c does not have multiple values for a . so we cannot say c->>a but we can say a->>c. and question is saying c can take different value not a .

INNER JOIN-
The INNER JOIN selects all rows from both the tables as long as the condition satisfies. INNER JOIN will create the result-set by combining all rows from both the tables where the condition satisfies i.e value of the common field will be same.

Already solved  this question please click this link for solution : https://academyera.com/isro-2016-sql-2

Hierarchical Model

• The data is organize in a tree structure where the nodes represent the records and the branches of the tree represent the fields.
• Since the data is organized in a tree structure, the parent node has the links to its child nodes.
• If we want to search a record, we have to traverse the tree from the root through all its parent nodes to reach the specific record. Thus, searching for a record is very time consuming.
• Hashing function is used to locate the root.

Relations produced from an E-R model will always be in 3NF

Already solved click the following link for explanation https://academyera.com/isro-2018-functional-dependency-2

We have many-to-one relationship set between entity sets student and Department.
Let two relations be
R1(Student with pk SId)
R2(Department with pk DId)

NOTE :
SId can uniquely identify DId but DId can not uniquely identify the SId.
Thus,pk(R1) -> pk(R2)
Using Student ID(SID) we can unique identify Department(DID) but using DID we can find the student uniquely because one department have many students

Database systems have special requirements which are typically more refined than most operating systems. For example, a single user may have different privileges on different files throughout the system, including changing indices and attributes which file systems typically don’t monitor. The advantage of using the operating system’s security mechanism is that it simplifies the database system and can be used for simple (read/write) security measures.

Given
R1(a,b,c) and R2(x,y,z)

Let us say "a" is the foreign key of R1 that refers to "x" primary key of R2

• Insert into R1 operation will take place there will be inconsistency in the database, since it has a foreign key which refers to the primary key of R2
• Insert into R2 Can not cause violation
• Delete from R1 Can not cause violation.
• Delete from R2 Can cause violation if we delete any value of x in R2, then the value referred by a in R1 should also be deleted.

Maximum number of Superkeys possible for a table with n attributes = 2^(n-1)
Given n = 4.
So, Possible Superkeys = 2^4-1 = 8
Possible Superkeys = X, XW, XZ, XY, XZW, XYW, XYZ, XYZW

• Lossless, dependency-preserving decomposition into 3NF is always possible - TRUE
  IN 3NF it is possible to satisfy both lossless and dependency preserving. i.e., for any relation there always exist a decomposition to 3NF, which is guaranteed to satisfy both the given properties.
• Any relation with two attributes is in BCNF. - TRUE

• TRUE - Number of child pointers in a B/B+ tree node is always equal to number of keys in it plus one
• TRUE - B/B+ tree is defined by a term minimum depends on hard disk block size, key and address sizes.

• Given X is a primary key and Y is foreign key on deleting (3,4), all the foreign key occurrences of 3 will also be deleted (4,3).
• Now 4 is also deleted which was also primary key in (4,3), its corresponding foreign key occurrences will also be deleted (2,4) and (6,4).
• NOW 2 and 6 are also deleted, so their foreign key occurrences also deleted (5,2),(7,2).
• Similarly 5 and 7 foreign key occurrences will also be deleted (9,5).

Given
Functional Dependencies
A→B
B→C
D→E
E→D
F→G
F→H
(E,F)→l

(AD)⁺ ={A,B,D,E}
(AE)⁺ ={A,B,D,E}
So (AD) & (AE) are candidate keys for this relation R(E,D,A,B)
Given relation is in 1NF because it contains an atomic values.
There exists PARTIAL DEPENDENCY
A -> B
D -> E
E -> D
So the relation not in 2NF

• Multiplicity defines how many objects participate in a relationship and it is the number of instances of one class related to one instance of the other class.
• For each association and aggregation, there are two multiplicity decisions to make, one for each end of the relationship.
• Multiplicity is represented as a number and a * is used to represent a multiplicity of many.

For 3NF every non‐prime attribute of table must be dependent on primary key. The transitive functional dependency should be removed from the table. The table must be in second normal form 2NF.

Transitive Functional

• When an indirect relationship causes functional dependency it is called Transitive Dependency.
• If P -> Q and Q -> R is true, then P-> R is a transitive dependence
• A transitive dependency can only occur in a relation of three of more attributes

Refer : https://academyera.com/normalization-in-dbms-1nf-2nf-3nf-bcnf

The fundamental operations in relation algebra are

• Select
• Project
• Union
• Set difference,
• Rename
• Cartesian product

• CANDIDATE KEY is a set of attributes that uniquely identify tuples(rows) in a table.
• Candidate Key is a super key with no repeated attributes
• Each Table have one or more candidate keys
• Primary key should be selected from the candidate keys.
• Every table must have at least a single candidate key.
• Each table can have multiple candidate keys but only a single primary key

In functional dependency between two sets of attribute A and B denoted as
A →B
A is determinant, B is dependent
Dependent It is displayed on the right side of the functional dependency diagram.
Determinant It is displayed on the left side of the functional dependency diagram.

Prepared Statement

• PreparedStatement interface extends the Statement interface.
• It represents a precompiled SQL statement which can be executed multiple times.
• This accepts parameterized SQL quires and you can pass 0 or more parameters to this query.
• Initially, this statement uses place holders “?” instead of parameters, later on, you can pass arguments to these dynamically using the setXXX() methods of the PreparedStatement interface.

Source : Tutorialspoint

Following are the fundamental operations in relation algebra

• Select
• Project
• Union
• Set difference
• Rename
• Cartesian product

• True : BCNF is stricter than 3NF
• False Lossless, dependency-preserving decomposition into BCNF is always possible
  It is not always possible to decompose a table in BCNF and preserve dependencies.
• True Lossless, dependency-preserving decomposition into 3NF is always possible
• True Any relation with two attributes is in BCNF

BCNF decomposition can always satisfies lossless, but it may not be always possible to get a dependency preserving BCNF decomposition.

Let take an example Relation R (V, W, X, Y, Z), with functional dependencies

• V,W -> X
• Y,Z -> X
• W -> Y

It may not be possible to get a dependency preserving BCNF decomposition.

Note : Redundancies are sometimes still present in a BCNF relation as it is not always possible to eliminate them completely.

-A functional dependency X → Y is said to be trivial if and only if Y ⊆ X.
-If RHS of a functional dependency is a subset of LHS, then it is called as a trivial functional dependency.

Let us take an examples of trivial functional dependencies are

• XY  →  X
• XY  →  Y
• XY  →  XY

Using the two relationships mother and father provides us a record of a child’s mother, even if we are not aware of the father’s identity; a null value would be required if the ternary relationship parent is used. Using binary relationship sets is preferable in this case.

Given dependencies
S→T
T→U
U→V
V→S.

Candidate keys are S,T,U and V.
All attributes are Candidate keys
All attributes are Prime Attributes So, given relation R is 3NF and BCNF

R1(S,T) { S→T ,T →S} ∩ R2(U,V){ U → V , V → U} = Ø
R1∩R2 = Ø means there is no common attribute in R1 and R2. This makes the decomposition lossy
Decomposition in BCNF but dependency not preserved.

• Data scrubbing is also called  as data cleansing it is the process of amending or removing data in a database that is incorrect, incomplete, improperly formatted, or duplicated.
• By using data scrubbing tool can save a database administrator a significant amount of time and can be less costly than fixing errors manually.

Find the closure set of all the options given. If any closure includes all attributes of a table then it becomes the candidate key.

Algorithm to find Closure Set

• Step1: Equate an attribute or attributes to X for which closure needs to be identified.
• Step2: Take each FD (functional dependency) one by one and check whether the left side of FD is available in X, if yes then add the right side attributes to X if it is not available.
• Step3: Repeat step 2 as many times as possible to cover all FD's.
• Step4: After no more attributes can be added to X declare it as the closure set.

Closure of AEF, i.e. AEF⁺ = {ABCDEF}
Closure of BEF, i.e. BEF⁺ = {ABCDEF}
Closure of DEF, i.e. DEF⁺ = {ABCDEF}

So AEF, BEF and DEF are candidate keys So, option(A) is correct.

Answer should be option(c ) Here we are not using any checkpoints. So, We must undo log record (vi) to set A to 16,500 and then redo log records (ii) and (iii) because system fail before commit operation. So we need to undone active transactions(T2) and redo committed transactions (T1)

Checkpoint : Checkpoint is a mechanism where all the previous logs are removed from the system and stored permanently in a storage disk. Checkpoint declares a point before which the DBMS was in consistent state, and all the transactions were committed.

Recovery : When a system with concurrent transactions crashes and recovers, it behaves in the following manner?
⇒ The recovery system reads the logs backwards from the end to the last checkpoint.
⇒ It maintains two lists, an undo-list and a redo-list.
⇒ If the recovery system sees a log with and or just, it puts the transaction in the redo-list.
⇒ If the recovery system sees a log with but no commits or abort log found, it puts the transaction in undo-list.
All the transactions in the undo-list are then undone and their logs are removed. All the transactions in the redo-list and their previous logs are removed and then redone before saving their logs.

Primary Index(Sparse)
- It is defined as an ordered data file.
- The data file is ordered on a key field.
- The key field is generally the primary key of the relation.

Secondary Index(Dense)
- This index may be generated from a field which is a candidate key and has a unique value in every record, or a non-key with duplicate values.

Clustering Index(Sparse)
- This index is defined on an ordered data file.
- The data file is ordered on a non-key field.

• UPDATE a DML command is used to update the data or row in the table
• DELETE is a DML command and is used to remove tuples/records from a relation/table.
• DROP is a DDL command and is used to remove the whole structure of the relation. we can delete a table/relation

• B⁺ tree is a balanced Tree and it can store multiple keys in each node of a B⁺ tree so that tree height is small
• Where BST where each node contains only one key, So height of BST would be more than height of B⁺ tree
• Thus, disk can transfer data in blocks when B⁺ tree is used for indexing database relations.

First we need to Draw precedence graph for all the options and for conflict serializability graph must not contain cycle.
option (D) is the only graph will be acyclic remaining all are cyclic

Domain Level Integrity
A domain defines the possible values of an attribute. Domain Integrity rules govern these values. In a database system, the domain integrity is defined by:

• The datatype and the length
• The NULL value acceptance
• The allowable values, through techniques like constraints or rules
• The default value

Locks are used In-order to maintain transactional integrity and database consistency.

Maximum number of children node is 6
Maximum number of keys can have is 5
Order is Key(5)+1=6
Minimum number of children that a node can have is ceil(6/2) = 3.
Therefore, Minimum number of keys that a node can have becomes 2 i.e (3-1)

• CANDIDATE KEY is a set of attributes that uniquely identify tuples in a table. Candidate Key is a super key with no repeated attributes. A super key with no redundant attribute is known as candidate key
• PRIMARY KEY should be selected from the candidate keys. Every table must have at least a single candidate key. A table can have multiple candidate keys but only a single primary key.

Reference : https://www.sqlshack.com/locking-sql-server/

• SQL Parser parses a SQL query in a string field.
• When parsing a query the processor generates fields based on the fields defined in the SQL query and specifies the CRUD operation, table, and schema information in record header attributes.
• SQL DML Parser generator is used for SQL query parsing
• In addition to syntactic validation the parser can also perform a semantic validation

One useful way of generating more primitive recursive functions from existing ones is through what is known as bounded summation and bounded product
Reference : https://planetmath.org/boundedminimization

• Holding a lock is how one thread tells other threads: “I am updating this thing, So don’t touch it right now.”
• Global locks synchronize access to global resource

• In a relational schema, each tuple is divided into fields called Domains .
•  In a relational model, each attribute carries a domain of its own.
• A domain refers to the set of unique values used to define that attribute and will act as a "model" set of values.
• These values, being unique to that attribute are referred to as "atomic values".
•  A domain is a set of acceptable values that a column is allowed to store
• The rule for determining the domain boundary may be as simple as a data type with an enumerated list of values.

Reference : https://www.javatpoint.com/dbms-integrity-constraints#:~:text=Integrity%20constraints%20are%20a%20set,data%20integrity%20is%20not%20affected.

In Given relational algebra, Tuple t should have 2 attributes A and B such that t.A=10 and t.B=20.

option(A) : Incorrect
Select tuples having A=10 or B=20

option(B) : Incorrect
Select tuples having A=10 or we can select tuples having B=20

option(C) : Correct
Select tuples having A=10 and tuples having B=20
(Tuples having A=10)∩( Tuples having B=20) Which is equal to (Tuples having A=10 and B=20).

Find the closure set of all the options given. If any closure covers all the attributes of the relation R then that is the key.
{CD}⁺={CDF}
{EC}⁺={ABCDEF}
{AE}⁺={ABE}
{AC}⁺={ABCF}
EC is the key So, Option(B) is Correct

Already solved click the following link for explanation https://academyera.com/isro-2018-functional-dependency-2

In this case 500 accesses are sufficient to transfer 1000 records.
∴ Elapsed time=(Time taken to transfer Z records in one access +Processing time of two records) x 500
= [(10 + 4 + 4) + 3 + 3] x 500m sec
= [(18) + 6] x 500m sec
= 12 sec.

These SQL commands are mainly categorized into 4 categories as:

• DDL – Data Definition Language
• DQl – Data Query Language
• DML – Data Manipulation Language
• DCL – Data Control Language

Remember Names

• Relation or Table
• Tuple or Row or Record
• Attribute or column or filed
• Degree means Number of columns
• Cardinality means Number of rows
• Domain means data type(legal values)

option(C) is correct Total 5 splits will occur
Let take an example of inserting 10 keys from 1 to 10 which cause maximum split
Insertion of 3 keys
1 2 3
Insertion of 4th key 1^st split will occur

• Data scrubbing is also called  as data cleansing it is the process of amending or removing data in a database that is incorrect, incomplete, improperly formatted, or duplicated.
• By using data scrubbing tool can save a database administrator a significant amount of time and can be less costly than fixing errors manually.

Find the closure set of all the options given. If any closure includes all attributes of a table then it becomes the candidate key.

Algorithm to find Closure Set

• Step1: Equate an attribute or attributes to X for which closure needs to be identified.
• Step2: Take each FD (functional dependency) one by one and check whether the left side of FD is available in X, if yes then add the right side attributes to X if it is not available.
• Step3: Repeat step 2 as many times as possible to cover all FD's.
• Step4: After no more attributes can be added to X declare it as the closure set.

Closure of AEF, i.e. AEF⁺ = {ABCDEF}
Closure of BEF, i.e. BEF⁺ = {ABCDEF}
Closure of DEF, i.e. DEF⁺ = {ABCDEF}

So AEF, BEF and DEF are candidate keys So, option(A) is correct.

Answer should be option(c ) Here we are not using any checkpoints. So, We must undo log record (vi) to set A to 16,500 and then redo log records (ii) and (iii) because system fail before commit operation. So we need to undone active transactions(T2) and redo committed transactions (T1)

Checkpoint : Checkpoint is a mechanism where all the previous logs are removed from the system and stored permanently in a storage disk. Checkpoint declares a point before which the DBMS was in consistent state, and all the transactions were committed.

Recovery : When a system with concurrent transactions crashes and recovers, it behaves in the following manner?
⇒ The recovery system reads the logs backwards from the end to the last checkpoint.
⇒ It maintains two lists, an undo-list and a redo-list.
⇒ If the recovery system sees a log with and or just, it puts the transaction in the redo-list.
⇒ If the recovery system sees a log with but no commits or abort log found, it puts the transaction in undo-list.
All the transactions in the undo-list are then undone and their logs are removed. All the transactions in the redo-list and their previous logs are removed and then redone before saving their logs.

Primary Index(Sparse)
- It is defined as an ordered data file.
- The data file is ordered on a key field.
- The key field is generally the primary key of the relation.

Secondary Index(Dense)
- This index may be generated from a field which is a candidate key and has a unique value in every record, or a non-key with duplicate values.

Clustering Index(Sparse)
- This index is defined on an ordered data file.
- The data file is ordered on a non-key field.

• UPDATE a DML command is used to update the data or row in the table
• DELETE is a DML command and is used to remove tuples/records from a relation/table.
• DROP is a DDL command and is used to remove the whole structure of the relation. we can delete a table/relation

Process of analyzing relation schemas to achieve minimal redundancy and insertion or update anomalies is classified as normalization of data

Normalization is a process of organizing the data in database to avoid data redundancy, insertion anomaly, update anomaly & deletion anomaly.

Following are the most commonly used normal forms:

• First normal form(1NF)
• Second normal form(2NF)
• Third normal form(3NF)
• Boyce & Codd normal form (BCNF)

Reference : https://academyera.com/normalization-in-dbms-1nf-2nf-3nf-bcnf#De%20Normalization

• If every functional dependency in set E is also in closure of F means F⁺ ⊆ E⁺
• E cover F if every FD in F can be referred from E.
• E cover F if F⁺ ⊆ E⁺ (⁺ means closure)
• Every set of functional dependencies has a canonical cover

• Given a relation R, a set of attributes X in R is said to functionally determine another set of attributes Y, also in R, (written X → Y) if, and only if, each X value in R is associated with precisely one Y value in R; R is then said to satisfy the functional dependency X → Y.
• A functional dependency is denoted by an arrow "→". The functional dependency of X on Y is represented by X → Y.
• X → Y means X attribute we can derive attribute Y.
• The left side of FD is known as a determinant, the right side of the production is known as a dependent

• A candidate key is a column, or set of columns, in a table that can uniquely identify any database record without referring to any other data.
• Candidate Key is a super key with no repeated attributes
• Each Table have one or more candidate keys
• Primary key should be selected from the candidate keys.
• Every table must have at least a single candidate key.
• Each table can have multiple candidate keys but only a single primary key
• Each table may have one or more candidate keys, but one candidate key is unique, and it is called the primary key.
• A candidate key is the minimal subset of a super key.
• Candidate key of having "no redundant attributes" and being a "minimal representation of a tuple" in a relational database table

In tuple relational calculus P1 → P2 is equivalent to ~P1 V P2

• The term cardinality refers to the uniqueness of data values contained in a particular column (attribute) of a database table. The lower the cardinality, the more duplicated elements in a column.
• In the context of databases, cardinality refers to the uniqueness of data values contained in a column.
• Cardinality refers to a number. It gives the number of unique values that appear in the table for a particular column.
• cardinality of relation= multiplication of domain of each attribute
• ∣R∣ ≤ ∣ dom(A1)×dom(A2)…dom​​​​​​​(An) ∣ For eg: you have a table called Person with column Gender. Gender column can have values either 'Male' or 'Female".
• Then the cardinality of Gender column is 2, since there are only two unique values that could possibly appear in that column – Male and Female.

Reference : https://en.wikipedia.org/wiki/Cardinality

We can use parentheses to override the rules of precedence

"AS command" in SQL is used to rename a column or table with an alias name.
An alias only exists for the duration of the query.
Example
SELECT StudentID AS ID, StudentName AS Student
FROM Student;

• Relation sometime called as Table
• Fields sometime called as columns or Attributes
• Rows sometime called as Records or Tuples

In a database, Related fields are grouped within a table to form a record

• A table joined with itself is called self join
• Table must contain a column (call it X) that acts as the primary key and a different column (call it Y) that stores values that can be matched up with the values in Column X

Create one table for each entity while converting an ER diagram into relation model
So we got 2 tables i.e.
One for Exam(ExamID, ExamName)
Another for Person(NID, Name)

Now problem is with relationship Entity Qualification, on which side we can add it, unfortunately we cannot add QualifiedDate in any of the two tables formed so we need to create a new table using Primary Key from both Entity Person and Entity Exam.

We obtain Qualification(NID, ExamID, QualifiedDate)

Answer should be option(B ) Here we are not using any checkpoints. So, We must undo log record 6 to set b to 10,000 and then redo log records 2 and 3 because system fail before commit operation. So we need to undone active transactions(T2) and redo committed transactions (T1)
Refer this link for more information : https://academyera.com/ugc-net-cs-2018-dec-paper-2-transactions-2

Given data
Database contains n records
Single block can hold either 3 records or 10 key pointers
By using Dene index
Total Number of blocks needed to store data file with "n" records = n/3
Total Number of blocks needed to store dense file index = n/10
Total blocks required to hold data and dense index= n/3 + n/10 = 13n/30 blocks.

Find the closure set of all the options given. If any closure covers all the attributes of the relation R then that is the key.
{CD}⁺={CDF}
{EC}⁺={ABCDEF}
{AE}⁺={ABE}
{AC}⁺={ABCF}
EC is the key So, Option(B) is Correct

SELECT P.pid FROM Parts P WHERE P.color<>’blue’
Above query select all non blue parts

SELECT C.sid FROM Catalog C WHERE C.pid NOT IN
Above query returns all suppliers who have supplied a blue part

SELECT S.sname
FROM Suppliers S
WHERE S.sid NOT IN
Above query returns suppliers who have not supplied any blue parts.

So, None of the options are matching.

Option(A): wrong, It may include who have supplied blue parts and may not include “null” parts.
Option(B): wrong because it returning other than any blue part.
Option(C): wrong as it does not select suppliers who have not supplied any parts which the given query does
Option(D): wrong

Reference : http://sqlfiddle.com/#!9/9ae12d/1/0