Database design

“© 2008 Infosys Technologies Ltd. This document contains valuable confidential and proprietary information
of Infosys. Such confidential and proprietary information includes, amongst others, proprietary intellectual
property which can be legally protected and commercialized. Such information is furnished herein for training
purposes only. Except with the express prior written permission of Infosys, this document and the information
contained herein may not be published, disclosed, or used for any other purpose.”
Confidential Information
• This Document is confidential to Infosys Technologies Limited. This document contains
information and data that Infosys considers confidential and proprietary (“Confidential
Information”).
• Confidential Information includes, but is not limited to, the following:
– Corporate and Infrastructure information about Infosys;
– Infosys’ project management and quality processes;
– Project experiences provided included as illustrative case studies.
• Any disclosure of Confidential Information to, or use of it by a third party, will be damaging to
Infosys.
• Ownership of all Infosys Confidential Information, no matter in what media it resides, remains
with Infosys.
• Confidential information in this document shall not be disclosed, duplicated or used – in
whole or in part – for any purpose without specific written permission of an authorized
representative of Infosys.
• This document also contains third party confidential and proprietary information. Such third
party information has been included by Infosys after receiving due written permissions and
authorizations from the party/ies. Such third party confidential and proprietary information
shall not be disclosed, duplicated or used – in whole or in part – for any purpose without
specific written permission of an authorized representative of Infosys.

2
What is Microsoft Access?

Microsoft Access is a relational database management system

(DBMS or RDBMS). At the very core, it is a software “engine”
that provides an interface between physical data and user
application queries.

Other examples of DBMS applications include:

Oracle
mySQL
SQL Server (Microsoft)
DB2 (IBM)
Informix

3 3 PA Harris, Vanderbilt University

Why choose MS-Access over Excel?

Although there is always overlap, the following rules might

help when deciding when / when not to use MS Access:

MS Access is best used for long-term data storage and/or

data sharing.

MS Excel is best used for minor data collection,

manipulation, and especially visualization.

Excel is not database management systems

It is easy to export data from MS Access to Excel

4 4 PA Harris, Vanderbilt University

Why choose MS-Access over other DBMS
systems?

Cheap, readily available (packaged with MS-Office

Premium).

Easy to use (relative to other systems –Oracle may require

one FTE to maintain the server as a database administrator
and another FTE to serve as an application developer).

Includes front-end tools for rapid application development

(RAD). This also makes MS-Access a good prototype
environment.

5 5 PA Harris, Vanderbilt University

Why choose other DBMS systems over MS-
Access?

MS-Access can handle a large number of records, but is

somewhat slow compared to some of the high-end platforms.

Multiple users may use the database simultaneously, but MS-

Access is known to become unstable with greater than 3-5
users.

There is a “snob factor”. I personally recommend the use of

other systems (Oracle, SQL Server, mySQL, etc) when
writing grant proposals - especially phase II type grants).

6 6 PA Harris, Vanderbilt University

What is in MS-Access?

Although the term “database” typically refers to a collection of related data

tables, an Access database includes more than just data. In addition to tables,
you can add:

Saved queries (stored procedures) - organizing and/or manipulating data

Forms – GUI interaction with data, event programming
Reports – customized results for printing (~ static forms)
Macros and VB programs for extending functionality

Microsoft provides some logical integration of these tools through “wizards”.

However, these are pretty basic - most developers must pick and choose the
best approach when implementing applications.

7 7 PA Harris, Vanderbilt University

What is in MS-Access?
• Relational Database: in relational databases such as Access,
data is stored in tables made up of one or more fields (Access
calls a column a field). The data stored in each column must
be of a single data type such as Character, Number or Date. A
collection of values from each column of a table is called a
record or a row in the table.
• Different tables can have the same column in common. This
feature is used to explicitly specify a relationship between two
tables. Values appearing in column A in one table are shared
with another table
• Table: tables are the main units of data storage in a database.
A table is a collection of data about a specific topic; it is made
up of one of more fields.

