JSPM’s

RAJARSHI SHAHU COLLEGE OF ENGINEERING,

POLYTECHNIC
Department of Computer Engineering
Academic Year: 2024-25

UNIT-2 ( 12 Marks)
MSBTE Question bank

Sr.No. Question Year Marks

1 Define the term : i) Candidate key ii) Primary key W-18 2
S-23
ans Candidate key: In a relation, there may be a primary key or may 1 mark
not, but there may be a key or combination of keys which uniquely Candidate key
identify the record. Such a key is called as Candidate key. OR A 1 mark
candidate key is a column, or set of columns, in a table that can Primary Key
uniquely identify any database record without referring to any
other data. The candidate key can be simple (having only one
attribute) or composite as well. For Example, {STUD_NO,
COURSE_NO} is a composite candidate key for relation
STUDENT_COURSE.
Primary key: A key which is selected by the designer to uniquely
identify the entity is called as Primary key. A primary key cannot
contain duplicate values and it can never contain null values inside
it. Example, RollNo attribute is a primary key for Relation Student
2 Define normalization, list its types. W-18 2
S-19
W-22
S-24
ans Normalization:
Normalization can be defined as process of
decomposition/division of database tables to
avoid the data redundancy.
Types of Normalization:
1. 1NF
2. 2NF
3. 3NF
4. BCN
3 State and explain 1NF and 2NF with example. W-18 4
S-23 6

S-24 4

11
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 ans First Normal Form (1NF):
A relation R is said to be in first normal form (1NF) if the domain
of all attributes of R
are atomic.
OR
A table is in the first normal form if it contains no repeating
elements groups. Example:
Supplier(sno,sname,location,pno,qty)

4 Draw an E-R diagram of library management system considering W-18 6

issue and return, fine calculation facility, also show primary key,
weak entity and strong entity.
ans

1 State and explain 2NF with example S-19 4

Define Normalization. Explain 2NF with example. S-22 4
ans A table is said to be in 2NF if both the following conditions hold:
 Table is in 1NF (First normal form)  No non-prime attribute is
dependent on the proper subset of any candidate key of table.  San
attribute that is not part of any candidate key is known as non- prime
attribute.  Example: Suppose a school wants to store the data of
teachers and the subjects they teach. They create a table that looks
like this: Since a teacher can teach more than one subjects, the
table can have multiple rows for a same teacher.

12
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 CandidateKeys:{teacher_id,subject}
Non-prime attribute: teacher_age The table is in 1 NF because
each
attribute has atomic values. However, it is not in 2NF because
nonprime attribute teacher_age is dependent on teacher_id alone
which is a proper subset of candidate key. This violates the rule
for 2NF as the rule says “no non-prime attribute is dependent on
the proper subset of any candidate key of the table “To make the
table complies with 2NF we can break it in two tables like this:
teacher details tab
A table is said to be in 2NF if both the following conditions hold:
 Table is in 1NF (First normal form)  No non-prime attribute is
dependent on the proper subset of any candidate key of table.  San
attribute that is not part of any candidate key is known as non- prime
attribute.  Example: Suppose a school wants to store the data of
teachers and the subjects they teach. They create a table that looks
like this: Since a teacher can teach more than one subjects, the
table can have multiple rows for a same teacher.

CandidateKeys:{teacher_id,subject}
Non-prime attribute: teacher_age The table is in 1 NF because
each attribute has atomic values. However, it is not in 2NF because
nonprime attribute teacher_age is dependent on teacher_id alone
which is a proper subset of candidate key. This violates the rule for
2NF as the rule says “no non-prime attribute is dependent on the
proper subset of any
candidate key of the table “To make the table complies with 2NF
we
can break it in two tables like this: teacher_subject Table
13
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 2 Draw ER diagram for library management system considering S-19 6
issue and return, fine collection facility. Consider appropriate
entities.
ans

1 Define table and field. W-19 2

ans Table: A table is a collection of related data held in table format. Each
It is a set of data elements using a model of vertical columns and definitio
horizontal rows. n 1M
Field: Each table contains field which is a data structure, used to
hold the data. It can also be termed as attribute.
2 Define primary key and foreign key. W-19 2
ans Primary key ;is an attribute or set of attributes used to identify an Each
entity from an entity set. All the values of a primary key should be definitio
unique and null values are not allowed. n 1M
Foreign key is an attribute of an entity which is the primary key
of
another entity. It is used to show relation between entities. The
table containing foreign key is called the child table.
3 State and explain 3NF with example. W-19 4

