Database Administration MSBTE III Sem

Unit 5

 

Summer 2013

Q1. State types of database User. (2 Marks)

Ans. The main types of database users are:

1. Database Administrators (DBAs) - Manage and maintain the database system.
2. Application Programmers - Develop applications that interact with the database.
3. End Users - Use the database through applications (e.g., casual, naive, or sophisticated users).
4. Database Designers - Design the structure and schema of the database.

 

Q2. State the concept of database recovery. (4 Marks)

Ans. Database recovery refers to the process of restoring a database to a consistent and operational state after a failure or error. Failures can occur due to hardware malfunctions, software bugs, power outages, or human errors, which may lead to data loss or corruption.

Key Points:

1.      Purpose:

• To ensure data integrity and consistency.
• To minimize data loss and downtime.
• To restore operations quickly.

2.      Types of Failures:

• Transaction Failure: Issues during a specific transaction (e.g., deadlock, invalid operations).
• System Failure: Crashes affecting the database but not the disk storage (e.g., power outages).
• Media Failure: Disk corruption or hardware damage.

3.      Recovery Techniques:

• Backup and Restore: Using backups to restore the database.
• Transaction Logs: Rolling forward committed transactions and rolling back incomplete ones.
• Checkpointing: Periodically saving the database state to reduce recovery time.

4.      ACID Compliance: Database recovery ensures Atomicity and Durability from ACID properties by guaranteeing that either all operations in a transaction are completed or none are, and that changes from committed transactions persist.

Q3. With neat sketch describe Transaction states diagram. (4 

Marks)

Ans. Transaction States Diagram

A transaction in a database management system (DBMS) goes through various states during its lifecycle. Here's a diagram illustrating these states and their transitions:

Explanation of States:

1. Active State:
• Initial state of a transaction.
• Transaction is being executed.
• If successful, it moves to the Partially Committed state.
• If an error occurs, it transitions to the Failed state.
2. Partially Committed State:
• Transaction has completed its execution.
• Changes are made to the database but not yet permanently stored.
• If the commit operation is successful, it moves to the Committed state.
• If an error occurs during the commit process, it transitions to the Failed state.
3. Committed State:
• Transaction is successfully completed.
• Changes are permanently stored in the database.
• Transaction is terminated.
4. Failed State:
• Transaction encounters an error and cannot be completed.
• The system rolls back the transaction, undoing all changes made by the transaction.
• Transaction is terminated.
5. Aborted State:
• Transaction is intentionally terminated before completion.
• The system rolls back the transaction, undoing all changes made by the transaction.
• Transaction is terminated.

Transition Rules:

• A transaction can only move from the Active state to the Partially Committed state if all operations are executed successfully.
• A transaction can only move from the Partially Committed state to the Committed state if the commit operation is successful.
• A transaction can move to the Failed state from either the Active or Partially Committed state if an error occurs.
• A transaction can move to the Aborted state from the Active state if it is explicitly terminated by the system or the user.

 

Q4. Write SQL commands for Create user RAM Grant create, 

select, privileges to RAM () Remove update privilege from user 

RAM - (4 Marks)

Ans.

CREATE USER RAR IDENTIFIED BY password:

-- Create a new user named RAM with the specified privileges

CREATE USER RAM WITH PASSWORD 'your_password';

GRANT CREATE SESSION TO RAM

 -- Grant CREATE and SELECT privileges to RAM

GRANT CREATE, SELECT ON YOUR_DATABASE_NAME TO RAM;

GRANT CREATE TABLE TO RAM: GRANT SELECT ANY

TABLE TO RAM

GRANT INSERT ANY TABLE TO RAM GRANT UPDATE ANY 

TABLE TO RAM

GRANT DELETE ANY TABLE TO RAM

-- Revoke the UPDATE privilege from RAM

REVOKE UPDATE ON YOUR_DATABASE_NAME FROM RAM;

REVOKE UPDATE ANY TABLE FRON RAM

Replace the following:

• YOUR_DATABASE_NAME: Replace this with the actual name of your database.
• your_password: Replace this with a strong password for the user RAM.

