ABES Engineering College, Ghaziabad

B. Tech Odd Semester Sessional Test-1

Printed Pages:
Session: 2024-25
Semester: 5th
Course Code: BCS 501 Roll No.:
Course Name: Database Management System Time: 75 Minutes
Maximum Marks: 30
Instructions:
1. Attempt All sections.
2. If require any missing data, then choose suitably.

Q. Question Marks CO KL PI
No.
Section-A Total Marks : 20
1 Attempt ANY ONE part from the following

Differentiate between physical and logical data independence.

a) 5 CO1 K2 1.4.1
 Discuss three level of abstractions or schemas architecture of DBMS.

b) 5 CO1 K2 1.4.1

2 Attempt ANY ONE part from the following

Differentiate between traditional file system and database management system.
Basics File System DBMS
The file system is a way of
DBMS is software for managing
Structure arranging the files in a storage
the database.
medium within a computer.
Data Redundant data can be present in a In DBMS there is no redundant
Redundancy file system. data.
It doesn‟t provide Inbuilt It provides in house tools for
Backup and
mechanism for backup and recovery backup and recovery of data
Recovery
of data if it is lost. even if it is lost.
Query There is no efficient query Efficient query processing is
processing processing in the file system. there in DBMS.
There is more data consistency
There is less data consistency in the
Consistency because of the process of
file system.
normalization .
It has more complexity in
a) Complexity It is less complex as compared to 5 CO1 K2 1.4.1
handling as compared to the file
DBMS.
system.
DBMS has more security
Security File systems provide less security in
mechanisms as compared to file
Constraints comparison to DBMS.
systems.
It has a comparatively higher
Cost It is less expensive than DBMS.
cost than a file system.
In DBMS data independence
exists, mainly of two types:
Data
There is no data independence.
Independence 1) Logical Data Independence .

2)Physical Data Independence.

Only one user can access data at a Multiple users can access data at
User Access
time. a time.
The users are not required to write The user has to write procedures
Meaning
procedures. for managing databases
Data is distributed in many files. So, Due to centralized nature data
Sharing
it is not easy to share data. sharing is easy
 What are the different types of Data Models in DBMS? Explain them.

b) 5 CO1 K2 1.4.1

3 Attempt ANY ONE part from the following

 A database is being constructed to keep track of the teams and games of a sport
league. A team has a number of players, not all of whom participate in each game. It
is desired to keep track of players participating in each game for each team, the
positions they play in that game and the result of the game.
(i) Design an E-R schema diagram for this application.
(ii) Map the E-R diagram into relational model

a) 5+5 CO1 K3 2.2.3

 PLAYER(Pname)
 TEAM(Tname)
 GAME(Game#, Date, Time, Field, Key)
 PLAYS_FOR(Pname, Tname, StartDate, EndDate)
 PARTICIPATES(Pname, Game#, Performance)
 HOME_TEAM(Game#, Tname, Inning, Runs, Hits, Errors, FinalScore)
 AWAY_TEAM(Game#, Tname, Inning, Runs, Hits, Errors, FinalScore)

Preparation of time table of an Engineering College, catering for a number of

Sections (Year/Branch/Section),a number of courses, a number of faculty members
b) teaching the courses and a number of class rooms (ignorelabs). 5+5 CO1 K3 1.4.1
(i) Design an E-R schema diagram for this application.
(ii) Map the E-R diagram into relational model
Section-B Total Marks : 10
4 Attempt ANY ONE part from the following
Explain Entity integrity, Domain integrity and referential integrity Constraints in the 2+2+1
a) context of DBMS. CO2 K2 2.2.3
 Explain select, project and Cartesian product operations in relational algebra with an
b) 2+2+1 CO2 K2 2.2.3
example?
5 Attempt ANY ONE part from the following

Consider the following three relations in a relational database.

Employee ( eld, Name), Brand (bld , bName), Own (eld , bld )

Write the relational algebra expressions to return the set of elds who own all the brands?
a) 5 CO2 K3 2.2.3

Consider the following schema for institute library:

Student (RollNo, Name, Father_ Name, Branch) Book (ISBN, Title, Author, Publisher)
Issue (RollNo, ISBN, Date-of –Issue) Write the following queries in relational algebra:
i) Find the name of student who has issued a book published by „ABC‟ publisher.

b) 2.5+2.5 CO2 K3 2.2.3

ii) List title of all books and their authors issued to a student „RAM‟.

[Gate 2014]

CO Course Outcomes mapped with respective question

