DBMS Intro

DBMS
1. ER-MODEL
• Which is a popular high-level conceptual data model, this model and its variations are
frequently used for the conceptual design of database applications, and many database design
tools employ its concepts. ER-Models are used in the design of conceptual schemas for database
applications. The diagrammatic notation associated with the ER model, known as ER diagrams.
ENTITIES: An entity may be an real world object with a physical existence, such as an
employee or it may be an object with a conceptual existence such as a company.
Examples: STUDENT, CAR, COMPANY, UNIVERSITY, etc.
ATTRIBUTES : Each entity has attributes, which are the particular properties that describe
it. Example: EMPLOYEE entity may be described by the employee’s name, age, address, salary,
and job.
TYPES OF ATTRIBUTES
1. Simpleattributes.
2. Composite attributes.
3. Singlevalued attributes.
4. Multivalued attributes.
5. Stored attributes.
6. Derived attributes
 Simple Attribute:
 An attribute that cannot be divided into smaller independent attribute is known as atomic
attribute. Example: The age (attribute) of student (entity) cannot further divide. Age
is atomic attribute.
 Composite attribute:
 An attribute that can be divided into smaller independent attribute is known ascomposite
attribute. Example: The address (attribute) of student (entity) can be further divide into
House no, city and so on.
 Single valued attribute :
 An attribute that has only single value for an entity is known as single valued attribute.
Example:Any manufactured product can have only one serial no, the serial no. can be
subdivided on the basis of region, part no. etc.
 The single valued attribute cannot be simple valued attribute because it can be
subdivided.
 Multi valued attribute :
Department of CSE Page 1

DBMS
An attribute that can have multiple values for an entity is known as multi valued attribute.
Example: Phone no (attribute) of student (entity) can have multiple value because a
student may have many phone numbers.
 Stored attribute
 An attribute that cannot be derived from another attribute is known as stored attribute.
Example: Birth date cannot derive from age of student.
 Derived attribute
 An attribute that can be derived from another attribute is known as derived attribute.
Example: Age cannot derive from birth date of student.
Some other types of attributes are

 Null valued attribute:
 An attribute, which has not any value for an entity is known as null valued attribute.
Example: assume Student is an entity and its attributes are Name, Age, Address and
Phone no. There may be chance when a student has no phone no. In that case, phone no is
called null valued attributes.
 Key attribute:
 An attribute that has unique value of each entity is known as key attribute.
Example: every student has unique roll no. Here roll no is key attribute.
 Complex Attributes:
 Composite and Multivalued attributes can be nested arbitrarily.Such attributes are
called as complex attributes.
 Composite attributes can be represented by grouping components of a composite
attribute between parentheses () and separating the components with commas, and by
displaying multivalued attributes between braces { }.
 Example:if a person can have more than one residence and each residence can have a
single address and multiplephones. Both Phone and Address are themselves
composite attributes.
 {Address_phone({phone(Area_code,Phone_number)},Address(street_address(N
umber,Street,Apartment_number),City,State,Zip))}
Entity Types and Entity Sets

 An entity type defines a collection (or set) of entities that have the same attributes. Each
entity type in the database is described by its name and attributes. Example: a company
employing hundreds of employees may want to store similar information concerning each of
the employees. These employee entities share the same attributes, but each entity has its own
value(s) for each attribute.
 The collection of all entities of a particular entity type in the database is called an entity set.
The entity set is usually referred to using the same name as the entity type. Example:
DBMS
EMPLOYEE refers to both a type of entity as well as the current set of all employee entities
in the database.
Weak Entity Types

 Entity types that do not have key attributes of their own are called weak entity types.
 In contrast, regular entity types that do have a key attribute are called strong entity
types.
 Entities belonging to a weak entity type are identified by being related to specific entities
from another entity type in combination with one of their attribute values.
 This other entity type is the identifying or owner entity type, and the relationship type
that relates a weak entity type to its owner is the identifying relationship of the weak
entity type.
 A weak entity type always has a total participation constraint (existence dependency)
with respect to its identifying relationship because a weak entity cannot be identified
without an owner entity.
 Example: entity type DEPENDENT, related to EMPLOYEE, which is used to
keep track of the dependents of each employee via a 1:N relationship.
DEPENDENT is the weak entity type, EMPLOYEE is the owner Entity type and
DEPENDENTS of is the identifying relationship.
 A weak entity type normally has a partial key, which is the attribute that can uniquely
identify weak entities that are related to the same owner entity.
 Example: if we assume that no two dependents of the same employee ever have
the same first name, the attribute Name of DEPENDENT is the partial key.
Entity-Relationship(ER) Diagram Notations :

DBMS
Symbols Meaning
Relationship Types, Sets, and Instances :

 A Relationship is a association among two or more entity sets. Example: in a company
database the attribute Manager of DEPARTMENT refers to an employee who manages the
department and the attribute Controlling_department of PROJECT refers to the department
that controls the project and so on. This control and manages are the example for
relationship.
 A Relationship set is a set of relationships of the same type.
 A relationship type R among n entity types E1, E2, ..., En defines a set of associations
among entities from these entity types.
 The Degree of a Relationship Type is the number of participating entity types.
o 3 different types of Degree of relationship
 Unary Relationship
 Binary Relationship

DBMS
 Ternary relationship
o Unary Relationship : if number of participating entity type is only one then Its degree is
one. A relationship type of degree one is called Unary Relationship.
 In some cases the same entity type participates more than once in a relationship type
in different roles. In such cases the role name becomes essential for distinguishing the
meaning of the role that each participating entity plays. Such relationship types are
called recursive relationships.
 Example: The SUPERVISION relationship type relates an employee to a supervisor,
where both employee and supervisor entities are members of the same EMPLOYEE
entity set. Hence, the EMPLOYEE entity type participates twice in SUPERVISION:
once in the role of supervisor (or boss), and once in the role of supervisee (or
subordinate).
o Binary Relationship : if number of participating entity type is two then Its degree is
two. A relationship type of degree two is called Binary Relationship.
 Example: the WORKS_FOR relationship type is of degree two since two entity types
EMPLOYEE and DEPARTMENT participate.
o Ternary relationship : if number of participating entity type is three then Its degree is
three. A relationship type of degree three is called Ternary relationship
 Example of a ternary relationship is SUPPLY, shown in Figure in below 7.10, where
each relationship instance ri associates three entities—a supplier s, a part p, and a
project j, whenever s supplies part p to project j.
Constraints on Binary Relationship Types

 Relationship types usually have certain constraints that limit the possible combinations of
entities that may participate in the corresponding relationship set.
 There are two main types of binary relationship constraints:
1. cardinality ratio and
2. participation Constraints
 The cardinality ratio for a binary relationship specifies the maximum number of
relationship instances that an entity can participate in. Example: in the WORKS_FOR
binary relationship type, DEPARTMENT : EMPLOYEE is of cardinality ratio 1:N, meaning

DBMS
that each department can be related to (that is, employs) any number of employees, but an
employee can be related to (work for) only one department.
 The possible cardinality ratios for binary relationship types are 1:1, 1:N, N:1, and M:N.
Participation Constraints and Existence Dependencies
 The participation constraint specifies whether the existence of an entity depends on its
being related to another entity via the relationship type. This constraint specifies the
minimum number of relationship instances that each entity can participate in, and is
sometimes called the minimum cardinality constraint.
 There are two types of participation constraints