Important Note:

• Password Security: Always use strong, complex passwords to protect your database and user accounts.
• Privilege Management: Be cautious when granting privileges. Grant only the necessary privileges to users to minimize the risk of unauthorized access and data manipulation.
• Regular Review: Periodically review and update user privileges to ensure they remain appropriate and secure.
• Consult Your DBMS Documentation: Refer to your specific database management system's documentation for exact syntax and additional options related to user management and privilege control.

Winter 2022

Q5. Explain ACID properties of transaction. (4 Marks)

Ans. The ACID properties ensure the reliability, consistency, and integrity of database transactions, especially in multi-user and failure-prone environments. They are:

1. Atomicity:
• Ensures a transaction is an indivisible unit. Either all its operations are performed, or none are.
• Example: In a fund transfer, money is either debited and credited completely, or no changes occur if an error happens.

2. Consistency:
• Guarantees that the database transitions from one valid state to another.
• Example: A transaction must maintain the integrity rules, such as ensuring the total balance remains consistent in a banking system.

3. Isolation:
• Ensures that concurrent transactions do not interfere with each other.
• Example: Two users updating the same account won't see each other's changes until the first transaction is completed.

4. Durability:
• Ensures that once a transaction is committed, its changes are permanent, even in the event of a system failure.
• Example: After a successful purchase, the order details remain saved despite a server crash.

These properties collectively maintain database reliability and prevent data corruption.

Q6. Explain Database Recovery techniques in detail. (4 Marks)

Ans. Database Recovery Techniques 

Database recovery techniques restore a database to a consistent state after a failure. These techniques ensure minimal data loss and maintain database integrity.

1. Backup and Restore

• Description: Regular backups of the database are taken and used to restore the database in case of failure.
• Process:
• Restore the most recent backup.
• Apply transaction logs (if available) to recover the most recent state.
• Use Case: Disk failures or accidental deletion of data. 

2. Checkpointing

• Description: The database periodically saves a snapshot of its current state (called a checkpoint).
• Process:
• During recovery, transactions before the last checkpoint are ignored.
• Transactions after the checkpoint are replayed using logs.
• Use Case: Reduces recovery time by avoiding unnecessary redo operations.

3. Log-Based Recovery

• Description: Uses transaction logs to recover the database. Logs record every change made by transactions.
• Types:
• Redo Logging: Reapplies committed changes.
• Undo Logging: Reverts uncommitted changes.
• Use Case: System crashes or transaction failures.

4. Shadow Paging

• Description: Maintains two copies of the database – the current page table and a shadow page table.
• Process:
• Changes are made to the current page table.
• The shadow page table remains unchanged.
• If a failure occurs, the shadow page table is used for recovery.
• Use Case: Ensures quick recovery without log replay.

5. ARIES (Algorithm for Recovery and Isolation Exploiting Semantics)

• Description: A sophisticated recovery algorithm that supports undo and redo operations using logs and checkpoints.
• Process:
• Performs analysis, redo, and undo phases during recovery.
• Use Case: Modern databases with high performance requirements.
  
  

Summer 2023

Q7. Explain properties of transaction. (2 Marks)

Ans. The ACID properties of transactions ensure reliability and data integrity in SQL databases:

1. Atomicity: A transaction is treated as a single unit. All operations must complete successfully, or none are applied.
• Example: In a bank transfer, money is debited and credited fully or not at all.
2. Consistency: Ensures the database transitions from one valid state to another, maintaining rules and constraints.
• Example: Inventory and sales data remain consistent after a purchase.
3. Isolation: Ensures transactions execute independently without interference from others.
• Example: Two users updating the same record do not see each other’s changes until committed.
4. Durability: Guarantees that committed changes are permanent, even in case of system failure.
• Example: A ticket booking remains valid after a system crash.

Q8. Explain the most common types of database failures for (4 Marks)

Ans. Database failures can occur due to various reasons, and understanding the types of failures is crucial for implementing effective recovery techniques. The most common types of database failures are:

