Database

1. Database

2. Data vrs Information • Data and information drives the design of database. • Data are raw facts • The raw indicates that the facts have not been processed to reveal their meaning • Information is data processed to reveal meaning • Information age recognizes that the production of accurate, relevant, and timely information is the key to good decision making. • Good decision making is the key to business survival in the global market

3. Data constitute the building blocks of information • Information is produced by processing data • Information is used to reveal the meaning of data • Accurate, relevant, and timely information is the key to good decision making

4. Timely and useful information requires accurate data. • Such data must be generated properly, and it must be stored properly in a format that is easy to access and process • The data environment must be managed carefully like any basic resource

5. Data management is a discipline that focuses on the proper generation, storage, and retrieval of data. • Data management is a core activity for any business, government agency, service organization, or charity. • Efficient data management typically requires the use of a computer database

6. A database is a shared, integrated computer structure that houses a collection of: • End user data, that is, raw facts of interest to the end user • Metadata, or data about data, through which the data are integrated and managed • Metadata provide a description of the data characteristics and the set of relationships that link the data found within the database

7. A database resembles a very well-organized electronic filing cabinet in which powerful software, known as a database management system, helps manage the cabinet’s contents. • A database management system(DBMS) is a collection of programs that manages the database structure and controls access to the data stored in the database • The DBMS makes it possible to share the data in the database among multiple applications or users

8. Advantages of DBMS • It helps create an environment in which end users have better access to more better managed data than they did before the DBMS became the data management standard. • Such access makes it possible for end users to respond quickly to changes in their environment. • The availability of data, combined with the tools that transform data into usable information, empowers end users to make quick information decisions that can make the difference between success and failure in the global economy

9. Advantages of DBMS • Wider access to well-managed data promotes an integrated view of the organization’s operations and clearer view of the “big picture.” • It becomes much easier to see how actions in one segment of the company affect other segments

10. Advantages of DBMS • The probability of data inconsistency is greatly reduced in a properly designed database that is managed through a DBMS. • Better data make it possible to generate better information, on which better decisions are based

11. Advantages of DBMS • It makes it possible to produce quick answers to ad hoc queries • A query is a question, and an ad hoc query is a spur-of-the-moment question.) • Example, end users when dealing with large amounts of sales data might want quick answers to questions (ad hoc queries)

13. Types of Databases • Databases can be classified according to the number of users, the database site location(s), and the expected type and extent of users

14. Number of Users • The number of users determines whether the database is classified as single-user or multiuser. • A single-user database supports only one user at a time • In other words, if user A is using the database, users B and C must wait until user A has completed his/her database work. • If a single-user database runs on a personal computer, it is also called a desktop database

15. In contrast, a multiuser database supports multiple users at the same time. • If the multiuser database supports a relatively small number of users ( usually fewer than 50) or a specific department within an organization, it is called a workgroup database. • If the database is used by the entire organization and supports many users ( more than 50, usually hundreds) across many departments, the database is known as an enterprise database

16. Database Location Sites • The database site location might also be used to classify the database. • A database that supports data located at a single site is called a centralized database • A database that supports data distributed across several different sites is called a distributed database

17. The most popular way of classifying databases today, however, is based on how they will be used, and on the time of sensitivity of the information gathered from them. • Example, transactions are time-critical and must be recorded accurately and immediately. • A database that is primarily designed to support a company’s day-to-day operations is classified as a transactional database or a production database.

18. In contrast, a data warehouse database focuses primarily on the storage of data used to generate information required to make tactical or strategic decisions • Such decisions typically require extensive data massaging (data manipulation) to extract information from historical data to formulate pricing decisions, sales forecasts, market positioning, and so on

19. Because most decision support information is based on historical data, the time factor is not likely to be as critical as for transactional databases. • Additionally, the data warehouse database can store complex data derived from many sources. • To make it easier to retrieve such complex data, the data warehouse database structure is quite different from that of transaction-oriented database.

20. Importance of Database design • Proper database design requires the database designer to precisely identify the database’s expected use • Designing a transactional database emphasizes data integrity, data consistency, and operational speed. Each type has its own operation

21. Importance of Database design • A well-designed database facilitates data management and becomes a valuable information generated. A poorly designed database will likely become a breeding ground for redundant data. Redundant data are often the source of difficult-to-trace information errors. • A poorly designed database tends to generate errors that are like to lead to bad decisions, and bad decisions can lead to the failure of an organization

22. Files and File Systems • A file system was traditionally composed of a collection of file folders, each properly tagged and kept in a filing cabinet. • Organizations of the data within the file folders was determined by the data’s expected use • The contents of each file folder were logically related

23. As long as a data collection was relatively small and an organization’s managers had few reporting requirements, the manual system served its role well as a data repository. • However, as organizations grew and as reporting requirements become more complex, keeping track of data in a manual file system became more difficult. Finding and using data in growing collections of file folders became such a time-consuming and cumbersome task.

