Chapter 3 (Part 2) Database Concept and Management

1. Chapter 3 (Part 2)Database Concept and Management

2. Contents 2 Database Management System (DBMS). Qualities of valuable information. Hierarchy of data. Data validation. Data model. Guideline for developing a database. Database Analyst & Database Administrator.

3. Database 3 • It is a collection of data organized so user can access, retrieve, and use it. • Database software (also known as Database Management System [DBMS]) allows user to: • Create database. • Add, change, and delete data. • Sort and retrieve data. • Create forms and reports.

4. Data Integrity 4 • It is the degree to which datais correct. • Garbage In, Garbage Out (GIGO): It is a computer phrase that means you cannot create correct information from incorrect data.

5. Qualities Of Valuable Information 5 Accurate Verifiable Timely Organized Accessible Useful Cost-effective

6. Hierarchy of Data 6 Database contains files, file contains records, record contains fields, field contains character.

7. Field 7 • It is a combination of one or more characters. • It is the smallest unit of data user accesses. • Field size: • Defines the maximum number of characters a field can contain. • Field name: • Uniquely identifies each field. • Data type: • Specifies kind of data field contains.

8. Data Types Text (also called alphanumeric): letters, numbers, or special characters AutoNumber unique number automatically assigned to each new record Numeric numbers only 8 Currency dollar and cent amounts or numbers containing decimal values Date/Time month, day, year, and time Memo lengthy text entries Object photograph, audio, video, or document created in other application such as word processing or spreadsheet Yes/No (also called Boolean): only the values Yes or No (or True or False) Hyperlink web address that links to document or Web page

9. Record 9 • It is a group of related fields. • Key field, or primary key: • Field that uniquely identifies each record.

10. Data File 10 Member ID First Name Last Name Address City State 2295 Donna Vandenberg 1029 Wolf Avenue Montgomery AL 2928 Shannon Murray 33099 Clark Street Montgomery AL 3376 Adrian Valesquez 15 Duluth Street Prattville AL 3928 Jonah Weinberg P.O. Box 45 Clanton AL 4872 Marcus Green 22 Fifth Avenue Auburn AL It is a collection of related records stored on disk. records key field fields

11. File Maintenance 11 • It is the procedure that keeps the data current. • File maintenance procedures include: • Adding Records. • Changing Records. • Deleting Records.

12. When To Add, Change & Delete? 12 • Why do you add records? • Add new record when you obtain new data. • Why do you change records? • Correct inaccurate data • Update old data • Why do you delete records? • When record no longer is needed. • Some programs remove record immediately, others flag record by putting an asterisk ( * ).

13. Data Validation 13 • Process of comparing data with a set of rules to find out if data is correct. • Reduce data entry errors and enhance data integrity before program writes data on disk.

14. Types of Validity Checks 14 • Check Digit: • Number(s) or character(s) appended to or inserted into a primary key value to confirm accuracy of primary key value. • Alphabetic/Numeric Check: • Ensures correct type of data entered.

15. Types of Validity Checks (cont…) 15 • Range Check: • Determines whether number is within specified range. • Completeness Check: • Verifies that a required field contains data. • Consistency Check: • Tests for logical relationship between two or more fields.

16. File Processing vs. Databases 16 • File Processing: • Each department or area within organization has own set of files. • Records in one file may not relate to records in any other file. • May have weaknesses: • Data redundancy: • Same fields stored in multiple files. • Isolated data: • Data stored in separate files so it is difficult to access.

17. File Processing vs. Databases 17 • Database approach: • Many programs and users can share data in database. • Secures data so only authorized users can access certain data.

18. File Processing vs. Databases 18 • Strengths of Database Approach: • Reduced data redundancy. • Improved data integrity. • Shared data. • Easier access. • Reduced development time.

19. File Processing vs. Databases 19 How do a database application and a file processing application differ in the way they store data?

20. Database Management System (DBMS) Database Manufacturer Computer Type Access Microsoft Corporation Personal computer, server, PDA 20 DB2 IBM Corporation Personal computer, midrange server, mainframe Informix IBM Corporation Personal computer, midrange server, mainframe Ingres Computer Associates International, Inc. Personal computer, midrange server, mainframe Oracle Oracle Corporation Personal computer, midrange server, mainframe, PDA SQL Server Microsoft Corporation Server Sybase Sybase Inc. Personal computer, midrange server, PDA It is a softwarethat allows user to create, access, and manage databases.

21. Data Dictionary 21 Contains data about each file in database and each field within those files.

22. Query 22 • It is a request for specific data from a database. • Query Language: • Consists of simple, English-like statements that allow users to specify data to display, print, or store.

23. Query By Example (QBE) 23 Program retrieves records that match criteria entered in form fields. Has a graphical user interface that assists users with retrieving data.

24. Form 24 Window on screen that provides areas for entering or changing data in db. Used to retrieve and maintain data in a database. Form that sends data across network or Internet is called e-form, short for electronic form.

