INTRODUCTORY DATABASE QUESTION MODEL

1. Brief Answer Questions: (1 marks each)

a) Why organization maintain database?
 With a good database, an organization can monitor the progress of the operation well, so they
can take quick and appropriate steps if a problem occurs.
 Keeping the organization’s data security, because any data can be protected by provide login and
password for each data.
 The database can help to determine a better strategy for the advancement of an organization in
the future.
 Good database systems can help you manage all your business-critical data centrally, safely and
securely.
b) What is RDBMS? Give any two example of it.
A relational database management system (RDBMS) is a program that allows you to create, update, and
administer a relational database. Most relational database management systems use the SQL language to
access the database. Examples include Oracle Database, MySQL, Microsoft SQL Server, and IBM DB2
c) What is an attribute?
Attributes are the properties which define the entity type. For example, Roll_No, Name, DOB, Age,
Address, Mobile_No are the attributes which defines entity type Student. In ER diagram, attribute is
represented by an oval.

d) Why records of database are made unique?

The primary key is used to identify a row of data in a table. It is used whenever you need to refer to a
particular row, eg. in other tables or by application code etc. In order to identify a row, the values of a
PK must be unique.
e) What is one-to-one relationship?
If An  entity   in A is  associated  with   at  most  (only)  one  entity   in  B  and  an entity   in  B  is
associated  with  at most (only) one entity  in A.

f) What are advantages of Database over file system?

g) Differentiate between Hierarchical and Network model.
h) What is composite attribute?
An attribute composed of many other attribute is called as composite attribute. For example, Address
attribute of student Entity type consists of Street, City, State, and Country. In ER diagram, composite
attribute is represented by an oval comprising of ovals.

i) What do you mean by data abstraction?

Database systems are made-up of complex data structures. To ease the user interaction with database, the
developers hide internal irrelevant details from users. This process of hiding irrelevant details from user is
called data abstraction.
j) What do you mean by derived and multi-valued attribute?
 Multivalued Attribute –
An attribute consisting more than one value for a given entity. For example, Phone_No (can be more than
one for a given student). In ER diagram, multivalued attribute is represented by double oval.

Derived Attribute –
An attribute which can be derived from other attributes of the entity type is known as derived attribute. e.g.;
Age (can be derived from DOB). In ER diagram, derived attribute is represented by dashed oval.

k) How specialization is differ from generalization?

Difference between Generalization and Specialization in
1. Generalization :
It works on the principle of bottom up approach. In Generalization lower level functions are combined to form
higher level function which is called as entities. This process is repeated further to make advanced level entities.
In the Generalization process properties are drawn from particular entities and thus we can create generalized
entity. We can summarize Generalization process as it combines subclasses to form superclass.
Example of Generalization –
Consider two entities Student and Patient. These two entities will have some characteristics of their own. For
example Student entity will have Roll_No, Name and Mob_No while patient will have PId, Name and Mob_No
characteristics. Now in this example Name and Mob_No of both Student and Patient can be combined as a
Person to form one higher level entity and this process is called as Generalization Process.

2. Specialization :
We can say that Specialization is opposite of Generalization. In Specialization things are broken down into
smaller things to simplify it further. We can also say that in Specialization a particular entity gets divided into
sub entities and it’s done on the basis of it’s characteristics. Also in Specialization Inheritance takes place.
Example of Specialization –
Consider an entity Account. This will have some attributes consider them Acc_No and Balance. Account
entity may have some other attributes like Current_Acc and Savings_Acc. Now Current_Acc may have
Acc_No, Balance and Transactions while Savings_Acc may have Acc_No, Balance and Interest_Rate
henceforth we can say that specialized entities inherits characteristics of higher level entity.
 l) Define data model.