1. Total participation constraints and
2. Partial participation constraints
o Example : If a company policy states that every employee must work for a department, then
an employee entity can exist only if it participates in at least one WORKS_FOR relationship
instance Thus, the participation of EMPLOYEE in WORKS_FOR is called total
participation, meaning that every entity in the total set of employee entities must be related
to a department entity via WORKS_FOR. Total participation is also called existence
dependency.
o we do not expect every employee to manage a department, so the participation of
EMPLOYEE in the MANAGES relationship type is partial, meaning that some or part of
the set of employee entities are related to some department entity via MANAGES, but not
necessarily all.
o In ER diagrams, total participation (or existence dependency) is displayed as a double line
connecting the participating entity type to the relationship, whereas partial participation is
represented by a single line.

DBMS
The Entity-Relationship Model(MCQs)
1. An ________ is a set of entities of the same type that share the same properties, or attributes.
a)Entity set
b)Attribute set
c)Relation set
d) Entity model
2. Entity is a _________
a) Object of relation
b) Present working model
c) Thing in real world
d) Model of relation
3. The descriptive property possessed by each entity set is _________
a) Entity
b) Attribute
c) Relation
d) Model
4. The function that an entity plays in a relationship is called that entity’s _____________
a) Participation
b) Position
c) Role
d) Instance
5. The attribute name could be structured as a attribute consisting of first name, middle initial,
and last name. This type of attribute is called
a) Simple attribute
b) Composite attribute
c) Multi-valued attribute
d) Derived attribute
Explanation: Composite attributes can be divided into sub-parts or Attributes that can be
arranged into hierarchy.
6. The attribute AGE is calculated from DATE_OF_BIRTH . The attribute AGE is
a) Single valued
b) Multi valued
c) Composite
d) Derived
Explanation: The value for this type of attribute can be derived from the values of other
related attributes or entities.
7. Not applicable condition can be represented in relation entry as
a) NA
b) 0

DBMS
c) NULL
d) Blank Space
Explanation: NULL always represents that the value is not present.
8. Which of the following can be a multivalued attribute?
a) Phone_number
b) Name
c) Date_of_birth
d) All of the mentioned
Explanation: Name and Date_of_birth cannot hold more than 1 value.
9. Which of the following is a single valued attribute
a) Register_number
b) Address
c) SUBJECT_TAKEN
d) Reference
10. In a relation between the entities the type and condition of the relation should be specified.
That is called as______attribute.
a) Descriptive
b) Derived
c) Recursive
d) Relative
Explanation: Consider the entity sets student and section, which participate in a relationship
set takes. We may wish to store a descriptive attribute grade with the relationship to record
the grade that a student got in the class.
11. Considering binary relationships, possible cardinality ratios are
a) one : one
b) 1 : N
c) M : N
d) all of above
12. Set of values which specifies which values are to be assigned to individual entities is
considered as
a) domain of values
b) composition of values
c) attribution of values
d) derivation of values
Explanation: Domain is value set or set of values specified to particular attribute of entity
type.

DBMS
13. Snapshot of the data in the database at a given instant of time is called
a) Database Schema
b) Database Instance
c) Database Snapshot
d) All of the above
14. Which of the following indicates the maximum number of entities that can be involved in a
relationship?
a) Minimum cardinality
b) Maximum cardinality
c) Greater Entity Count
d) Key constraint
15. Which type of entity cannot exist in the database unless another type of entity also exists in
the database, but does not require that the identifier of that other entity be included as part of
its own identifier?
a) Weak entity
b) Strong entity
c) ID-dependent entity
d) ID- independent entity
16. In a one-to-many relationship, the entity that is on the one side of the relationship is called
a(n) ________ entity.
a) Parent
b) Child
c) Instance
d) Subtype
17. A recursive relationship is a relationship between an entity and ________ .

a) Itself
b) a subtype entity
c) an archetype entity
d) an instance entity
18. In which of the following is a single-entity instance of one type related to many entity
instances of another type?
a) One-to-One Relationship
b) One-to-Many Relationship
c) Many-to-Many Relationship

DBMS
d) Composite Relationship
19. In which of the following can many entity instances of one type be related to many entity
instances of another type?
20. Which of the following is NOT a basic element of all versions of the E-R model?
a) Entities
b) Attributes
c) Relationships
d) Primary keys
21. In which of the following is a single-entity instance of one type of related to a single-entity
instance of another type?
22. Entities can be associated with one another in which of the following?
a) Entities
b) Attributes
c) Identifiers
d) Relationships
23. In a one-to-many relationship, the entity that is on the many side of the relationship is called
a(n) ________ entity.
a) Parent
b) Child
c) Instance
d) Subtype
24. Which of the following gives a logical structure of the database graphically?
a) Entity-relationship diagram
b) Entity diagram
c) Database diagram
d) Architectural representation

DBMS
Explanation: E-R diagrams are simple and clear—qualities that may well account in large
part for the widespread use of the E-R model.
25. The entity relationship set is represented in E-R diagram as

a) Double diamonds
b) Undivided rectangles
c) Dashed lines
d) Diamond
Explanation: Dashed lines link attributes of a relationship set to the relationship set.
26. The Rectangles divided into two parts represents

a) Entity set
b) Relationship set
c) Attributes of a relationship set
d) Primary key
Explanation: The first part of the rectangle, contains the name of the entity set. The second
part contains the names of all the attributes of the entity set.
27. Consider a directed line(->) from the relationship set advisor to both entity sets instructor and
student. This indicates _________ cardinality
a) One to many
b) One to one
c) Many to many
d) Many to one
Explanation: This indicates that an instructor may advise at most one student, and a student
may have at most one advisor.
28. We indicate roles in E-R diagrams by labeling the lines that connect ___________ to
__________
a) Diamond , diamond
b) Rectangle, diamond
c) Rectangle, rectangle
d) Diamond, rectangle
Explanation: Diamond represents a relationship set and rectangle represents a entity set.
29. An entity set that does not have sufficient attributes to form a primary key is termed a
_________
a) Strong entity set
b) Variant set

DBMS
c) Weak entity set

d) Variable set
Explanation: An entity set that has a primary key is termed a strong entity set.
30. For a weak entity set to be meaningful, it must be associated with another entity set, called
the
a) Identifying set
b) Owner set
c) Neighbour set
d) Strong entity set
Explanation: Every weak entity must be associated with an identifying entity; that is, the
weak entity set is said to be existence dependent on the identifying entity set. The identifying
entity set is said to own the weak entity set that it identifies. It is also called as owner entity
set.
31. Weak entity set is represented as

a) Underline
b) Double line
c) Double diamond
d) Double rectangle
Explanation: An entity set that has a primary key is termed a strong entity set.
32. Types of partial constraints are

a) total and partial
b) intensive and extensive
c) minimum and maximum
d) floating and string
33. Relationship type in which one attribute is migrated to N-side of relationship is classified as
a) 4:N
b) 3:N
c) 1:N
d) 2:N
34. For a relationship type, participation constraints and cardinality ratio are considered together
to make
a) intensive constraints
b) recursive constraints

DBMS
c) composite constraints
d) structural constraints
35. Constraint which specifies minimum number of relationship instances is classified as
a) participation constraint
b) non-participation constraint
c) extensive constraint
d) intensive constraint
2. Data Model
Data models define how the logical structure of a database is modeled. Data Models are
fundamental entities to introduce abstraction in a DBMS.Data models define how data is
connected to each other and how they are processed and stored inside the system. Data models
can facilitate interaction among the designer, the applications programmer, and the end user.
Depending on the levels of data, data model divided into 3 categories
1. Object Based
2. Physical and
3. Record based Data models.
1. Object based Data Models

Object based Data Models are based on real world objects. It is designed using the entities in
the real world, attributes of each entity and their relationship. It picks up each thing/object in
the real world which is involved in the requirement.
There are two types of object based data Models
• Entity Relationship Model: It is one of the important data model which forms the basis
for the all the designs in the database world. It defines the mapping between the entities
in the database.

