Database System & Applications Question & Answers

( Suggestion : Find the question by search page and keep refreshing the page for updated content )

Q1. Check whether the following two sets of functional dependencies P and Q for a relation

R(X, Y, Z, W, V) are equivalent or not.
P={X→ Y, XY→ Z, W→ XZ, W→ V}
Q= {X → YZ, W→ XV }

Answer:- To determine whether the two sets of functional dependencies P and Q for the relation R(X, Y, Z, W, V) are equivalent or not, we need to check if each functional dependency in P can be derived from Q and vice versa.

Let’s analyze each set of functional dependencies:

Set P: {XY, XYZ, W→XZ, W→V}

XY: This functional dependency states that X determines Y. It is not present in set Q.

XYZ: This functional dependency states that X determines YZ. It is present in set Q as X→YZ.

W→XZ: This functional dependency states that W determines XZ. It is not present in set Q.

W→V: This functional dependency states that W determines V. It is present in set Q as W→XV.

Based on the above analysis, we can see that set P has two functional dependencies (XY and W→XZ) that are not present in set Q. Therefore, set P is not a subset of set Q, and they are not equivalent.

In conclusion, the two sets of functional dependencies P and Q for the relation R(X, Y, Z, W, V) are not equivalent.

Q2. a) Create a table with the following fields:

Consider the following relations:
Student(snum: integer, sname: string, major: string, level: string, age: integer)
Class(name: string, meets at: string, room: string, fid: integer)
Enrolled(snum: integer, cname: string)
Faculty(fid: integer, fname: string, deptid: integer)
Write the following queries in SQL. No duplicates should be printed in any of the answers.

i)Find the names of all Juniors (level = SR) who are enrolled in a class taught by Ram.
ii)Find the age of the oldest student who is either a History major or enrolled in a course taught by Ram.
iii)Find the names of all classes that either meet in room R128 or have five or more students enrolled.
iv)Find the names of all students who are enrolled in two classes that meet at the same time.

b) List few disadvantages of SQL.

Answer:- a) SQL Queries:
i) Find the names of all Juniors (level = SR) who are enrolled in a class taught by Ram.
sql
SELECT DISTINCT s.sname
FROM Student s
JOIN Enrolled e ON s.snum = e.snum
JOIN Class c ON e.cname = c.name
JOIN Faculty f ON c.fid = f.fid
WHERE s.level = ‘SR’ AND f.fname = ‘Ram’;

ii) Find the age of the oldest student who is either a History major or enrolled in a course taught by Ram.
sql
SELECT MAX(s.age) AS oldest_age
FROM Student s
WHERE s.major = ‘History’ OR s.snum IN (
SELECT e.snum
FROM Enrolled e
JOIN Class c ON e.cname = c.name
JOIN Faculty f ON c.fid = f.fid
WHERE f.fname = ‘Ram’
);

iii) Find the names of all classes that either meet in room R128 or have five or more students enrolled.
sql
SELECT DISTINCT c.name
FROM Class c
LEFT JOIN Enrolled e ON c.name = e.cname
WHERE c.room = ‘R128’ OR (SELECT COUNT(*) FROM Enrolled WHERE cname = c.name) >= 5;

iv) Find the names of all students who are enrolled in two classes that meet at the same time.
sql
SELECT DISTINCT s.sname
FROM Student s
JOIN Enrolled e1 ON s.snum = e1.snum
JOIN Enrolled e2 ON s.snum = e2.snum AND e1.cname <> e2.cname
JOIN Class c1 ON e1.cname = c1.name
JOIN Class c2 ON e2.cname = c2.name AND c1.meets_at = c2.meets_at;

b) Disadvantages of SQL:

1. Complexity: SQL queries can become complex, especially when dealing with large and complex databases. Writing and understanding complex SQL queries can be challenging.
2. Scalability: SQL may face scalability issues when dealing with large-scale data processing. As the amount of data grows, performance can decrease, requiring careful optimization
techniques.
3. Lack of standardization: SQL is a standard language, but different database management systems (DBMS) may have their own variations and proprietary extensions. This lack of standardization can make it difficult to write portable SQL code.
4. Limited support for complex data structures: SQL is primarily designed for relational databases and is not well-suited for handling complex data structures, such as hierarchical data or graphs. Working with such data may require additional layers or specialized databases.
5. Learning curve: Mastering SQL and understanding its nuances can take time and effort. It requires knowledge of the specific syntax, query optimization techniques, and understanding the underlying database structure.
6. Performance issues: Poorly designed SQL queries or inefficient database schemas can lead to performance issues. It is essential to optimize queries and maintain the database properly to avoid such problems.
7. Security concerns: SQL injection attacks are a common security vulnerability in web applications that interact with a database. Proper input validation and parameterized queries must be used to prevent these attacks.
8. Limited support for procedural programming: SQL is primarily a declarative language for querying and manipulating data. It has limited support for procedural programming constructs, making it less suitable for complex application logic or algorithm implementation.
9. Lack of version control: SQL databases often lack built-in support for version control, making it difficult to track changes to the database schema or perform rollbacks in case of errors or issues.
10. Vendor lock-in: Moving from one database system to another can be challenging due to vendor-specific SQL dialects and proprietary.

