Article No: 4
TYPES OF DATABASES
A DBMS can support many different types of databases. Databases can be classified according to number of users, the database location(s), and the expected type and extend to use.
FIGURE 1.2 Types of databases
According to users
1. Single user database. It supports only one user at a time. In other words, if user A is using database, user B and user C must wait until user A is done.
a. Desktop database. A single-user database that runs on a personal computer.
2. Multiuser database. It supports multiple users at the same time.
a. Workgroup database. When the multiuser database supports a relatively small number of users (usually fewer than 50) or a specific department within an organization.
b. Enterprise database. When the database is used by the organization and supports many users (more than 50, usually hundreds) across many departments.
According to location(s)
1. Centralized database. A database that supports data located at a single site.
2. Distributed database. A database that supports data distributed across several different sites.
According to use
1. Operational database. Transactions such as product or service sales, payments, and supply purchases reflect critical day-to-day operations. Such transactions must be recorded accurately and immediately. A database that is designed primarily to support a company’s day-to-day operations is classified as an operational database (also known as transactional or production database).
2. Data warehouse. It focuses primarily on storing data used to generate information required to make tactical or strategic decisions. Such decisions typically require “data massaging” (data manipulation) to extract information, to formulate pricing decisions, sales forecasts, market positioning, etc. Most decision support data are based on historical data obtained from operational databases. Additionally, the data warehouse can store data retrieved from many sources.
THE DATABASE SYSTEM ENVIRONMENT
A database system refers to an organization of components that define and regulate the collection, storage, management, and use of data within a database environment. From a general management point of view, the database system is composed of the five major parts :
1. Hardware. It refers to all of the system’s physical devices; for example, computers (microcomputers, mainframes, workstations, and servers), storage devices, printers, network devices (hubs, switches, routers, fiber optics), and other devices (Automated Teller Machines, ID readers, etc.).
2. Software. Although the most readily identified software is the DBMS itself, to make the database system function fully, three types of software are needed:
· Operating system. It manages all hardware components and makes it possible for all other software to run on the computers. E.g., Microsoft Windows, Linux, Mac OS, and UNIX.
· DBMS software. It manages the database within the database system. E.g., Microsoft Access and SQL Server, Oracle, IBM’s DB2.
· Application programs and utilities. It is used to access and manipulate data in the database and to manage the computer environment in which data access and manipulation takes place. Application programs are most commonly used to access data found within 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 of major DBMS vendors now provide graphical user interfaces (GUIs) to help create database structures, control database access, and monitor database operations.
3. People. This component includes all users of the database system. On the basis of primary job functions, five types of users can be identified in a database system:
· System administrators. They oversee the database system general operations.
· Database administrators. It manages the DBMS and ensures that the database is functioning properly.
· Database designers. 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.
· System analysts and programmers. They design and implement the application programs. They design and create the data entry screens, reports, and procedures through which end user access and manipulate the database’s entities.
· End users. They are the people who use the application programs to run the organization’s daily operations. For example, salesclerks, supervisors, managers, and directors are all classified as end users. High-level end users employ the information obtained from the database to make tactical and strategic business decisions.
4. Procedures. Procedures are the instructions and rules that govern (manage) the design and use of the database system. Procedures play an important role in a company because they enforce the standards by which business is conducted within the organization and with customers. Procedures are also 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 that data.
5. Data. The word data covers the collection of facts stored in the database. Because data are the raw material from which information is generated, the determination of what data are to be entered into the database and how that data are to be organized is a vital part of the database designer’s job.
STRUCTURAL AND DATA DEPENDENCE
Structural Dependence
The file system exhibits structural dependence; i.e. access to a file is dependent on its structure. In short because the file system application programs are affected by change in the file structure, they exhibit structural dependence.
Structural Independence
It exists when it is possible to make changes in the file structure without affecting the application program’s ability to access.
Data Dependence
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 storage characteristics change (that is changing the data type), the file system exhibit data dependence.
Data Independence
It exists when it is possible to make changes in the data storage characteristics without affecting the application program’s ability to access the data.
FIELD DEFINITIONS AND NAMING CONVENTIONS
Suppose you want to create a student phone directory. Storing the student name as a single field turns out to be liability because the directory must break up the field contents to list the last names, first names, and initials in alphabetical order. Or suppose you want to get a student listing by area code. Including the area code in the phone number field is inefficient.
From the user’s point of view, a much better (more flexible) record definition would be one that anticipates reporting requirements by breaking up fields into their component parts. Thus, the STUDENT file’s fields might be listed as shown in Table.
FIELD | CONTENTS | SAMPLE ENTRY |
STU_LNAME | Student last name | Chaudhary |
STU_FNAME | Student first name | Amit |
STU_INITIAL | Student initial | V |
STU_AREACODE | Student area code | 247 |
STU_PHONE | Student phone | 232-345678 |
STU_ADDRESS | Student street address | 432 Green city |
STU_CITY | Student city | Gandhinagar |
STU_STATE | Student state | Chandigarh |
STU_ZIP | Student zipcode | 383997 |
FIGURE 1.4 Student table
Selecting proper field names is also important. For example, make sure that field names are reasonably descriptive. For example if there is a CUSTOMER file, it is not obvious that the field name REN represents the customer insurance renewal date. Using field name CUS_RENEW_DATE would be better for two reasons. First, the prefix CUS can be used as an indicator of the field’s origin, which is the CUSTOMER file. Therefore, you know that the field in questions yields a CUSTOMER property. Second, the RENEW_DATE portion of the field name is more descriptive of the field’s contents. With proper naming conventions, the file structure becomes self documenting. That is by simply looking at the field names, you can determine which files the fields belong to and what information the fields are likely to contain.
Some software packages place restrictions on the length of field names. So it is wise to be as descriptive as possible within those restrictions. In addition, very long field names make it difficult to fit more than a few fields on a page, thus making output spacing a problem. For example, the field name CUSTOMER_INSURANCE_RENEWAL_DATE, while being self documenting, is less desirable than CUS_RENEW_DATE.
DATA REDUNDANCY
The organizational structure promotes the storage of the same basic data in different locations. (Database professionals use the term islands of information for such scattered data locations). Because it is unlikely that data stored in different locations will always be updated consistently, the islands of information often contain different versions of the same data. For example, in figure the agent names and phone numbers occur in both the CUSTOMER and the AGENT files. You need only one correct copy of the agent names and phone numbers. Having them occur in more than one place produces data redundancy. Data redundancy exists when the same data are stored unnecessarily at different places.
FIGURE 1.4 Table of Customer and Agent
Uncontrolled data redundancy creates many problems such as data anomalies and data inconsistencies.
Data Inconsistency
Data inconsistency exists when different and conflicting versions of the same data appear in different places. For, example, suppose you change an agent’s phone number or city in the AGENT file. If you forget to make corresponding changes in the CUSTOMER file, the files contain different data for the same agent. Reports will yields inconsistent results depending on which version of the data is used.
Data entry errors are more likely to occur when complex entries (such as 10-digit phone numbers) are made in several different files and/or recur frequently in one or more files. For example, Phone number (615-882-2144) rather than (615-882-1244). It is possible to enter a nonexistent sale’s agent name and phone number into the CUSTOMER file, but customers are not likely to be impressed if the insurance agency supplies the name and phone number of an agent who does not exist. And should the personnel manager allow a nonexistent agent to accrue bonuses and benefits?
Data Anomalies
The data dictionary defines anomaly as “an abnormally”. Ideally, a field value change should be made in only a single place. Data redundancy, however, fosters an abnormal condition by forcing field value changes in many different locations. Look at the CUSTOMER file in Figure 1.4. If agent Virat decides to get married and move, the agent name, city is likely to change. Instead of making just a single name and/or city change in a single file (AGENT), you must also make change each time that agent’s name, city occur in the CUSTOMER file. You could be faced with the prospect of making hundreds of corrections, one for each of the customers served by that agent! The same problem occurs when an agent decides to quit. Each customer served by that agent must be assigned a new agent. Any changes in any field value must be correctly made in many places to maintain data integrity. A data anomaly develops when all of the required changes in the redundant data are not made successfully.
· Update anomaly. If agent Vishal has a new phone number, that number must be entered in each of the CUSTOMER file records. In this case, only three changes must be made. In a large file system, such changes might occur in hundreds or even thousands of records.
· Insertion anomaly. If only the CUSTOMER file existed, to add a new agent, you would also add a dummy customer data entry to reflect the new agent’s addition.
· Deletion anomaly. If you delete the customers Amit, Naresh and Bhavik, you will also delete Vishal’s agent data. Clearly this is not desirable.
Resource Used:
1. Databse Management System (By: Peter Rob)
Compiled By: Chaudhary Amit V.
Comments
Post a Comment