UNIT 3 – DATABASE MANAGEMENT SYSTEM

NOTES:

Data: Raw facts and figures are called Data

Information: It is the processed or organized form of data.

Database: A database is a collection of related data items stored in an organised manner.

DBMS: The software that is used to create, update and retrieve data is known as Database Management System(DBMS). Example : MS Access, LibreOffice Base, Oacle, Ingress, MySQL.

Data Models:

A database can be designed in different ways depending on the data being stored. This structure of database is known as data model that describes.

Hierarchical Data Model :In this model the data is organized into a tree like structure. The data is stored in the form of records.

Network Data Model :In this model, multiple records are linked to same master file. It is also considered as an inverted tree where master is present in the bottom of the tree and the branches contain information linked to the master.

Relational Data Model :This data model is based on the principle of setting relationships between two or more tables of the same database.

Some of the common terms used in RDBMS.

Entity – It is a real world object about which information is to be stored in a database . For example Student

Attributes: Details associated with the entity are called attributes. Example: Rollno,Name, Class,Marks

Table – A table is a collection of logically related records. It is organised as a set of columns, and can have any number of rows.

Record or Row – The data values for all the fields related to a person or object is called a record. It is presented as rows within a table

Primary Key – A primary key or simply a key is a field that uniquely identifies a row in a table. For example, in a student table, each student has a unique roll no., which forms the primary key.

Foreign Key: A foreign key in a database management system (DBMS) is a column or set of columns in one table that reference the primary key of another table

Objects of an RDBMS

Table – A table is the basic unit of any DBMS.

A table is a collection of logically related records. It is organised as a set of columns, and can have any number of rows.

Forms – A form is a feature of a database using which we can enter data in a table in an easy and user friendly manner.

Reports – A report in a database management system (DBMS) is a structured presentation of data from a database that’s organized for a specific purpose. Reports can be used to: analyze patterns and trends, make decisions, and track progress.

Query– A query is used to retrieve the desired information from the database. For example, if we want to view the names of only those students who have scored more than 50 marks, then we post a query.

Chapter 8 – Introduction to Database Management System

Q1. Define the terms

(a) Database:
An organized collection of related data stored in a structured format so that it can be easily accessed, managed, and updated.

(b) Data redundancy:
Storing the same data in more than one place, leading to duplication and possible inconsistency.

(c) Report:
An organized and formatted presentation of data from a database, generated for analysis or decision-making.

Q2. Give one point of difference between

(a) Data and Information:

Data: Raw facts and figures without context.
Information: Processed and organized data that is meaningful and useful.

(b) Form and Query:

Form: Used to enter, modify, and view records in a database.
Query: Used to retrieve specific data from a database by applying conditions.

(c) Network and Hierarchical Data Model:

Network Model: Data is organized using records connected by links, allowing many-to-many relationships.
Hierarchical Model: Data is organized in a tree-like structure with one-to-many parent-child relationships.

Q3. Give any four advantages of a DBMS

Reduces data redundancy.
Maintains data integrity and accuracy.
Provides data security through access control.
Allows multi-user access and data sharing.

Q4. Consider the table given below and answer the questions

Book_Id	Book Name	Author Name	Price	Publisher
F001	Pride and Prejudice	Jane Austen	550	ABC
S004	Amazing Astronomy	E. Shane	1050	ABC
C005	IT and Mankind	MHA Diwaan	2500	HYM

(a) Fields: Book_Id, Book Name, Author Name, Price, Publisher.
(b) Primary Key: Book_Id (uniquely identifies each record).
(c) Alternate Key: Book Name (if each book name is unique).
(d) Difference between Primary Key and Foreign Key:

Primary Key: Unique identifier for each record in a table (e.g., Book_Id in Library table).
Foreign Key: Field in one table that refers to the primary key in another table (e.g., Book_Id in Issue table referring to Library table’s Book_Id).

Chapter 9 – Tables in LibreOffice Base

Q1. Differentiate between

(a) Memo and Varchar data type:

Memo: Stores long text entries such as paragraphs (up to ~65,000 characters).
Varchar: Stores short text strings of variable length with a defined maximum size.

(b) Number and Decimal data type:

Number: Stores numeric values without decimal precision.
Decimal: Stores numeric values with decimal precision for currency or exact calculations.

(c) Design View and Datasheet View of a table:

Design View: Used to define table structure (fields, data types, primary keys).
Datasheet View: Used to view, enter, and edit data directly.

Q2. Name the menu items present on the Base User Interface

File, Edit, View, Insert, Tools, Window, Help.

Q3. Label the components of LibreOffice Base User Interface

Title Bar: Displays the name of the database.
Database Pane: Lists objects like Tables, Queries, Forms, and Reports.
Tasks Pane: Displays actions for the selected object.
Status Bar: Shows record count, navigation controls, and view information.

Q4. How can we define a primary key in a table?

Open the table in Design View.
Select the field to set as primary key.
Click the Primary Key button on the toolbar (or right-click → Primary Key).
Save the table.

Q5. Steps to sort the table in descending order of primary key

Open the table in Datasheet View.
Click on the primary key column heading.
Click the Sort Descending button on the toolbar.

Q6. Use of navigation box with respect to tables in a database