24. A data processing (DP) specialist is employed to create the necessary computer file structures, often wrote the software that managed the data within those structures, and designed the application programs that produced reports based on the file data. • The computer files within the file system were similar to the manual files

25. Problems with file system data management • Although the file system method of organizing and managing data was a definite improvement over a manual system, many problems and limitations became evident in this approach. • The first and most glaring problem with the file system approach is that even the simplest data-retrieval task requires extensive programming in a third-generation language(3GL). • A 3GL requires the programmer to specify both what must be done and how it is to be done. • Examples of 3GLs include BASIC, COBOL, FORTRAN. • A 4GL allows the user to specify what must be done without specifying how it must be done

26. Programming in a 3GL can be a time-consuming, high-skill activity. Because the simple file system is quite different from the way the computer physically stores the data on disk, the programmer must be familiar with the physical file structure, that is, how and where the files are stored in the computer. • Therefore, every file reference in a program requires the programmer to define access paths to the data. • Such access paths use complex coding to establish the precise location of the various file and system components and their data characteristics.

27. As file systems become more complex, the access paths become difficult to manage and tend to produce system malfunctions. • The need to write 3GL programs to produce even the simplest reports makes ad hoc queries impossible. • Another problem, related to the need for extensive programming, is that as the number of files in the system expands, system administration becomes difficult. • Each file must have its own file management system composed programs that allow the user to create, add, delete data, modify and list contents from a file.

28. Limitations of file system data management • It requires extensive programming • System administration can be complex and difficult • It is difficult to make changes to existing structures • Security features are likely to be inadequate. • These limitations in turn lead to problems of structural and data dependency

29. Structural and Data dependence • Structural dependence is access to a file is dependent on its structure. Even changes in file data characteristics, such as changing a field from integer to decimal, require changes in all programs that access the file. • Because all data access programs are subject to change when any of the file’s data characteristics change, the file system is to exhibit data dependence. • Structural independence exits when it is possible to make changes in the database structure without affecting the application program’s ability to access the data

30. The practical significance of data dependence is the difference between the logical data format (how the human being views the data) and the physical data format (how the computer “sees” the data). Any program that accesses a file system’s file must not only tell the computer what to do, but also how to do it

31. Data redundancy • Data redundancy exists when the data environment contains redundant –unnecessarily duplicated –data • Uncontrolled data redundancy sets the stage for : • Data inconsistency: data inconsistency exits when different and conflicting versions of the same data appear in different places. Data that display data inconsistency are also referred to as data that lack data integrity • Data anomalies: data redundancy, however, fosters an abnormal condition by forcing field value changes in many different locations. Any change in any field value must be correctly made in many places to maintain data integrity. A data anomaly develops when all the required changes in the redundant data re not made successfully

32. Database Systems • Unlike the file system, with its many separate and unrelated files, the database consists of logically related data stored in a single logical data repository. • The “logical” label reflects the fact that, although the data repository appears to be a single unit to the end user, its contents may actually be physically distributed among multiple data storage facilities and/or locations. • The database’s DBMS provides numerous advantages over file system management by making it possible to eliminate most of the file system’s data inconsistency, data anomalies, data dependency, and structural dependency problems

33. DBMS software stores not only the data structures, but also the relationships between those structures and the access paths to those structures, all in a central location • DBMS software also takes care of defining, storing, and managing all the required access paths to those components. • DBMS is just one of the several crucial components of a database system

34. Database System Environment • Database system refers to an organization of components that define and regulate the collection, storage, management, and use of data within a database environment • The database system is composed of the five major parts: hardware, software, people, procedures, and data

36. Hardware • Hardware refers to all the system’s physical devices. • The database system’s main and most easily identified hardware component is the computer, which might be a microcomputer, a minicomputer, or a mainframe computer • The hardware also includes all of the computer peripherals, which are the physical devices that control computer input and output such as keyboard, modems, printers etc • Hardware also includes devices that are used to connect two or more computers, thereby producing a computer network. • Networks are an essential part of modern database systems, because data are likely to be accessed from a local network, from remote locations such as airplane reservation systems and automatic teller machines, or over the internet, or all of the above

37. Software • Software refers to the collection of programs used by the computers within the database system • Although the most readily identified software is the DBMS itself, to make the database system function fully, it take three types of software: operating system software, DBMS software, and application programs and utilites

38. Operating system software manages all hardware components and makes it possible for all other software to run on the computers • DBMS software manages the database within the database system. Examples include Microsoft Access and SQL server, Oracle corporation’s oracle and IBM’s DB2 • Application programs and utility software are used to access and manipulate the data in the DBMS and to manage the computer environment in which data access and manipulation take place. Application programs are most commonly used to access the data found with the database to generate reports, tabulations, and other information to facilitate decision making. Utilities are the software tools used to help manage the database system’s computer components. For example, all the major DBMS vendors now provide GUI interfaces to help database administrators create database structures, control database access, and monitor database operations

