Microsoft Access Specialist Test Prep

1. 3 Week Class Mr. Louis Sequin Microsoft AccessSpecialistTest Prep

2. Louis Sequin www.hire-a-sequin.com Louis_sequin@yahoo.com

3. Schedule

4. We have a LOT of material to cover in three classes. The only way we can possibly do this is to have a set of rules and all agree to follow them. For the good of the class as a Whole I reserve the right to eject or deny admission to anyone who refuses to follow These rules. Rules

5. No talking unless I ask the class a question. Questions from the class only when I open the class up to questions. Write your questions down so you won’t forget them. Questions may only be asked by students at computers. Computers are assigned in order of registration. Breaks will end promptly according to schedule. If there are 5 people in the class at the end of a break I will begin the lecture with 5 people. If you need to take a personal break at any time it is fine to simply get up and exit the room. Rules

6. Links to exam 77-885 information http://www.microsoft.com/learning/en/us/exam.aspx?ID=77-881&Locale=en-us http://www.certiport.com/Portal/desktopdefault.aspx?page=common/pagelibrary/mos2010.html www.hire-a-sequin.com

7. Hard Class Easy Class • Hard Test! • Easy Test!

8. If you wanna pass diss. • Practice!

9. Access 2010 Inside And Out Step by Step Access 2010 Plain & Simple Access 2010 Access 2010 for Dummies General Knowledge Access Books

10. MOS Study Guide 2010 Microsoft Access 2020 Official Academic Course Test Prep Study Materials

11. We will talk more about the exam and registering for it next class! Questions?

12. 5 Minute Break

13. Introduction RDBMS What the heck is an RDBMS? What good are they?

14. What is an RDBMS? That’s a good question. It’s a bunch of letters for one thing. The letters mean something: RDBMS stands for Relational Data Base Management System. That’s a mouthful, but it’s not as complicated as it sounds. Let’s break it down.

15. What is an RDBMS? At the root is the DB or the Data Base. A Data Base is just a collection of data. Data is any kind of information, like a name or someone’s age. So a list of names would be an example of a data base! Louis Sequin Rub Butler Jim Moir Nicole Lesperance Don Berry Emily Good

16. What is an RDBMS? A Relational Data Base is a certain kind of Data Base where several pieces of information can be associated, or… RELATED… to another piece of information through a link called a Relation. For example imagine that some of these people each have two or more books checked out from this library. 1 Dune 1 Foundation 1 2001 3 Peter Rabit 3 Hot HotRotti for DaddaJi 3 Babar 2 Vegan Cooking for Dummies 2 How to Survive on Cabbage Louis Sequin Rub Butler Jim Moir Nicole Lesperance Don Berry Emily Good

17. What is an RDBMS? And a Management System basically, in this case, is a program, a piece of Software, that is dedicated to Managing all these lists, Databases, and the ways they are related to each other. Viola, RDBMS, Relational Data Base Management System. FYI, you will see Data Base written as two words and sometimes as one, Database. Don’t let it throw you. Hence you will also see RDMS rather than RDBMS.

18. Back to Relations These two list are related to each other. You match the 1’s with the 1’s. You match the 2’s with the 2’s and so forth. These numbers that you are matching are what is referred to as the primary key of the first list. Each of the numbers 1-6 refers to one and only one line, or record of data. Does the 2nd list have a primary key? 1 Dune 1 Foundation 1 2001 3 Peter Rabit 3 Hot HotRotti for DaddaJi 3 Babar 2 Vegan Cooking for Dummies 2 How to Survive on Cabbage Louis Sequin Rub Butler Jim Moir Nicole Lesperance Don Berry Emily Good

