Q.

1 Explain the term Super key and Candidate key with example
Q.2 Construct an ER Diagram of Bank Management in which bank has the
following : [5] Description : Bank have Customer. Banks are identified by a name,
code, address of main office. Banks have branches. Branches are identified by a
branch_no., branch_name, address. Customers are identified by name, cust-id,
phone number, address. Customer can have one or more accounts. Accounts are
identified by account_no., acc_type, balance. Customer can avail loans. Loans are
identified by loan_id, loan_type and amount. Account and loans are related to
bank’s branch.
Q.3 Define the term Database Management System. Explain advantages of
Database Management System over file system.
Q.4 Explain with example any two fundamental operators in relational algebra. [5]
Q.5 Explain with example how E-R diagram are converted into tables.[5]
Q.6 Describe the three levels of data abstraction with suitable diagram and
example?

Q.1 Explain the term Super key and Candidate key with example

In a Database Management System (DBMS), keys play a fundamental role

in ensuring the integrity, organization, and efficient retrieval of data. Keys

are essentially attributes or combinations of attributes that uniquely identify

records within a database table. They help maintain data consistency and

establish relationships between tables, facilitating data retrieval and

manipulation.

●​ There are some types of Keys in DBMS……… Such as,

Types of Keys

Primary Key

A primary key is a field or set of fields in a database table that uniquely

identifies each record in the table. It serves as a unique identifier for each
row and ensures that each record can be uniquely identified and retrieved. It

ensures that no two records have the same value for this key attribute(s).

Typically, primary keys are used for fast data retrieval and are often

automatically indexed by the DBMS for quick access.

Here are some key characteristics and points about primary keys:

●​ Uniqueness :- Every value in the primary key must be unique for

each record in the table. No two records can have the same

primary key value.

●​ Non-null :- The primary key value cannot be NULL. Every record

in the table must have a valid, non-null primary key value.

●​ Fixed :- The primary key should be a stable value that does not

change over time. It provides a constant reference to a specific

record.

●​ Single or Composite :- A primary key can be a single field or a

combination of fields. In the case of multiple fields, it is referred

to as a composite primary key.

●​ Indexed :- The primary key is often automatically indexed by the

database management system (DBMS) to enhance the retrieval

performance.

●​ Used in Relationships :- Primary keys are often used to establish

relationships between tables in a relational database. In a related

table, the primary key from one table is used as a foreign key in

another table to create a link between them.

Basic understanding of Primary Key

For an example :- In a table of employees, a unique employee ID could be

used as the primary key. In a composite key scenario, a combination of fields

such as (department ID, employee ID) might be used.

Here’s an example of creating a table with a primary key in SQL

CREATE TABLE Employees (​

EmployeeID INT PRIMARY KEY,​

FirstName VARCHAR(50),​

LastName VARCHAR(50),​

DepartmentID INT​

);

In this example, EmployeeID is the primary key for the Employees table.

A primary key’s main features are :-

 1.​ It must contain a unique value for each row of data.

2.​ It cannot contain null values.

3.​ Every row must have a primary key value.

Foreign Key

A foreign key is a field in a relational database table that is used to establish

a link between two tables. It creates a relationship between the tables by

referencing the primary key of another table. The purpose of a foreign key is

to ensure referential integrity in the database, meaning that relationships

between tables are consistent and that data remains accurate and reliable.

Foreign keys are crucial for maintaining data consistency and ensuring that

related data is linked correctly.

Here are some key characteristics and points about foreign keys:

●​ Reference to a Primary Key :- A foreign key refers to the primary

key of another table. This establishes a connection between the

two tables, indicating that the values in the foreign key column(s)
 of one table correspond to the values in the primary key column(s)

of another.

●​ Ensures Referential Integrity :- The foreign key relationship helps

maintain referential integrity by ensuring that every foreign key

value in a table corresponds to a valid primary key value in

another table. This helps prevent orphaned or inconsistent data.

