TOPIC 12: DATABASES


DATABASES

A Database is a collection of logically related data with descriptions designed to meet the information needs of an organisation.
A Databank is an enormous/large collection of two or more databases for several users within and outside an organisation.

Database management system (DBMS) is software system that allows multiple users to define, create, store, maintain and control access to the database. Examples of DBMSs include; Microsoft Access, Oracle, Microsoft SQL Server, Dbase, Fox Pro, Sysbase

TYPES OF DATABASES

  1. Flat databases. These consist of one table
  2. Relational databases. These consist of two or more tables and manipulate data by relating the tables.

FUNCTIONS OF A DATABASE MANAGEMENT SYSTEM

  1. Takes care of storage, retrieval and management of large data sets in a database
  2. Used to creates a database structure to accommodate data that may be text, numbers, objects, video, sound
  3. It lets you easily add new records, delete out-dated records, update records
  4. Allows one to organises records in different ways, i.e. sorted and indexed order
  5. Helps to locate specific records, i.e. search, find and replace
  6. Eliminates duplicate data say by editing, e.g. deleting and retyping
  7. Used to create relationships between tables
  8. You can ask questions about your data and get answers using queries
  9. Used to create data entry forms
  10. Used to create professional good-looking reports
  11. Used to change appearance of information, i.e. perform some formatting, etc.

ADVANTAGES OF DATABASE MANAGEMENT SYSTEMS

  1. Sharing of data. Data is easily shared among different users and applications
  2. Data persistence. Data exists beyond the scope of the process that it was created for.
  3. Data security. Data is protected from unauthorised access using passwords. It also provides protection of databases through security, control and recovery facilities
  4. Data validity, integrity&correctness. Data should be correct with respect to the real entity that they represent. Auditing or error check and correction are easily done
  5. Consistency of data. The system always produces consistent values with respect to the relationships
  6. Data integrity. Refers to both correctness and consistency of data. Correctness is being free from errors while consistence is having no conflicts among related data items
  7. Large data storage. It is capable of storing enormous data amounts for personal and organisational use
  8. Nonredundancy. Eliminates or decreases duplication of data in the same container. No two data items in a database should represent the same real-world entity.
  9. Data independence. Both the data and the user program can be altered independently of each other.

DISADVANTAGES OF DATABASE MANAGEMENT SYSTEMS

  1. Complexity. The systems are complex, costly, and take much time to develop, e.g. they include sophisticated software programs that may require special hardware.
  2. Need for substantial conversion effort. Changing from a traditional file oriented system to a computerised database system can involve large-scale reorganisation of data and programs. This can create user resistance
  3. Organisation security may be compromised since a database is used by many people, departments or personnel who may cause havoc by leaking out vital secrets
  4. They are difficult to thoroughly test and audit errors
  5. Initial expense. Because of their complexity and efficiency, they include sophisticated database systems which can be expensive to setup
  6. Requires special skills to handle. Being complex and enormous, databases require skilled personnel to develop, establish and maintain
  7. Vulnerability. Data in the database may be exposed to software and hardware failures, sabotage, theft, destruction, virus attacks, etc.
  8. Routine back-up. Requires back-up systems, which are inconveniencing, complex, tedious and expensive

DISADVANTAGES OF PAPER/MANUAL/FILE-BASED/FLAT DATABASES

Before computerised databases management systems and even now data may be kept and managed manually on paper files and filing cabinets. This system has the following drawbacks or deficiencies

  1. Data redundancy. Data are often repeated in more than one file.
  2. Updating difficulties. Keeping all files up-to-date can be problematic
  3. Data dispersion. Scattered data are difficult for programs and people to share
  4. Under-utilisation of data. Dispersed data cannot usually be used to full advantage
  5. Not durable. Data on manual papers does not last for long
  6. Exposed to risks. Data can be easily lost due to fire, rot, termites, rats, etc.
  7. Data dependence. Programs may be dependent on the data formats and file organisation.

