A database is an organised, structured, collection of related data, designed to allow access by a number of different applications. It holds information about entities in a series of related tables. A primary key uniquely identifies a record in a table. A composite key consists of two or more key fields each of which is a primary key in another table. A foreign key is a key field which is not a primary key in the table but which is s primary key in another table.
Data consistency refers to a particular attribute that has only one value throughout the database. A change to a data value is implemented throughout the database because it is held in only one table. Data integrity refers to the correctness of data which can be affected by input errors and processing errors. However it is minimised by data verification and validation. Data redundancy refers to the same (non-key) data being stored multiple tables causing the database to use up more storage space than required. This can lead to data inconsistency. Data independence refers to data which is kept separate from the software which processes it.
The purpose of an entity relationship diagram is to identify what data is to be stored and processed by the system, to identify which files will be linked or related in the system and to identify how data should be structured to allow for efficient retrievals and further extensions. The purpose of normalisation is to help ensure that a database is structured in the best possible way for processing. It reduces data redundancy and maximises data consistency.
- First Normal Form (1NF) – a table is said to be in first normal form if repeating groups of attributes have been removed
- Second Normal Form (2NF) – a table is in second normal form if it is in 1NF and if attributes dependent on a partial key have been removed i.e. contains no partial key dependencies.
- Third Normal Form (3NF) – a table is in third normal form if it is in 2NF and if all non-key dependencies have been removed i.e. it contains no non-key dependencies.
The DBMS is an application program that provides an interface between the operating system and the user in order to make access to the data as simple as possible. It has several other functions as well: data storage, retrieval and update, the creation and maintenance of the data dictionary, the management of the facilities for sharing the database, backup and recovery as well as security
Components of a relational DBMS
There are three major components to a relational DBMS:
- Data definition language - is the formal language used by programmers to specify the content and structure of the database. It is a descriptive language that is used to outline the entities required for the application, and the relationships that may exist between them.
- Data manipulation language - is a specialised language that is used in conjunction with other generation programming languages to manipulate the data within the database. It contains commands that allow users to extract data from the database in order to carry out user requests and enable applications development.
- Data dictionary - is an automated or manual file that stores definitions of data elements and characteristics such as usage, ownership, authorisation, and security. They can produce lists and reports of data utilisation, groupings, program locations etc… They are passive; they simply report. With more advanced types, a change in the dictionary is automatically used by any related programs.
The role of the database administrator in a DBMS is to define, maintain, and modify the database structure. They control access to the database, assigning access rights etc… They design and modify queries, reports, macros etc… They must inform users of changes, and manage user training. They must manage backups of the system as well as ensuring compliance with legislation.
Management Information System
An MIS transforms large amounts of raw data from a data processing system into useful information which is necessary for a business to be managed effectively. It is used at different levels of management, utilising data from many sources including internal and external sources. The MIS generates appropriate queries and reports for routine and non – routine purposes.
Management Information Systems (MIS) are designed to help managers monitor and control organisational performance and plan for the future. The role of an MIS is to convert data from internal and external sources into information that can be used to aid in making effective decisions for planning, directing, and controlling the activities for which they are responsible.
Role of MIS
Plan; in order to maximise profit
Organise; Resources - people, space, equipment
Coordinate; activities of different departments
Decision make; organisation. products, employees
Control; monitor and supervise activities of others
Failure of MIS
Failure to meet user requirements
System may not be delivered on time
System may exceed original budget
Information output may be incorrect or in an inappropriate format
The user interface may not be easy to use
Top Level; Senior Management
Overall company objectives identified and evaluated
Decisions taken will affect the whole organisation
Long term decision
Middle Level; Department Heads
Responsible for implementing objectives set at Strategic Level
Decisions taken will affect large groups within the organisation
Medium term decision
Lowest Level; Operatives
Small area of responsiblity; face to face with the public
Decisions taken will have effect immdeiately; small number of people
Short term decision
Decision Support System
A Decision Support System (DSS) assists managers in solving complex business problems by applying different business models to data. The problems may be ad-hoc or unstructured problems such as ‘what if’’. It assists organisations with strategic, tactical, and operational decision making. It may incorporate an expert system. A decision support system should not be relied on, as the data used may not have been designed properly and some decisions may require human intuition.
An Expert System (ES) is an application that carries out a task otherwise performed by a human expert, at or near the skill level of the expert. While a DSS makes recommendations that humans are expected to discuss, evaluate and query further, an ES is expected to give the correct answer without the need for discussion. It is suitable only for certain applications where rules can be defined. It is limited to a certain area of knowledge, and is based on rules, facts, and principles. It can deal with fuzzy logic and the system can be explained to the user.
Examples of an expert systems include language translations, medicine, law, gaming and online help
Components of an ES
An ES has three main components:
- Knowledge base - is a store of facts, rules, and principles from a given field.
- Inference engine - solves a problem by applying the rules and knowledge already in the system to the facts that are entered concerning the problem
- User interface - includes menus, graphics and facilities for explaining the system’s reasoning
- Natural disasters
- Faulty hardware and software
- Hackers and viruses
- Insecure communications
- Removal of data on portable media
- Unauthorised access to premises
- Disgruntled ex-employees
Auditing and Accounting software
Auditing and accounting software automatically maintains a log of all database activity. In the event of data being corrupted, either by accident or on purpose, the changes made can be undone to restore the original data. Auditing software records information such as:
- The time a system was accessed, on what terminal, by whom and for how long
- Changes to files; i.e. records of access and inserting, deleting, updating of records or files
- The number of times the database was accessed.
Auditing cannot prevent misuse or guarantee security, but it does allow an organisation to recover data if data is corrupted. A company can manage security through usernames/passwords, limited logins, access rights, locking and audit trails.
Backup and Recovery policy
A ‘backup and recovery policy’ is a self contained section of a security policy that describes how all data can be recovered in the event of a natural or man-made catastrophe. Companies must maintain a full backup of all data in a secure off-site location. The strategy describes how the company will maintain its core business during a natural emergency. The company’s key business processes and there hardware, software, communication technology and personnel are stored in this strategy. There are details on continuity and alternative ICT facilities, along with changeover methods.
In general a backup strategy should:
- Ensure all files are backed up at specified, frequent, intervals and held securely
- State how long backups are to be held
- State how and where files will be backed up
- State the media used to perform the backups
RAID is an exact copy called a mirror image of all data that is kept. All data is recorded on both systems simultaneously, and is stored in a remote location.
Incremental backup is when only data that has been changed since that last backup is backed up. The master data is copied and stored away from the computer system.