1. Transaction Failures

• Description: Occurs when a transaction cannot be completed successfully due to errors like invalid operations, constraint violations, or logical errors in SQL statements.
• Example: A transaction fails if a bank transfer attempts to withdraw more money than the available balance.

2. System Failures

• Description: Happens when the database system or server crashes due to hardware or software malfunctions (e.g., power outages, memory leaks, or deadlocks). The database might be partially updated when the failure occurs.
• Example: A server crash while processing multiple transactions can cause data inconsistencies or loss.

3. Media Failures

• Description: Refers to hardware-related failures that affect the storage devices (e.g., hard disk crashes, data corruption on storage media). These failures cause the database to become inaccessible or the data to become corrupted.
• Example: A disk failure leading to the loss of critical database files.

4. Concurrency Failures

• Description: Occurs when concurrent transactions interfere with each other, leading to issues like dirty reads, non-repeatable reads, or lost updates. This usually happens when isolation levels are not properly maintained.
• Example: Two users trying to update the same record simultaneously, causing data corruption or inconsistent results.

Winter 2023 

Q9. Define failure. Enlist types of failure. (2 Marks)

Ans. Database Failure

A database failure occurs when the database system or its components fail to operate as expected, leading to data inconsistency, inaccessibility, or corruption.

Types of Database Failures:

1. Transaction Failure: Occurs when a transaction cannot complete due to errors such as invalid operations or constraint violations.
2. System Failure: Happens when the database system crashes due to hardware or software malfunctions.
3. Media Failure: Refers to the failure of storage devices (e.g., disk crashes) that result in data loss or corruption.
4. Concurrency Failure: Occurs when concurrent transactions interfere with each other, leading to issues like data corruption or inconsistent results.

Q10. Describe ACID properties of transaction. (2 Marks)

Ans. The ACID properties ensure the integrity and reliability of transactions in a database:

1. Atomicity: Ensures that a transaction is fully completed or not executed at all. If a transaction fails, all changes are rolled back.
• Example: If money is transferred between two accounts, either both the debit and credit happen, or neither does.
2. Consistency: Ensures that a transaction brings the database from one valid state to another, maintaining integrity constraints (e.g., primary keys, foreign keys).
• Example: A transaction that violates business rules (e.g., negative balances) will not complete.
3. Isolation: Ensures that concurrent transactions do not interfere with each other and that intermediate transaction states are not visible to others.
• Example: Two users updating the same record will not see each other’s changes until one transaction is complete.
4. Durability: Guarantees that once a transaction is committed, its changes are permanent, even in the event of a system crash.
• Example: After a successful purchase, the changes are saved permanently, even if the system crashes immediately after.

Q11. Describe database privileges. Write down the command for granting and revoking privileges on database object to users.

Ans. Database privileges are permissions that determine what operations a user or role can perform on database objects. They control access to the resources within the database, such as tables,  views, procedures, etc. Privileges can be granted or revoked by database administrators or users with appropriate privileges.

Types of Database Privileges:

1. System Privileges: These are high-level privileges that allow users to perform administrative tasks, such as creating and managing database objects or users.
• Examples: CREATE USER, DROP USER, CREATE DATABASE, ALTER USER, GRANT, REVOKE.
2. Object Privileges: These privileges control access to specific database objects (tables, views, procedures, etc.) and define what operations can be performed on those objects.
• Common Object Privileges:
• SELECT: Allows reading data from a table or view.
• INSERT: Allows inserting new data into a table.
• UPDATE: Allows modifying existing data in a table.
• DELETE: Allows deleting data from a table.
• EXECUTE: Allows executing stored procedures or functions.
• ALTER: Allows modifying the structure of a table or other database object (e.g., adding/removing columns).
• DROP: Allows deleting a table, view, or other database objects.
• REFERENCES: Allows creating foreign key constraints referencing a table.

Granting and Revoking Privileges

The GRANT and REVOKE statements are used to assign and remove privileges for database objects.

1. Granting Privileges

