DATABASE CONCEPT

DATA 
The Latin word DATUM which known as DATA in English language. The collection of fact and figure is called data. OR any thing which is in raw form or non-decision-able. OR action taking is called Data, e.g. Name, Address, Number, Phone #, Roll No. etc.
Example:
when student fill College Admission Form, the form consists of raw facts about students. These raw facts are student name, father's name, marks obtain etc.

TYPE OF DATA
There different types of data.

1. Alphabetic Data Type:
                                        This data type is consists of letters from A-Z or a-z. e.g. PAKISTAN, Ali, Peshawar etc.
2. Numeric Data Type:
                                        It consists of digit from 0-9, e.g. 123, 567, etc
3. Alphanumeric Data Type:
                                        It consists on alphabetic letters asa well as digit. Street no. A/10 etc
4. Graphic Data Type:
                                        It consists table, chart, graphics and statements.
5. Audio Data Type:
                                        It consists only sounds, for example radio news.
6. Video Data Type:
                                       It consists photos, images and moving picture, such as TV news etc.
7. Mixed Data Type:
                                       It consists more than one type of data, such as the combination of Audio & Video data types.

WHAT IS DATA BASE 

A database is an integrated collection of data that is shared by all organisation users or staff. It is a large collection of data in a computer system, organised so that it can be expanded, updated and retrieved rapidly for various users. In the database management approach, data recor5ds are consolidate into database that can be accessed by many different application programms. An important software packages called a Data Base Management System (DBMS) serves as software interface between users and database. This helps users easily access the records in a database. Thus, database management involves the use of database management software to control how databases are created, interrogated, and maintain to provide information needed by end user and their organisation.

According to "Martin (1981)", "A data base is a shared collection of interrelated data designed to meet the needs of multiple types of end users".

Database has two properties.

1. It is Integrated
2. It is Shared.

By integrated we mean that previously distinct data files have been logically organised to eliminate redundancy and facilitate data access. By shared we mean that all the qualified users in the organisation have access to the same data for use in a variety of activities.

A data base is not only shared by multiple users but it is perceived differently by different users. 

The data stored in a database is independent of the application programms using it and of the type of secondary storage devices on which it is stored.

WHY USE ELECTRONIC DATABASE 

We use electronic database to use, store, retrieved, processed and communicate data electronically.

Electronic Database management reduces the duplication of data and integrates data so that they can be accessed by multiple programms and users. Programms are not depending on the data and type of secondary storage devices. Users are providing with an inquiry / response and reporting capabilities that allow them to easily obtain information they need. Information they needed with out having to write computer programm. The integrity and security of the data store in database can be increased, since access to data and modifications of database are control by database management software, a data dictionary and database administrator function.

TABLES AND RELATION 

1. TABLE:
   A table is a collection of data. Tables organised data into columns (called fields) and rows (called records).
2. RELATION:
   The relation is an association between common fields (column) in two tables.

TYPES OF RELATION (ASSOCIATION)

There are three types of relation ship, which are the following:

1. ONE TO ONE RELATION
   A one to one association from A to B means that for the specified period of time for a given value A there is associated a one and only one values of B. It can be represented as:
   
   Suppose we have data items REG-NO of students and STUDENTS NAME. A one to one association from data items REG-NO to data item STUDENTS NAME means that for specified period of time, the given value of REG-NO has one and only one value of STUDENT-NAME associated with it.
2. ONE TO MANY RELATION
   These states that for a given value of A there will be many values of B associated with it
   
                               Deptt:       ====>>========>                  Employees:
   
   In the above figure we have two data item Deptt: and Employee. A one to many associations from the data item Deptt: to data item Employee means that a Department has many employee. Double-headed arrows represent a one to many associations.
3. MANY TO MANY
   It states that at any given times there exist more than one values of B associated with the given value of A, and also for given value of B there are associated many value A.
   
                               Student  <====<===========>===>  Teacher
   
   In the figure we have two data items student and teacher. Many students have many teachers.

DATA BASE FEATURE

1. ATTRIBUTE, CHARACTER, FIELD, RECORD, FILES, TUPLES
   
   ENTITY:
   Any thing about which we can store data is called entity, for example students, city, books, college, board etc.
   • Attribute:
     The characteristics of any entity or the column of table is called Attribute, for example a student attribute are Roll No, Name, Father's Name, Age, Colour etc.
   • Character:
     The smallest piece of information is called character. There are three types of character.
   1. Numeric that is from 0 to 9
   2. Alphabetic that is from A - Z capital or small a - z.
   3. Special character that consist upon +,-,=,",;", <,> etc.
   • Field:
     The combination of related characters is called field that is name, address, city, ph-no, age, roll-no etc, for example Peshawar, Pakistan, Khalid, Imran, 2300, 20 years, etc.
   • Record:
     The combination of related field is called record that is payroll record for a person, student admission record for a student etc.
     For example student record.
     
     Roll No            Name                 Age            Qualification                   Address
     01                    Khalid Khan       25               D-COM                        Peshawar
     02                    Imran Gul           26                B-COM                       New Yark
     In above example Roll No, Name, Age, Qualification, & Address are field.
   • Tuples:
     The row of record is called Tuple.
2. KEY, PRIMARY KEY, FOREIGN KEY, COMPOSITE KEY


• Key: A key is data item used to identify a record.
• Primary Key:
  It is key or data item that uniquely identifies record, Primary Key is an identifies, for example a student number would be the primary key for student record.