14
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 ans 3NF: Explana
An entity is said to be in the third normal form when, tion 2M
1) It satisfies the criteria to be in the second normal form. Any
2) There exists no transitive functional dependency. (Transitive example
functional dependency can be explained with the relationship link 2M
between three tables. If table A is functionally dependent on B,
and B is functionally dependent on C then C is transitively
dependent on A).
Let us consider the Schema given:
(Supplier_no,SupplierName,Supplier_city,Order_no,Order_quant
ity,
Order_amount,Product_code,Product name,rate)
Step 1.To convert it into 2NF, We have to decompose the given
table into two tables with fully functional dependencies and
establishing a referential integrity constraint relationship among
the two tables.Table2: Supplier Details
(Supplier_no,Supplier_name,Supplier_city)
Table 3:Order Details
(Order_no,Orderquantity,Order_amount,Supplier_no
Product_code,
product_name,rate)
Now the above two tables are in 2NF
Step 2: To convert the above tables in 3NF, we have to
decomposehem in three tables satisfying the transitive
dependencies
property.
Table 4: Supplier Details
(Supplier_no,Supplier_name,Supplier_city)
Table 5: Product Details:
(Product_code, product_name,rate)
Table 6: Order Details (or Transaction Details)
((Order_no,Supplier_no,Product_code,Order_quantity,Order_am
ount
)
Hence the above three tables are satisfying Transitive
dependencies.
Thus they are in 3NF.
4 Explain strong and weak entity set. W-19 4
ans Strong entity set: Each
An entity set that has sufficient attributes to form a primary key is entity set
called as Strong entity set. 2M
Example: Employee is a Strong entity with attributes as empid,
name,
address, salary, birthdate among which empid can be considered
as
primary key.
Weak entity set:
The entity set which does not have sufficient attributes to form a
primary key is called as Weak entity set.
A weak entity is an entity that cannot be uniquely identified by its

15
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 attributes alone; therefore, it must use a foreign key in conjunction
with its attributes to create a primary key. The foreign key is
typically
a primary key of an entity it is related to.
Example: Employee has "dependents" with name, birthdate, and
relationship to employee and it can be related to employee with the
help of empid, so "dependents" is a weak entity which depends on
strong entity "Employee".
5 Draw an ER diagram for library management system. (Use Books, W-19 6
Publisher & Member entities).
ans

1 State the components used in E-R diagram. S-22 2

16
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 ans

3 Explain. i) Candidate key ii) Foreign key S-22 4

ans Candidate key: In a relation, there may be a primary key or may
not, but there may be a key or combination of keys which uniquely
identify the record. Such a key is called as Candidate key. OR A
candidate key is a column, or set of columns, in a table that can
uniquely identify any database record without referring to any
other data. The candidate key can be simple (having only one
attribute) or composite as well. For Example, {STUD_NO,
COURSE_NO} is a composite candidate key for relation
STUDENT_COURSE
A foreign key is a column or group of columns in a relational
database table that provides a link between data in two tables. It
acts as a cross-reference between tables because it references the
primary key of another table, thereby establishing a link between
them.
4 Draw E-R diagram of Banking system considering deposite, S-22 6
withdrawal facility. Also show primary key, weak entity, strong
entity.

17
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 ans

1 Explain Primary and Unique key constraint with syntax. W-22 4

ans Primary key constraint:
Primary key constraint applied to any column can’t accept the
duplicate and null
values.
This constraint can be applied at the time of table creation.
Syntax for Primary Key:
CREATE TABLE <Table_Name>
(
Column1 datatype,
Column2 datatype,CONSTRAINT <Name> PRIMARY KEY
(Column name)
);
Example:
CREATE TABLE CUSTOMERS
( ID INT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
PRIMARY KEY (ID) )
CREATE TABLE CUSTOMERS
(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID, NAME)
);
Unique key constraint:
Unique key constraint applied to any column can’t accept the
duplicate values (only
accepts unique values).
This constraint can be applied at the time of table creation.
Syntax for Unique key:

18
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 CREATE TABLE <Table_Name>
(
Column1 datatype,
Column2 datatype, CONSTRAINT <Name> UNIQUE KEY
(Column name)
);
Example:
CREATE TABLE students
(
S_Id int NOT NULL,
LastName varchar (255) NOT NULL,
FirstName varchar (255),
City varchar (255),
UNIQUE (S_Id)
)
OR
CREATE TABLE students
(
S_Id int NOT NULL,
LastName varchar (255) NOT NULL,
FirstName varchar (255),
City varchar (255),
CONSTRAINT uc_studentId UNIQUE (S_Id, LastName)
)