KL Bloom's knowledge Level (K1, K2, K3, K4, K5, K6)
K1- Remember, K2- Understand, K3-Apply, K4- Analyze, K5: Evaluate, K6- Create
(Ans 2B)
1. Hierarchical Model

Hierarchical Database Model, as the name suggests, is a database model in which the data is arranged in a hierarchical
tree edifice. As it is arranged based on the hierarchy, every record of the data tree should have at least one parent,
and each parent should have one or more child records, except for the child records in the last level. The Data can be
accessed by following through the classified structure, always initiated from the Root or the first parent. Hence this
model is named as Hierarchical Database Model.

Hierarchical database model is one of the oldest database models, which originated in 1950. One of the first
hierarchical database model was Information Management System developed by North American Rockwell company
and IBM.
In this model, data is stored in the form of records which are the collection of fields. The records are connected
through links. Each field in a record can contain only one value. The first node of the tree is called the root node. When
data needs to be retrieved, then the whole tree is traversed starting from the root node. This model can represent only
one-to-many relationships. It is not able to represent many-to-many relationships. (Note:- we will study relationships
in detail later in the course)

Let us take an example of college students who take different courses. A course can be assigned to a student, but a
student can take as many courses as they want.

Example:

Figure 1. 1: Hierarchical structure

Records: Course, Teacher, Student

Relationship: Offered by, Attended by
Hierarchical model arranged as in Figure 1.6 indicates the relationship and can answer the queries like:
 Who is teaching a particular course?
 Who has registered in a particular course?

It does not indicate the relationships and cannot answer the following queries:
 What are the courses being offered by a faculty?
 What are the courses being attended by a student?
 Whom are the students being taught by a faculty?
 Whom are the faculty teaching a student?

Figure 1. 2 (a): Example of Hierarchical structure (Course to Student)

 Figure 1.7(b): Example of Hierarchical structure (Course to Teacher)

As shown in Figure. 1.7(a), when one course is taken by more than one student, then we need to replicate the student
record. As shown in the above example, Anil has registered for courses Java and Python and thus, the record of Anil is
repeated twice. Also, we can see that record of Rohan has also appeared twice in this model. It leads to the
redundancy of data. The same hierarchical structure can be represented for the link course to teacher shown in Figure
1.7(b).

A Hierarchical database model was widely used during the Mainframe Computers Era. Today, it is used mainly for
storing file systems and geographic information. It is used in applications where high performance is required, such as
telecommunications and banking. A hierarchical database is also used for Windows Registry in the Microsoft Windows
operating system. It is useful where the following two conditions are met:
1. The data should be in a hierarchical pattern, i.e., a parent-child relationship must be present.
2. The data in a hierarchical pattern must be accessed through a single path only.

Advantages:
 Simplicity: It is conceptually simple due to the parent-child relationship. A clear chain of command or
authority is there. Data can be retrieved easily due to the explicit links present between the table
structures.
 Data Integrity: Referential integrity is always maintained, i.e., any changes made in the parent table are
automatically updated in a child table.
 Data Security: It is provided and enforced by DBMS.
 Efficiency: It is efficient when a large number of one-to-many relationships exist in a database

Limitations:
 A child record cannot be created if it is not linked to any parent record. In our example above, we cannot
create a course until it is opted by any student.
 M: N relationship (many-to-many) is not supported.
 Redundancy can result in data inconsistency.
 Change in structure leads to change in all application programs.
 No data manipulation language or data definition language.
 Poor flexibility
 Rigid structure

2. Network Model
The network model replaces the hierarchical tree with a graph, thus allowing more general connections among the
nodes. It allows a record to have more than one parent and multiple child records, thus permits many-to-many
relationships. The network model was adopted by the Conference on Data System Language (CODASYL) Data Base Task
Group in 1969 and underwent a major update in 1971. The operations on the network model are done with the help of
the circular linked list. A child node in the network model can have more than one parent record, as shown in Figure
1.8.

Example:

Figure 1. 3 : Network model

Records: Course, Teacher, Student

Relationship: Offered by, Attended by, Taught by, Offers, Attends, Teaches
Now the above network model can give the answer to all queries mentioned below, which was not possible in the case
of Hierarchical model.
 What are the courses being offered by a faculty?
 What are the courses being attended by a student?
 Whom are the students being taught by a faculty?
 Whom are the faculty teaching a student?

Figure 1. 4 : Example of Network structure (Course to Student)

An example of a network model, as shown in Figure 1.9, shows that the record of Anil has two parent records Python
and Java. Similarly, the record of Rohan has two parent records of record type Course. It reduces the redundancy of
data as depicted in Figure 1.7(a), showing hierarchical structure storing the same set of records.