8 8
What is in MS-Access?
• Field: a field is a column in a table and defines a data type for
a set of values in a table. For example, a mailing list table
might include fields for first name, last name, address, city,
state, zip code, and telephone number.
• Record: a record in a row in a table and is a set of values
defined by fields. In a mailing list table, each record would
contain the data for one person as specified by the intersecting
fields.
• Data type: data types are the properties of each field. A field
only has one data type, such as Character, Number or Date.
• Primary Key: a primary key is a value that can be used to
identify a unique record in a table.
• Design View: it provides the tools for creating fields in a table.
• Datasheet View: it allows you to update, edit, and delete in
formation from a table.

9 9
Access window
• Title Bar:Displays name of the Database
• Tabs in ribbon-display different tyes of commands based
on the category you select
• Ribbon-has commands that reflect the active contextual
tab, your position in the database and the selected
objects
• Navigation Pane: displays database objects of the types
you select
• Object window : displays any open data base objects
• Status bar- indicates the progress of any ongoing
process

10 10
Object window
• Object Tab: displays all open database objects
• Close button: To close the active database object with a
single mouse click
• Opening a data base object displays contextual tabs that
contain design and formatting tools
• Horizontal Scroll bar: To move to side within the object
• Vertical Scroll Bar: To move up and down within your
project
• Right clicking the control on the body of a database
object while you display the object in Design view
displays a contextual menu that enables you to edit the
selected control

11 11
Starting Access and Creating Database
• Click on the START button on your computer and position
your cursor over the PROGRAMS menu to view a list of
installed programs. Once you see the list, position the cursor
over Microsoft Office and then click on Microsoft Access
2007.

12 12
2. After opening Access, you will be presented with the window
shown below Figure

13 13
3.Open a database on start up
• Click More
• Navigate to the folder with the database to be opened
• Double click the file to be opened

14 14
4.Open recently used database
• Start MS Access 2007
• In the open recent database section, click the name of the
database you want to open

15 15
5. Creating Database from a template
• Click a template category
• Click on the desired template
• Type a name for your database
• Navigate to the folder where you want to store the newly
created database
• Click ok
• Click download

16 16
Database Components
• An Access database consists of several different components.
Each component listed is called an object.
• Tables Tables are where the actual data is defined and entered.
Tables consist of records (rows) and fields (columns).
• Queries Queries are basically questions about the data in a
database. A query consists of specifications indicating which
fields, records, and summaries you want to see from a
database. Queries allow you to extract data based on the
criteria you define.
• Forms Forms are designed to ease the data entry process. For
example, you can create a data entry form that looks exactly
like a paper form. People generally prefer to enter data into a
well-designed form, rather than a table.

17 17
Database Components
• Reports When you want to print records from your database,
design a report. Access even has a wizard to help produce
mailing labels.
• Pages A data access page is a special type of Web page
designed for viewing and working with data from the Internet
or an intranet. This data is stored in a Microsoft Access
database or a Microsoft SQL Server database.
• Macros A macro is a set of one or more actions that each
performs a particular operation, such as opening a form or
printing a report. Macros can help you automate common
tasks. For example, you can run a macro that prints a report
when a user clicks a command button.
• Modules A module is a collection of Visual Basic for
Applications declarations and procedures that are stored
together as a unit.

18 18
Microsoft Access – Module 1 Summary

MS-Access is a powerful relational database

program. It has many integrated features and
can be greatly customized to fit most
personal/departmental needs for data
collection and storage.

19 19 PA Harris, Vanderbilt University

Microsoft Access – Module 2

Creating / Working with Tables

20 20 PA Harris, Vanderbilt University

Creating Tables in Database
• Click the Create Tab
• In the tables group click Table
• Type the data for the first new field and press tab
• Repeat step 3 until you have typed all of the data for one
record and then press Enter twice to return to the first
field.
• Click Save
• Type a name for your table
• Click OK
• Click the Close box for the table

21 21
Creating Tables in Database