• Secondary Key:
  A secondary key is data item that normally does not uniquely identify a record but identifier a number of record in set that be used as a secondary key for STUDENT record.
• Foreign Key:
  It is primary key field in one table while used as a foreign key in another table. It is used for linking two tables, Example.
  Table1: Customer Number, Cname, Caddress, Cphone, (here customer no is a primary key).
  Table2: Loaned Amount, Date of Issue, Custer No. (here loaned is primary key and customer no a foreign key).
• Composite Key / Concatenate Key:
  A primary key that consists two or more attribute is known as composite key, for example the following relation uses two fields. Registration No. and subject to identify each tuple. This is an example of composite key.
  
  
3. REFERENTIAL INTEGRITY
   Referential integrity is a constraint on foreign key. It state that if a foreign key exist in a relation, the foreign key value must match the primary key value of some tuples in its parent relation. Referential integrity is uses to ensure that relationships between records in related tables are valid and that you don't accidentally delete or change related data.
4. USE OF WILD CARDS
   Wild Card characters as placeholders for other characters when you are specifying a value you want to find and know only part of the value.
   To find values that start with a specific letter or match a certain. In a Microsoft Access Database, you can use the following characters in the Find and Replace dialog boxes, or in queries commands, and expression, to find such things as field values, records, or file names.
   
   

MICROSOFT ACCESS (DATA BASE)
What is MS-Access?
Ms-Access is relational database management system (RDMS). Access stores and retrieves information, present requested information and automates repetitive tasks. With access you can create easy-to-use input forms. You can display your information in any way that you want and run powerful reports.
Use of MS-Access:
Ms-Access is used to store and manipulate a large amount of information. It is very easy to understand for users. Its graphical interface helps the users to create queries, forms, and reports easily. Even an inexperienced programmer can use Ms-Access to perform different activities. He can turn the invoices, files of customer names, ledger and inventory lists into relational database. The process of entering, updating and reporting information become very easy.
The Access Window:
When you first open Ms-Access, you see the Ms-Access Window showing a title bar with the Ms-Access caption and its menu bar and toolbar. The access window is the center of activity for everything that you can do in Ms-Access. From this window, you can open many other windows simultaneously each window display a different  look.

TITLE BAR
Title Bar is horizontal area located at the very top of the screen. The Title Bar display the name of Microsoft Access, the control menu and the minimize and maximize and close buttons are also contained by Title Bar.

CONTROL MENU BUTTON
Control Menu Button
This button is found in the upper left corner of the Title Bar. When you click this button, a menu appear that lets you perform certain tasks, including moving, sizing, minimizing, or closing the current application window.

MENU BAR
The Menu Bar is directly below the Title Bar and it display the menu. A menu displays a list of commands.

TOOL BAR
The Tool Bar is a group of picture, button just below the menu bar, it provide shortcut for running command.
Tool Bar provides shortcuts to menu commands. Tool Bars contain different tools. Tool Bars are located just below the Menu Bar.

STATUS BAR
The Status Bar is a horizontal area at the bottom of the Ms-Access window.
The left side of the Status Bar, displays Ready. The right side of the Status Bar tells you whether certain keyboard settings are active.
For example, if you have the Caps Lock feature turned on, the word CAPS appears in the Status Bar.

DATABASE WINDOW
This window appears whenever a database is open; it is the control center of your data base. You use the Database window to open the objects within a database, including table, queries, forms, reports, macros, and modules.
The Database window consists of three basic parts: A set of seven object buttons in a vertical row on the left side, a set of eight toolbar buttons along the top of the window, and a list of object names in the right pane.
Object Buttons:
These buttons are located in a vertical row along the left side of the Database window. Using these buttons, you can select the type of object you want to work with, for example, selecting the Form button displays a list of Form created for that database. Selecting this button also lets you create a new form or redesign an existing one.
Tool Bar Button:
You use the toolbar buttons, which are located along the top of the Database window, to change a database object in a different window or view. These buttons let you create, open or design a database object and view certain details about those database objects.
Object List:
This list displays existing objects for the database object that you select. You can choose a name from the list to display or redesign the object. You can also select what type of view you want for these objects, for example, you can view the details about your database objects, such as description, date modified, date created, and type. You can change the view of the objects in the Object List by selecting View from the Access window menu bar or by using the buttons on top of the database window (the last four buttons on the right side of the database window).
Explain the Different Object Button

1. Table:
   It is used to create, modify and manipulate tables. The tables are used to store data in the database.
2. Queries:
   It is used to create Queries, Queries are used to retrieve data from database.
3. Form:
   It is used to create Form. Forms are used to enter data in tables easily using graphical user interface. The Form consists of button, text boxes and lists etc.
4. Report:
   It is used to create Reports. Reports are used to display the information from database in different ways. The reports are used to make important decisions. The report can be based on tables or queries.
5. Pages:
   It is used to design data access pages. The data access pages are used to display the Ms-Access data on the web.
6. Macro:
   It is used to create Macro. The macro are used to perform same sequence of steps quickly and automatically. It automates different tasks repeatedly.
7. Modules:
   It is used to create Modules. A Module contain an object that stores the code of VBA (Visual Basic for Application).

EXPLAIN DIFFERENT DATA TYPES AVAILABLE IN MS - ACCESS

Ms-Access provides the following data types:

1. Text:
   Text data type is used to store alphabetic, numbers and special characters. It can store up to 255 characters. It the length of text field is not specified, the default setting is 50 characters.