●​ Uniqueness or Null Values :- The values in a foreign key column

either must be unique or can contain null values, depending on the

specific requirements and constraints defined during table

creation.

●​ Constraint :- A foreign key is a type of constraint that is applied to

a column or columns in a table. This constraint defines the

relationship between tables and imposes rules to maintain

consistency.

●​ CASCADE Options :- Some database management systems allow

the specification of “CASCADE” options, such as CASCADE

DELETE or CASCADE UPDATE. These options determine the

 actions to be taken when a referenced primary key is deleted or

updated.

●​ Multiplicity :- Foreign keys can have different levels of

multiplicity, such as one-to-one, one-to-many, or many-to-many

relationships. This depends on the nature of the relationship

between the tables.

●​ Syntax in Table Creation :- In SQL, the syntax for defining a

foreign key is typically included in the CREATE TABLE

statement. It involves specifying the foreign key column(s) and

the referenced primary key.

Here’s a simplified example in SQL:

CREATE TABLE Orders (​

OrderID INT PRIMARY KEY,​

ProductID INT,​

Quantity INT,​
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)​

);

In this example, the Orders table has a foreign key ProductID that references

the ProductID column in the Products table. This establishes a relationship

between the two tables based on the ProductID values.

Basic understanding of Foreign Key

Super Key

A super key is a set of one or more attributes (columns) that, taken

collectively, can uniquely identify a record in a database table. In other

words, it’s a set of columns that can uniquely identify each row in a table. It

may contain more attributes than necessary for uniqueness and can include

the primary key.

Here are some key characteristics and points about Super keys:

●​ Uniqueness :- A super key must have the property that no two

distinct rows in a table have the same combination of values for

all the columns in the super key.

●​ Minimality :- A super key should be minimal, meaning that no

proper subset of the super key should have the uniqueness

property. Removing any column from the super key would result

in a loss of the unique identification property.

 ●​ Candidate Key :- Every candidate key is a super key, but not every

super key is a candidate key. A candidate key is a super key that is

irreducible, meaning it has no unnecessary attributes and

removing any attribute from it would cause it to lose the

uniqueness property.

●​ Composite Super Key :- A super key can be a single attribute or a

combination of multiple attributes. A composite super key

involves a combination of two or more columns to uniquely

identify a record.

●​ Used in Database Design :- Super keys are relevant in the process

of database design and normalization. They help in identifying

potential candidate keys and aid in designing tables with minimal

redundancy.

For an Example :- In a table of students, a super key could be the

combination of (StudentID, Email), assuming that no two students share the

same combination of StudentID and Email.

Here’s a simple example to illustrate a super key :-

CREATE TABLE Employees (​

EmployeeID INT,​

FirstName VARCHAR(50),​

LastName VARCHAR(50),​

Email VARCHAR(100),​

PRIMARY KEY (EmployeeID),​

UNIQUE (EmployeeID, Email) — This combination is a super key​

);

In this example, the combination of (EmployeeID, Email) forms a super key

for the Employees table. It ensures uniqueness, as no two records in the table

can have the same combination of EmployeeID and Email.

Basic understanding of Super Key

●​ To find the Super Keys for a table :

●​ We can find the maximum value of a super key based on it’s

column number. It can find (2^n -1) this equation. In here, n is

number of columns.
Here, We have to create several Super Keys.

●​ SID

●​ SID, Name

●​ SID, Marks

●​ SID, Department

●​ SID, Course

●​ SID, Name, Marks

●​ SID, Name, Department

●​ SID, Name, Course

●​ SID, Marks, Department

●​ SID, Marks, Course

●​ SID, Department, Course

●​ SID, Name, Marks, Department

●​ SID, Name, Marks, Course

●​ SID, Marks, Department, Course

●​ SID, Name, Marks, Department, Course

 ●​ SID, Name, Department, Course

●​ Name, Marks, Department, Course

●​ Marks, Department, Course