25. Report Generator 25 • Allows user to design a report on screen, retrieve data into report design, then display or print reports. • Also called report writer.

26. Data Security 26 • DBMS provides means to ensure only authorized users can access data. • Access privileges: • Define activities that specific user or group of users can perform. • Generally there are two types of privileges: • Read-only privileges: • User can view data, but cannot change it. • Full-update privileges: • User can view and change data.

27. Backup & Log 27 • Backup: • It is a copy of the entire database. • Log • It is a listing of activities that change database contents. • DBMS places three items in log: before image, actual change, and after image.

28. Recovery Utility 28 • Uses logs and/or backups to restore database when it is damaged or destroyed. • There are two recovery techniques: • Rollforward (also known as forward recovery): • DBMS uses log to re-enter changes made to database since last save or backup. • Rollback (also known as backward recovery): • DBMS uses log to undo any changes made to database during a certain period of time. • It basically restore the database to its condition prior to the failure. Then user reenters any transaction entered after the failure.

29. Data Model 29 DATA MODELS FOR POPULAR DBMS: • Rules and standards that define how database organizes data. • Defines how users view organization of data. • Three popular data models: • Relational. • Object-oriented. • Multidimensional.

30. Relational Database 30 • Stores data in tables that consist of rows and columns. • Each row has primary key • Each column has unique name • Stores data relationships.

31. Relationship 31 It is basically connections between data.

32. Structured Query Language (SQL) 32 Allows user to manage, update, and retrieve data. Has special keywords and rules included in SQL statements.

33. Object-Oriented Database (OODB) 33 • Stores data in objects: • Object is item that contains data, as well as actions that read or process data. • Advantages: • Can store more types of data. • Can access data faster. • Often uses object query language (OQL).

34. Object-Oriented Database (OODB) Multimedia databases Hypertext databases Hypermedia databases Web databases Store images, audio clips, and/or video clips Contain text links to other documents Contain text, graphics, video, and sound Link to e-form on Web page 34 Groupware databases Store documents such as schedules, calendars, manuals, memos, and reports Computer-aided design (CAD) databases Store data about engineering, architectural, and scientific designs Examples of applications appropriate for an object-oriented database:

35. Multidimensional Database 35 Stores data in dimensions. Multiple dimensions, also called hypercube, allow users to analyze any view of data. Can consolidate data much faster than relational database. The number of dimensions in a multidimensional database varies. Example is the retailing business that have four dimensions: products, customers, regions, and time.

38. Data Warehouse 38 • It is a huge database system that stores and manages data required to analyze historical and current transactions. • Quick and efficient way to access large amounts of data. • Uses multidimensional databases. • Often uses a process called data mining to find patterns and relationships among data. • Smaller version of data warehouse is Data Mart.

39. Web Database 39 • Database you access through the Web by filling in a form on a Web page. • Usually resides on a database server, a computer that stores and provides access to a database.

40. Guidelines For Developing a DB 40 • Determine the purpose of the database. • Design the tables: • Design tables on paper first. • Each table should contain data about one subject.

41. Guidelines For Developing a DB 41 • Design the records and fields for each table: • Be sure every record has a unique primary key. • Use separate fields for logically distinct items. • Do not create fields for information that can be derived from entries in other fields. • Allow enough space for each field. • Set default values for frequently entered data. • Determine the relationships among the tables.

42. Database Analyst (DA) and Database Administrator (DBA) 42 • Database Analyst (DA): • Focuses on meaning and usage of data. • Decides proper placement of fields, defines relationships, and identifies users’ access privileges. • Database Administrator (DBA): • Creates and maintains data dictionary, manages database security, monitors database performance, and checks backup and recovery procedures.

43. Summary 43 Database Management System (DBMS). Qualities of valuable information. Hierarchy of data. Data validation. Data model. Guideline for developing a database. Database Analyst & Database Administrator.

44. Questions • Why object-oriented database model is gaining acceptance for developing applications and managing the hypermedia 2. A database consists of: • Region : Kuching, Bintulu, Sarikei, Sibu • Month : January, February, March, April, May, June • Sales items : Refrigerator, Microwave, Blender, Toaster • Margin items : Refrigerator, Microwave, Blender, Toaster • Sales Channel : Actual, Budget, Forecast, Variance • Draw out a multidimensional database with four different dimensions.

45. ERD

46. Questions • The Human Resources database is where transactions relating to payroll processing, time and attendance, recruitment, benefits, and other human resources data collection protocols are recorded, stored and retrieved in a variety of ways. The transactions recorded ensure the timely payment of employees and provide information to busines staff, managers, leaders, and local and state government for the purpose of making decisions and accountability reporting. Suppose you are database analyst who would is designing a database for the human resource department, write a report on the hr database which includes the tables, fields, primary key and transactions relationg to payroll processing.

47. End Of Chapter 3