1. What is the significance of Physical Data Independence?
hysical Data Independence is important because it lets you change how the data is
P
physically stored without having to change the main design of the database1. For example, if
you change the storage size of the database server, the main structure of the database
doesn't get affected2. This makes it easier to manageand update the database system.
2. List the four functions of DBA.
Database Administrator (DBA) is the person who is in charge of everything in the database
A
system3. Here are four of their main jobs:
● Defining the schema: Deciding how the database isstructured4.
● Defining storage: Deciding how and where the datais stored5.
● Granting access: Giving users permission to use thedatabase6.
● S
etting rules: Making sure the data is accurate andconsistent by specifying integrity
constraints7.
. Draw the overall structure of DBMS and explain its various
3
components.
The document shows a drawing of the overall DBMS structure8.
Here are its main parts explained simply:
● Users: Different people who use the database in differentways9. This includes regular
users, programmers, and the database administrator (DBA)10101010.
● Q
uery Processor: This part understands what the userwants from the database and
makes a plan to get it11111111. It includes:
○ DDL Interpreter: Reads commands that define the databasestructure12.
○ D
ML Compiler: Translates requests for data into simpleinstructions that the system
can execute13.
○ Query Evaluation Engine: Follows the simple instructionsto get the actual data14.
● S
torage Manager: This is the part that handles theactual saving and retrieving of data
from the disk15. It makes sure data is stored, updated,and read efficiently16.
● D
isk Storage: This is the physical place where everythingis stored, including the data
itself, information about the data (the data dictionary), and indexes to find data
quickly17171717.
4. Write the difference between DDL and DML.
● D
DL (Data Definition Language)is used tocreate anddesignthe database
structure18. Think of it as the blueprint for buildingthe database tables.
● D
ML (Data Manipulation Language)is used towork withthe datainside the
database19. This includes adding, changing, deleting,or reading data.
5. Define candidate key and super key with example.
● S
uper Key: A super key is any set of one or more columnsthat can uniquely find any
single row in a table20.
● Candidate Key: A candidate key is aminimalsuperkey21. This means it's a super key
with no extra, unneeded columns. A table can have several candidate keys22.
○ E
xample: In acustomertable, thecustomer-idcolumnis a candidate key because it
can uniquely identify each customer23. In an
accounttable, theaccount-numberis a candidate key24.
6. Differentiate TRUNCATE and DELETE command.
The provided document does not explain the TRUNCATE or DELETE commands.
7. What is the difference between DROP and DELETE command?
The provided document does not explain the DROP or DELETE commands.
8. What are the different types of Data Models in DBMS? Explain them.
data model is a set of tools used to describe how data is structured and related25252525. The
A
main types are:
● O
bject-Based Models: These describe data using objectsand their relationships.
○ Entity-Relationship (E-R) Model: This is a popularmodel for database design that
uses entities (things like acustomerorproduct)and shows the relationships
between them262626262626262626.
● R
ecord-Based Models: These models are based on records.
○ Relational Model: This is the most common model. Itorganizes data into tables with
rows and columns27272727.
○ N
etwork Model: This model connects records in a graph-likestructure, where a
record can have many "parent" and "child" records28282828.
○ H
ierarchical Model: This model organizes data in a tree-like structure, like a family
tree, with parent and child records29.
. State the procedural DML and non procedural DML with their
9
differences.
● P
rocedural DML: The user tells the systemwhat datathey wantand alsohow to get it
step-by-step30303030.
● N
onprocedural DML: The user only tells the systemwhat data they want, but not how
to get it31.
he main difference is that procedural DML requires you to give detailed instructions, while
T
nonprocedural DML lets the database figure out the best way to get the data for you32. SQL is
the most common nonprocedural language33.
1 0. What is ER Diagram? Explain different Components of an ER
Diagram with their Notation. Also make an ER Diagram for Employee
Project Management System.
An
E-R Diagramis like a blueprint for a database34.It's a drawing that shows the different pieces
of information in a system and how they are connected35.
The main parts (components) and their symbols (notations) are:
● Entity Set: A real-world object or concept, like apersonor acar36. A group of the same
type of entities is an entity set37.
○ Symbol: A rectangle38. A double rectangle is usedfor a "weak" entity, which depends
on another entity39.
● Attribute: A property that describes an entity, likethenameof a person or thecolorof a
car40.
○ Symbol: An oval (ellipse)41. A double oval means itcan have multiple values (like
phone numbers)42.
● R
elationship Set: Shows how two or more entities areconnected or related to each
other43.
○ Symbol: A diamond44.
he document you provided does not have an E-R Diagram for an "Employee Project
T
Management System."
1 1. What is Data Abstraction? How the Data Abstraction is achieved in
DBMS?
ata Abstraction means hiding the complicated details of how data is stored, so users only
D
see a simple view of it45.
This is done in three levels:
1. P
hysical Level: The lowest level. It describes
how the data is actually storedon the computer'sdisks46. Users don't see this.
2. L
ogical Level: The middle level. It describes
what data is storedand what relationships exist betweenthe data, like acustomertable
withnameandcitycolumns47.
3. V
iew Level: The highest level. It shows
only a part of the databasethat is relevant to aspecific user48. For example, a bank
teller might see a customer's account balance but not their salary49.
1 2. List any four disadvantages of file system approach over database
approach.
sing regular files to store data has several problems compared to using a DBMS50. Here are
U
four:
1. D
ata Redundancy and Inconsistency: The same informationcan be saved in multiple
different files, which can lead to errors if it's updated in one place but not another51.
2. D
ifficulty in Accessing Data: To get information,you often need to write a new
computer program for each new task52.
3. I ntegrity Problems: Rules about the data (like anaccount balance must be greater than
zero) are hard to enforce because they are hidden inside the program code53535353.
4. P
roblem with Multiple Users: If multiple users tryto access and update the same file at
the same time, it can lead to inconsistent data54545454.
13. Differentiate between physical and logical data independence.
Feature Logical Data Independence hysical Data
P
Independence
What It Is he ability to change the
T he ability to change how
T
main database design data is physically stored
without affecting the user's without affecting the main
view or application database design56.
programs55.
Levels It Separates eparates the user's view
S eparates the main
S
from the main (conceptual) (conceptual) database
database design57. design from the physical
storage58.
Example ou can add a new column
Y ou can move the database
Y
to a table without breaking to a new, faster hard drive
xisting programs that
e t o improve performance,
don't use that column59. and the main design of the
database stays the same60.
1 4. Illustrate the concept of data independence and its type with
example. Why is it required?
ata independencemeans you can change one part ofthe database without having to
D
change other parts61. It is needed so that "changesin some parts do not seriously influence
others"62. This makes the database system much moreflexible.
There are two types:
1. P
hysical Data Independence: Lets you change the physicalstorage of data without
affecting the main database structure63.
○ E
xample: The DBA can change how data is stored ondisk to make the system faster.
Thanks to physical data independence, this change won't require anyone to rewrite
their application programs64.
2. L
ogical Data Independence: Lets you change the maindatabase structure without
forcing you to rewrite user application programs65.
○ E
xample: You can add a new attribute (like an emailaddress) to the customer table.
Any existing programs that don't need the email address will continue to work
without any changes66.
1 5. Explain the architecture of DBMS in terms of its components with a
proper diagram.
The architecture of a DBMS explains how its different parts are connected, usually in a
client-server setup67.
1. 1 -Tier Architecture: The simplest setup where thedatabase and the application are on
the same machine68.
2. 2-Tier Architecture: This is a basic client-serversetup69.
○ Client: Your computer, which runs the application70.
○ Server: A powerful computer where the database isstored and managed71. The
client application talks directly to the server72.
3. 3-Tier Architecture: This adds a middle layer formore complex applications73.
○ Client: Your computer, which just shows the user interface74.
○ A
pplication Server (Middle Tier): The client talksto this middle layer, which
contains the business logic and handles tasks75.
○ D
atabase Server: The application server talks to thedatabase server to get or store
data76.
1 6. What are the different types of Data Models in DBMS? Explain
them.
This question is the same as question 8. Please see the answer for question 8.
17. Define candidate key, super key, primary key and foreign key.
● S
uper Key: A set of one or more columns that can beused to uniquely identify any row in
a table77.
● C
andidate Key: A minimal super key. It's a columnor set of columns that can uniquely
identify a row, with no extra columns78.
● Primary Key: The one candidate key that is chosento be the main identifier for a table79.
It cannot have any empty (null) values80.
oreign Key: A column in one table that points tothe primary key in another table81. It's
● F
used to link two tables together.
18. What is strong and weak entity set explain with example?
● S
trong Entity Set: This is a regular entity set thathas its own primary key to uniquely
identify its members82. For example, a
ustomer entity with a customer-id is a strong entity. It is shown with a single rectangle in
c
an E-R diagram83.
● W
eak Entity Set: This is an entity set that cannotbe uniquely identified by its own
attributes alone84. It depends on a strong entityset (called the owner) to be identified.
○ E
xample: A payment entity for a loan. A payment onlymakes sense in the context of
a specific loan. Its full key would be the
loan_number(from the strong entityloan) plus itsownpayment_number85. It is
shown with a double rectangle in an E-R diagram86.
19. Discuss DDL and DML command.
The document discusses DDL and DML as types of languages, not specific commands.
● D
DL (Data Definition Language): This language is usedtodefinethe database
structure87. You use it to create tables and definewhat columns they have. The DDL
compiler saves these definitions in the data dictionary88.
● D
ML (Data Manipulation Language): This language isused tomanagethe data inside
the tables89. It lets users add, update, delete, andretrieve data.
20. Discuss the role of database administrator.
The
atabase Administrator (DBA)is the person in chargeof managing the entire database
D
system90. Their main roles are:
● To define the database structure (schema)91.
● To decide how the data is stored and accessed92.
● To make changes to the database structure when needed93.
● To give users permission to access the database94.
● To set rules (integrity constraints) to keep the data accurate95.
● To talk with users and help with their needs96.
● To monitor how well the database is performing and make improvements97.
21. Give example for one to one and one to many relationship.
The document uses a customer and a loan to show relationships.
● O
ne-to-One Relationship: This means one customer canhave at most one loan, and
one loan can belong to at most one customer98. Thisis shown with an arrow pointing
from the relationship diamond to each entity99.
● O
ne-to-Many Relationship: This means one loan canbelong to at most one customer,
but one customer can have many loans100. This is shownwith an arrow pointing to the
"one" side (
loan) and a simple line to the "many" side (customer)101.
2. Describe the three-schema architecture. Why do we need
2
mappings between schema levels? How do different schema
definition languages support this architecture?
he three-schema architecture is another name for the three levels of data abstraction that
T
separate how users see the data from how it's actually stored102. The three schemas (or
levels) are:
1. Physical Schema: The design for how data is physicallystored on disk103.
2. L
ogical Schema: The main design for the entire database,showing what data is stored
and how it's related104.
3. View Schema: A design for a particular part of thedatabase that a specific user sees105.
We need mappings between these levels to create
ata independence106. This means we can change a lowerlevel (like how data is stored)
d
without affecting the higher levels (like what the user sees).
The document says that a
ata Definition Language (DDL)is used to define thedatabase schema107, but it doesn't
D
explain in detail how different languages are used for each specific level.
3. Explain the following with example (i) Generalization (ii)
2
Specialization (iii) Aggregation.
● ( i) Generalization: This is abottom-upprocess. Youtake several specific entity sets that
have common features and combine them into a single, more general one108108108108. For
example, you could take
officer,teller, andsecretaryand generalize them into a singleemployeeentity109.
● ( ii) Specialization: This is atop-downprocess. Youtake a general entity set and break it
down into more specific subgroups110. For example,you can take a general
personentity and specialize it intoemployeeandcustomersubgroups111. The subgroups
inherit the properties of the general entity112. Thisis shown with an
ISAtriangle in diagrams113.
● ( iii) Aggregation: This is when you treat a relationshipbetween entities as a single entity
itself114. This lets you create relationships involvingother relationships.
○ E
xample: An employee works_on a job at a branch. Ifyou want to say that a
managermanages this entire activity, you can useaggregation to bundle the
works_onrelationship into a single thing that themanagercan manage115.
4. Draw an ER diagram of Hospital or Bank with showing the
2
Specialization, Aggregation, and Generalization. Also convert it in to
relational schemas and SQL DDL.
The document has a large E-R diagram for a bank on page 34116.
● T
his diagram showsSpecialization/Generalization.The
accountentity is specialized into asavings-accountand a
checking-account117117117117117117117117117.
● H
owever, this specific bank diagram does not showAggregation. A separate example for
aggregation is shown on page 29118.
The document explains how to convert E-R diagrams into tables (relational schemas)119119119119.
For example, each entity set becomes its own table120.
owever, the document does not provide the full set of tables or the SQL DDL commands for
H
the bank diagram.
5. A database is being constructed to keep track of the teams and
2
games of a sport league... (i) Design an E-R schema diagram for this
application. (ii) Map the E-R diagram into relational model.
he document you provided does not contain any information, diagrams, or models for a
T
sports league database. Therefore, this question cannot be answered.
6. Explain the process of reducing an ER diagram to tables. Provide a
2
simple example.
he process of turning an E-R diagram into tables is the first step in creating a relational
T
database121. Here's how it generally works:
1. C
reate a Table for Each Set: You create a unique tablefor each entity set and for each
relationship set in the diagram122.
2. A
dd Columns for Attributes: The columns of each tablewill be the attributes of that
entity or relationship123.
3. H
andling Entity Sets:
○ A
strong entity setbecomes a table with its attributes124.
○ A
weak entity setbecomes a table that includes columnsfor its own attributes plus
the primary key of the strong entity it depends on125.
4. H
andling Relationship Sets:
○ Amany-to-manyrelationship becomes its own table.This table includes the primary
keys from the two entities it connects126.
○ F
ormany-to-onerelationships, instead of creatinga new table, you can just add a
new column (a foreign key) to the table on the "many" side. This new column will hold
the primary key of the entity on the "one" side127.
he second part of your question asks to convert a specific university database example. The
T
document you provided does not contain this example, so it cannot be converted.