DBMS
• Object oriented data model : Along with the mapping between the entities,
describes the state of each entity and the tasks performed by them. It considers each
object in the world as objects and isolates it from each other. It groups its related
functionalities together and allows inheriting its functionality to other related sub-
groups.
2.Physical Data Models

• Physical data model represent the model where it describes how data are stored in computer
memory, how they are scattered and ordered in the memory, and how they would be
retrieved from memory. Physical data model represents the data at data layer or internal
layer. It represents each table, their columns and specifications, constraints like primary key,
foreign key etc.
3.Record based Data Models

• These data models are based on application and user levels of data. They are modeled
considering the logical structure of the objects in the database. This data models defines the
actual relationship between the data in the entities.
• There are 3 types of record based data models
a. Hierarchical data model: In this data model, the entities are represented in a
hierarchical fashion(tree like structure). Here we identify a parent entity, and its child
entity. Again we drill down to identify next level of child entity and so on.
b. Network data model: It is designed to address the drawbacks of the hierarchical model.
It helps to address M:N relationship. This data model is also represented as hierarchical,
but this model will not have single parent concept. Any child in the tree can have
multiple parents here.
• Relational data models: This model is designed to overcome the drawbacks of hierarchical
and network models. This models define how they are structured in the database physically
and how they are inter-related. It purely based on how the records in each table are related.It
purely isolates physical structure from the logical structure. Logical structure is defines
records are grouped and distributed.
• Most widely used record based data model is relational data model.

DBMS
• A relational data model revolves around 5 important rules.

a. Order of rows / records in the table is not important. Similarly, order of columns in the
table is not important.
b. Each record in the table is unique. That is there is no duplicate record exists in the table.
c. Each column/attribute will have single value in a row.
d. All attributes should be from same domain. That means each column should have
meaningful value.
e. Table names in the database should be unique. In the database, same schema cannot
contain two or more tables with same name. But two tables with different names can have
same column names. But same column name is not allowed in the same table.
Data Model Basic Building Blocks
The basic building blocks of all data models are entities, attributes, relationships and constraints.
 Entity is real world thing, such as a person, place, thing, or event, about which data are to be
collected and stored. Example: CUSTOMER, STUDENT, EMPLOYEE, DEPARTMENT
 Attribute is a characteristic of an entity. Example: a CUSTOMER entity would be described by
attributes such as customer last name, customer first name, customer phone, customer
address, and customer credit limit.
 Relationship describes an association among (two or more) entities. Example: A relationship
between customers and agents that may be described as “an agent can serve many customers
and each customer may be served by one agent. Here serve is a relationship.
 Constraint: is a restriction placed on the data. Constraints are important because they help to
ensure data integrity. And it's normally expressed in the form of rules. Example: An
employee's salary must have values that are between 6000 and 350000 Each class must have
one and only one teacher
Data Model(MCQs)
36. Which of the following is a Data Model?
a) Entity-Relationship model
b) Relational data model
c) Object-Based data model
d) All of the above
37. Logical design of database is called

a) Database Instance
b) Database Snapshot
c) Database Schema
d) All of the above

DBMS
38. ____________ is a classical approach to database design?

a) Left – Right approach
b) Right – Left approach
c) Top – Down approach
d) Bottom – Up approach
39. Which of the following is the oldest database model?

a) Relational
b) Hierarchical
c) Physical
d) Network
40. Which is the most popular database model:

a) Network Model
b) Relational Model
c) Hierarchical Model
d) Object Oriented
41. Form of data model in which concepts provided are useful for end users and are also close to
way it is stored in computer system is called
a) representational data models
b) implementation data models
c) clientele data models
d) both a and b
42. Form of data model which focuses concepts in same way as data stored in computer system
is classified as
a) low level data models
b) high level data models
c) dynamic data models
d) medium level data models
43. In categories of data modeling, low level data models are also called
a) conceptual data models
b) physical data models
c) triggered data models
d) logical data models
44. In categories of data modeling, high level data models are also called
a) conceptual data models

DBMS
b) physical data models

c) triggered data models
d) logical data models
45. Concepts of data models that are only useful to computer specialists rather than end users of
programs are classified as
A. triggered data models
B. logical data models
C. conceptual data models
D. physical data models
46. Which of the following is the building block of Data model

a) Entity
b) Relationship
c) Constraint
d) All the above
47. Which of the following design is both software and hardware independent?
a) Logical
b) physical
c) conceptual
d) none of the above
48. Object based data models are used in describing the abstraction of the following level
a) Only physical
b) conceptual and view
c) physical and conceptual
49. A data model is

a) used to describe structure of a database
b) set of basic operations on the database
c) both A and B
50. Which of the following is an object-based logical model?

a) ER-Model
b) Relational Model
c) Physical model

DBMS
d) All the above
51. Which structure is the hierarchical model based on?

a) Tree
b) Graph
c) Linked List
d) None of these
52. Which structure is the Network model based on?

a) Tree
b) Graph
c) Linked List
d) None of these
3. Relational Model
RELATIONAL MODEL CONCEPTS
 Relational Model represents how data is stored in Relational Databases. A relational
database stores data in the form of relations (tables). The relational model represents the
database as a collection of relations.
 Each relation resembles a table of values each row in the table represents a collection of
related data values. The table name and column names are used to help to interpret the
meaning of the values in each row.
 In the formal relational model terminology, a row is called a tuple, a column header is called
an attribute, and the table is called a relation. The data type describing the types of values
that can appear in each column is represented by a domain of possible values.
IMPORTANT TERMINOLOGIES
 Attribute: Attributes are the properties that define a relation. e.g.; ROLL_NO, NAME
 Relation Schema: A relation schema represents name of the relation with its attributes. e.g.;
STUDENT (ROLL_NO, NAME, ADDRESS, PHONE and AGE) is relation schema for
STUDENT. If a schema has more than 1 relation, it is called Relational Schema.
 Tuple: Each row in the relation is known as tuple. The above relation contains 4 tuples, one
of which is shown as:
1 RAM DELHI 9455123451 18
 Relation Instance: The set of tuples of a relation at a particular instance of time is called
as relation instance. Table 1 shows the relation instance of STUDENT at a particular time.
It can change whenever there is insertion, deletion or updation in the database.

DBMS
 Degree: The number of attributes in the relation is known as degree of the relation.

The STUDENT relation defined above has degree 5.
 Cardinality: The number of tuples in a relation is known as cardinality.
The STUDENT relation defined above has cardinality 4.
 Column: Column represents the set of values for a particular attribute. The
column ROLL_NOis extracted from relation STUDENT.
ROLL_NO
4
 NULL Values: The value which is not known or unavailable is called NULL value. It is
represented by blank space. e.g.; PHONE of STUDENT having ROLL_NO 4 is NULL.
CONSTRAINTS ON RELATION
 Constraints
Every relation has some conditions that must hold for it to be a valid relation. These
conditions are called Relational Integrity Constraints. There are three main integrity
constraints −
1. Key constraints
2. Domain constraints
3. Referential integrity constraints
1. Key Constraints
 There must be at least one minimal subset of attributes in the relation, which can identify
a tuple uniquely. This minimal subset of attributes is called key for that relation.
 If there are more than one such minimal subsets, these are called candidate keys.
 Key constraints force that −in a relation with a key attribute, no two tuples can have
identical values for key attributes.
 a key attribute cannot have NULL values.
 Key constraints are also referred to as Entity Constraints.
2. Domain Constraints
 Attributes have specific set of values in real-world scenario.
 For example, age can only be a positive integer.
 The same constraints have been tried to employ on the attributes of a relation. Every
attribute is bound to have a specific range of values.

DBMS
 For example, age cannot be less than zero and telephone numbers cannot contain a digit
outside 0-9.
3. Referential integrity Constraints
 Referential integrity constraints work on the concept of Foreign Keys. A foreign key is a
