CVHS Databases 2024 - PPT

Learning Databases Objectives To understand what a database is, why we A N D G use them and how they I N are they built. To understand how data is vali...

Learning Databases Objectives To understand what a database is, why we A N D G use them and how they I N are they built. To understand how data is validated and MA K G I N verified. To be able to create and use a database U S ES S effectively. A B A Slide 1 of 111 D AT Keywords: Access Database Information Data 1  Learning Databases Temporary Objectives To understand what a database is, why we use them and how they are they built. To understand how data is validated and verified. To be able to create and use a database effectively. Slide 2 of Keywords: 111 Access Database Information Data 2  Learning Databases Temporary Objectives To understand what a database is, why we use them and how they are they built. To understand how data is validated and verified. To be able to create and use a database Complete the effectively. questions on page X Slide 3 of Keywords: 111 Access Database Information Data 3  Learning Databases Objectives CS Learning Journey To understand what a database is, why we use them and how they are they built. To understand how data is validated and Update Thi verified. To be able to create and use a database effectively. Slide 4 of Keywords: 111 Access Database Information Data 4  Learning Databases Objectives To understand what a Coming up in this unit: database is, why we Understanding what data is and how it can be stored in many ways – use them and how they are they built. not just on the computer. Understanding that databases can be used to store data electronically, To understand how data is validated and and why this is really useful. verified. Learning what we might want to do with a database and then using To be able to create Microsoft Access to create one. and use a database Learning how to enter, edit, view or delete data in a database. effectively. Learning the different types of data that can be entered into a database. Slide 5 of Keywords: 111 Access Database Information Data 5  Learning Databases Objectives To understand what a Coming up in this unit: database is, why we Using queries to ask questions about our data, in order to gain use them and how they are they built. meaningful information from it. Using reports to show our data in a printable form. To understand how data is validated and Understanding the different ways we can ensure that the data is as verified. correct as possible To be able to create Looking at additional features of databases such as importing and and use a database exporting data as well as security. effectively. Understanding the benefits and drawbacks of using electronic databases. Slide 6 of Keywords: 111 Access Database Information Data 6  Learning Databases Objectives Steps: To understand what a database is, why we Step 2 use them and how they are they built. Can use basic spreadsheet formula appropriately. To understand how Can filter and/or sort data in a spreadsheet. data is validated and verified. Can combine basic text and graphics. Update Thi To be able to create Step 3 and use a database Can use the sum function in a spreadsheet. effectively. Can create a basic chart or graph in a spreadsheet. Slide 7 of Keywords: 111 Access Database Information Data 7  Learning Databases Objectives Steps: To understand what a database is, why we Step 4 use them and how they are they built. Can explain and use data validation in spreadsheets. To understand how Can adjust variables in what-if scenarios. data is validated and verified. Step 5 Update Thi To be able to create Can use basic functions (SUM, COUNT, AVERAGE, MAX, MIN) and use a database appropriately in spreadsheets. effectively. Step 6 Can use conditional formatting appropriately in spreadsheets Slide 8 of Keywords: 111 Access Database Information Data 8  Learning Databases Objectives To understand what a SECTION 1 database is, why we Understanding what data is and how it can be stored in many ways – use them and how they are they built. not just on the computer. Understanding that databases can be used to store data electronically, To understand how data is validated and and why this is really useful. verified. Learning what we might want to do with a database and then using To be able to create Microsoft Access to create one. and use a database Learning how to enter, edit, view or delete data in a database. effectively. Learn the different types of data that can be entered into a database. Slide 9 of Keywords: 111 Access Database Information Data 9  Learning Databases Objectives What is a data? What is Information? To understand what a database is, why we Discuss with a partner, or as a class, what you understand by data, use them and how they are they built. information and the difference between them. To understand how data is validated and verified. To be able to create and use a database effectively. Slide 10 of Keywords: 111 Access Database Information Data 10  Summarise Learning Databases this in your Objectives What is a data? booklet To understand what a database is, why we Data is raw facts Information is created when that data has use them and how they are they built. and figures: been processed and becomes meaningful: To understand how John: 28 data is validated and verified. Claire: 49 These are scores from a test where the pass Jade: 40 mark was 35. To be able to create and use a database John needs to resit the test. effectively. Ahmed: 45 Chloe: 38 The average score is 40. Slide 11 of Keywords: 111 Access Database Information Data 11  Learning Databases Objectives How do we store data? To understand what a What do these images have in common? S database is, why we I use them and how they A are they built. To understand how data is validated and DA T E D verified. To be able to create T OR ! and use a database S effectively. P E R Slide 12 of 111 Keywords: N Access P A Database Information Data 12  Learning Databases Objectives How do we store data? To understand what a database is, why we All of those images showed data/information being stored in a paper format use them and how they are they built. In what way is this method of To understand how data is validated and storing data or information useful verified. and what are the drawbacks of To be able to create storing data this way vs. using and use a database electronic storage? effectively. Fill in the relevant boxes Keywords: in your booklet Slide 13 of 111 Access Database Information Data 13  Learning Databases Objectives Why do we store data electronically? To understand what a database is, why we Did you come up with any of these? use them and how they Electronic data storage systems are faster when adding, viewing or are they built. querying the data. To understand how Electronic data storage systems are more secure. data is validated and verified. They can be made to suit your specific needs. Users may need access to their data anywhere around the world, at any To be able to create and use a database time. effectively. Less risk of human error. Can be linked to other computer systems. Data and information is more organised. Slide 14 of Keywords: 111 Access Database Information Data 14  Learning Databases Objectives Why do we store data on paper? To understand what a database is, why we Did you come up with anything else in favour of using paper based storage? use them and how they It can be accessed when there is no power are they built. It cannot be hacked To understand how Can be physically locked away data is validated and Can’t be easily edited verified. To be able to create and use a database effectively. Slide 15 of Keywords: 111 Access Database Information Data 15  Learning Databases Objectives What would we want to do with a database? To understand what a database is, why we Now that we know that electronic databases are much more efficient than use them and how they paper based storage systems, what would we actually want them to do? are they built. To understand how Firstly, what is a database? data is validated and verified. A collection of organised data Can be used to search and sort through the data To be able to create Is stored on a computer and use a database effectively. Can be used to present data in different ways Can be used to calculate new data from existing data Slide 16 of Keywords: 111 Access Database Information Data 16  Learning Databases Objectives What would we want to do with a database? To understand what a database is, why we Lets take the example of Argos. They have a database system that can be use them and how they used in many different ways: are they built. To understand how We can go online and search for different items. data is validated and verified. We can see how many they have left and where they are. To be able to create and use a database effectively. When we buy items, the system updates automatically. When they bring in new stock , they add it to the system. Slide 17 of Keywords: 111 Access Database Information Data 17  Learning Databases Objectives What would we want to do with a database? To understand what a database is, why we Lets take the example of Argos. They have a database system that can be use them and how they used in many different ways: are they built. To understand how We can see the same system in the shop. data is validated and verified. We can see the same system in an app on our phone. To be able to create and use a database effectively. The item pickers are told what to pick by the system. Managers can get reports of sales figures at any time. Slide 18 of Keywords: 111 Access Database Information Data 18  Learning Databases Objectives What would we want to do with a database? To understand what a database is, why we Go onto the Argos website and look at some products. use them and how they are they built. In your booklets, write down as many different pieces of data about those To understand how products that you can spot. data is validated and verified. But instead of “Spiderman Lunchbox” write “Product Name” or instead of To be able to create and use a database £12.99, write down “Product Cost” effectively. You have 3 minutes to get as many written down as you can. Slide 19 of Keywords: 111 Access Database Information Data 19  Learning Databases Objectives What would we want to do with a database? To understand what a Summary: Fill in the database is, why we use them and how they relevant are they built. 1) We want to add new data boxes in To understand how 2) We want to view the existing data your data is validated and verified. booklet 3) We want to edit/update the data To be able to create and use a database 4) We want to remove the data effectively. 5) We want to ask questions about the data 6) We want to see reports about the data Slide 20 of Keywords: 111 Access Database Information Data 20  Making the database Slide 21 of 21 111  Learning Databases – Making a database Objectives What software can we use? To understand what a database is, why we There are 2 different types use them and how they of software packages that are they built. are used for databases. To understand how Software to create new data is validated and verified. database systems, or Software that is To be able to create and use a database already a pre-made, effectively. ready to use, “off the shelf” database system. We will be using Microsoft Access Add this into your Slide 22 of Keywords: booklet 111 Table Form Query Report 22  Learning Databases – Making a database Objectives What software can we use? To understand what a database is, why we Microsoft Access is a well-known use them and how they database management system are they built. produced by Microsoft and is part To understand how of the Microsoft 365 office suite. data is validated and verified. It was first released in November 1992, so it is over 30 years old. This To be able to create and use a database means that it has been developed and “fine-tuned” into a fairly easy to effectively. use system. Even beginners can have a small database up and running in 10 minutes – if they follow the instructions! Slide 23 of Keywords: 111 Table Form Query Report 23  Learning Databases – Making a database Objectives Step 1 – don’t open the software! To understand what a database is, why we You might think that you need to open Access and start clicking buttons – use them and how they wrong! are they built. To understand how NO! In fact, this can make the whole process twice as long. This is because data is validated and verified. we need to know what to make, before we make it. To be able to create and use a database So, step 1 is really – “let’s have a look at the data”. effectively. Once we understand the data, we can then construct the database that will be used to store it. Slide 24 of Keywords: 111 Table Form Query Report 24  Learning Databases – Making a database Objectives What data are we using? To understand what a database is, why we We have a sample set of data for you to use. If you had to type this in by use them and how they hand, it could take hours. are they built. To understand how However, we will be importing it into our database when it is ready, again, data is validated and verified. this will save us a lot of time and effort. To be able to create and use a database You will be directed to a text file that contains all the data when we need effectively. to get it. It will look weird when we open it but, we don’t need to worry about how it looks. Slide 25 of Keywords: 111 Table Form Query Report 25  Learning Databases – Making a database Objectives What data are we using? To understand what a database is, why we Here is what our looks like: Describe use them and how they are they built. this in your booklet To understand how data is validated and verified. To be able to create and use a database effectively. Slide 26 of Keywords: 111 Table Form Query Report 26  Learning Databases – Making a database Objectives What data are we using? To understand what a database is, why we These are the different pieces of data that we could store about each book. use them and how they In this example, they are separated by commas. are they built. To understand how data is validated and verified. Each one of these items is called a “FIELD” To be able to create and use a database Update effectively. your In reality, there could be dozens or hundreds of fields. booklet Slide 27 of Keywords: 111 Table Form Query Report 27  Learning Databases – Making a database Objectives What data are we using? Update To understand what a database is, why we These are the different pieces of data your use them and how they that we could store about each book , booklet are they built. they will be mostly different for each book. To understand how data is validated and verified. Each complete set of FIELDS is called a “RECORD” To be able to create Here are the first 5 records: and use a database effectively. Slide 28 of Keywords: 111 Table Form Query Report 28  Learning Databases – Making a database Objectives What data are we using? To understand what a database is, why we The data, once it is entered, can be viewed in a few different ways inside a use them and how they database, but it is all stored in a “TABLE”. Some databases require more are they built. than one table to store the data. To understand how data is validated and verified. In which case, data is grouped To be able to create and use a database in a special way. effectively. For example: Answer the questions in Slide 29 of Keywords: your booklet 111 Table Form Query Report 29  Learning Databases – Making a database Objectives What data are we using? To understand what a database is, why we This view shows a large table of data: Fields use them and how they are they built. To understand how data is validated and verified. Records To be able to create and use a database effectively. Slide 30 of Keywords: 111 Table Form Query Report 30  Learning Databases – Making a database Objectives What data are we using? To understand what a database is, why we What is special about the ISBN field?. use them and how they Would you be able to find an ISBN number that relates to more than one are they built. book? To understand how data is validated and verified. You will find that ISBN numbers are “Unique”, meaning there is only ever To be able to create and use a database one number for each book , they are never duplicated or reused. effectively. Knowing just the ISBN number, means you can identify any book , correctly, every time. Slide 31 of Keywords: 111 Table Form Query Report 31  Learning Databases – Making a database Objectives What data are we using? To understand what a database is, why we use them and how they Fields like the ISBN are known as the “PRIMAR Y KEY”. are they built. Knowing this value, will allow us to pull through all of the other data To understand how related to that key. Some other examples: data is validated and verified. To be able to create and use a database effectively. Slide 32 of Keywords: 111 Table Form Query Report 32  Data Types Slide 33 of 33 111  Learning Databases – Making a database Objectives What types of data are we using? To understand what a database is, why we Once we know the Primary Key, use them and how they the next thing to think about is are they built. what types of data are there To understand how and how big are they likely to be. data is validated and verified. Each field in our table of data To be able to create and use a database will need to be set as one of effectively. many different possible data types. As you can see, there are quite a few to choose from: Slide 34 of Keywords: 111 Table Form Query Report 34  Learning Databases – Making a database Objectives Data Types: Short text vs. Long text? To understand what a database is, why we use them and how they Stores text or combinations of text and are they built. numbers up to 255 characters. To understand how Short Commonly used for names, addresses, or data is validated and Text short descriptions. verified. Example: "John Doe", "123 Main St". To be able to create and use a database Used to store larger amounts of text, effectively. more than 255 characters, suitable for detailed descriptions or notes. Long Text Example: "This is a detailed description Keywords: of the product, including specifications Slide 35 of and usage instructions." 111 Table Form Query Report 35  Learning Databases – Making a database Objectives Data Types: Numbers and Autonumbers? To understand what a database is, why we use them and how they Used to store numerical data. Can be are they built. configured to store different types of numbers such as integer, long integer, To understand how Number single, double, etc. data is validated and verified. Example: 42, 3.14. To be able to create and use a database Automatically generates a unique effectively. number for each record, often used as AutoNumb a primary key to uniquely identify er records in a table. Keywords: Example: 1, 2, 3,... Slide 36 of 111 Table Form Query Report 36  Learning Databases – Making a database Objectives Data Types: Currency and Dates? To understand what a database is, why we use them and how they Stores currency values and numbers are they built. formatted as currency, ensuring To understand how Currenc accurate financial calculations by fixing data is validated and y the number of decimal places. verified. Example: £2.99, $99.99, €50.00. To be able to create and use a database Stores date and time values, allowing for effectively. Date/ date arithmetic and formatting. Time Example: 06/04/2024, 14:30:00. Slide 37 of Keywords: 111 Table Form Query Report 37  Learning Databases – Making a database Objectives Data Types: Yes/No and Hyperlinks? To understand what a database is, why we use them and how they Stores Boolean values (True/False or are they built. Yes/No). Suitable for fields that have To understand how Yes/No only two possible values. data is validated and verified. Example: Yes (True), No (False). To be able to create Stores hyperlinks to web pages, files, or and use a database other objects, enabling users to click and effectively. Hyperlin follow the link directly. k Example: http://www.example.com, C:\ Users\Documents\file.pdf. Slide 38 of Keywords: 111 Table Form Query Report 38  Learning Databases – Making a database Objectives Data Types: Calculated and Lookup Wizard? To understand what a database is, why we use them and how they Used to store results of a calculation. are they built. The calculation must reference other To understand how Calculat fields in the table. data is validated and ed verified. Example: [Quantity] * [Price] to calculate the total cost. To be able to create and use a database Not a data type itself, but a tool to effectively. create a field that looks up values in another table or list, providing a drop- Lookup down list for easier data entry. Wizard Keywords: Example: A drop-down list of customer Slide 39 of names from a Customers table. 111 Table Form Query Report 39  Learning Databases – Making a database Objectives Data Types: Match up exercise To understand what a database is, why we Now, to test your memory, try your best to match up data types, their basic use them and how they descriptions and a sample of the data. are they built. To understand how Here is the one of them done for you as an example: data is validated and verified. To be able to create and use a database effectively. Complete the match up exercise Slide 40 of Keywords: in your booklet 111 Table Form Query Report 40  Learning Databases – Making a database Objectives What types of data are we using? To understand what a database is, why we So, from our original data file, use them and how they we should now be able to work are they built. out what data type we need to To understand how set for each individual field. data is validated and verified. To be able to create and use a database effectively. Slide 41 of Keywords: 111 Table Form Query Report 41  Learning Databases – Making a database Objectives What types of data are we using? To understand what a database is, why we Look at the table below and fill in your booklet use them and how they are they built. Field Name Field Type Sample Value ISBN 9818505523 To understand how data is validated and Author’s First Bernadette verified. Name Author’s Last Moses To be able to create Name and use a database effectively. Book Title Stargazer Price £6.52 Type Complete the Children’s empty column in Slide 42 of Keywords: your booklet 111 Table Form Query Report 42  Learning Databases – Making a database Objectives What types of data are we using? To understand what a database is, why we Look at the table below and check/update your booklet use them and how they are they built. Field Name Field Type Sample Value ISBN Short Text 9818505523 To understand how data is validated and Author’s First Short Text Bernadette verified. Name Author’s Last Short Text Moses To be able to create Name and use a database effectively. Book Title Short Text Stargazer Price Currency £6.52 Type Lookup Wizard Update your Children's booklet if Slide 43 of Keywords: necessary 111 Table Form Query Report 43  Data Validation Slide 44 of 44 111  Learning Databases – Making a database Objectives Making sure the data is correct: To understand what a database is, why we To make sure that people enter data that fits with what is expected, at this use them and how they point, we can add some form of restrictions to the way the data is entered, are they built. so that it minimises silly data entry mistakes. To understand how data is validated and Imagine you want to buy some new trainers online, but in the quantity box verified. you enter 1,000,000 or in the size box, instead of size 7, you enter the word To be able to create ”cheese”. Obviously, neither of these should be allowed. and use a database effectively. This is called Data Validation Update your booklet if Slide 45 of Keywords: necessary 111 Table Form Query Report 45  Learning Databases – Making a database Objectives Making sure the data is correct: To understand what a database is, why we Data Validation will ensure the data roughly fits with what we expect, use them and how they however, it cannot ensure that the data is actually correct. are they built. To understand how For example, it should allow a person’s date of birth as 12/25/2024, but it data is validated and verified. cannot check if that is actually that person’s date of birth. To be able to create and use a database This is a process called Data Verification and it’s pretty much down to the effectively. data entry person to do the checking. Slide 46 of Keywords: 111 Table Form Query Report 46  Learning Databases – Making a database Objectives Making sure the data is correct: To understand what a database is, why we use them and how they are they built. Term Description Can a database To understand how do this? data is validated and verified. Data Validation Will ensure the data roughly fits with what Yes we expect. To be able to create and use a database Data Will ensure that the data is actually correct. No effectively. Verification Complete the table in your Slide 47 of Keywords: booklet 111 Table Form Query Report 47  Learning Databases – Making a database Objectives Making sure the data is correct: Validation Types To understand what a database is, why we Type of Description What do we put use them and how they are they built. Validati in the on database? To understand how Length Setting the maximum amount of Field size = 50 data is validated and Check characters that the field will accept verified. Format Setting the way the data entered should i.e Argos product code: To be able to create Check look 123/1234 and use a database effectively. Type Check A data type check confirms that the data Currency, short text, entered has the correct data type date/time etc. Slide 48 of Keywords: 111 Table Form Query Report 48  Learning Databases – Making a database Objectives Making sure the data is correct: To understand what a database is, why we Type of Description What do we put use them and how they are they built. Validati in the on database? To understand how Presence Forcing the user to enter a value, the field Required = Yes data is validated and Check cannot be left empty verified. Range Setting a maximum value, a minimum >=10 To be able to create Check value, or both. Between 0 and 100 and use a database effectively. Lookup Using the Lookup wizard to ensure only Small Check the values in your list can be entered Medium Large Slide 49 of Keywords: 111 Table Form Query Report 49  Learning Databases – Making a database Objectives Making sure the data is correct? To understand what a database is, why we Look at the table below and check/update your booklet use them and how they are they built. Field Name Field Sample Validation Rules Type Data To understand how data is validated and ISBN Short Text 981850552 verified. 3 Author’s First Short Text Bernadette To be able to create Name and use a database effectively. Author’s Last Short Text Moses Name Book Title Short Text Stargazer Price Currency £6.52 Update your Type Lookup Children's booklet if Slide 50 of Keywords: Wizard necessary 111 Table Form Query Report 50  Learning Databases – Making a database Objectives Making sure the data is correct? To understand what a database is, why we Look at the table below and check/update your booklet use them and how they are they built. Field Name Field Sample Validation Rules Type Data To understand how data is validated and ISBN Short Text 981850552 Presence Check verified. 3 Author’s First Short Text Bernadette Length Check To be able to create Name and use a database effectively. Author’s Last Short Text Moses Length Check Name Book Title Short Text Stargazer Length Check Price Currency £6.52 Update Range Check your Type Lookup Children's Lookup booklet Check if Slide 51 of Keywords: Wizard necessary 111 Table Form Query Report 51  Learning Databases – Making a database Objectives Making sure the data is correct? To understand what a database is, why we At this point, our database planning is complete. use them and how they We have: are they built. Identified all the data we need to enter. To understand how Determined how the data links together and how many tables we would data is validated and verified. need to store the data. Determined what type and size of data we have. To be able to create Set out various ways in which the data van be validated to make sure it and use a database effectively. fits with what we expect. We are now ready to make the database! Slide 52 of Keywords: 111 Table Form Query Report 52  Creating the Database Slide 53 of 53 111  Learning Databases – Making a database Objectives Using Microsoft Access software: To understand what a database is, why we There are two main ways of creating a database, and which one you choose use them and how they will depend on how you have the data stored currently. are they built. 1) If the data is in a format like our example data, it can usually be To understand how imported into an empty database and the database will build most of data is validated and verified. what you need for you 2) If you have the data stored on paper, or in a format that that can not be To be able to create and use a database imported for some reason, then you will need to build the database from effectively. scratch, and then type the data in manually. Slide 54 of Keywords: 111 Table Form Query Report 54  Learning Databases – Making a database Objectives Using Microsoft Access software: To understand what a database is, why we We will be importing the data to save time. use them and how they are they built. Now we can open Microsoft Access! To understand how data is validated and verified. In the task bar search box, type: access To be able to create and use a database effectively. Then click on the Access 2016 app to launch it: Slide 55 of Keywords: 111 Table Form Query Report 55  Learning Databases – Making a database Objectives Using Microsoft Access software: To understand what a database is, why we Once the software opens, click this button: use them and how they are they built. It will then ask you what you want to call To understand how the database, and where you want to save it. data is validated and verified. To be able to create and use a database effectively. You will need to change both of these. Slide 56 of Keywords: 111 Table Form Query Report 56  Learning Databases – Making a database Objectives Using Microsoft Access software: To understand what a database is, why we Change the name of the database file to: use them and how they are they built. Books Database To understand how data is validated and verified. Then, click the little folder icon and then navigate to your ICTePortfolios folder To be able to create and use a database effectively. Once both of these are done, click the Create button Slide 57 of Keywords: 111 Table Form Query Report 57  Learning Databases – Making a database Objectives Using Microsoft Access software: To understand what a database is, why we When a new database opens, it is completely empty. use them and how they are they built. Any new tables, queries, forms To understand how or reports that we make will data is validated and verified. appear down the left once they have been saved. To be able to create and use a database effectively. Any data or layouts that make up the objects listed above, will appear on the right. Slide 58 of Keywords: 111 Table Form Query Report 58  Learning Databases – Making a database Objectives Using Microsoft Access software: To understand what a database is, why we Because we want to use the import process to help build our database, we use them and how they need to get the data. are they built. To understand how Click the External Data data is validated and verified. tab and select Text File To be able to create and use a database Now, in the new window, click effectively. Browse. Then navigate your way to find the “Books” data file. Then click OK. Slide 59 of Keywords: 111 Table Form Query Report 59  Learning Databases – Making a database Objectives Using Microsoft Access software: To understand what a database is, why we Because the data has use them and how they been separated or are they built. “delimited” by commas, To understand how we can just click next data is validated and verified. as it is already pre-selected. To be able to create and use a database effectively. You may notice the data looks the same as it did earlier. Slide 60 of Keywords: 111 Table Form Query Report 60  Learning Databases – Making a database Objectives Using Microsoft Access software: To understand what a database is, why we The Comma option is usually pre-selected but you need to click the box next use them and how they to “First row contains Field Names” are they built. To understand how data is validated and verified. To be able to create and use a database effectively. Slide 61 of Keywords: 111 Table Form Query Report 61  Learning Databases – Making a database Objectives Using Microsoft Access software: To understand what a database is, why we After clicking Next, we are given the option, in this window, of picking a use them and how they data type for each column. are they built. To understand how Access is quite good at data is validated and verified. choosing the most likely data type for each field. To be able to create and use a database But here, we need to change effectively. ISBN from “Long Integer” to “Short Text”, and then we just click “Next” again. Slide 62 of Keywords: 111 Table Form Query Report 62  Learning Databases – Making a database Objectives Using Microsoft Access software: To understand what a database is, why we Here, Access has decided we might need a Primary Key, but we already have use them and how they one, the ISBN number. So we click “Choose my own” and it will select the are they built. ISBN for us. To understand how data is validated and verified. To be able to create and use a database effectively. Slide 63 of Keywords: 111 Table Form Query Report 63  Learning Databases – Making a database Objectives Using Microsoft Access software: To understand what a database is, why we Finally, Access just needs us to save the table where it is storing the data, so use them and how they just type in the name “tbl_books” and click Finish then Close. are they built. To understand how data is validated and verified. To be able to create and use a database effectively. Slide 64 of Keywords: 111 Table Form Query Report 64  Learning Databases – Making a database Objectives Using Microsoft Access software: To understand what a database is, why we Double clicking the object name will open it on the right use them and how they are they built. To understand how data is validated and verified. To be able to create and use a database effectively. Slide 65 of Keywords: 111 Table Form Query Report 65  Learning Databases – Making a database Objectives Using Microsoft Access software: To understand what a database is, why we Right clicking the tab “tbl_books” will allow us to do several things. use them and how they We can save or close the object at any time. are they built. To understand how But we want to be able to data is validated and verified. view the fields so we can edit them, so we need to To be able to create and use a database from this “Datasheet View” effectively. to the “Design View”. Click “Design View” here. Slide 66 of Keywords: 111 Table Form Query Report 66  Learning Databases – Making a database Objectives Using Microsoft Access software: To understand what a database is, why we The Design View allows us to change many of the data validation options use them and how they we were are they built. looking at earlier, such as To understand how field size or a validation data is validated and verified. rule. To be able to create and use a database We could also change the effectively. data type, the Primary Key or the field name here but we won’t in this example. Slide 67 of Keywords: 111 Table Form Query Report 67  Learning Databases – Making a database Objectives Using Microsoft Access software: To understand what a database is, why we There are many validation methods we could use here, but we will just do a use them and how they couple. are they built. To understand how First, the ISBN number is data is validated and verified. 10 digits long, so we can set the Field Size to 10 To be able to create and use a database We can then set the Author effectively. Names and Book Title to 50 as there will won’t be any names bigger than this. Slide 68 of Keywords: 111 Table Form Query Report 68  Learning Databases – Making a database Objectives Using Microsoft Access software: To understand what a database is, why we For the Price, we can assume that there will not be any books sold for less use them and how they than £0 and probably not more than £1000. So we can type out the are they built. validation rule and text as shown below to achieve this. To understand how data is validated and verified. To be able to create and use a database effectively. The Validation Text is a message box that appears if the user enters a value that is not between 0 and 1000. Slide 69 of Keywords: 111 Table Form Query Report 69  Learning Databases – Making a database Objectives Using Microsoft Access software: Forms To understand what a database is, why we Now that the data is in and validated, we can now make a Form to view use them and how they and edit the data more easily. We use these kinds of forms online all the are they built. time, so once ours is set up, using it should be very easy. To understand how data is validated and verified. First, we need to save and close the table. So right click the tab To be able to create and use a database and click Save, then right click effectively. again and click Close. It will warn us about losing data, we won’t, just click OK Slide 70 of Keywords: 111 Table Form Query Report 70  Learning Databases – Making a database Objectives Using Microsoft Access software: Forms To understand what a database is, why we To create a form, as with most objects we will be creating, we will go to the use them and how they Create menu and select the correct option that best fits our needs. are they built. To understand how data is validated and verified. To be able to create and use a database Tables, Queries, Forms and Reports can all be created from here, with most effectively. of them having manual or automatic “using a Wizard” options. Slide 71 of Keywords: 111 Table Form Query Report 71  Learning Databases – Making a database Objectives Using Microsoft Access software: Forms To understand what a database is, why we We will use the form wizard as it is easy to follow and creates a usable use them and how they form quite quickly. We can play around with the design afterwards if we are they built. want to. To understand how data is validated and verified. To be able to create and use a database effectively. You will see that this is one of the easier things to create in Access, but often one of the longest because we can spend a loooooong time changing the way it looks. Slide 72 of Keywords: 111 Table Form Query Report 72  Learning Databases – Making a database Objectives Using Microsoft Access software: Forms To understand what a database is, why we First, we need to select the fields from the table we want to show on the use them and how they form. In this case, it’s all of them, so click the double arrow in the middle are they built. and click “Next”. To understand how data is validated and verified. To be able to create and use a database effectively. Slide 73 of Keywords: 111 Table Form Query Report 73  Learning Databases – Making a database Objectives Using Microsoft Access software: Forms To understand what a database is, why we There are 4 pre-made layout styles we can choose from, but the most familiar use them and how they is the columnar style, which is pre selected, so just click “Next”. are they built. To understand how You can click the others to data is validated and verified. have a look but you will see the columnar style is the way To be able to create and use a database most forms are made these effectively. days, with one field below the other in a column. Slide 74 of Keywords: 111 Table Form Query Report 74  Learning Databases – Making a database Objectives Using Microsoft Access software: Forms To understand what a database is, why we Rename the Form “frm_Books” and click Finish. use them and how they are they built. You could choose to modify To understand how the design straight away, data is validated and verified. but we might as well have a look at if first. To be able to create and use a database effectively. Most forms created this way need very little editing. Slide 75 of Keywords: 111 Table Form Query Report 75  Learning Databases – Making a database Objectives Using Microsoft Access software: Forms To understand what a database is, why we Notice the form has appeared on the left. use them and how they are they built. Also notice that the form is To understand how pretty much ready to go data is validated and verified. To be able to create and use a database And also notice it is telling effectively. you how many records there are. In our case, 60. Slide 76 of Keywords: 111 Table Form Query Report 76  Learning Databases – Making a database Objectives Using Microsoft Access software: Forms To understand what a database is, why we This is a built in record selector. use them and how they It allows us to browse through the records, skip to the first or last, or create are they built. a new record. This is OK, but we will add some nice buttons on the form to To understand how do this for us. data is validated and verified. To modify the design, right click To be able to create and use a database the tab and select Design View. effectively. The Design View will allow us to change placement, colours and fonts. We could even add images. Slide 77 of Keywords: 111 Table Form Query Report 77  Learning Databases – Making a database Objectives Using Microsoft Access software: Forms To understand what a database is, why we The Design View looks similar for Forms and Reports so learning form use them and how they design will help later on with reports. are they built. To understand how Firstly, there are a lot of data is validated and verified. buttons and a lot of things that can go wrong, but with To be able to create and use a database practice, it should be fairly effectively. straight-forward to get a little bit of design into our work. Slide 78 of Keywords: 111 Table Form Query Report 78  Learning Databases – Making a database Objectives Using Microsoft Access software: Forms To understand what a database is, why we PAY ATTENTION to these tips!!!!! Your teacher will show you, but this use them and how they can go wrong very quickly. are they built. These are the To understand how This is the button buttons you data is validated and verified. to add a new field need if you want to add To be able to create and use a database This one adds a label new content to effectively. so you can add text the form. If the form does not This one adds a button need them, don’t use them! Slide 79 of Keywords: 111 Table Form Query Report 79  Learning Databases – Making a database Objectives Using Microsoft Access software: Forms To understand what a database is, why we As well as adding buttons etc., we might want to change the way the form use them and how they looks. To do this, we need to get to the Format menu. are they built. To understand how data is validated and verified. To be able to create and use a database You may notice a lot of the commands here look like Word or PowerPoint, effectively. this is because Access is also written by Microsoft. Slide 80 of Keywords: 111 Table Form Query Report 80  Learning Databases – Making a database Objectives Using Microsoft Access software: Forms To understand what a database is, why we All you need to do is click on or highlight the part of the form you wish to use them and how they change and then set the options at the top, just like Word or PowerPoint. are they built. To understand how Here, the Form title data is validated and verified. has had the font size and colour changed to To be able to create and use a database make it stand out. effectively. Slide 81 of Keywords: 111 Table Form Query Report 81  Learning Databases – Making a database Objectives Using Microsoft Access software: Forms To understand what a database is, why we You can make changes to multiple objects at the same time by carefully use them and how they selecting them as a group. are they built. To understand how By dragging your mouse data is validated and verified. to create a box around the items, you can get To be able to create and use a database Access to select them effectively. when you let go. This means you don’t have to edit them individually. Slide 82 of Keywords: 111 Table Form Query Report 82  Learning Databases – Making a database Objectives Using Microsoft Access software: Forms To understand what a database is, why we You might want to change the colour of the back ground of your form. To use them and how they do this, click the back ground and click the Shape Fill- option, then pick the are they built. colour you want. To understand how data is validated and verified. To be able to create and use a database effectively. Slide 83 of Keywords: 111 Table Form Query Report 83  Learning Databases – Making a database Objectives Using Microsoft Access software: Forms To understand what a database is, why we You can make many other changes to the design, but remember it is a form use them and how they and you need to design it so it can be used easily. It should look are they built. professional, not hideous… To understand how data is validated and verified. Keep switching between Form View and Design view to see how your To be able to create and use a database changes look. effectively. Don’t forget to save it as well! Slide 84 of Keywords: 111 Table Form Query Report 84  Learning Databases – Making a database Objectives Using Microsoft Access software: Forms To understand what a database is, why we When your form looks OK, we can add some buttons to help us navigate use them and how they through the records more easily. are they built. To understand how Click the button data is validated and verified. and then draw a small rectangle the same To be able to create and use a database size and shape as the effectively. button you want to make. Slide 85 of Keywords: 111 Table Form Query Report 85  Learning Databases – Making a database Objectives Using Microsoft Access software: Forms To understand what a database is, why we In the new window that pops up, we want to select these options. This use them and how they will allow us to create a button that moves us to the next record in the are they built. database. To understand how data is validated and verified. We will repeat all of this again in a minute, so we can To be able to create and use a database create a button that looks effectively. back at the previous record. Slide 86 of Keywords: 111 Table Form Query Report 86  Learning Databases – Making a database Objectives Using Microsoft Access software: Forms To understand what a database is, why we In the next window, you can choose a text based button or one with an use them and how they image. The choice is yours. Then click Finish in the next window, don’t are they built. worry about the text. To understand how data is validated and verified. To be able to create and use a database effectively. Slide 87 of Keywords: 111 Table Form Query Report 87  Learning Databases – Making a database Objectives Using Microsoft Access software: Forms To understand what a database is, why we So, repeat to add a Previous Record button. Remember, that the buttons use them and how they will not be active until you view it in Form View mode. are they built. To understand how Test out the next data is validated and verified. and previous buttons to make sure they To be able to create and use a database work as expected. effectively. Don’t forget to save the form. Slide 88 of Keywords: 111 Table Form Query Report 88  Learning Databases – Making a database Objectives Using Microsoft Access software: Queries To understand what a database is, why we We may need to go back and add more to the form later but for now it is use them and how they ready to use. are they built. To understand how The next thing we might want to do is create a facility where we can get the data is validated and verified. database to show us specific data based upon a query that we build in. To be able to create and use a database This introduces the third element of databases: Queries. effectively. Slide 89 of Keywords: 111 Table Form Query Report 89  Learning Databases – Making a database Objectives Using Microsoft Access software: Queries To understand what a database is, why we A query is a way of telling Access which records you want to see (rather use them and how they than all of them) and which fields you want to see (rather than all of them) are they built. To understand how Before we can make a query though, it might be useful to know which data is validated and verified. records and fields we want it to show. To be able to create and use a database For this example, we will ask the database to show only Children's books effectively. costing £7 or more. Rather than us checking every record, the query will do this instantly. Slide 90 of Keywords: 111 Table Form Query Report 90  Learning Databases – Making a database Objectives Using Microsoft Access software: Queries To understand what a database is, why we Close down any open forms or tables, then choose Query Design from the use them and how they Create menu. are they built. To understand how There is a Query Wizard data is validated and verified. but it is more hassle than it’s worth for simple To be able to create and use a database queries like ours. effectively. The next bit is very fiddly, so pay attention! Slide 91 of Keywords: 111 Table Form Query Report 91  Learning Databases – Making a database Objectives Using Microsoft Access software: Queries To understand what a database is, why we All we need to do here is tell Access where the data is. use them and how they are they built. In our example, there is only one table, so we can just click Add and then To understand how Close. data is validated and verified. To be able to create and use a database effectively. More complicated databases could have dozens of tables and other queries to choose from… Slide 92 of Keywords: 111 Table Form Query Report 92  Learning Databases – Making a database Objectives Using Microsoft Access software: Queries To understand what a database is, why we You may need to expand the table here in order to see all the fields in it. use them and how they Just click and drag the corner a bit. are they built. To understand how data is validated and verified. To be able to create and use a database effectively. We will now need to select which fields we want and what criteria we want to apply, to get our desired result. Slide 93 of Keywords: 111 Table Form Query Report 93  Learning Databases – Making a database Objectives Using Microsoft Access software: Queries To understand what a database is, why we You can now double click the fields in the use them and how they order you want them, or you can select are they built. them from the drop downs at the bottom To understand how of the screen. data is validated and verified. To be able to create and use a database In our example, we are going effectively. to add the Book Title, Price and Type fields because these contain the data we need. We do not need to add any others; we don’t need them. Slide 94 of Keywords: 111 Table Form Query Report 94  Learning Databases – Making a database Objectives Using Microsoft Access software: Queries To understand what a database is, why we Once we have added the Book Title, Price and Type fields, we just need to use them and how they think whether we want to sort the data in any way. In our example, we are they built. will sort by Book Title, in Ascending (alphabetical A-Z) order. To understand how data is validated and verified. To be able to create and use a database effectively. Slide 95 of Keywords: 111 Table Form Query Report 95  Learning Databases – Making a database Objectives Using Microsoft Access software: Queries To understand what a database is, why we Running the query now will show all of the use them and how they records, but only the 3 fields we selected. are they built. Hit the Run button to see for yourself. To understand how data is validated and verified. You will also notice that the records are sorted To be able to create and use a database alphabetically, by title, in effectively. ascending order, just as we had instructed it to. Now, we need to edit our query to add in extra criteria. Slide 96 of Keywords: 111 Table Form Query Report 96  Learning Databases – Making a database Objectives Using Microsoft Access software: Queries To understand what a database is, why we To go back to the Design View to edit our query, right click the tab and use them and how they choose Design View. are they built. To understand how Once in design view, we can data is validated and verified. add in the additional criteria to filter the records so that To be able to create and use a database it only shows Children’s books effectively. that cost more than £7. We do this by adding information to the Criteria row. Slide 97 of Keywords: 111 Table Form Query Report 97  Learning Databases – Making a database Objectives Using Microsoft Access software: Queries To understand what a database is, why we To shows Children’s books that cost more than £7, we just type the right use them and how they criteria into the right column are they built. To understand how In the Type column, add the text (in speech marks) data is validated and verified. “Childrens” To be able to create and use a database In the Price column effectively. add the following: >7 (no speech marks) which means more than 7 Slide 98 of Keywords: 111 Table Form Query Report 98  Learning Databases – Making a database Objectives Using Microsoft Access software: Queries To understand what a database is, why we Now run the query again, this time you use them and how they should see the number of records is much less, and are they built. the ones that you are left with, all match the To understand how criteria given. data is validated and verified. Before: After: To be able to create and use a database effectively. Slide 99 of Keywords: 111 Table Form Query Report 99  Learning Databases – Making a database Objectives Using Microsoft Access software: Queries To understand what a database is, why we We need to save the query now, so right click the tab and choose save, the use them and how they give it a sensible name, In this example, the name qry_Childrens_Books is are they built. appropriate. To understand how data is validated and verified. It now means that this query can be run any time we want to, and it will To be able to create and use a database always pull through the most up to effectively. date data – every time. All that is left now, is to create a way in which the results can be printed out – this will be a report. Slide 100 Keywords: of 111 Table Form Query Report 100  Learning Databases – Making a database Objectives Using Microsoft Access software: Reports To understand what a database is, why we We can make a report in several ways, but for our needs, running the Report use them and how they Wizard will do just fine. are they built. First, click once on the new query to To understand how select it. Then , from the Create menu, data is validated and verified. click Report Wizard To be able to create and use a database effectively. Slide 101 Keywords: of 111 Table Form Query Report 101  Learning Databases – Making a database Objectives Using Microsoft Access software: Reports To understand what a database is, why we For a basic report, there is very little to do while using the wizard, but here use them and how they are the options we need. First, click the double arrows to add all the fields are they built. we want. To understand how data is validated and verified. To be able to create and use a database effectively. Slide 102 Keywords: of 111 Table Form Query Report 102  Learning Databases – Making a database Objectives Using Microsoft Access software: Reports To understand what a database is, why we Click next on the next three screens. use them and how they are they built. To understand how data is validated and verified. To be able to create and use a database effectively. Slide 103 Keywords: of 111 Table Form Query Report 103  Learning Databases – Making a database Objectives Using Microsoft Access software: Reports To understand what a database is, why we On this one, change the title to rpt_Childrens_Books and click Finish. Here use them and how they is the report. are they built. To understand how data is validated and verified. To be able to create and use a database effectively. Slide 104 Keywords: of 111 Table Form Query Report 104  Learning Databases – Making a database Objectives Using Microsoft Access software: Reports To understand what a database is, why we If you are happy with the report, that’s it, it’s done, but most people want use them and how they to add a bit of design to make it look more professional or make it match are they built. their other office documentation. To understand how data is validated and verified. We will edit the design a little so right click the tab and go to To be able to create and use a database Design View. This will allow you effectively. to edit the report in a similar way to the form we did earlier. Slide 105 Keywords: of 111 Table Form Query Report 105  Learning Databases – Making a database Objectives Using Microsoft Access software: Reports To understand what a database is, why we Don’t move the boxes up or down between sections as this will cause issues, use them and how they but we can edit fonts, colours, labels and the back ground. are they built. To understand how data is validated and verified. To be able to create and use a database effectively. Slide 106 Keywords: of 111 Table Form Query Report 106  Learning Databases – Making a database Objectives Using Microsoft Access software: Reports To understand what a database is, why we Don’t change the wording of any of these orange fields. You can change the use them and how they font / colour if you want to though. are they built. To understand how data is validated and verified. To be able to create and use a database Use the design tools in the Format menu. effectively. Slide 107 Keywords: of 111 Table Form Query Report 107  Learning Databases – Making a database Objectives Using Microsoft Access software: Reports To understand what a database is, why we Regularly switch between Design View and use them and how they Report View to see what your report looks are they built. like. Keep editing until you are happy that To understand how you have a professional looking report that data is validated and verified. is ready to print. To be able to create and use a database Remember, the report needs to be readable and not be a bog mess of colours effectively. and weird fonts. Whilst it is fun to do, it is not the look that we are going for here. But there is room for a little creativity though. Slide 108 Keywords: of 111 Table Form Query Report 108  Learning Databases – Making a database Objectives Using Microsoft Access software: Reports To understand what a database is, why we Compare these examples: before and after designing. use them and how they are they built. To understand how data is validated and verified. To be able to create and use a database effectively. Small changes can make a big difference. Slide 109 Keywords: of 111 Table Form Query Report 109  Learning Databases – Making a database Objectives Using Microsoft Access software: Overview To understand what a database is, why we Once you have saved your report and then saved your database, our use them and how they database is almost ready to use. are they built. To understand how Now we want to add a few extra features that will make the database even data is validated and verified. more usable. To be able to create and use a database The next slide will list several additions to the database that you will need effectively. to try and implement on your own. Try your best. The world won’t explode if you get it wrong, but your teacher might if you don’t try… Slide 110 Keywords: of 111 Table Form Query Report 110  Learning Databases – Making a database Objectives Using Microsoft Access software: Additions To understand what a database is, why we 1) Add a button to your form that allows you to add a new record. Then use them and how they use it to add the following 2 books to the database: are they built. To understand how data is validated and Field Name 1st New 2nd New verified. Record Record ISBN 9879586740 9686845673 To be able to create and use a database Author’s First Bob Harry effectively. Name Author’s Last Builder Potter Name Book Title Can We Fix Science – Why? it? Slide 111 Keywords: Price £12.99 £18.99 of 111 Table Type Form Childrens QueryEducation Report 111  Learning Databases – Making a database Objectives Using Microsoft Access software: Additions To understand what a database is, why we 2) Create a new query that pulls though Education books that cost less use them and how they than £20. The query needs to include ISBN, Book Title, Price and Type. are they built. Sort the query by Price so the most expensive is at the top. To understand how data is validated and verified. 3) Create a suitable report to show the information from this new query. Edit the title and design to give it a more professional appearance. To be able to create and use a database effectively. Save all of this as you go, and again at the end. Slide 112 Keywords: of 111 Table Form Query Report 112