Module - 1

3 Marks Questions

1. Write the difference between specialization and generalization?

2. Explain about three tier schema architecture.

Database Architecture is a representation of DBMS design.

It helps to design, develop, implement, and maintain the database management system.

A DBMS architecture allows dividing the database system into individual components
that can be independently modified, changed, replaced, and altered.

It also helps to understand the components of a database.

 Types of DBMS Architecture

There are mainly three types of DBMS architecture:

● One Tier Architecture (Single Tier Architecture)

● Two Tier Architecture
● Three Tier Architecture

1 Tier Architecture

1 Tier Architecture in DBMS is the simplest architecture of Database in which the client,
server, and Database all reside on the same machine. A simple one tier architecture
example would be anytime you install a Database in your system and access it to practice
SQL queries. But such architecture is rarely used in production.

A 2 Tier Architecture

A 2 Tier Architecture in DBMS is a Database architecture where the presentation layer runs on a
client (PC, Mobile, Tablet, etc.), and data is stored on a server called the second tier.
Two tier architecture provides added security to the DBMS as it is not exposed to the end- user
directly. It also provides direct and faster communication.
 3-Tier Architecture

A 3 Tier Architecture in DBMS is the most popular client server architecture in DBMS in
which the development and maintenance of functional processes, logic, data access,
data storage, and user interface is done independently as separate modules. Three Tier
architecture contains a presentation layer, an application layer, and a database server.

3- Tier database Architecture design is an extension of the 2-tier client-server

architecture. A 3-tier architecture has the following layers:

1. Presentation layer (your PC, Tablet, Mobile, etc.)

2. Application layer (server)
3. Database Server

3. With the help of a diagram explain the different levels of data abstraction?
1. Physical or Internal Level:

● The physical or internal layer is the lowest level of data abstraction in the database
management system.
● It is the layer that defines how data is actually stored in the database.
● It defines methods to access the data in the database.
● It defines complex data structures in detail, so it is very complex to understand, which is
why it is kept hidden from the end user.
● Data Administrators (DBA) decide how to arrange data and where to store data.
● The Data Administrator (DBA) is the person whose role is to manage the data in the
database at the physical or internal level.
● There is a data center that securely stores the raw data in detail on hard drives at this
level.

2. Logical or Conceptual Level:

● The logical or conceptual level is the intermediate or next level of data abstraction.
● It explains what data is going to be stored in the database and what the relationship is
between them.
● It describes the structure of the entire data in the form of tables.
● The logical level or conceptual level is less complex than the physical level.
● With the help of the logical level,
● Data Administrators (DBA) abstract data from raw data present at the physical level.

3. View or External Level:

● View or External Level is the highest level of data abstraction.

● There are different views at this level that define the parts of the overall data of the
database.
● This level is for the end-user interaction; at this level, end users can access the data based
on their queries.
4. What is the referential integrity constraint?

A referential integrity constraint is a database constraint that ensures that the values in one table
(the referencing table) are related to the values in another table (the referenced table). This is
done by creating a foreign key in the referencing table that references the primary key of the
referenced table.

Referential integrity constraints are important for maintaining the accuracy and consistency of
data in a database. Without referential integrity constraints, it would be possible to insert or
update values in a table that would create invalid relationships between tables. This could lead to
data corruption and errors in reports and queries.

There are three main types of referential integrity constraints:

Cascade delete: When a row is deleted from the referenced table, all rows in the referencing
table that reference the deleted row are also deleted.
No action: When a row is deleted from the referenced table, no rows in the referencing table are
affected.
Set null: When a row is deleted from the referenced table, all rows in the referencing table that
reference the deleted row have their foreign key values set to null.

The type of referential integrity constraint that is used depends on the specific requirements of
the database application.

Here are some of the benefits of using referential integrity constraints:

Accuracy: Referential integrity constraints help to ensure that the data in a database is accurate
and consistent.
Consistency: Referential integrity constraints help to ensure that the data in a database is
consistent across multiple tables.
Efficiency: Referential integrity constraints can help to improve the performance of database
queries and reports.
Security: Referential integrity constraints can help to improve the security of a database by
preventing unauthorized users from modifying data.

