Page 1 of 32

1-2A (CSIT-Sem -5) mnaye oouLttt Introduction

PART-1
Overview, Database System,us File System,
Database System Concept and Architecture.

Questions-Answers
Long Answer Type andMedium Answer Type Questions

Que 1.1. What is database management system (DBMS) ? What

are the tasks performed by users in DBMS
?G isB
vitaoioi9
Answer
1. Database management system (DBMS) is a software which is use to
manage the database. For example, MySQL, Oracle, are commercial
eisa database which is used in different applications.
2. DBMS provides an interface to perform various operations like database
creation, storing data, updating data, creating a table in the database
etc.
case of multiple
3. It provides protection and security to the database. In
users, it also maintains data'consistency.
:
DBMS allows users the following tasks
: modification, and removal of
1 Data definition It is used for creation,
database objects that defines the organization of data in the database.
2. Data updation: It is used for the insertion, modification, and deletion
of the actual data in the database.
3. Data retrieval : It is used to retrieve the data from the database
which can be used by applications for various purposes,.
users,
4.:User administration :It is used for registering and monitoring
maintaining data integrity, enforcing data security, dealing with
ae concurrency control, monitoring performance and recovering
information corrupted by unexpected failure.
of DBMS?
Que 1.2. What are the advantages and disadvantages

Answer
Advantages of DBMS: K02ati!
redundancy: It controls data redundancy because it stores
1 Database
all the data in one single database file and that recorded data is placed
722in the database.
 Page 2 of 32

1-3A (OSTT-Sem-5)
Management System users of an organization can
Database authorized
DBMS, the
sharing: In users.
Data among multiple maintainable due to the centralized
share the data can be easily
maintenance:It
. Easy
nature of the database
system.
development time
and maintenance need.

Reduce time:It reduces and

recovery subsystems which create
4. provides backup hardware and software failures and
Backup: It of data from
5 automatic backup required.
restores the data if provides different types of user interfaces
interface: It application program interface
Multiple user
6 user interface,
like graphical DBMS:
speed of do.
Disadvantages of : It requires high
Cost of and software run DBMS software.
bardware memory
1. size to memory to run
processor and large space of disks and large
a large
occupies
Size : It and
2
efficiently. creates additional complexity
system
Complexity : Database database
requirements.
:Failure is highly impacted the a single
impact of failure in
all the data stored failure or
4 Higher organization,
to electric
because in most of the database is damaged dueforever.
database and if the may be lost
the data
database corruption then Describe
by database
users ?
What do you understand
Que 1.3. users.
different types of database
the
Answer benefits of database. The
one who use and take the te
Database users are the depending on the need and way of accessing
users
different types of
database are:
L Application programmers: the database by
means
interact with
a They
are the developers who
of DML queries. programs like C,
application
b. These DML queries are written in the
C+t,JAVA, Pascal etc. communicate with
to
C These queries are converted into object code
the database. .lte
2. Sophisticated users : queriesto
select

a. They database developers, who

are write SQL
insert/delete/update data.
means ofquery
b. They directly interact with the database by
like SQL. thoroughy
who
C. These users can be scientists, engineers, analysts requiremen.
study SQL and DBMS to apply the concepts in their
 Page 3 of 32

14A(CSIT-Sem-5) ioisveiig tiSu Introduction