Q3.Consider the relation StudentHostel = (StudID, StudName, StudDOB, HostelNumber. HostelName).

We decompose it into Student = (StudID, StudName, StudDOB), R2 =(StudID, HostelNumber, HostelName).
The set of functional dependencies is:
StudID> {StudName, StudDOB},
{StudDOB.HostelNumber}-> HostelName,
StudName> HostelNumber,
HostelName-> StudID.

a). Verify if this decomposition is a lossless-join decompos
b). Verify if this decomposition is a dependency-preserving decomposition.

Answer:- To verify whether the given decomposition of the relation StudentHostel into Student and R2 is a lossless-join decomposition and a dependency-preserving decomposition, we need to analyze each condition separately.

a) Lossless-Join Decomposition:
A decomposition is considered lossless-join if and only if the natural join of the decomposed relations is equivalent to the original relation, i.e., no information is lost.

In this case, the decomposed relations are Student (StudID, StudName, StudDOB) and R2 (StudID, HostelNumber, HostelName).

To determine if it is lossless-join, we need to check if their natural join results in the original relation:

Student ⨝ R2 = (StudID, StudName, StudDOB) ⨝ (StudID, HostelNumber, HostelName)

The common attribute between the two relations is StudID. When we perform the natural join using StudID, the resulting relation will have attributes (StudID, StudName, StudDOB, HostelNumber, HostelName).

We compare this result with the original relation StudentHostel = (StudID, StudName, StudDOB, HostelNumber, HostelName).

Since the attributes in the natural join match the attributes of the original relation, we can conclude that the decomposition is a lossless-join decomposition.

b) Dependency-Preserving Decomposition:
A decomposition is considered dependency-preserving if all the functional dependencies of the original relation can be enforced using the decomposed relations.

The given set of functional dependencies for the relation StudentHostel is as follows:
StudID → {StudName, StudDOB}
{StudDOB, HostelNumber} → HostelName
StudName → HostelNumber
HostelName → StudID

Let’s check if these functional dependencies hold in the decomposed relations:

In Student (StudID, StudName, StudDOB):

StudID → StudName (This dependency holds as StudID is the primary key of Student.)
In R2 (StudID, HostelNumber, HostelName):

{StudDOB, HostelNumber} → HostelName (This dependency holds as it is directly represented in R2.)
However, the following dependency does not hold in the decomposed relations:

StudName → HostelNumber (This dependency is not represented in the decomposed relations.)
Since one of the functional dependencies is not preserved in the decomposition, we can conclude that this decomposition is not dependency-preserving.

In conclusion:
a) The given decomposition of StudentHostel into Student and R2 is a lossless-join decomposition.
b) The given decomposition is not a dependency-preserving decomposition.

Q4. Having the most recent information on the handling and present location of each dispatched item is something IndiaMart takes great pride in. IndiaMart utilizes a corporate information system to accomplish this. The IndiaMart system for tracking products is built around shipments. Item number (unique), weight, size, insurance amount, location, and date of delivery can classify shipped goods. One retail location serves as the IndiaMart system’s point of entry for shipped goods. Retail centres can be identified by their address, uniquelD, and kind. Items that are shipped travel to their destination via one or more regular IndiaMart transportation methods (such as flights and truck deliveries). These transportation- related events are identified by their distinct scheduleNumber, type (such as truck or flight), and deliveryRoute.Indicating the identifiers and cardinality constraints, draw an ER diagram that captures IndiaMart’s activities.

Answer:-

Shipment:

ItemNumber (unique identifier)
Weight
Size
InsuranceAmount
Location
DateOfDelivery

Retail Centre:

UniqueID (unique identifier)
Address
Kind

Transportation:

ScheduleNumber (unique identifier)
Type
DeliveryRoute

Now, let’s establish the relationships between these entities:

Shipment and Retail Centre:

Each shipment is associated with a retail centre.
Cardinality: One retail centre can have multiple shipments, but each shipment is associated with only one retail centre.
Relationship: Shipment is linked to Retail Centre.

Shipment and Transportation:

Each shipment may involve one or more transportation events.
Cardinality: One shipment can have multiple transportation events, and each transportation event is associated with only one shipment.
Relationship: Shipment is linked to Transportation.

The entities are represented as rectangles, and their attributes are listed within them. The relationships between the entities are shown as lines connecting them. The cardinality constraints are indicated near the relationships.

Q5.Two Entities RRESULT and STUDENT of an application is shown below. (“note. Marks will not be provided for YES/NO)

a) Identify the strong and weak Entity, and state why?
b) Is it possible to delete a tuple of ROCKY in the Student Entity? Justify your answer.
c) Is it possible to enter a tuple with Reg. No. 7 in the RESULT entity? Justify your answer.

