Database Management System
2.1 Introduction
Data
Data are raw form of any facts, figures or entities which does not give any meaning. For example, Aaradhya, 1000, account, balance etc. are raw data individually does not give any meaning.
A collection
of systematically organized inter-related data is
called a database. The purpose of data base is to store, organize and retrieve data. E.g. Dictionary, Marks Ledger, Telephone Directory, Attendance Register
etc.
The database which is managed in file or ledger without using computer system is called manual database (non- computerized).
Difference
between computerized and non-computerized database
|
Computerized Database |
Non-Computerized Database |
|
It can
store large volume of data. |
It is limited by physical storage
available. |
|
We can search
data very easily. |
It can take a while to manually search
through all of the records. |
DBMS:
Database Management System is software that manages the data stored in a database. This is a collection of software which is used to store data, records, process them and obtain desired information. Since, data are very important to the end users, we must have a good way of managing data.E.g. MS-Access, Oracle, MySQL, Fox Pro etc.
Advantages of DBMS
Ø It reduces data redundancy which means duplication of data.
Ø It allows multiple users to access the same data simultaneously.
Ø Large volume of data can be stored and updated easily.
Ø It provides high security of data as well as maintains accurate database.
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.
MS-Access
MS-Access is a relational database management system developed by Microsoft Corporation which is used to store and manipulates large volume of data in multiple tables.
Features of MS-Access:
Ø It provides the flexible ways to add, edit, delete and display the related data.Ø Queries help to view, change and analyse the data indifferent ways.Ø Forms are used for viewing and editing the information.Ø Reports are used for summarizing and printing the data.
Record (Tuple)
A record is a row in a table which contains information about single items in a database. Record is complete set of information.
A field is a column in a table which contains information about a certain type for all records. Field is a smallest unit of information.
Value
A value is the each specific piece of information in a table.
|
Symbol_No |
Name |
Address |
Class |
|
25612 |
Ram Thapa |
Kathmandu |
10 |
|
25617 |
Shyam
Shrestha |
Dhading |
8 |
|
25635 |
Rita Dhungel |
Pokhara |
10 |
Fields are : Symbol_No, Name, Address and Class
Records are : 25612, Ram Thapa, Kathmandu, 10 ; 25617, Shyam Shrestha, Dhading, 8 ; 25635, Rita Dhungel, Pokhara, 10
Values are: 25612, 25617, 25635, Ram Thapa, Shyam Shrestha, Rita Dhungel, Kathmandu, Dhading, Pokhara, 10, 8, 10.
Data type
Data type is an attribute for a field that determines the type of data that can be stored in that field.
Data types of MS-Access:
|
Data Type |
Description |
Maximum Data / Space |
Default Field Size |
|
Text |
Used
for text or combinations of text and numbers, as well as numbers those
don't require calculations, such as phone numbers |
Up to 255 Characters |
255 |
|
Memo |
Lengthy text or
combinations of text and numbers. |
Up to 64,000 / 65535 Characters |
|
|
Number |
Used for data
to be included in mathematical calculations. |
1,2,4 or 8 Bytes |
Long Integer |
|
Date/Time |
Used for dates
and time data |
8 Bytes |
|
|
Currency |
Used for currency values. |
8 Bytes |
|
|
Auto Number |
Used for unique
sequential (incrementing by 1). |
4 Bytes |
|
|
Yes/No |
Used for data
that can be only one
of two possible values, such
as Yes/No (Boolean values) |
1 Bit |
|
|
OLE Object |
Used for
OLE objects (such as MSWord documents, MS-Excel spread sheets, pictures, sounds) |
Up to about 1 GB |
|
|
Hyperlink |
Used for hyperlinks |
Up to 2048
Characters |
|
|
Attachments |
To attach any supported type of file |
Up to about 2 GB |
|
|
Lookup Wizard |
Used to
create a field that allows you to choose a
value from another table |
Dependent
on the data type of the lookup field |
|
Field Properties
Field properties
are settings or attributes that allow users to control various aspects of data
entry, validation, formatting, and behavior within the database.
|
Field Properties |
Description |
|
Field Size |
Field size is a field
property which is used to set
the maximum size
for data stored in the
field that is set to
the Text or Number data
type. |
|
Caption |
Caption is a field property which gives alternative
name given for any field. The maximum size for this is 2048
characters. |
|
Default Value |
Default Value is a field
property that is displayed automatically for the field
when we add a new record to the table. |
|
Format |
Format is a field property that allows to display
data in a format different from the way
it is actually stored in a
table. |
|
Input Mask |
Input mask is a
field property that controls the value of a record and sets in a specific format. |
|
|
Validation Rule |
Validation Rule
is a field property which
is used to limit
the values that can be entered into
a field. |
|
|
Validation Text |
Validation Text
is a field property which
displays an error message that appears if the data entered is invalid according to the specified validation rule. |
|
|
Required |
Required is a
field property which
is used to specify whether a value is required in a field or not. |
|
|
Indexed |
Indexed is a field
property which speeds
up searching and sorting of records based on a
field. |
Table, Form, Query, Report, Pages, Macros, and Modules
Table
Tables are the primary building block of database which stores and manages large volume of data into rows and column.
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.
Design view Datasheet view Using wizard
Design View – Related with table structure. We can add, edit or delete field and its properties.
Datasheet View – Related with records. We can add, modify, search or delete records.
Form
Form is one of the MS-Access database objects which provides graphical interface to view, modify and add data in a table or multiple linked tables.
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.
Design view Using wizard
Report
Report is one of the MS-Access database objects used to present information in an effective and organized format that is ready for printing.
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.
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.
The data sources for report are table and query
Methods to create report
Design view Using wizard
Query
Query is an object of database that is used to view, retrieve, change and analyze records from a table or multiple linked tables based on specified condition.
Queries can be used to filter data, to perform calculations with data, and to summarize data.
Its types are select query and action query.
Select query is a type of query which is used to select and display the relevant data from the database.
Action query is a query that makes changes to or removes many records in just one operation.
Examples of action query are update query and delete query.
An update query is a type of query that makes entire changes to a record or group of records in one or more tables
A delete query is a type of query that deletes a record or group of records from one or more tables.
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.
A make-table query is a type of query used to create a new table based on the results of a select 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.
Methods to create query
Design view Using wizard
Sorting
The process of arranging all the records in a table either ascending or descending order based on field or fields is known as sorting. 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 the process of viewing required record of a table that matches the specifies criteria.
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.
The primary key does not accept duplicate value for a field and it does not allow a user to leave the field blank or null.
Importance
of primary key
- It sets the relationship between tables.
- It reduces and controls duplication of record in a table
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.
Composite Key
Composite
Key is the group of primary key that
consists of two or more attributes.
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.
Relationship
A relationship refers
to the association or connection between two tables
based on a common field
or fields. Its types are:
- One to one relationship
- One to many relationship
- Many to many relationship
Post a Comment