The GRANT command is used to assign specific privileges on database objects to a user or role. It can also grant the ability to further grant the privileges to other users using the WITH GRANT OPTION.

Syntax:

sql

Example: Granting SELECT and INSERT privileges on the employees table to the user john:

sql

Example with WITH GRANT OPTION: Allowing john to further grant SELECT and INSERT privileges to others:

sql

2. Revoking Privileges

The REVOKE command is used to remove privileges from a user. This command can be used to revoke all privileges or specific ones.

Syntax:

sql

Example: Revoking the INSERT privilege from the user john on the employees table:

sql

Example to Revoke All Privileges: Revoking all privileges on the employees table from john:

sql

Key Points:

• GRANT: Assigns privileges to a user, allowing them to perform specific actions on database objects.
• REVOKE: Removes previously granted privileges from a user, restricting their ability to perform certain actions on database objects.
• Privileges can be granted at both the object level (e.g., tables, views) and system level (e.g., creating users, altering database schema).

These privilege management commands ensure secure and controlled access to the database.

Summer 2024 

Q12. List system privileges and object privileges

Ans. System Privileges and Object Privileges (2 Marks)

System Privileges: These control administrative tasks and high-level operations in the database.

1. CREATE USER
2. DROP USER
3. ALTER USER
4. CREATE TABLE
5. DROP TABLE
6. GRANT
7. REVOKE

Object Privileges: These control access to specific database objects like tables, views, and procedures.

1. SELECT
2. INSERT
3. UPDATE
4. DELETE
5. EXECUTE
6. ALTER
7. DROP 

Q13. Write SQL command for following:

(1) Create user

(ii) Grant privileges to user

(iii) Remove privileges from user

Ans

(i) Create user

CREATE USER <username> IDENTIFIED BY <password>,

OR

CREATE USER RAJ IDENTIFIED BY RAJ123;

(ii) Grant privileges to user.

GRANT <privilege list> ON <relation name or view name>

TO<user list>

OR

(assuming table Employee for granting permissions to user 'RAJ' for select, insert, update and delete privilege).

GRANT SELECT, INSERT, UPDATE, DELETE ON EMPLOYEE TO RAJ

(iii) Remove privileges from user.

REVOKE <privilege list> ON <relation name or view name >

FROM <user list>;

