Introduction to database ~concepts, table and query

1. Introduction to database~concepts, table and query Day 1 10 – 09 – 2012 St Paul Convent School By Ivan Wong

2. Objective • Understand Module 5 test format. • Understand basic concepts (theory question). • Understand MS Access Structure. • Understand Table structure. • Understand Query process.

3. Module 5 test format • Full marks is 32, passing mark is 24 marks. • 7 multiple choice question on Database theory. • Around 9 questions on Database table. • Around 5 questions on Database query. • Around 4 questions on Database form. • Around 4 questions on Database report.

4. Component of Module 5

5. Concepts 1 ~What is database? • Database consist of massive amount of data about a specific subject. • Data are organized by field, record and table. • Field contains one element of data.(eg, age, name, DOB, score, identification code) • Record contains all field of one data. • Table contains many record.

6. Concept 1 ~What is Database? • For a school database,there are many table of each classmany student record in each class tableDOB , age, name, number of absent… are record as field of student • Common uses of large scale database :airline booking systems,government records,bank account records,hospital patient details.

7. MS Access Outlook and Structure • 4 Kinds of Access Tools : Table, Query, Form, Result (Shown on the column on the left)

8. MS Access Outlook and Structure • To close the table / query / form / report, click the lower cross button.

9. MS Access Outlook and Structure • To close the Whole Database (including all table / query / form / report) click the upper cross button.

10. MS Access Outlook and Structure • “Save” and “Save as” refers to saving of Database tools (table/query/form/report). • “Save Database” refers to saving whole Database file. • You are suggested to save regularlyduring the test.

