Database Concept

CONCEPT OF DATABASE

Database is a data structure used to store organized information. A database is typically made up of many linked tables of rows and columns. For example, a company might use a database to store information about their products, their employees, and financial information. Databases are now also used in nearly all ecommerce sites to store product inventory and customer information. Database software, such as Microsoft Access, FileMaker Pro, and MySQL is designed to help companies and individuals organize large amounts of information in a way where the data can be easily searched, sorted, and updated.

7.1 DEFINITIONS OF A DATABASE

1. A Database (DB) is structure that can store information about: multiple types of entities, the attributes that describe those entities; and the relationships among the entities
2. A Database (DB) is collection of related data - with the following properties:

·         A Database is logically coherent and has some relevant meaning

·         A Database is designed, built and populated with data for a specific purpose

·         A Database represents some aspect of the real world.

3. A database is an integrated, self-describing collection of related data

·         Integrated: Data is stored in a uniform way, typically all in one place (a single physical computer for example)

·         Self-Describing: A database maintains a description of the data it contains (Catalog)

·         Related: Data has some relationship to other data. In a University we have students who take courses taught by professors

By taking advantage of relationships and integration, we can provide information to users as opposed to simply data. We can also say that the database is a model of what the users perceive.

7.1.1 DATA MINING

Data mining is primarily used as a part of information system today, by companies with strong consumer focus retail, financial, communication, and marketing organizations. It enables these companies to determine relationships among "internal" factors such as price, product positioning, or staff skills, and "external" factors such as economic indicators, competition, and customer demographics. And, it enables them to determine the impact on sales, customer satisfaction, and corporate profits. Finally, it enables them to "drill down" into summary information to view detail transactional data. With data mining, a retailer could use point of sale records of customer purchases to send targeted promotions based on an individual's purchase history. By mining demographic data from comment or warranty cards, the retailer could develop products and promotions to appeal to specific customer segments.

7.1.2 DATA WAREHOUSING

A data warehouse is a copy of transaction data specifically structured for querying and reporting. The main output from data warehouse systems is either tabular listings (queries) with minimal formatting or highly formatted "formal" reports on business activities. This becomes a convenient way to handle the information being generated by various processes. Data warehouse is an archive of information collected from wide multiple sources, stored under a unified scheme, at a single site. This data is stored for a long time permitting the user an access to archived data for years. The data stored and the subsequent report generated out of a querying Process enables decision making quickly. This concept is useful for big companies having plenty of data on their business processes. Big companies have bigger problems and complex problems. Decision makers require access to information from all sources. Setting up queries on individual processes may be tedious and inefficient. Data warehouse may be considered under such situations.

7.2 FILE SYSTEMS

A File System is a collection of individual files accessed by applications programs. Usually the files are stored separately in cabinets and the control of the data is not centralized. It is also called traditional file processing approach that focuses on informational needs of individual departments/applications. For example, accounting department has its own application, similarly Sales, Production, Purchases department and others each have its own application.

7.2.1 LIMITATIONS OF A FILE SYSTEM:

• Data redundancy - These files are likely to have some data in common, i.e. duplication of data (data redundancy). This makes it expensive to store and update such files. It wastes space and can lead to data integrity (inconsistency) problems

·         Lack of data integrity - Since the files are maintained separately data about one record may bear inconsistent values, thus leading to inaccurate reporting.

·         Incapable of ad hoc reporting - Since the files are not related easily it would take much time to get an unplanned-for report involving data analysis from all the functional files.

·         Security risks - The data is owned by the departments that use it and therefore it is not easily to control the data as an organization resource making data possible to be subject to fraud.

•  Isolated Data - Makes coordinating, assimilating and representing data difficult
• Application Program Dependencies - Changes to a single file can require changes to numerous application programs thus incompatible Files
• Lack of Data Sharing - Difficult to control access to files, especially some portions of files

7.3 DATABASE MANAGEMENT SYSTEMS

Redefine the term database as a single organized collection of structured data, stored with a minimum duplicated data so as to provide a consistent and controlled pool of data. This data is common to all users of the system, but is independent of programs that use the data. The following are the features that characterize a database approach

·         Share ability of data - Data is stored in a centralized database to which all potential users are permitted access.

·         Centralized control -   The database is managed and controlled by a Database Administrator. Centralization enables DBA to be impartial toward individual departments needs and to make decisions that are to the best of the interest of the organization.