OR (assuming table Employee for revoking permissions to user 'RAJ)

REVOKE SELECT, INSERT, UPDATE, DELETE ON EMPLOYEE FROM RAJ;

Relational Data Model MSBTE III Sem

 Unit 2 

Summer 2022

Q1. State the components used in E-R diagram. 2Marks

Ans. The components used in an Entity-Relationship (E-R) diagram are:

·       Entities: These represent objects or things in the real world that have a distinct existence. An entity can be a person, place, object, or event. In an E-R diagram, entities are typically represented by rectangles.

·       Relationships: These represent associations or interactions between two or more entities. Relationships are depicted as diamonds in an E-R diagram.

·       Attributes: These represent the properties or characteristics of entities or relationships. Attributes are represented by ovals connected to their respective entities or relationships.

·       Primary Key: An attribute that uniquely identifies an entity in a set. It is typically underlined in the E-R diagram.

·       Cardinality: This indicates the number of instances of one entity that can or must be associated with each instance of another entity. It is represented by labels like "one-to-one", "one-to-many", or "many-to-many".

 

Q2. Define Normalization. Explain 2NF with example. 4 marks

Ans. Normalization is the process of organizing the attributes and tables of a relational database to minimize redundancy and dependency by dividing large tables into smaller ones. The main goal of normalization is to ensure that data is stored in such a way that it reduces the chances of data anomalies like insertion, deletion, and update anomalies. It also helps improve data integrity.

Normalization typically involves dividing a database into normal forms (1NF, 2NF, 3NF, etc.), each with specific rules.

Second Normal Form (2NF):

A table is in Second Normal Form (2NF) if it is in First Normal Form (1NF) and every non-prime attribute (an attribute that is not part of the primary key) is fully functionally dependent on the primary key. This means that the non-prime attributes should depend on the whole primary key, not just part of it.

Steps to Achieve 2NF:

1.     The table must be in 1NF (i.e., it should have atomic values, meaning no repeating groups).

2.     Remove partial dependencies (i.e., non-prime attributes must depend on the entire primary key, not just a part of it).

Example:

Consider a table for a student’s course registration:

StudentID	CourseID	Instructor	InstructorPhone
1	C101	Mr. Smith	1234567890
2	C102	Mrs. Brown	0987654321
1	C103	Mr. White	1112233445

Here:

• StudentID and CourseID together form the composite primary key.
• The attributes Instructor and InstructorPhone depend only on CourseID and not on the StudentID.
• This is a partial dependency, as Instructor and InstructorPhone are only dependent on CourseID, not on the whole composite key.

To convert this to 2NF:

1.     Decompose the table into two separate tables:

o   Student-Course Table (with StudentID and CourseID):

StudentID	CourseID
1	C101
2	C102
1	C103

o   Course-Instructor Table (with CourseID, Instructor, and InstructorPhone):

CourseID	Instructor	InstructorPhone
C101	Mr. Smith	1234567890
C102	Mrs. Brown	0987654321
C103	Mr. White	1112233445

Now:

• The Student-Course Table has StudentID and CourseID as the primary key.
• The Course-Instructor Table has CourseID as the primary key, with Instructor and InstructorPhone fully dependent on CourseID.

This eliminates the partial dependency, and the database is now in Second Normal Form (2NF).

Q3. Explain: (i) Candidate key (ii) Foreign key. 4 marks

Ans. 

(i) Candidate Key:

• A candidate key is a set of attributes in a table that can uniquely identify each row (tuple) in the table.
• It is a minimal set of attributes, meaning that no subset of the candidate key can uniquely identify the rows.
• A table can have multiple candidate keys.
• From the candidate keys, one is chosen as the primary key, and the rest are considered alternate keys.

Example:

Consider a table called "Employees" with the following attributes:

EmployeeID	Name	Department	Salary
1	John Doe	IT	50000
2	Jane Smith	HR	45000

In this table, both "EmployeeID" and "Name" can be considered candidate keys because they uniquely identify each employee. However, "EmployeeID" is usually chosen as the primary key due to its simplicity and efficiency.

(ii) Foreign Key:

• A foreign key is a column (or set of columns) in one table that refers to the primary key of another table.
• It establishes a relationship between ¹ two tables, creating a parent-child relationship.  
• The table containing the foreign key is called the child table, and the table whose primary key is referenced is called the parent table.
• Foreign keys ensure data integrity by enforcing referential constraints. This means that the values in the foreign key column must match existing values in the primary key column of the parent table.

Example:

Consider two tables: "Employees" and "Departments":

Employees

EmployeeID	Name	DepartmentID	Salary
1	John Doe	1	50000
2	Jane Smith	2	45000

Departments

DepartmentID	DepartmentName
1	IT
2	HR

In this example, "DepartmentID" in the "Employees" table is a foreign key that references the primary key "DepartmentID" in the "Departments" table. This ensures that each employee is associated with a valid department.

Q4. Draw E-R diagram of Banking system considering deposited, withdrawal facility. Also show primary key, weak entity, strong entity. 6 marks

Ans.

E-R Diagram for a Banking System

Entities:

1.     Customer:

o   Attributes: CustomerID (PK), Name, Address, PhoneNumber

2.     Account:

o   Attributes: AccountNumber (PK), AccountType, Balance

3.     Transaction:

o   Attributes: TransactionID (PK), TransactionDate, Amount

Relationships:

1.     Customer and Account:

o   One-to-Many relationship: A customer can have multiple accounts, but an account belongs to only one customer.

2.     Account and Transaction:

o   One-to-Many relationship: An account can have multiple transactions, but a transaction belongs to only one account.

E-R Diagram:

Explanation:

• Strong Entity: Customer and Account are strong entities as they can exist independently.
• Weak Entity: Transaction is a weak entity as it depends on the Account entity for its existence. It is shown as a double-lined rectangle and has a partial participation with the Account entity.
• Primary Keys: CustomerID for Customer, AccountNumber for Account, and TransactionID for Transaction are the primary keys.

Additional Considerations:

• Transaction Type: You can add an attribute to the Transaction entity to differentiate between deposits and withdrawals (e.g., TransactionType).
• Employee: If you want to include employees who handle transactions, you can add an Employee entity and a relationship between Employee and Transaction.
• Time Stamp: You can add a timestamp attribute to the Transaction entity to record the exact time of the transaction.

This E-R diagram provides a basic foundation for a banking system. You can further customize it based on specific requirements and complexity.

Winter 2022

Q5. Define Normalization, Enlist its types 2Marks – Summer 2024

Ans. Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller ones and defining relationships between them to ensure data is stored efficiently and consistently.

Types of Normalization:

1.     First Normal Form (1NF): Ensures that the table has a primary key and that all columns contain atomic (indivisible) values without repeating groups or arrays.

2.     Second Normal Form (2NF): Achieved when the table is in 1NF, and all non-key attributes are fully dependent on the entire primary key.

3.     Third Normal Form (3NF): Ensures that the table is in 2NF and has no transitive dependencies (non-key attributes depend only on the primary key).

4.     Boyce-Codd Normal Form (BCNF): A stronger version of 3NF, where every determinant is a candidate key.

5.     Fourth Normal Form (4NF): Ensures that the table has no multivalued dependencies.

6.     Fifth Normal Form (5NF): Removes join dependencies to ensure data is reconstructed only through key joins.

Q6. Explain Primary and Unique key constraint with syntax. 4Marks

Ans. Primary Key Constraint

• A Primary Key is a column or a set of columns in a table that uniquely identifies each row in that table.
• It does not allow NULL values and ensures that each value in the primary key column(s) is unique.
• A table can have only one primary key.

Syntax:

1. Define Primary Key while creating a table:

CREATE TABLE table_name (

    column1 datatype PRIMARY KEY,

    column2 datatype,

    ...

);

2. Add Primary Key to an existing table:

ALTER TABLE table_name

ADD PRIMARY KEY (column_name);

Unique Key Constraint

• A Unique Key ensures that all values in a column (or combination of columns) are unique across the table.
• Unlike a primary key, it allows NULL values, but only one NULL per column.
• A table can have multiple unique keys.

Syntax:

1. Define Unique Key while creating a table:

CREATE TABLE table_name (

    column1 datatype UNIQUE,

    column2 datatype,

    ...

);

2. Add Unique Key to an existing table:

 ALTER TABLE table_name

ADD CONSTRAINT unique_key_name UNIQUE (column_name);

 

Summer 2023

Q7. Define: i) Primary key ii) Candidate key 2Marks