key attribute of a relation that can be referred in other relation.
 Referential integrity constraint states that if a relation refers to a key attribute of a different
or same relation, then that key element must exist.
The rules are:
o We can't delete a record from a primary table if matching records exist in a
related table.
o We can't change a primary key value in the primary table if that record has
related records.
o We can't enter a value in the foreign key field of the related table that doesn't
exist in the primary key of the primary table.
o We can enter a Null value in the foreign key, specifying that the records are
unrelated.
SPECIFYING KEY CONSTRAINTS IN SQL

Types of constraints available in SQL
 NOT NULL
 UNIQUE
 DEFAULT
 CHECK
 Key Constraints – PRIMARY KEY, FOREIGN KEY
 NOT NULL:
NOT NULL constraint makes sure that a column does not hold NULL value. When we don’t
provide value for a particular column while inserting a record into a table, it takes NULL
value by default. By specifying NULL constraint, we can be sure that a particular column(s)
cannot have NULL values.
 UNIQUE:
UNIQUE Constraint enforces a column or set of columns to have unique values. If a column
has a unique constraint, it means that particular column cannot have duplicate values in table.
 DEFAULT:
The DEFAULT constraint provides a default value to a column when there is no value
provided while inserting a record into a table.

DBMS
 CHECK:
This constraint is used for specifying range of values for a particular column of a table. When
this constraint is being set on a column, it ensures that the specified column must have the
value falling in the specified range.
 KEY CONSTRAINTS:
PRIMARY KEY:
Primary key uniquely identifies each record in a table. It must have unique values and cannot
contain nulls. In the below example the ROLL_NO field is marked as primary key, that
means the ROLL_NO field cannot have duplicate and null values.
 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.
ENFORCING INTEGRITY CONSTRAINTS ON RELATION
 Integrity constraints are specified when a relation is created and enforced when a relation is
modified.The impact of domain, PRIMARY KEY, and UNIQUE constraints is
straightforward: if an insert, delete, or update command causes a violation, it is rejected.
 A similar problem arises whenever we try to insert a tuple with a value in a field that is not in
the domain associated with that field, i.e., whenever we violate a domain constraint.
 Deletion does not cause a violation of domain, primary key or unique constraints.
 The impact of foreign key constraints is more complex because SQL sometimes tries to
rectify a foreign key constraint violation instead of simply rejecting the change.
 The referential integrity enforcement steps taken by the DBMS in terms of our Enrolled and
Students tables, with the foreign key constraint that Enrolled.sid is a reference to (the primary
key of) Students.
 Deletions of Enrolled tuples do not violate referential integrity, but insertions of Enrolled
tuples could.

DBMS
 Insertions of Students tuples do not violate referential integrity although deletions could.
Further, updates on either Enrolled or Students that change the sid value could potentially
violate referential integrity.
 SQL provides several alternative ways to handle foreign key violations.
We must consider three basic questions:
1. What should we do if an Enrolled row is inserted, with a sid column value that does not
appear in any row of the Students table? In this case the INSERT command is simply
rejected.
2. What should we do if a Students row is deleted?
The options are:
o Delete all Enrolled rows that refer to the deleted Students row (CASCADE).
o Disallow the deletion of the Students row if an Enrolled row refers to it(NO
ACTION).
o Set the sid column to the sid of some (existing) ‘default’ student, for every
Enrolled row that refers to the deleted Students row(SET DEFAULT).
o For every Enrolled row that refers to it, set the sid column to null. In our example,
this option conflicts with the fact that sid is part of the primary key of Enrolled
and therefore cannot be set to null (SET NULL).
3. What should we do if the primary key value of a Students row is updated? The options
here are similar to the previous case.
 The options are specified as part of the foreign key declaration. The default option is NO
ACTION, which means that the action (DELETE or UPDATE) is to be rejected. Thus, the
ON UPDATE clause in our example could be omitted.
 The CASCADE keyword says that if a Students row is deleted, all Enrolled rows that refer to
it are to be deleted as well. If the UPDATE clause specified CASCADE, and the sid column
of a Students row is updated, this update is also carried out in each Enrolled row that refers to
the updated Students row.
 If a Students row is deleted, we can switch the enrollment to a ‘default’ student by using ON
DELETE SET DEFAULT. The default student is specified as part of the definition of the sid
field in Enrolled.
Example: sid INT DEFAULT ‘53666’.
SQL also allows the use of null as the default value by specifying ON DELETE SET NULL.
Relational Model(MCQs)

DBMS
1. Choose the correct statement regarding superkeys

a) A superkey is an attribute or a group of multiple attributes that can uniquely identify
a tuple
b) A superkey is a tuple or a set of multiple tuples that can uniquely identify an attribute
c) Every superkey is a candidate key
d) A superkey is an attribute or a set of attributes that distinguish the relation from other
relations
Explanation: A superkey is an attribute or a set of multiple attributes that can uniquely
identify a tuple. It is used to differentiate between tuples.
2. What is an Instance of a Database?

a) The logical design of the database system
b) The entire set of attributes of the Database put together in a single relation
c) The state of the database system at any given point of time
d) The initial values inserted into the Database immediately after its creation
Explanation: The state of the database system at any given point of time is called as an
Instance of the database. State is values assign to the attributes of entity type.
3. What is a foreign key?

a) A foreign key is a primary key of a relation which is an attribute in another relation
b) A foreign key is a super key of a relation which is an attribute in more than one other
relations
c) A foreign key is an attribute of a relation that is a primary key of another relation
d) A foreign key is the primary key of a relation that does not occur anywhere else in the
schema
Explanation: A foreign key is an attribute of a relation that is initially a primary key of
another relation. A foreign key usage preserves referential integrity.
4. An attribute is a __________ in a relation.

a) Row
b) Column
c) Value
d) Tuple
Explanation: An attribute is a column in a relation. A tuple is a row in a relation.
5. What is the method of specifying a primary key in a schema description?

a) By writing it in bold letters

DBMS
b) By underlining it using a dashed line

c) By writing it in capital letters
d) By underlining it using a bold line
Explanation: We can specify a primary key in schema description by underlining the
respective attribute with a bold line.
6. Statement 1: A tuple is a row in a relation
Statement 2: Existence of multiple foreign keys in a same relation is possible
a) Both the statements are true
b) Statement 1 is correct but Statement 2 is false
c) Statement 1 is false but Statement 2 is correct
d) Both the statements are false
Explanation: A tuple is a row is a relation. There can exist multiple foreign keys in the same
relation because there can exist multiple attributes in the relation that are primary keys in two
or more other relations.
7. When primary key is null of new tuple then constraint violated is
a) null integrity constraint
b) primary integrity constraint
c) secondary integrity constraint
d) entity integrity constraint
Explanation: Primary key should not contain null value and Duplicated value
8. In formal relational model, set of indivisible values is called

a) range
b) domain
c) relation
d) tuple
Explaination: Domain is a value set
9. In relational model terminology, table is considered as

a) range
b) domain
c) relation
d) tuple
10. Key which specifies that two different tuples cannot have same value is classified as
a) super key
b) simple key
c) parallel key
d) conceptual key

DBMS
Explaination: A superkey is a combination of columns that uniquely identifies any row

within a relational database management system table.
11. Format or data type must be specified for

a) table ender
b) entity domain
c) range
d) domain
12. Cardinality in relational data model is considered as

a) total number of values
b) limited number of values
c) two numbers from set
d) three numbers from set
13. A Relation is a
a) A Subset of a Cartesian product of a list of attributes
b) Subset of a Cartesian product of a list of domains
c) Subset of a Cartesian product of a list of tuple
d) Subset of a Cartesian product of a list of relations
14. _______ allow us to identify uniquely a tuple in the relation.