·         Adaptability - Database approach is adaptable to change. It allows users to easily relate existing data items quickly to others that emerge. New requests are easily assimilated into the existing database as the database grows.

·          Application specific approach - This means there is a set of electronic files dedicated to each business function with each set being accessed by a different program.

7.3.1 ADVANTAGES OF DATABASE APPROACH

·         Redundancy control -             Data items are stored ideally once therefore there is reduced replication or duplication on data.

·         Improved data integrity - Due to the fact that data about a record is stored ideally once when it is updated everyone using that data gets up to date data. There are therefore no inconsistencies in data stored. This is an aspect of data integrity.

·         Improved data security - Since data is centrally managed by the Database Administrator it is easy to monitor and control access and use of the database. This done using user names and passwords, and assignment of user rights and their subsequent revoking when necessary

·         Data independence - The data in the database is independent of the programs that access it and the storage media in which it is stored. Therefore programs that access the data can change without need to change the database or even the storages.

·         Multiple schemas /user views - Since the database is designed around the organization rather than specific applications it is possible to have many user views or sub schemas according to users’ needs. User views are only selected record types, fields or relationships in the conceptual database.

·         Reduced overall cost - Due to reduced redundancy, data independence and consequently database adaptability, there are substantial cost reductions by use of database approach.

·         Data Shareability - Data in a database is widely available for all users

·         Organization resource - Installation of a database encourages management of data as an organization resource thus fostering the achievement of organizations objectives.

·         Ease in auditing - Data is centrally managed and controlled so it makes it easy to audit the system for it efficiency and effectiveness.

7.3.2 DISADVANTAGES OF DATABASE APPROACH

·         Complex conceptual - Developing a conceptual database for an entire organization is usually too complex. Finally some organizations end designing several functional database e.g. financial databases, marketing databases, etc.

·         High acquisition costs – Need to hire database related employees, An organization must hire a DBA and other related staff, system development time, acquisition of DBMS software, file conversion, database operations: such as back-ups

·         Complex programmer environment - Testing a DBMS during its development is very complex.

·         Potentially catastrophic - If DBMS fails the loss to the organization can be great since its operations may get totally impaired.

·         Longer running time for applications - As programs complexity increases, so does the amount of time required to run the program. Therefore applications using DBMS may take longer to run unless installed on very fast CPUs or disk drives and remain memory resident.

·         High costs of operations - The costs of converting applications to a new DBMS are quite high and also DBMS requires, Definition of database, DBMS, DBA, file management systems, Advantages and Disadvantages of Database approach, Components of DBMS, Types of DBMS structures, Duties of DBA

7.4 CONTENTS OF A DATABASE

1. User Data - Data users work with directly by entering, updating and viewing. For our purposes, data will be generally stored in tables with some relationships between tables. Each table has one or more columns. A set of columns forms a database record. Recall the fact that a database is self- describing
2. Metadata: Data about data i.e. Data that describe how user data are stored in terms of table name, column name, data type, length, primary keys, etc. Metadata are typically stored in System tables or System Catalog and are typically only directly accessible by the DBMS or by the system administrator.

NB: Applications Metadata - is accessed via the database development programs and where many DBMS have storage facilities for forms, reports, queries and other application components.

3. Indexes - In keeping with our desire to provide users with several different views of data, indexes provide an alternate means of accessing user data. Sorting and Searching: An index for our new banking example might include the account numbers in a sorted order. Indexes allow the database to access a record without having to search through the entire table. Updating data requires an extra step: The index must also be updated. Example: Index in a book consists of two things: A Keyword stored in order and a pointer to the rest of the information. In the case of the book, the pointer is a page number.

7.4.1 DATABASE SYSTEM COMPONENTS

·         The term database system refers to the organization of the components that define and regulate the collection, storage, management and the use of the data within a database environment. The database eliminates most of the file systems that have data inconsistencies, anomalies and structural dependency problems.

·         The current generations of the DBMS software stores not only the data structures in a central location but also stores the relationships between the database components. The DBMS also takes care of defining all the required access paths on the required component. Consider the following database system components

1.      Hardware – this identifies all the system’s physical devices e.g. the composition peripherals, storage devices etc

2.      Software – this comprises of the collection of the programs used by the computer within the database system and includes:-