1 Explain not null constraint with example S-23 4

ans The Not Null constraint is used to ensure that a given column of a
table is never
assigned the null value.
Example:
CREATE TABLE student
(
Rollno number(4) constraint NN NOT NULL,
Name varchar(255) ,
Marks number(3)
);
Check Constraint:
The Check constraint is used to limit the value range that can be
placed in a
column.
Example:
CREATE TABLE student
(
Rollno number(4),
Name varchar(255),
Marks number(3),
Age number(3) constraint ck CHECK (Age>=18)
);
2 Explain any four attributes with example S-23 4

19
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 ans 1. Simple Attribute: Simple attributes are those that cannot be
further divided into sub-attributes.
For example, roll number of a student or the employee
identification number.
2. Composite Attribute: Composite attributes are made up of two
or more simple
attributes.For example, a persons address may be a composite
attribute that is made up of
the persons street address, city, state, and zip code.
3. Single Valued Attribute: Single-valued attributes can only have
one value.
Single-valued attributes are typically used to provide a unique
identifier for an
entity and are often used in databases.
For example, a persons Social Security Number is a single-valued
attribute.
4. Multivalued Attribute: Multivalued attributes can have more
than one value.
For example, a person may have multiple email addresses or phone
numbers.
5. Stored Attribute: Values of stored attributes remain constant and
fixed for an
entity instance and also, and they help in deriving the derived
attributes.
For example, the Age attribute can be derived from the Date of
Birth attribute, and
also, the Date of birth attribute has a fixed and constant value
throughout the life
of an entity. Hence, the Date of Birth attribute is a stored attribute.
6. Derived Attribute: Derived attributes are based on other
attributes and are not
stored directly in the database.
For example: Consider a database of employees. Each employee
has a date of
birth, and we can calculate their age which can be called as derived
attribute.
3 Explain strong entity and weak entity set .draw diagram indicating S-23 6
strong and weak entity set
Explain strong and weak entity set. 4
W-19

ans Strong entity set:

Entity set that have sufficient attribute to define the primary key is
called
as strong entity sets.

single.

20
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 a
diamond symbol
of
an entity set
with the relationship set. Total participation may or may not exist
in the
relationship.
Example:
Student and course relation: Student entity is a strong entity
because it
consists of a primary key called student id which is enough for
accessing each
record uniquely. In the same way, the course entity contains of
course ID attribute
which is capable of uniquely accessing each row.

Weak entity set:

Entity sets that does not have sufficient attribute to define the
primary key iscalled as weak entity set.

set.
he identifying
relationship is double.
strong and a weak entity set is
represented by a doublediamond symbol.(known as identifying
relationship)

Example:
Employee is a strong entity because it has a primary key attribute
called Employee number (Employee_No) which is capable of
uniquely
identifying all the employee.Unlike Employee, Dependents is
weak entity because it does not have any primary key .

21
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 4 Draw an ER Diagram of Libarary management system consdiering S-23 6
publisher, member also show primary key , weak entity and strong
entity
ans

1 Define Normalization. State and explain 2NF with example. W-23 4

ans Normalization is a process of organizing the data in database to
avoid data redundancy,

22
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 insertion anomaly, update anomaly & deletion anomaly.
Second Normal Form (2NF):
A relation is said to be in the second normal form if it is in first
normal form and all the
non key attributes are fully functionally dependent on the primary
key.
Example

• This table is in first normal form, in that it obeys all the rules of
first normal form.
• In this table, the primary key consists of SNO.
• However, the table is not in second normal form because there
are partial
dependencies of primary keys and columns.
• In the above relation NAME, LOCATION depends on SNO and
QTY on (SNO,
PNO)
• so the table can be split up into two tables as
Supplier(SNO,SNAME,LOCATION)
and SP(SNO,PNO,QTY) and now both the tables are in second
normal form.
Supplier

23
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 2 Draw ER diagram for Hospital management system. (Use Entity- W-23 6
Doctor, Patient, Hospital and Medical Record). Show Primary
Key, and Foreign Key
ans Correct
entities: 2M,
correct
symbols: 2M,
Correct
relationships:
2M

3 Draw ER Diagram for hospital management system S-24 4

24
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 ans

25
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE