DBMS is the KTU 2019 course for S4 CSE students, also known as Database Management System. This course provides in-depth information on database concepts, site models, implementation of multilingual data processing and SQL commands. This course is compulsory for all fourth-year CSE students at KTU. database users' activities. The program provides data interpreting services, data manipulation, and query data, support for a high-level programming language, helping to ensure the integrity of the database. SQL, transactions, triggers, data mining algorithms, data storage design considerations.
| Board | KTU |
| Scheme | 2019 New Scheme |
| Year | Second Year |
| Semester | S4 Computer Science |
| Subject | CST 204 | Database Management System Solved Model Question Paper |
| Type | Model Question Paper Solved |
| Category | KTU S4 Computer Science |
To fulfil our mission, we try hard to give you qualified and updated study materials. Here is the answer key for the KTU DBMS MODEL QUESTION PAPER in the syllabus. Study well & don't forget to share with your friends.
--------------------------------------------------------
DBMS Model Question Paper Solved | 2019 Scheme
PART-A
Notation A ⋈θ B
Theta joining can apply to any conditions in the matter of selection.
Example
- The clause when used first in each row of tables or items with a table value in the drawing window. Only the lines that meet the conditions in the section WHERE they are collected.
- The HAVING clause is then applied to the lines in the result set. Groups that meet the HAVING criteria only appear in the query output. You can only use the WE-section in the re-columns in the GROUP BY or integration function.
- The trigger is widely used to maintain the integrity of the information on the database.
- Triggers help the database designer ensure certain actions such as maintaining an audit file, are completed regardless of which program or user makes changes to the data.
- Create a job evaluation tracker on a database. For example, you can track updates in the order table by reviewing the verification information in the checklist.
- Apply the rule of business. For example, you can determine when an order exceeds a customer’s credit limit and display a message indicating that.
- Find additional data not available within the table or on the database. For example, if an update occurs in the bulk column of the item table, you can calculate the correlation corresponding to the column_value.
- Focus on the integrity of the index. When you delete a customer, for example, you can use a trigger to delete matching lines with the same customer number in the order table.
- The table should not contain any multivalued attribute
- The column must contain content from the same domain
- Each column must have a unique name
- There are no column layouts
- No duplicate lines If any conditions are not satisfied the table is not in 1NF
- Also called write - write Conflict problem
- In the case of a missing update issue, an update made to the data item is missing as it is written over an update made with another purchase.
PART-B
| Physical Data Independence | Logical Data Independence |
|---|---|
| It is mainly concerned with how the data is stored in the system. | It is mainly concerned with the structure or the changing data definition. |
| It is easy to retrieve. | It is difficult to retrieve because the data is mainly dependent on the logical structure of the data. |
| As compared to logical independence it is easy to achieve physical data independence. | As compared to physical independence it is not easy to achieve logical data independence. |
| Any change at the physical level does not require to change at the application level. | The change in the logical level requires a change at the application level. |
| Modifications made to the internal level may or may not be necessary to improve the performance of the structure. | Modifications made at a reasonable level are important whenever the logical structure of the site needs to be changed. |
| It is concerned with the internal schema. | It is concerned with the conceptual schema. |
| Example: Change in compression techniques, Hashing algorithms and storage devices etc. | Example: Add/Modify or Delete a new attribute. |
(b) Salaries of employees from ‘Accounts’ department
(c) Names of employees along with his/her supervisor's name
(d) For each employee return name of the employee along with his department name and the names of projects in which he/she works
(e) Names of employees working in all the departments (14)
πName (σGender=’female’ AND Salary> ‘20000’ (Employee))
πName,Salary (Employee ⋈Dnum=Dno AND Dname='Accounts' Department)
πEmplyee.Name,Emp1.Name (Employee ⋈Employee.SuperEno=Emp1.Eno1 AND Dname=' Accounts^' ) ρ_Emp1 (Employee))
Create tables STUDENT(ROLLNO int primary key,NAME text not null,CLASS varchar(10) ,SEM int ,ADVISER text ,Foreign key (Adviser) references faculty( name ));
| PersonID | Last Name | First Name | Age |
|---|---|---|---|
| 1. | Hansen | Ola | 30 |
| 2. | Svendson | Tove | 23 |
| 3. | Pettersen | Kari | 20 |
| OrderID | OrderNumber | PersonID |
|---|---|---|
| 1. | 77895 | 3 |
| 2. | 44678 | 3 |
| 3. | 22456 | 2 |
| 4, | 24562 | 1 |
select employee.employee-name, employee.street, employee.city fromemployee, workswhere employee.employee-name=works.employee-nameand company-name = ' RIL Inc.' and salary > 10000)
select e.employee-namefrom employee e, works w, company cwhere e.employee-name = w.employee-name and e.city = c.cityand w.company-name = c.company-name
select employee-namefrom workswhere company-name <> ‘KYS Inc.'
select employee-namefrom workswhere salary > all (select salaryfrom workswhere company-name = 'SB Corporation')
SELECT employee-name, COUNT(*)FROM worksGROUP BY company-name;ORDER BY company-name DESC
- A noncorrelated (simple) subquery obtains its results independently of its containing the (outer) statement.
- A correlated subquery requires values from its outer query in order to execute.
=> SELECT name, street, city, state FROM addressesWHERE EXISTS (SELECT * FROM states WHERE states.state = addresses.state);
- The query extracts and evaluates each address. state value in the outer subquery records.
- Then the query—using the EXISTS predicate—checks the addresses in the inner (correlated) subquery.
- Because it uses the EXISTS predicate, the query stops processing when it finds the first match.
=> SELECT name, street, city, state FROM addresses WHERE state IN (SELECT state FROM states);
- Executes the subquery SELECT state FROM states (in bold).
- Passes the subquery results to the outer query.
3NF
A relation is in the third normal form if
there is no transitive dependency for non-prime attributes as well as it is in the second normal form.
Two Conditions for 3NF
•
It should be in 2NF
•
No transitive Dependency
– What is Transitive dependency?
– If a Non-prime attribute → Non-prime attribute, then it is called a Transitive dependency
Example:
•
R(A,B,C,D)
– FD:- (A→B, B→C, C→D)
– Check whether the above relation is in 3NF
Solution
•
Find the attribute closure of
all the attributes
– ABCD+={A,B,C,D}
– ABCD+ is a superkey
•
Find out the candidate key
– Discard the element by checking the FD
– ABCD
– Check A is a superkey
– A+={A, B, C, D} ---→ A is a candidate key because there is no proper subset for identifying a superkey
•
Check out more candidate keys
are possible
– Check whether the prime attribute A is present on the right-hand
side of the FD
– In this problem, no prime attribute is there
– Hence no more candidate key
•
Now what we have identified.
– A is the only candidate key
– Prime Attribute is A
•
Now check all the FD whether
satisfies the following conditions
– It should be in 2NF
– No transitive Dependency
– A→B, B→C, C→D
BCNF
•
A relation is in BCNF iff
– It is in 3NF
– For each non-trivial FD XY, X must be
Super key
Example:
•
R(A,B,C)
– FD:- (A→B, B→C, C→A)
– Check whether the above relation is in BCNF
Solution
•
Find the attribute closure of
all the attributes
– ABC+={A,B,C}
– ABC+ is a super key
•
Find out the candidate key
– Discard the element by checking the FD
– ABC
– Check AC is a
– AC+={A,B,C} ---→is a super key
– AC
– A+={A, B, C} ---→ is a super key (A is a candidate key because there is no proper subset for identifying a super key )
•
Check out more candidate keys
are possible
– Check whether the prime attribute A is present on the right-hand
side of the FD
– Yes prime attribute is there in the RHS of the FD:- (A→B, B→C, C→A)
– C, B
– Hence the candidate keys are A, B, C
– Hence prime attributes are A, B, C
•
Check for BCNF Conditions
– It is in 3NF
– For each non-trivial FD X→Y, X must be super key
Based
on property 1:
R1 U R2 U R3 = R
R1(A1,A2,A3) U R2(A1,A4) U R3(A2,A4,A5) =
R(A1,A2,A3,A4,A5)
Property 1 satisfies
Based
on property 2 common attributes are there in
R1, R2 and R3
Based
on property 3:
R1(A1,A2,A3), R2(A1,A4)
Common attribute for R1 and R2 is A1. Find
the Attribute closure for A1
A1+ = {A1,A2,A4,A5}
A1 is candidate key for R2
R1 ⋈ R2=A1,A2,A3,A4
Now check with R3 (A2,A4,A5)
Here A2 and A4 are the common attribute and
therefore find attribute closure for A2A4
A2A4+ = A2,A4,A5
A2A4 is the candidate key for R3 and
Hence it is Lossless
ABCDEFG+={A,B,C,D,E,F,G} is a
ACD+={A,C,D,B,G,E,F} is a
Proper subset of ACD+ = {AC,CD,AD,A,C,D}
- AC+ is not a super key
- CD+ is not a super key
- AD+ is not a superkey
- A+ is not a super key
- C+ is not a super key
- D+ is not a super key
Hence ACD is a candidate key
Now
prime attributes are A,C,D
CDE+={C,D,E,A,B,E,F,G} is a superkey
Proper subset of ACD+ = {AC,CD,AD,A,C,D}
- CD+ is not a super key
- DE+ is not a super key
- CE+ is not a super key
- C+ is not a super key
- D+ is not a super key
- E+ is not a super key
Hence CDE is a candidate key
Now prime attributes are A,C,D,E
Now check for transitive dependency
A→B --> No transitive dependency
AC→G --> No transitive dependency
AD→EF --> No transitive dependency
EF→G --> No transitive dependency
CDE→AB --> No transitive dependency
Hence
relation R(A, B, C, D, E, F, G) with the FDs A→B , AC→G, AD→EF, EF→G, CDE→AB is
in 3NF
Lossless Join Decomposition
If we decompose a relation R into relations R1 and R2,
·
Decomposition is lossy if R1 ⋈ R2 ⊃ R
·
Decomposition is lossless if R1 ⋈ R2 = R
To check for lossless join decomposition using FD set, following
conditions must hold:
1.
Union of Attributes of R1 and R2 must be equal to attribute of R.
Each attribute of R must be either in R1 or in R2.
Att(R1) U
Att(R2) = Att(R)
2.
Intersection of Attributes of R1 and R2 must not be NULL.
Att(R1) ∩
Att(R2) ≠ Φ
3.
Common attribute must be a key for at least one relation (R1 or R2)
Att(R1) ∩
Att(R2) -> Att(R1) or Att(R1) ∩ Att(R2) -> Att(R2)
For Example, A relation R (A, B, C, D) with
FD set{A->BC} is decomposed into R1(ABC) and R2(AD) which is a lossless join
decomposition as:
1.
First condition holds true as Att(R1) U Att(R2) = (ABC) U (AD) =
(ABCD) = Att(R).
2.
Second condition holds true as Att(R1) ∩ Att(R2) = (ABC) ∩ (AD) ≠ Φ
3.
Third condition holds true as Att(R1) ∩ Att(R2) = A is a key of
R1(ABC) because A->BC is given.
Dependency Preserving Decomposition
If we decompose a relation R into relations
R1 and R2, All dependencies of R either must be a part of R1 or R2 or must be
derivable from combination of FD’s of R1 and R2.
For Example, A relation R (A, B, C, D) with
FD set{A->BC} is decomposed into R1(ABC) and R2(AD) which is dependency
preserving because FD A->BC is a part of R1(ABC).
Example
Consider a schema R(A,B,C,D) and
functional dependencies A->B and C->D which
is decomposed into R1(AB) and R2(CD)
This decomposition is dependency
preserving decomposition because
- A->B can be
ensured in R1(AB)
- C->D can be
ensured in R2(CD)
•
A transaction is a
single logical unit of work which accesses and possibly modifies the contents
of a database.
– Transactions access data using read and write operations.
•
In order to maintain
consistency in a database, before and after the transaction, certain properties
are followed. These are called ACID properties
Atomicity
•
By this, we mean that either
the entire transaction takes place at once or doesn’t happen at all.
•
There is no midway i.e.
transactions do not occur partially.
•
Each transaction is considered
as one unit and either runs to completion or is not executed at all.
•
It involves the following two
operations.
—Abort: When a job is terminated, changes made to database are not
visible.
—Commit: If a transaction commits, changes made are visible.
Atomicity is also known as the ‘All or nothing rule’.
Atomicity Example
•
Consider the following
transaction T consisting of T1 and T2:
Transfer of 100 from account X to account Y.
•
If the transaction fails after
completion of T1 but before completion of T2.(
say, after write(X) but before write(Y)), then
amount has been deducted from X but not added to Y.
•
This results in an inconsistent
database state.
•
Therefore, the transaction must
be executed in entirety in order to ensure correctness of database state.
Consistency
•
This means that integrity
constraints must be maintained so that the database is consistent before and
after the transaction.
•
It refers to the correctness of
a database.
•
Referring to the same
example,
– The total amount before and after the transaction must be
maintained.
– Total before T occurs = 500 + 200 = 700.
– Total after T occurs = 400 + 300 = 700.
– Therefore, database is consistent.
– Inconsistency occurs in case T1 completes but T2 fails.
As a result T is incomplete.
Isolation
•
This property ensures that
multiple transactions can occur concurrently without leading to the
inconsistency of database state.
•
Transactions occur
independently without interference.
•
Changes occurring in a
particular transaction will not be visible to any other transaction until that
particular change in that transaction is written to memory or has been
committed.
•
This property ensures that the
execution of transactions concurrently will result in a state that is
equivalent to a state achieved these were executed serially in some
order.
Isolation Example
•
Let X= 500, Y =
500.
Consider two transactions T and T”.
•
Suppose T has
been executed till Read (Y) and then T’’ starts.
•
As a result , interleaving of
operations takes place due to which T’’ reads correct value
of X but incorrect value of Y and sum
computed by
– T’’: (X+Y = 50, 000+500=50, 500)
•
is thus not consistent with the
sum at end of transaction:
– T: (X+Y = 50, 000 + 450 = 50, 450).
•
This results in database
inconsistency, due to a loss of 50 units. Hence, transactions must take place
in isolation and changes should be visible only after they have been made to
the main memory.
Durability
•
This property ensures that once
the transaction has completed execution, the updates and modifications to the
database are stored in and written to disk and they persist even if a system
failure occurs.
•
These updates now become
permanent and are stored in non-volatile memory.
• The effects of the transaction, thus, are never lost.
| Transaction 1 | Transaction 2 | Transaction 3 |
|---|---|---|
| r1(x) | ||
| r2(Z) | ||
| r1(Z) | ||
| R3(x) | ||
| R3(y) | ||
| w1(X) | ||
| C1 | W3(y) | |
| C3 | ||
| r2(Y) | ||
| w2(Z) | ||
| w2(Y) | ||
| C2 |
Since there is no cycle of transaction in
the above schedule, it is conflict serializable.
Because all of the transactions T1, T2, and
T3 commit after the transactions that wrote the data, this schedule is recoverable.
Key-value
DB
In Key-value DB data is recorded (added, updated, and deleted) and retrieved based on its key / retrieve key. A key-value website links a value to a key, which is used to track an item. In its simplest form, a key value store is similar to a dictionary / list / object item as it exists in most system paradigms, but is stored in a sustainable manner and managed by the Database Management System.
► As the name suggests, this type of NoSQL database implements a hash
table to store unique keys along with the pointers to the corresponding data
values.
► The values can be of scalar data types such as integers or complex
structures such as JSON, lists, BLOB, and so on.
► A value can be stored as an integer, a string, JSON, or an
array—with a key used to reference that value.
► It typically offers excellent performance and can be optimized to
fit an organization’s needs.
► Key-value stores have no query language but they do provide a way
to add and remove key-value pairs.
► Values cannot be queried or searched upon. Only the key can be
queried.
Graph
DB
A graphical database uses nodes, relationships between nodes and key values structures instead of tables to represent information. This model is usually very fast in integrated data sets and uses a schema-less and low-top model ready to capture ad hoc and fast-changing data.
Graph database is designed for the purpose of maintaining and navigating relationships. Relationships are first-class citizens on a graph site, and most of the value of graph information is derived from these relationships. Graphical data uses nodes to store data entities, and margins to maintain relationships between businesses.
Ans 20 (b):
A transaction is said to follow the Two-Phase Locking protocol if
Locking and Unlocking can be done in two phases.
- . Growing Phase: New locks on data items may be acquired but none can be released.
- . Shrinking Phase: Existing locks may be released but no new locks can be acquired.
Example
Transaction T1:
- The growing Phase is from steps 1-3.
- The shrinking Phase is from steps 5-7.
- Lock Point at 3
Transaction T2:
- The growing Phase is from steps 2-6.
- The shrinking Phase is from steps 8-9.
- Lock Point at 6
LOCK POINT is the Point at which the growing
phase ends, i.e., when a transaction takes the final lock it needs to carry on
its work
drawbacks of 2-PL
- Cascading Rollback is possible under 2-PL.
- Deadlocks are possible.
On analyzing the schedule, Dirty Read in T2
and T3 in lines 8 and 12 respectively, when T1 failed we have to roll back
others also. Hence, Cascading Rollbacks are possible in 2-PL
Deadlock in 2-PL
Consider this example. We have two transactions T1 and T2.
Schedule: Lock-X1(A) Lock-X2(B) Lock-X1(B) Lock-X2(A)
--------------------------------------------------------
You May Like :
