Contents
DBMS: MS ACCESS
Data
The raw facts and figure which are uninterpreted and isolated and doesn’t give any particular sense are known as data. Data are the real-world entity such as student name, ward no., age, etc. For eg. Ram,12
Information
The processed form of data is known as information. They are the meaningful result obtained after processing. For eg., Ram is a boy who is 12 years old.
Database
The database is the systematic and scientific collection of data in which data are kept in the form of rows and columns i.s. database table. Using the database, data can be retrieved quickly whenever required. For eg. telephone directory, dictionary and marks ledger, etc.
Limitations of traditional record-keeping system. (Flat filing system)
- Repetition of data (data redundancy)
- Slower access of data
- Doesn’t allow data sharing
- Poor data security
- Data inconsistency.
Since data are stored in the tabular form we call that table a database table.
Note: The column of a database table is known as a field. Whereas the row of the database table is known as a record.
DBMS
DBMS stands for a database management system which is the collection of software that is used to manage databases i.e. storing, manipulating, and retrieving data systematically and scientifically. It is software used to store, process, and access data and information whenever required. For eg. MS ACCESS, Oracle, FoxPro, SQL Server (MY SQL), dBase, etc.
Advantages of DBMS
- It helps in faster access to data.
- It helps to reduce data redundancy.
- It helps to provide security and privacy to data.
- It helps to provide security and privacy to data.
- It helps in data sharing
- Easy to modify the data.
Disadvantages of DBMS
- Expensive to install and manage the database.
- Technical manpower is required.
- Periodic backup is required.
Difference between database and DBMS with examples.
Database | DBMS |
It is less secure. | It is highly secure. |
It is less reliable. | It is more reliable. |
It requires low manpower. | It requires skilled manpower. |
It is the collection of data. | It manipulates and edits data in the database. |
eg. telephone directory, dictionary. | eg. MY SQL, Oracle, FoxPro. |
Objects/ Elements of DBMS
- Table
- Form
- Query
- Report
i. Table
The primary building block of DBMS where data are organized in tabular form i.e. rows and columns. The column of the database table is called the field. Whereas rows are called records.
Importance of table
- It helps to store data in an organized group.
- Easy to sort distinct records.
- The fields of the table help to store different types of data on a different topic.
ii. Form
The element object of the database allows users to enter new data and edit the existing one through a user-friendly interface is called form.
Importance of form
- It provides a user interface through which users can enter data.
- It helps to modify records.
iii. Query
The important object of a database that is used to retrieve/ access and interpret the information according to the user requirement is called a query.
Importance of query
- It helps in faster access to data.
- It helps to display information as per the user’s condition.
- It helps in sorting and filtering data.
Types of query
There are two types of queries. They are:
- Select Query
used to retrieve data from one or more tables depending upon the condition. - Action query
Used to make changes to records available in the database. They are of four types. They are:a. Update Query
Used to make global changes to several records in different tables.b. Append query
Used to add more records in existing one or more tables.c. Delete query
Used to delete one or more records from one or more tables.d. Make-table query
Creates a new table from all or part of the data in one or more tables.
iv. Report
The object of a database that is used to generate results after processing data in a database is called a report. It is an effective way of displaying data either in softcopy or hardcopy i.e. formatted output.
Importance of Report
- It helps to generate a summary after processing data.
- It can be used to generate invoices, bills, statements, and labels.
- It helps to generate results in an attractive and effective way.
Data types
The characteristics of a field that specifies what kind of data can be stored in the given field are called data types.
The following are the data types used in MS-Access
Datatypes | Purpose/ use | Size |
Text | Alphanumeric characters | 0-255 character/ 256 characters |
Memo | Alphanumeric characters | 0-65535 character/ 65536 characters. |
Number | Numeric value | Integer 2 bytes. |
Date/Time | Date and time | 8 bytes |
Currency | Currency data | 8 bytes |
Autonumber | A sequential unique serial increased | 4 bytes |
Yes/ No | Logical value | 1 bit |
OLE | Picture/ audio/ documents | 1 GB |
Hyperlink | link with several application & websites | 2048 characters |
look wizard | create a field that allows selecting a value | 4 bytes |
Field properties
- Field size
The maximum number of characters that a particular field can hold is called field size. - Format
It allows users to display data in different ways, from actually it is stored. - Input Mask
It is used to specify the format in which data must be entered. - Caption
It is used to display an alternate name or descriptive to make the field name more explanatory. - Decimal places
It is used to place the number of decimal in our number or currency field. - Default value
A default value is one that is displayed automatically for a field when we add a new record to the table. - Validation Rule
These properties allow users to enable limit values that can be accepted into a field. - Validation text
It is the error message which is displayed when the validation rule is not followed. - Required
This option is used to specify whether the data must be entered in a field or not. - Allow zero-length.
This option is used only for text, memo, and hyperlink datatype. - Indexed
This is used to speed up the searching and sorting of records in a table.
Sorting
It is a process of arranging records either in ascending or descending order so that records can be kept systematically ad sequentially.
Primary key
It is the unique key that uniquely identifies the record from the database table. This key can’t be duplicated or kept empty. Similarly, the field in which primary keys are written is known as the primary key field.
Importance of primary key
- It helps to uniquely identify the record.
- It helps in faster searching of records.
DBA
It stands for Database Administrator. DBA is a person who controls and monitors the overall operation of our database system.
Roles and responsibilities of DBA
- S/he ensures data security and privacy.
- S/he involves in periodic backup and recovery.
- S/he helps in necessary updates of the existing system
- S/he facilitates database sharing if necessary.