Observe Yourself

1. Observe Yourself • Turn off communication devices Dress properly

2. Database

3. Key Issues • What is a database? • Some important database concepts and vocabulary. • Key characteristics about the data in a database.

4. What is a Database? • Database is a collection of related data that is stored and organized in a manner enabling information to be retrieved from the database as needed.

5. Database Software • It is more formally called a database management system (DBMS), is used to create, maintain, and access databases. • A DBMS also controls the organization of the data and protects the integrity and security of the data, so it is accurate and protected against both intentional and accidental damage. [Morley 3008 p575]]

6. DBMS Product Category • Workgroup/ Personal level • Enterprise level • Enterprise level can serve heavier load than personal level in terms of number of users accessing database or amount of data stored in a database.

7. DBMS Product • Examples of personal level are • Microsoft Access, • Corel Paradox, • Lotus Approach. • Examples of enterprise level are • Microsoft SQL Server, • Oracle, • IBM DB2, • MySQL

8. Database Element

9. A database typically consists of interrelated tables consisting of fields and records. • A field (column) is a single type of data to be stored in a database, such as • Person’s name: Thomas, Jimmy • Person’s telephone number: 02-300-4543 • ID code: 451277 • A record (row) is a collection of related fields.

10. Student Student 4715233 David ? • David • Thailand • 1st Year • David • Thailand • 1st Year 4612544 David • David • Australia • 2nd Year • David • Australia • 2nd Year Key Field • The field which is chosen to be the uniquely identify a record so the record can be easily retrieved and process • Can either called “” • Eg. Student ID

11. Student Teacher Grace David • Grace • Thailand • CS • David • Thailand • 1st Year Annie Peter • Annie • Australia • 2nd Year • Peter • Australia • IT Data Hierarchy • Characters • A letter, number or special character • Eg. A, B, C, 1, 2, 3, # • Field • Unit of data consisting of one or more characters (bytes) • Eg. Name, address • Record • A collection of related fields • Eg. Each student record • Table • A collection of related records • Eg. Student file • Database • An organized collection of integrated files • Eg. S&T database

12. Flat File and Database

13. Flat File • A file management system is a program that allows the creation of individual database tables (flat files), each of which is stored in its own physical file and is not related to any other file. • Consequently, file management systems can work with only one table at a time, • and each table has to contain all the data that may need to be accessed or retrieved at one time. • As a result, file management systems have a much higher level of redundancy than database management systems.

14. An organization would have different files for different purposes.

15. Problem in Flat File Processing • Data redundancy • Data inaccuracy

16. Relational Database • In a relational database, data from several tables is tied together (related) using a field that the tables have in common so that information can be extracted from multiple tables, if needed, to fulfill a user request.

17. File DB

18. Advantage of DBMS • Reduced Data Redundancy • Data redundancy means the same data fields appear in many different files and is in different format • Save storage space • Improve Data Integrity • Data integrity: data is accurate, consistent, and up-to-date • The change of data just make in only one place. • File sharing • All authorized users can work on the same set of file. • Increased security • Each level of user has different level of access.

19. Query language Retrieving information from a relational database

20. Retrieving data from a database is called query. • Query Language • An easy-to-use computer language for making queries to a database and for retrieving selected records based on criteria.

21. Query Language • Each DBMS software provides its own tool for query. The most popular is Structured Query Language • Ex. SELECT product_name FROM product WHERE price = 100

22. Form of SQL • The fields used in the query are specified with • The tables to which they belong are specified with • Selection criteria are determined by • The query results can be sorted in any sequence with

23. Demo • Query/Requirement • Retrieve address and e-mail of student ID 5001234

24. Demo • Query/Requirement • Seek the students who get honor.

25. QBE • Query By Example(QBE) is a feature of query language that use a sample record form to define the qualifications he wants for selected records. In other words, the user fills in a form

26. EX • You want to contact to studentID 5201111 • So you have to search for E-mail and phone no. of studentID 5201111.

27. Source : http://www.w3schools.com/sql/sql_quickref.asp More SQL Statements

28. Other concepts Master/Transaction file Batch/Realtime processing Individual/Distributed database DBA Data recovery Data mining

29. Sale Amount 2005 Jan 45,456 Fab 75,558 Mar 21,794 Apr 34,129 Unchanged update Jan Feb Mar Apr May Type of Data Files • Master File • Data file which contains relatively permanent records that are generally update periodically • Transaction File • Temporary holding file that holds all change to be made to the master file: addition, deletion, revision. • Each period, master file would be updated with the change called for in transaction file.

30. Inventory Table Update Master File Purchase Table Transaction File

31. Batch & Real-time Processing • The transactions can be handled via • Batch processing (offline processing) • the data is gathered over a period of time and then processed in a set (batch) at periodic intervals, such as at the end of the day or once a week. • Real-time processing (online transaction processing, OLTP) • Each transaction is processed immediately as it is entered.

32. Database Classification • Single-user vs. Multiuser Database Systems • Centralized vs. Distributed Database Systems

33. Single-user & Multiuser DB • Single-user • Multiuser

34. Centralized & Distributed DB • Centralized DB • Distributed DB Customer Credit History Customer Address

35. Database Administrator (DBA) • Person who coordinates all related activities and needs for an organization’s database. • The DBA determines user access privileges, sets standards, guidelines, and control procedures, backup data.

36. Responsibilities of DBA • Database design, implementation, and operation • How space will be used on secondary-storage devices • How file or record may be added, deleted, changed • Coordinate with users • User access privileges • Control procedures • Establish priority for requests • Develop user documentation • System security • Prevent unauthorized access • Backup and recovery • Perform regularly backup • Develop plan for recovering data • Performance monitoring • Make sure that service for user is at the standard, not too slow

37. Data Warehouse • It is a comprehensive collection of data about a company and its customers. • In a data warehouse, data from transaction processing and other operations are reorganized and put into a form that is optimized for queries. http://www.persysinc.com/images/persys_data_warehouse.gif

38. Data Mining http://www.mbaknol.com/management-information-systems/data-mining-and-its-business-applications/attachment/data-mining/ • Data mining makes use of a data warehouse by applying intelligent software to scan its contents for subtle patterns that may not be evident to management. • It identifies patterns and relationships between data. • It can be a useful sales and marketing tool to help retail companies match customers with products that would be likely to purchase – a type of customer profiling. • Wal-Mart is one company that uses extensive data mining and targets specific types of merchandise in their local stores, based on the characteristics of the people that live in that geographical area.