2. Memo:
   Memo data type is used to store lengthy text. It is normally used to store comment etc. Memo fields can hold up to 64,000 characters.
3. Number:
   Number data type is used to store numeric data. The fields with Number data type can be used in mathematical calculation. Different types of numeric data types are as follows:
   Byte, Integer, Long Integer, Single, Double.
   The Byte, Integer and Long Integer data type cannot store decimal values. If you need to store values with up to four decimal places only, use Currency data type in preference to Single or Double lumber fields.
4. Date / Time:
   Date / Time data type is used to store date and time. MS-Access stores date in the standard format (mm/dd/yyyy).
5. Currency:
   Currency data type is used to store numbers representing Currency. The value in Currency field is rounded to two decimal places. The negative currency values are display in brackets. The values automatically include a dollar sign ($).
6. AutoNumber:
   AutoNumber data type is used to generate the next number automatically when a new record is added. It creates a unique number for each record. The value starts from 1 and is incremented by 1 in each records.
7. Yes / No:
   Yes / No data type is used to store Boolean value. The possible values in this field are True and False.
8. OLE Object:
   OLE Object stands for Object Linking and Embedding. This data type is used to enter objects from-other applications such as a spreadsheet created in Ms-Excel or a picture file.
9. Hyperlink:
   The Hyperlink data type is used to store links to other files, document or web pages in a field.

WIZARD AND TEMPLATE

1. Wizard:
   Wizard consists a series of dialog boxes that ask you design and contents questions relating to the type of presentation you which to create.
2. Template:
   A Template is pre-designed database that comes with MS-Access.

HOW TO MS-ACCESS IS STARTED OR LOADED: 

The following procedure can be used to start or load MS-Access.

1. Click Start button on task bar.
2. Point to the Program menu.
3. Click on Microsoft Access.

EXPLAIN THE PROCEDURE FOR CREATING TABLE IN MS-ACCESS 

A table is created in a database. The option Create Table in Design View is the most common ways of creating table. Different methods of creating a table are as follows:

1. Creating table in Design View.
2. Creating table by Wizard.
3. Creating table by Entering Data.

WHAT ARE TABLE VIEW AVAILABLE IN MS-ACCESS OR HOW CAN SWITCH BETWEEN THEM 

Table view is a way of looking at the table. Microsoft Access provides two table views that are design view and datasheet view.

1. Design View:
   The table view that is used to design the structure of a table is called Design View. It is used to specify name, data types, and description of fields. Primary Key is also specified or modified in this view. The structure of an existing table can also be change in design view.
2. Datasheet View:
   The table view that is used to enter, delete or modify data in a table is called Datasheet View. The table in this view is displayed in rows and columns. The name of each field is displayed at the top of the column as header. Each row contains a complete record. The table or query is opened in datasheet view to perform different operations on data in the table. These operations include adding, deleting, updating and searching data etc.

Switching Between Views 

Following procedure is used to switch between table views:

1. Click on View menu.
2. Select Datasheet View or Design View from the menu.

The table will be displayed according to the selected view option.

CREATING TABLE IN DESIGN VIEW AND WIZARD

Creating Table by Design View:

The design view is used to define the fields of a table. The window is divided into two parts. The following procedure is used to create a table by using Design View.

1. Creating new blank data base.
2. Double click Create Table in Design View. The Design View will appear.
3. Type the name of the first field in Field Name column.
4. Press tab key to move to the Data Type column, select the required data type for the field.
5. Press Tab key to move to Description column, which is used to enter comments about the field. This is optional.
6. Press Tab key to move to the Field Name for the next field.
7. Repeat the above steps for entering any number of fields in the table.

How would you Setting Primary Key: 

The following procedure is used for setting Primary Key.

1. Select the field for Primary Key.
2. Click Primary Key on Table Design Toolbar.
   OR
   Select Edit > Primary Key. The row that is chosen as Primary Key is marked will small symbol in selector button.

How You Save A Table: 

The following steps are performed to save a table

1. Click Save on Table Design toolbar, OR Select File > Save The Save As dialog box will appear
2. Enter a Table Name and Click OK. The table will saved.
   The new table will appear in the main database.

Method to Close the Database: 

1. Click on File menu from menu bar.
2. Click on Close option.
   
   OR

1. Press  Ctrl + F4.

Steps to Exit MS-Access 

1. Click on File menu from menu bar.
2. Click on Exit.
   
   OR

1. Press Alt + F4. 

Write the steps to Add Record in a Table: 

This procedure is used to add new record to the table in data sheet view. The new data is typed in the record that has an asterisk (*) on left side. It indicates the new record.

How to Edit Record in a Table: 

This procedure is used to edit record of the table in data sheet view. Edit existing record by placing the cursor in the record to be dited and making necessary changes. The arrow keys are used to move through the record grid. The previous, next first and last record buttons at the bottom of datasheet are helpful in editing the datasheet.

How Delete Record From A Tablwe: 

this procedure is used to delete a record from a table in datasheet view.

1. Placeing cursor in any field of the record to be deleted.
2. Click on Edit menu from menu bar.
3. Click Delete Record.
   
   OR

1. Placing cursor in any field of the record to be deleted.
2. Click delete Record button on the datasheet Toolbar.
   
   OR

1. Click right mouse button on record selector at the left of the record that you want to delete.
2. Press Delete key.
3. Click Yes button.

How Insert or Delete Field: 

A better method to add new fields in the use of design view. The design view provides more option to add fields quickly.

The following procedure is used to Insert a New Column or Field in the design view.