11. Practical part 1 : table/query/forms/report • Exercise 1 : (http://www.icdl.com.hk/m5/ex1.mdb)1, Open Access application software.2, Fill in the empty field according to the below information: Name Age Class Result Chan Long Yu 16 3A 76 Cheung Chi Ming 17 4B 82 Tsz Wing Sum 16 3B 70

12. Practical part 1 : table/query/forms/report • Exercise 2 :1, Open a blank database. (steps will be demonstrated here).2, Build a new database with 4 field, 3 records : Name Age Class Result Chan Long Yu 16 3A 76 Cheung Chi Ming 17 4B 82 Tsz Wing Sum 16 3B 70 3, Click “ Add New field“ , choose “text” , type in “Name” Click “ Add New field“ , choose “number” , type in “Age” Click “ Add New field“ , choose “text” , type in “Class” Click “ Add New field“ , choose “number” , type in “Result” 4, Type in the above data as what you did in exercise 1.

13. Practical part 1 : table/query/forms/report • Exercise 2 :5, Save Database as Exercise 2.6, What will be result if you input some text in the “Age” field?

14. End of Session 1 • Quiz :1, What is a database?2, What is the 4 database tools?3, How is a database organized in different level?4, Name 3 common use of large scale database.5, What is limited by a field?

15. Concepts 2 ~More about data format • You may change the field content by click “view” > “design view” on the top left hand corner. • There is a field size for text. (see demonstration) • Decimal place can be adjusted for number. (see demonstration) • Date can be set in different format.(see demonstration) • Different dollar sign can be found for currency. • Autonumber assign number to new record automatically.

16. Concepts 2 ~More about data format • Decreasing field size may result in lost of data. • Setting Index can speed up searching. • Primary key has highest priority over all index. • Primary key contains unique data, no duplication is allowed. (eg, ID card number, student card)

17. Practical part 2 : table/query/forms/report • Exercise 3 :1, Open ex2.mdb. (http://www.icdl.com.hk/m5/ex2.mdb)2, Add a new field called “Payment”, type is “currency”3, Fill in the new data as shown below: Name Payment Chan Long Yu 16 3A 76 450.3 Cheung Chi Ming 17 4B 82 366.78 Tsz Wing Sum 16 3B 70 550.1 4, click “design view”, change field properties of field “Payment” to be of 2 decimal place. 5, click “ Datasheet View“ , any change to the field data?

18. Practical part 2 : table/query/forms/report • Exercise 3 :6, Change the Primary key to “Result” field in design view.7, click back to “Datasheet View “ , any change to the record sequence?8, Save the file as Exercise 3.Make a copy of Exercise 3 before step 9 and 10!9, Change field size of Name to be 10.10, If we decrease field size, what may happened?

19. End of Session 2 • Quiz :1, What is index?2, What is primary key? Why is it unique?3, How can we change field properties?4, What may happen if field size is decreased?You may visit the following website for more exercise:http://elearning.icdl.com.hk/hk036/NO www, NOT HK036Username is your student identification code.Initial password is 12345.

20. Concepts 3 ~Database Operator • Database specialist designs and creates database. • Database user enter data, maintains of data, retrieve data. • Database administrator provides access and permission to different user. • Database administrator responsible for recovery of a database after a crash or major errors.

21. Practical part 3 : table/query/forms/report • Exercise 4 :1, Open ex3.mdb. (http://www.icdl.com.hk/m5/ex3.mdb)2, Click to visit design view3, Click on the index button4, Give a name “Age sorting” for the new index Set age field to be sorted by the new index Sort in ascending order. (Keep all other setting unchanged)5, Remove Primary key6, Click back to “Datasheet view”, do you notice any change?7, Do not save the file, close the database.*Will the result be the same if primary key is not removed?

22. Practical part 3 : table/query/forms/report • Exercise 5 :1, Open ex3.mdb. (http://www.icdl.com.hk/m5/ex3.mdb)2, Click to visit design view3, Click on “Age” field, set a new input rule at “validation rule” as >15.4, save, click “” to see, any change?5, input a new record as :Shing Yan Yan 14 5C 77 425.16, Input process is not allowed, why?7, Change the age from 14 to 18, save database as Exercise 4.

23. End of Session 3 • Quiz :1, How to set index?2, Is it possible to have no primary key?3, What means by validation rule?4, Who is responsible for updating database?5, Who is responsible for granting access and permission for user?6, Who is responsible for recovery of database after database crush?7, What is the job of a Database specialist?

24. Building Relationship Table 1Name , Age , Address , Class , Score , Student ID , Present or Absent …Name , Age , Address , Class , Score , Student ID , Present or Absent …Name , Age , Address , Class , Score , Student ID , Present or Absent … Table 1aStudent ID , Name Table 1bStudent ID, Class Table 1cStudent ID, Score Table 1bStudent ID, Address

25. Integrity Table 1aStudent ID , Name Table 1aDeleted, Name Table 1bStudent ID, Class Table 1bStudent ID, Class Table 1cStudent ID, Score Table 1cStudent ID, Score Table 1bStudent ID, Address Table 1bStudent ID, Address

26. Concepts 4 ~Building relationship • Building Relationship can reduce duplication of data.No more table with numerous field is allowed,tiny tables with only useful data are left instead.This can speed up the searching process. • Relationship is built between 2 unique field of data, such as …? • After relationship is built, data are kept meaningful by integrity.

27. Practical part 4 : table/query/forms/report • Exercise 6 :1, Open ex6.mdb. (http://www.icdl.com.hk/m5/ex6.mdb)2, Click on the “name” field, choose “descending” from the sorting and filter group.3, Any change after sorting? Remove the sorting.4, Click on the “class” field, choose “filter” from the sorting and filter group.5, Deselect all, then click on “3A”, what is left?6, Click on the “result” field, choose “filter” from the sorting and filter group.7, Choose number filter, enter >75, what is left?*How many student are there is 5C?*How many student got over 85?*Will you be able to find 5C student who got over 85?

28. Practical part 4 : table/query/forms/report • Exercise 7 :1, Open ex6.mdb. (http://www.icdl.com.hk/m5/ex6.mdb)2, Click on the “computer class” query in the column on the left.3, Go to design view (the design view is different from table design view).4, Enter “>16” in “age” field criteria.5, Enter “>75> in “result” field criteria.6, Click “run” on the top left hand corner to see the result.*can you find student who is over 17 years old and got a score below 75?

29. End of Session 4 • Quiz :1, In table tools, how to make a sorting?2, In table tools, how to make a filter?3, In table tools, how to make a number filter?4, In query tools, how to set multiple criteria?*table is for data entry, validation rule control the rule to input data.*query is for data extraction, criteria control the rule to extract data.5, What is the purpose of building relationship?6, What is limited by integrity?7, To build a relationship, you will need an ________ field in both table.

30. Reminder • You may visit the following website for more exercise:http://elearning.icdl.com.hk/hk036/NO www, NOT HK036Username is your student identification code.Initial password is 12345. • Exercise 1-6 will be provided for you practice.Practice: http://www.icdl.com.hk/m5/ex(1-6).mdb.Question : http://www.icdl.com.hk/m5/Que.ppt To get your practicing file and question.Make a copy before starting your work.

31. Reminder • Query, Form and Report can be generated by Wizard. You are welcome to try them at home.(see the demonstration) • Please spare time to review database concepts.It takes up a large part in your test.See you tomorrow!