Composite Key

A composite key is a type of key in a relational database that consists of two

or more columns (attributes) in a table, working together to uniquely identify

each record in that table. Unlike a single-column primary key, which is

composed of only one attribute, a composite key involves a combination of

multiple attributes. It is often used when no single attribute can guarantee

uniqueness, but the combination of several attributes does.

Here are some key characteristics and points about Composite keys:

●​ Uniqueness :- The combination of values in the columns that

make up the composite key must be unique for each row in the

table.
●​ Primary Key or Alternate Key :- A composite key can serve as the

primary key for a table if it uniquely identifies each record.

Alternatively, it can be an alternate key if the primary key is a

different column or set of columns.

●​ Use in Relationships :- Composite keys are often used in

relationships between tables. Columns from a composite key in

one table may be used as foreign keys in another table to establish

a link between them.

●​ Example :- In a table of orders, a composite key could be formed

by combining the columns (OrderID, ProductID) to uniquely

identify each order line.

●​ CREATE TABLE OrderLines (​

OrderID INT,​

ProductID INT,​

Quantity INT,​

PRIMARY KEY (OrderID, ProductID),​

FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)​

);
●​ Redundancy Reduction :- The use of composite keys can help

reduce redundancy by capturing more complex relationships

between entities without the need for additional tables.

●​ Querying Considerations :- When querying tables with composite

keys, it’s essential to use all the columns in the key to uniquely

identify records. Queries that involve a subset of the columns in

the composite key may not be sufficient for uniquely identifying

records.

●​ Syntax in Table Creation :- In SQL, the syntax for creating a table

with a composite key involves listing the columns within the

PRIMARY KEY constraint.

●​ Here’s an example of creating a table with a composite key in

SQL :-

●​ CREATE TABLE StudentCourses (​

StudentID INT,​

CourseID INT,​

Grade CHAR(2),​
 PRIMARY KEY (StudentID, CourseID),​

FOREIGN KEY (StudentID) REFERENCES

Students(StudentID),​

FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)​

);

●​ Before In this example, the combination of (StudentID, CourseID)

forms a composite key for the StudentCourses table.

Basic understanding of Composite Key

Alternate Key

An alternate key, also known as a secondary key, is a candidate key that is

not selected as the primary key for a table. A candidate key is a set of one or

more columns in a table that can uniquely identify a record. While a table

can have multiple candidate keys, one of them is chosen to be the primary

key, and the others become alternate keys. Alternate keys can be used for

unique identification as well.

Here are some key characteristics and points about Alternate keys:

●​ Uniqueness :- Like the primary key, an alternate key must ensure

uniqueness. The combination of values in the alternate key

columns should be unique for each record in the table.

●​ Candidate Key :- An alternate key is essentially a candidate key

that is not chosen as the primary key. It remains a unique identifier

for records in the table.

●​ Used for Relationships :- Alternate keys are often used in

relationships between tables. Columns from an alternate key in

one table may be used as foreign keys in another table to establish

connections.

●​ Enforcing Uniqueness :- Even though an alternate key is not the

primary key, the database management system (DBMS) enforces

uniqueness for the columns that make up the alternate key.

●​ Example :- In a table of employees, if both Social Security

Number (SSN) and Employee ID are candidate keys, one of them

may be chosen as the primary key, and the other becomes an

alternate key.

●​ CREATE TABLE Employees (​

EmployeeID INT PRIMARY KEY,​

SSN VARCHAR(11) UNIQUE,​

FirstName VARCHAR(50),​

LastName VARCHAR(50)​

);

●​ In this example, SSN is an alternate key.

 ●​ Data Integrity :- Alternate keys contribute to data integrity by

ensuring that no two records in the table have the same

combination of values in the alternate key columns.

●​ Querying and Indexing :- Depending on the use cases and query

patterns, alternate keys might be indexed to improve query

performance, especially when they are used in JOIN operations.

