This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.
Database systems have permeated all spheres of life. To understand database management system at first we should have some clear concept about some terms relating database management. Data is the raw material from which information is derived as the end product. Data represents a set of characters that have no meaning on their own, i.e., it consists of just symbols. On processing, meaning is attached to data, which transforms into information.
A database is a collection of logically related data arranged in a structured form designed to meet the information requirements of multiple users. It may also be defined as a collection of non-redundant operational data sharable between different application systems.
Database Management System
It is a collection of software that is used to store, delete, modify and retrieve data that is stored in a database. DBMS acts as an interface between the user and the data.
The database management systems have the following goals:
i) To provide retrieval flexibility. It should be relatively easy to link data from different files.
ii) To facilitate reduction of data duplication and elimination of multiple copies of a master file. Data redundancy control helps in overcoming updating problems and promotes data integrity.
iii) To ensure high level of data independence. The data is hidden from the programming language, operating system and processing environment. It should be up to DBMS to convert the stored data into a form that could be used in whatever language the programmer desires to use.
Need for DBMS
Let us consider the scenario of data processing that existed before the advent of database management systems. In the file-oriented system, which was used at that time, a master file (the file which contains all the up-to-date data on a subject) is created using a programming language. Access techniques based on the requisite queries on the data are embedded in the file at the time of its creation. Any change in the master file i.e., addition of a new field or change in the structure of an existing field has to be implemented by creating the file all over again along with the modified access techniques.
To illustrate the limitations of data management using master files, let us consider the example of a database of an educational institution running professional courses. Let us assume that the database consists of three master files of student, faculty and course records.
Now, suppose there is a query to provide the names of all the female students being taught by female faculty members. This query cannot be answered by the available master files despite the fact that the data needed for the query exists in the database. The difficulty lies in the fact that the needed data is available in two master files created in different programming languages and having their own access techniques. To answer the query a new master file with data items derived from the student and faculty master files will have to be created and new programmes for accessing the data written. This makes data retrieval cumbersome and time-consuming.
Take another situation when the accounts department of the institution in the example also wants to use the database and needs the student master file with additional fields like stipend paid, fees due, penalties charged, etc. To meet these requirements, another copy of the student master file with new fields is created. Similarly, there may be copies of faculty and course master files created to meet specific requirements. This results in duplication of data i.e., data redundancy. In such circumstances it becomes difficult to keep the master files identically updated i.e., propagate the updates in all the copies.
These limitation and drawbacks were at the core of development of database management systems. The basic solution of this is to use an RDBMS (Relational Database Management System).
A Relational Database Management System (RDBMS) is a collection of programs that help to organise and manipulate data in a relational database. There are many examples of RDBMS such as Oracle, DB2, PostgreSQL, MySQL, FileMaker, and MS Access.
In a small library database of books, members, and circulation can easily be maintained by using MS Access.
It is easily available.
It is easy to install and run.
Lots of reading on Access is available.
It is good and easy for people who have just started with DBs.
Microsoft Access is a relational database management system used on desktop computers to manage information on different levels for different purposes. It is a powerful package used for creating and managing databases. It has certain built-in features which assist in construction and viewing of information.
Using Microsoft Access, one can manage information from a single database file. Within the file, one can use:
Tables to store data.
Queries to find and retrieve the data that is required.
Forms to view, add, and update data in tables.
Reports to analyse or print data in a specific layout.
Data access pages to view, update, or analyse the database's data from the Internet or an intranet.
In Access, the database information is stored in the form of data tables. Every table has a structure that provides the facility for the collection, organisation, storage, and retrieval of data. These tables are contained in a database file. Each database file can have numerous data tables.
A data table consists of fields and records. Fields are categories of information, such as name or phone number. A group of related fields (for example, one person's name, address, and phone number) is called a record. In tables, fields are displayed as columns and records are displayed as rows.
Tables are the basic objects in Access, but there are other objects also that allow data to be retrieved, viewed, manipulated, and reported. The following table describes the other Access objects.
Tables store data in rows (records) and columns (fields). A database must always contain at least one table.
Queries ask a question of data stored in a table.
Forms are custom screens to enter and view data in a table or query.
Reports present data from a table or query.
Web page designed for viewing and working over the Internet.
Macros help to perform routine tasks by automating them into a single command.
Like, automate tasks by using VB programming language.
How a small library's database can be managed using MS Access?
Using the following easy steps database of a small library can easily be managed. Here as example a database have been created for a small library.
MS Access can be opened by using the following steps:
At first one has to create a folder in any local drive.
Then entering in that folder one has to make a right click and from New option one has to select Microsoft Access Application to make a database in MS Access.
After opening a new database the following screen appears.
Tabs for the main database Window provide the following options:
Tables (tables available in the database)
Queries (any queries saved in the database)
Forms ( forms saved in the database)
Reports ( reports saved in the database)
Macros (any short program saved in the database)
Modules (Visual basic for application procedures stored in the database)
A table is a collection of data on a specific aspect viz. student records, user information etc. In tables data are organised in rows and columns. Each row represents a particular record and a column represents a particular field.
For example, each field in a book table contains the same type of information for every book, such as title etc. Each record in that table contains all the information about one book, such as the title, author, pub, price etc.
Access provides three ways to create a table for which there are icons in the Database Window. Double-click on the icons to create a table. In Access, tables can be created either through the help of wizard or through the design view option.
Create table in Design view will allow you to create the fields of the table. This is the most common way of creating a table.
Create table using wizard will lead you step-by-step through the creation of a table.
Create table by entering data will give you a blank datasheet with unlabelled columns.
Create table in Design view is the most common way of creating tables and here this option has been followed to create three tables (book, cir and Mem). To create table in this view one have to double click on this option and a screen with table design view appears.
Table design view consists of three parts-
Field name: A field name can be up to 64 characters long, including spaces.
Data type: The data type determines the kind of values that users can store in the field.
Data types are as follows:
Text: Text or text and nos., as well as nos. that don't require calculations, i.e. phone nos. -Up to 255 characters
Memo: Lengthy text or combinations of text and nos. - Up to 65,535 characters.
Number: Numeric data used in mathematical calculations.
Date/Time: Date and time values for the years 100 through 9999
AutoNumber: A unique sequential (incremented by 1) no or random no assigned whenever a new record is added.
Yes/No: Yes and No values and fields that contain only one of two values (Yes/No, True/False, or On/Off).
OLE Object: An object (such as Excel spreadsheet, Word document, graphics, sounds, or other binary data) linked to or embedded in a table.
In the Table Design View fill in the Field Name, Data Type and Description for each field, as required. Then the table is saved by giving a desirable name. After defining the table the next step is to add data in the table. Data can be added, deleted or modified in tables using the spread sheet like interface. After adding data it should be saved by choosing save button from the file menu.
Here in this same process the three tables have been made. After adding data in book, cir and Mem table they appear as the figure below.
Forms are windows objects used to view and/or enter data in the database. They offer more user-friendly interface by adding labels for each field and other helpful information. A form can combine data from one or more tables or queries. Forms can be created by hand using a Design View or through wizards.
Forms can have different layouts or arrangement based on the labels and fields on the screen:
Columnar - Labels are placed to the left of each field. Most suitable for viewing data is one record at a time.
Tabular - Field labels are placed at the top of the screen and the records are displayed below like a spreadsheet. Most suitable for displaying multiple records is of data at a time.
Datasheet - Data appears in the similar fashion as viewing or adding data to a table.
Justified - Labels are placed above each field with the fields spread out on the form. This is suitable for viewing a single record.
Forms are used as an alternative way to enter data into a database table.
Create Form by Using Wizard
To create a form using the assistance of the wizard, follow these steps:
Click the Create form by using wizard option on the database window.
A new screen will appear as shown in the figure below. From the Tables/Queries drop-down menu, select the table or query whose datasheet the form is required to modify. Then, select the fields that will be included on the form by highlighting each one of the Available Fields window and clicking the single right arrow button > to move the field to the Selected Fields window. To move all of the fields to Select Fields, click the double right arrow button >>. If you make a mistake and would like to remove a field or all of the fields from the Selected Fields window, click the left arrow < or left double arrow << buttons. After the proper fields have been selected, click the Next button to move on to the next screen.
On the second screen select the layout of the form. Click the Next button to move on to the next screen.
Select a visual style for the form from the next set of options and click Next.
On the final screen name the form in the space provided. Select "Open the form to view or enter information" to open the form in Form View or "Modify the form's design" to open it in Design View. Click Finish to create the form.
Adding Records Using a Form
Input data into the table by filling out the fields of the form. Press the Tab key to move from field to field and create a new record by clicking Tab after the last field of the last record. A new record can also be created at any time by clicking the New Record button at the bottom of the form window. Records are automatically saved as they are entered so no additional manual saving needs to be executed.
Creating Relationships between Tables
While creating relationships between tables, the related fields do not have to have the same names. However, related fields must have the same data type.
To create relationships at first all the tables have to be closed. Relationships can not be created or modified between open tables. Then from tools menu select relationships option. The show table dialog box will be automatically displayed.
Now add tables by selecting them one by one. Then drag the field that is intended to make relationship from one table to the related field in the other table. Then Edit Relationships dialog box is displayed. Then check the field names displayed in the two columns to ensure they are correct. Then click the Create button to create the relationship. Then save it in the database.
After creating relationships the screen will appear like this
Queries facilitate fast retrieval and display of data from tables. This is very useful when tables are very large. Queries can be used to retrieve information from a single table or multiple tables. Queries select records from one or more tables in a database so that they can be viewed, analysed, and sorted on a common datasheet. Queries help in limiting the data in a table for specific goal.
Creating a query can be accomplished by using either the query design view or the Query wizard.
Create Query in Design View
Follow these steps to create a new query in Design View:
Double click on the Create query in design view option.
Select tables and existing queries from the Tables and Queries tabs and click the Add button to add each one to the new query
Click Close when all of the tables and queries have been selected.
Add fields from the tables to the new query by selecting the field from the Field: and Table: drop-down menus on the query form. Specify sort orders if necessary i.e., you can specify the order in which the records are displayed. Field row - shows the name of the fields included in the query.
Table row - shows the name of the table that the field comes from.
Sort row - allows you to specify the order in which the records are displayed.
Criteria row - allows you to specify criteria for including or excluding records from the results set.
Enter the criteria for the query in the Criteria: field. After selecting all of the fields and tables, click the Run button on the toolbar.
Save the query by clicking the Save button.
Create query by using wizard
Click the Create query by using wizard icon in the database window to have Access step you through the process of creating a query.
From the first window select fields that will be included in the query by first selecting the table from the drop-down Tables/Queries menu. Select the fields by clicking the single arrow > button to move the field from the Available Fields list to Selected Fields. Click the double arrow button >> to move all of the fields to Selected Fields. Select another table or query to choose from more fields and repeat the process of moving them to the Selected Fields box. Click Next button when all of the fields have been selected.
On the next window, enter the name for the query and click Finish.
An SQL query is a query you create by using an SQL statement. You can use Structured Query Language (SQL) to query, update, and manage relational databases such as Access.
When you create a query in query Design view, Access constructs the equivalent SQL statements behind the scenes for you. In fact, most query properties in the property sheet in query Design view have equivalent clauses and options available in SQL view. If you want, you can view or edit the SQL statement in SQL view.
Create a SQL query
In the Database window, double click on Create query in design view.
Without adding tables or queries, click Close in the Show Table dialog box.
From view menu select SQL view.
Enter SQL SELECT statements. SQL string/statement is an expression that defines an SQL command, such as SELECT, UPDATE, or DELETE, and includes clauses such as WHERE and ORDER BY. SQL strings/statements are typically used in queries and in aggregate functions.).
To see the query result, select Run option from the query menu. Then query screen will appear with results.
A report is an organised document that can be used to print form data. Reports add formatting to the output including fonts, colors, backgrounds and other features. Reports are usually printed out on paper rather than just viewed on the screen. A report can include data from various tables, queries, forms or data imported from other packages. There are many ways to create reports to show summarisation, sorting and layout of the data in an organised form. Reports organise and group the information in a table or query and provide a way to print the data in a database.
Create Report by Using Wizard
Create a report using Access' wizard by following these steps:
Double-click the "Create report by using wizard" option on the Reports Database Window.
A new screen appears. Select the information source for the report by selecting a table or query from the Tables/Queries dropdown menu. Then, select the fields that should be displayed in the report by transferring them from the Available Fields menu to the Selected Fields window using the single right arrow button > to move fields one at a time or the double arrow button >> to move all of the fields at once. Click the Next button to move to the next screen.
Select fields from the list in which the records should be grouped by and click the right arrow button > to add those fields to the diagram. Use the Priority buttons to change the order of the grouped fields if more than one field is selected. Click Next > to continue.
If the records should be sorted, identify a sort order here. Select the first field by which records should be sorted by and click the A-Z sort button to choose from ascending or descending order. Click Next to continue.
Select a layout and page orientation for the report and click Next.
Select a colour and graphics style for the report and click Next.
On the final screen, name the report and select to open it in either Print Preview or Design View mode. Click the Finish button to create the report.
After finishing this report will appear like the figure shown below.
Thus by using MS Access database of a small library can easily be managed.