The navigation box lets you move between records in a table, showing the current record number, total records, and options to go to the first, previous, next, last record, or add a new record.

CHAPTER-10(UNIT-3)

1. Give any two advantages of relating a table in a database.

Advantages of creating Relationship:

A relationship can help prevent data redundancy.

Creating relationships between tables restricts the user from entering invalid data in the referenced fields.

Any updation in the master table is automatically reflected in the transaction tables

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

Redundancy and inconsistency are controlled by setting relationships between tables, which ensures that duplicate records are avoided and a single value does not vary across tables.
Once the relationship is set, adding or updating a record in one table reflects the changes in all related tables.

Example :
If the Student_Details table stores student information, and the Student_Result table only references the student using Admission_No, then updating the details in Student_Details automatically reflects in Student_Result, preventing inconsistency.

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

Referential Integrity:

Referential integrity ensures that a foreign key value in one table must either match a primary key value in another table or be NULL. This guarantees that the relationships between tables are valid and that no invalid references exist.

Why Referential Integrity Is Important
Data Consistency: Ensures that data across related tables remains synchronized and consistent.
Avoid Orphan Records: Prevents records in a child table from referencing non-existent rows in a parent table.
Error Prevention: Reduces the chances of logical errors, such as deleting a parent record without updating related child records.

DBMS maintains referential integrity in a datbase.

4. Differentiate between one-to-one relationship and one-to-many relationship. Give suitable examples.

Difference:

One-to-One (1:1): One record in the master table corresponds to exactly one in the transaction table.
- Example: A particular Admission_No in Student_Details appears exactly once in Student_Result.
One-to-Many (1:N): One record in the master table corresponds to multiple records in the transaction table.
- Example: One teacher can teach multiple students; or a teacher may handle multiple classes.

5. Explain many-to-many relationship with an example.

In a many-to-many relationship, multiple records in the master table relate to multiple records in the transaction table—and vice versa.
Example :
A teacher in a school may have multiple responsibilities—such as class teacher, activity in-charge, or examination in-charge—and for each responsibility, the teacher may be linked with many students.

Extra Questions

Q-1 What are the three types of relationship?

Three types of relationships can be set up between two tables in a relational database.

These are One-to-One, One-to-Many and Many-to-Many.

• In One-to-One type of relationship, one specific record of a master table has one and only one corresponding record in the transaction table.

• In One-to-Many type of relationship, one specific record of the master table has more than one corresponding records in the related transaction table.

• In Many-to-Many type of relationship, there are multiple records in the master table that correspond to multiple records in the transaction table

Q-2 Name the four options provided by LibreOffice Base to maintain referential integrity.

LibreOffice Base gives us following four options to choose from to maintain referential integrity in such cases.

• No action – This is the default option. This option states that a user should not be allowed to update or delete any record in the master table if any related record exists in the transaction table.

• 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.

• Set NULL – This option assigns NULL value to all the related fields if the master record is deleted or updated.

• Set default – This option assigns any fixed default value to all the related fields if the master record is deleted or updated.

CHAPTER-11(IT-402)

Q1) Define a query? What is the need of creating a query in a database?

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.

Q2) Rearrange the steps given below so as to create a query using a wizard.
Correct order:

Give table name
Select the fields
Set the sorting order
Set the criterion
Give Alias
Overview (last step)

Q3) What all information is seen in the overview (last step) of the Query wizard?

The overview displays the complete structure of the query, including the fields selected, the tables used, the sorting order, the criteria applied, and aliases if any

Q4) What is the use of Alias row in the Design grid of the Query Design window?

The Alias row is used to give an alternate name to any field heading in the result of a query

Q5) Name any four mathematical functions that can be applied to numerical data in a query.

Definition: Mathematical functions are used in queries to perform calculations on numerical data. They summarize or compute values such as totals, averages, highest or lowest values.
Examples:
- SUM – to add values
- AVG – to calculate average
- MAX – to find maximum value
- MIN – to find minimum value

Q6) Name the three ways of creating a query in LibreOffice Base.

Using Query Wizard
In Design View
In SQL View

CHAPTER-12(IT-CLASS 10)

Q1) Give one difference between a form and a report.
A form is an object of the database that has a user-friendly interface where data can be entered and seen in an attractive and easy-to-read format.
A report is used to present the retrieved data in an attractive and customized manner .

Q2) What is a field control with respect to forms?
A form contains field controls arranged in a presentable and user-friendly manner.

Each field control consists of a label and the field value text box.

A label specifies the data that should be entered, and the text box is linked to the respective field in the table .

Q3) Which tool on the Forms Record toolbar is used to insert text on the form?
The Label tool on the Forms Controls toolbar is used to insert text (titles, headings, subheadings) on the form .

Q4) Name the two ways to create a form in LibreOffice Base.
The two ways to create a form are:

Using a wizard
Using the Design View

Q5) What is the difference between a static and a dynamic report?

A Dynamic report changes automatically as the field values in the base table or query change.
A Static report does not change automatically with updates in the base table or query .

Q6) Write the function of Forms Controls toolbar and Records toolbar.

Forms Controls toolbar contains various tools to add or edit controls on the form .
Records toolbar contains navigation control buttons to traverse, add, save, or delete records in the form .