Unit 3-Database Management System

Important and Difficult Q & A

Introduction to Database Management System

[Link] is Data model? Explain the different data models

The structure of database is known as data model that describes the manner in which data will
be stored, maintained and fetched from the database.
There are many components in the data model, all these components are used for representing
the data, relationships between different data and various conditions that are applied on the
data.
Different types of Data Models
1. Hierarchical Data Model 2. Network Data Model 3. Relational Data Model
Hierarchical Data Model
In Hierarchical Data Model, data is arranged in the form of a hierarchy of different levels. A tree
like structure is formed of data. The data is stored in the form of records.
A record is a collection of fields and its data values. All these records are arranged in the form of
hierarchy and are linked to each other at various levels.
Network Data Model
Network Data Model is similar to hierarchical data model with a difference that the records are
not arranged in the form of hierarchy. There are no levels of hierarchy, any record can be linked
to any other record.
Relational Data Model
In Relational Data Model, the data is stored in the form of relation or table. these relations are
linked to each other through referential integrity constraints. This data model is most commonly
used for representing structured database.

2. Give one point of difference between

(a) Data and Information.
(b) Form and Query.
Answer:
(a) Data and Information: Data comprises raw, unprocessed facts that need context to become useful, while
information is data that has been processed, organized, and interpreted to add meaning and value.

(b) Form and Query: Forms provides an interface to users to view, add, modify in both tables and queries.
Reports are presented forms used to present formatted summaries or lists of the data from one or more
tables or queries.

4. Consider the table given below and answer the questions that follow
Table: Library
Book_Id Book_Name Author_Name Price Publisher
F001 Pride and Prejudice Jane Austen 550 ABC

1|Page
 S004 Amazing Astronomy E. Shane 1050 ABC
C005 IT and Mankind MHA Diwaan 2500 HYM

(a) Name the fields in the given table.

(b) Which field should be made the primary key?
(c) Is there any alternate key in the table?
(d) How is primary key different from foreign key? Explain with example.

Answer:
(a) Book_Id, Book_Name, Author_Name, Price, Publisher.
(b) Book_Id field should be made the Primary Key.
(c) Yes we can make Publisher as Foreign Key.
(d) The main difference between a primary key and a foreign key is that a primary key uniquely identifies
each row in a table, while a foreign key links data in one table to another:
Primary Key:
A column or columns that uniquely identify each row in a table. Primary keys ensure that each record is
identifiable and that no two rows contain the same value. They are used to extract or archive data from a
table. Examples of primary keys include a driver's license number, telephone number with area code, or
vehicle identification number (VIN).
Foreign Key:
A column or columns that references a primary key in another table. Foreign keys establish and enforce
relationships between tables, and ensure data integrity. For example, in a library database, a Books table
might have a BookID primary key, and a Loans table might have a BookID foreign key. The foreign key
references the primary key in the Books table, ensuring that only valid BookIDs are stored in the Loans table.
Here are some other differences between primary keys and foreign keys:
1. Number of keys: There is only one primary key in a table, but there can be multiple foreign keys.
2. Null values: Primary keys cannot accept null values, but foreign keys can.
3. Deleting values: Primary key values cannot be removed from the parent table, but foreign key values
can.
4. Temporary tables: Primary keys can be defined in a temporary table, but foreign keys cannot.

Starting with LibreOffice Base

1. Differentiate between:
(a) Memo and Varchar data type
(b) Number and Decimal data type
(c) Design View and Datasheet view of a table
Answer:
(a) Memo and Varchar data type
Memo Varchar
Can store text or binary data of any format and Stores character strings of varying length that
variable length. The memo data type is also can contain single-byte and multi-byte
known as CLOB (character large object) or BLOB. characters. The length of a varchar column can
In Microsoft Access desktop databases, the be specified as a value from 0 to a limit, which
memo data type is now called "Long Text". varies by database.

2|Page
(b) Number and Decimal data type
Number Decimal
The Numeric data type is used to represent a The Numeric data type is used to represent a
number with fixed precision and fixed scale. This number with fixed precision and fixed scale. This
means, there is a defined precision for numeric means, there is a defined precision for numeric
data type such that if we try to store a number data type such that if we try to store a number
that is out of bounds, the numeric data type will that is out of bounds, the numeric data type will
return an error. return an error.

