2.1 Database Management System Class 10

 

2.1  Database Management System

Data

Data refers to the collection of raw facts and figures that may or mayn’t give any sense. Eg: Ram, 16, years are data.

Information

Information is the Processed or refined data that gives a complete sense. Information is an organized collection of related data, which gives a complete sense. “Ram is a student. He is 20 years old.”, is information that gives a complete sense.

Data Processing

The process of getting useful information from data by processing data with the help of database software is called data processing.

Database

Database is simply organised collection of related data arranged in a specific order so that it can be easily access, managed or updated. E.g. Flight schedule Dictionary, Marks Ledger, Telephone Directory etc.

Advantage of database

Ø  It allows sharing large volume of data

Ø  It allows searching of data quickly

Ø  It reduces data duplication

Ø  It allows advance filtering of data

 DBMS (Database Management System)

DBMS is a software which is used to store the data, process them and provide the useful information to the user. It involves in creating, modifying, deleting and adding data in database. Eg: Dbase, Fox Pro, Ms- Access, Oracle, Sybase, FoxBASE, SQL

 DBMS is a software which helps to extract, view and manipulate data in an organized way. In DBMS, data can be accessed, managed and updated easily. E.g.: MS-Access, Oracle, Fox pro, Dbase etc.

Advantage of DBMS.

ž  Reduce data redundancy

ž  Control data consistency: (anychange made in one file automatically done in all field)

ž  Facilitates sharing of data

ž  Ensure data security

ž  Maintain data integrity: (make sure that database accept only the valid data)

Disadvantage of DBMS

ž  Required H/W and S/W are expensive.

ž  Costly and time-consuming procedures

ž  High operating cost

ž  Qualified persons are required to operate.

ž  Difficult to recover backup

Computerized Database

a) It can store large volume of data. It is very fast to find a specific record.
b) Data can be sorted into ascending or descending order on multiple criteria.
c) The database can be kept secure by use of passwords.
d) We can search data very easily.
e) Modification is very easy in comparison of manual database.

Non-Computerized Database

a) It is limited by physical storage available.
b) It can take a while to manually search through all of the records.
c) Difficult to sort data on more than one criteria.
d) The only security would be locking up the records.

Elements of Database

ž  Field: Field is the topic name under which records are stored. It is found in column wise. A column in database is called field. For eg: Name, Age,Gender.

ž  Record: A piece of information under the related fields about any person, place, organization or anything. For eg: Ram , 15, male.

ž  Table: A table is collection of records or group  of records. It contains no. of rows and columns.

ž  Tuple: A record row in database is called Tuple

Types of DBMS

•         RDBMS (Relational Database Management system)

•         ODBMS (Object-oriented Database Management system)

•         DDBMS (Distributed Database Management System)

•         HDBMS (Hierarchical  Database Management System)

RDBMS (Relational Database Management System)

RDBMS is a database management system that is based on the relation model in which data is stored in the form of tables and the relationship among the data is also stored in the form of tables.
E.g.: SQL, MS-Access, Oracle, 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 the form of table.

Features of MS-Access

a) IT is simplest and most flexible DBMS solution today.

b) it is easy to search and find the data.
c) Forms are used for viewing and editing the information.
d) Reports are used for summarizing and printing the data.

e) It provides the flexible ways to add, edit, delete and display the related data.

f) Queries help to view, change and analyses the data indifferent ways

Objects of MS-Access

1.  Table
2.  Form
3.  Query
4.  Report
5.  Pages
6.  Macro
7.  Module

a) Table

A table is the primary object of Ms-Access database. It is used to store data. A database may contain one or more table. It is an object of MS-Access that stores large volume of data in the form of rows and columns. The different ways to create table are:
i) Design view
ii) Using wizard
iii) Entering data

Importance of table

a) it is used to store the data.

b) Different properties of a field can be set in a table.
c) It provides options for primary key which helps to make data consistent.

Record

Record is complete set of information. Record is a collection of multiple related fields in a row which gives complete information about a person or thing. E.g.: Khem Pokhara 1234567890

Field

Field is a small unit of information. A column in a table is called field, and it contains a specific piece of information within a record. E.g.: Name, Address, Telephone

b)     Query:

 A query is an object of MS-access database which is used to retrieve and display the records from a table or multiple tables based on specified condition.

Importance of query.

Ø  It is use to retrieve and display the records of a table.

Ø  It allow us to view, change and analyze the data in various ways.

c)      Form:

Form is an object of Ms-Access that provides a quick and easy way to insert and modify records into our database.

Importance of form

Ø  To allow users to perform data entry.

Ø  To edit, update or delete the data.

Ø  To provide a way customize the presentation of data in the database.

d)     Report:

Report is an object of MS-Access database that is used to organize and present the data in an effective and understandable form.

Importance of report

Ø  It is used to organize and summarize  the data in an effective form.

Ø  It present the data for viewing online and printing.

e) Pages: used to enter or view data from website or internet

f) Macros: small program to automate repetitive task.

g) Modules: stores a programming codes.

Primary key

Primary key is a rule which ensures that unique data is entered for a field and the field is not left blank(null) 

Importance of primary key                        

• It doesn’t allow the duplication of data in a field.
• It doesn’t allow the field to be left blank(null).
• It plays a vital role in establishing relationships.

Foreign Key:

When a relationship is created between two tables, the parent table contributes its primary key to the child table, where it is known as foreign key.

• It allows duplicate value as well as null value.

• It is used to establish the relationship between two or more tables.

Composite key

The group of primary keys that consists of two or more attributes is called composite key.

What is data redundancy? How it can be controlled?

Data redundancy means repetition/duplication of data in a database. Data redundancy can be controlled in MS-Access by normalization of database.

Define data type? List any four data types of MS-Access?

 Data type is an attribute for a field that determines what type of data it can contain.

Any four data types of MS-Access are:
i) Text
ii) Number
iii) Memo
iv) Currency

Q) 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 – short text
Address – short text
Salary – Currency
Date of birth – Date/Time

Identify a record, field and value from the following table structure.

Roll	Name	Marks
1	Kopila	87
2	Rabin Rana	56

 Ans: Record =          

  1          Kopila                        87,                  

  2          Rabin Rana                56

Field = Roll,   Name, Marks

Value = 87,     56

What happens when we enter text in a numeric field?

Ans: If we enter text in a numeric field then it displays the errors and numeric fields does  not accept text data.

Data type: The type of data that a field can store is called as data type.

S.N.	Data type	Summary
1.	short text	•        Default data type •        Stores letters, number other characters up to 255 characters. •        Default size is 255
2.	long text	•        Store long text (alphanumeric character) •        Can hold up to 64000
3.	Number Byte Integer Long integer Single precision Double precision	Holds numeric data. •        Occupies 1 byte memory. (0 to 255) •        occupies 2 byte •        occupies 4 bytes •        occupies 4 bytes •        Occupies 8 bytes
4.	Date/Time	•        Stores date and time values. •        Occupies 8 bytes of memory
5.	Currency	•        Used for monetary value. •        Occupies 8 bytes of memory
6.	AutoNumber	•        Stores integer value •        Value increment and decrement automatically •        Occupies 4 bytes of memory
7.	Yes/No	•        Logical data type •        Occupies 1 bit memory •        Display Yes/No, True/false or On/Off.
8.	OLE objects	•        Used to insert pictures, sound, video, word/excel document files and so on. •        Occupies up to  1 GB.
9.	Hyperlink	•        Used to link to the www or email. •        Contain upto 2048 characters
10.	Lookup Wizard	•        Choose a value form another table •        Occupies 4 bytes of memory
11.	Attachment	•        Stores file such as digital photos •        Data type is not available in earlier version of Access.
12.	Calculated field	•        Result of calculation.

 

Field properties

• Field size: Used to set maximum size for data stored in the text. Maximum size 255 and default size is 255
• Format: Allow us to display data in a format different form the way we actually stores data.
• Input mask: This option is used to specify the format in which data must be entered.
• Caption: This option is used to display an alternat name for the field. It contains 2048 characters.
• Decimal place: valid for only currency and number data types. The decimal places numbers vary from 0 to 15 depending upon the field size.
• Default value: is one that is displayed automatically for the field when we adda n ew record to the table
• Validation rule: this option is used to enable us to limit values that can be accepted into a field.
• Validation text: This option contains error message that is to be displayed when we enter a value against the validation rule.    
• Required: This option is used to specify whether the data must be entered in a field or not.
• Allow zero length: This option is only for Text, Memo and hyperlink datatypes.
• Indexed: this option is used to speed up searching and sorting of records in a table.

Relationship

A relationship is an association among several entities (table). Its types are:

i)One to one relationship

ii) One to many relationship

iii) Many to many relationship

Sorting

Sorting is the process of arranging the record in ascending or descending order according to a given field or fields. Sorted data is easier to handle than unsorted data.

Advantages of sorting:

i)It helps to find specific information quickly.

ii)It helps to arrange data in alphabetical order.

Filtering

Filtering is the process of selecting and displaying the required records based on given criteria.

Query

The different types of queries are:

i) Select query

ii) Action query: In action query, we have update query, append query, delete query and make-table query.

1. Select query

A select query is the most common category and is used for extracting specific information from one or more tables in a database. It cannot make changes in tales.

We use select query to group records and perform calculations on field values in the groups such as sum, count, average, minimum and maximum.

2. Action query

Action query is a query which makes changes to many records in just one operation.

Update query: Update query is a type of action query which make global changes to a group of records in one or more tables.

Append query: The use of append query is to add a group of records at the end from one or more tables.

Delete Query: A delete query is an action query that deletes a set of records according to criteria that is specified.

Insert Query: Insert queries can be used to insert records from one table into another table, or from a query into a table. The records inserted will be appended into that table (rather than replace the previous records)

 Advantages of query

a) We can perform calculations and summarize data.

b) Large volume of records can be updated or deleted at a same time.

c) It retrieves and display records from one or more tables

d) It is used to perform mathematical calculation of data.

Form

Methods to create a form are:

i)      Using auto forms

ii)    Using form wizard

iii)   Using design view.

Report

The methods of creating report are:

i)Using design view

ii)Using report wizard

Q)   Why is Report created?

Report is created to print documents according to user's specifications of the summarized information through query or table.

Importance of report

It helps to prepare well formatted output.

It displays the information the way we want to view it.

It presents the information retrieved through queries or tables.

It presents the information in designed layouts by adding necessary titles, setting font color or font size, etc.