a) Primary key
b) Domain
c) Attribute
d) Schema
15. Minimal Superkeys are called

a) Schema keys
b) Candidate keys
c) Domain keys
d) Attribute keys
16. Who proposed the relational model?

a) Bill Gates
b) E.F. Codd
c) Herman Hollerith
d) Charles Babbage

DBMS
17. Which of the following in true regarding Referential Integrity?

a) Every primary-key value must match a primary-key value in an associated table
b) Every primary-key value must match a foreign-key value in an associated table
c) Every foreign-key value must match a primary-key value in an associated table
d) Every foreign-key value must match a foreign-key value in an associated table
18. How many types of keys in Database Design?

a) Candidate key
b) Primary key
c) Foreign key
d) All of these
19. The minimal set of super key is called

a) Primary key
b) Secondary key
c) Candidate key
d) Foreign key
20. The primary key is selected from the:

a) composite keys.
b) determinants.
c) candidate keys.
d) foreign keys.
21. Which of the following is a group of one or more attributes that uniquely identifies a row?
a) Key
b) Determinant
c) Tuple
d) Relation
22. When the values in one or more attributes being used as a foreign key must exist in another
set of one or more attributes in another table, we have created a(n):
a) transitive dependency.
b) insertion anomaly.
c) referential integrity constraint.
d) normal form.
23. A relation is considered a:

a) Column.
b) one-dimensional table.
DBMS
c) two-dimensional table.
d) three-dimensional table.
24. In the relational model, relationships between relations or tables are created by using:
a) composite keys.
b) determinants.
c) candidate keys.
d) foreign keys.
25. A functional dependency is a relationship between or among:

a) tables.
b) rows.
c) relations.
d) attributes.
26. Which of the following is not a restriction for a table to be a relation?

a) The cells of the table must contain a single value.
b) All of the entries in any column must be of the same kind.
c) The columns must be ordered.
d) No two rows in a table may be identical.
27. A key:
a) must always be composed of two or more columns.
b) can only be one column.
c) identifies a row.
d) identifies a column.
28. The number of attributes in relation is called as its .....................

a) Cardinality
b) Degree
c) Tuples
d) Entity
29. ____defines the structure of a relation which consists of a fixed set of attribute-domain pairs.
a) Instance
b) Schema
c) Program
d) Super Key
4. Database Management System

 Data means known fact that can be recorded and that have implicit meaning.

DBMS
 A database is a collection of related data which are known facts that can be recorded and
that have implicit meaning.
o For example, consider the names, telephone numbers, and addresses of the people we
know. We may have recorded this data in an indexed address book or you may have
stored it on a hard drive, using a personal computer and software such as Microsoft
Access or Excel. This collection of related data with an implicit meaning is a
database.
 A database management system (DBMS) is a collection of programs that enables users to

create and maintain a database. The DBMS is a general-purpose software system that
facilitates the processes of defining, constructing, manipulating, and sharing databases
among various users and applications.
Advantages of DBMS :
 Minimize Data Redundancy - In Database Management System, all the files are integrated
in a single database. The whole data is stored only once at a single place so there is no chance
of duplicate data.
 Sharing Of Data - In DBMS, Data can be shared in between authorized user of database. All
the users have their own right to access the database up to a level. Database Administration
has complete access of database..
 Data Consistency - DBMS controls data redundancy which in turn controls data
consistency. Data consistency means if you want to update data in any files then all the files
should not be updated again.
 Data Integrity - Data integrity means unification of so many files into a single file. In
DBMS data is stored in different tables. A database contains different tables that are linked to
each other. Many users feed entries in these tables so it is important to maintain data items
and association between data items.
 Search Capability - Users of database may require to fetch data from the database. There are
numerous queries users may ask about the data. Search speed of the database must be fast to
produce quick results.
 Security - Data security means protecting your precious data from unauthorized access. Data
in database should be kept secure and safe to unauthorized modifications. Only authorized
users should have the grant to access the database.
 Privacy - Privacy means up to what extent a user can access the data. It is predetermined by
the DBA that who will access the data and up to what level he will be able to access it. Let

DBMS
say when you make a Facebook page then you have the power to give rights to other users
that who will be the promoter, editor and admin.
 Simplicity - Simplicity means to represent the overall logical view of data in a simple and
clear manner. DBMS is very simple for its users who use it. All the operations like insert,
delete, create and update are very easy to implement.
 Backup and Recovery - DBMS solves this problem of taking back up again and again
because it allows automatic backup and recovery of database.
 Integrity Constraints - Constraints are used to store accurate data because there are many
users who feed data in database. Data stored in database should always be correct and
accurate. DBMS provides the capability to enforce these constraints on database.
 Data Atomicity - Any complete transaction in database is called atomic unit. It is the duty of
DBMS to store a complete transaction in database. If any transaction is partially completed
then it roll backs them.
 Concurrency Control - If two users are accessing data simultaneously and they both want to
update values of same record then it may create concurrency. DBMS has the power to control
concurrency so that no transactions are lost.
 Maintaining Cost is lower - DBMS systems are costly but after purchasing them their
maintenance cost is very less. It can be maintained by few programmers that is not costly for
an enterprise.
 Very Less Chances of Data Loss - As there is lot of security constraint made on database so
chances of data loss are minimum. One can store their precious data or many years in DBMS
without loss of any information.
Database system:

DBMS
Structure of DBMS :

DBMS
Database Users:
Users are differentiated by the way they expect to interact with the system:
 Application programmers:
o Application programmers are computer professionals who write application
programs. Application programmers can choose from many tools to develop user
interfaces.
o Rapid application development (RAD) tools are tools that enable an application
programmer to construct forms and reports without writing a program.
 Sophisticated users:
o Sophisticated users interact with the system without writing programs. Instead,
they form their requests in a database query language.
o They submit each such query to a query processor, whose function is to break
down DML statements into instructions that the storage manager understands.
 Specialized users :

DBMS
o Specialized users are sophisticated users who write specialized database

applications that do not fit into the traditional data-processing framework.
o Among these applications are computer-aided design systems, knowledge base
and expert systems, systems that store data with complex data types (for example,
graphics data and audio data), and environment-modeling systems.
 Naïve users :
o Naive users are unsophisticated users who interact with the system by invoking
one of the application programs that have been written previously.
o For example, a bank teller who needs to transfer $50 from account A to account B
invokes a program called transfer. This program asks the teller for the amount of
money to be transferred, the account from which the money is to be transferred,
and the account to which the money is to be transferred.
Database Administrator:
 Coordinates all the activities of the database system. The database administrator has a
good understanding of the enterprise’s information resources and needs.
 Database administrator's duties include:
o Schema definition: The DBA creates the original database schema by executing
a set of data definition statements in the DDL.
o Storage structure and access method definition.
o Schema and physical organization modification: The DBA carries out changes
to the schema and physical organization to reflect the changing needs of the
organization, or to alter the physical organization to improve performance.
o Granting user authority to access the database: By granting different types of
authorization, the database administrator can regulate which parts of the database
various users can access.
o Specifying integrity constraints.
o Monitoring performance and responding to changes in requirements.
Query Processor:
The query processor will accept query from user and solves it by accessing the database.
Parts of Query processor:
 DDL interpreter
This will interprets DDL statements and fetch the definitions in the data dictionary.
 DML compiler

DBMS
a. This will translates DML statements in a query language into low level instructions
that the query evaluation engine understands.
b. A query can usually be translated into any of a number of alternative evaluation plans
for same query result DML compiler will select best plan for query optimization.
 Query evaluation engine
This engine will execute low-level instructions generated by the DML compiler on
DBMS.
Storage Manager/Storage Management:
 A storage manager is a program module which acts like interface between the data stored
in a database and the application programs and queries submitted to the system.
 Thus, the storage manager is responsible for storing, retrieving and updating data in the
