Databases

1. Databases The basics of Access

2. Database Basics A database is an organised collection of information. For example A telephone directory is a database It stores your name your address your telephone number It is organised information This information is all about you. It’s all related to you. What are some examples of databases?

3. What’s in a record? A record is one batch of related information For example Your record in the telephone directory your name your address your telephone number This information is all about you. Its all related to you. Its your record.

4. Records held in tables Records are often presented in a table This table belongs to database containing information about pupils in a school. 1 2 3 4 How many records does this table store?

5. Fields { A field = 1 piece of data in a record. Record Fieldname Fieldname Fieldname Fieldname

6. Fieldnames Give some examples of appropriate field names for each of the following tables Police database of criminals Surname Firstname Address Members of Video Club Surname Address Telephone Number Videos available for hire in a video club Title Date hired Date to be returned Customer ID Books in library Title Author ISBN Number

7. Demonstration in Access • Interface • The 2x different views we work in • Datasheet and Design views

8. Access View Datasheet View when entering new records Design View when creating new tables • We are going to be working mainly in two different views.

9. MS Access Field Names Datasheet View Design View

10. MS Access

11. Task - Creating a new table • The P.E department have requested a database on with relevant student sporting information. • You will create a new table in MS Access to record this information from your classmates. • What fields will we require? • You need to include at least 5 of your classmates information.

12. Garbage in Garbage out How many people live in Bourne?

13. Field Validation • Validation lets us check for errors or invalid data. • The tools we use: Validation Rules & Input Masks

14. Input Mask Rules

15. Data types • Every field has to be given a data type, the field has to be told what type of data can be expected in the field. Currency

16. Data types What data types would you use for each of the following fields? Example of Data entered Data Type Field Name Gray Surname Text Start of holiday Date/Time 21/12/2002 BA345 or 6754 Flight number Text or Number Number 24 Stock on shelf Cost of holiday Currency $250.50

17. Field Size A field can be assigned a size, this is the space allowed for the data to fill. For example a field that holds data on a persons title would not need to be too large as all it needs to accommodate would be Mr, Mrs, Miss or possibly doctor. So a field of 6 would be fine. What field size would you use for each of the following fields? Postcode 8 House Number 4 Gender 2 or 6 Country 25

18. Flat File vs Relational • Flat file = all data in one table • What we have done so far • What are the issues with this? • Relational • How is this of benefit to us? • Storage • Speed • Organisation • Update efficiency • How can we connect the tables?

19. Relationships Tables can be joined together allowing both table to be searched or sorted together. The fields that are used to relate two tables MUST be of the same data type or they will not join. In this example both fields are text. One common field must be in each table. In this example it is “Country”.

20. Related Tables This shows the two tables joined by the field “country”.

21. Relationships This database example is to track the exams pupils sit, it consists of 2 tables one for the pupil information and one for the exam information. The field Pupil ID has been used in the exam table to track which exams the pupils are sitting. Tblpupil Tblexam Primary key Primary key The pupil ID field is used in both tables

22. Relationships Which fields would you join in this table. The database is made of two tables tracking the hire of videos to the members. Tblcustomers Tblvideos Primary key Primary key

23. Primary Keys A primary key is the unique key that identifies that record. Which field would you use as the primary key?

24. Primary Keys A primary key is a unique field that identifies the record What field would you identify as the primary key? Primary Key

25. Primary Keys This is the table we created to hold information about your classmates, we left the primary key as ID and AutoNumber. While this works, having a field like this is a little meaningless. What other field could we use to uniquely identify each student? Primary Key

26. Primary Keys If we changed the name of the field to StudentID it would have more meaning. Is this OK? This would work, BUT do we want automatic numbers? Probably not, it would be better if we could type in the unique student ID for each student. Primary Key

27. Primary Keys What field from each table would you use as the primary key Primary Key This table hold details of exams that students can be entered for This table holds information on stock Primary Key

28. Primary Keys Primary Keys

29. Foreign Keys Primary Key Primary Key Foreign Key Foreign Key – a primary key used in another table for example the country is the primary key in the Countries and the foreign key in the RWCTeams.

30. Foreign Keys This database example is to track the exams pupils sit, it consists of 2 tables one for the pupil information and one for the exam information. The field Pupil ID has been used in the exam table to track which exams the pupils are sitting. It is the primary key in the pupil table and the foreign key in the exam table. Tblpupil Tblexam Primary key Primary key Foreign Key

31. Video Club In this database what would be the primary key, the foreign key and which fields would be joined. Tblcustomer Primary key Tblvideos Primary key Foreign Key