(c) Design View and Datasheet view of a table

View Datasheet View

Allows you to create or change the table, Allows you to visualize your data, fields, and
form, or other database object, and configure records in the form of a spreadsheet. You can
the fields. You can also see the header, detail, edit the data, but you can't change the format of
and footer sections for the form. the database other than minor changes.

5. Write steps to sort the table in descending order of primary key.

Answer:
Step 1: Open the Event table in datasheet view and select the field on which you want to sort, that the field
“Points” is selected to sort the records in ascending order.

Step 2: From the tool bar click Sort Ascending icon if the table has to be sorted in ascending order of selected
field. Alternatively select Sort Descending icon if the table has to be sorted in descending order of selected
field.

Step 3: The table will be sorted in the descending order of points.

Working with Multiple Tables

1. Give any two advantages of relating a table in a database.
Answer:
Relating tables in a database can help you understand the relationships between data and create meaningful
information. Here are some advantages of relating tables in a database:

Create Meaningful Information:

You can join tables to understand how they connect and create meaningful information.

Automatically Update Related Fields:

3|Page
You can use the Cascade Update Related Fields option to automatically update all fields that reference a
primary key when you update it.

Flexibility:
You can easily add, update, or delete tables and relationships without impacting the database structure or
existing applications.

2. How is redundancy or inconsistency controlled in a database? Explain with an example.

Answer:
Database redundancy and inconsistency can be controlled by:
Normalization: This process organizes data to minimize duplication and dependency. It involves reorganizing
the database's tables and columns to ensure that dependencies are enforced correctly.
Primary keys: Each record has a unique identifier.
Foreign keys: These ensure that relationships between tables are consistent.
Constraints: These are rules that enforce data integrity.
Deleting unused data: If you move data to a new database but forget to delete it from the old one, you'll
have the same data in two places.
Centralized administration: This removes inconsistency by ensuring that the same information is not stored
in different files.

3. Define referential integrity. Who maintains referential integrity in a database?

Answer:
Referential integrity is a database concept that ensures the relationships between tables remain consistent
and accurate. It's maintained by foreign key constraints in a database system.

 Referential integrity is a logical dependency between a foreign key and a primary key. A foreign key
is a primary key from one table that appears in another table. Referential integrity ensures that each
foreign key in a table points to a unique primary key value in another table.
 Referential integrity is important because it prevents incorrect records from being added, deleted,
or modified. It also helps to ensure that data is not lost and that data quality is maintained.
 Referential integrity is enforced through constraints, such as foreign key constraints. When an SQL
operation attempts to change data in a way that would violate referential integrity, the database
manager enforces a set of rules associated with each referential constraint.

4. Differentiate between one to one relationship and one to many relationship. Give suitable examples to
explain your answer.
Answer:
The main difference between a one-to-one relationship and a one-to-many relationship is that in a one-to-
one relationship, each record in one table is linked to a single record in another table, while in a one-to-
many relationship, one record in one table is linked to multiple records in another table:
 One-to-one relationship: In a one-to-one relationship, each record in one table has at most one
related record in another table. For example, a one-to-one relationship can exist between employees
and the cars they drive.
 One-to-many relationship: In a one-to-many relationship, one record in one table is linked to
multiple records in another table. For example, a one-to-many relationship can exist between a
product category and the individual products within that category.

4|Page
5. Explain many to many relationship with an example.
Answer:
 A many-to-many relationship is when one or more items in one table can be related to one or more
items in another table.
 To create a many-to-many relationship, you need to create a new table to connect the other two
tables. This third table is called a join table. Each record in the join table includes a match field that
contains the value of the primary keys of the two tables it joins.
 Here are some examples of many-to-many relationships:
1. Customers and Products: Customers can buy multiple products, and products can be
purchased by many customers.
2. Students and Classes: Students can enroll in multiple courses, and each course can have
multiple students.
3. Reservations and Payments: A hotel's reservation table and payment table both include the
name of the guest, and a guest can have multiple reservations and payments.
4. Sales Reps and Customers: Customers can buy from multiple sales reps on a sales team, and
a sales rep can sell to many different customers.
6. To Maintain referential integrity, explain the four options provided by base
1. No action
This is the default option. This option states that user should not be allowed to update or delete
any record in the master table if any related record exist in the transaction table.
2. Update Cascade
This option allows the user to delete or update the referenced field but along with
it all the related records in any of the transaction tables will also be deleted or updated.
3. Set NULL
This option assigns NULL value to all the related fields if the master record is deleted or updated.
4. Set default
This option assigns any fixed default value to all the related fields if the master record is deleted or
updated.

Queries in Base
1. Define a query? What is the need of creating a query in a database?
Answer:
A query is one of the most important feature of any DBMS. Using a query, we can retrieve and display data
from one or more tables in a database. This is done by giving specific search criteria to the DBMS so that
we are able to view the exact information that we want.
Need of Creating a Query:
1. Retrieving Specific Information: Queries allow you to extract specific data from your database based on
your needs. You can filter, sort, and group data to answer specific questions or generate reports.
2. Data Analysis and Insights: Queries enable you to analyze data, identify trends, and gain insights. You
can use them to calculate statistics, find patterns, and make informed decisions.
3. Data Manipulation: Queries can be used to modify data in your database. You can update records, insert
new data, or delete existing data based on specific criteria.
4. Data Integration: Queries can be used to combine data from multiple tables or sources. This allows you

5|Page
to create comprehensive views of your data and perform complex analysis.

2. Rearrange the steps given below so as to create a query using a wizard.

 Give Alias
 Select the fields
 Set the criterion
 Set the sorting order
 Give table name
Answer:
 Select the fields
 Give table name
 Give Alias
 Set the criterion
 Set the sorting order

3. What all information is seen in the overview (last step) of the Query wizard?
Answer:
The last step of the Query wizard displays the entire overview of the query.
• Name of the Query – By default, the name of the query is Query_Events by default. If desired, type the
new name in the text box.
• The action to be performed after the wizard finishes –
By default Display Query option will be selected. Click and select the Modify Query radio button if the
query has to be edited in the Design view.
• Complete detail of the query –
This section contains a summary about the query that has been created.

4. What is the use of Alias row in the Design grid of the Query Design window?

Answer:
The column header name will be displayed when we run the query. By default the field names will be
displayed as column headers. Many times field names are not user friendly, so an alias name which is more
readable, is chosen to be displayed in the query output.

5. Name any four mathematical functions that can be applied to numerical data in a query.
Answer:
1. count
2. sum
3. minimum
4. maximum
5. average

6. Name the three ways of creating a query in LibreOffice Base?

Answer:
A query can be created in three ways.
1. Using a Wizard
2. In Design View
6|Page
 3. In SQL view

Forms and Reports

1. Give one difference between a form and a report.
Answer:
FORMS REPORTS
It provides systematic way of storing It displays and print the data in a summarized
information into a database manner.
It lets user to provide input, view or edit data It provides output of information in specified.
directly in database objects. User cannot edit or enter data.
Forms gather information of a record form Reports can represent information from
single table. multiple tables.

2. What is a field control with respect to forms?

Answer:
A field control consists of a physical control (either a date box, DateTime box, label, Boolean check box,
number box, radio button, text box, or time box) with a label. The field type determines the kinds of controls
that can be used for each field.

3. Which tool on the Forms Record toolbar is used to insert text on the form?
Answer:
Tool for inserting text on the form The "Text Box" tool on the Forms Record toolbar is used to insert text
on the form.

4. What is the difference between a static and a dynamic report?

Static reports Dynamic reports
These reports show data in real time and
can change automatically to reflect the
These reports contain fixed information that remains
most current information. They can be
unchanged unless manually updated. They are like a
accessed anywhere, at any time, by anyone
photograph that captures a moment in time. Static
who has permission to view it. Dynamic
reports can be limiting in scope, and it can be difficult
reports incorporate an interactive interface
to analyze or compare specific metrics and results.
that allows users to customize how they see
the information.

4. Write the function of Forms Controls toolbar and Records toolbar.

Answer:
Forms Controls toolbar:
Provides tools for adding and configuring controls (like text boxes, buttons, etc.) on a form.

Records toolbar:
Offers tools for navigating through records, adding new records, and saving changes.

7|Page