.jpg)
1. Define foreign key.
A foreign key is a field (or a set of fields) in one table that uniquely identifies a row of another table. It establishes a link between the data in the two tables, ensuring referential integrity.
2. What is a conflict in a transaction?
A conflict in a transaction occurs when two or more transactions simultaneously access the same data and at least one of them modifies it, leading to potential inconsistencies.
3. What do you mean by functional dependency?
A functional dependency in a database is a relationship between two sets of attributes, typically within a table, where one set of attributes (the determinant) uniquely determines another set of attributes. For example, if A determines B, then B is functionally dependent on A.
4. Define relationship.
In a database, a relationship is an association between two or more tables based on common fields. It defines how data in one table is related to data in another, often established using primary and foreign keys.
5. What is granularity?
Granularity in databases refers to the size or level of detail of the data items that are being managed, often in the context of concurrency control and locking mechanisms. It indicates the extent of data that a lock can cover, such as rows, pages, or entire tables.
6. What is locking in DBMS.
6. What is locking in DBMS.
Locking in DBMS is a mechanism to control concurrent access to data by multiple transactions. It ensures data integrity and consistency by preventing conflicts, such as two transactions modifying the same data simultaneously.
7. Define view in DBMS.
A view in DBMS is a virtual table that provides a way to present data from one or more tables. It is defined by a query and does not store data itself, but dynamically retrieves data from the underlying tables whenever it is accessed.
8. What is a serial schedule?
8. What is a serial schedule?
A serial schedule in DBMS is a sequence of operations (transactions) in which each transaction is executed completely before the next transaction begins. In other words, transactions are executed one after another in a sequential manner, without any interleaving or parallel execution. This ensures that transactions are isolated from each other and do not overlap in terms of their execution time.
9. Discuss the 3-tier ANSI/SPARC Database Architecture Along with Its Advantages.
1. Internal Level:
- Description: Describes how the data is stored physically on the storage devices.
- Details: Includes data structures, indexing, and file organization.
2. Conceptual Level:
- Description: Provides a unified view of the entire database, independent of how data is stored.
- Details: Represents all entities, their attributes, and relationships.
3. External Level:
- Description: Describes how users interact with the database.
- Details: Contains multiple user views, tailored to different user needs.
Advantages:
1. Data Abstraction: Separates user applications from physical database details.
2. Data Independence: Supports logical and physical data independence.
3. Security: Allows different levels of security for different users.
4. Simplified Database Design: Facilitates database design by abstracting complex details.
5. Improved Maintenance: Eases database maintenance and management due to clear separation of levels.
11. What Do You Mean by Weak and Strong Entity Sets? Discuss with Example.
Strong Entity Set:- An entity set that has a primary key and does not depend on any other entity for its identification.
- Example: `Student` entity with `StudentID` as primary key.
Weak Entity Set:- An entity set that does not have a primary key and relies on a strong entity set for its identification. It has a partial key and is identified by being related to another entity.
- Example: `Dependent` entity related to `Employee` entity. `Dependent` cannot be uniquely identified without `Employee`.
- `Employee (EmployeeID, Name)`
- `Dependent (DependentName, EmployeeID)` where `EmployeeID` is a foreign key linking to `Employee`.
12. Discuss Data Independence with Suitable Examples.
Definition: The capacity to change the schema at one level without affecting the schema at the next higher level.
- Types:
1. Logical Data Independence:
- Definition: Ability to change the conceptual schema without altering the external schema or application programs.
- Example: Adding a new field to a table without changing user views.
2. Physical Data Independence:
- Definition: Ability to change the internal schema without changing the conceptual schema.
- Example: Changing file organization or indexing methods without affecting the database's logical structure.
Example:
- Scenario: Suppose we have a `Customer` table:
- Initial schema: `Customer (CustomerID, Name, Address)`
- Logical Change: Adding a `PhoneNumber` field: `Customer (CustomerID, Name, Address, PhoneNumber)`. User applications remain unaffected.
- Physical Change: Changing storage from B-tree indexing to hash indexing. The conceptual schema remains unchanged.
13. Explain the Advantages of DBMS Over File-Oriented Systems.
1. Data Redundancy and Inconsistency Reduction:
- Explanation: Centralized control of data reduces data duplication and ensures data consistency.
- Example: Customer data stored once and shared across applications, unlike multiple copies in file-based systems.
2. Data Integrity and Security:
- Explanation: DBMS enforces integrity constraints and security measures.
- Example: Constraints like primary keys and foreign keys ensure valid data, while user roles and permissions control access.
3. Data Sharing and Concurrent Access:
- Explanation: Multiple users can access and manipulate data simultaneously.
- Example: Banking applications allow multiple users to access account information concurrently without conflicts.
4. Backup and Recovery:
- Explanation: DBMS provides automated backup and recovery mechanisms.
- Example: Periodic backups and transaction logs enable data recovery in case of failures.
5. Efficient Data Access:
- Explanation: DBMS uses indexing and query optimization for faster data retrieval.
- Example: SQL queries with optimized indexes retrieve data much faster compared to sequential file searches.
15. Discuss Different Constraints Encountered in Database Design.
Database Constraints: Constraints ensure data integrity and consistency in a database. They are rules applied to table columns to enforce valid data entry and relationships.
1. Domain Constraints:
- Description: Restrict the values for a column to a specific domain (data type, range, or format).
- Example: A column `age` might be restricted to integer values between 0 and 120.
CREATE TABLE Person (age INT CHECK (age >= 0 AND age <= 120)); (code-box)
2. Entity Integrity Constraints:
- Description: Ensure that each entity (row) in a table is uniquely identifiable.
- Primary Key: A unique identifier for table rows.
- Example: `CustomerID` in a `Customers` table must be unique and not null.
CREATE TABLE Customers (CustomerID INT PRIMARY KEY,Name VARCHAR(100)); (code-box)
3. Referential Integrity Constraints:
- Description: Maintain consistency among rows of two related tables. Ensures that a foreign key value in one table matches a primary key value in another table.
- Example: `OrderID` in `Orders` table references `CustomerID` in `Customers` table.
CREATE TABLE Orders (OrderID INT PRIMARY KEY,CustomerID INT,FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)); (code-box)
4. Unique Constraints:
- Description: Ensure all values in a column (or a set of columns) are distinct.
- Example: A column `email` in a `Users` table must be unique.
CREATE TABLE Users (UserID INT PRIMARY KEY,email VARCHAR(100) UNIQUE); (code-box)
5. Not Null Constraints:
- Description: Ensure that a column cannot have null values.
- Example: `Name` column in `Employees` table must not be null.
CREATE TABLE Employees (EmployeeID INT PRIMARY KEY,Name VARCHAR(100) NOT NULL); (code-box)
6. Check Constraints:
- Description: Ensure that the values in a column satisfy a specified condition.
- Example: A column `salary` must be greater than zero.
CREATE TABLE Jobs (JobID INT PRIMARY KEY,salary DECIMAL CHECK (salary > 0)); (code-box)
7. Default Constraints:
- Description: Assign default values to a column when no value is specified.
- Example: The `status` column in an `Orders` table defaults to 'Pending'.
CREATE TABLE Orders (OrderID INT PRIMARY KEY,status VARCHAR(20) DEFAULT 'Pending'); (code-box)
16. Discuss Different Relational Algebra Operations.
Relational Algebra is a procedural query language for relational databases. It uses operators to manipulate relations (tables).
1. Selection (σ):
- Description: Selects rows that satisfy a given predicate.
- Syntax: `σ_condition(R)`
- Example: Select employees older than 30.
σ_age > 30(Employees) (code-box)
2. Projection (π):
- Description: Selects specific columns from a relation.
- Syntax: `π_column1, column2,...(R)`
- Example: Select names and salaries of employees.
π_name, salary(Employees) (code-box)
3. Union (∪):
- Description: Combines tuples from two relations, removing duplicates.
- Syntax: `R ∪ S`
- Example: Combine two sets of employees.
Employees1 ∪ Employees2 (code-box)
4. Set Difference (−):
- Description: Returns tuples in one relation but not in another.
- Syntax: `R − S`
- Example: Employees in `Employees1` but not in `Employees2`.
Employees1 − Employees2 (code-box)
5. Cartesian Product (×):
- Description: Combines tuples from two relations into all possible pairs.
- Syntax: `R × S`
- Example: Combine employees and departments.
Employees × Departments (code-box)
6. Rename (ρ):
- Description: Renames the output relation or its attributes.
- Syntax: `ρ_newname(R)` or `ρ_newname(A1, A2,...)(R)`
- Example: Rename relation `Employees` to `Staff`.
ρ_Staff(Employees) (code-box)
7. Join (⋈):
- Description: Combines related tuples from two relations based on a condition.
- Syntax: `R ⋈_condition S`
- Example: Join employees and departments on `DeptID`.
Employees ⋈_Employees.DeptID = Departments.DeptID Departments (code-box)
8. Intersection (∩):
- Description: Returns tuples common to both relations.
- Syntax: `R ∩ S`
- Example: Employees common to two departments.
Employees1 ∩ Employees2 (code-box)
9. Division (÷):
- Description: Returns tuples in one relation that are associated with all tuples in another relation.
- Syntax: `R ÷ S`
- Example: Find employees who work on all projects.
Employees ÷ Projects (code-box)
17. Explain Hierarchical, Network, and Relational Data Models.
Hierarchical Data Model:
- Description: Organizes data in a tree-like structure with parent-child relationships.
- Structure: Each parent can have multiple children, but each child has only one parent.
- Example: An organizational chart with departments and employees.
- Advantages: Simple and easy to understand, efficient for certain queries.
- Disadvantages: Lack of flexibility, difficult to model complex relationships.
Network Data Model:
- Description: Organizes data using a graph structure with many-to-many relationships.
- Structure: Records are nodes, and relationships are edges.
- Example: A social network where users are connected to multiple friends.
- Advantages: More flexible than the hierarchical model, supports complex relationships.
- Disadvantages: More complex to design and manage, requires careful handling of pointers and links.
Relational Data Model:
- Description: Uses tables (relations) to organize data, with rows representing records and columns representing attributes.
- Structure: Data is stored in tables, and relationships are represented by foreign keys.
- Example: A customer database with `Customers` and `Orders` tables.
- Advantages: Flexibility, simplicity, supports powerful query languages (SQL).
- Disadvantages: Can be less efficient for certain types of queries, relies heavily on join operations.
Comparison and Conclusion:
- Hierarchical Model: Best for simple, hierarchical data structures but inflexible for complex relationships.
- Network Model: Suitable for complex relationships but more complex to manage.
- Relational Model: Offers the most flexibility and simplicity, widely used in modern databases.
.jpg)