39. People • This component includes all users of the database system • On the basis of primary job functions, we can identify five types of users in a database system: systems administrators, database administrators, database designers, systems analysts/programmers, and end users. • The members of each user type perform both unique and complementary functions

40. System administrators oversee the database system’s general operations • Database administrators, also known as DBAs, manage the DBMS’s use and ensure that the database is functioning properly. • Database designers design the database structure. They are, in effect, the database architects. If the database design is poor, even the best application programmers and the most dedicated DBAs cannot produce a useful database environment. Because organizations strive to optimize their data resources, the database designer’s job description has expanded to cover new dimensions and growing responsibilities • System analysts and programmers design and implement the application programs. They design and create the data entry screens, reports, and procedures through which end users access and manipulate the database’s data • End users are the people who use the application programs to run the organization’s daily operations. High level end users employ the information obtained from the database to make tactical and strategic business decisions.

41. Procedures • Procedures are the instructions and rules that govern the design and use of the database system. • Procedures are a critical, although occasionally forgotten, component of the system • They play an important role in a company, because they enforce the standards by which business is conducted within the organization and with customers. • Procedures also are used to ensure that there is an organized way to monitor and audit both the data that enter the database and the information that is generated through the use of such data.

42. Data • It covers the collection of facts stored in the database. • Because data are the raw materials from which information is generated, the determination of which data are to be entered into the database and how such data are to be organized is a vital part of the database designer’s job

43. The existence of a database system adds a new dimension to an organization’s management structure. • Just how complex this managerial structure is depends on the organization’s size, its functions, and its corporate culture • Therefore, database systems can be created and managed at quite different levels of complexity and with widely varying adherence to precise standards. • For example , compare a local movie rental system with a national insurance claims system. The movie rental system might be managed by two people. The hardware used is probably a single microcomputer, the procedures are probably simple, and the data volume will tend to be low. The national insurance claims system is likely to have at least one systems administrator, several full-time DBAs, and many designers and programmers; the hardware probably includes several mainframes at multiple locations throughout the country; the procedures are like to be numerous, complex and rigorous; and the data volume will tend to be very high

44. In addition to the fact that different levels of database system complexity are dictated by the organization activities and the environment within which those activities take place, managers must also take another important fact into account: database solutions must be cost-effective as well as tactically and strategically effective • Database technology already in use is likely to affect the selection of a database system

45. DBMS Functions • A DBMS performs several important functions that guarantee the integrity and consistency of the data in the database. • Most of these functions are transparent to end users, and most can be achieved only through the use of a DBMS • They include data dictionary management, data storage management, data transformation and presentation, security management, multiuser access control, backup and recovery management, data integrity management, database access languages and application programming interfaces, and database communication interfaces. • Example, how Ms Access presents the data definition for the CUSTOMER table. Note the definition of the field properties for the CUS_RENEW_DATA

47. Data Dictionary Management • DBMS stores the definitions f the data elements and their relationships (metadata) in a data dictionary • In turn, all programs that access the data in the database work through the DBMS. • The DBMS uses the data dictionary to look up the required data component structures and relationships, thus relieving us from having to code such complex relationships in each program • Additionally, any changes made in a database structure are automatically recorded in the data dictionary, there by freeing us from having to modify all the programs that access the changed structure • DBMS provides data abstraction, and it removes structural and data dependency from the system

48. Data Storage Management • The DBMS creates and manages the complex structures required for data storage, this relieving us from the difficult task of defining and programming the physical data characteristics. • A modern DBMS system provides storage not only for the data, but also for related data entry forms or screen definitions, report definitions, data validation rules, procedural code, structures to handle video and picture formats, and so on • Data storage management is also important for database performance tuning • Performance tuning relates to the activities that make the database perform more efficiently in terms of storage and access speed. Although the user sees the database as a sing data storage unit, the DBMS actually stores the database in multiple physical data files • Such datafiles may even be stored on different storage media. • Therefore the DBMS doesn’t have to wait for one disk request to finish before the next one starts. In otherwords, the DBMS can fulfill database requests concurrently

49. Data Transformation and Presentation • The DBMS transforms entered data to conform to the data structures that are required to store the data. Therefore, the DBMS relieves us of the chore of making a distinction between the data logical format and the data physical format • By maintaining data independence, the DBMS translates logical requests into commands that physically locate and retrieve the requested data. • That is , the DBMS formats the physically retrieved data to make it conform to the user’s logical expectations. • DBMS provides application programs with software independence and data abstraction. • Example, two dates july 11, 2004 and 07/11/2004 should properly formatted by the DBMS to suit each user

50. Security Management • The DBMS creates a security system that enforces user security and data privacy within the database. Security rules determine which users can access the database, which data items each user may access, and which data operations (read, add, delete, or modify) the user may perform. • This is especially important in multiuser database systems where many users can access the database simultaneously • All database users are authenticated to the DBMS through the use of a username and password • The DBMS then uses this information to assign access privileges to various database components such as queries and reports