• Operating system – manages the hardware components and makes it possible for all other and software to run or use the computer
• DBMS – manages database within the database system e.g. Oracle, DB2, Ms Access etc
• Application programs and utilities to access and manipulate data in the database system

3.      Data – the collection of the facts that are stored in the database and serves as the raw material from which the information is generated – thus the determination of what is to be stored in the database and how the data is to be organized; which is a vital part of the database design task

4.      Procedures – these are instructions that govern the design and the use of the database system. They enforce the standards by which the business is conducted within the organization and with the customers. They also ensure that there is an organized way to monitor and audit both the data that enters the database and the information that is generated through the use or processing of such data

5.      People – these are basically the database users and include:-

·         Systems Administrator – oversees the database system’s general operations

·         Database Designer – the database architects who design the database structure

·         Systems Analyst – analyses and implements the application programs and creates the procedures through which the users access and manipulate the data in the database

·         End Users – these are the people who use the application programs to run the organization’s daily operations

·         Database Administrator – manages the DBMS use and ensures that the database is operating properly

7.4.2 DATABASE ADMINISTRATOR

The DBA is a person who controls activities in a Database Management System environment. The person must have good knowledge of the databases and the DBMS. The person should also have a thorough knowledge of the organization and its information system needs.

7.5 FUNCTIONS OF DBA

The DBA must perform the following roles:

·         Scheme definition – original database scheme is created by writing a set of definitions which are translated to tables that are permanently stored in the data dictionary

·         Storage structure – ensure that facilities to access data and produce outputs are appropriate for the organization.

·         Access methods definition – these are translated by the data storage and definition language compiler

·         Physical modifications – allows for the modifications to the appropriate internal systems tables

·         Granting authorization – grant data access so as to regulate which the parts of the database users can access

·         Maintaining integrity constraints – these are special system features that are retained whenever an update takes place in the system and that data can be added in the required formats and constraints

·         Integrity constraint specification - ensures that data in the database is complete, accurate, and not duplicated.

·         Maintaining flexible structure - provides for a form which can be modified without endangering then instances in the database.

·         System controls - provide for system recovery, backup procedures,  control on passwords, privileges, granting of permissions etc

·         Document controls - so as to ensure that the applications and equipment are safe from piracy and vandalism

7.6 DATABASE SECURITY   

 As database practitioners, we must provide a means of preventing unauthorized use of data in a database. Three areas are considered

1. Access Control: Who should be allowed access to which databases? This is typically enforced using system level accounts with passwords.
2. Authorization: for the purposes of:

·         Reading Data - Such as reading another employee's salary (using a SELECT statement)

·         Writing Data - Such as changing a value in a database (using UPDATE or DELETE)

3.      Statistical Information: Enforcing who should be allowed access to information derived from underlying databases (Census example).

7.6.1 DB SECURITY ISSUES

• Legal – maintain licenses and contract terms
• Ethical considerations - Who has the right to read what information
• Policy issues - Who should enforce security (government, corporations etc)
• System-Level issues - Where should security be enforced in the system and how

7.7 DBMS SECURITY FEATURES

7.7.1 AUTHENTICATION

Accounts have user-ids and password; Passwords are stored in an encrypted format in the data dictionary. Passwords include user passwords whether across the network or local connections, server to server passwords, and database administrator passwords. Host-based authentication which is based on the operating system’s user accounts which are then passed on to DBMS

7.7.2 PRIVILEGES

New users must be given privileges before they can logon or execute any database operation. There are an impressive number of privileges that can be given, around 100 in all. There are two types of privileges available to be granted to users. They are system and object privileges.

·         System privileges allow a user to create or manipulate objects, but do not give access to actual        

Database objects. System privileges allow a user to execute commands such as ALTER  

TABLE, CREATE TABLE, EXECUTE ANY PROCEDURE, and DELETE TABLE.

·         Object privileges are used to allow access to a specific database object, such as a particular

Table or view and are given at the view level. This allows for an administrator to give users   access to a chosen sub-set of columns or rows in a table, rather than the entire table. Oracle also allows for the user of the GRANT privilege which allows a user to GRANT their privileges to another user or role for objects that they own.

7.7.3 ROLES

