DBMS
Data and information
Data: Data
are the raw facts and figure with are isolated and uninterpreted generally
represented by letter and numbers. Data undergoes processing. E.g. 32, John, 12
Information: The
collection of meaningful result obtained after processing raw data. They are
generated after processing. E.g. John is 32 years old and he reads in class 12.
Database: The
systematic and scientific collection of data that can access quickly whenever
required is called database. It is the organized way of collecting and keeping
data and information. E.g. Dictionary, telephone directory, marks ledger,
attendance register.
DBMS and its advantages
DBMS: DBMS
stands for Database Management System which is the collection of software that
is used to manage database i.e. storing, manipulating and retrieving data
systematically and scientifically. It is a software used to store, process and
access data and information whenever required. Eg, MS-Access, MY SQL, Oracle,
dBase, Foxpro
Advantages of DBMS.
· It
helps in faster access of data.
· It
helps to reduce duplication of data.
· It
helps to provide security and privacy to data.
· It
helps in data sharing.
· Easy
to modify the data
Differentiate between database and DBMS with examples.
[V.imp]
|
Database |
DBMS |
|
It is collection of related data. |
It is a software to maintain database. |
|
It is not secured. |
It is highly secured. |
|
Data sharing is impossible. |
Data can be easily share in a network |
|
Every DBMS has database. |
Every database doesn't have DBMS. |
|
E.g, Dictionary, telephone directory |
E.g, MY SQL, Oracle. |
MS-Access:
It is a DBMS developed by Microsoft Corporation for
organizing data in the form of tables. It allows user to add, edit, delete,
sort, modify, share and print records.
Feature of MS-Access.
· Easy
to store data and information.
· Easy
to enter, edit, delete and display data.
Objects / Element of DBMS
A) Table: The
primary building block of DBMS where data are organized in the tabular form i.e
rows and columns. The columns of database table are called field whereas, rows
are called record. Importance of table.
· Helps
to store data in organized group.
· Easy
to sort distinct records.
Fields of table helps to store different types of datas
on different topic.
B) Form: The
element/object of database which allows user to enter new datas and edit the
existing one easily through user friendly interface. Importance of form.
· It
provide user interface through which user can enter data.
· It
helps to modify records.
C) Query: The
importance object of database which is used to retrieve/access and interpret
the information according the user requirement. Importance of query.
· It
helps in faster access of data.
· It
helps to display information as per the user’s condition.
· It
helps in sorting and filtering of data.
Types of Query
1. Select query:
Used to retrieve data from one or more table depending upon the condition.
2. Action Query:
Used to make changes to records available in database. Types are
3. Update query:
Used to make global (All at once) changes to several records in different table.
4. Append query:
Used to add more records in existing one or more tables.
5. Delete query:
Used to delete one or more record from one or more tables.
6. Make-table
query: Create new table link one or more available tables in database/
D) Report: The
object of database which is used to generate result after processing data in a
database. It is the effective way of displaying data either in soft copy
(monitor) format or hard copy (printed) format. Important of report.
· It
helps to generate summary after processing data.
· It
can be used to generate invoice, bills, statement and labels.
· It
helps to generate result in attractive and effective way.
Data types: The
characteristics of field that specifies what kind of data can be stored in the
given field is called data types.
Following are the data types used in MS-Access.
|
S.N |
Data Type |
Purpose/use |
Size |
|
1. |
Text |
Alphanumeric characters |
0-255 characters / 256 characters |
|
2. |
Memo |
Alphanumeric characters |
0-65,535 characters / 65,536 characters |
|
3. |
Number |
Numeric Values |
Integer (2 bytes) Long integer (4 bytes) |
|
4. |
Date/Time |
Date and time data |
8 bytes |
|
5. |
Currency |
Currency data |
8 bytes |
|
6. |
Auto-number |
A sequential unique serial number automatically
increased by 1 |
4 bytes |
|
7. |
Yes/No |
Logical Values |
1 bit |
|
8. |
OLE object (Object Link Embedded) |
Pictures, audio, video, graphics, documents |
1GB |
|
9. |
Hyperlink |
Link with several applications and websites |
2048 characters |
|
10. |
Look Wizard |
Create a field that allows to select a value. |
4 bytes |
Field properties
Field size: It
is used to set the maximum size for the data stored in a given field. For eg,
maximum characters (size) store in text field is 256 characters. Default field
size is 50.
Format: It is
used to display formatted output that means data can be display in different
formats and layouts. For eg, < (Display content in lower case), >
(Display content in upper case).
Input mask: It
is used to specify the format in which data can be entered.
Caption: It
is used to display alternate name for the field to make it more descriptive. It
can contain 2048 characters.
Default value: It
is the value or information that is displayed automatically when we add a new
record in table for a field.
Validation rule: It
enables user to limit values that can be accepted by particular field. It can
contain 2048 characters.
Validation text: The
message that is displayed when validation rule is violated or not followed.
Required: It
is used to specify whether the data must be enter or not in the particular
field.
Indexed: It
is used to speed up the searching and sorting of record from the database table.
Some important terms used in DBMS
1) Extension of MS-Access database file is .MDB
2) Primary key (v.imp): The key or field that is used to
uniquely identified records from the database table is called primary key. It
must be unique and cannot be kept empty (null).
Importance of primary key.
· It
uniquely identifies records.
· It
helps in faster access of data.
· It
helps to prevent duplication of data.
· It
avoids empty (null) values.
· It
is used to establish table relationship.
3) Table relationship: The logical link between two or
more than two tables of database using unique key fields is called table
relationship. Types are: one to one, one to many, many to many.
4) Sorting: Arranging data in a particular order of field
i.e. ascending or descending is called sorting. Helps in faster access of data.
5) Filtering: The process of making selection of record
depending upon the supplied criteria is called filtering. Helps in faster
access of data.
6) DBA: DBA stands for Database Administrator is a person
or specialist who is responsible to manages and monitor overall resources and
operation (such as analyzing, planning, operating, implementing and securing)
associated with DBMS.
Responsibilities of DBMS.
· Planning
database activities.
· Securing
database and system.
· Implementing
modern tools and technology..
· Maintaining
database.
· Providing
user safety and privacy.
Pg 133 table- Data type table
1.
What is a
data type in MS Access?
→ It defines the kind of data that can be stored in a field.
2.
Which data
type is used to store numbers in MS Access?
→ Number
3.
What data
type is used to store long text in MS Access?
→ Long Text
4.
Which data
type stores date and time values?
→ Date/Time
5.
Which data
type in MS Access is used to store currency
values?
→ Currency
6.
Which data
type is used to store only Yes or No values?
→ Yes/No
7.
What is
the default data type in MS Access?
→ Short Text
8.
Which data
type allows hyperlinks to be stored?
→ Hyperlink
9.
Which data
type can store attachments like files and
images?
→ Attachment
10.
Name a
data type that can store values like 3.14 or 10.5.
→ Number (with field size set to "Double" or "Single")
11.
Which data
type in MS Access is used to store
characters or alphabets?
→ Short Text
12.
Which data
type stores values automatically incremented
by Access?
→ AutoNumber
13.
What is
the maximum character limit of Short Text
data type in MS Access?
→ 255 characters
14.
What is
the main use of the AutoNumber data type?
→ To generate unique values automatically for each
record.
15.
Which data
type is suitable for storing essays or long
paragraphs?
→ Long Text
16.
Which data
type can be used to display or store a photo
in MS Access?
→ Attachment or OLE Object
17.
What is
the use of the OLE Object data type in MS
Access?
→ To store objects like images, Word documents,
or
Excel files.
18.
Which data
type is best for storing website URLs?
→ Hyperlink
19.
Which data
type would you choose to store true/false
values?
→ Yes/No
20.
Can a
field with the Number data type store letters?
→ No
21.
Which data
type allows you to store both date and time
together?
→ Date/Time
22.
Name a
data type that helps in logical (Boolean) decisions.
→ Yes/No
23.
Which data
type is suitable to store salary data?
→ Currency
24.
Which
field type cannot contain duplicate values if set as
Primary Key?
→ AutoNumber (commonly used with Primary Key)
25.
Can you
apply calculations on fields with Number data
type?
→ Yes
Data types used in MS-Access.
|
S.N |
Data
Type |
Purpose/use |
Size |
|
1. |
Text |
Alphanumeric characters |
0-255 characters / 256 characters |
|
2. |
Memo |
Alphanumeric characters |
0-65,535 characters / 65,536
characters |
|
3. |
Number |
Numeric Values |
Integer (2 bytes) Long integer (4
bytes) |
|
4. |
Date/Time |
Date and time data |
8 bytes |
|
5. |
Currency |
Currency data |
8 bytes |
|
6. |
Auto-number |
A sequential unique serial number
automatically increased by 1 |
4 bytes |
|
7. |
Yes/No |
Logical Values |
1 bit |
|
8. |
OLE object (Object Link Embedded) |
Pictures, audio, video, graphics,
documents |
1GB |
|
9. |
Hyperlink |
Link with several applications and
websites |
2048 characters |
|
10. |
Look Wizard |
Create a field that allows to select a
value. |
4 bytes |
1.
Which data type is used to store numeric characters or special symbols in
MS-Access?
Answer: Text (Short Text)
2. Which data type is suitable to store
photographs of students in MS-Access?
Answer: OLE Object
3. What is the storage size of Text data
type in MS-Access?
Answer:0- 255 characters
4. What is the storage size of Long Text
(Memo) data type?
Answer: 65,536 characters
5. Which data type is used to store
Yes/No values?
Answer: Yes/No
6. What is the storage size of Yes/No
field?
Answer: 1 bit
7. Which data type automatically
generates a unique number?
Answer: AutoNumber
8. Which data type is used to store date
and time?
Answer: Date/Time
9. Which data type is used to store
currency values?
Answer: Currency
10. Which data type is used to store numbers
used for calculation?
Answer: Number
11. Which data type is used to store web
addresses?
Answer: Hyperlink
12. Which data type is used to store
large text like remarks or description?
Answer: Long Text (Memo)
13. Which data type is suitable to store
student phone numbers?
Answer: Text
14. Which data type is used to store
pictures, audio, or video files?
Answer: OLE Object
15. Which data type occupies the least
storage space?
Answer: Yes/No
16. Which data type is suitable to store
symbols along with numbers like +977 -98413467..?
Answer: Text
17.Which data type is best for storing
remarks of students?
Answer: Long Text (Memo)
18.Which data type is used to store
True/False or On/Off values?
Answer: Yes/No
19.Which data type can store both letters and numbers?
Answer: Text
21. Which data type is suitable for
mathematical calculation?
Answer: Text
22. Which data type is suitable for
storing student ID that must be unique automatically?
Answer: AutoNumber
23. Which data type should be used to
store date of birth?
Answer: Date/Time
24.Which data type can store up to 255
characters only?
Answer: Text (Short Text)
25. Which data type is used to store
long descriptions exceeding 255 characters?
Answer: Long Text (Memo)
26. Which data type is best for storing
prices with decimal accuracy?
Answer: Currency
27. Which data type is suitable to store
website URLs in a database?
Answer: Hyperlink
28. Which data type is used to store files such as
images or documents?
Answer: OLE
29.Which
data type should be used to store house numbers like 12A or B-15?
Answer: Text
30.Which
data type is suitable to store gender (Male/Female) in MS-Access?
Answer: Text / Yes-No
Long answer question.
1.
What is a database? Give some examples of database.
A
database is an organized collection of related data that is stored in a
computer so that it can be easily accessed, managed, and updated. Databases
help users to retrieve, sort, and manipulate data efficiently.
Examples:
·
Student database: Stores information
about students like name, roll number, marks.
·
Library database: Stores information
about books, authors, issue dates.
·
Employee database: Stores details
like employee ID, salary, and department.
·
Hospital database: Stores patient
records, appointments, and treatments.
2.
Differentiate between data and information.
Data: Data are the raw facts and
figure with are isolated and uninterrupted generally represented by letter and
numbers. Data undergoes processing. E.g. 32, John, 12
Information: The collection of
meaningful result obtained after processing raw data. They are generated after
processing. E.g. John is 32 years old and he reads in class 12.
3.
What is DBMS? Name any four DBMS software.
DBMS
(Database Management System) is a software system that allows users to create,
store, manage, and retrieve data from a database efficiently. It helps in
handling large amounts of data, ensures data security, prevents duplication,
and maintains relationships between data.
Examples
of DBMS software are MS-Access, Oracle,
MySQL, SQL Server
4.
What are the components of a database?
The
main components of a database are:
1.
Tables: Store data in rows (records) and columns (fields). Example: Student
table with fields like Name, Roll No, Marks.
2.
Queries: Used to retrieve specific information from tables. Example: Select
students with marks > 80.
3.
Forms: Provide a user-friendly interface for entering or viewing data. Example:
Student admission form.
4.
Reports: Display data in a structured format for printing. Example: Mark sheet
report.
5.
List any four features of MS-Access.
1.
User-friendly interface: Easy to navigate and manage data without programming.
2.
Data storage: Can store large amounts of structured data efficiently.
3.
Queries: Allows filtering, searching, and retrieving required information
quickly.
4.
Forms and Reports: Simplifies data entry (forms) and presents data neatly for
printing (reports).
5.
Data integrity: Supports primary key, foreign key, and validation rules to
maintain accuracy.
6.
What is a database object? List any four database objects.
Database
objects are components or tools in a database that help to store, manage, and
display data.
Examples
of database objects:
1.
Table: Stores data in rows and columns.
2.
Query: Retrieves specific data from tables.
3.
Form: Provides an interface for data entry.
4.
Report: Displays data in a printable format.
7.
What does data type mean? Name any four data types in MS-Access.
A
data type defines the kind of data a field can store in a table. It ensures
that only the correct type of data is entered.
Examples
of data types:
Text: Stores letters, numbers, or symbols (up
to 255 characters).
Number: Stores numeric values for
calculations.
Date/Time: Stores dates and times.
Yes/No: Stores logical values (True/False).
8.
What is a primary key? Why is it important to specify?
A
primary key is a field in a table that uniquely identifies each record.
Importance:
·
Prevents duplicate records.
·
Ensures data accuracy.
·
Helps in creating relationships
between tables.
·
Required for database normalization
and indexing.
Example:
Roll Number in a student table.
9.
List any four advantages of primary key.
1.
Ensures uniqueness of each record.
2.
Prevents duplicate data entry.
3.
Helps in establishing relationships between tables.
4.
Improves data retrieval speed through indexing.
10.
What are field properties? Name any four of them.
Field
properties are characteristics of a field that control how data is entered,
stored, and displayed in a table.
Examples:
Field Size: Maximum number of characters
allowed.
Default Value: Value automatically assigned if
left blank.
Validation Rule: Restricts the type of data
entered.
Required: Determines if the field must have a
value.
11.
Define indexing. Mention its importance.
Indexing
is a technique used to speed up searching and retrieval of data from a
database.
Importance:
·
Makes searching faster.
·
Helps in sorting data quickly.
·
Improves database performance.
Example:
Indexing on Roll Number field in a student table.
12.
Which view is used to modify a table in MS-Access?
The
Design View is used to modify the structure of a table.
·
You can add, delete, or modify
fields.
·
Set data types, field size, and
other properties.
·
Define primary keys and indexing.
13.
Explain data sorting. List any two advantages of using it.
Data
sorting is the process of arranging data in a specific order, either ascending
(A-Z or 0-9) or descending (Z-A or 9-0).
Advantages:
1.
Makes it easier to analyze and read data.
2.
Helps in finding specific information quickly.
Example:
Sorting student marks in descending order to find the topper.
14.
What is a query? List the different types of query.
A
query is a tool used to search, filter, and display specific data from one or
more tables.
Types
of queries in MS-Access:
1.
Select Query: Retrieves and displays data without changing it.
2.
Action Query: Modifies data (Insert, Update, Delete, Make Table).
3.
Parameter Query: Asks the user to provide a value before executing.
4.
Crosstab Query: Summarizes data in a table format with rows and columns.
15.
What is the importance of query in a database?
·
Retrieves only the required
information from large data.
·
Filters and sorts data for analysis.
·
Helps generate reports and forms
efficiently.
·
Saves time by automating searches.
16.
What is a form? Mention its uses.
A
form is a database object that provides a user-friendly interface to enter,
modify, and view data.
Uses:
·
Simplifies data entry
·
Reduces errors in entering data
·
Makes it easier to navigate through
records
·
Can include buttons, drop-down
lists, and checkboxes for convenience
17.
Differentiate between Select query and Action query.
|
Select
Query |
Action
Query |
|
Select query is simply used to select
and display the relevant data from the database. |
An action query is a query that makes
changes to or removes many records in just one operation. |
|
It does not make change to database. |
It makes change to database. |
|
A query does not save data; rather, it
displays information from tables. |
Action query allows previewing the
query results before performing it, which is a nice feature |
|
A select query is a database item that
presents information in Datasheet view. |
Multiple records can be added,
updated, or deleted simultaneously using action queries. |
18.
Explain the process to create a form using wizard.
Steps
to create a form using wizard:
1.
Open MS-Access and select the table or query.
2.
Click Create → Form Wizard.
3.
Select fields to include in the form.
4.
Choose a layout (Columnar, Tabular, Datasheet, etc.).
5.
Click Finish to generate the form.
19.
Why is a form required in MS-Access?
·
Provides a simple and user-friendly
interface for entering and viewing data.
·
Reduces data entry errors.
·
Can include navigation buttons for
moving through records.
·
Makes the database interactive for
users who are not familiar with tables.
20.
What is a report?
A
report is a database object used to display data in a structured and printable
format.
·
Summarizes and organizes data
neatly.
·
Can include headers, footers,
totals, and calculated fields.
·
Used for presenting information to
management or printing official documents.
21.
Mention the importance of creating reports in a database.
·
Helps in analyzing and interpreting
data.
·
Useful for printing information for
official purposes.
·
Summarizes large amounts of data in
a readable format.
·
Can include charts, calculations,
and totals.
22.
List any two major differences between forms and reports.
|
Form |
Report |
|
A
form is a database object used to enter, edit, or view data in a
user-friendly way. |
A
report is a database object used to display and present data in a formatted
and printable form. |
|
Forms
are interactive, allowing the user to type, select, or update data. |
Reports
are read-only, and the user cannot edit data through them. |
|
Forms
are mainly used for data entry and data management. |
Reports
are mainly used for data presentation and printing. |
|
Forms
can include buttons, drop-down lists, checkboxes, and navigation
controls. |
Reports
can include headers, footers, totals, and charts for summarizing data. |
|
Example:
A student admission form to enter student details. |
|
Example: A mark sheet report showing students’ grades. |
23.
Define relationship
A
relationship is a connection between two tables based on a common field.
Example:
Student table (StudentID) → Marks table (StudentID)
Importance:
Maintains data consistency and avoids duplication.
24.
What is the Validation Rule in MS-Access?
Validation
Rule is a condition that restricts the type of data entered in a field.
Example:
Marks >= 0 AND Marks <= 100
25.
What is the Validation Text?
Validation
Text is the message displayed when a user enters invalid data according to the
validation rule.
Example:
“Marks should be between 0 and 100.”
26.
Define MS-Access
MS-Access
is a Database Management System (DBMS) developed by Microsoft that allows users
to create, store, manage, and retrieve data using tables, queries, forms, and
reports.
27.
Define field
A
field is a single column in a table that stores a specific type of data.
Example:
Name, Roll Number, Date of Birth.
28.
Define foreign key
A
foreign key is a field in one table that refers to the primary key of another
table to establish a relationship between the two tables.
Example:
In Marks table, StudentID is a foreign key that refers to Student table’s
primary key StudentID.
Importance:
Maintains referential integrity between tables.
29.
Objects /
Element of DBMS
A) Table: The primary building
block of DBMS where data are organized in the tabular form i.e rows and
columns. The columns of database table are called field whereas, rows are
called record. Importance of table.
· Helps
to store data in organized group.
· Easy
to sort distinct records.
Fields of table helps to store different
types of datas on different topic.
B) Form: The element/object of
database which allows user to enter new datas and edit the existing one easily
through user friendly interface. Importance of form.
· It
provide user interface through which user can enter data.
· It
helps to modify records.
C) Query: The importance object of
database which is used to retrieve/access and interpret the information
according the user requirement. Importance of query.
· It
helps in faster access of data.
· It
helps to display information as per the user’s condition.
· It
helps in sorting and filtering of data.
Types of Query
1. Select
query: Used to retrieve data from one or more table depending upon the
condition.
2. Action
Query: Used to make changes to records available in database. Types are
3. Update
query: Used to make global (All at once) changes to several records in
different table.
4. Append
query: Used to add more records in existing one or more tables.
5. Delete
query: Used to delete one or more record from one or more tables.
6. Make-table
query: Create new table link one or more available tables in database/
D) Report: The object of database
which is used to generate result after processing data in a database. It is the
effective way of displaying data either in soft copy (monitor) format or hard
copy (printed) format. Important of report.
· It
helps to generate summary after processing data.
· It
can be used to generate invoice, bills, statement and labels.
· It
helps to generate result in attractive and effective way.
30. List disadvantages of primary key.
1. Cannot have duplicate values: Once a
primary key is set, no two records can have the same value, which may cause
problems if duplicates are needed.
2. Cannot contain null (empty) values:
Every record must have a value in the primary key field, so it cannot be left
blank.
3. Cannot be changed easily: Changing a
primary key value may affect relationships with other tables, making updates
complicated.