SEE COMPUTER SCIENCE 2079 Database Management System (DBMS)
Database
A
database is an organized collection of data that is designed to efficiently
store, manage, and retrieve information.
E.g.
Dictionary, Marks Ledger, Telephone Directory etc.
DBMS (Database Management System)
A
DBMS is a software that manages databases, allowing users to store, access, and
manipulate data in an organized and secure way.
E.g.: MS-Access, Oracle, Fox pro, Dbase etc.
RDBMS (Relational Database Management
System)
RDBMS
is a type of DBMS that uses a relational model to organize and manage data,
allowing users to easily retrieve and manipulate it.
E.g.: MySQL, Oracle, MS Access etc.
Advantages of DBMS
a)
It
controls data redundancy which means duplication of data.
b)
It
allows multiple users to access the same data simultaneously.
c)
Large
volume of data can be stored and updated easily.
d)
It
provides high security of data as well as maintains accurate database.
MS-Access
Microsoft
Access is a relational database management system (RDBMS) that allows users to
store, organize, and manage large amounts of data.
Features of MS-Access
a)
It
provides the flexible ways to add, edit, delete and display the related data.
b)
Queries
help to view, change and analyse the data indifferent ways.
c)
Forms
are used for viewing and editing the information.
d)
Reports
are used for summarizing and printing the data.
Objects of MS-Access
· Tables: Used to
store data in a structured format with columns and rows.
· Queries: Used to
retrieve and manipulate data from one or more tables based on specified
criteria.
· Forms: Used to
create user-friendly interfaces for entering and viewing data in tables.
· Reports: Used to
generate formatted and organized views of data from tables or queries.
Computerized Database vs
Non-Computerized Database
|
Computerized
Database
|
Non-Computerized
Database
|
|
It
can store large volume of data. It is very fast to find a specific record.
|
It
is limited by physical storage available.
|
|
The
database can be kept secure by use of passwords.
|
The
only security would be locking up the records.
|
|
We can
search data very easily.
|
It
can take a while to manually search through all of the records.
|
|
Data
can be sorted into ascending or descending order on multiple criteria.
|
Difficult
to sort data on more than one criteria.
|
Data
· Data refers to
any collection of raw facts, figures, or other information that can be
processed or analyzed.
· Data measured in
bits and bytes.
· Data never
depends on information.
· E.g.: Ram,
student, 20, etc.
Information
· Information is
processed or organized data that has meaning and context for its users.
· Information is
measured in meaningful units like time, quantity, etc.
· Information is
dependent on data
· E.g. “Ram is a student. He is 20 years old.”, is information that
gives a complete sense.
Table.
Table
is a database object that stores a collection of related data organized in rows
and columns, where each row represents a record and each column represents a
field or attribute of that record.
The different ways to create table are:
· Table Design View
· Table Wizard
Importance of table
· Tables store and organize data efficiently, reducing data
redundancy and inconsistencies.
· Tables enable the use of data validation rules to ensure
data accuracy and completeness.
· Tables are essential for creating forms, queries, and
reports that extract useful insights and information from data.
· Tables provide security features to restrict access to
sensitive data and ensure data privacy.
Form
Form
is a database object that provides a graphical user interface for users to
view, enter, and edit data from one or more tables, queries, or other data
sources.
Methods
to create a form are:
Form Design View
· Form Wizard
Importance of form
· Forms provide an easy-to-use interface for data entry and
manipulation, improving data accuracy and completeness.
· Forms can include validation rules to ensure data quality
and prevent errors.
· Forms enable users to customize the look and feel of data
input screens.
· Forms can be linked to other objects, such as tables or
queries, to simplify data management.
Query
Query is a request for information
from a database, used to retrieve and display data based on specified criteria,
such as filtering, sorting, or calculating values.
Methods to create a query are:
Query Design View
Query Wizard
The different types of query are:
i) Select query
ii) Action query
In action query, we have update query, append query, delete query and
make-table query.
Advantages of query
· Queries enable users to retrieve specific data from tables
or other data sources, based on specified criteria.
· Queries allow users to filter, sort, and group data in
meaningful ways.
· Queries enable users to perform calculations and aggregate
functions on data to extract useful insights.
· Queries can be used to create reports and forms that extract
meaningful information from data.
Select query
A select query is a type of query
used to retrieve and display data from one or more tables or queries, based on
specified criteria or selection rules, such as filtering or sorting.
Action
query
An action query is a type of query
used to perform a specific action on a set of records, such as adding,
deleting, updating, or appending records in a table or query based on specified
criteria or selection rules.
Update query
An update query is a type of action
query used to modify or update existing data in a table or query based on
specified criteria, such as changing values in a field or updating multiple
records at once.
Append query
An append query is a type of action
query used to add records from one or more tables or queries to another table,
based on specified criteria or selection rules.
Delete query
A delete
query is a type of action query that removes specific records or all records
from a table.
Make table query
A
make-table query is a type of query used to create a new table based on the
results of a select query.
Report
Report is a formatted presentation
of data from one or more tables or queries, designed for printing or sharing.
The data sources for report are
table and query.
The methods of creating report are:
Methods to create a form are:
Report Design View
Report Wizard
Why
is Report created?
Reports are created in MS Access to
present data from one or more tables in a formatted and organized manner,
allowing users to easily view and analyze the information.
Importance of report
· Reports provide a formatted presentation of data that is
easy to read and understand.
· Reports enable users to summarize, analyze, and visualize
data in meaningful ways.
· Reports can be exported or printed for sharing with others.
· Reports can be based on tables, queries, or other reports,
providing flexibility and ease of use.
Data type
Data
type is an attribute for a field that determines the type of data that can be
stored in that field.
· Text: Used for storing alphanumeric text values such as
names, addresses, and descriptions.
· Memo: Used for storing large blocks of text or lengthy
descriptions.
· Number: Used for storing numeric data such as integers,
decimals, or currency values.
· Auto number: Unique value generated by Access for each new
record. (4 bytes)
· Date/Time: Used for storing dates and times. (8 bytes)
· Yes/No: Used for storing boolean (true/false) values. (1 bit)
· Attachment: Used for storing files, such as documents or
images, within the database.
· Hyperlink: Used for storing URLs or links to other documents
or web pages.
· Lookup: Used for storing a value from a predefined list of
values.
· OLE Object: Used for storing objects from other
applications, such as Excel spreadsheets or Word documents. (1 GB)
What happens when we enter text in a
numeric field?
Ans:
an error message is usually displayed, indicating that the data type does not
match the expected format.
Text
Text
data type is used for storing alphanumeric text values with a maximum size of
255 characters.
The default size of text field is
50.
Memo
Memo data type is used for storing
large blocks of text or lengthy descriptions up to 65,536 characters.
Field properties
· Field Name: The
name of the field, which should be unique within the table.
· Field Size: The
maximum size of the data that can be stored in the field.
· Format: The
format for displaying the data in the field, such as currency, date/time, or
text.
· Input Mask: A
pattern that controls the format of the data as it is entered into the field,
such as a phone number or ZIP code.
· Validation Rule:
A rule that specifies the valid values or ranges of values that can be entered
in the field.
· Validation Text:
A message that appears if a user enters invalid data in the field.
· Default Value:
The default value that is entered into the field if no other value is
specified.
· Indexed:
Specifies whether the field is indexed for faster searching and sorting.
· AutoNumber:
Specifies whether the field automatically generates a unique number for each
record.
· Caption:
Specifies a descriptive label for the field, which can be displayed in forms
and reports instead of the field name. It can contain up to 2048 characters.
Relationship
Relationship
is a connection between two tables that is established using a common field or
fields, such as a primary key and a foreign key. Its types are:
i)One to one relationship
ii)One to many relationship
iii)Many to many relationship
Record
Record
is complete set of information. Record is a complete set of information that
contains data values for all the fields in a table, represented by a row in the
table. E.g.: Ram Chabahil 4468790
Field
Field
is a small unit of information. Field is a column in a table that stores
specific information, such as a name, date, or number. E.g.: Name, Address,
Telephone
Sorting
Sorting
refers to the process of organizing data in a specific order (ascending or
descending) based on one or more fields in a table, query, or report. Sorted data is easier to handle than unsorted
data.
· The advantages
of sorting are:
Sorting helps to organize data and make it easier to find and retrieve specific
information.
· Sorting can save
time and improve efficiency by allowing users to quickly access the data they
need.
Filtering
Filtering
is an option that selects the required records and displays the result.
Primary key
A
primary key is a field or combination of fields in a table that uniquely
identifies each record, and is used to establish relationships between tables
and enforce data integrity.
Uses of Primary key
i) To reduce and control duplication of record in a table.
ii) To set the relationship between tables.
Composite key
The
group of primary key that consists of two or more attributes is called
composite key.
Foreign Key
A
foreign key in MS Access is a field that establishes a relationship between two
tables by referencing the primary key of another table.
Data Redundancy
Data
redundancy is the duplication of data within a database.
Data
redundancy can be controlled by normalizing the database, eliminating duplicate
data and storing it in separate tables, and using foreign keys to establish
relationships between the tables.
While designing table structure which
data types are suitable to store information about teacher’s name, address,
salary and date of birth.
Teacher’s
name - Text
Address – Text
Salary – Currency
Date of birth – Date/Time
Identify a record, field and value from the following table
structure. [SQE 2074]
Roll
Name
Marks
1
Kopila
87
2
Rabin Rana
56
Ans:
Record = 1 Kopila 87, 2 Prabin Rana
56
Field
= Roll, Name, Marks
Value
= 1, 2, Kopila, Prabin Rana, 87, 56
List
the field name from the following table structure.
|
Symbol No.
|
Name
|
Marks
|
|
00100200Q
|
Surya Gurung
|
85
|
|
00100201R
|
Birendra
Sharma
|
91
|
Field
names are : Symbol No., Name and Marks
1. Which data type is used to store date of birth
of an employee in MS Access?
2. Which data type of MS Access is used for data
that can store only one of two possible values?
3. Which data type is also known as logical data
type in MS-Access?
4.
Which
data type consumes the least storage space?
5. Which of the
following data type is appropriate to store gender of person in terms M or F?
6. Which data type of MS-ACCESS is used to store
photo, video?
7. Which data type used for lengthy text and numbers?
8. What is the default extension of MS-Access?
9.
What is the memory space
consumed by a currency data type?
10.
What is the memory space
consumed by auto number data type?
11.
What is the memory space
consumed by date/time data type?
12.
What
is the field size of logical field data type?
13.
What
is the maximum size of text field in MS-Access?
14.
What
is the default size of text data type in MS Access?
15.
What
is the default data type of MS-Access?
16.
Which
data type is appropriate to store salary
17. Which key uniquely identifies each record?
18. Which view is to used to modify a table structure
in Ms-Access?
19. Which object of MS-Access is used to retrieve
date from the table?
20. Which query changes value in main table?
21. Which query is used to view the data stored in
database?
22. Which object of
MS-Access that is used to generate hard copy of records?
23. Which object of
MS-Access that is used to store records
in MS Access?
24. List any four components of database management.
25. Name any four objects of Ms-Access.
26. Name any four data types of Ms Access
27. Write any two examples of database
28. Write any two examples of DBMS.
29. In how many ways we can create a table?
30. List the types of query.
31. What is database?
32. Define DBMS.
33. Define RDBMS.
34. Define MS Access.
35. List any two advantages of DBMS.
36. What are validation text and validation rule
in MS-Access?
37. What is table in MS Access?
38. Why table is called primary object of Ms
-Access?
39. What is query?
40. List any two advantages of query.
41. What is action query?
42. Define form.
43. Write the importance of form.
44. What is report?
45. Mention the uses of report.
46. Why is report created?
47. What is primary key?
48. List any two of its advantages of primary key.
49. What is data redundancy? How can it be reduced
in database?
50. Differentiate between Primary key and foreign
with example.
51. What is data sorting?
52. List any two advantages of sorting.
53. Differentiate between fields and records with
example
1. While designing
table structure which data types are suitable to store information about
teacher’s name, address, salary and date of birth.
2. Identify a record, field and value from the
following table structure.
Roll
Name Marks
1
Kopila
87
2
Rabin Rana 56
Everybody
is a genius. But if you judge a fish by its ability to climb a tree, it will
spend its whole life believing that it is stupid.” ― Albert Einstein