Ans.

i) Primary Key

A primary key is a column or a set of columns in a table that uniquely identifies each row in the table. It cannot contain null values and must be unique.

A table can have only one primary key.

ii) Candidate Key:

A candidate key is a column or a set of columns that can uniquely identify each row in a table. It is a minimal set of attributes, meaning that no subset of the candidate key can uniquely identify the rows. A table can have multiple candidate keys.

From the candidate keys, one is chosen as the primary key, and the rest are considered alternate keys.

Q8. Explain strong and weak entity set with example, Draw ER diagram indicating strong and weak entry set. 4Marks

Ans.

Strong Entity Set:

• Definition: A strong entity set is an entity set that has its own primary key. It exists independently and does not rely on another entity set for its existence.
• Characteristics:
• Has a unique primary key.
• Can exist independently.
• Represented by a single rectangle in an ER diagram.

Weak Entity Set:

• Definition: A weak entity set is an entity set that does not have its own primary key. It depends on another entity set, called the owner entity, for its existence.
• Characteristics:
• Does not have a primary key.
• Relies on the primary key of the owner entity for its identification.
• Represented by a double rectangle in an ER diagram.
• The relationship between a weak entity set and its owner entity is represented by a double diamond.

Example:

Consider a university database with the following entities:

• Department: A department has a department ID (primary key), name, and location.
• Course: A course has a course ID (primary key), name, and credits.
• Professor: A professor has a professor ID (primary key), name, and department ID (foreign key referencing the Department entity).
• Project: A project has a project ID (primary key) and title.