●​ Notation :- In data modeling, alternate keys are sometimes

denoted with a dashed underline to distinguish them from the

primary key.

It’s worth noting that while the primary key uniquely identifies records and

is often used in foreign key relationships, alternate keys provide additional

ways to uniquely identify records and may be useful in certain querying

scenarios or for specific business requirements.

Basic understanding of Alternate Key

Unique Key

A unique key is a database constraint that ensures that all values in a column

or a set of columns are unique across the rows in a table. While it’s similar to

a primary key in terms of enforcing uniqueness, a unique key differs in that it

allows for at least one NULL value. Unique keys help maintain data integrity

by preventing duplicate values in the specified column(s).

Here are some key characteristics and points about Unique keys:

●​ Uniqueness :- Like the primary key, a unique key ensures that the

values in the specified column or columns are unique. No two

rows can have the same combination of values.

●​ Nullable :- Unlike the primary key, a unique key allows for at least

one NULL value. This means that while NULL values are

allowed, any non-NULL values must be unique.

●​ Use Cases :- Unique keys are often used when a column or set of

columns needs to have unique values but can also contain NULLs.

This is different from primary keys, which are not nullable.

●​ Multiple Unique Keys :- A table can have multiple unique keys.

Each unique key enforces uniqueness independently.

●​ Indexed :- Similar to primary keys, unique keys are often

automatically indexed by the database management system

(DBMS) to improve query performance.

●​ Syntax in Table Creation :- In SQL, you can specify a unique key

constraint when creating a table.

●​ For example :-

●​ CREATE TABLE ExampleTable (​

ID INT,​

UniqueColumn VARCHAR(50) UNIQUE,​

AnotherColumn INT,​

PRIMARY KEY (ID)​

);

●​ Before this example, UniqueColumn has a unique key constraint.

●​ Use in Relationships :- Unique keys can be used in relationships

between tables. Columns with unique key constraints in one table

can be referenced as foreign keys in another table.

Unique keys are useful when you need to enforce uniqueness in a column

but still allow for the possibility of NULL values. They provide more

flexibility than primary keys in this regard. However, primary keys are

typically used when you want a column or set of columns to uniquely

identify each row, and NULL values are not allowed.

Basic Understanding of Unique Key

Candidate Key
A candidate key is a set of one or more columns (attributes) in a database

table that can uniquely identify each record in that table. These keys are

considered as potential primary keys for the table. There may be multiple

candidate keys in a table, but only one is chosen as the primary key.

Here are some key characteristics and points about Candidate keys:

●​ Uniqueness :- A candidate key must have the property that the

values in the columns uniquely identify each row in the table. No

two distinct rows should have the same combination of values in

the candidate key columns.

●​ Minimality :- A candidate key should be minimal, meaning that

no proper subset (a subset that is not equal to the candidate key

itself) should have the uniqueness property. Removing any

column from the candidate key would result in a loss of

uniqueness.
●​ Candidate for Primary Key :- Every candidate key is a potential

primary key. However, only one candidate key is chosen to be the

primary key for a given table.

●​ Example :- In a table of employees, possible candidate keys could

include Employee ID, Social Security Number (SSN), or a

combination of attributes like (FirstName, LastName,

DateOfBirth).

●​ Multiplicity :- A table can have multiple candidate keys. These

keys may be single-column keys or combinations of columns.

●​ Selection of Primary Key :- From the set of candidate keys, one

key is chosen to be the primary key for the table. The choice of

the primary key depends on factors like stability, simplicity, and

ease of use.

●​ Use in Relationships :- Candidate keys are often used in

relationships between tables. The columns of a candidate key in

one table may be referenced as foreign keys in another table.

●​ Enforcing Uniqueness :- In practice, the database management

system (DBMS) enforces uniqueness constraints on candidate

keys to ensure data integrity.

●​ Here’s a simplified example in SQL:

●​ CREATE TABLE Employees (​

EmployeeID INT PRIMARY KEY,​

SSN VARCHAR(11) UNIQUE,​

FirstName VARCHAR(50),​

LastName VARCHAR(50)​

);

●​ Before this example, both EmployeeID and SSN could be

considered candidate keys, but EmployeeID is chosen as the

primary key. The UNIQUE constraint on SSN indicates that it is

also a candidate key, providing an alternative unique identifier for

each employee.
Basic Understanding of Candidate Key

Example:-

1.​ The following table shows, the examples of the keys in DBMS.

There are two tables in here. Such as Employee and Designation.

Employee Table having 5 attributes. They are EmpID,

Emp_Name, EmpLicence, EmpPassport and DId. The

 Designation Table having 2 attributes such as DId and

Designation.

In Employee Table Primary Key is EmpID. Because it is a unique value, it

can’t multiple. The designation table Primary Key is DId.

The Employee Table having Foreign. It is from Designation table’s Primary

Key. That is DId.

There are some candidate keys here, E.g:- EmpID & EmpLicence &

EmpPassport. Both are combine to come to the candidate key.

The EmpLicence & EmpPassport attributes are the Alternate Key. If the

attribute can come only the Alternate Key, it can’t combine alternate key.

The EmpLicence and the EmpPassport attributes are the unique Key

individually. It same like Primary Key but in a table having only one Primary

Key, Multiple Unique Key. In this Key also have attributes for the Primary
Key. Such as, It data’s can’t be duplicate and also it can’t be the null values.

It can have only one null Value.

Examples of Keys in one View

Importance points of Keys : —

 ●​ All the Alternate Keys are Super Key.

●​ All the Primary Keys are Super Key.

●​ Unique Key have only 1 null value.

●​ Primary Key don’t have Null values.

●​ All the Primary Keys are Unique Keys.

●​ All the Unique Keys are not Primary Keys.

●​ All the Composite Keys are Primary Keys.

●​ All the Alternate Keys are Candidate Keys.

●​ Foreign Keys are used to connect the table.

In summary, keys in a DBMS are crucial for maintaining data accuracy,

establishing relationships between tables, and ensuring efficient data

retrieval. The choice of which key(s) to use depends on the specific

requirements of the database schema and the nature of the data being stored.

Properly defined keys are essential for the overall effectiveness and

reliability of a database system.

Q.2 Construct an ER Diagram of Bank Management in which bank has the
following : [5] Description : Bank have Customer. Banks are identified by a name,
code, address of main office. Banks have branches. Branches are identified by a
branch_no., branch_name, address. Customers are identified by name, cust-id,
phone number, address. Customer can have one or more accounts. Accounts are
identified by account_no., acc_type, balance. Customer can avail loans. Loans are
identified by loan_id, loan_type and amount. Account and loans are related to
bank’s branch.

an Entity-Relationship (ER) model uses entities, attributes, and relationships. We

can define the following for a bank management system:

●​ Entities and Attributes:​

○​ Bank: Bank_Name, Bank_Code (Primary Key), Main_Office_Address​

○​ Branch: Branch_No (Primary Key), Branch_Name, Branch_Address,

Bank_Code (Foreign Key)​

○​ Customer: Cust_ID (Primary Key), Name, Phone, Address​

○​ Account: Account_No (Primary Key), Acc_Type, Balance, Cust_ID

(Foreign Key), Branch_No (Foreign Key)​

○​ Loan: Loan_ID (Primary Key), Loan_Type, Amount, Cust_ID

(Foreign Key), Branch_No (Foreign Key)​

●​ Relationships:​
 ○​ A Bank has multiple Branches (1:N)​

○​ A Branch issues multiple Accounts and Loans (1:N)​

○​ A Customer owns multiple Accounts and avails multiple Loans (1:N)

​
Q.3 Define DBMS. Explain advantages over file system.

According to “Database System Concepts” by Silberschatz, Korth, and Sudarshan,

