WHAT IS DATABASE and how to create in Ms Access

WHAT IS DATABASE

Database is used to organize information into data in the form of tables. For example, a table with student's data. Different tables are joined together on the basis of relationships. The table and relationships together form relational structure. Relational Structure helps in reduction of repetitive data, improves accuracy and provides better management of data.

Create Table in Database

1.      Click the Microsoft Office Button, and then click New.

2.      In the File Name box, type a file name for the new database.

3.      To browse to a different location to save the database, click the folder icon.

4.      Click Create.

The new database opens, and a new table named Table1 is created and opens in Datasheet view. When you create a new table in Datasheet view, Access automatically creates a primary key for you and assigns it a field name of ID and the AutoNumber data type.

Add Fields to the Table

In order to add new fields to the Table, enter into Design view, On the Home tab, in the Views group, click View, and then click Design View. 

Here add the fields and set their properties like datatype, size, default value, input mask etc. The field which has to be made primary key is also 

set here.

When you create a new table in Datasheet view, Access automatically creates a primary key for you and assigns it a field name of ID and the AutoNumber data type. In Design view, you can change or remove the primary key, or set the primary key for a table that doesn't already have one.

After the table is created, it is saved in the database by going to Microsoft office button and choosing the Save option.

Add Data in the Table

Once the Tables have been created, data has to be added to them.

1.      Open the database to which data has to be added.

2.      Select the table to which has to be inserted

3.      The table opens in the datasheet view mode.

4.      Add the data to the table record by record in the datasheet grid and save the table.

The data added can also be modified also in the datasheet view mode.

Add New Fields in the Table

To add new fields to the table, open the table in design view mode.

1.      In the Home tab, in the Views group, click View, and then click Design View.

2.      In the table design grid, add the new field

3.      Define its properties like datatype, length, default value etc.

4.      Save the changes made to the table

Set a Table's Primary Key

When a new table is created, Access automatically creates a primary key and gives a default field name ID and AutoNumber as its datatype. In Design view mode, it is possible to change, remove or set the primary key of a Table.

1.      Select the table for which the primary key has to be set or modified.

2.      On the Home tab, in the Views group, click view and then select the Design view mode.

3.      In the Design grid, select the field or fields (by holding down CTRL to select more than one field.

4.      On the Design tab, in the Tools group, click Primary Key. A key indicator appears on the left of the field or fields selected to indicate the           field as primary key.

Defining Relationships

When you create a relationship between tables, the common fields are not required to have the same names, although it is often the case that they do. But, the common fields must have the same data type. If the primary key field is an AutoNumber field, however, the foreign key field can also be a Number field if the FieldSize property of both fields is the same. For example, you can match an AutoNumber field and a Number field if the FieldSize property of both fields is Long Integer. When both common fields are Number fields, they must have the same FieldSize property setting.

To create relationships between two tables,

1.      Choose Relationships under the Database Tools menu.

2.      Then Choose the Table option and add the tables between which relationship has to be created.

3.      Drag a field (typically the primary key) from one table to the common field (the foreign key) in the other table. To drag multiple fields,               press the CTRL key, click each field, and then drag them.

The Edit Relationships dialog box appears as below:

5.      To enforce referential integrity for this relationship, select the Enforce Referential Integrity check box.

     6.      Click Create.

Access draws a relationship line between the two tables. If the Enforce Referential Integrity check box is selected, the line appears thicker at each end. In addition, the number 1 appears over the thick portion on one side of the relationship line, and the infinity symbol (∞) appears over the thick portion on the on the other side of the line, as shown in the following screen.

The purpose of referential integrity is to prevent orphan records and to keep references synchronized. Once referential integrity is enforced, Access rejects any operation that would violate referential integrity for that table relationship. This means that Access will reject both updates that change the target of a reference and deletions that remove the target of a reference. To propagate the referential updates and deletions so that all related rows are changed accordingly, see the Set the cascade options section.