5. Explain Aggregation with examples.

Aggregation refers to the process by which entities are combined to form a single meaningful
entity. The specific entities are combined because they do not make sense on their own. To
establish a single entity, aggregation creates a relationship that combines these entities. The
resulting entity makes sense because it enables the system to function well.

Example:
In Real-world situation for example if students visit a coaching institute then he shows interest
not only to inquire about the course alone or not only just coaching center, he will definitely
enquire the details about both the coaching institute and the details of the concerned course

Why aggregation

1. Aggregation is a process of compiling information on an object thereby abstracting

higher-level object
2. In SQL we need to find the sum of salaries of all the employees working in an
organization or to find the highest-paid employee from all branches of the organization
etc.

When using data in the form of numerical values, the following operations can be used to
perform DBMS aggregation:

Average (AVG): This function provides the mean or average of the data values.

Sum: This provides a total value after the data values have been added.

Count: This provides the number of records.

Maximum (Max): This function provides the maximum value of a given set of data.

Minimum (Min): This provides the minimum value of a given set of data.
6. List the differences between DBMS and file processing systems.
7. Describe the roles of DBA.

A Database Administrator (DBA) is an individual or person responsible for controlling,

maintaining, coordinating, and operating a database management system.
Managing, securing, and taking care of the database systems is a prime responsibility.
They are responsible and in charge of authorizing access to the database, coordinating
capacity, planning, installation, and monitoring uses, and acquiring and gathering software
and hardware resources as and when needed.
Their role also varies from configuration, database design, migration, security,
troubleshooting, backup, and data recovery.
Various responsibilities of Database Administrator (DBA) :

● Responsible for designing overall database schema (tables & fields).

● To select and install database software and hardware.
● Responsible for deciding on access methods and data storage.
● DBA selects appropriate DBMS software like oracle, SQL server or MySQL.
● Used in designing recovery procedures.
● DBA decides the user access level and security checks for accessing, modifying
or manipulating data.
● DBA is responsible for specifying various techniques for monitoring the
database performance.
● DBA is responsible for operation management.

8. Explain about Attribute Inheritance.

In database management systems (DBMS), attribute inheritance refers to the concept of

inheriting attributes or properties from higher-level entities to lower-level entities within a
database schema. It allows child entities to inherit attributes defined in parent entities, reducing
redundancy and ensuring consistency in attribute definitions.

Here's an explanation of attribute inheritance in DBMS:

Entity Hierarchy:
In DBMS, entities are organized in a hierarchical structure, typically represented through
entity-relationship modeling. This hierarchy is established using relationships such as
one-to-one, one-to-many, or many-to-many. In this context, attribute inheritance occurs when
attributes defined in a higher-level entity are automatically inherited by lower-level entities.

Inherited Attributes:
When an entity inherits attributes from a higher-level entity, it means that the lower-level entity
will possess all the attributes defined in the parent entity. The attributes inherited by the
lower-level entity are not explicitly defined within its own schema but are inherited from the
parent entity. This ensures that common attributes are shared across related entities without the
need for redundant attribute definitions.

Consistency and Redundancy:

Attribute inheritance helps ensure consistency in attribute definitions within the database
schema. When attributes are defined in a parent entity, they can be inherited by multiple child
entities. This avoids the need to redefine the same attributes in every child entity, reducing
redundancy and the potential for inconsistencies in attribute definitions.

Overriding and Extending:

While attribute inheritance ensures the automatic inheritance of attributes, it also allows for the
overriding or extension of inherited attributes in lower-level entities. In some cases, a lower-level
entity may need to provide a modified or specialized implementation of an inherited attribute.
This can be achieved by overriding the inherited attribute with a new definition. Additionally,
lower-level entities can extend the attributes by adding new attributes specific to the child entity,
which are in addition to the inherited attributes.

Schema Organization:
Attribute inheritance helps in organizing the database schema hierarchically. By defining
attributes at higher-level entities, the schema becomes more streamlined and modular. Changes
made to attribute definitions at the parent entity level can propagate to all child entities, ensuring
consistency and ease of maintenance.