Answer:-

a) Strong and Weak Entities:
In the given entities, “STUDENT” is the strong entity, and “RESULT” is the weak entity.
A strong entity is an entity that has its own unique identifier (primary key) and can exist independently. In this case, “STUDENT” has its own unique identifier “Reg No” and can exist without any dependency on other entities.

A weak entity, on the other hand, does not have its own unique identifier and relies on the existence of a related strong entity. In this case, “RESULT” does not have its own unique identifier. It relies on the “Reg No” attribute of the “STUDENT” entity to identify the corresponding results. The “Reg No” attribute in the “RESULT” entity is a foreign key referencing the “Reg No” attribute of the “STUDENT” entity.

b) Deleting a tuple of ROCKY in the Student Entity:
Yes, it is possible to delete a tuple of ROCKY in the Student Entity.
Since “STUDENT” is the strong entity and “ROCKY” is a tuple within that entity, it can be deleted without any issues. However, deleting the tuple of ROCKY will also remove any related tuples in the “RESULT” entity that are associated with that particular Reg No.

c) Entering a tuple with Reg. No. 7 in the RESULT entity:
No, it is not possible to enter a tuple with Reg. No. 7 in the RESULT entity based on the given information.
Looking at the available Reg No values in the “STUDENT” entity (1, 2, 3, 4), there is no Reg No. 7 present. The “RESULT” entity has a foreign key dependency on the Reg No attribute from the “STUDENT” entity. Therefore, to enter a tuple in the “RESULT” entity, the corresponding Reg No must exist in the “STUDENT” entity.

In conclusion:
a) “STUDENT” is the strong entity, and “RESULT” is the weak entity.
b) Yes, it is possible to delete a tuple of ROCKY in the Student Entity.
c) No, it is not possible to enter a tuple with Reg. No. 7 in the RESULT entity.

Q6.a.Normalize below Relation in 1NF, 2NF, 3NF

Student | Advisor | Adv-room | Class1 | Class 2 | Class3
1022 | Jones | 412 | 107-07 | 143 | 159-02
4123 | Smith | 216 | 201-01 | 322 | 214-01

b.Suppose a following stu_project relation schema:

Identify functional dependencies in above table and find out that relation is in 2NF or not? If not decompose it in 2NF.(A student can work on many projects and a project can have many students associated with it.)

Answer:- A. To normalize the given relation into 1NF, 2NF, and 3NF, we need to analyze the functional dependencies and identify the primary key.

The given relation appears to represent information about students, their advisors, assigned rooms, and their enrolled classes. Let’s identify the functional dependencies:

Functional dependencies:

Student → Advisor, Adv-room
Advisor → Adv-room
Student → Class1, Class2, Class3
Primary key:
The primary key is not explicitly mentioned in the given relation. However, assuming Student ID is the unique identifier for each student, we can consider Student as the primary key.

Normalized relation in 1NF:
Student (PK), Advisor, Adv-room, Class1, Class2, Class3

In 1NF, we have placed all the attributes in a single table, ensuring that there are no repeating groups or multivalued attributes.

Normalized relation in 2NF:
Student (PK), Advisor (FK), Adv-room
Advisor (PK), Adv-room

In 2NF, we have eliminated partial dependencies by creating a separate table for Advisor and its corresponding Adv-room. This ensures that each table is about a single entity or concept.

Normalized relation in 3NF:
Student (PK), Advisor (FK)
Advisor (PK), Adv-room
Class (PK), Course

In 3NF, we have removed transitive dependencies by separating the Class information into a new table called “Class” with its own primary key (Class). This way, we have achieved a higher level of normalization.

B. Functional dependencies:

Stu_id → Stu_name
Proj_id → Proj_name, Proj_lang_used
These dependencies can be identified based on the unique values of Stu_id and Proj_id, which determine the corresponding Stu_name, Proj_name, and Proj_lang_used attributes.

Now, let’s determine if the relation is in 2NF or not. For a relation to be in 2NF, it should meet the following criteria:

It must be in 1NF.
There should be no partial dependencies, i.e., non-prime attributes should not depend on part of a candidate key.
In the given relation, we have the following candidate keys:

{Stu_id, Proj_id}
By analyzing the functional dependencies, we find that Proj_name and Proj_lang_used are dependent only on Proj_id, which is part of the candidate key. However, Stu_name is dependent on Stu_id, which is a proper subset of the candidate key {Stu_id, Proj_id}.

Therefore, the relation is not in 2NF because Stu_name is a non-prime attribute that depends on part of a candidate key.

To decompose the relation into 2NF, we can split it into two separate relations:

Relation 1: stu_details
Stu_id (PK)
Stu_name
Relation 2: proj_details
Proj_id (PK)
Proj_name
Proj_lang_used
These two relations eliminate the partial dependency and satisfy the conditions of 2NF. Each relation now represents a single concept, and the functional dependencies are properly maintained.

For More Updates Join Our Channels :