Introduction to Database Concepts and Access

1. Introduction to Database Concepts and Access Using Access 2000 - Foundation/Intermediate

2. What is Data? • Data - facts made up of text, numbers, images and sounds -Murray 15000 10 • Information is the meaning given to data in the way it is interpreted:Mr Murray is a sales person whose basic annual salary is $15,000 and whose commission rate is 10%

3. What is a Database? • A structured collection of related data • An address book, a Telephone directory, a Timetable etc.

4. Name: David MurrayCompany: CCTTel: 01242 227200 Basic Concepts • File • A set of related records • Record • A collection of Data about an individual item • Field • A single item of data common to all records Name: David MurrayCompany: CCTTel: 01242 227200 Name: David Murray

5. The Telephone Directory - An Example of a Database Telephone Directory Aardvark A.A. Railway Cuttings Cheltenham (01242) 123456 Aardvark S.F. 23 High Street Cirencester (01285) 654321 Aaron A.M. The Paddock Cheltenham (01242) 101010 File Name Records Fields

6. Tables and Relationships • Customermakesorder • Orderconsists of order details make consist of have deal with

7. One-to-one Many-to-many Types of Relationships • One-to-many • One-to-one • Many-to-many resolved into two one-to-many One-to-many

8. Why Use an Electronic Database? • Speed • Ease of Use • Versatility

9. Why Use Access? • Familiar look and feel of Windows • Easy to start building simple databases • Can build sophisticated systems • True relational database • Allows prototyping

10. An Introduction to Access Each column represents a field within the record Each line represents a record within the table

11. Customer Company Name Address City Telephone Contact Name Introducing Access Tools Tables Employees Customers Reports Forms Queries

12. Using Access as Part of Microsoft Office Professional • Microsoft Office Professional includes: • Access • Word • Excel • PowerPoint • Outlook

13. Starting Microsoft Access • Click on “Microsoft Access” in the Start menu Or if you have set up a shortcut on your Desktop, click on the Access shortcut icon

14. The Access Application Window

15. Access - Concepts, Terminology and Usage Using Access 2000 - Foundation/Intermediate

16. Opening a Database • To open a database when you start Access • Choose the Open an existing file option on the opening dialog, as illustrated • To open a database once you have already started Access- Choose Open from the File menu- OR press CTRL+O- OR click on the Open icon on the toolbar

17. The Access Database Window • Icons down the left hand side provide access to all database objects • Select the object by clicking the icon

18. Closing a Database • To close a database • Choose Close from the File menu • Or click on the Control menu and select Close • Or press CTRL+F4

19. Opening a Table • To open a table-Click on the Table icon in the Database window- Select the table you want- Click on the Open icon

20. The Table Window • A table opened from the database window appears as a datasheet • Each row contains a separate record • Each column contains a separate field

21. First record Previous record Next record Last record Exploring the Table • To move through records and fieldsuse TAB, SHIFT+TAB, HOME, END, CTRL+HOME, CTRL+END, PAGE UP, PAGE DOWN, and the arrow keys • To move through records

22. Table Design View

23. Introducing Queries • A means of asking questions of your database • Can look across a number of Tables

24. Introducing Forms • A friendlier view of the database • Used for data input, menus, display and printing

25. Types of Form

26. Opening an Existing Form • To open a form

27. Form Design View • A form can be viewed in • Datasheet view • Design view • Form View

28. Introducing Reports • Output of information from your database in the form of a printed report • Allows you to group and summarize information • Can be previewed to the screen prior to printing • Can include logos, graphs and drawings

29. Database Design and Table Creation Using Access 2000 - Foundation/Intermediate

30. Design and Document Your Database • A designers best tools are a pencil and paper • It is important to plan what you are going to do • The sooner you touch the computer the sooner you’ll make a mistake • If you don’t plan you will often have to start again • Document what you are doing, will you rememberwhat you did in three months time?

31. Questions To Ask Yourself • What do I want? • (Outputs) • What have I got? • (Inputs) • What do I need to do to get there? • (Process)

32. Define Your Needs • Draw a picture • Write a description PRODUCTS CATEGORIES SUPPLIERS ORDERS C.C. Toys EMPLOYEES

33. Basic Design Rules • Unique records • Unique fields • Functionally dependent fields • Independent fields • No calculated or derived fields • Data is broken down into smallest logical parts RULES

34. one many many one Determine Relationships • Customer makes many orders: one-to-many • Order contains many products and products can appear on many orders: many-to-many • Employee belongs to social club: one-to-one • Get rid of many-to-many by introducing another table, e.g. Order Details

35. Creating a Database

36. Using the Table Wizard

37. Adding Fields Using the Table Wizard (1) BUSINESS PERSONAL OR

38. Adding Fields Using the Table Wizard (2)

39. Creating a Table Without a Wizard OR

40. Adding Fields to a New Table • Type Fieldname • Choose Data Type • Type Description • Enter Field Properties

41. Field Properties

42. The Input Mask Property • Allows you to specify the format of input • Useful if input always follows a standard format • ZIP or Post codes • Telephone Numbers • National Insurance codes • UK Post code • >LL09\ 0LL • UK Telephone Number • \(99999") "000000

43. Setting a Primary Key • In Table Design View • Select the field you wish to use as the Primary Key • Click on Primary Key Button

44. Saving a Table • To save a table • Choose Save from the File menu • Enter a table name if this is the first time you have saved the table • Click OK

45. Adding Records to a Table Datasheet • Click here and start typing • Pressing TAB moves you to the next field • When in the last field of the record pressing TAB moves you to the next record

46. Formatting a Table Using Access 2000 - Foundation/Intermediate

47. Editing Records • Many editing operations involve selection • There are many ways to select fields and records • Record selectors indicate the current status of the record Current record Record is selected Record is being edited Last (empty) record

48. Selecting Records With The Mouse • Group of Records • Click and drag across record selectors • All Records • Click here • Single Record • Click in the record selector

49. Selecting Records Using The Keyboard • Single Record • Select the record required • Select the Edit menu (Alt+E) • Choose Select Record (L) • All Records • Select the Edit menu (Alt+E) • Choose Select All Records (A)or • Press Ctrl+A

50. Selecting Fields With The Mouse • Single Field • place the mouse at the beginning of the field (cross pointer) and click once • Group of Fields • Select 1st field • Hold [Shift] + select last fieldor • Select and Drag