19. Primary Keys That is correct, the 2nd list doesn’t have a primary key because there is no one piece of information, Data, in each line, Record, that uniquely identifies each one. We can fix that. Is that a problem? It can be. What if we want to record many pieces of information about each book, like all the people who have checked them out? 1 Dune 1 Foundation 1 2001 3 Peter Rabit 3 Hot HotRotti for DaddaJi 3 Babar 2 Vegan Cooking for Dummies 2 How to Survive on Cabbage Louis Sequin Rub Butler Jim Moir Nicole Lesperance Don Berry Emily Good

20. Tables Well, we can fix that very easily. Now each record in the 2nd List has a primary key. Just as a point of interest, it is more normal to refer to the lists below as Tables than as lists. There is no difference between a table and a list though, sort of. 1 Dune 1 Foundation 1 2001 3 Peter Rabit 3 Hot HotRotti for DaddaJi 3 Babar 2 Vegan Cooking for Dummies 2 How to Survive on Cabbage Louis Sequin Rub Butler Jim Moir Nicole Lesperance Don Berry Emily Good

21. Tables So, okay , I lied. There are differences between lists and tables. But not important ones. For example, tables have… Columns Columns Rows Rows

22. Rows But, now we have a problem. What do 48 and 220 and 6660 mean? Perhaps 48 is the number of dollars in Louis’ bank account. Maybe 220 is his IQ? Who knows. Rows go left to right and are like the lines in a list. Each one contains a set of related information or a Record, for example… Rows Louis Sequin 48 220 6660 Rows

23. Columns This is where columns come in. Each column has a name that identifies the data kept in that column. Columns Columns Louis Sequin 48 220 6660 FNameLName Age IQ HouseNum

24. Primary Key Remember our friend the Primary Key, every row of every table has a primary key! Primary Key

25. Table Names Each table has a name. This one might be called Contacts orsomething descriptive like that.

26. Back to Relations Remember our old friends the list of people and the list of books they had checked out. Lets see what that looks like as a couple of tables called, respectively Patrons and Checkouts. Louis Sequin Rub Butler Jim Moir Nicole Lesperance Don Berry Emily Good 1 Dune 1 Foundation 1 2001 3 Peter Rabit 3 Hot HotRotti for DaddaJi 3 Babar 2 Vegan Cooking for Dummies 2 How to Survive on Cabbage

27. Back to Relations Notice in the Checkouts table there is a column labeled Fkey. Notice also that it is the column that Relates to the patrons table. Fkey stands for Foreign Key. Foreign Key is the name we give to a primary key from another table that is used as a Relation into that table Checkouts Patrons

28. Let’s throw a Monkey Wrench in. This is a perfectly reasonable database for keeping track of people and the books they have checked out. Now lets say we want to keep track, also, of Books and all the people that have checked them out as long as the library has owned them. Checkouts Patrons

29. How about we do this We could create a table called Books and a Related table called checkoutHistory (camel’s hump notation). Checkouts Patrons Books checkoutHistory If you are smart and I know that you are you will be bothered by the fact that we are keeping track of two tables of books and two tables of names. This is BAD BAD NAUGHTY!

30. This is better! In the following Database we keep track of each piece of information only once. Patrons Books booksCheckedOutToPatrons Notice that booksCheckedOutToPatrons has nothing in it but Keys. One Primary Key and two Foreign Keys that Relate into Patrons and into Books. This is called a Relation Table AND because each single record from Patrons is related to many records in Books this is called a One To Many Relation.

31. More on Relations There are, in fact , only three (3) different types of relations in databases. Patrons Books booksCheckedOutToPatrons It makes sense if you think about it there are One to Many Relations. These are by FAR the most common. There are One to One Relations and these are the least common. There are Many to Many Relations which are less common than One to Many.

32. Many to Many Relations If you think about it Books and Patrons actually have a Many to Many Relation. Each Patron can have Many Books checked out, but likewise each Book may have been checked out by Many Patrons Patrons booksCheckedOutToPatrons patronsWhoHaveCheckedOutBooks Books Theoretically this is considered to be a single Relation, but in reality in Databases it is always represented by two separate One to Many Relations.

