Introduction to Access and Databases

1. Introduction to Access and Databases By: Dr. K.V.Vishwanatha Professor, Dept. of CSE, R.V.C.E, Bangalore

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

3. Relational databases • Database: store of information • Relational: relationships between pieces of data are stored Access is a program for creating and managing relational databases.

4. A non-relational database STAFF

5. A relational version LOCATIONS ROLES STAFF

6. What tables do you need? • Split data into “subject areas”, eg: Rooms, Staff, Students, Events, Committees • Each subject is a potential table • Identify how the facts (data) you want to store relate to the subjects (tables) If the data cannot be thought of as a characteristic of that table it may need to be assigned to a new table.

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

8. Document in memory CREATE/EDIT SAVE CREATE/ EDIT/ SAVE Hard disk Hard disk Access program Databases are on disk! • Must choose a file location before working on your database Word or Excel program

9. A record (this record describes black ring binders) A field (the name of this field is “PRODUCT DESCRIPTION”) Tables • Tables hold all the information in Access. • Similar to a “list” in Excel – a grid of information.

10. 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)

11. 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...

12. Table relationships “Students” table: “Staff” table:

13. Table relationships • The “Relationships Window”(Tools > Relationships)

14. 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

15. Forms • Used for entering information into tables • Not “necessary” – but useful • Flexible formatting • Can help ensure that you’re entering the right data

16. Forms Form in admissions database with the same layout UCAS form

17. 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

18. Exporting information You can: • Export to Excel(File > Save As/Export)N.B. This will export a Table or Query – not the whole database! • Save As HTML for the web(File > Save as HTML)

19. 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

20. 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...

21. When to use spreadsheets Use a spreadsheet: • To maintain a small, simple set of information • To perform numerical analyses (formulae, functions) • To create graphs & charts quickly