Introduction to Database Management

1

• Introduction to Database Management

with Microsoft Access
2
What is a Database?

• An organized collection of related information
  managed by a system to store, find, sort, analyze
  and manipulate the data.
• Database Management System (DBMS) manages a
  collection of organized data stored in a Table.
• Relational Database Management System (RDMBS),
  such as Access XP, organizes data in Tables that
  are related or linked to one another.

3
Features of Access XP

• Find Information
• Add, Delete, and Modify Records
• Sort Records
• Analyze Data and Create Queries
• Generate Reports

4
Creating a Database

• Plan the Database
• Locate and Run Microsoft Access XP
• Entering and Editing Data in Tables
• Sorting and Analyzing Data
• Querying the Database
• Creating Output in the Form of Reports

5
Planning the Database

• What is the Purpose of the Database?
• What Information should the Database Hold?
• What Output should the Database produce?

6
Database ObjectsHow is data organized?

• Database objects used in Access to manipulate
  data
• - Tables
• - Forms
• - Queries
• - Reports
• - Pages

7
Database Objects Tables

• Table
• Records (rows) complete information per entry.
  Collection of all fields in a row.
• Fields (columns) a unit of information within a
  record.
• Example

One Record with 4 Fields
8
Table example

• Table total of 8 records - 6 fields per record

9
Database Objects Forms

• Form Graphical object - provides a nicer
  interface to manipulate data.

10
Database objects Queries

• Query
• Question the database to retrieve and return
  specific information when certain criteria or
  conditions are met.

Example Find all people who live in Kennington
where their mortgage is lt 95.000
11
Query Example
12
Database Objects Reports

• Report create an output of the database
• in an attractive printable format

13
Database Objects Pages

• Pages create an output of the database for
  publication on the internet Web page layout.

14
I. Creating a Database

• From Blank Database DB is empty
• From Database Wizards, Pages, Projects
• Customize to your DB plan
• From an existing database file
• DB is already created

15
Creating a Database (Cont)
- From Blank DB - From Wizard - From a File
DB Name Students.mdb
Table(s)
Form
Query
Report
Page
-Design View -From Wizards -By entering data
(Datasheet)
Create a Database then create at least one Table
of DB records.
16
Editing and Entering Data

• Three methods to create a Table in Access
• Design view design the table field by field,
  setting all properties before entering data
• Datasheet view enter the data to create the
  table and rename generic fields.
• Wizard go over the steps of the selected wizard
  to create the table.

17
Design view

• Enter
• - Field Name
• - Data Type
• - Description
• - Field Properties

18
Datasheet view
19
Creating a Table using a wizard

• Select a commonly used database type (i.e.
  Students and classes), walk through the wizard,
  and answer questions to select fields and create
  a table.

20
Creating a table using a Wizard...
21
Table Structure

• Field Name
• Data Type
• Field Properties and description
• Primary key

22
Data Types

• - Text characters and numbers
• - Memo variable length of a long text
• - Number only digits
• - Date/Time any valid date
• - Currency
• - AutoNumber increment 1 for a new record
• - OLE Object graphics or multimedia object
• - Hyperlink entries to link to objects
• - Lookup Wizard start the db lookup wizard

23
Field Properties

• -Field Size max number of characters.
• -Format how to display and print.
• -Default Value value inserted automatically when
  a new record is created.
• -Input Mask control data in a field display.
• -Validation Rule field data must meet rules.
• -Validation Text validation rule error message.
• -Required verify if data must be entered in
  field.
• -Indexed a field is set as indexed.

24
Changing Field Properties
-To change field properties switch to design view
then click on the field you want to change and
make your change at the bottom of the screen.
Example
25
Defining the Primary Key

• A Primary Key should be unique for each record.
• To change the primary key
• -In Design View click on the field you want
  to define as the primary key
• -Then click on the primary key icon on the
  toolbar to make that field your primary key.

The Primary Key Icon
26
Primary Key.
27
Creating a Form

• - Go to the Objects menu
• Right click on the table you want as a form and
  click Save As and then select Form
• Or, Click the Forms Object

28
Entering Data in a Form

• Use the Tab Key to move between fields.
• Click on the single arrows to move one record
  forward or backward.
• Click on the Double Arrows to move to the first
  or last record.
• Click on the arrow with an asterisk to create a
  new record.

29
Form Example
30
Inserting Graphics

• Define Field Data type OLE Object (Picture)
• Click on Insert and then Object
• Insert object from
• Existing photo (Bitmap image)
• ClipArt gallery (soft Clip Gallery)
• Media Clip
• Excel Chart
• File
• .

31
Inserting Graphics
32
Graphics viewed in a Form

• After Inserting Objects
• Save the Table as a Form and then view the Form

33
Saving a Table

• Table and its structure is saved under the Table
  object within the same database.
• A table may be saved as Form
• will be saved under the Form object
• Query the DB then save results as
• Query saved under the Query Object
• Report Saved under the Report object

34
Compacting a Database

• To reduce the size of the database, compact it
  using Tools/Database Utilities/Compact Database
• from the Tools menu

35
II. Modifying a Database

• Change Primary key
• Search Find Replace data
• Change Field properties
• Insert and delete a record
• Insert and delete a Field
• Change a field width
• Rename a field
• Sort record

36
Changing Primary key

• Switch table to design view
• Select new filed name
• Click the Primary Key button

37
Find and replace data

• Design view or Datasheet view
• Enter the string to search a record(s)
• String Found, Find Next or Replace

38
Change Field properties