Roles are used to ease the management task of assigning a multitude of privileges to users. Roles are first created and then given sets of privileges that can be assigned to users and other roles. Users can be given multiple roles. It is much easier to create sub-sets of privileges that are organized into roles and then assign the role to one or more users. Roles can be protected with passwords. Roles that are protected with passwords require that a password be provided before activating a role unless it is the user’s default role. The password feature can be useful in situations where a user needs access to data through an application but it is not desirable to give the user direct access to the data through the use of a report writing tool, etc. The password can be supplied by the application, thus preventing the user to even need to know the password.

7.7.4 PROFILES

Profiles allow the administrator to place specific restrictions and controls on a number of system resources, password use etc.  These profiles can be defined, named, and then assigned to specific users or groups of users. Two types of profiles:

·         System resource profiles can be used to put user limits on certain system resources such as CPU time, the number of data blocks that can be read per session or program call, the number of concurrent active sessions, idle time, and the maximum connection time for a user. Define and enforce password rules such as password life, grace logins, and account lockout after a defined number of failed login attempts.

·         Product profiles can be used to prevent users from accessing specific commands or all commands in Oracle SQL, SQL*Plus, SQL*Report Writer, and PL*SQL. Use of this option allows the administrator to do such things as prevent user access to the operating system (SQL*PLUS HOST command), and to prevent unauthorized copying of data from one table to another (SQL*PLUS COPY command).

7.7.5 DATABASE SECURITY THREATS

A threat is any situation or event, whether intentional or unintentional, that will adversely affect a system and consequently an organization

·         Human Error -The risks with the highest incidence. Examples include entering incorrect transactions, failing to correct errors, using wrong data files during processing, and failing to carry out instructions in respect of security procedures

·         Technical Error - This is probably the second most common risk after error.  They involve malfunctioning of hardware, system software, application software or communications software

·         Natural Disasters - Fire, flooding, bombs, impact and lightening etc.

·         Deliberate Actions - Fraud. There is a wide variety of different methods by which such fraud can be committed.

·         Commercial Espionage - When considering the value of data to competitors the organization should consider how a particular item of data might complement other data, which a competitor has obtained from publicly available sources.

·         Malicious Damage - Disaffected employees destroying data also includes sabotage also falls under this heading.

7.7.6 SECURITY POLICY 

·         A security policy will include the following:

·         Identification of and qualification of risks

·         Identification of counter-measures

·         Costing of counter-measures

·         Selection of counter-measures

·         Implementation of counter-measures and drawing up of a contingency plan.

NB: Risk Analysis - A contingency can be defined as 'an unscheduled interruption of computing services that requires measures outside the day to day routine operating procedures. A contingency plan must therefore provide for standby procedures to those operations can be performed while normal services are disrupted, recovery procedures, and personnel management policies.

7.7.7 ADMINISTRATIVE CONTROLS

·         Building controls

·         Contingency plans

·         Secure positioning of equipment

·         Physical access controls

·         Personnel controls e.g. selection and division of responsibility.

7.7.8 DEVELOPMENT CONTROLS

When a database system is developed, there should be controls over the design, development and testing  

·         Program testing, system testing and user department's acceptance testing

·         Formal Technical Review - Controls over changes by use configuration management

·         Controls over file conversion - Standards are required for the documentation such as:

·         Requirement Specification; Program Specification; Operations Manual, User Manual

·         Legal Issues - legal contracts concerning software, Maintenance Agreements, Copyrights,  Licenses, Privacy (Data Protection Act)

7.8 DATABASE AVAILABILITY

·         Backups - "Cold" backups allow backups when the database is down. "Hot" backups allow backups to be done while the database is up. Logical backups or "exports" take a snapshot of the database at a given point in time by user or specific table(s) and allow recovery of the full database or of single tables if needed. There is also a sophisticated Recovery Manager facility which catalogs backup sets to aid in successful recovery

·         Database Encryption - Oracle 8i provides a special PL/SQL package which can be used to encrypt and decrypt data. However, encryption has a high cost in over-head due to the processing power needed to execute the complex encryption/decryption algorithms. For these reasons, Oracle 8i does not provide full database encryption.

·         Auditing - There are three standard types of auditing available in Oracle, including SQL statement-level, privilege-level, and object-level auditing. Audit records can be written to the standard Oracle audit table, to an operating system audit trail (dependent on operating system used), or to an external file. The three basic types of auditing can be done by user, successful or non-successful attempts, and by session or access time intervals. The standard auditing is useful but is at the table level. It cannot be used to audit at the record or column level.