Some of the popular network databases are:

 Integrated Data Store (IDS)
 IDMS (Integrated Database Management System)
 Raima Database Manager
 TurboIMAGE
 Univac DMS-1100
Advantages:
 Conceptual Simplicity: It is conceptually simple and easy to design.
 Capability to handle more relationship types: It handles many to many relationships, which is a real help in
modeling real-life situations.
 Ease of data access: The data access is easier and flexible as compared to the hierarchical model.
 Data Integrity: It ensures data integrity as a user must define the owner record before defining the member
record.

Limitations:
 Implementation Complexity: It requires the knowledge of actual physical data storage for navigational data
access. The database administrators, designers, programmers, and even the end-users should be familiar
with the internal data structure. It can't be used to create user-friendly DBMS.
 Absence of structural Independence: The data access method is navigational, which makes structural
change very difficult and impossible in most cases. So if changes are made in the database structure, then
all the application programs need to be modified.
 Any change like updation, deletion, insertion is very complex.
The data operation like retrieval, deletion, and insertion is very complex because of the graph structure. The
network model uses the graph structure for data storage.

3. Relational Model

The relational model was introduced in 1970 by E. F. Codd (of IBM) in his landmark paper “A Relational Model of Data
for Large Shared Databanks” (Communications of the ACM, June 1970, pp. 377−387). The relational model foundation
is a mathematical concept known as a relation. A relation is also called a table, as a matrix composed of intersecting
rows and columns. These tables represent both data and relationships amongst the data. Each table corresponds to an
application entity. Each row in a relation is called a tuple and represents an instance of that entity. Each column
represents an attribute. The relational model also describes a precise set of data manipulation constructs based on
advanced mathematical concepts.

The relational data model is implemented through a very sophisticated relational database management system
(RDBMS). The RDBMS performs the same basic functions provided by the hierarchical and network DBMS systems, in
addition to other functions. Figure 1.10, shows a relational model corresponding to the network model of records
Course, Teacher and Student.

Relation Attributes
 Student : (S_id, S_name, S_age)
Course : (C_id, C_name)
Teacher : (T_id, T_name)
Teacher Course : (T_id, S_id)
Student Course: (S_id, C_id)

Figure 1. 5 : Example of Relational Model

Some popular Relational Database management systems are:

According to DB-Engines, in January 2021, the most widely used systems were Oracle, MySQL (free software),
Microsoft SQL Server, PostgreSQL (Open Source, a continuation development after INGRES), IBM DB2, SQLite (free
software), Microsoft Access, MariaDB (free software), Teradata, Microsoft Azure SQL Database, Apache Hive (free
software; specialized for data warehouses)
According to research company Gartner, in 2011, the five leading proprietary software relational database vendors by
revenue were Oracle (48.8%), IBM (20.2%), Microsoft (17.0%), SAP including Sybase (4.6%), and Teradata (3.7%).[24]

Advantages:
 Structural independence is promoted using independent tables. Database users need not be familiar with
internal data storage.
 The tabular view improves conceptual simplicity. Database designers need to focus on logical design rather
than knowing the physical data storage details.
 Ad hoc query capability is based on SQL. SQL is 4GL which permits the user to specify only what needs to be
done without specifying how it will be done.
 Isolates the end-user from physical-level details
 Improves implementation and management simplicity
 Precision: The usage of relational algebra and relational calculus in the manipulation.

Limitations:
  Requires substantial hardware and system software overhead: It requires more powerful hardware and also
physical storage devices to hide the complexity of implementation and data storage.
 Conceptual simplicity gives untrained people the tools to use a good system poorly. Poor database design
degrades the performance when the database grows and it slows down a system.
 May promote information problems, i.e., information island phenomenon. As relational data model is easy
to use, thus many departments may create their own database application, which creates the problem in
integrating information and may create the issues of data inconsistency, redundancy, duplication, etc.
 Few relational databases have limits on the length of fields. It cannot be exceeded to accommodate more
information.

There are some database model that extends the concept of object-oriented programming are discussed below in
brief. The detailed discussion on these models is beyond the scope of this book.

Object-Oriented Data Model

Relational database models may fail to handle the needs of complex information systems. The problem with RDBMS is
that they force an information system to model into form of tables. Increasingly complex real-world problems
demonstrated a need for a data model that more closely represented the real world. In the object-oriented data model
(OODM), both data and their relationships are contained in a single structure known as an object. Object oriented
models represents an entity as a class and its instance as an object. A class represents both attributes and the behavior
of an entity. Object-oriented data models can typically be represented using Unified Modeling Language (UML) class
diagrams. It is used to represent data and their relationships. Some object-oriented databases are designed to work
well with object-oriented programming languages such as Delphi, Ruby, Python, JavaScript, Perl, Java, C#, Visual Basic
.NET, C++, Objective-C and Smalltalk; others such as JADE have their own programming languages