1. Select the Table in the Database Window.
2. Click on the Design Icon button to open the table in Design View.
3. Click on the first empty row in the Field Name column.
4. Type the name of the Field and select their Type.
   
   OR

1. Place the cursor where you want to insert new Field.
2. Click Right Mouse Button on the top of the field, a drop down menu will be appear.
3. Click Insert Row.

The following procedure is used to Delete a Column / Field in the Design View. 

1. Select the table in the Database Window.
2. Click on the Design Icon button to open the table in Design View.
3. Click Right Mouse Button on the top of the field, a drop down menu will be displayed.
4. Click  Delete Row.

RESIZING ROWS AND COULUMNS 

1. ROW SIZE:
   The hight of rows on a datasheet can be changed. The user can drag the gray sizing line between row labels up and down with the mouse. The hight of the rows is changed if there is any change in the hight of any row.
2. COLUMN SIZE:
   The column width on datasheet can be changed. the user can drag the sizing line between columns. The user can double click on line expand the column according to the longest value in the column. Different column on a datasheet can have different widths. The exact values can be assigned from Format > Row Height OR Format > Column Width from menu bar.

Procedure for Freezing & Hiding Column: 

Freezing Column: 

The user can freeze the columns on an Access table. It helps the user view certain columns easily it the datasheet has many column and required columns are not visible.

Follow the following steps to freezing the columns:

• Placing the cursor in any record in the column to be freeze.
• Select Format > Freeze Columns from the menu bar.

The option Format > Unfreeze can be used of unfreeze a single column.

Hiding Columns
The columns can be hidden from datasheet temporarily. The hidden columns are not be deleted from the database. The following procedure is used to hide column:

• Place the cursor in any record in the column to be hide Or highlight multiple adjacent columns by clicking and dragging the mouse along the culumn header:
• Select Format > Hide Columns from the menu bar.
  The option Format >Unhide Column can be used to unhide the columns.

Procedure for Finding Data: 

Microsoft Access provides facility to find the required data easily. The following procedure is used for finding data:

• Open the table in Datasheet View.
• Place the cursor in a field to Find Data.
• Choose Find from Edit menu.
• Enta the data to find in Find What text box.
• Select any option from Match list box.
• Click Find Next button. If the data enter in Find What text box.is found in the field, the cursor will move to that record.

If the data is not found, a message will appear showing that the data is not found.

Procedure for Replacing Data: 

Microsoft Access provides the facility to find and replace data easily. Replaceing data consists of the following steps:

• Place the cursor in a field to Find Data.
• Click Replace from Edit menu or press Ctrl + H.
• In the Find What box enter the text you want to replace.
• Select any option from Match lish box. It is used to watch entered with whole field, any part of field or start of field.
• Enter the data to replace in Replace With text box. 
• Cllick Find Next button. If the data entered is found the cursor will move to that record.
• Click on the Replace button to replace the search string. OR click on Replace All button to replace all matching data in the table.

Use of Check Spelling & AutoCorrect Option:
The spell checker can be used to indicate spelling error in text and menu fields in a datasheet. The following procedure is used to check spellin:

• Select Tools > Spelling from the menu bar to activate spell checker.

The AutoCorrect feature can automatically correct common spelling errors. Some common correction are as follows:

• Two intial capital such as HEllo WOrld.
• Capitalising the first letter of the first word of sentence.
• Anything option defined by the user.

It can be activated from Tools > AutoCorrect option.

Describ the Method of Printing a Datasheet: 

The following procedure is used to print a datasheet.

• Click Print button on the Toolbar OR Select File > Print to set more Printing Options.

Describe the Procedure of Defining Multiple Primary Key: 

The following procedure is used to define Composite Primary Key:

1. Move the mouse over the gray column next to the field, names and note that it becomes an arrow.
2. Click the mouse, hold it down and drag it over all fields that should be primary keys and release the button. Click the Primary Key button.
3. Click the Primary Key button.

Creating Table by Using Wizard:

MS-Access table wizard privides an easy way to create tables. It provides various table templates to create business and personal tables. The Wizard helps the user to create common types of tables to manage mailing lists, recepes, investments and video collection etc.

The following procedure is used to create a table using wizard.

1. Open Database window.
2. Click Tables in Object bar.
3. Double click create table by using Wizard. The table wizard will start.
   The sample table include with access are grouped into two categories. Business and Personal.
4. Select appropriate category and type of table to be created. 
5. Select appropriate fields for the table. The four video controls are used to perform the following operations.
   
6. Select the field to rename.
7. Click on Rename Field, button. The Rename Field dialog box will appear.
8. Enter new name of the field and click OK.
9. Select appropriate fields and click Next >.
10. The default name of table is the name of first sample table. It can be changed.
11. The wizrd also prompts to select a primary keyy for the table. 
12. Click Next >.
13. The final screen of wizard offers the choice to modify table design, enter data in table in datasheet view or create a form, to use to enter data.
14. The user screen also choose to display an appropriate help topic, that gives advice on entering and modifying data.
15. Select any options.
16. Click Finish.

CREATE RELATIONSHIPS BETWEEN TABLES

RELATIONSHIP: 

A relationship is a logical connection between different, entities. A relationship is established on the basis of interaction among these entites. For example, a relationship exists between a Student & Teacher because the teacher teaches the students.

CREATING RELATIONSHIP: 

The procedure for Creating Relationship between tables is as follow:

