UNIT IV
Database Concepts: The concept of data base management system; Data field, records, and files, Sorting and
indexing data; Searching records. Designing queries, and reports; Linking of datafiles; Understanding
Programming environment in DBMS; Developing menu drive applications in query language (MS–Access).
I. DATABASE MANAGEMENT SYSTEM (DBMS):
DBMS is software designed to assist in maintaining and utilizing large collections
of data. (Or)
DBMS is a collection of interrelated data and a set of programs to access that
data.
MANAGING DATA:
1. Database design and application development:
- A user describes a real-world enterprise in terms of data.
- How to organize the stored data?
- Develop applications upon a DBMS.
2. Data Analysis:
- Using Queries over the data in the DBMS.
3. Concurrency and Robustness:
- To allow many users to access data concurrently.
- To protect the data in the case of system failures.
4. Efficiency and scalability:
- To store large data sets for answering the data efficiently.
II. DATA FIELD, RECORDS, AND FILES
Data is any sequence of one or more symbols given meaning by specific act of
interpretation. Data requires interpretation to become information. To translate data to
information, there must be several known factors considered. The factors involved are
determined by the creator of the data and the desired information.
A field is a single piece of information;
a record is one complete set of fields; and a file is a collection of records. For example, a
telephone book is analogous to a file.
This is a collection of programs that enables you to enter, organize, and select data in
a database.
III. SORTING AND INDEXING DATA
Sorting is the process of arranging data into meaningful order so that you can analyse it more
effectively. For example, you might want to order sales data by calendar month so that you can
produce a graph of sales performance. You can use Discoverer to sort data as follows: sort text data
into alphabetical order.
The SQL ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set in ascending or descending order. The
ORDER BY keyword sorts the records in ascending order by default. To sort the records in
descending order, use the DESC keyword.
Indexing creates an index file that consists of a list of rows in a logical row order, along with
their corresponding physical position in the table.
There are three types of indexing namely Ordered, Single-level, and multi-level. Single Level
Indexing is divided into three types namely Primary(index table is created using primary keys),
Secondary(index table is created using candidate keys), and Clustered(index table is created using
non-key values).
IV. SEARCHING RECORDS
The search processes.
1. Step 1 - Identifying the words to search for.
2. Step 2 - Formatting the words for searching.
3. Step 3 - Turning the words into searches.
4. Step 4 - Using the filters and limits in databases to refine your search.
5. Step 5 - Reviewing your search results.
A database is a searchable collection of information. A research database is where you find
journal, magazine, and newspaper articles. Each database contains thousands of articles
published in many different journals, allowing you find relevant articles faster than you would by
searching individual journals.
V.
Designin
g
queries,
and
reports
Creating
a forms:
Forms are used to simplify data entry work or to display information in a specific manner.
Forms let you add, modify and delete database data. In access you can create six types of forms.
To create a form follows the following steps:
Click on forms tab from the shortcut bar.
Double click on create form by using wizard option.
Choose any one table from the table option by clicking on it.
Click on >> this button to take all the fields of the active table to the selected category.
Click on next button.
Click on layout that you like for your form.
Click on next button.
Click on the style of form that you want.
Click on next button to continue.
Give the name of the form.
Click on the radio button to enter the data directly in to the next dialog box.
Click on finish button to view the forms.
Finally your form look like below:
Query:
A query is a filter through which data is evaluated. You can define filter criteria in a query and
only those records, which meet these criteria, are displayed. There are three types of queries:
Selection query
Action query
Cross tab query
To create a query using design view follows these steps:
Click on queries tab from the shortcut bar.
Double click on ‘create query in design view’ option.
The following dialog box will be displayed.
Click on newtable table from the tables category.
Click on add button to add the table in the query.
Click on close button.
Choose update query from query menu.
Now the thing to note here would be that only those fields that have a particular criteria
for calculating that particular field.
Click in the update to column.
Click on the build icon from the formatting toolbar.
In the builder dialog box would be placed.
Double click on the tables option given below in the diagram.
Click on newtable option given here.
Double click on the age option.
Choose the function that you want from the function bar.(for ex: newtable.age * 100)
Click on OK button to continue.
Fill up the formulas for the rest of the fields.
Click on close button to close this query.
It will open another dialog box.
Click on yes button to save this query.
Give the name of the query that you have just created in the query name box.
Finally click OK button.
After defining the criteria: (the age value multiplied by 100)
Creating reports:
A report summarizes data in a format suitable for publishing that is to view data on the screen or
to print it on a printer or to publish it on the web. Reports are used to present data in a
meaningful and attractive manner and here you can combine data, chart, and images even audio
and video. There are six ways to create a report in access.
Design view
Report wizard
Columnar auto report
Tabular auto report
Chart wizard
Label wizard
To create a report follows these steps:
Click on report tab.
Double click on the option ‘create reports by using wizard’
Choose any table from the tables/queries option.
Click on this [>] button to select the fields that you want in your presentation.
Click on next button to continue.
Specify the grouping level if you want by clicking on the fields and then clicking on ‘>’
button.
Click on next button to continue.
Next specify a sorting sequence if you want in your records.
Click on the field on basis of which you want to sort.
Click on next button to continue.
Click the type of report (for ex: columnar) from the layout option to choose the layout of
your report.
You could also change the orientation of your report by clicking on either portrait or
landscape depending upon your requirement.
Click on next button to continue.
Then choose the style of report that you want.
Click on OK button to continue.
Nest Give the name of your report.
Click on FINISH button.
You can now see your report. You can scroll through the report by clicking on the record
button given below and by using the scroll bars or you can also use the arrow keys from
the keyboard.
VI. Linking of datafiles
Creating a database :( table wizard)
We can create a database table using the design mode, table wizards and entering data
directly. First we can see the creating a table through design wizard.
Creating a table through the table wizard is much faster than through the design mode.
However if you use wizards you are somewhat restricted with the predefined settings already
available.
Click on the business radio button.
Choose employee from the sample tables by clicking on it.
To add fields in to the table click on fields in the sample fields. Then click on ‘>’ button
to add the fields in the ‘fields in my new table’ category. If you want to take all the fields
press ‘>>’ button. To remove the field press the ‘<’ button. To remove all the fields press
“<<“.
Click on the next button to continue.
Next it will open again one check box.
Here you must specify the table name and also primary key.
Click the next button it will again open one dialog box.
Here we have three options:
Modify the table design
Enter data directly in to the table
Enter data in to the table using a form the wizard creates for me.
Select the second option to enter the records in to the table.
Finally click the finish button.
Now you can start entering the data directly in the table.
After entering the data your table would look somewhat like this.
Saving the database:
After having finished entering the data, you must save it, so that in case of any electrical
disturbances all your precious work is not lost.
Choose save command from the file menu.
The following dialog box would be displayed.
Give the name of the new table in this box.
Click on OK button to save the table.
Relation ships:
A relation ship works by matching the data in two different tables using a common primary
field. There are broadly two kinds of relationships – one to one & one to many.
Now to link the two tables,
Choose relationship command from tool menu.
Click the name field in man table. Keep your mouse pressed and drag your mouse pointer
on top of the personality field in the woman table. Now leave your mouse.
Click on create button to create the relationship b/w the two tables.
As you see here that the tables are now linked together.
Click on the close button to close the dialog box.
VII. Understanding Programming environment in DBMS
A database environment is a collective system of components that comprise and regulates the group of
data, management, and use of data, which consist of software, hardware, people, techniques of handling
database, and the data also.
The hardware in a database environment means the computers and computer peripherals that are being
used to manage a database, and the software means the whole thing right from the operating system (OS)
to the application programs that include database management software like M.S. Access or SQL Server.
The techniques are the rules, concepts, and instructions given to both the people and the software along
with the data with the group of facts and information positioned within the database environment
VIII. Developing menu drive applications in query language (MS–Access).
Create a database in Access
1. Open Access. If Access is already open, select File > New.
2. Select Blank database, or select a template.
3. Enter a name for the database, select a location, and then select Create. If needed, select
Enable content in the yellow message bar when the database opens.
