Introduction to Database Concepts and Access

1. Introduction to Database Concepts and Access Using Access 97 - Foundation

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. Review Questions

16. Access - Concepts, Terminology and Usage Using Access 97 - Foundation

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

18. The Access Database Window • Six tabbed cards provide access to all database objects • Select the object by clicking the tab • Click a button to • create a new object • open an existing object • design or modify an object

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

20. Opening a Table • To open a table-Click on the Table tab in the database window- Select the table you want- Click on the Open button

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

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

23. Table Design View • To switch to Datasheet View • To switch to Design View

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

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

26. Types of Form

27. Click Here Opening an Existing Form • To open a form Select

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

29. Closing a Form • To close a form • Click on the Window’s Close icon (top-right) • Choose Close from the File menu • Or press CTRL+F4

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

31. Review Questions

32. Database Design and Table Creation Using Access 97 - Foundation

33. 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?

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

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

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

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

38. Creating a Database

39. Using the Table Wizard

40. Adding Fields Using the Table Wizard (1) BUSINESS Mailing ListContactsCustomersEmployeesProductsOrdersOrder DetailsSuppliersCategoriesPaymentsInvoicesInvoice DetailsProjects ... PERSONAL AddressesGuestsCategoriesHousehold InventoryRecipesPlantsExercise LogDiet LogWine ListRolls of FilePhotographsAuthorsBooks ... OR

41. Adding Fields Using the Table Wizard (2)

42. Creating a Table Without a Wizard OR

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

44. Field Properties

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

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

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

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

49. Review Questions

50. Formatting a Table Using Access 97 - Foundation