Data models defines the data elements and the relationships between the data elements. Data Models are
used to show how data is stored, connected, accessed and updated in the database management system.
Here, we use a set of symbols and text to represent the information so that members of the organization
can communicate and understand it.
m) Differentiate between DDL and DML.
DDL DML
It stands for Data Definition Language. It stands for Data Manipulation Language.
It is used to create database schema and can be used to It is used to add, retrieve or update the data.
define some constraints as well.
It basically defines the column (Attributes) of the table. It add or update the row of the table. These
rows are called as tuple.
It doesn’t have any further classification. It is further classified into Procedural and Non-
Procedural DML.
Basic command present in DDL are CREATE, DROP, BASIC command present in DML are
RENAME, ALTER etc. UPDATE, INSERT, MERGE etc.
DDL does not use WHERE clause in its statement. While DML uses WHERE clause in its
statement.
n) List the role of database administrator.
 Software installation and maintenance
 Data extactraction, transformation and loading
 Defining the database Schema.
 Defining storage structure and access method
 Defining security and integrity checks.
o) Why are table joined?
To stitch the database back together to make it easy to read and use database joins are used. They match
rows between tables. In most cases we're matching a column value from one table with another.
p) Write an example of generalization.
q) What is foreign key?
Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-reference
between tables.
Course_Id Stu_Id Stu_Id  Stu_Name  Stu_Age
C01 101 101 Chaitanya 22
C02 102 102 Arya 26
C03 101 103 Bran 25
C05 102 104 Jon 21
In the above example the Stu_Id column in Course_enrollment table is a foreign key as it points to the
primary key of the Student table.
r) Define transaction.
A transaction is an action or series of actions that are being performed by a single user or application program,
which reads or updates the contents of the database.
2. Short answer questions: ( 3 marks each)
a) List any four significant differences between a file processing system and a DBMS.
S.NO. FILE SYSTEM DBMS
1. File system is a software that manages and organizes DBMS is a software for managing the
 the files in a storage medium within a computer. database.
2. Redundant data can be present in a file system. In DBMS there is no redundant data.
3. It doesn’t provide backup and recovery of data if it It provides backup and recovery of data even
is lost. if it is lost.
4. There is no efficient query processing in file system. Efficient query processing is there in DBMS.
5. There is less data consistency in file system. There is more data consistency because of
the process of normalization.
6. It is less complex as compared to DBMS. It has more complexity in handling as
compared to file system.
7. File systems provide less security in comparison to DBMS has more security mechanisms as
DBMS. compared to file system.
8. It is less expensive than DBMS. It has a comparatively higher cost than a file
system.
b) How is data of multivalued attribute (phone_number) of person relation (table) is stored in table?
Multivalued attribute is a type of attribute which can have zero or more values per record.
Rule: To convert a multivalued attribute in an ER diagram into relational schema, we need to create a
separate table for multivalued attribute along with the primary key of the base table.
Example:
Let us convert the Entity set Employee given in ER Diagram of Figure 1. Entity set Employee has one
multivalued attribute (represented inside double ellipse). 

According to the rule stated above, we have to create two relation schemas for Employee as follows;
Employee (EID, FName, LName, DoorNo, Street, City) [Refer Composite attribute conversion]
Emp_Phone (EID, Phone)
c) What is data abstraction? Explain different layer of data abstraction in database system with diagram.
Data abstraction: As a data in database are stored with very complex data structure so when user come
and want to access any data, he will not be able to access data if he has go through this data structure. So
to simplify the interaction of user and database, DBMS hides some information which is not of user
interest, this is called data abstraction:- So developer hides complexity from user and store abstract view
of data. Data abstraction has three level of abstractions

Types of data abastraction:

• Physical level / internal level
• Logical level / conceptual level
• view level / external level / user level

 Physical level:- This is the lowest level of data abstraction which describe How data is actual stored in
database. This level basically describe the data structure and access path /indexing use for accessing
file.
 Logical level:- At this level, Programmer define the entity sets and relationship among these entity sets
using a programming language like SQL. So, the programmers work at the logical level and even the
database administrator also operates at this level.
 View level:- In this level user only interact with database and the complexity remain unview. At
