8.1 Database Concept

1. 8.1 Database Concept • Revolution of Information Management • In the past, we often used the following methods to manage information storage and retrieval. A librarian looking up the index card of a book in a filing cabinet An office clerk storing customer information on index cards Historical methods of storing and retrieving information

2. 8.1 Database Concept • Revolution of Information Management • The searching or sorting process of information stored in a manual filing system such as a filing cabinet could be very time consuming. • Most companies realize that information is one of their most valuable assets. • Instant access to required information can take much less time to complete business transactions. • e.g. a staff member of a fashion company can check the system and find out instantly whether the requested item is in stock.

3. 8.1 Database Concept • Revolution of Information Management Switching from a manual filing system to an electronic one can save time and space on information management

4. 8.1 Database Concept • Databases • Many activities in our daily lives involve the use of databases. • Database is a collection of data related to a particular topic or purpose. • It is well organized so that information can be stored and retrieved efficiently.

5. 8.1 Database Concept • Databases • Typical applications of databases: • Personal phone book • Video or CD catalogue • Property list of an estate agent • Employee records of a company • Animal records of a vet clinic • Stock control of a shop • Club membership of a club house • Student records of a school

6. 8.1 Database Concept • Databases • Typical applications of databases: • Personal phone book • Video or CD catalogue • Property list of an estate agent • Employee records of a company • Animal records of a vet clinic • Stock control of a shop • Club membership of a club house • Student records of a school

7. 8.2 Using the Database Management System (DBMS) • Database Management System (DBMS) • Data hierarchy can be divided into • Field • Record • Table • Database

8. 8.2 Using the Database Management System (DBMS) • Designing a Database • A good database design is the keystone to creating a database that canperform effectively, accurately and efficiently. • Make a list of items you want to record. • Each item will become a field of a table in the database. • Each field should be given a unique name that is short but descriptive. • Additional information is the size and data type of each field.

9. 8.2 Using the Database Management System (DBMS) • Designing a Database Some examples of fields in a typical employee database

10. 8.2 Using the Database Management System (DBMS) • Designing a Database • The data type of each field helps us enter the data. • e.g. designating the data type of the ‘date of birth’ field as date prevents us from entering anything other than a valid date. • Rules for data entry can be set so that the DBMS will compare data entered against a predefined value. • This process is called data validation which is an important feature of DBMSs.

11. 8.2 Using the Database Management System (DBMS) • Key Field • The data in a key field is unique to a specific record. • The key field should not be anything with a chance of duplication such as names and birthdays. • If no field in your database meets this requirement, you can ask the DBMS to assign an identity number to each record. • Examples of key fields: • Driver’s licence number • Student identification number • Bank account number • Product code • Order number

12. 8.2 Using the Database Management System (DBMS) • Key Field • ‘member_id’ field is assigned as the key field of a member table. A sample member table

13. 8.2 Using the Database Management System (DBMS) • Input Mask • Help prevent users from inputting invalid data. • Make data entry easier by controlling the values that users can input. • e.g. an input mask can make sure that phone numbers always have the correct number of digits.

14. 8.2 Using the Database Management System (DBMS) • Input Mask • Help prevent users from inputting invalid data. • Make data entry easier by controlling the values that users can input. • e.g. an input mask can make sure that phone numbers always have the correct number of digits.

15. 8.3 Microsoft Access • Microsoft Access • A very robust and powerful DBMS • Easy to set up a database • An Access database contains various database objects and is stored in a single file.

16. 8.3 Microsoft Access • Microsoft Access Template categories Recently used databases Office button Blank database button Getting Started Screen of Microsoft Access

17. 8.3 Microsoft Access • Database Objects in Microsoft Access • Six types of database objects in Microsoft Access Database objects in Microsoft Access

18. 8.3 Microsoft Access • Database Objects in Microsoft Access • Macrosand modules are beyond the scope of this book and will not be discussed. Database objects in Microsoft Access

19. 8.3 Microsoft Access • Understanding Tables in Access • Every database needs at least one table to store data. • If you want to store several lists of related information, you may need to create more than one table. • e.g. an event table and a student table are needed to keep track of the students that take part in different events during Sports Day.

20. 8.3 Microsoft Access • Understanding Tables in Access • Two ways to create or edit the structure of a table in Microsoft Access: • Datasheet View • Design View Datasheet View of a table

21. 8.3 Microsoft Access • Understanding Tables in Access Design View of a table

22. 8.3 Microsoft Access • Understanding Tables in Access • The Datasheet View • Display the table organized in rows and columns. • Work like a spreadsheet and you can input data directly into the table. • During the data input, it will assign a data type to each field. • It can only guess what you need and most often you need to rename each field and re-assign its data type again.

23. 8.3 Microsoft Access • Understanding Tables in Access • Design View • Display the underlying structure of the table. • You can inspect and modify the data type and description of each field. • The preferred way of creating or modifying the structure of a table.

24. 8.3 Microsoft Access • Understanding Tables in Access Data types supported by Microsoft Access

25. 8.3 Microsoft Access • Data Sorting and Filtering in Access • Sorting • Rearrange all the records in ascending or descending order according to a specified field. • Filtering • Extract a subset of the records in the table according to specified criteria. • e.g. you can apply a filter on the ‘members’ table to obtain a list of members who live in a certain district.

26. 8.3 Microsoft Access • Data Sorting and Filtering in Access • Sorting • Rearrange all the records in ascending or descending order according to a specified field. • Filtering • Extract a subset of the records in the table according to specified criteria. • e.g. you can apply a filter on the ‘members’ table to obtain a list of members who live in a certain district.

27. 8.4 Forms • Forms • A graphical user interface used to manipulate and display the data. • A well-designed form has the fields displayed in a clearer and more attractive format than the Datasheet View. • Provide more efficient interface

28. 8.4 Forms • Forms The same table displayed in the Datasheet View (upper) and by a form (lower)

29. 8.4 Forms • Forms The same table displayed in the Datasheet View (upper) and by a form (lower)

30. 8.5 Reports • Reports • For printing data in a professional and easy-to-read layout • Can display data from tables or queries • Often used to present summarized information • Report Wizard helps produce professional reports easily

31. 8.5 Reports • Reports • For printing data in a professional and easy-to-read layout • Can display data from tables or queries • Often used to present summarized information • Report Wizard helps produce professional reports easily

32. 8.6 Queries • Queries • Used to retrieve data from one or more tables that meets specified criteria. • Perform calculations and reorder the data on request. • Results are presented in a datasheet.

33. 8.6 Queries • Queries • Must be written in a format that Access can interpret, i.e. Structured Query Language (SQL). • The syntax of SQL statements are shown below. SELECT field name(s) FROM table name(s) WHERE criteria; SELECT field name(s) FROM table name(s) WHERE criteriaORDER BY field name(s);

34. 8.6 Queries • Queries Operators used to specify criteria in SQL statements

35. 8.6 Queries • Queries • Three ways to create a query: • Query Wizard – a quick way to build a simple query • Design View – a graphical tool that can be used to create query • SQL View – a text-input interface for writing SQL statement

36. 8.6 Queries • Queries • Three ways to create a query: • Query Wizard – a quick way to build a simple query • Design View – a graphical tool that can be used to create query • SQL View – a text-input interface for writing SQL statement