3. Specialized users: r
Ein ?
(roitxiu
ga. These are also sophisticated users, but they write special database
'se appication programs.g
They are the developers who develop the complex programs
b

according to the requirement.

4. Standalone users :
a These users will have standalone database for their personal use.
b. These kinds of database will have predefined database packages
which will have menus and graphical interfaces.
5. Native users :
These are the users who use the existing application to interact
with the database.
b
For example, online library system, ticket booking systems, ATMs
etc.
Que Who are data administrators ? What are the functions
ld.
of database administrator?
OR
Discuss the role of database administrator.
AKTU2017-18, Marks 10

AnsVer
over data and
Database administrators are the personnel's who has control
programs used for accessing the data.
Functions/role of database administrator (DBA):
1. Schema definition:
a. Originaldatabase schema is defined by DBA.
are
b. This is accomplished by writing a set of definitions, which
translated by the DDL compiler to a set oflabels that are permanently
stored in the data dictionary.
:
2 Storage structure andaccess method definition
deue
a. The creation of appropriate storage structure and access method.
b. This is accomplished by writing a set of definitions, which are
translated by the data storage and definition language compiler.
3. Schema and physical organization and modification :
a. Modification of the database schema or the description of the physical
storage organization.
b. These changes are accomplished by writinga set of definition to do
modification to the appropriate internal system talbles.
 Page 4 of 32

Database Management System (1-5A(CSTT-Sem-5)

4. Granting of authorization for data access : DBA grants different

types ofauthorizationfor data access to the various users offthe database.
: DBA carry.out dot
5. Integrity constraint specification
administration in data dictionary such as defining constraints.

Que 1.5. What is data abstraction ? Explain different levels of

abstraction.
Answer
Data abstraction is the process of finding irrelevant details from user i.e..
hiding the background details from the users.
Different levels of data abstraction :ik -tt dt 2!
View levelrul
View 1 View 1 View n

Logical level

Physical level
Fig, 1.5.1. The three levels of data abstraction,
:
1. Physical level
i Physical level is the lowest level of abstraction and describes how
the data are actually stored.
The physical level describes the complex low-level data structures
in details.
2 Logical level :
'otri
Logical level is the next-higher level of abstraction and it describes
what data are stored in the database, and what relationship exists
among those data.
iü. The logical level thus describes the entire database terms a
in of
small number of relatively simple structures.
3. View level:
View level is the highest level of abstraction;
it describes only part
of the entire database.
*iy
of abstraotio
in , The view level
with
exists to simplify their interaction
the system,I.sli.)i: isoibo
ii The system may provide many views for the same
database.
 Page 5 of 32

1-6A (CSIT-Sem-5) tiainyc i3aognerIntroduction

One 6. Explàin the differences between physical level,

conceptual level and view level of data abstraction.rgi
AKTU 2016-17, Marks 10

Answer
S.No. Physical level Conceptual/ View level
Logical level hd4l
1. Thisis the lowest |This is the middle levelThis is the, highest
level of data of data abstraction. level of data
i abstraction. abstraction.
It describes howIt describes what data It describes theuser
Godata is actually is stored indatabase. interaction with
stored in database. database system.
3. It describes the It describes the It describes only those
complex low-level structure of whole part of the database
data structures in database. and hides in which the users are
detail. details of physical interested and hides
storage structure. rest of all information
siiti from the users.
aigrs

4 useris not aware A user is not aware of A user is aware of the

A

of the complexity the complexity. of|complexity of

of database. database.ng s database. s1
Que l7. Explain the difference between database management
system (DBMS) and file system.
Anser
S.No. DBMS FileSystem
1. In DBMS, the user is not|In this system, thé user has to
required to write the write the procedures for managing
procedures. the file.
2. DBMS gives an abstract view File system provides the detail of |
o
2rof data that hides the details. the data representation and
istorage of data. iutr
 Page 6 of 32

1-7A(CST-Sem)
Management System
Database
File system do not have a
DBMS. provides. a, crash| Crash
mechanism, i.e., if the system
3.
recovery mechanism, i.e.,
from| crashess while enteringssome
DBMS protects the data data
failure. then the content of the file will
the system lost.
is very difficult to protect a
DBMS provides a
good| It file
4
under the file system.
protection mechanišm.
File system cannot eficiently
DBMS can efficiently store and| store
5.
retrieve the data. and retrieve the data.
Ifor
6 DBMS takes careofIn the file system, concurrent
concurrent access of data using access has many problems l1
, redirecting the file while other.
some form of locking.
deleting some information or

updating some information.

Discuss the architecture of DBMS. What are the types

of DBMS architecture ?iwt,

Answer
client
The DBMS design depends upon its architecture. The basic
server architecture is used to deal with a large number of PCs, web
servers, database servers and other components that are Connected
with networks.
2. architecture depends upon how users are connected t0 ne
DBMS
database to get their request done.
Types of DBMS architecture:i sSih e

i 1-Tier architecture :
user.
1 In this: architecture, the database
is directly available tothe
It does
directly done on the database itself.
2.
Any changes done are
not provide a handy tool for end users.
local
3. The 1-Tier architecture
used for development--of
is
with
the
application, where programmers can communicate
directly
atite the database for the quick response.
ii. 2-Tier architecture:
*
1.i The 2-Tier architecture is same as basic client-server:
can
end For
bt2.. In the two-tier architecture, applications on the clientside.
directly còmmunicate with
the database at the server
this interaction, API's such as:
ODBC, are used.
JDBC
2
The user interfaces and application programs
client-side. are
 Page 7 of 32

1-8A (CSTT-Sem-5) L5veJnossDRMi Introductiòn

3 The server side is responsible to provide the functionalities like
query processing and transaction management.
4. To communicate with the DBMS, client-side application establishes
a connection with the server side.

Database system Server

Application
Client
User

Fig. architecture.
l8.1. 2-Tier
iüi. 3-Tier architecture:
The 3-Tier architecture contains another layer between the client
and server. In this architecture, client cannot directly communicate
25D,ith the erver.
2. The application on the client-end interacts with an application
server which further communicates with the database system.
3
End user has no idea about the existence of thenodatabase beyond
any
the application server. The database also has idea about
other user beyond the application.
r4 The 3-Tier architecture is used in case of large web application.

Database
Server
Application server

|Application client
Client

User
tobsa,Ieroining
 Page 8 of 32

Database Management System

1-9A(CS/T-Sem5

PART-2
Data Model Schema and Instances, Data Independence
Database Language and Interfaces, Data Definition and
Language DML, Overall Databasee Structure.

Questions-Answers
Long Answer Type,and Medium Answer Type Questions

Que 1.9. What are data models ?

Briefly explain different types of
data models.
Answer
Data models :
Data models define how the logical
structure
of the database is modeled.
2 Data models are a collection
of conceptual tools for describing
relationships, data semantics data, data
and consistency constraints.
3. Data models
define how data is connected
are processed and to each other and how they
stored inside the system.
Types of data models:
1 Entity relationship model:
a. The entity relationship (ER)
model consists of a collection basic
objects, called entities of
and of relationships among
b
Entities are represented by means
these entities.
attributes. of their properties, called

(Attribute) (Attribute)
tsite ii (Attribute (Attribute)

Entity
Relationship
Entity

Fig. 1.9.I The

Odel
2. Relational
model :
Therelational
model represents
by a collection data and Irelationships among data
of
tables, each of which has a
with unique names. number of columns
 Page 9 of 32

1-10 A (CSIT-Sem-5) itotge in9reIntroduction

2
b.'r Relational data model is used for data storage and processing.
C. This model is simple and it has all the properties and capabilities
1wi3ir required to process data with storage efficiency.stef i4
8 Hierarchical model:
a In hierarchical model data elements are linked as an inverted tree
structure (root at the top with branches formed below).
b. Below the single root data element are subordinatè elements each
of which in turn has its own subordinate elements and so on, the
tree can growtomultiple
levels.s3& e828Li shiaisi
Data element has parent child relationship as in a tree.
4. Network model:
a. This model is the extension of hierarchical data model.
In this model there exist a parent child relationship but a child data,
oe element can have more than one parent
element or no parent at
all.
5. Object-oriented model :
Object-oriented models were introduced to overcome
shortcomings of conventional models like relational, hierarchical
the
and network model.
b An object-oriented database is collection of objects whose behaviour,
state, and relationships are defined in accordance with object
oriented concepts (such as objects, class, etc.).
Que 1.10. Describe data schema and instances. seini
Answer
1
The description of a database is called the database schema, which
specified during database design and is not expected to change
2
Most of the data models have certain convention for displaying
oisas diagram which is called as schema schema
diagram.',is ot 2isigy J!
3. A schema diagram displays
only some aspects of a schema, such as the
to(names record types and data items, and some types of constraints.
of

For example: Schema diagram for studentinfo

Student (Name, Student_number, Class, Branch)
database.
(Course_name, Course_number, Department)
Instances :
1
The datain the database at a particular, moment, is called, a
database
-state or snapshot. It is also called the current set occurrences
of or
instances in the database:
 Page 10 of 32

Database Management System 1-11A(CS/IT.

2, In a databasco state, each schemna construct has its own current

instances.
Many database states can be constructed to correspond
to particular
databaseschema. Every time we insert or delete a record a
or
a a
value of data item in record, we change one state ofthe change
database the
another state. into
Que 1.11 Describe data independence with its types, st
.

OR
Explain data independence with its types.
AKTU 2019-20,
Marks 07

Answer
Data independence:Data independence
is defined as the capacity to chano
the schema at one level of a database system
schema at the next without having to change t
higher level.
Types ofdataindependence:
1. Physical data independence:
Physical data independence is
2tornal
the ability to modify i
without changing the conceptual schema. schema
b. Modification at the physical
level is occasionally necessary order
to improve performance. in
C. It refers tothe immunity of the conceptual
internal schema. eri schema to change in the
Lerisn si g

d Examples of physical data independence are

reorganizations of
files, adding a new access path or
modifying indexes, etc.
2Logical data independence: r
a Logical data independence
is the ability to modify the conceptual
schema without having to change
the external schemas or
Gapplication programs.
b
It refers to the immunity of the
ad3 conceptual model.
external model to changes in tie
c.
Examples of logical data
. independence are addition/removal
entities.siot
Que 1.12.| Describethe ssbrt
type of language is SQL?
classificoti92 of
database language.
Which

Discuss thefollowing OR
terins (i) DDL Command
(ii)
DMIGComimand

U
AKYU 2019-20 Marks
 Page 11 of 32

1-12 A (CSSIT-Sem-5) inn inbtean Introduction

Answer
Classificntion of datnbnso lnngunges : oalfs eozush jncs
:
1, Data Definition Longunge (DDL)
a. DDL is set of SQL commands uscd to create, modify and delete,
BaO database structures but not data." ioT St)3 ,AA
b. They are uscd by theDBA toa limited extent, a database designer,
or application devloper.
C. Create, drop, alter, truncate are commonly used DDL Command.
Data Manipulation Language (DML) :
a. A DML is a language that enablès users to access or manipulates
data as organized by the appropriate data model.
There aretwo types of DMLs
b :

i. Proccdural DMILs : It requires a user to specify what data

are necded and how to get those data, g 9

ii. Declarative DMLs (Non-procedural DMLs) :It requires

a user to specify what data are needed without specifying
how to get those data.
C. Insert, update, delete, query are commonly used DML commands.
3. Data Control Language (DCL):
a. It is the component of SQL statement that control access to data
and to the database.
b. Commit, rollback command are used in DCL.o3nor.
4. Data Query Language (DQL) :
a. It is the component of SQL statement that allows getting data
from the database and imposing ordering upon it.
b. It includes select statement.
5. View Definition Language (VDL):
VDL is used to specify user views and their mapping to conceptual,
oul
schema.
2. Itdefines the subset of records available to classes of users.nin't
3. It creates virtual tables and the view appears to users like conceptual
level.
4. It specifies user interfaces.s021.2 ae*:ivo 4s1

Que 1.13. Explain alldatabase languages in detail with example.

bas qtswkqz ts 9enisinb ao anaFAKNU2017-18, Marksi10
 Page 12 of 32

Database Management System 1-13 A(CSIT-Sem-5)

Answer
Database languages: Refer Q. 1.12, Page 1-11A, Unit-1.
:
Examples
DDL:to
CREATE, ALTER, DROP, TRUNCATE, COMMENT, GRANT,
REVOKE
statementts iroltil siigebos
DML:
INSERT, UPDATE, DELETE statement
DCL:
a
GRANT and REVOKE statement
DQL:
SELECT statement
VDL:
create view emp5
1.
select *
asts
fromemployee.ohh0th n
e
where dno = 5;
Creates view for dept 5 employees.irzus isy o

2. create view empdept as

select fname, Iname, dno,
c dname.
from employee, department:) y32
T,2 l"raod sl s
where dno=dnumber ;
ait hdti l
Creates view using two tables.s
:uihi ho
Que l.l4. Explain DBMS interfaces. What are
the various DBMS
interfaces ?

Answer
DBMS interfaces :A database management system
user interface which allows for (DBMS) interface is a
the ability input queries to a database
to
without using the query language itself.
Various DBMS interfaces are: 9tocr2 d: gricb 11

.Menu-based interfaces for web clients orbrowsing : !1

These interfaces present the user with lists of options (called menus)
that lead the user through the formulation of a reqest.
b. Pull-down menus are a very popular technique in Web-based user
interfaces.
C. They are also often used in browsing interfaces, which allow a user
to look through the contents of a database an
in exploratory and
unstructured manner.
 Page 13 of 32

1-14 A (CSIT-Sem-5) Introduction

:
2. Forms-based interfaces
a. forms-based interface displays a form toeach user.
A

b
Users can fillout all of the form entries to insert new data, or they
can fill out only certain entries, in which the DBMS will retrieve
matching data for the remaining entries.
3. Graphical user interfaces (GUI) :
a. A GUI typically displays a schema to the user in diagrammatic
form.
b
The user then can specify a query by manipulating the diagram. In
many cases, GUIs utilize both menus and forms.5eql.
:
4. Natural language interfaces
a. A natural language interface has its own schema, which is similar
to the database conceptual schema, as well as a dictionary of
important words.
b
The natural language interface refers to the words in its schema,
as well as to the set of standard words in its dictionary to interpret
the request.
c.
If the interpretation is successful, the interface generates a high
level query corresponding to the natural language request and
submits it to the DBMS for processing; otherwise, a dialogue is
started with the user to clarify the request.
5. Speech input and output :
a. The speech input is detected using a library of predefined words
and used to set up the parameters that are supplied to the queries.
b. For output,a similar conversion from text or numbers intospeech
takes place.
6. Interfaces for the DBA:
Most database systems contain privileged commands that can be
used only by the DBA's staff.
b. These include commands for creating accounts, setting system
parameters, granting account authorization, changing a schema,
and reorganizing the storage structures of a database.
Que 1.15. Briefly describe the overall structure of DBMS.
OR : i

Draw the overall structúre of DBMS and explain its components in

brief. AKTU 2018-19, Marks 07

A database system is partitioned into modules that deal with each of the
responsibilities of the overallsystem. The functional components of a database
system can be broadly divided into two components iieizno9 A

-yriloifirO9 Žsoclirw bssoorq aaois3z9 oituRaST)

 Page 14 of 32

Database Management System

1-15A
(
Application
Naive Users Sophisticated
Programmers
users Database
Administrator
Use Write
Use
Use
Application Application
Interfaces Programs Query Tools Administrative

Tools)
Compiler and
Application Linker DML Queries
Program Object DDL
Interpreter
Code

DML Compiler
t k
s6 and
Organizertt o
.
|Query Evaluation
Engine

sQuery processor
Buffer
manager File Authorization
manager and ransaction
integrity manager
manager

7 Storage manager

Indices
Data
Dictionary Disk storage iu
D2!
Data
Statistical
data

ENSI Fig.115.1. Si5:8 as

L.
Storage Manager Overall database
(SM):Astorage manager structure.
ti provides the interface
between the low
and the application programs is a program module that
level data
SM components and queries submittedstoredinthe datab
include': to the system.
Authorization
satisfaction of and integrity manager
orls 30
d'nusers to access integrity constraints and : It tests for theof
sandrb.h Trangaction data. iyoit dgti checks the aúthority
manager: baenoil;a ei
a
consistent state It ensures
despite of system that the: database: remains in
transaction executions failures 'and
proceed without that conçurrent
conflicting.
 Page 15 of 32

1-16 A (CSSTT-Sem-5) isteIntroduction

File manager :It manages the allocation of space on disk storage
and the data structures are used to represent information stored
on disk.
d. Buffer manager : It is responsible for fetching data from disk
storage into main memory and deciding what data to cache in main
memory. The buffer manager is a critical part of the database
system, since it enables the database to handle data sizes that are
much larger than the size of main memory.
2. Query Processor (QP) : The Query Processor (Query Optimizer) is
li responsible for taking every statement sent to SQL Server and figure
out how to get the requested data or perform the requested operation.
The QP components are :

DDL interpreter: It interprets DDL statements and records the

definition in data dictionary.
b. DML compiler: It translates DML statements in a query language
into an evaluation plan consisting of low-level instructions that the
query evaluation engine understands.
Query optimization : It picks the lowest cost evaluation plan
from among the alternatives.
d. Query evaluation engine : It executes low-level instructions
generated by the DML compiler.

PART-3
Data Modeling using the Entity Relationship Model: ER Model Concepts
Notation forER Diagram, Mapping Constraints

Questions-Answers

Long Answer Type and Medium Answer Type Questions

Que 1.16. What is ER model? What are the elements of ER model$

What are the notations of ERdiagram ?1iiEDfVIS Sj 2a41lav

Angwer
An entity relationship model (ER model) is a way. of representing the entities
create aa database.
and the relationships betweenthe entities in order to create
 Page 16 of 32
1-17A((OSISema
System
Management
Databaso modelldiagram :
Elements/notation ofCR
that can be easily identifiable.
1. Entity: a real world object
Anentity is
can be abstract.
An entity
object that exists and is distingumshable rom othe
an
An entity is
C.
objects.
9
set : of similar type of entities.
2 EntityEntity set is a collection
contain entities with attribute
sharing similk
Sa. b. An entity
set may

tcivalues.
3. Attribute: characteristics of the entity, ii
An attribute gives the
ata also called as data
field, a field,
element, datafe
a data item, oran
b
It
is 0:daersi
U2 elementary item,!i
Relationship :
4. entities or enti
A
relationship is the association between
a.
Occurrence. Bna

Relationship is represented by diamond with straight

b. 23
2013 connecting the entities.

Que 1.17. What do you understand by

attributes and domain
conceptual data mode!
Explain various types of attributes used in
Answer
Attributes:
are used to represent the entities.
1 Attributes are properties which
2. All attributes have values. For example,
a student entity may hai
name, class, and age as attributes. attributes
3 There exists a domain1or range of values that can be assignedlto
hastob
4. For example, a student's name cannot be
a
numeric value. It
alphabetic. A student's age cannot be negative, etc.
Domain: a
type ofdis
1
A
domain is an
attribute constraint which determines the
values that are pernmitted for that attribute.
2. Attribute domains can be very large, or very short.
Types of attributes used in conceptual data model:
çan
L Simple attribute: Simple attributes values, which
be divided further.
Ior example, a
value of 10 digits.
number is
an
stdLULRanato
 Page 17 of 32

1-18A (CSIT-Sem-5) ?i Tntroduction

2. Composite attribute : Composite attributes are made of more than

one simple attribute. For example, a student's complete name may have
first _name and last name.
3. Derived attribute:Derived attributes are the attributes that do not
exist in the physical database, but their values are derived from other
attributes present in the database. For example, average_salary in a
department should not be saved directly in the database, instead it can
be derived.
4 Single-value attribute : Single-value attributes contain single value.
For example, Social_LSecurity_Number.
5. Multi-value attribute: Multi-value attributes may contain morethan
one values. For example, a person can have more than one phone
number, email_address, etc.
Que l.18. What is purpose of the ER diagram ? Construct an ER
diagram for a University system which should include information
about students, departments, professors, courses, which students
are enrolled in which course, which professors are teaching which
courses, student grades, which course a department offers.
Answer
:
Purpose of the ER diagram
ER diagram is used to represent the overall logical
structure of the
database.
2. ER diagrams emphasis on the schema of the database
and not on the
instances because the schema of the database is changed rarely.
to end
3. It is useful to communicate the logical structure of database
users.
4. It serves as a documentation tool.
to be
5. It helps the database designer in understanding the information
contained in the database.
ER diagram :
Professor Course (Student Student
name teaching grade
enrolled in
2
Course
ProfessorsDepartment <University> Course durationy

type
Course
name department)

vIU0O r Eig 1181 ER diagram for Oniversitysystem. ssisllva

 Page 18 of 32
Database Management System
19A(CT
Que 1.19, Draw an ER diagram for a
small marketing
database, assuming your own data requirements,
com

AKTU6-)
Answer Mano

addross

Store

Payment

KAvailablo
D Product

Product

Address

Salary

Demand

Employee

name

KEmployee

itema
(Empoyee
delkvery

of Deliverv

ID(No.
name)
of
Number

7
KInitiates

Order Promotion
codeD(Promno

type

Availability) No.
Shipping
ecation
Order Tracking

Pie
D) Prom0

Product

name

uct

thee0:4 A.university
following
sylabus entities registrar's
(a) courses, office maintains dataan
and prerequisites;
including title, c (b) course number, co
offerings, including
 Page 19 of 32

1-20 A (CSIT-Sem-5) Introduction

number, year, semester section number, instructor(s), timings and

classroom; (c) students, including student-id, name and program;
and (d) instructors, including identification number, name
department and title. Frther the enrollment of students in courses
and grades awarded tostudents in each course they are enrolled
for must be appropriately modeled. Construct an ER diagram for
the registrar's office. Document all assumption that you make
about the mapping constraints. AKTU 2015-16, Marks 10

Answer
In this ER diagram, the mnain entity sets are student, course, course offering
and instructor. The entity set course offering is a weak entity set dependent
on course. The assumptions made are
::ial iovs! t8.
a.í A class meets only at one particular place and time. This ER diagram
cannot model a class meeting at different places at different times.
b. There is no guarantee that the database does not have two classes
so meeting at the same place and time, bts r ii
i

s_id name time secno rOOm name

i_id

student enrolls cOurse teaches

offerings instructor

year (semester deptt title

program grade

Koffered

syllabus courseno

prerequisite
requires cOurse title
maincourse

(credits)

ig 0,119R diagram for University

Que 1.21 Describe mapping constraintswith types.
its
OR
Describe mapping constraints with its types.
AKTU2019-20 Me
 Page 20 of 32

Database Management System ACsIT-So

AnswVer
1. Mappingconstraints act as arule followed by contents of database
2
Data in the database must follow the constraints. it e
5Toc
Types of mapping constraints are:oi: iTGOGtyEs su:
1. Mapping cardinalitios.!} troetRuof? g'sdnoe
a. Mapping cardinalities (or cardinality ratios) specifies the number of
entities of which another entity can be associated via a relationshin
set.
sb. Mapping cardinalities are used in describing binary relationship
islins sets, although they contribute to the description relationship
of
sets that involve more than two entity sets.3h
st
C. For binary relationship set R between entity sets A
mapping cardinality must be one of the following:Rs
and B, the.
One to one: An entity in A is associated with at most one
entity in B and an entity in B is associated
with at most one
entity in A.
A B

ite
b

ag ba

Fig l211
ii. One to many:An entity in A is associated any number
of entities in B. An entity in B, however, can with
be associated with
at most one entity in A.
B

b,
a

b4

iii. Many to one : An entity

in A is associated with at most one
entity inB, and an entity B,
any number of in however, can be associated wiu
entities inA.
 Page 21 of 32

1-22 A (CSIT-Sem-5) n:Tntroduction

B
A

a4 ba

Fig. l21.3.
any
iv. Many to many An entity inAis associated with any number
:

of entities in B,and an entity in B is associated with number

of entities in A,

an b

a
bg

OST:taebi sL2.t

Fig l 214
of entity sets.
Participation constraints It tells: the participation
:
2.
There are two types of participations yad
Partial participation s2i jite
S0i 2ittsrebi vlerpi 9siev ioreinn03
Total participaion

PART-4
Key,
Keys, Concept ofSuper Key, Candidate
Pruman Key, Generalization, AggTegation.

Questions Answers
TVpe Questions
Long Answer Type and Medlium Answer

super key,
Que 122.| Discuss the candidate key, primary key,
composite key and alternate key.
 Page 22 of 32

Database Management System 1-23 A (CST-Sem

OR
Explain theprimary key, super key, foreign key andlcandidate
key
with example. AKTU 2017-18, Marks n

OR
Define key. Explain various types of keys.
AKTU 2019-20, Marks07
Answer
Key is a attribute or set of
1

attributes that is used to identify data in

entity sets.
2. Key is defined for unique
identification of rows table.
Consider the following example an in
of Employee table :
Employee (EmployeelD, FullName,
SSN, DeptID)
Various types of keys are :
1, Primary key :
a. Primary key uniquely
never, be the same identifies each record in a table and must
for records.
choose either EmployeelD or Here in Employee table we can
SSN columns as a primary
b
Primary key is a candidate key.
key that is used for unique
of entities within the table. identification
C. Primary key cannot be null:
d. Any table has a unique
primary key.
Super key:
a, Asuper
key for an entity
combined value uniquely is a set of one or more attribute whose
identifies the entity
b
For example : Here employee in the entity set.
(EmployeeID, FullName,in table (EmployeelD, FullName) or
DeptID) is a super
3 Candidate key.
key:
A candidate key is a
column, or set of column,
uniquely identify any in the table that can
other data. database record without
referring toany
b. Candidate key are individual
uniqueness of all the rows. columns in a table that qualifies tor
and SSN are candidate Here in Employee
keys. table Employee
C, Minimal super keys are
called candidate keys.
 Page 23 of 32

1-24 A (CSIT-Sem-5) Introduction

4 Composite key :
A composite key is a combination of two or more columns in:a
table that can be used to uniquely identify each row in the table.
b. It is used when we cannot identify a record using single attributes.
C. A primary key that is made by the combination of more than one
.
sttribute is known as a composite key.
Alternate key:
a. The alternate key of any table are those candidate keys which are
not currently selected as the primary key.
b. Exactly one of those candidate keys is chosen as the primary key
s
and the remainders, if any are then called alternate keys.
C.
An alternate key is a function of all candidate keys minus the
prìmary key.
d. Here in Employee table if EmployeeID is primary key then SSN
would be the alternate key.itsil:
:
6. Foreign key
betweenen tables and
a. Foreign key represents the relationship
ensures the referential integrity rule. i slacsrg i0
same or some
b.A foreign key is derived from the primary key the
of

(naaother table.
one or more columns ina table
C. Foreign key is the combination of (child
(parent table) at references a primary key in another table
table).
A foreign key value
can be left null..
d :

For example : Consider another table

EmployeelD)
Project (ProjectName, TimeDuration,
points to the
a. Here, the EmployeeID' in the Project' table
EmployeelD' in 'Employee' table sbttdaigsil )
b. The 'EmployeelD' in the Employee' table is the
primary key.
C. The EmployeeID' in the Project' table is a foreign key.

Que 123. What do you mean by a key tothe relation ? Explain the
differences between super key, candidate key and primary key.
10
BMarks
AKURZ0DO

Key: Refer Q. 1.22, Page 1-22A, Unit-1.

 Page 24 of 32

Database Management System 1-25 A (CS/IT-Sem-k

Difference between super key, candidate key and primary kev.

S. No.
Super key Candidate key Primarykey
1. Super key is an Candidate key is a Primary key is a
attribute (or set ofminimal set of super minimal set of
attributes) that is key. attributes that
used to uniquely uniquely identifies
identifies all rows in a relation.
attributes in a
relation.
2
All super keys All candidate keys are
Primary key is a
cannot be super keys but not subset of candidate
candidate keys. primary key. key and super key.
|

3.It can be null. It can be null. It cannot be null.

4. A relation can haveNumber of candidate
Number of primary
any number of keys is less than keys is less than
Super keys. super keys. candidate keys.
5
For example, in For example, in For example,
Fig. 1,23.1, super Fig. 1.23.1, candidate Fig. 1.23.1, in
primary
key are: key are: key is: (Registration)
(Registration), (Registration,
bl:dusVehiçle id). Vehicle_id)
(Registration,
Vehicle_id),
(Registration,
Vehicle_id, Make)
etc.

Registration Vehicle id

osiog

Year CAR 5e Model

Colour Make
ig, 1:23.1 An entioND
.0AR for defining keys,
0f:vu
 Page 25 of 32

1-26 A (CSTT-Sem-5) Introduction

Que 1.24. Explain generalization, specialization and aggregation.

OR
Compare generalization, specialization and aggregation with
suitable examples. AKTU 2018-19, Marks 07

Answer
Generalization:
. Generalization isa process in which two lower level entities combine
to form higher level entity.
b. It is bottom-up approach.
C. Generalization is used to emphasize the similarities among lower level
entity sets and to hide the differences.
:
For example Generalization

Account

Saving Current
Fig. 124.1.
:
Specialization into lower
Specialization is a process of breaking higher level entity
232070
level entity.
b
It is top-down approach.
c.
Itis opposite to gemeralization.
:
For example Specialization

Person

|EmployeeCustomer
Fig. l242.
:
Aggregation
are treated
a. Aggregation is an abstraction through which relationships
as higher level entities.
:
For example
1. The relationship works_on (relating the entity sets employee, branch
and job) act as a higher-level entity set. on
2. We can then create a binary relationship Manages', between works
and manager to represent who manages what tasks.
 Page 26 of 32

Database Management System 1-27 A (CSTT-Sem-5)

Job

|Employee Works_on Branch

Manages

Manager
Fig. 124.3. ERdiagram with aggregation.
:
Comparison
S. No.
Generalization Specialization Aggregation
1. In generalization, In specialization, an Aggregation is an
the common entity of higher-level |
abstraction through
attributes of two orentity is broken down which relationships
more lower-level into two or more are treated as higher
entities combines entities oflower level. level entities.
to form a new
higher-level entity.
2
Generalization is a Specialization is a top- It allows us to
bottom-up down approach. indicate that a
approach. relationship set
participates in
relationship
Liiahother set.
3. It helps in reducing It increases the size It also increases the
the schema size. of schema. size of schema..
4. Itis appliedtogroup It can be applied to a It is applied to group
of entities. single entity. of relationships.

PART-5
an
Reduction of
Diugram to Tables, Etended
ER ER Model
cie6 RelationshipofHighen Degree,

49Questions Answers
fio ong Answer Type and
MediumAnswerType.Questtons
 Page 27 of 32

1-28 A (CSIT-Sem-5) Introduction

ue 125. Explain the reduction of ER schema to tables.

OR:y
How to reduce an ER model into table?a.ti 2.
Nud
Answer
1. In ER model, database are represented using the different notations or
diagrams, and these notations can be reduced to a collection of tables.
2
In the database, every entity set or relationship set can be represented
in tabular form.
Consider following ER diagram:
Subject_Name
Lecturer_ID)

Course_Name
Subject_ID

Course_ID

JTUieb:

Course Subject
M
Has

Course_ID:

State
Pin Attends
Takes Teaches

Course_ID

City

isutivi|š)(Street)(
Address
Teaches Lecturer_Namne

Door_No)

ID)
Student_Name

ID Lecturer

Student
Age
Hobby
 Page 28 of 32

Database Management System 1-29 A (CSIT-Sem.A)

Basic rles for converting the ER diagrams into tables are.

1. Convert allthe entities in the diagram to tables :
Allthe entities represented in the rectangular box in the
a.

ER diagram
become independent tables in the database.
b. In the ER diagram, Student, Course, Lecturer and Subjects fo
individual tables.
Al single-valued attribute becomes a column ifor the table:
Allthe attributes,whose
value at any instance of time is unigue
are considered as columns of that table.
b. In the Student entity, Student_Name and Student ID formithe
column of Student table. Similarly, Course_Name
and Course ID
form the column of Course table and so on.
3 Akey attribute of the entity is the primary key:
a. All the attributes represented in the oval shape
and underlined in
the ER diagram are considered as key attribute which act as a
primary key of table.
b. In the given ER diagram, Student_ID, Course_ID, Subject ID, and
Lecture ID are the key attribute of
the Student, Course, Subjects
and Lecturer entity.
4. The multivalued attribute is represented by a separate
table:
a. In the student table, a hobby is a multivalued
attribute.
b. So it is not possible torepresent multiple
values in a single column
ofStudent table. Hence we create a table Stud Hobby
name Student_ID and Hobby. with colum
Using both the column, we create a
composite key.
5. Composite attributes are merged into same
columns : table as differen
a In the given ER diagram, student addressis a composite
attribute
It contains City, Pin, Door_No, Street, and
b
State.
In the Student table, these attributes can merge as an
column. indiviau
6 Derived attributes are not considered
in the table :
In the Student table, Age is the
derived attribute.
b
It can be calculated at any point of time by calculating
between current date and Date the difer
of Birth (DoB).
 Page 29 of 32

1-30 AnsTSem-5)
A'(
KCS2 inoxgSi Tntroduction

Table structure for given ER diagram is:

Student Lecturer Subject

Student_ID Lcturer_ID Subject_ID
Student_Name Lecturer_Name
DoB Subject_Namel
Course ID Lecturer_ID
Door_No
Street
City
State Course
Course _ID
Pin
Course ID Course_Name

Stud_Hobbey
Student _ID
Hobby

Que 1.26 Discuss extended ER (EER) model.

AnsTer
1
The ER model that is supported with the additional semanticconcepts
is called the extended entity relationship model or EER model.
2. The EER model includes all the concepts of the original ER model
together with the following additional concepts:
:
a. Specialization Refer Q. 1.24, Page 1-26A, Unit-1.
b. Generalization : Refer Q. 1.24, Page 1-26A, Unit-1.
c.Aggregation : Refer ge l-26A.
3. The super class/subclass entity types (or super type /subtype entities).
is one of the most important modelling constructs that is included in
the EER model.
4. This feature enables us to model ageneral entity andthen subdivide it
into several specialized entity types (subclasses or subtypes):
5. EER diagramsare used to capture business rules such as constraints
iv in the super type/subtype relations. Thus, a super class is an entity
type that includes distinct subclasses that require to be represented in
a data model.
6. A subclass is an entity type that has a distinct role and is also a member
of a super class.
 Page 30 of 32

1-31A (CSITSem-5)
Database Management System
Et 2

( Shared attributes

Superclass

Subclass Subclass

Unique attributes Unique attributes

Fig. 1.26.1. Basic notation of the superclass/subclass relationshin

Que 127. What is Unified Modeling Language ? Explain different

types of UML.
Answer
1 Unified Modeling Language (UML) isa standardized modeling language
enabling developers to specify,visualize, construct and document artifacts
of a software system.
2. UML makes these artifacts scalable, secure and : in execution.
0oust
3. UML is an important aspect involved in object-oriented software
development.
4 It uses graphic notation to create visual models of software systems.
st
Types of UML:
1

1. Activity diagram :

ta. It is generallyused to describe the flow, of different activities and

actions.
b. These can be both sequential and in parallel.
ITni RA
92:c. They describe the objects used, consumedior produced by an activiy
e k
and the relationship between the different activities,xri! j

2. Use case diagram :

a. Case diagrams are
used to 'analyze the system's high,level
requirements.
 Page 31 of 32

1-32A(CSIT-Sem-5) 19a Hcntroduction

b. These requirements are expressed through different use cases.
3. Interaction overview diagram: oiqe23ri)s37e)
a. The interaction overview diagram is an activity diagram made of
different interaction diagrams.t,h sie sisi ai isai
:
4. Timing diagram
a. Timing UML diagrams are used to represent the relations of objects
when the center of attention rests on time.
b. Each individual participant is represented througha lifeline, which
is essentially a line forming steps since the individual participant
transits from one stage to another.
C. The main components of a timing UML diagram are: s
i Lifeline State timeline
iii. Duration constraintiv. Timeconstraint 7.3;
Destruction occurrence
5. Sequence UML diagram:
Sequence diagrams describe the sequence of messages and
interactions that happen between actors and objects.
b
Actors or objects can be active only when needed or when another
object wants to communicate with them.
All communication is represented in a chronological
manner.
C.
:
6. Class diagram
a. Class diagrams contain classes, alongside with their attributes (also
as
referred to as data fields) and their behaviours (also referred to
member functions).
name at the
b. More specifically, each class has three fields: the class
top, the class attributes right below the name, the classoperations/
behaviours at the bottom.
C. The relation between different classes (represented by a connecting
line), makes up a class diagram.

VERY IMPORTANT QUESTIONS

Following questions are very important: These

qestions
may be asked in you SESSIONALS as welLas
UNIVERSITY EXAMINATION.

Q. 1. What is database management system ?

AhE: Refer Q. 1.1.
 Page 32 of 32

Database Management System 1-33A (CSIT-Sem.51

Q.2.Explain the advantages of database management system
over
the simple file processing System,gwi AKooato
Ans. Refer Q. 1.2.
Q.3. What is data abstraction ? Describe different levels of Ao
abstraction.
Ans: Refer Q. 1.5.*n-:

Q.4. Describe the overall structure of DBMS.

Ang Refer Q.1.15.

Q.5. Explain all database languages in detail with example.

Ans: Refer Q. 1.13.

Q.6. Describe the different types of database user.

Ans Refer Q. 1.3.

Q7. Describe the various types of attributes used

in conceptual
ba data model.
Ans. Refer Q. 1.17,

Q8. What is key ? Explain various types

Ans Refer Q. 1.22. of key.

Q.9. Explain extended

ER model.
Ans Refer Q.,
1.26.