4.6 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.
4.6.1 Introduction
A Table is a database object that is used to store data about a particular subject
like employees, students or products. In a Table, the columns are called fields or
attributes and the individual records are called Tuples (rows). One of the most
important advantage of a Relational table structure is the Primary key field in
each table. A Primary key is a field or a combination of fields that make each
record in the table unique. Primary keys help in removing duplicate data and
creating relationships between different tables. Foreign Key contains values that
correspond to the primary key of another table. A table can have one or more than
one foreign key
Every field has a datatype which indicates the type of data that filed will stores,
such as text, numbers or external files.
The most commonly used database in the industry is MS-Access. Here, we will
see how to manage a simple database in MS-Access.
4.6.2 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.
4.6.3 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.
4.6.4 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.
4.6.5 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
4.6.6 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.
4.6.7 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.
4. 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.
4.6.8 Defining Queries
When data has to be reviewed, added, changed or deleted from the database, a
query is used. Queries are also used to answer very specific questions about the
data that would be difficult to answer directly by just looking at the table data.
Queries can be used to perform calculations on the data, to filter data and to
summarize the data.
1. On the Create tab, in the query group, click query design. A show Table dialog box appears.
2. In the Show Table dialog box, choose the table on which query has to be based and close the show Table dialog box.
4. On the Design Tab, in the Results group, click Run.
The query will be executed and the result is displayed in the grid as shown below.
4.6.9 Reports
A Report consists of information from tables and queries that is stored with a
particular report design such as labels, headings and graphics.
1. In the Navigation pane, select the query or Table on which the report has to be based.
2. On the Create Tab, in the Reports group, click Report
3. The Report will be created and displayed
4.6.9.1 A Small Example in Access 2007
We wil create a small database for employees in an organization using MS Access
2007. Make two tables T_Employee_dtls and T_Dept_master database tables with
the following structure:
T_Employee_Dtls
between the two tables as explained above in section 5.3.7. Add data to the table
and then define the following queries as explained above:
SELECT T_Employee_dtls.Emp_Name, T_Employee_dtls.Emp_Address
FROM T_Employee_dtls;
SELECT T_Employee_dtls.Emp_Name, T_Employee_dtls.Emp_Age,
T_Dept_Master.Dept_Name
FROM T_Dept_Master INNER JOIN T_Employee_dtls ON
T_Dept_Master.Dept_Code = T_Employee_dtls.Dept_Code;
Reports can be generated based on these queries or different queries can be
written to generate various reports.
4.7 E-MAIL
Emailing is one of the most important utilities in today’s world. It is used widely
and utilized by almost each and every one of us not only as professionals but also
in our personal lives. Emailing software like MS Outlook , Outlook Express etc,
not only provides emailing facility but also used for scheduling appointments,
maintaining contacts, setting task reminders, sending attachments with emails etc.
4.7.1 Introduction
Email Accounts can be from the Internet Service provider, employer, or webmail
services like yahoo, Gmail etc. Outlook doesn’t have its own accounts but uses
these email accounts. Outlook uses POP configuration to access the web based
servers for emails. Post Office Protocol (POP) is an application-layer Internet
standard protocol used by local e-mail clients to retrieve e-mail from a remote
server over a TCP/IP connection.
POP supports simple download-and-delete requirements for access to remote
mailboxes. Although most POP clients have an option to leave mail on server
after download, e-mail clients using POP generally connect, retrieve all messages,
store them on the user's PC as new messages, delete them from the server, and
then disconnect.
To send or receive email messages with Microsoft Outlook, these email account
information has to be added. Email Accounts are contained in profiles. An email
profile consists of email accounts, data files and settings as to where the emails
are to be stored. There can be one profile or more than one profile depending on
the requirement of the user. There can be one or more email accounts within a
single outlook profile.
4.7.2 Creating Email Accounts and Profiles
Before using Outlook, an Email profile and account has to be created.
1. In MS Windows, go to control panel and select Mail option.
2. Click Show Profiles
3. Click on Add and Type a name for the profile and then click OK
4. Add email account to use in your profile by following the directions on the
screen.
4.7.3 Create a new Email Message
The most important feature of Email software is to send and receive emails. Once
the email account has been configured, the same can be used to send and receive
emails.
1. On the File menu, select, New ->Mail Message
2. In the Subject box, type the subject.
3. Add the recipient’s name in the To, CC, Bcc box. Separate the names with semicolon.
4. To select the recipient’s name from a address book, click the To or CC button
5. The level of importance for message can also be set which appears as an
indicator by selecting High Importance or Low Importance in the Options
group in the message tab. This is only an informational flag and does not
expedite or affect actual message delivery.
It is also possible to put file attachments along with the email message or include website links or links to documents present on a shared network.
4.7.4 Manage Contacts
New Contacts can be added to the Address Book. It is possible to make groups of
Contacts and create distribution lists to ease selection of contacts when sending
messages.
1. To add a new Contact, select File Menu, New->Contact
2. Fill in the Contact Details on the contact form that appears.
It is also possible to create a contact from an email message received. To do this:
1. Open or preview the email message that contains the Contact name that is to
be added to the Contact List
2. Right Click the name of the sender and then Click Add to Contacts through
the shortcut menu.
4.7.5 Calendar Management
Using the Calendar feature, one can schedule activities as appointments,
meetings, events or tasks. This choice will depend on who is involved and how
the entry should appear.
To view the activities of any day, click on Calendar in the Navigation Pane. And
then choose the date for which the activities need to be shown.
It is possible to view the activities, day wise, month wise or week wise as
required.
Right Click on the time and date the activity has to be set and choose the activity
type whether it will be an appointment, meeting or event or task.
A meeting occurs only at a schedules time and includes other people and a
meeting location. An Appointment is an activity where only one person is
involved. A task is an entry that is seen in the calendar but doesn’t need to be
scheduled for a specific time. An event is an activity that lasts all day and do not
occupy schedule time in the calendar but appear as banners on the top of the date
specified for the event like conferences, business travels, holidays etc.
If any particular activity is occurring again and again, it can be set as a Recurring
activity using the Recurrence feature. To set up a recurrence pattern, open the
appointment and click Recurrence button in the Options group in the
Appointment tab.
Check Your Progress 3
1. Is it possible to import data from Excel into Access?
…………………………………………………………………………………
…………………………………………………………………………………
…………………………………………………………………………………..
2. What is a signature of an email message?
…………………………………………………………………………………
…………………………………………………………………………………
…………………………………………………………………………………..
3. Is it possible to get a delivery receipt from the email recipient?
…………………………………………………………………………………
…………………………………………………………………………………
…………………………………………………………………………………..
4. Which of the following entry will be best entered as an event in your
calendar?
…………………………………………………………………………………
…………………………………………………………………………………
…………………………………………………………………………………..
A party that occurs from 4 pm to 6 pm
Birthday
A visit to the dentist
