It is important to understand the overall needs of the system before you begin to work on the details. For instance, you must know the logical flow of activities in the system. If you are designing a book collection database for a library, you need to know the process of purchase, cataloging classification, and issues. Only after you have understood the sequence of activities can you decide upon the data structure.
The ultimate purpose of every DBMS is the generation of reports and queries. Therefore, you need to remember the final outputs that your system is designed to generate and decide the data structure accordingly. You need to design your database in such a manner that data integrity is maintained at all levels. Data integrity refers to a state of the database where none of the data is duplicated or inconsistent. For example, in the Book Collection database, if you store the author’s name and address values for all their books, you will have redundant data.
This also increases the chances of inconsistency in the data. For example, if you are storing the author’s address for every book, the changes to the address may not get reflected in every record. Thus, it would be better to split your data into multiple tables and maintain separate tables for author details and book details. Both the tables can then be related by including an author identification number in the Books table.
After you have split up your database into multiple tables, the next step is to set relations between them in such a way that a query or report can retrieve the data from multiple tables. Access does this by matching primary keys with the foreign keys in other tables. For example, to print a report showing the books and their authors, the query can match the Author ID in the Author table with the Author ID in the Books table and generate a dataset containing all the book details and the author details.
After you have decided upon the tables that you will have in your database, you can define the various fields and their properties. For every field, you should decide the:
*Field Name
*Field Typo
*Field Size
One of the most important tasks in a DBMS is ensuring data integrity. For this, you can define data entry rules and data validations. This would ensure that only valid and consistent data is stored in the table and inconsistent data is rejected. For example, in the Book Collection database, your data validation rules can check for:
Entry of O in the Price field
*A blank in the Name or Title field
*An Author ID that exists in the Authors table
*A Title Typo that exists in the list of titles
A database also consists of various objects like forms and reports that manipulate and display data. After you have created the table, you need to design data entry and display forms, queries, and reports. You will also need macros lo automate routine tasks. These objects must be created keeping in mind the format of the final output required. Do pending upon the complexity of the object, you can either custom design it or create it with the help of the Wizards.
After you have designed the database and table structures on paper, you can begin creating a database. There are several ways in which you can create a database. You can create a blank database and add tables and other objects yourself. Alternatively, you can invoke the Database Creation Wizard to create a database.
Access has several database templates, such as Inventory control. Address Book and Music Collection that the wizard can create for you. You can then modify these databases to suit your requirements.
The stops to create a database are:
1. As you invoke Access, the starting screen is displayed
2. Select the Access Database Wizard radio button,
The Wizard gives you an option to create a blank database or use one of the available sample databases,
3. Select the Databases tab in the Now dialog box
4. A list of sample databases appears.
5. Select a database from the list of sample databases and click on the Create button,
6.Type in a name for the new database file in the File name text box and then click on the Create button.
7. Select the fields and click on the Next button. A list of layouts for the display of forms appears.
8. Select a layout of your choice and click on the Next button. A list of formats for displaying printed reports displayed
9. Select the desired format and click on the Next button. The Table Naming dialog box appears.
10. Click on the Finish button.
After the database is created, it is opened and you can view the various forms and reports that have been included as part of the database.
Note: You can change the settings that you have specified to the Database Wizard any time by clicking the Back button and reselecting previous options. You can also select Finish any time to exit the Wizard and create the rest of the database yourself.
Datasheet View
When you create a table through the datasheet view, you need to enter the data into a spreadsheet-like format. The data type and size of the fields is determined and the table is created automatically. The steps to create a table using the datasheet view are:
1.Select the Datasheet View from the New Table dialog box
2.enter the data records in the datasheet. Depending upon the kind of data you enter the table structure is created.
3. To save the table, choose either the Close or the Save option from the File menu.
4.Enter the table name and click on the OK button.
The Table Wizard:
The Table Wizard creates tables from sample tables. You can modify the table design after the basic structure has been created by the Wizard.
The steps to create a table with the help of the Wizard are:
1. Sealed Table Wizard from the New Table dialog box
2. Select the Authors table from the Personal list.
3. Select the fields that you want in the table and click on the Next button.
4. Type a name for the table and click on the Next button.
Adding Records
If you have created the table design through the datasheet view, you will already have a few records in the table. You will also be familiar with the datasheet window.
The steps to add records to a table are:
1. Click on the Tables tab in the Database window.
2. Click on the table name you want to add records to.
3. Click on the Open button
4. Bring the cursor to the first field and type the value. As you begin to type data, the record pointer turns into a pencil, indicating that the record is being edited. A second-row also appears as you type the first one.
As you move on to the next record for data entry, Access saves the previous record. Any time you move to a new record or close the table, the last record you worked with is saved. You can also save a record by selecting the Save Record option from the Racard menu.
