2.1 Database Management System [SEE Computer Science 2078]

 

2.1 Database Management System

Data [SEE 2073] [SLC 2064] [SLC 2070] [[SLC 2065 S] [SLC 2069 S]

Data can be numbers, letters or symbols representing facts and figures which may or may not give any sense. E.g.: Ram, student, 20, etc.

Information [SLC 2070]

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.

Database [SEE 2074] [SEE 2073] [SEE 2073 U] [SLC 2072] [SEE 2071 S] [SLC 2064] [SLC 2065]

A database is an organized collection of related data and information that can be used for different purpose so that it can easily be accessed, managed, and updated.

E.g. Dictionary, Marks Ledger, Telephone Directory etc.

DBMS (Database Management System) [SEE 2075 U] [SEE 2074 U] [SLC 2066] [SLC 2067]

Database management system (DBMS) is a computerized system that stores data process them and provide information in an organized form. E.g.: MS-Access, Oracle, Fox pro, Dbase etc.
Features of DBMS  [SEE 2075] [SLC 2071]

a)      It reduces data redundancy which means duplication of data.

b)     It makes data searching easy and fast.

c)      Large volume of data can be stored and updated easily.

d)     It provides high security of data as well as maintains accurate database.

RDBMS (Relational Database Management System) [SQE 2074]

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 RDBMS software developed by Microsoft Corporation which is used to collect, store and manage information in multiple tables.

Features of MS-Access

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

b)     Graphical user interface made it easy to use

Objects of MS-Access [SEE 2074 U] [SLC 2071]

a) Table

b) Form                 

c) Query    

d) Report
Table

Table 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

Importance of table

a)      Different properties of a field can be set in a table.

b)     It provides options for primary key which helps to make data consistent.

Record [SEE 2075 S2]

Record is a collection of multiple related fields which contains information about single items in a database. Record is complete set of information.  E.g.: Ram Chabahil 4468790

Field [SEE 2075 S2]

A column in a table is called field which contains information about a certain type for all records. Field is a small unit of information. E.g.: Name, Address, Telephone

Primary key [SEE 2075] [SEE 2073] [SEE 2071 S] [SLC 2067] [MM 2076] [PMT 2075K]

A primary key is the field that uniquely identifies each record in a database so that there will not be repetition of data. It does not allow duplicate data and null value.

 

Importance of Primary Key [SEE 2073] [SLC 2069 S] [MM 2076]

a)       To reduce and control duplication of record in a table.

b)     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

Foreign key is a unique field or combination of fields in linked table whose value matches with a primary key in another table.

Data Redundancy [SEE 2074]

Data redundancy means repetition of same piece of data in a database in more than one location. Data redundancy can be controlled in MS-Access by  normalization of database.

Data type [SEE 2075 S2] [SEE 2074] [SEE 2073] [SLC 2072] [SLC 2064] [SLC 2066] [SLC 2070]

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

While designing table structure which data types are suitable to store information about teacher’s name, address, salary and date of birth. [SLC 2065] [SLC 2068 S]
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 = 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.
Text

It is used for storing text or combination of text and numbers. It can store up to 255 characters.

Memo

It is used for storing lengthy text and numbers. It can store up to 65,535 characters.

Field properties

The four types of field properties of MS-Access are:
i) Caption               ii) Format                     iii) Validation rule                     iv) Input mask

 

Caption
Caption is a field property which displays an alternate name for the field to make the field name more explanatory. It can contain up to 2048 characters.

Validation rule:

A field property which enables to limit values that can be accepted into a field is known as validation rule.

Validation text

A field property which allows type text to be displayed if the user input value is prohibited by the validation rule is known as validation text.

Indexing

Indexing is one of the important properties of database that speed up searching and storing of records using on the field.

 

Relationship

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 [SEE 2073 U] [SLC 2068]
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. Sorting helps to find specific information quickly.

Filtering

Filtering is an option that selects the required records and displays the result.
Query [SEE 2075 U] [SEE 2075 S2] [SLC 2072] [SLC 2065] [SLC 2069] [SEE 2066 S] [SLC 2068 S]

Query is an object of Ms-Access that is used to view, retrieve, change and analyze records from a table or multiple linked tables based on specified condition. It is the question asked to database.

The different ways to create query are:
i) Design view
ii) Using 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.

Select query

A select query is a query which is used to select and display the relevant data from the database.

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 is used to change one or more field values in many records at once.

Append query

Append query is a type of action query which adds a group of records in one or more tables to the end od records.

Delete Query

A delete query is a type of action query that removes a record or group of records from one or more tables according to the specific condition or criteria.

Make Table Query

A make-table query is a type of action query which creates a new table from all or part of the data in one or more tables.

Advantages of query [SLC 2065]

a)      We can filter, perform calculations and summarize data.

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

Form [SEE 2073 U] [SLC 2064] [SLC 2066] [SLC 2068] [SLC 2065 S] [SLC 2069 S] [SQE 2075K]

Form is an object of Ms-Access which provides graphical interface to view, modify and add records.

The different ways to create form are:
i) Design view
ii) Using wizard

Importance of form [SEE 2075] [SLC 2064,  2066,  2065 S,  2070 S

i. We can view a complete record in specific format

ii. We can edit record easily at a time.

Report [SEE 2074 U] [SEE 2071 S] [SLC 2070] [SLC 2067 S]

Report is one of the MS-Access database objects used to present information in an effective and organized format that is ready for printing. It is generated from tables or queries.

The methods of creating report are:
i) Using design view
ii) Using report wizard

Why is Report created? / Importance of report [SEE 2071 S] [SLC 2070] [SLC 2067 S] [SEE 2075 U] [SLC 2067]

-to print documents according to user specifications of the summarize information through queries or tables.