Inheritance vs. Aggregation:

It's important to differentiate attribute inheritance from attribute aggregation in the context of
DBMS. Attribute inheritance refers to the hierarchical relationship between entities, where child
entities inherit attributes from parent entities. Attribute aggregation, on the other hand, refers to
the combination of multiple entities into a single entity, resulting in a "has-a" relationship.
Aggregation typically involves combining attributes from multiple entities into a new entity.

Attribute inheritance in DBMS facilitates schema organization, reduces redundancy, and ensures
consistency in attribute definitions across related entities. It simplifies maintenance and promotes
modular design, enhancing the overall efficiency and effectiveness of the database schema.
6 Marks Questions
9. Explain the different operations of Relational Algebra with examples.

Relational Algebra

Relational algebra is a procedural query language. It gives a step-by-step process to

obtain the result of the query. It uses operators to perform queries

1. Select Operation:

● The select operation selects tuples that satisfy a given predicate.

● It is denoted by sigma (σ)
Notation: σ p(r)

Where:

● σ is used for selection prediction

● r is used for relation
● p is used as a propositional logic formula which may use connectors like: AND OR and
NOT. These relations can be used as relational operators like =, ≠, ≥, <, >, ≤.

For example −

σsubject = "database"(Books)

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

2. Project Operation:

● This operation shows the list of those attributes that we wish to appear in the result. Rest
of the attributes are eliminated from the table.
● It is denoted by ∏.

Notation: ∏ A1, A2, An (r)

Where

A1, A2, A3 is used as an attribute name 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.

3. Union Operation:

● Suppose there are two tuples R and S. The union operation contains all the tuples that
are either in R or S or both in R & S.
● It eliminates the duplicate tuples. It is denoted by 𝖴.

Notation: R 𝖴 S = { t | t ∈ r or t ∈ s}

A union operation must hold the following condition:

● R and S must have the attribute of the same number.

● Duplicate tuples are eliminated automatically.

Eg:

∏ author (Books) 𝖴 ∏ author (Articles)

Output − Projects the names of the authors who have either written a book or an article or
Both.

4. Set Intersection:

● Suppose there are two tuples R and S. The set intersection operation contains all tuples
that are in both R & S.
● It is denoted by intersection ∩.

Notation: R ∩ S

5. Set Difference:

● Suppose there are two tuples R and S. The set intersection operation contains all tuples
that are in R but not in S.
● It is denoted by intersection minus (-).

Notation: R - S

● Finds all the tuples that are present in r but not in s.

Eg:

● ∏ author (Books) − ∏ author (Articles)

 ● Output − Provides the name of authors who have written books but not articles.

6. Cartesian product

● The Cartesian product is used to combine each row in one table with each row in the
other table. It is also known as a cross product.
● It is denoted by X.

Notation: R X S

Where r and s are relations and their output will be defined as −

r Χ s = { q t | q ∈ r and t

∈ s} Eg:

σauthor = 'tutorialspoint'(Books Χ Articles)

7. Rename Operation:

The rename operation is used to rename the output relation. It is denoted by rho (ρ).

8. Natural Join:

● The natural join is a binary operation that allows us to combine certain selections
and a Cartesian product into one operation.

● It is denoted by the join symbol

● While applying natural join on two relations, there is no need to write equality
conditions explicitly.

● Natural Join will also return the similar attributes only once as their value will be
same in the resulting relation.
10. List the drawbacks of File Systems to store data.

1. Data redundancy:

In File Processing Systems, the same data is often stored in multiple files, which can lead to data
redundancy. Data redundancy means that the same data is stored in more than one place, leading
to the wastage of storage space and making data maintenance more difficult.

2. Data inconsistency:

Due to data redundancy, inconsistencies can occur in the data. If changes are made to one file,
these changes must be manually made to all other files containing the same data. If this is not
done properly, data inconsistencies can arise.

3. Limited data sharing:

File Processing Systems are not designed to share data between applications or users. Each
application has its own set of files, and sharing data between applications is difficult and often
requires custom coding.

4. Security:

File Processing Systems do not have built-in security features to protect data from unauthorized
access. Access to files is typically controlled through file permissions, which can be difficult to
manage and can lead to security breaches.

5. Lack of flexibility:

File Processing Systems are not flexible and cannot easily adapt to changes in the data structure
or new requirements. Adding new fields or modifying existing fields requires significant effort
and can disrupt existing applications that rely on the data.

6. Limited scalability:

File Processing Systems are not scalable and can handle only a limited amount of data. As the
amount of data grows, File Processing Systems can become slow and unreliable.

7. Difficult to maintain:

File Processing Systems require significant effort to maintain. As the number of files and
applications grows, it can become difficult to keep track of which files are being used by which
applications and to ensure data consistency.
11. With the help of a neat sketch describe the architecture of a database system.

Refer question no:2

12. Briefly explain how data is represented and accessed in DBMS.

In a database management system (DBMS), data is represented and accessed using a structured
approach based on tables, rows, and columns. Here's a brief explanation of how data is
represented and accessed in DBMS:

Data Representation:
1. Tables: Data in a DBMS is organized into tables, also known as relations. Each table
represents a specific entity or concept, such as "Customers," "Orders," or "Products." Tables
consist of rows and columns.

2. Rows: Rows, also called records or tuples, represent individual instances of data within a
table. Each row contains a unique identifier called a primary key and stores values for different
attributes or fields defined in the table's schema.

3. Columns: Columns, also referred to as attributes or fields, define the types of data that can be
stored in a table. Each column has a specific name and data type, such as text, numeric, date, or
Boolean.

Data Access:
1. Query Language: DBMS provides a query language, such as SQL (Structured Query
Language), to retrieve, manipulate, and manage data. Users can write queries using SQL to
perform operations like selecting, inserting, updating, and deleting data.

2. Retrieval: Data retrieval is achieved using SELECT statements in SQL. Users specify the
desired columns and optional conditions to filter the rows they want to retrieve. The query
engine processes the query and returns the requested data.

3. Insertion, Update, and Deletion: DBMS allows users to insert new data into tables using
INSERT statements, update existing data using UPDATE statements, and delete data using
DELETE statements. These operations modify the data stored in the tables based on specified
criteria.

4. Joining and Relating Data: DBMS supports joining tables based on common attributes or
keys. Users can combine data from multiple tables using JOIN operations to create meaningful
relationships between different entities and retrieve data that spans across multiple tables.
5. Indexing: DBMS employs indexing techniques to enhance data access performance. Indexes
are data structures that store a sorted copy of one or more columns, allowing for faster searching
and retrieval of data. Indexes are created on specific columns to optimize query execution.

6. Security and Access Control: DBMS provides mechanisms to control access to data and
ensure data security. Users can be assigned specific roles and privileges that determine their level
of access to tables, ensuring data confidentiality and integrity.

DBMS provides a structured and efficient approach to represent, organize, and access data. It
offers a standardized query language, data manipulation operations, indexing techniques, and
security measures, enabling users to interact with the data in a controlled and efficient manner.

13. How does specialization differ from generalization? Explain with the help of an E-R
Diagram?

Generalization

● It works using a bottom-up approach.

● The size of the schema is reduced.
● It is generally applied to a group of entities.
● Inheritance is not used in generalization.
● It can be defined as a process where groupings are created from multiple entity sets.
● It takes the union of two or more lower-level entity sets, and produces a higher-level
entity set.
● Some of the common features are obtained in the resultant higher-level entity set.
● The differences and similarities between the entities that need to be in union operation are
ignored.

Example:
Pigeon, house sparrow, crow and dove can all be generalized as Birds −
Specialization

● It uses a top-down approach.

● The size of the schema is increased.
● It can be applied to a single entity.
● It can be defined as the process of creation of subgroups within an entity set.
● It is the reverse of generalization.
● It takes a subset of a higher level entity, and forms a lower-level entity set.
● A higher entity is split to form one or more low entities.
● Inheritance can be used in this approach.

Example
A person has a name, date of birth, gender, etc. These properties are common in all
persons, human beings. But in a company, persons can be identified as employee,
employer, customer, or vendor, based on what role they play in the company.