1. Start Microsoft MS-Acces.
2. Select File > New from menu. A dialog box will appear. 
3. Choose Database option and click OK.. A new dialog box will appear.
4. Enter the name of new database and click Create button. A new database will be created and the main window will appear.
5. Double click on Create table in Design View option. The Table window will appear.
6. Create a table.
7. Set the Field RegNo as primary key and save the table with StdMaster name.
8. Create the second table with the following fields.
9. Set fields RegNo and Subject as primary key and save the table as StdDetal.
10. Select tools > Relationship. The following dialog box will appear:
11. Select StdMaster table and click Add.
12. Select StdDetail table and click Add.
13. Click Close button to close dialog box. The relationship window will appear.
14. Click on RegNo in StdMaster table, drag it on RegNo StdDetail table and release the mouse. A dialog box will appear.
15. Select Enforce Referentail Integrity checkbox and click Create button... Both tables will be joined by one to many relationship.

CREATION OF QUERIES
QUERY 
A query is a statment that extracts specific information from database. A query is created by specifying fields to display from a table or another query. It can also specify criteria on One or more fields for extracting data.
The queries can select records from one or more tables in a common datasheet. The resulting collection of record is called a dynaset. The dynaset stands for dynamic subset. It is saved as a database object and can be easily used in future. The query is updated whenever the original tables are updated.
Use of Query:
A query can be used for the following purposes:

1. Extract records according to the Specifies criteria.
2. Choose the fields to display in the result.
3. Sort the record specific order.
4. Calculate fields and summarise data.

Advantages 

Some important advantages of using query are as follows:

1. Flexibility:
   Query is a flexible way of manipulating data in databases. It provide different facilities to add, deleted, modify and scearch data easily.
2. Joining:
   Query can be executed against mutiple tables. If the tables are related with, one another, the query can join these tables for extracting data.
3. Ease of Use:
   Queries are very easy to use. The user can execute queries easily to perform deferent operations on the database.

TYPES OF QUERIES
There are different types of queries. The Select Queries extract dat from tables based on specified values. The Find Duplicate queries display records with duplicate values for one or more of the specified fields. The Find Unmatched queries display records from one table that do not have corresponding values in a second table.
In general different types of queries are as follow:

1. Select Queries.
2. Action Queries.
3. Crosstable Queries.
4. Parametk Queries.
5. SQL Queries.

1. Select Queries:
   A select query is used to extract data from tables based on specified values. It gathers information and presents it in usable form. It may retrieve data from one or more tables. It displays the results in datasheet where the records can be updated. The select query can be used to group records and calculate sums, counts, averages and other types of totals.
2. Action Queries:
   An action query is used to make changes in specified records on an existing table. It is also used to create a new table. There are four types of Action Queries.
• Delete Query:
  It is used to delete a group of records from one or mare tables.
• Update Query:
  It is used to make changes to a group of records in one or more tables.
• Append Query:
  It is used to add a group of records from one or more tables to the end of one or more tables.
• Make Table Query:
  It is used to create a new table a copy the selected records in it.
3. Crosstable Queries:
   The corsstable query is used to calculate and restructure data for easier analysis. It calculates a sum, average, count or otherr types of computation of data. These queries are grouped by two types of information. One is down the left side of datasheet and the other is across the top of datasheet.
4. Parametic Queries:
   A parametic query displays a dialog box when it is executed. It gets some information from the user as parameter. These queries are used as basis for creating forms and reports.
5. SQL:
   SQL stands for Structured Qurey Language. Query mean user request where user write a statement to extract a specific data from table.

PROCEDURE TO CREATING SIMPLE QUERY:
The procedure for creating simple query is as follow:

1.  Open the databse.
2. Click on Queries button in main database window.
3. Double click Create Query in Design View.
4. Select Query window will appear. It will contain another dialog box Show Table.
5. Select the table for extracting data.
6. Clcik Add button.
7. Click Close button. The Select Query window will appear. It contains the selected table in the upper part. The bottom part contains different options for creating quer.
8. Select any field from Field list box to include it in the query in the bottom of window.
9. Add all other fields that are to be included in the query.
10. Select the sorting order from Sort list box.
11. Give any condition in Criteria field if required.
12. Click on save button on toolbar to save the query. Or select File > Save. The Save As dialog box appear.
13. Type the name of query.
14. Click OK. The query will be saved 
15. Select Run from Query, menu to execute the query and view its result.

PROCEDURE FOR EXECUTING A SAVE QUERY 
A query that has been saved on the disk can be executed later. The following procedure isused to execute a save query.

1. Open the database that contain the saved Query.
2. Click Quries botton in the Object list. The name of all save queries will appear.
3. Double click any Query name. It will be executed and result of the query will appear on the screen.

CRITERA IN QUERY & HOW IT SPECIFIED 

A condition used to limit the number of rows extracted from database is called criteria. If a query contqains any criteria, it retrieves only those records that match with the specified criteria. Criteria may be specified to extract the records of only those students who got more than 700 marks. Similarly; criteria can be used in a query if the user wants to view only those students who live in Peshawar etc.

Specifying Criteria: 

Criteria is specified with the help of wild card. Wild Cards are special symbols that are used to extrat particular records from databas.

WHAT IS JOIN & IT'S PUPOSE

A query that extracts data from multiple related tables is called join. A join uses the relationship of the tables for extracting data from the tables. A join searches the required records in the first table and then searches the corresponding record in the second table.

DIFFERENCE BETWEEN RELATIONSHIP & JOIN 