In this example, Department and Professor are strong entity sets because they have their own primary keys and can exist independently. However, Course is a weak entity set because it depends on the Department entity for its existence. A course cannot exist without being associated with a department.

ER Diagram:

Q9. State and explain 1NF and 2NF with example 4 M - Summer  2024

1NF (First Normal Form)

• Definition: A relation is in 1NF if all attribute values are atomic, meaning they are indivisible.
• Example: Consider a table Orders with the following attributes:

·        OrderID | CustomerName | OrderDate | Items (Item1, Quantity1, Price1, Item2, Quantity2, Price2)

This table is not in 1NF because the Items attribute is multi-valued and not atomic. To normalize it to 1NF, we can break it into two tables:

2NF (Second Normal Form)

• Definition: A relation is in 2NF if it is in 1NF and every non-prime attribute is fully functionally dependent on the primary key.
• Example: Consider a table Products with the following attributes:

·        ProductID | ProductName | SupplierID | SupplierCity | Price

In this table, SupplierCity is not fully dependent on the primary key ProductID. It is partially dependent on SupplierID. To normalize it to 2NF, we can break it into two tables:

By normalizing to 1NF and 2NF, we ensure data integrity, reduce redundancy, and improve data consistency.

Q10. Draw ER diagram for hospital management system. 4Marks Winter 2023

Ans.

Explanation:

Entities:

• Patient: Represents information about patients, including their ID, name, address, contact information, and medical history.
• Doctor: Represents information about doctors, including their ID, name, specialization, and contact information.
• Appointment: Represents appointments scheduled between patients and doctors, including date, time, and room number.
• Room: Represents hospital rooms, including room number, type, and capacity.
• Bill: Represents bills generated for patient treatments, including bill number, date, and total amount.

Relationships:

• Patient-Appointment: A patient can have many appointments, and an appointment is for one patient. This is a one-to-many relationship.
• Doctor-Appointment: A doctor can have many appointments, and an appointment is with one doctor. This is also a one-to-many relationship.
• Appointment-Room: An appointment is scheduled in one room, and a room can have many appointments. This is a many-to-one relationship.
• Patient-Bill: A patient can have many bills, and a bill is for one patient. This is a one-to-many relationship.

Note: This is a simplified ER diagram. A real-world hospital management system would likely have many more entities and relationships, such as:

• Medication: Represents medications prescribed to patients.
• Test: Represents medical tests performed on patients.
• Insurance: Represents insurance information for patients.
• Staff: Represents hospital staff, including nurses and administrative staff.

The complexity of the ER diagram would depend on the specific requirements of the hospital management system.

Winter 2023

Q11. Define Normalization. State and Explain 2NF with example. 2 marks

Ans.

Normalization is the process of organizing data in a database to reduce redundancy and dependency, thereby improving data integrity and efficiency. It involves breaking down a complex database into simpler, more organized tables.

Second Normal Form (2NF):

A relation is in 2NF if it is in 1NF and every non-prime attribute is fully functionally dependent on the primary ¹ key.  

Example:

Consider a table named Orders with the following attributes:

OrderID	CustomerName	OrderDate	Item	Quantity	Price
101	John Doe	2023-11-27	Laptop	2	50000
101	John Doe	2023-11-27	Phone	1	20000

In this table, CustomerName and OrderDate are fully dependent on OrderID, but Item, Quantity, and Price are partially dependent on OrderID. To normalize it to 2NF, we can break it into two tables:

Orders Table:

OrderID	CustomerName	OrderDate
101	John Doe	2023-11-27

OrderDetails Table:

OrderID	Item	Quantity	Price
101	Laptop	2	50000
101	Phone	1	20000

By normalizing to 2NF, we have removed partial dependencies and improved the overall database design.