CHAPTER-3 (Database Management Tool)



CLASS X

CHAPTER-3 DATABASE MANAGEMENT TOOL

Question: What is MS Access?

Answer: Microsoft Access is a DBMS (also known as Database Management System) from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools.

Question: What is the need of MS Access?

Answer: MS-Access has very easy operating procedure compared to other database management tools that are available. Also this is fairly cheaper and can be installed in most of the computers. Another advantage is, it provides excellent graphical user interface (GUI) whereas most database management tools provide console operating interface (CUI). This feature creates excellent User-friendly environment. Table creation for database management is easily done here. That is direct interaction with tables created and editing are possible. Table creation is done directly by typing its name, property, etc., this leads to easy operation whereas other have complex syntaxes for creating these. There is no need for the user to have high programming knowledge for using MS-Access. So, users with basic computer knowledge can use it.

Question: Write the process to create a database in MS access?

Answer: When you first start Access, or if you close a database without closing Access, Microsoft Office Backstage view is displayed.

Creating a database
When you open Access, Backstage view displays the New tab. The New tab provides several ways that you can create a new database:
  • A blank database    You can start from scratch if you want. This is a good option if you have very specific design requirements or have existing data that you need to accommodate or incorporate.
  • A template that is installed with Access    Consider using a template if you are starting a new project and would like a head start. Access comes with several templates installed by default.
  • A template from Office.com    In addition to the templates that come with Access, you can find many more templates on Office.com. You don't even have to open a browser, the templates are available from the New tab.
Question: What are the data types in MS-Access?

Answer: Data types in MS-Access are:
1.      Text Use for text or combinations of text and numbers, such as addresses, or for numbers that do not require calculations, such as phone numbers, part numbers, or postal codes. Stores up to 255 characters. The FieldSize property controls the maximum number of characters that can be entered. 

2.      Memo Use for lengthy text and numbers, such as notes or descriptions. Stores up to 65,536 characters. 

3.      Number Use for data to be included in mathematical calculations, except calculations involving money (use Currency type). Stores 1, 2, 4, or 8 bytes; stores 16 bytes for Replication ID (GUID). The FieldSize property defines the specific Number type. 

4.      Date/Time Use for dates and times. Stores 8 bytes. 

5.      Currency Use for currency values and to prevent rounding off during calculations. Stores 8 bytes. 

6.      AutoNumber Use for unique sequential (incrementing by 1) or random numbers that are automatically inserted when a record is added. Stores 4 bytes; stores 16 bytes for Replication ID (GUID). 

7.      Yes/No Use for data that can be only one of two possible values, such as Yes/No, True/False, On/Off. Null values are not allowed. Stores 1 bit. 

8.      OLE Object Use for OLE objects (such as Microsoft Word document, Microsoft Excel spreadsheets, pictures, sounds, or other binary data) that were created in other programs using the OLE protocol. Stores up to 1 gigabyte (limited by disk space). 

9.      Hyperlink Use for hyperlinks. A hyperlink can be a UNC path or a URL. Stores up to 64,000 characters.

Question: How to set a primary key in MS Access?

Answer: Set a primary key:

 Open up an MS Access table in design view and highlight the ID field. In the Tools group of the ribbon you will see a big key icon with a label “Primary Key”. Click on this button.
Another way to add a primary key is to select a row by clicking on it and then right-click and choose the key icon.
Question: What is the process to entering data in MS Access?

Answer: Enter data in a table

  1. In the Navigation Pane, double-click the table that you want to use.
By default, Access opens the table in Datasheet view — a grid that resembles an Excel worksheet.
  1. Click or otherwise place the focus on the first field that you want to use, and then enter your data.
  2. To move to the next field in the same row, press TAB, use the Right or Left arrow keys, or click the cell in the next field.
When you press TAB, by default, Access uses your Windows regional settings to determine whether it moves the cursor to the left or to the right. If the computer uses a language that reads from left to right, the cursor moves to the right when you press the TAB key. If the computer uses a language that reads from right to left, the cursor moves to the left.
To move to the next cell in a column, use the Up or Down arrow keys, or click the cell you want.

Question: How to insert and delete record from MS-Access?

Answer:

Add a record by using the New button

  1. Click the Microsoft Office Button , and then click Open.
  2. In the Open dialog box, select and open the database.
  3. When the startup form appears, click the button provided for adding a new record.
  4. Fill in the form. Enter your information in each field, pressing TAB to move to the next field, or pressing SHIFT+TAB to move to the previous field.
  5. When you finish entering records, click OK to save the information or, if you want to add additional records, click Save and New, if it is available.

Delete a record