33. Questions?

34. Lunch Break Be Back at 12:30

35. Introduction RDBMS What the heck is an RDBMS? What good are they?

36. Section II on RDMS

37. Database Normalization Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

38. Database Normalization Redundancy: This means that you are keeping track to same information, data, in more than one table. You DON’T want this. BAD BAD, Redundancy, BAD DOG! Dependency: When you have a Database with a lot of dependencies, changing one small thing in one table tends to break things in other places in the data base. Dependency is also BAD, Naughty, Bad Bad. Dependency is most often caused by redundancy. For example, if you are keeping track of someones name in three different tables rather than just one and you change their name in only one table… sometimes when you use the database you will get the right name and sometimes you won’t.

39. Third Normal Form There are 6 levels of database normalization. It is generally recommended that every database adhere to the first three rules of Normalization and any database that does is said to be in 3rd normal form. Tables may not contain duplicate columns of data and each and every row of data in a table (record) MUST be uniquely identified by a primary key which can be a single column or a set of columns. Generally speaking keep related data in separate tables. No single column in a single row of a table should contain multiple data points, and no subset of rows should contain multiple data points referring to a single key. Remove columns that are not dependent upon the primary key. This basically means that you shouldn’t include calculated or “built” columns.

40. Third Normal Form Don’t lose sleep over third normal form. It’s simpler than it sounds and it’s pretty intuitive. Let’s restate the rules in plainer English.

41. First Normal Form Don’t waste space! Specifically don’t waste space by having three columns recording the same person’s first name in one table because it’s just plain dumb and it could create problems later when you want to change someone’s first name. Duh, you have to remember to change all three first names instead of just one. Obvious right? Tables should contain related data. If you have unrelated data in your table it should go into a separate table. Don’t record the names of all the people that checkout books in the table that just keeps track of books for the library and don’t keep track of the books patrons have checked out in a table that is just about the patrons. Also, you should have an address, primary key, that points to each and every row in the table and only to that row!

42. Second Normal Form Simply stated a single table should NEVER contain any One to Many or Many to Many relationships. For example In the above example there is a One to Many relationship between the data in the first three columns and the data in the last column. This is BAD. Just DON’T do it. BAD Baddy Badnick!

43. Third Normal Form Don’t have calculated or created columns in your tab le. For example… Who can tell me what the problem is with having the FullName column and the NumCharsInFullName column?

44. Types What are they and why do we need them. The simple answer is that by keeping track of the type of each column of data the RDBMS knows what it can and cannot do with the data in that column. For example, you can’t add Lname to Fname. Lname+Fname= ???. We know that cause we’re smart but computers are dumb, dumb, dumb. The only way they know that they might add two values from the Key column together but not two Names is because we tell the computer by giving each column a type.

45. Types In Access there are 12 different Types that columns can be. They are: • Text • Memo • Number • Date/Time • Currency • AutoNumber • Yes/No • Lookup and Relationship • Rich Text • OLE Object • Attachment • Hyperlink • Calculated Field

46. Text Use the Text data type for simple text fields such as contact or project names or for fields such as street addresses and postal codes, which include numbers that aren’t used in mathematical operations. You can store as many as 255 characters in a text field, but you can also set the Field Size property to specify a length shorter than the limit.

47. Memo The Memo data type is designed for fields in which you want to store large blocks of text. You can store approximately 1 gigabyte of alphanumeric data in a memo field, but not all that data will be displayed on a form or in a control on a report.

48. Number Use the Number field to store numeric data. You can choose between various field sizes, including Byte, Integer, Long Integer, and Decimal.

49. Date/Time Designed to store dates and times. You can choose formats such as Short Date (3/08/2012) and Long Date (Monday, May 23, 2011). You can perform calculations on the data in date/time fields to determine the interval between two dates, for example.

50. Currency Use this data type for monetary values. You can specify formats that include up to four decimal places.