database.
 The storage manager components include:
o Authorization and integrity manager: Checks for integrity constraints and
authority of users to access data.
o Transaction manager: Ensures that the database remains in a consistent state
although there are system failures.
o File manager: Manages the allocation of space on disk storage and the data
structures used to represent information stored on disk.
o Buffer manager: It is responsible for retrieving data from disk storage into main
memory. It enables the database to handle data sizes that are much larger than the
size of main memory.
Data structures implemented by storage manager.
o Data files: Stored in the database itself.

o Data dictionary: Stores metadata about the structure of the database.
o Indices: Provide fast access to data items.
Database Management System (MCQs)

30. The DBMS utility, ...................... allows to reconstruct the correct state of database from
thebackup and history of transactions.
a) Backup
b) Recovery
c) Monitoring

DBMS
d) Data loading
31. The program interface which provides the feature to client side programs to call the database
management system is classified as
a) open programming interface
b) closed programming interface
c) application programming interface
d) data programming language
32. The database catalog or dictionary defining the descriptive information which is stored in
database is called
a) constrained data
b) Metadata
c) basic data
d) filtered data
33. Database catalog or dictionary defining descriptive information which is stored in database is
called
a) constrained data
b) metadata
c) basic data
d) filtered data
34. Collection of known and useful raw facts that has some meaning and can be processed in
useful way is classified as
a) management oriented facts
b) updated facts
c) data
d) recorded facts
35. Specification given to database management system regarding data whether it is string,
integers or special characters is classified as
a) type of data stored
b) type of query asked
c) type of structure developed
d) sequence of data cycle
36. Structuring of database by specifying types and constraints of data is classified as

a) defining a database
b) creating a database

DBMS
c) analyzing a database
d) filtering a database
37. Result of Compilation of DDL statement is stored in special file called as _________.
a) Data Dictionary
b) Symbol Table
c) Database File
d) Database Schema File
38. Data Dictionary is also called as _____________.

a) Hash Table
b) Symbol Table
c) System Catalog
d) None of these
39. Data Dictionary contain one of the following entity inside it, Guess the answer
a) Associated Data
b) Important Data
c) Meta Data
d) Duplicate Data
40. Storage structure and Access methods used by database system are specified using
____________.
a) Data Storage and Definition Language
b) Data Dictionary
c) Data Manipulation Language
d) None of these
41. Which of the following is a database administrator's function?

a) database design
b) backing up the database
c) performance monitoring
d) All of the above
42. The data dictionary tells the DBMS

a) what files are in the database
b) what attribute are possessed by the data
c) what these files contain
d) All of the above
43. The DBMS acts as an interface between what two components of an enterprise-class
database system?

DBMS
a) Database application and the database

b) Data and the database
c) The user and the database application
d) Database application and SQL
44. The following are components of a database except ________ .

a) user data
b) metadata
c) reports
d) indexes
45. An application where only one user accesses the database at a given time is an example of
a(n) ________ .
a) single-user database application
b) multiuser database application
c) e-commerce database application
d) data mining database application
46. An on-line commercial site such as Amazon.com is an example of a(n) ________

47. Because it contains a description of its own structure, a database is considered to be

________
a) described
b) metadata compatible
c) self-describing
d) an application program
48. The following are functions of a DBMS except ________ .

a) creating and processing forms
b) creating databases
c) processing data
d) administrating databases
49. An Enterprise Resource Planning application is an example of a(n) ________ .


DBMS
50. _______refers to the basic facts and entities, such as names and numbers.
a) Data
b) Information
c) Input
d) Output
51. Information is processed from _________

a) Output
b) Data
c) Memory
d) None of these
52. MIS stands for:

a) Management Information Server
b) Management Information Service
c) Management Information System
d) Master Information System
53. Which is the false statement:

a) A database is ordered collection of data.
b) A database is systematic compilation of records in a computer.
c) DBMS manages the database
d) Data helps in making decisions.
54. Which is not the feature of database:

a) Data redundancy
b) Independence
c) Flexibility
d) Data Integrity
55. Which is the feature of database:

a) Query Language
b) Multi user access
c) Data Dictionary
d) All of these
56. Which is the advantage of database:

a) Prevents Data redundancy
b) Restricts unauthorized access
c) Persistent storage

DBMS
d) Backup and recovery

e) Integrity Constraints
f) All of these
57. Which is the database language:

a) C
b) C++
c) SQL
d) None of these
58. Which person is responsible for overall activities for database:

a) Database designer
b) Database analyst
c) Database Administrator
d) Database manager
59. Which is the component of database management system:

a) Query Language
b) Database Manager
c) File manager
d) All of these
60. DBMS is the bridge between operating system and ___________.

a) User
b) Database administrator
c) Application program
d) None of these
61. A user that manages the files of application in DBMS is called:

a) Administrator
b) Database analyst
c) File Manager
d) None of these
5. Data Independence
 Data independence is the capacity to change the schema at one level of a database system
without having to change the schema at the next higher level.
 The following are the types of data independence:
o Logical data independence
o Physical data independence

DBMS
Logical data independence

 It is the capacity to change the conceptual schema without having to change the external
schemas or application programs.
 Conceptual schema may be changed to expand the database, to change constraints, or to
reduce the database by removing a record type or data item.
 Only the view definition and the mappings need to be changed in a DBMS that supports
logical data independence. After the conceptual schema undergoes a logical reorganization,
application programs that reference the external schema constructs must work as before.
Changes to constraints can be applied to the conceptual schema without affecting the external
schemas or application programs.
 Logical data independence is harder to achieve because it allows structural and constraint
changes without affecting application programs.
Physical data independence

 It is the capacity to change the internal schema without having to change the conceptual
schema. Hence, the external schemas need not be changed as well.
 Changes to the internal schema may be needed because some physical files may be
reorganized, for example, by creating additional access structures to improve the
performance of retrieval or update.
 If the same data as before remains in the database, there is no need to change the conceptual
schema.
 For example, providing an access path to improve retrieval speed of section records in
university database by semester and year should not require a query such as list all sections
offered in fall 2008 to be changed, although the query would be executed more efficiently by
the DBMS by utilizing the new access path.
 Physical data independence exists in most databases and file environments where physical
details such as the exact location of data on disk, and hardware details of storage encoding,
placement, compression, splitting, merging of records, and so on are hidden from the user.
Applications remain unaware of these details.
 Data independence occurs because when the schema is changed at some level, the schema at
the next higher level remains unchanged, only the mapping between the two levels is
changed. Hence, application programs referring to the higher-level schema need not be
changed.
 The three-schema architecture can make it easier to achieve true data independence, both
physical and logical.
Data Independence(MCQs)

DBMS
1. The type of data independence in which the internal schema can be modified without
modifying the conceptual schema is classified as
a) logical data independence
b) physical data independence
c) conceptual level independence
d) external level independence
2. Which statement is false regarding data independence?

a) Hierarchical data model suffers from data Independence
b) Network model suffers from data Independence
c) Relational model suffers only from logical data Independence
d) Relational model suffers only from physical data Independence.
3. Type of data independence in which schema can be altered on conceptual level without
altering external level is classified as
a) conceptual level independence
b) external level independence
c) logical data independence
d) physical data independence
4. Characteristic of database management system which allows program operation

independence and program data independence is classified as
a) conceptual abstraction
b) implementation abstraction
c) data abstraction
d) interface abstraction
5. Capacity to alter database schema at one level without altering any other schema level is
classified as
a) data mapping
b) data independence
c) data dependence
d) data transformation
6. A physical view of data refers to the way data is handled at a_____ its storage and retrieval:
a) High level
b) Low level
c) Medium level
d) All of these