1. Relationship:
   A Relationship is a logical connection between different entities. A Relationship is a query extablished on the basis of interaction amoung these entitites.
2. Join:
   A Join is a query that extracts data from multiple related tables. A Join uses the relationship of the tables for extracting data from the tables.

PROCEDURE TO EXTRACT DATA FROM MULTIPLE TQABLES

The procedure to extract data from multiple tables is as following:

1. Open the database. 
2. Click on Queries button in main database window.
3. Double click Create query in Design View. Select Query window will appear. It will contain another dialogbox show.
4. Select the first table for extracting data (e.g. Class).
5. Click Add button.
6. Select the second table (Phone).
7. Click Add button.
8. Click Close button. The Select Query window will appear. It contains the selected table in the upper part. The bottom part contains different options form creating query.
9. In the bottom part of the window, select any field; from field list box to include it in the query. Field list box will contain, the field of both tables.
10. Add all other fields that are to be included in the query. 
11. Select the sorting order from Sort list box.
12. Give any condition m Criteria field if required.
13. To save the query, click on save button on toolbar. OR 
14. Select Save from File menu. The Save As dialog box will appear.
15. Type the name of Query.
16. click OK. The Query will be saved.
17. Select Run from Query menu to execute the query and view its result.

USE OF QUERY WIZARD WITH EXAMPLE

The Query Wizar in MS-Access helps the user to create a select query easily. It consist of simple steps to create query. The following procedure is used to create a select query susing query wiazrd:

1. Click Create Query by Using Wizard icon in database window to start query wazrd.
2. Select the fields to be included in the query from the first window. Selecting the Table > from Tables / Queries meny. Select the fields by clicking :> button to move the fiekd from Availble Fields list to Selected Fields. Click Double Arrow Button >> to move all of the fields to Selected Field.
3. Select another table or query to choose from fields and repeat the process of moving them to Select Fields box.
4. Click Next > when all of the fields have been selected.
5. Enter the name for the query and click finsh.

CREATING FORMS

Form & its Uses Or Advantages: 

A window that consist of visual components is called Form. Forms are used to interact with databases through grphical user interface.

A form is constructed from a collection of individual design elements. These elements are called Controls or Control Objects, and radio button etc. Different elements are used for different purposes. A textbox is used to enter data a label is used to display message to the user. 

Use of Form: 

Form is used to mainipulate database easily. It can be used to 

1. Add Data in the database,
2. Modify data in the database.
3. Delete data from the database.
4. Retrieve and view data from the database.
5. Search the required data from the database.

Advantages of Form: 

Some advantages of using forms are as follow:

1. Easier to Use:
   Form can be used by the user very easily. It contains simple graphical components that simplify the process of the data mainipulation.
2. User Friendly:
   form contain graphical components that are user-friendly. The user can use these forms by simply clicking different with mouse. 
3. No Technical Knowledge Required:
   A user can use manipulate database without any technical knowledge of databases. The visual components enable the user to interact with the database withou writing technical statements.
4. Time Saving:
   In some situation forms require less time to enter data in tables. Access provides master-details form that is used to enter many records at one time.
5. Flexibility:
   MS-Access provides different types of form. These foms can display data in different styles.

TYPES OF FORMS IN MS - ACCESS

1. Columnar Form
2. Tabular Form
3. Datasheet
4. Justified Form

1. COLUMNAR FORM:
   Columnar form is used to display one record at a time. It displays text boxes and labels. The text boxes represents the fields of table or query. The labels represent the names of field.
   Columnar Form provides different bottons to navigate through different records at the bottom.
2. TABULAR FORM:
   Tabular Form is used to display many form at one time. It displays records as a table. Each row in this form displays one record of the table. The labels are displayed on the top of each column.
   Tabular Form provides different buttons to navigate throuugh different records at the bottom.
3. DATASHEET FORM:
   Datasheet Form is used to display many records at one time. It displays records in the datasheet view of MS-Access. Each row in this form displays one record of the table. The labels are displayed on the top of each column.
   Datasheet Form provides different buttons to navigate through different records at the bottom.
4. JUSTIFIES FORM:
   Justified Form is used to display one record at a time. The fields are justified according the form window. The lables are display on the top of each column.
   Justified Form provides different button to navigate through different records at the bottom.

CREATING A FORM BY USING WIZARD

The procedure to create form by using wizard is as follow.

1. Open database.
2. Click on forms butto in Object list.
3. Double click create form by using wizard. The wizard will appear.
4. Select any table from Table / Queries list box. The fields of the selected table will appear in  Available Fields box.
5. Click on any field to include in the form. 
6. click on > button. The field move to Select Field box one by one. OR Click on >> button to include all fields in the form.
7. Click on Next button. 
8. Select any lay out for the form.
9. Click Next button
10. Select any style for the form. 
11. Click Next.
12. Enter any title.
13. Select open the form to view or enter information option.
14. Click Finish button.

CREATING A FORM BY DESIGN VIEW

The following procedure is used to create a form by Design View.

1. Click New botton on the form database windows. 
2. Select Design View and choose table or query to which the form will be associated.
3. Select View > Toolbox from menu bar to view the floating toolbar with additional option.
4. Add controls to form by clicking and dragging field  names from  Field List floating window. MS - Acces creates a text box for the value and label for the field name. OR
   Double - click  Field List window's title bar and drag all of the highlighted firlds to the form to add controls for all of the fields in Field List.

PROCEDURE TO ADD NEW RECORD THROUGH FORM
The following procedure is used to add new records through forms:

1. Open the database.
2. Click on  Form button in Obje list.
3. Double click the form to open.
4. Click the New Record button. The fields of form will become empty.
5. Enter new data is the fields. 
6. Close the form. The data entered in field will be saved automatically.

PROCEDURE TO EDIT RECORD THROUGH FORM

The following procedure is uused to edit record through forms.

1. Open the database.
2. Click on Form button in Object list.
3. Double click the Form to open.
4. Go to the record to be edited by using navigation buttons at the bottom.
5. Change the contents of the fields.
6. Go the form. The changes will be updated automatically.

Different Option for Editing Form 

The following option are helpful for modifying forms in Design View.

1. Gridlines
   Ms-Access diplays a series of lines and dots in design view to align to align form elements easily. This option can be turned on and off by selecting View > Grid.
2. Snap to Grid
   It is used to align form object to the grid. The option can be turned on and off from Format > Snap to Grid.
3. Resizing Objects
   The form object can be resized as follows:
   Click and drag the handle on the edges and corners of the elements with mouse.
4. Change Form Object Type
   The type of tthe form can be changed easily without creating a new form. The form type can be changed as follow:
• Right click on the object with the mouse.
• Selecting chnge to option.
• Select an available object type from the list.
5. Lable / Object Alignment
   Each form object and its corresponding lable are bounded. Both move together when any of them is moved with the mouse. The user can change the position of the object and lable in relation to each other. The user can click and drag the large handle at the top, left  corner of the object or lable for the purpose.
6. Tab Order
   This option is used to alter the tab order of object on form. The user can change the order as follow:
• Select View ? Tab Order .......... from nemu bar.
• Click the gray box before the row whose tab order is to be change. 
• Drag it to a new location and release the mouse button.
7. Form Appearnce
   The Form Appearnce can be changed as follows:
• Click Fill/Back Color button Formatting toolbars and click any color on palette.
• Change the color of individual object by highlighting the object and selecting a color from Font/Fore Color palette on formatting. toolbar.
• The font and size, font effect, font alignment, border width and special effect can also modified using formatting toolbar.
• The font and size3, font effect, font alignment, border width and special effect can also modified using formatting toolbar.
8. Page Header and Footer
   Headers and Footers added to a form will only appear when it is printed. The option can be used as follows:
• Select View > Page Header / Footer on the menu bar.
• Select Insert > Page Number to add Page number to these selection.
• Select Insert > Date and Time to add date and time.
• Select View > Page Header and Footer to hide these section from view in Design View.

LIST BOX & COMBO BOX? & ALSO ADDING THESE OBJECT ON FORM
List box and combo box are used to display a list of item in forms. The user can select the desired item from available items. These objects are used if the possible val;ues are small and limited for a certain field in the form. The user can entered value quickly and easily by using these objects.
These two control types differ in the number of values they display. The values in a list box are visible until the user click on the arrow button on the combo box.
Adding List Box and Combo Box:
The following procedure is used to add a list box or combo box to a form:

1. Open the form in Design View.
2. Select View > Toolbox to view the toolbox. 
3. Make sure that Control Wizard button is pressed.
4. Click the list or combo box tool button and draw the outline on form. The combo box wizard dialog box will appear.
5. Select source type for the list or combo box values and click Next >.
6. The next option depends on the selected choice in the first dialog box. The box will  appear if the user selected look up values from a table or query.
7. Select the table or query from which the values of the combo box will come.
8. Click Next > and choose fields from the table or query.
9. Click Next >.
10. Set the width of combo box by clicking & drangging the right edge of column.
11. Click Next >.
12. The next dialog box tells Access what to do with the value that is selected. Choose "Remember the value for latter use" to use the value in a macro or procedure (the value is discarded when the form is closed) OR
    Selected the field in which the value should be stored.
13. Click Next >.
14. Type the name that will appear on the box;s label.
15. Click Finish.

CHECKBOX & OPTION BUTTON

Procedure:  

The check boxes and option buttons are used to display yes / no, True / False, or On / Off values. The user can select only one value from a group of option buttons. The user can select any or all values from a group of chec boxes. These controls are typically used when five or less options are available.

Adding Checkbox & Radio Button to Form 

The following procedure is used to add a checkbox or option group to a form:

1. Click Option Group tool on toolbox and draw the area where the group will be placed o form with mouse. The Option Group wizard dialog box will appear.
2. Enter lables for the options.
3. Click the tab key to enter additional lables.
4. Click Next > after finishing; lables.
5. Select a default value if there is any and click Next>.
6. Select values for the option & click Next >.
7. Choose what should be done with value and click Next >.
8. Choose the type and style of the option group and click Next >.
9. Type the caption for the group and click Finish.

COMMAND BUTTON 

 A command button is used to execute different commands by clicking on it. The caption of the command button indicates the type of command execute by the button.

Adding Command Button: 

The following procedure is used to add a command button to a form.

1. Open the form in Design View.
2. Ensure that Control Wizard button on the toolbox is pressed.
3. Click the command button icon on toolbox and draw the button on the form. The Command Button Wizard will appear.
4. The action categories are displayed in the left list on first dialog window. The right list displays the actions in each category.
5. Select an action for the Command Button and click Next >.
6. The next few pages of options will vary based on the selceted action. Continue selecting option for Command Button.
7. Choose the appearances of button by entering caption text or selecting a picture.
8. Check Show All Picture box to view full list of available image.
9. Click Next >.
10. Enter name for the Command Button and click Finish to create the button.