Advantages:
a. Capability to handle large number of different data types. It can store any type of data including text, numbers,
pictures, voice and video.
b. Object oriented programming with database technology
c. Semantic content is added.
d. Object oriented features improve productivity: Inheritance, Polymorphism and Dynamic binding

Limitations:
a. Difficult to maintain
b. Not suited for all applications.

Object-Relational Data Model

Object relational systems combine the advantages of modern object oriented programming languages with relational
database features such as multiple views of data and a high-level, non-procedural query language. Relational database
systems are being enhanced by adding an object infrastructure to the database system itself, in the form of user
defined data types, functions and rules and by building relational extenders that support specialized applications as
image retrieval, advanced text searching and geographic applications. PostgreSQL is an Object-Relational Database
Management System.
Advantages:
a. Complex data sets can be saved and retrieved quickly and easily.
b. Works well with object-oriented programming languages.

Limitations:
a. Object databases are not widely adopted.
b. In some situations, the high complexity can cause performance problems.
c. High system overhead slows transactions
d. Ans 4A

1. Domain Integrity-
Domain integrity means the definition of a valid set of values for an attribute. You define data type, length or size, is null value allowed , is the value unique or not for an attribute ,the
default value, the range (values in between) and/or specific values for the attribute.

2. Entity Integrity Constraint-

This rule states that in any database relation value of attribute of a primary key can't be null.

EXAMPLE- Consider a relation "STUDENT" Where "Stu_id" is a primary key and it must not contain any null value whereas other attributes may contain null value e.g "Branch"
in the following relation contains one null value.

Stu_id Name Branch

11255234 Aman CSE

11255369 Kapil ECE

11255324 Ajay ME

11255237 Raman CSE

11255678 Aastha ECE

3. Referential Integrity Constraint-
It states that if a foreign key exists in a relation then either the foreign key value must match a primary key value of some tuple in its home relation or the foreign key value must be
null.

The rules are:

1. You can't delete a record from a primary table if matching records exist in a related table.
2. You can't change a primary key value in the primary table if that record has related records.
3. You 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.
4. However, you can enter a Null value in the foreign key, specifying that the records are unrelated.
Ans 4B
Select Operation (σ)
It selects tuples that satisfy the given predicate from a relation.

Notation − σp(r)

Where σ stands for selection predicate and r stands for relation. p is prepositional logic formula which may use
connectors like and, or, and not. These terms may use relational operators like − =, ≠, ≥, < , >, ≤.

For example −

σsubject = "database"(Books)

Output − Selects tuples from books where subject is 'database'.

σsubject = "database" and price = "450"(Books)

Output − Selects tuples from books where subject is 'database' and 'price' is 450.

σsubject = "database" and price = "450" or year > "2010"(Books)

Output − Selects tuples from books where subject is 'database' and 'price' is 450 or those books published after 2010.

Project Operation (∏)

It projects column(s) that satisfy a given predicate.

Notation − ∏A1, A2, An (r)

Where A1, A2 , An are attribute names of relation r.

Duplicate rows are automatically eliminated, as relation is a set.

For example −

∏subject, author (Books)

Selects and projects columns named as subject and author from the relation Books.
 EXAMPLE-
Consider 2 relations "stu" and "stu_1" Where "Stu_id " is the primary key in the "stu" relation and foreign key in the "stu_1" relation.

Relation "stu"

Stu_id Name Branch

11255234 Aman CSE

11255369 Kapil EcE

11255324 Ajay ME

11255237 Raman CSE

11255678 Aastha ECE

Relation "stu_1"

Stu_id Course Duration

11255234 B TECH 4 years

11255369 B TECH 4 years

11255324 B TECH 4 years

11255237 B TECH 4 years

11255678 B TECH 4 years

Examples
Rule 1. You can't delete any of the rows in the ”stu” relation that are visible since all the ”stu” are in use in the “stu_1” relation.

Rule 2. You can't change any of the ”Stu_id” in the “stu” relation since all the “Stu_id” are in use in the ”stu_1” relation. * Rule 3.* The values that you can enter in
the” Stu_id” field in the “stu_1” relation must be in the” Stu_id” field in the “stu” relation.

Rule 4 You can enter a null value in the "stu_1" relation if the records are unrelated.