Database Management System
Database management systems are applications (software) that are developed to create and manage databases. Data are stored as records in various database files that can be combined to produce meaningful information for users. The DBMS controls all functions of capturing, processing, storing, and retrieving data from databases and generates various forms of data output. The most common examples of DBMS include Oracle, DB2, Microsoft Access, Microsoft SQL Server, PostgreSQL, MySQL, and FileMaker. A DBMS includes modeling language to define the schema of each database hosted in the DBMS, according to the DBMS data model; database query language to allow users to interactively interrogate the database to analyze its data and update it according to the user’s privileges on data. The DBMS also controls the security of the database. Data security prevents unauthorized users from viewing or updating the database. The DBMS maintains the integrity of the database by not allowing more than one user to update the same record at the same time.
Overall systems design decisions are performed by data administrators and systems analysts. Detailed database design is performed by database administrators. Database servers are specially designed computers that hold the actual databases and run only the DBMS and related software. Most of the current (modern) DBMS follows a relational database management system. The term relational refers to the fact that each record in the database contains information related to a single subject and only that subject.
Database Capabilities
Generally, database capabilities can be explained in three important terms which are:
Data Definition: This involves the selection of what data is to
be stored in the database and their types such as numbers, characters, strings,
Boolean, etc, and how the data is related.
Data Manipulation: This involves the selection of data
fields required and their relationships. It also includes operations such as
updating data, deleting, copying and etc.
Data Control: This involves the definition of users of the
database and their privileges such as who is allowed to read, update or insert
data.
Features of Microsoft Access
Microsoft Access is a Windows-based DBMS that allows you to create and process data in a database. Microsoft Access has all the features of a classic RDBMS. The structure of Microsoft Access is composed of objects. Within an Access database, the main objects are tables, queries, forms, reports, macros, and modules.
A table is an object used to store data. Each table contains information about a particular subject, such as customers, orders, students, or courses. Tables contain fields (or columns) that store different kinds of data, such as a name or an address, and records (or rows) that collect all the information about a particular instance of the subject, such as all the information about an entertainment group named.
A Query is an object that provides a custom view of data from one or more tables. In Access, you can use the graphical query by example (QBE) facility or you can write SQL statements to create your queries. You can define queries to select, update, insert, or delete data. You can also define queries that create new tables from data in one or more existing tables.
Reports are designed for formatting, calculating, printing, and summarizing selected data. You can view a report on your screen before you print it. Reports are a great way to organize and present data from your Microsoft Access database. Reports enable you to format your data in an attractive and informative layout for printing or viewing on-screen.
a table overview
A query overview
A report overview
Steps in Designing a Database
Determine
the Purpose of your Database
First, you need to determine the requirements and decide which
facts or information you want Microsoft Access to store. Before you start
building an application, you'll probably have some idea of what you want it to
do.
Determine the Tables you need
Once you have a clear purpose for your database, you can
divide your information into separate subjects. Each subject will be a table in
your database. A database may contain many tables.
Determine the Fields you need
Decide what information you want to keep in each table. Each category of information in a table is called a field and is displayed as a column in the table. For example, some of the fields in the Library Books table could be Book No, Book Title, Publisher, year of publication. Decide on the field names and the primary key for each table. A primary key is a field that uniquely identifies each record within a table. A primary key is one or more fields (columns) whose value or values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.
Determine
the Relationships
Look at each table and decide how data in one table is related to the data in other tables. Add fields to tables or create new tables to clarify the relationships, as necessary.
Staring and Quitting Microsoft Access
You can start using Microsoft Access by selecting the Microsoft Access program from the programs at the start button. In the Startup window you can select the Blank Database which allows you to create a new database from scratch, i.e. start creating the database yourself. Also, you can use a Database Wizard which allows you to create a new database by following the steps as supplied by the new database wizard. The MS Access databases end with the file extension .mdb. On the other hand, there are various ways of exiting from the Microsoft Access program after using it. You can select Exit from the File menu to close and exit Microsoft Access. You can also click the exit button at the title bar on the right side where you have the right-most button called the exit button.
exit button overview
Object and Database Utilities
You can manage database objects directly by clicking on it to highlight the object’s name. Then, you can perform one of the following actions: deleting, renaming, copying, printing, and preview. The utility functions must be accomplished outside of Access. Note that you CANNOT make a copy of the entire database inside Access. While in Windows Explorer, you can right-click on an MDB database file and choose Copy, Rename, Delete, or Send To. If you choose Copy, you will also have to right-click on a blank area in a destination folder and choose Paste to complete the copy operation. There are multiple ways to copy, delete, and rename files.
Data Types
MS Access allows several data types such as Text, Memo, Number, Date/Time, Currency, AutoNumber, and Yes/No, Hyperlink and OLE. Text is suitable for small amounts of text. It is used to store data such as names, addresses, and any numbers that do not require calculations, such as phone numbers, part numbers, ssn, or postal codes such as names, addresses, and telephone numbers. It holds characters, numbers, punctuation, special symbols (up to 255 characters).
The number of characters stored in a text field can be changed. The memo is suitable for large amounts of text such as descriptions or abstracts. It can take up to 65,535 characters. The number of characters stored in a Memo field cannot be changed.
A number is a numeric value (whole or fractional) that can be used to define numbers, plus/minus signs, and decimal places. Date/Time is used to hold dates and times in a variety of common formats. Currency is used to define monetary (data) values.
The AutoNumber is an automatic counter which increments for each record i.e. it holds automatic record numbering. Lastly, Yes/No is used to define Yes-or-No and true-or-false values. They appear in the datasheet view as a check box and are referred to as a logical or Boolean type. A hyperlink is a text used as a hyperlink address, with up to three parts: display text, address, and subaddress (location within a page). OLE data type holds graphics, sounds, and other Object Linking and Embedding objects.
Table Design
After designing your table, a table structure needs to be
stored so that when you are using that table for the storage of the data you
have a permanent structure. Supply the unique name for each table. A table name
should be meaningful and can belong to mixed cases and spaces.
Table Fields
Fields are the columns of the table. At any stage of table design, you can add, delete, rename or modify the table fields. Each field name can be up to 64 characters long and can include letters, numbers, spaces, and most special characters except for the period, exclamation point, and square brackets [ ].
A good table design involves naming each field by starting with
the first letter of the table name. You cannot begin a field name with a space
or blank. Note that there is no Undo for the action of deleting the field. Field
names within a table must be unique. Avoid using extremely long names as they
are difficult to remember and to refer to. You might also want to avoid using
spaces anywhere in field names if you are planning to write macros or modules
or use Visual Basic for Applications (VBA) with a database. Also, avoid specifying
a name for a field that could cause a conflict with built-in Microsoft Access
functions.
Primary Keys
One field that contains unique data should be designated as the primary key for the table. A primary key is a field or attribute within a table which acts as a unique identifier for the records of tables. For example, a primary key can be a student registration number, employee check number, mobile number, or bank account. To set the primary key, you choose one of the fields and use it as a primary key.
Through a primary key, you can search information in the database; you can create relations based on the primary key, etc. Note that you can have only one primary key per table but can be a combination of more than one field. Also, if the primary key is used in a relationship and you want to remove a primary key, you must delete the relationship before you can remove the primary key. Also, note that you can’t delete relationships between open tables. A set of icons along the table to provide you with some status information:
Data Entry
You can enter data directly into the rows of a datasheet by simply typing the data into the cells. Use the tab key to move from cell to cell. A tab keypress in the final column in a record will move you to the beginning of the next record. When you move to another record, Microsoft Access saves your changes. The Data Entry option for tables can be activated from the Records menu. It hides all completed records and places the current blank record directly under the column headers. Field data can be cut from, copied from, or pasted into the table cells. The shortcut keys of Ctrl X for Cut, Ctrl C for Copy, and Ctrl V for Paste can also be used.
You can also import data or link table data from other Microsoft Access databases as well as data from other programs and file formats, such as MS Excel, dBase, Microsoft FOXPRO, Paradox, etc. Wrong data entered into the table field can be corrected by use of BACKSPACE key or delete key. To cancel your changes in the current field, press ESC. To cancel your changes in the entire record, press ESC again before you move out of the field.
Find Command
To locate records with field values that match all or part of a particular value use a Find command (ctrl-F). Enter your search text (string) in the Find What: field. − Wildcard characters can be used to fine-tune the search.
A table Searching in MS Access
Select your desired options and then click the Find Next button.
Sorting Data
Sorting is arranging data in some meaningful order. By default, Access displays the datasheet records on the screen in the order dictated by the primary key. To change the sort order, select the desired sort field by clicking on the header to highlight the column.
Choose the Sort command and then choose Ascending or Descending sort order. Another option is to click on one of the sort buttons on the toolbar labeled with [A->Z] or [Z->A].
Relationships in a Database
After you've set up different tables for each subject in your database, you need a way of telling Microsoft Access how to bring that information back together again. The first step in this process is to define relationships between your tables. After you've done that, you can create queries, forms, and reports to display information from several tables at once. A relationship works by matching data in key fields usually a field with the same name in both tables. In most cases, these matching fields are the primary key from one table, which provides a unique identifier for each record, and a foreign key in the other table.
Primary and Foreign Key in MS Access Overview
Relationships between tables can be one-to-many, many-to-many, and one-to-one. A one-to-many relationship is the most common type of relationship. In a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A.
In a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A. This type of relationship is only possible by defining a third table (called a junction table) whose primary key consists of two fields the foreign keys from both Tables A and B. A many-to-many relationship is really two one-to-many relationships with a third table.
In a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A. This type of relationship is not common, because most information related in this way would be in one table. A one-to-many relationship is created if only one of the related fields is a primary key or has a unique index.
A one-to-one relationship is created if both of the related fields are primary keys or have unique indexes. A many-to-many relationship is really two one-to-many relationships with a third table whose primary key consists of two fields the foreign keys from the two other tables.
Introduction to Queries
With a query, you ask questions about the data stored in your tables. The most commonly used is a select query. A select query retrieves data from one or more tables. The data in the query can come from one or more tables. After Microsoft Access retrieves the data that answers your question, you can view, change, and analyze data in different ways. A query is created with the help of Query Wizards.
You can always create a query from a single table or you can base your query on more than one table. Designing a query, which bases on more than one table, then the tables must be related by using the relationships between them. The Query will not allow you to create a query based on more than one table in which the tables are not related.
Creating a Data Entry Form and Simple Report
A quick way to create a simple data entry form is by using Autoform Wizard. Reports have two principal advantages over other methods of printing data. Reports can compare, summarize, and subtotal large sets of data and also can be created to produce attractive invoices, purchase orders, mailing labels, presentation materials, and other output you might need to efficiently conduct business. A Report Wizard is also used to generate a simple report based on a table.
Backing Up a Database
Access database objects are all stored in one file. Therefore, you must protect your data by creating backups at regular intervals of time. Also, create a backup copy of your database before you attempt certain procedures or queries or anytime you are unsure of what you are doing. Access does not have any internal backup procedure. To make a backup of your database, you must exit Access and copy your database file(s). Backup can be done manually or automated and can also be done onsite or offsite. Always make sure that the backup file is protected and properly working.