DATABASE OBJECTS

  1. Table. Is a collection of data arranged and stored in rows and columns. It is the basic/primary object where all other objects derive data from.
  2. Query. Is used to ask questions on table data and find qualifying answers.
  3. Form. Is a tool for displaying data from data tables easily and for entering & editing data in the data tables.
  4. Report. Is a summarised and good-looking display of data from tables and queries. It is for output only.

DEFINITION OF TERMINOLOGIES

  1. File. Refers to the entire collection of data in the database.
  2. Field. This is the entire column that contains similar data items
  3. Field name. This is the name/title of a particular field
  4. Field type. This refers to how particular data items are stored in a table
  5. Field properties. This refers to specific characteristics of particular fields
  6. Record. This refers to particulars within a file, or a set of entire data items in a row
  7. Macro. This is an automated procedure of action in a computer
  8. Attribute. This refers to a group of fields or columns in a table
  9. Primary key. This is a unique record identifier in the table. It is used to ensure that there are no duplicate fields in the table. It is also used to create relationships among tables.
  10. A foreign key, is a copy of the primary key in another table
  11. A view. Is a virtual table that does not necessarily exist in its own right but may be dynamically derived from one or more base tables
  12. Relationships. This refers to how two or more entities/tables share information in the database structure. That is, how data in one table are related to data in another table. Relationships are of three types; one-to-one (1:1), one-to-many (1:M) and many-to-many (M:M)
  13. Datasheet view. This is a table view which allows you to update, edit, format and delete information from the table.
  14. Design view is a table view which provides tools for creating fields in a table, i.e. specify field names, data types, field properties and descriptions (a view for creating the table)

CHARACTERISTICS OF DATABASE APPLICATIONS

  1. Data is organised in rows and columns
  2. Each column has a distinct name and represents an attribute of table entities
  3. All values in a column must conform to the same data format or data type.
  4. Each row represents a single entity occurrence (entity instance)
  5. It contains tools known as database objects such as; forms, queries and reports

COMPONENTS OF A DATABASE MANAGEMENT SYSTEM

  1. Data dictionary. This is an automated or manual tool used to store and organise the data in the database and defines each data field that will be contained in the database file including characteristics of each item
  2. Data definition language (DDL). It helps one to create and maintain the data dictionary and define the structure of files in a database. It defines each term as it appears in a database, e.g. delete, create, browse, zap, index, sort, etc.
  3. Data manipulation language. This is used to manipulate data in the database. That is, it helps one to; add, change, select, and delete data in the database and mine it for valuable information

DATA TYPES, FIELD PROPERTIES, VALIDATION CHECKS AND ERRORS

DATA TYPES

Data type specifies and determines the kind/category of values or information entered in the field containers. There are various data types applied in Microsoft Access and these include;

  1. Text. Are alphabetic letters or numbers that cannot be calculated. Examples of such fields are; names, addresses, subject names, course names, telephone numbers, etc. it can contain up to 255 characters.
  2. Number. Refers to numerical data you can calculate but not relating to money, e.g. age, height, weight, course duration, score, number of items in stock. It can be whole number or fraction.
  3. Currency. Are numerical monetary values that can be calculated and may have a currency symbol or not such as £56000.05, 59000.89, $5362, €4563, etc. It is suitable for field like; salary, gross pay, net pay, PAYE, school fees, amount paid, etc.
  4. Memo. It is for lengthy descriptive text and numbers usually several sentences or paragraphs. It can contain a maximum of 32,000 characters. It is suitable for fields like; remarks, comments, particulars, descriptions.
  5. Date/Time. For months, date and time values that are in the form; dd/mm/yy or dd-mm-yy, i.e. date/month/year for dates and Hr:Min:sec, i.e. Hour:Minutes:Seconds for time values. It is suitable for fields like; date of birth, date of joining, on/off set date/time, date/time of departure/arrival, etc.
  6. AutoNumber. A number that automatically increments for each record you enter. It stores sequential numbers entered automatically by Microsoft Access starting with one. They are unique and can make a good primary key. It is suitable for fields like; registration number, ID number, membership number, etc
  7. Yes/No. Here you can enter and store only one value or answer out of the available two options but not both. It is suitable for fields like; true/false, on/off, smoker/non-smoker, Ugandan/Non-Ugandan, in/out, etc.
  8. Object linking and embedding (OLE object). For object data and other binary information such as; sounds, symbols, graphics/pictures such as; signatures, thumbprints, company logos, one’s photo, etc.
  9. Hyperlink. Stores data in form of hyperlinks, which are the blue-coloured hotspots or connections that can be clicked to open other pages or documents, e.g. e-mail address, website, bookmarks, etc.
  10. Lookup wizard. Refers to a list of items in form of a list-box from which you can choose the desired item during data entry, especially if that data exists in another table or form. It is suitable for repetitive data such as marital status; single, married, separated, divorced, widowed, etc
  11. Calculated data type. This new data type lets you create a field that is based on a calculation of other fields in the same table. For example, you might create a Line Total field that contains the product of a Quantity field and a Unit Price field. Then, if you update the Quantity or Unit Price field, the Line Total is updated automatically
  12. Attachment. This is the preferred data type for storing digital images and any type of binary file, like; Pictures, Images, Office files