REPORT

Reports are the output of a database application. The user can generate different type of report by manipulating the database. The information on the report is in different styles. The reports may contain graphs and charts etc.

The user cannot edit the data displayed on the reports. The user also cannot input data in reports. The reports are generated for printing purpose. The can also be displayed on screen and stored on the disk.

Uses of Reports

The reports are basically used for the following purposes:

• Reports present the requried information in formatted style.
• Reports provide flexibility to present the same data in different ways.
• Reports can display information with graphics and charts etc.
• Reports are very important in making important decisions.
• Reports can be used to improve the database application.
• Reports can display the result of a query.

Standard Types of Reports
The standard types of reports in MS Access are as follow:

1. Columnar Reports:
   The Columnar reports display the values of each field in each record of a table or query in one long column of text boxes. The lables indicate name of the field. The text box to the right of lable provides the values. The Columnar Report spreads the information for a single record over many rows.
2. Tabular Report:
   The Tabular Reports provide a column for each field of the records in rows under column header. Additional pages are printed in sequence if the columns do not fit on one.

How Auto Report is used to Generate a Report?
Auto Report create columnar report based on specified table or query. The following procedure is used to create a report using auto report.

1. Select the table or query from the database window.
2. Select auto report from New Object Button on Database toolbar.
   ORSelect Insert > AutoReport. The report is automatically displayed in Print View.

The format of the report output is very basic. MS-Access does not provide any formatting of the page header or footer. The user can modify the report in Design View.

Save & Close AutoReport: 

The AutoReport is not saved to the database automatically. The following procedure is followed for saving the AutoReport.

1.  Select File > Close. You are prompted to save the report.
2. Click Yes. The Save As dialog box will appear.
3. Enter a name & click OK. The report is saved to the Database Window.

The format of the report output is very basic, MS-Access does not provide any formatting of the page header or footer. The user can modify the report in design-view.

Creating Single - Table Report by Report Wizard 

The following procedure is used to create a single - table report using report wizard.

1. Open a database.
2. Click on Reports button in Object list.
3. Double click Create Report by using Wizard. The report wizard will appear.
4. Select a table from Tables / Queries list box. The fields of the selected table or query will appear in Available Fields box.
5. Click on any field to include in the report.
6. Click > on button. The field will move to Selected Field box. OR
7. Click >> button to include all fields in the report.
8. Click on Next button.
9. Select any field to specify grouping level if necessary. 
10. Click Next button. 
11. Select any field accourding to which data record will be sorted in report.
12. Click Next button.
13. Select layout option and orientation Option.
14. Click Next button.
15. Select any style for the report.
16. Type the name of the report.
17. Click Finish Button. The report will appear.

Procedure for Creating Two-Table Report by Report Wizard 

The following procedure is to create a two-table report using report wizard.

1. Open a database.
2. Click on Report button in object list.
3. Double click Create Report by using wizard. The report wizard will appear.
4. Select a table from Tables / Queries list box. The fields of the selected table or query will appear in Available Fields box.
5. Click on any field to include in the report.
6. Click on > button. The field will move to selected Field box. OR
7. Click on >> button to include all fields in the report.
8. Select the second-table from Table / Query list box.
9. Move the fields of second table in Selected Fields box.
10. Click on Next button.
11. Select any field to specify grouping level if necessary.
12. Click Next button. The next window will appear.
13. Select any field according to which data record will be sorted in report.
14. Click Next button.
15. Select Layout option and orientation option.
16. Click Next button.
17. Select any style for the report.
18. Type the name of the report.
19. Click Finish button.

Creating Report by Design View 

The following procedure is used to creating a report in Design View.

• Click New button on Reports Database Window.
• Highlight Design View and choose the data source of report from Drop-Down menu.
• Click OK.
• A blank grid with a Field Box and form element toolbar will appear. It is similar to Design View form forms.
•  Design the report; for example, double click the title bar of Field Box to add all fields to the Report at once.
• Use the handles on the elements to re-size them.
• Move them to different locations.
• Modify the look of the report by using options on formatting toolbar.
• Click Print View button at the top left corner of the screen to preview the report.

Steps to Print Report: 

The following steps or procedure is adopted for printing of report.

• Select File > Page Setup to modify page margins, size, orientation, and column setup.
• Select File > Print from the menu bar OR Print button on toolbar.

DATABASE SECURITY
It means protecting the database from unauthorised access, modification, misuse and destruction. As database contains the most crucial data of the organisation so its security is an important goal. Database administrator is responsible for developing overall polices and procedures to protect database.
Types of Security

1. External Schema (View):
   In this case the user can only view the data which is allowed to him. The remaining data is unknown to him, so he can not damage the rest of data.
2. Authorisation Rules:
   It is designed to protect the database by preventing people from unauthorised reading, writing, updating or destruction of database.
3. Data Encryption:
   For highly sensitive data, such as company financial data, data encryption is used. Encryption is the coding of data so that human can not read them. It converts the data into such form, which is not readable to human, who does not know the Encrypted Key
4. Password Protection:
5. Audit & Trail:
   Another important security tool is security log, which is a journal. Keeps a record of all attempted security violations. The violation can be simply recorded in the log.
   The log contains the following.
   • All access to database.
   • The users who requested the data.
   • The operation performed.
   • Time of occurrence.
   • The old and new value of data item.
   Now when the authorised person want to access the database, he first checks his log file. If there is any change in the database he can find and recover the data by using the log.