Access and Databases - Computer Fundamentals - Lecture Slides, Slides of Computer Science

Download Access and Databases - Computer Fundamentals - Lecture Slides and more Slides Computer Science in PDF only on Docsity!

Introduction to Access

and Databases

Topics

• What is a relational database?
• Components of a database:
  • Tables
  • Queries
  • Forms
  • Reports
• When to use databases / spreadsheets

A non-relational database

Name DOB Role Manager Phone Location Sex

Alan Spencer 5/2/55 Administrator Colin Morton 4242 G23 M

Sarah Supton 3/12/70 Administrator Alan Spencer 2487 G23 F

Leanne Thurr 3/1/71 Teaching Assistant

Colin Morton 9210 111 F

Colin Morton 1/1/40 Dean - 4297 323 M

STAFF

A relational version

ID Firstname Lastname Role Manager DOB Phone Location Sex

1 Alan Spencer 1 4 5/2/55 4242 01 M 2 Sarah Supton 1 1 3/12/70 2487 01 F 3 Leanne Thurr 2 4 3/1/71 9210 02 F 4 Colin Morton 3 4 1/1/40 4297 03 M

LocationID LocationName 01 G 02 111 03 323

RoleID Title AnnualLeave 1 Administrator 1 2 Teaching Assistant 3 3 Dean 90 STAFF

LOCATIONS ROLES

The Access environment

• A lot like other Microsoft Office software (menus, toolbars, etc)
• “Database window” – the central point for your database

Databases are on disk!

• Must choose a file location before working on your database

Word or Excel program

Hard disk

SAVE

Document in memory CREATE/EDIT

Access program

Hard disk

CREATE/EDIT/ SAVE

Creating a table

Either:

1. Import from Excel or other source (File > Get External Data > Import)
2. Create new within Access (Insert > Table, or choose the “New” option on the database window)

Keys

• One field in a table is normally chosen to be the key
• Key must be unique to each record – for example, your payroll number or a student’s UCAS number
• Access uses the key to perform some of its operations, such as...

Table relationships

• The “Relationships Window” (Tools > Relationships)

Queries

• Queries look like tables
• They are used to get data from tables according to specific needs, eg: address lists; end-of-year marks
• “Dynamic”: when the table is updated, the query is updated

Forms

UCAS form Form in admissions database with the same layout

Reports

• Reports make the information “presentable”: printed reports, labels, charts…
• Can present information from Tables and/or Queries
• Dynamic (like Queries) – report will update whenever the underlying data updates

Starting a new database

• Plan the information that’s going to be in it, and the uses of it (pencil & paper) – include everything you can think of!
• Create database
• Enter information

When to use databases

Use a database:

• To store complex information
• When the information will need to be used/presented in a variety of ways Remember:
• Databases can be complex and difficult to set up properly
• Avoid re-inventing the wheel...