the view level, the users have the set of applications which they use to retrieve the data they are
interested in.
d) What do you mean by transaction? Explain the ACID properties.
A transaction is a single logical unit of work which accesses and possibly modifies the contents of a
database. Transactions access data using read and write operations. In order to maintain consistency in a
database, before and after the transaction, certain properties are followed. These are
called ACID properties.

a. Atomicity (Either all or none): By this, we mean that either the entire transaction takes place at once
or doesn’t happen at all. There is no midway i.e. transactions do not occur partially. Each transaction
is considered as one unit and either runs to completion or is not executed at all. It involves the
following two operations.
Abort: If a transaction aborts, changes made to database are not visible.
 Commit: If a transaction commits, changes made are visible.

b. Consistency – Consistency indirectly depends upon remaining all three properties. The database must
remain in a consistent state after any transaction. No transaction should have any adverse effect on the
data residing in the database. If the database was in a consistent state before the execution of a
transaction, it must remain consistent after the execution of the transaction as well.
c. Isolation − In a database system where more than one transaction are being executed simultaneously
and in parallel, the property of isolation states that all the transactions will be carried out and executed
as if it is the only transaction in the system. No transaction will affect the existence of any other
transaction.
d. Durability − The database should be durable enough to hold all its latest updates even if the system
fails or restarts. If a transaction updates a chunk of data in a database and commits, then the database
will hold the modified data. If a transaction commits but the system fails before the data could be
written on to the disk, then that data will be updated once the system springs back into action.
e) How weak entity set can be converted to strong entity set?
Combing two unique key values to create one is a valid method as long as both unique keys combined
have a one to one relationship,  As the weak entities do not have any primary key, they cannot be
identified on their own, so they depend on some other entity (known as owner entity). The weak entities
have total participation constraint (existence dependency) in its identifying relationship with owner
identity.

Suppose, I have taken 3 partial loan from the bank (L1, L2, L3) with following amounts. I have planned
that each and every month I shall pay 5000 amount against my all three loan accounts each. (i.e. 5000 for
each L1, L2, L2). Here, there is no way to have unique identification. So it is put in weak entity set
(payment).
• Identifying relation hold two key i.e. Lno and PaymentNo.
• Loan table: Loan(Lno., Amount)
• Loan-Payment table: (Lno., PaymentNo, Pay.Date, Pay.Amount)

Loan No. Loan Amount Pay. No. Date Amount

L1 1,00,000 20 24/7 5,000
L2 2,00,000 20 24/7 5,000
L3 1,50,000 20 24/7 5,000

Loan-Payment table
Lno. PayNo Pay.Date Pay.Amount
.
L1 20 24/7 5,000
L2 20 24/7 5,000
L3 20 24/7 5,000
f) Explain any two functions of database administrator.
 Software installation and Maintenance
A DBA often collaborates on the initial installation and configuration of a new Oracle, SQL Server etc
database. The system administrator sets up hardware and deploys the operating system for the
 database server, then the DBA installs the database software and configures it for use. As updates and
patches are required, the DBA handles this on-going maintenance.

Specialized Data Handling

Today’s databases can be massive and may contain unstructured data types such as images,
documents, or sound and video files. Managing a very large database (VLDB) may require
higher-level skills and additional monitoring and tuning to maintain efficiency.
 Database Backup and Recovery
DBAs create backup and recovery plans and procedures based on industry best practices,
then make sure that the necessary steps are followed. Backups cost time and money, so the DBA
may have to persuade management to take necessary precautions to preserve data.
 Security
A DBA needs to know potential weaknesses of the database software and the company’s
overall system and work to minimise risks. No system is one hundred per cent immune to attacks,
but implementing best practices can minimise risks.
In the case of a security breach or irregularity, the DBA can consult audit logs to see who has
done what to the data. Audit trails are also important when working with regulated data.
 Authentication
Setting up employee access is an important aspect of database security. DBAs control who
has access and what type of access they are allowed. For instance, a user may have permission to
see only certain pieces of information, or they may be denied the ability to make changes to the
system.
3. Long Answer question. (5 marks each)
a) One question from Entity Relation (E-R) diagram.