• Switch to Design View
• Format property
• _at_ text character or space is required
  Example _at__at__at_-_at__at_-_at__at__at__at_ displays
• 12345678 as 123-45-6789
• gt force upper case characters
• lt force lower case characters
• text character is optional
• Example _at__at_-_at__at_ four out five characters are
  required and the 5th is optional (12-34 or
  12-345)

39
Change Field properties

• Default Value property
• Enables automatic insert of a field value when a
  new record is created
• Input Mask
• Controls how data is entered in the field
• Example (000)000-AAAA would accept a combination
  of numbers (first six) and characters (last
  four).
• The display would be (___)___-_____
• Validation Rule
• Limits what the user can enter in the field.
• Example gt 0 And lt 9 would limit the display to
  numbers in this range (between 0 and 9).

40
(No Transcript)
41
Adding and Deleting Records
-In Design View Right-click on a row and select
either new record or delete record.
42
Adding and Deleting Fields In Design View

• Right-click on a field and click on
• Insert Rows or Delete Rows menu options.

43
Insert and Delete Fields in Design View
Or, b) Right-click on a field and click on
Insert Rows or Delete Rows buttons.
Delete Rows
44
Insert or Delete a Field in Datasheet view

• - Choose Column from the Insert menu or
• - Right click on the field and select Insert
  Column or Delete Column

45
Insert or Delete a Field in Datasheet view

• - Choose Insert Column or Delete Column from
  the Edit menu or
• - Right click on the field and select Delete
  Column or Insert Column

46
Finding Information

• Replace data

Click replace tab
Enter what You want to replace
Enter what you Want to replace with
Select replace or replace all base On what
changes you wish to make
47
Sort Records

• Select field you wish to sort
• Sort records on a single field
• Use the sort ascending or descending buttons
  or

Select Records/Sort/ (Sort Ascending or descending
48
Sort Records
49
Sorting on multiple fields

• Fields must be adjacent to each other
• Select both columns
• Then use the sort commands

50
III. Filter records and Query DB

• Filter Records
• By Selection retrieve temporarily all records
  that match the selected field (i.e. 131).

51
Filter Records(by selection)

• Click the Remove Filter button to get back to
  table.

52
Filter records( by Form)

• By Form displays a blank datasheet of the
  current table in which you specify criteria from
  the drop-down menu in the selected field (s).

53
Filter records( by Form)

• Select criteria from drop-down menu (i.e. 110)
• Click the Filter button to apply Filter by Form.

54
Filter records(by Form ANDOR operator)

• Filtering with AND and OR Operator
• Select criteria from multiple fields.
• Example
• Display all records with Grade A or B.
• Select GradeA then click the OR
• operator and select GradeB.

55
Filter Records(by Form ANDOR)
-Select A Or B then click the apply filter button
56
Filter Records(by Form ANDOR)
- Records with Grades A or B
57
Query the Database

• Query
• Ask questions and retrieve information when
  certain criteria or conditions are met.
• Query Types
• - Simple (Select)
• - Crosstab
• - Find duplicates one or more fields in
  table(s).
• - Find unmatched record exists in one table but
  not in the other.
• lt, gt, Less, greater, or exact match

58
Simple Query (Select)
Query Query Results
59
Query the Database

• Query Types Open Query from New Object button
• or from the DB Query Object.

60
Query Procedure

• Switch to Design View
• Open existing Table
• Start a New Query Object
• Select Fields to be in the Query results
• Set a criteria to query
• Run the Query using the run query button
• Save the Query

61
Query Procedure Single Table Start a new query
object and open an existing table
Add Table fields to the query then close table
62
Query Procedure Single Table Set a criteria to
query
Retrieve all records with grade B or A
B or A
63
Query Procedure Single Table Run the query

• Retrieved all records with GradeB or GradeA.

Save Query results under Query Object
64
Query multiple Tables

• Joins and Relationships
• Join how data between tables is related.
• Relationship between tables via at least one
  common field.
• One-to-manyTable A has many matching records in
  Table B.
• Many-to-one Table A has many matching records in
  Table B
• and Table B has many matching records in Table
  A.
• One-to-one Table A has only one matching record
  in Table B.

65
Query multiple Tables

• Tables share a common field
• Must be the same data type
• Adding tables to a query
• Create an automatic link (Join) between the
  common fields (if one is a primary key and they
  have the same name).
• Link (Join) created manually between the common
  fields if they dont have the same name.

66
Query multiple Tables Procedure

• Open the existing Tables
• Start a new Query object
• Add tables to the Query
• Create link (Join) between common fields in
  tables (automatically or manually).
• Add selected fields to Query
• Set a criteria for the Query
• Run the Query
• Save Query

67
Example1 Query multiple Tables
68
Three Tables Added to Query
Set Criteria Retrieve all records with gradeA
69
Multiple tables - Query results
70
Example 2 Query multiple Tables
Criteria Retrieve all records that belong to
Class ID131
71
Multiple tables - Query results 2
72
Create Reports

• Open a Report Object
• Design View
• Wizard
• Select Design View
• Select a Table or Query to report
• Walk through the Wizard
• Save Report
• Print Report

73
Create Reports
74
Create Reports with AutoReport

• Click in the objects
  list box
• Click

Use AutoReport i.e. Columnar
Specify a Table or Query to use.
75
AutoReport Result
76
Create a Report using Wizard

• Choose fields and their order of appearance

77
Report Wizard Layouts

• Click Next.
• Then pick a layout option.

78
Choosing a Style in the Wizard

• Click Next.
• Then choose a style.

79
Previewing a Report

• Click Next
• Click on Finish to Preview the Report before
  Printing.