22 22
Modifying Table
• Right click the table you want to modify
• Click design view
• You can either delete/add/reorder fields
– Delete a field: click the row selector of the row you want to
delete , click the delete rows button, click yes to confirm
– To add a field, click the row selector of the row below
where you want the new field to appear, use the Insert row
button to insert and type a name for the field. Click the
row’s Data type cell, click the down arrow that appears, and
then click the desired data type.
– To reorder the field: Use the row selector drag and drop it
to the new desired position

23 23
Modifying Table

24 24
Adding Data to a Table
• Once the table is opened the existing the data can be
edited or new data can be added.
• Selecting the text
– Move the mouse pointer over cell. When it turns white
cross, click in the cell. Double click the text/Drag
mouse to extend the selection
• Delete text: Select the text and use del key.
• Undo operation: Use undo button in the quick access
toolbar
• Copy and paste text: Select the text to copy. Click the
Home tab, click the copy button, click the position where
the text is pasted, Click the paste button

25 25
Table Relationships
• If the primary key in one table is found with another table,
relationship can be created between the two tables.
Click Relationships
• If the tables needed are not present in the relationships
window, click the show table button
• Click the first table to add to the Relationships window
• Click Add
• Repeat steps 4 and 5 as necessary
• Once finished adding the tables, click Close
• Drag the primary key in first table to the related foreign key
field in the second table
• Click create, use close button to close the relationships window

26 26 PA Harris, Vanderbilt University

Table Relationships

27 27
Importing Data
• There are situations where data from one database
would be needed in another data base too.
• The External data tab will be useful in such cases like
– Copying tables from a data base
• You can import data from other types of data base
objects like query by choosing the appropriate object
type from the Import Objects dialog box and then
clicking the object name from which you need to get the
data

28 28
Copy a table from another Database
• Click the External data tab
• Click Access
• Click Browse
• Double click the data base from which you want to import
the table
• Choose the option “Select the tables, Queries, Forms,
Reports, Macros, and Modules Into the current Database
option
• Click OK
• Click the Tables/Table to import
• Click OK

29 29
Copy a table from another Database

30 30
Copy a table from another Database

31 31
Exporting Data
• Exporting an Access table/query data to an Excel work
book takes a few steps
– Click the table /query to be exported
– Click the External Data Tab
– In the Export group, click the export to Excel
spreadsheet button
– Type a name for the file (If needed select the Export
Data with Formatting and Layout option)
– Click ok
– Click Close

32 32
Exporting Data

33 33
 MS Access – Module 2 Summary

Tables can be added and deleted easily from a data base

The data in the table can be easily edited
Relationship is a powerful tool to link two tables
together in the process of making business decisions
Importing and exporting data is possible through the
External Data Management option

34 34 PA Harris, Vanderbilt University

Microsoft Access – Module 3

Creating / Working with Queries

35 35 PA Harris, Vanderbilt University

Query Overview - 1

An MS-Access query is a set of stored SQL

instructions that manipulate and/or select data
from one or more tables.
Select Query – Data grouping and/or filtering
Make-Table Query – Select + creates/populates new
table.
Update Query – Updates fields from specified table
data
Append Query – Runs query on one table, appends
results to a table
Delete Query – Delete selected records from table

36 36 PA Harris, Vanderbilt University

Query Overview - 2

SQL (Structured Query Language) is a very widely used

database language designed specifically for
communicating with databases
SQL is not proprietary – almost every DBMS supports SQL
(including MS-Access).
SQL is relatively easy to learn, but extremely powerful – one
of the easiest ways to learn is to use MS-Access Query by
Example methods, then look at the generated SQL
command
Remember that a query is nothing more than the database
engine running the stored SQL command (it looks and
sometimes acts like a table, but really adds little mass to
the database file)

37 37 PA Harris, Vanderbilt University

Extracting Information From the queries
• In a select query:
– You identify the table with the data you want to find
– Name of the fields to appear in the query result
• The query wizard makes it simpler to do all these tasks