1.      Select the record or records that you want to delete.
To select a record, click the record selector next to the record, if the record selector is available.
To extend or reduce the selection, drag the record selector (if it is available), or press SHIFT+DOWN ARROW or SHIFT+UP ARROW.
2.      Press DELETE.
-or-
On the Home tab, in the Records group, click Delete.

Question: How to insert and delete fields from MS-Access?

Answer:

Add a Text field to an existing table
  1. Click the Microsoft Office Button, and then click Open.
  2. In the Open dialog box, select and open the database.
  3. In the Navigation Pane, double-click the table that you want to change.
Access opens the table in Datasheet view.
  1. If necessary, scroll horizontally to the first blank field. By default, Access displays Add New Field in the header row of all new fields.
  2. Double-click the header row, and then type a name for the new field.
  3. Select the first blank row under the header, and then type a block of text or a combination of text and numbers. You can enter a maximum of 256 characters. Access infers the Text data type for the field when you enter text or a mix of text and numbers, and you enter no more than 256 characters. If you enter more than 256 characters, Access infers the Memo data type.
-or-
Paste up to 256 characters of text data into the first row.
  1. Save your changes.
Delete a Text field in Datasheet view
  1. Click the Microsoft Office Button, and then click Open.
  2. In the Open dialog box, select and open the database.
  3. In the Navigation Pane, double-click the table that you want to change.
Access opens the table in Datasheet view.
  1. Locate the Text field, right-click the header row (the name), and then click Delete Column.

Question: What is field size and How to set it in MS-Access?

Answer: You can adjust the amount of space that each record in a table uses by changing the field size property of number fields in the table. You can also change the field size of a field that stores text data, although this action has a smaller effect on the amount of space that is used.

Change the field size of a number field

1.      Right-click the table that contains the field that you want to change, and then click Design View.
2.      In the table design grid, select the field for which you want to change the field size.
3.      In the Field Properties pane, on the General tab, enter the new field size in the Field Size property. You can choose from the following values:
o    Byte — For integers that range from 0 to 255. Storage requirement is a single byte.
o    Integer — For integers that range from -32,768 to +32,767. Storage requirement is two bytes.
o    Long Integer — For integers that range from -2,147,483,648 to +2,147,483,647. Storage requirement is four bytes.
Tip   Use the Long Integer data type when you create a foreign key to relate a field to another table's AutoNumber primary key field.
o    Single — For numeric floating point values that range from -3.4 x 1038 to +3.4 x 1038 and up to seven significant digits. Storage requirement is four bytes.
o    Double — For numeric floating point values that range from -1.797 x 10308 to +1.797 x 10308 and up to 15 significant digits. Storage requirement is eight bytes.
o    Replication ID — For storing a GUID that is required for replication. Storage requirement is 16 bytes. Note that replication is not supported using the .accdb file format.
o    Decimal — For numeric values that range from -9.999... x 1027 to +9.999... x 1027. Storage requirement is 12 bytes.

Question: What is default value in MS-Access?

Answer: You add a default value to a table field or form control whenever you want Access to enter a value in a new record automatically. For example, you can have Access always add the current date to new orders.

 Typically, you add a default value to your table fields. You add the value by opening the table in Design view and then entering a value in the Default Value property for the field. If you set a default value for a table field, Access applies your value to any controls that you base on that field. If you don't bind a control to a table field, or you link to data in other tables, you set a default value for your form controls itself.

Set a default value for a table field

When you set a default value for a table field, any controls that you bind to that field will display the default value.

Set a default value

1.      In the Navigation Pane, right-click the table that you want to change, and then click Design View.
2.      Select the field that you want to change.
3.      On the General tab, type a value in the Default Value property box.
The value you that you can enter depends on the data type that is set for the field. For example, you can type =Date() to insert the current date in a Date/Time field. For examples of default values, see Examples of default values, later in this article.
4.      Save your changes.

Question: How to create a table query on design view?

Answer:

Create a make table query

You use a make table query when you need to copy the data in a table or archive data. A make table query is a select query that runs and then creates a new table based on the results. If you already have a select query you want to use as the basis for a make table query, use the procedure shown here. If you need some more information before you get started, go to the section Learn about make table queries, below.
 
1.      Open your select query in Design view, or switch to Design view.
2.      On the Design tab, in the Query Type group, click Make Table.
The Make Table dialog box appears.
3.      In the Table Name box, enter a name for the new table.
-or-
Click the down-arrow and select an existing table name to replace that table.
4.      Choose whether to place the new table in the current database or in another database.
5.      Click OK.
6.      Click Run , and then click Yes to confirm the operation.

Labels: ,