Group-A
Answer ALL questions:
* (a) A schema contains the structure of the database.
(b) Hierarchical network & relational data models are record-based data models.
(c) A database is an organized collection of facts.
(d) The various kinds of data that describe an entity are known as its attributes.
(e) An ER model uses a double outlined rectangle to represent a weak entity set.
(f) An internal schema describes the physical storage structure of the database.
(g) A deadlock is a situation in which two or more transactions are in a simultaneous wait
state.
(h) OODBMS stands for Object-Oriented Database Management System.
Group-B
Answer any EIGHT within TWO to THREE sentences:
* (a) What is the role of External level in three-schema architecture?
The external level, or view level, describes the portion of the database that a particular user
sees. It provides a customized view of the database, abstracting away unnecessary details and
presenting only the data relevant to that user's specific tasks.
(b) Explain 3NF.
Third Normal Form (3NF) is a database normalization technique that requires a relation to be in
2NF and have no transitive dependencies. A transitive dependency exists when a non-key
attribute is dependent on another non-key attribute, rather than directly on the primary key.
(c) Write the components of ER-model.
The key components of an Entity-Relationship (ER) model are Entities, which represent real-
world objects or concepts; Attributes, which describe the properties of entities; and
Relationships, which show how entities are associated with each other.
(d) Define super key.
A super key is a set of one or more attributes that, taken collectively, can uniquely identify a
tuple (row) in a relation. A candidate key is a minimal super key, meaning it has no redundant
attributes.
(e) What is Time Stamp? How can they be generated?
A timestamp is a unique identifier assigned to a transaction that indicates its start time. They
can be generated using a system clock or a logical counter that is incremented each time a new
transaction begins, ensuring each transaction has a unique timestamp.
(f) Explain DBA.
DBA stands for Database Administrator. A DBA is a person or team responsible for managing
and maintaining a database system. Their duties include ensuring data security, performance,
integrity, and availability, as well as installing and configuring the database software.
(g) What is Relational Model?
The relational model organizes data into two-dimensional tables called relations. Each table
consists of rows (tuples) and columns (attributes), where each tuple represents a record and
each attribute represents a specific characteristic.
(h) What is deletion anomalies?
A deletion anomaly occurs when deleting a record from a table also unintentionally removes
other important information. This happens when multiple facts are stored in the same row, and
deleting a specific fact leads to the loss of other unrelated data.
(i) Define SQL.
SQL stands for Structured Query Language. It is a standard language used to interact with
relational databases. SQL allows users to create, modify, and manage database schemas and
to query, insert, update, and delete data.
(j) What is the meaning of functional dependency?
A functional dependency is a relationship between attributes in a relation where the value of one
attribute (the determinant) uniquely determines the value of another attribute (the dependent). It
is expressed as X → Y, meaning X functionally determines Y.
Group-C
Answer any EIGHT from the following within 75 words.
* (a) Differentiate between Relational data model and network data model.
| Feature | Relational Data Model | Network Data Model |
|---|---|---|
| Structure | Organizes data in tables (relations) with rows and columns. | Organizes data in a
graph-like structure of records and sets. |
| Relationship | Relationships are defined by common attribute values (foreign keys). |
Relationships are defined by pointers, with explicit links between records. |
| Navigation | Data is accessed via SQL queries; navigation is logical. | Data is accessed by
navigating explicit links, often leading to complex queries. |
| Flexibility | Highly flexible and easy to modify schema. | Less flexible; changing the schema
is difficult due to explicit links. |
(b) Explain the characteristics of SQL.
SQL is a declarative language, allowing users to specify what data they want without detailing
how to retrieve it. It is also an interactive language for executing commands. SQL is a
comprehensive language that combines data definition (DDL), data manipulation (DML), and
data control (DCL) functionalities. It is the standard for relational databases, ensuring portability
and widespread use.
(c) What is a data model? Briefly explain about the popular data models.
A data model is a conceptual tool used to describe the structure of a database, including the
data, relationships between data, and constraints. Popular data models include the Relational
model, which uses tables; the Network model, which uses a graph structure; the Hierarchical
model, which uses a tree structure; and the Object-Oriented model, which uses objects.
(d) Explain multivalued dependency.
A multivalued dependency (MVD) exists when an attribute determines multiple values of
another attribute independently of other attributes in the relation. For example, in a table with
Student_ID, Course, and Hobby, a student might have multiple courses and multiple hobbies,
and the set of hobbies is independent of the set of courses. This is denoted as A →→ B.
Group-D
Answer all questions:
* What is database model? Describe the types of database models. (4+6)
A database model is a theoretical framework that defines the logical structure of a database,
dictating how data is stored, organized, and manipulated. It determines the rules and
relationships that govern the data within the system.
The types of database models are:
* Hierarchical Model: Organizes data in a tree-like structure. Data is stored in records
connected by links, with a parent-child relationship where each child has only one parent. An
example is an organizational chart.
* Network Model: An extension of the hierarchical model, it allows a record to have multiple
parent records. This forms a graph-like structure, offering more flexible relationships than the
hierarchical model.
* Relational Model: The most widely used model today. Data is organized into two-
dimensional tables called relations, with rows (tuples) and columns (attributes). Relationships
are established using common fields, known as foreign keys.
* Object-Oriented Model: Treats data as objects, similar to object-oriented programming. It
stores complex data types and relationships in a single object structure, encapsulating both data
and behavior.
* Entity-Relationship (ER) Model: A high-level conceptual data model that describes data as
entities, attributes, and relationships. It is often used for database design and is visualized using
ER diagrams.
* What is an attribute? Explain all types of attributes with graphical notation. (8 + 2)
An attribute is a characteristic or property that describes an entity. It defines a specific piece of
information for each instance of an entity. For example, Name, Age, and Address can be
attributes of a Student entity.
Types of attributes:
* Simple Attribute: An attribute that cannot be broken down into smaller components. For
example, Age.
* Composite Attribute: An attribute composed of several simple attributes. For example,
Address can be composed of Street, City, and Zip Code.
* Derived Attribute: An attribute whose value is calculated from other attributes in the
database. It is not stored explicitly. For example, Age can be derived from the Date_of_Birth
attribute.
* Single-valued Attribute: An attribute that holds a single value for a given entity. For example,
a person's Social_Security_Number.
* Multivalued Attribute: An attribute that can have multiple values for a single entity instance.
For example, a Student can have multiple Phone_Numbers.
Graphical Notation:
* Simple: represented by a simple oval.
* Composite: represented by an oval connected to other ovals representing its components.
* Derived: represented by a dashed or dotted oval.
* Multivalued: represented by a double oval.
OR
Explain about aggregate functions in SQL with examples. (6)
Aggregate functions in SQL operate on a set of rows and return a single value. They are often
used with the GROUP BY clause to summarize data.
* COUNT(): Returns the number of rows in a set.
SELECT COUNT(emp_id) FROM Employees;
* SUM(): Calculates the sum of values in a column.
SELECT SUM(salary) FROM Employees;
* AVG(): Computes the average of values in a column.
SELECT AVG(salary) FROM Employees;
* MIN(): Returns the minimum value in a column.
SELECT MIN(age) FROM Employees;
* MAX(): Returns the maximum value in a column.
SELECT MAX(salary) FROM Employees;
* Explain operations of relational algebra along with their syntaxes. (4)
Relational algebra is a procedural query language that takes relations as input and produces
relations as output.
* Select (σ): Selects a subset of tuples from a relation that satisfies a selection condition.
* Syntax: σ <condition> (R)
* Project (π): Projects a relation on a set of attributes, removing columns that are not in the
projection list.
* Syntax: π <attribute_list> (R)
* Union (∪): Combines the tuples of two relations that have the same number of attributes and
compatible domains.
* Syntax: R ∪ S
* Set Difference (-): Returns the tuples that are in one relation but not in the other.
* Syntax: R - S
* Cartesian Product (x): Combines every tuple of one relation with every tuple of another
relation.
* Syntax: R x S
* Rename (ρ): Renames the output relation or its attributes.
* Syntax: ρ <new_name> (R)
OR
Define BCNF with suitable examples. (4)
Boyce-Codd Normal Form (BCNF) is a stricter version of 3NF. A relation is in BCNF if and
only if for every non-trivial functional dependency X → Y, X is a super key. This means that a
table is in BCNF if every determinant (the left side of a functional dependency) is a candidate
key.
* Example: Consider a table Student_Course_Advisor with attributes (Student_ID, Course,
Advisor).
* Student_ID → Course (A student can only take one course)
* Course → Advisor (Each course has only one advisor)
* Student_ID is a candidate key, so the table is in 3NF.
* However, Course is a determinant but not a super key, violating BCNF. To resolve this, we
decompose it into two tables: Student_Course (Student_ID, Course) and Course_Advisor
(Course, Advisor).
* Discuss the two-phase locking protocol. What benefits are provided by strict two-phase
locking? What are its disadvantages? (4)
The Two-Phase Locking (2PL) protocol is a concurrency control method that ensures
serializability by regulating lock acquisition and release. It has two phases:
* Growing Phase: A transaction can acquire new locks but cannot release any.
* Shrinking Phase: A transaction can release existing locks but cannot acquire any new ones.
Benefits of Strict Two-Phase Locking (Strict 2PL):
* It is a more restrictive version of 2PL, where all exclusive (write) locks are held until the
transaction commits or aborts.
* This prevents other transactions from reading or writing data that has been updated by an
uncommitted transaction, thereby preventing a cascade of rollbacks.
* It guarantees strict serializability, making it a robust concurrency control method.
Disadvantages of 2PL:
* It can lead to deadlocks, where two or more transactions are in a mutual waiting state for
locks held by each other.
* It can reduce concurrency by holding locks longer than necessary, especially in the shrinking
phase.
OR
What do you understand by the term concurrency? How can we avoid deadlock? (4)
Concurrency refers to the ability of a database management system (DBMS) to execute
multiple transactions simultaneously. This improves system throughput and resource utilization,
as transactions can be interleaved and processed in parallel, rather than waiting for each other
to complete sequentially.
Deadlock avoidance can be achieved using various methods:
* Deadlock Prevention: The system is designed to prevent deadlocks from occurring in the
first place.
* Wait-Die Scheme: If an older transaction (with a smaller timestamp) needs a lock held by a
younger transaction, the older one waits. If a younger one needs a lock held by an older one,
the younger one aborts and restarts.
* Wound-Wait Scheme: If an older transaction needs a lock held by a younger one, the
younger one is "wounded" (aborts and restarts). If a younger one needs a lock held by an older
one, the younger one waits.
* Deadlock Detection and Recovery: The system allows deadlocks to happen, detects them,
and then recovers by aborting one or more transactions to break the cycle. The system
maintains a wait-for graph to detect cycles.