a Database Management System (DBMS) is a collection of interrelated data and a
set of programs to access those data. The primary goal of a DBMS is to provide a
way to store and retrieve database information that is both convenient and efficient.

Advantages of DBMS over File System (from C.J. Date’s book):

1.​ Data Redundancy Control: In file systems, the same data may be stored in
multiple files, leading to redundancy. DBMS reduces this by maintaining
data in a centralized database.​

2.​ Data Integrity: DBMS provides constraints such as primary key, foreign key,
unique, and not null to ensure data accuracy.​

3.​ Data Security: DBMS provides user authentication and authorization to

control access.​

4.​ Data Abstraction: DBMS separates the logical structure of data from its
physical storage.​

5.​ Transaction Management: DBMS supports ACID (Atomicity, Consistency,

Isolation, Durability) properties for transactions, which file systems lack.​

6.​ Concurrent Access: DBMS handles simultaneous data access by multiple

users effectively.​

7.​ Backup and Recovery: Automated tools help recover data from failures.​

Thus, DBMS provides a robust, secure, and efficient data management solution as
compared to traditional file systems.
Q.4 Explain with example any two fundamental operators in relational algebra

According to “Database System Concepts”, relational algebra is a procedural query

language that operates on relations.

1.​ Selection (σ):​

○​ Used to select tuples that satisfy a given predicate.​

○​ Syntax: σ(Relation)​

○​ Example: σDept='CSE'(STUDENT) retrieves all tuples where the

department is 'CSE'.​

2.​ Projection (π):​

○​ Used to select specific columns from a relation.​

○​ Syntax: π<attribute_list>(Relation)​

○​ Example: πName, Dept(STUDENT) returns the names and

departments of all students.​

These operators are foundational and are used to build complex queries. As stated
by C.J. Date, relational algebra forms the theoretical basis for SQL.
Q.5 Explain with example how E-R diagram are converted into tables

Based on the conversion rules outlined in “Database System Concepts”:

1. Entity Conversion:

●​ Each strong entity set becomes a table with attributes and a primary key.​
2. Relationship Conversion:​

●​ 1:1 Relationship: Can be implemented by adding a foreign key in either of

the tables.​

●​ 1:N Relationship: Add a foreign key in the table on the N side.​

●​ M:N Relationship: Create a new table with foreign keys from both entities.​

Example:​
Given Entities:

●​ STUDENT(Roll_No, Name)​

●​ COURSE(Course_ID, Title)​
Relationship:​

●​ ENROLLS(Roll_No, Course_ID)​

Tables:

1.​ STUDENT(Roll_No PRIMARY KEY, Name)​

2.​ COURSE(Course_ID PRIMARY KEY, Title)​

3.​ ENROLLS(Roll_No FOREIGN KEY, Course_ID FOREIGN KEY)​

This approach ensures the relational schema derived from an ER model is
normalized and preserves all the constraints and semantics.

Q.6 Describe the three levels of data abstraction with suitable diagram and example

As described in “Database System Concepts” and elaborated in C.J. Date’s book,

the three-level architecture helps achieve data abstraction and data independence.

1. Physical Level:

●​ Describes how the data is actually stored.​

●​ Example: B-trees, indexes.​

2. Logical Level:
 ●​ Describes what data is stored and the relationships among the data.​

●​ Example: STUDENT(Roll_No, Name, Dept)​

3. View Level:

●​ Describes part of the database seen by specific users.​

●​ Example: A librarian may see only Roll_No and Name.​

Diagram:

View Level (External Schema)

↑
Logical Level (Conceptual Schema)
↑
Physical Level (Internal Schema)

This architecture enables logical data independence (changes in logical schema

don't affect view schema) and physical data independence (changes in physical
storage do not affect logical schema).

Real-Life Analogy (Banking System):

Level What You See

View Level ATM screen: Balance, Mini-statement

Logical Customer table, Account table,
Level relations

Physical Binary files on disk, hash/index

Level mechanisms