38 38
Create a Detail Query
• Click on the Create Tab
• Click on the Query Wizard
• Click the Simple Query Wizard
• Give OK
• Click the Tables/Queries down arrow and then click the
table or query having field to be used in the query under
construction
• Click the first field to be included in the query results
• Click Add
• Do the same for the all the field to be included in the
query under construction

39 39
Create a Detail Query (continued)
• Click Next
• Click the Detail option button
• Click Next
• Give a name for the newly created query
• Click Finish

40 40
Query Wizard

41 41
2-Table Query
• Click on Create
• Click Query Design
• Select the tables to be queried
• Select the fields to be shown
• Save the query by giving a name
• Then go to query wizard
• Select the created query to obtain the data

42 42
2-Table Query

43 43
2-Table Query

44 44
Query – Calculating Fields

Name the calculated field, then type a colon, then

type the equation using brackets ( [ ] ) around table
fields. If there is ambiguity in the field names
between tables, you may need to type table.[field]
format.

Ex: BMI: [Weight]/([Height]/100)^2

Ex: For calculating experience of an employee

from years of joining
Exp:Year(Date())-[Year of joining]
Year(Date()) is a function to extract year from
current date

45 45 PA Harris, Vanderbilt University

Query – Sorting Data

Choose Ascending or Descending in the Sort Row

This query would sort by Deptno and Empno.

46 46 PA Harris, Vanderbilt University

Query – Filtering Data

This query will return all records in the database for:

deptno equal to 1
and year of joining between 2002 and 2006

47 47 PA Harris, Vanderbilt University

Query – Filter Operators

= equals
> greater than
>= greater than or equal
< less than
<= less than or equal
<> not equal to
Between between two values
Is Null field is empty
is not null field is not empty
Like Matches a pattern (Like John*)
OR Logical OR (one or other is true)
AND Logical AND (both are true)
etc.

48 48 PA Harris, Vanderbilt University

Query – Grouping Data - 1
Clicking the Totals Button
Enables Grouping, Counting
and Statistical Options

Notice new “Total” row.

Each field (column) can be set.

49 49 PA Harris, Vanderbilt University

Query – Grouping Data -2

Totals Options Include:

Group By
Sum
Avg
Min
Max
Count
StDev
Var
etc.

50 50 PA Harris, Vanderbilt University

 MS Access – Module 3 Summary

Queries are extremely easy to set up/use and provide an up-to-

date snapshot of your data at any time.

Queries may be used to calculate values based upon existing

fields, join fields from separate tables, globally update or delete
data, and export linked/calculated data to external programs.

Under the hood, queries are really nothing more than stored SQL
statements that are run upon command. They add little mass to
the file application.

51 51 PA Harris, Vanderbilt University

MS-Access Import/Query Practice

Import data from the one database to the current database.

Design and save a new query named to display only the
following fields: 1) StudentName; 2) Age; 3) TotalMarks (a
calculated field computed by summing up all the marks).

Select filter criteria in the query to show only those records

where: 1) students to whom Teacher with teacherid 111
teaches;
2) students with age greater than 11; and
3) students whom teacher with teacherid 111 or 444 teach.

52 52
Query Practice
Design and save a new query named Question2 to provide
summary data for each Student(ie we want to see all the
students data in separate rows). For each of these
compute: 1) Total marks obtained; 2) and average of
marks.

53 53
Thank You
“The contents of this document are proprietary and confidential to Infosys Technologies Ltd. and may not be
disclosed in whole or in part at any time, to any third party without the prior written consent of
Infosys Technologies Ltd.”

“© 2007 Infosys Technologies Ltd. All rights reserved. Copyright in the whole and any part of this document
belongs to Infosys Technologies Ltd. This work may not be used, sold, transferred, adapted, abridged, copied or
reproduced in whole or in part, in any manner or form, or in any media, without the prior written consent of Infosys
Technologies Ltd.”

ER/CORP/CRS/<C