DBMS
7. In logical and physical view of data the set of principles that defines a data model may be
divided into how many parts:
a) 1
b) 2
c) 3
d) 4
8. Which view is normally more stable

a) internal level
b) external level
c) conceptual level
d) view level
9. The following is a restricted view of databse

a) internal level
b) external level
c) conceptual level
d) physical level
10. The ability to modify the internal schema without causing any change to the external schema
a) Physical data independence
b) Logical Data independence
c) External Data independence
d) None
11. The ability to modify the conceptual schema without causing any change to the application
program a) Physical data independence
b) Logical Data independence
c) External Data independence
d) None
12. By data independence we mean application programs

a) do not need data
b) may be developed independent of data
c) may be developed without knowing the organization of data
d) may be developed with independent data
13. Data independence allows

(i) no changes in application programs

DBMS
(ii) change in database without affecting application programs

(iii) hardware to be changed without affecting application programs
(iv) system software to be changed without affecting application programs
a) i, ii
b) ii, iii
c) ii, iii, iv
d) i, ii, iv
14. Data independence allows

a) sharing the same database by several applications
b) extensive modification of applications
c) no data sharing between applications
d) elimination of several application programs
DATA ABSTRACTION
Database Schema
A database schema is the skeleton structure that represents the logical view of the entire
database. It defines how the data is organized and how the relations among them are associated.
It formulates all the constraints that are to be applied on the data.
A database schema defines its entities and the relationship among them. It contains a descriptive
detail of the database, which can be depicted by means of schema diagrams. It’s the database
designers who design the schema to help programmers understand the database and make it
useful.
6. Levels of Abstraction in a DBMS (Three-Schema Architecture)

The data in a DBMS is described at three levels of abstraction, as illustrated in Figure 1.2. The
database description consists of a schema at each of these three levels of abstraction: the
conceptual, physical, and external schemas.

DBMS
Figure 1.2 Levels of Abstraction in a DBMS

Conceptual Schema
 The conceptual schema (sometimes called the logical schema) describes the stored data in
terms of the data model of the DBMS. In a relational DBMS, the conceptual schema
describes all relations that are stored in the database.
 The conceptual schema hides the details of physical storage structures and concentrates on
describing entities, data types, relationships, user operations, and constraints.
 In our sample university database, these relations contain information about entities, such as
students and faculty, and about relationships, such as students’ enrollment in courses. All
student entities can be described using records in a Students relation. Each collection of
entities and each collection of relationships can be described as a relation, leading to the
following conceptual schema:
Students (sid: string, name: string, login: string, age: integer, gpa: real)
Faculty (fid: string, fname: string, sal: real)
Physical Schema
 It describes the physical storage structure of the database. Which uses a physical data model
and describes the complete details of data storage and access paths for the database.
 The physical schema specifies additional storage details. Essentially, the physical schema
summarizes how the relations described in the conceptual schema are actually stored on
secondary storage devices such as disks and tapes. We must decide what file organizations to
use to store the relations, and create auxiliary data structures called indexes to speed up data
retrieval operations.
 A sample physical schema for the university database follows:
o Store all relations as unsorted files of records. (A file in a DBMS is either a collection
of records or a collection of pages, rather than a string of characters as in an operating
system.)
o Create indexes on the first column of the Students, Faculty, and Courses relations, the
sal column of Faculty, and the capacity column of Rooms.

DBMS
 Decisions about the physical schema are based on an understanding of how the data is
typically accessed. The process of arriving at a good physical schema is called physical
database design.
External Schema
 External schemas allow data access to be customized (and authorized) at the level of
individual users or groups of users.
 Any given database has exactly one conceptual schema and one physical schema because it
has just one set of stored relations, but it may have several external schemas, each tailored to
a particular group of users.
 Each external schema consists of a collection of one or more views and relations from the
conceptual schema.
 A view is conceptually a relation, but the records in a view are not stored in the DBMS.
Rather, they are computed using a definition for the view, in terms of relations stored in the
DBMS. The external schema design is guided by end user requirements.
 For example, we might want to allow students to find out the names of faculty members
teaching courses, as well as course enrollments. This can be done by defining the following
view:
o Courseinfo(cid: string, fname: string, enrollment: integer)
 A user can treat a view just like a relation and ask questions about the records in the view.
Even though the records in the view are not stored explicitly, they are computed as needed.
Data Abstraction(MCQs)
15. Type data abstraction which allows conceptual representation of data in database
management system is considered as
a) logical design model
b) data model
c) interface model
d) user friendly model
16. Considering abstraction concepts, process of assigning similar entities to similar entity types
systematically is called
a) classification
b) instantiation
c) identification
d) exception abstract
17. Identification, aggregation and instantiation are abstraction concepts used in

a) intension and extension models

DBMS
b) classes and nodes models

c) concrete models
d) semantic data models
18. Process of abstraction in which objects and classes are identified with some kind of identifier
is classified as
a) classification
b) instantiation
c) identification
d) exception abstract
19. The types of architectures of DBMS are

a) single-tier architecture
b) two-tier architecture
c) three-tier architecture
d) both b and c
20. The graphical representation of database description is called

a) dynamic schema diagram
b) schema diagram
c) structure diagram
d) entity path diagram
21. In the two-tier architecture, the server is considered as

a) host server
b) client server
c) transaction server
d) module server
22. The type data abstraction which allows the conceptual representation of data in database
management system is considered as
a) data model
b) interface model
c) Relational model
d) User friendly model
23. The characteristic of database management system which allows the program operation
independence and program data independence is classified as
a) conceptual abstraction
b) implementation abstraction
DBMS
c) data abstraction
d) interface abstraction
24. The phase considering documentation of analyzed requirements and can be represented with
the help of computerized tools is considered as
a) logical data design
b) storage cycle design
c) conceptual design
d) structural design
25. ——– level describes what data is stored in the database and the relationships among the data
a) Physical Level
b) Logical Level
c) Conceptual Level
d) None of the above
26. Which of the following is not a level of data abstraction?

a) Physical Level
b) Logical Level
c) Critical Level
d) View Level
27. Which of the following is not an Schema?

a) Database Schema
b) Physical Schema
c) Critical Schema
d) Logical Schema
28. Which level of database is viewed by user:

a) Internal level
b) External Level
c) Conceptual Level
d) All of these
29. Internal level has:

a) Individual Users View of the database
b) Community view of the database
c) Physical Representation of the database
d) All of these

DBMS
30. Schema is usually stored in____________.

a) Tables
b) Data Dictionary
c) Both
d) None of these
31. Schema is defined by:

a) DML
b) DDL
c) DCL
d) DQL
32. Which is the schema object:

a) Database links and clusters
b) Packages and Indexes
c) Procedures and functions
d) All of these
7. DATABASE LANGUAGES IN DBMS
Database Languages in DBMS:To read data, update and store information in DBMS, some
languages are used. Database languages in DBMS are given as below.
 DDL – Data Definition Language

 DML – Data Manipulation Language
 DCL – Data Control Language
 TCL – Transaction Control Language
1. Data Definition Language (DDL)

DDL stands for data definition language and used to define database patterns or structures. DDL
is a syntax which is same as syntax of computer programming language for defining patterns of
database.
Few examples of it are:
 CREATE – used to create objects in database
 ALTER – alter the pattern of database
 DROP – helps in detecting objects
 TRUNCATE – erase all records from table
 COMMENT – adding of comments to data dictionary
 RENAME – useful in renaming an object

DBMS
o CREATE statement or command is used to create a new database. In structured query

language the create command creates an object in a relational database management system.
The commonly used create command is as follows
 CREATE TABLE [name of table] ( [ definitions of column ]) [parameters of table]
o DROP statement destroys or deletes database or table. In structured query language, it also

deletes an object from relational database management system. Typically used DROP
statement is
 DROP type of object name of object