FIELD PROPERTIES

These are traits or characteristics defining data entered in particular fields. Common properties include

  1. Field size. This specifies the maximum length of a field. That is, the maximum number of characters to be stored in the field. e.g. if you specify field size as 5, only 5 or less characters will be allowed in the column.
  2. Format. Specifies the way that the field appears by default when displayed or printed.
  3. Decimal Places. It is used to specify the number of decimal places to use when displaying numbers
  4. Input Mask. Specifies the pattern or format for data to be entered in that field, e.g. (–/–/–) for date.
  5. Caption. Used to set the text displayed by default in labels for forms, reports, and queries.
  6. Default Value. A value that appears in the field automatically even before you enter there anything.
  7. Validation Rule. An expression that must be true whenever you add or change the value in a given field. e.g. >=10 for age, “married” or “single” for marital status, etc.
  8. Validation Text. A message displayed when a value violates the expression in the Validation Rule property. e.g. “please, marital status is either single or married”
  9. Required. Specifies whether or not an entry must be entered in that field. That is, if yes, you must type an entry, but if no, you may proceed without entering anything.
  10. Allow Zero Length. A provision for a field to be left blank in case of unavailable data to be entered later even if the setting for required is yes. Nulls indicate that data may exist but it is unknown. To enter a null, leave the required property as no and leave the field blank, e.g. a company without a fax number
  11. Indexed. It specifies whether or not duplicates in the field should be allowed in order to speed up the data search, sort, filter, etc.
  12. Text Align. Specifies the default alignment of text within a control.
  13. New Values. Specifies whether an AutoNumber field is incremented or assigned a random value when a new record is added
  14. Unicode Compression. Compresses text stored in this field when a small amount of text is stored (< 4,096 characters).
  15. IME Mode. Controls conversion of characters in an East Asian version of Windows.
  16. IME Sentence Mode. Controls conversion of sentences in an East Asian version of Windows.
  17. Smart Tags. Attaches a smart tag (smart tags: Data recognized and labeled as a particular type. For example, a person’s name or the name of a recent Microsoft Outlook e-mail message recipient is a type of data that can be recognized and labeled with a smart tag.) to this field.
  18. Append Only. Tracks the history of field values (by setting the property’s value to Yes).
  19. Text Format. Choose the property’s Rich Text value to store text as HTML and allow rich formatting. Choose the property’s Plain Text value to store only unformatted text.

DATA VALIDATION

Validation is the process of comparing the data entered with a set of predefined rules or values to check if the data is acceptable. Validation is the name for the checks that detect incorrect data, display an error message and request another input or just reject the data.

Data validation is the checking of input data for errors (e.g. of the correct data type) before processing. Common data validation checks include; presence/existence or completeness check, range check, limit check, data type check or character check or alphanumeric check, format check, consistency check, control total check, and hash total check.

ERRORS

An error is a fault or an issue that arises unexpectedly causing the program not to function properly and to close. Common types of errors include; transcription errors and transposition errors. Transpositions errors include; error of omission, error of addition, random error, overflows error, rounding up error, and truncation errors.

Have any Question or Comment?

Leave a Reply

Your email address will not be published. Required fields are marked *