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.