o ALTER statement enhance the object of database. In structured query language it modifies
the properties of database object. The ALTER statement is
 ALTER type of object name of object
o RENAME statement is used to rename a database. It’s statement is as follows
 RENAME TABLE old name of table to new name of table
2. Data manipulation language (DML)
It has statements which are used to manage the data within the pattern of objects. Some of the
samples of the statements are as follows:
 SELECT – useful in holding data from a database

 INSERT – helps in inserting data in to a table
 UPDATE – used in updating the data
 DELETE – do the function of deleting the records
 MERGE – this do the UPSERT operation i.e. insert or update operation
 CALL – this calls a structured query language or a java subprogram
 EXPLAIN PLAN – has the parameter of explaining data
 LOCK TABLE – this ha the function of controlling concurrency
DML also include the structured query language (SQL) data modifying statements, they modify
the saved data but not the pattern of objects. The initial word of the DML statements has
functional capability.
Data manipulation languages are divided as:

DBMS
 Procedural programming
 Declarative programming
3. Data Control Language (DCL)

Data Control Language (DCL) is syntax similar to the programming language, which was
used to retrieve the stored or saved data. Examples of the commands in the data control
language (DCL) are:
 GRANT – this permits particular users to perform particular tasks

 REVOKE – it blocks the previously granted untrue permissions
The operations which has the authorization of REVOKE are CONNECT, INSERT, USAGE,
EXECUTE, DELETE, UPDATE and SELECT.
The execution of DCL is transactional; it also has the parameter of rolling back. But the
execution of data control language in oracle database does not have the feature of rolling
back.
4. Transaction Control Language (TCL)

Transaction Control Language (TCL) has commands which are used to manage the
transactions or the conduct of a database. They manage the changes made by data
manipulation language statements and also group up the statements in o logical management.
Examples:
 COMMIT – use to save work
 SAVE POINT – helps in identifying a point in the transaction, can be rolled back to the
identified point
 ROLL BACK – has the feature of restoring the database to the genuine point, since from
the last COMMIT
 SET TRANSACTION – have parameter of changing settings like isolation level and roll
back point
o COMMIT command permanently save the transaction in to database.

 It’s syntax is: Commit;
o ROLL BACK command uses the save point command to jump to save point in
transaction.

DBMS
 It’ s syntax is: rollback to name-save point;

o SAVE POINT command is used to save a transaction temporarily.
 It’s syntax is: Save point name-save point;
These are different types of DBMS languages, which are used to read, upgrade, save
information.
Database Languages (MCQs)
1. DMLs type that is also referred as nonprocedural DML, is
a) Declarative DML
b) Definitive DML
c) Procedural DML
d) Subjective DML
2. Language used for expression of database's queries and updates is known as

a) Data modelling language
b) Data definition language
c) Data manipulation language
d) Data declaration language
3. Database system checks for constraints every time database is

a) Retrieved
b) Accessed
c) Updated
d) Stored
4. The language used in application programs to request data from the DBMS is referred to as
the
a) DM
L
b) DD
L
c) VD
L
d) SDL

5. The DBMS language component which can be embedded in a program is
a) The data definition language (DDL).
b) The data manipulation language (DML).
c) The database administrator (DBA).
d) A query language.

DBMS
6. It is possible to define a schema completely using

a) VDL and DDL.
b) DDL and DML.
c) SDL and DDL.
d) VDL and DML
7. The statement in SQL which allows to change the definition of a table is

a) Alter.
b) Update.
c) Create.
d) Select.
8. Which of the following is correct:

a) a SQL query automatically eliminates duplicates.
b) SQL permits attribute names to be repeated in the same relation.
c) a SQL query will not work if there are no indexes on the relations
d) None of these
9. The full form of DDL is

a) Dynamic Data Language
b) Detailed Data Language
c) Data Definition Language
d) Data Derivation Language
10. A data manipulation command the combines the records from one or more tables is called
a) SELECT
b) PROJECT
c) JOIN
d) PRODUCT
11. The language used in application programs to request data from the DBMS is referred to as
the
a) DML
b) DDL
c) VDL
d) SDL
12. A DBMS query language is designed to

a) support end users who use English-like commands.
DBMS
b) support in the development of complex applications software.

c) specify the structure of a database.
d) all of the above
13. DML Stands for _____________.

a) Data Management Language
b) Database Markup Language
c) Data Manipulation Language
d) None of these
14. Which of the following language is used to specify database Schema ?

a) Data Management Language
b) Data Manupulation Language
c) Data Development Language
d) Data Definition Language
15. Data Manipulation Languages are used for

a) Delete Information
b) Insert Information into Database
c) Retrieve Information from Database
d) All of these
16. There are _______ types of Data Manipulation Languages.

a) 3
b) 2
c) 1
d) 4
17. ________ it requires a user to specify what data are needed and how to get those data.
a) Procedural DML
b) Non Procedural DML
c) Abstract DML
18. ________ it requires a user to specify what data are needed without specifying how to get those
data.
a) Procedural DML
b) Non Procedural DML
c) Abstract DML

DBMS
19. The Statement that requests a retrieval of information from database is called as ________.
a) Manipulation Statement
b) Query
c) Control Statement
d) None of these
20. ________ is a portion of DML that involves information retrieval.

a) Programming Language
b) Procedural Language
c) OOP Language
d) Query Language
21. Periodically adding, changing and deleting file records is called file
a) Updating
b) upgrading
c) restructuring
d) renewing
22. DML language is used to:

a) Define schema
b) Define internal level
c) Access Data
d) All of these
23. Grant and revoke is the type of command:
a) DDL
b) DML
c) DCL
d) DQL

DBMS Intro

Uploaded by

Copyright:

Available Formats

DBMS Intro

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

DBMS Intro

Uploaded by

Copyright:

Available Formats

DBMS

 Multi valued attribute :

Department of CSE Page 1

Some other types of attributes are

Entity Types and Entity Sets

Weak Entity Types

Entity-Relationship(ER) Diagram Notations :

Department of CSE Page 3

Relationship Types, Sets, and Instances :

Department of CSE Page 4

Constraints on Binary Relationship Types

Department of CSE Page 5

Department of CSE Page 6

The Entity-Relationship Model(MCQs)

Department of CSE Page 7

Department of CSE Page 8

17. A recursive relationship is a relationship between an entity and ________ .

Department of CSE Page 9

Department of CSE Page 10

25. The entity relationship set is represented in E-R diagram as

26. The Rectangles divided into two parts represents

Department of CSE Page 11

c) Weak entity set

31. Weak entity set is represented as

32. Types of partial constraints are

Department of CSE Page 12

35. Constraint which specifies minimum number of relationship instances is classified as

1. Object based Data Models

Department of CSE Page 13

2.Physical Data Models

3.Record based Data Models

Department of CSE Page 14

• A relational data model revolves around 5 important rules.

37. Logical design of database is called

Department of CSE Page 15

38. ____________ is a classical approach to database design?

39. Which of the following is the oldest database model?

40. Which is the most popular database model:

Department of CSE Page 16

b) physical data models

46. Which of the following is the building block of Data model

49. A data model is

50. Which of the following is an object-based logical model?

Department of CSE Page 17

d) All the above

51. Which structure is the hierarchical model based on?

52. Which structure is the Network model based on?

Department of CSE Page 18

 Degree: The number of attributes in the relation is known as degree of the relation.

Department of CSE Page 19

SPECIFYING KEY CONSTRAINTS IN SQL

Department of CSE Page 20

ENFORCING INTEGRITY CONSTRAINTS ON RELATION

Department of CSE Page 21

Department of CSE Page 22

1. Choose the correct statement regarding superkeys

2. What is an Instance of a Database?

3. What is a foreign key?

4. An attribute is a __________ in a relation.