Topic 1 – Data management and Utilisation

Study Unit 2: The Database Environment 27

2 The Database environment – page 28

 

The database environment consists of three components, namely the users of the database, database management systems (DBMS) and the physical database, which includes both the hardware it runs on and the data in the physical database.

 

• The users -all the people in a business who capture all the information
• A database management system an application such as pastel
• A physical database – a machine that stores the database, such as a server

 

3 Advantages and disadvantages of using a database environment – page 28

 

3.1 Advantages

 

– Name three (3) advantages of using a database (Marks 6)

 

ANY THREE

• Reduced data redundancy
• Reduced costs for data entry and data storage
• Data integrity is maintained and improved
• Improved data and information security
• Application software independence
• Standardisation of data structures, data access, system software and file formats
• Improved data access

 

3.2 Disadvantages

 

– Name three (3) advantages of using a database (Marks 6)

 

• The start-up and operating costs are high
• A DBMS is complex to design and use
• Databases become time-consuming to create
• Database’s or database management software failure

 

4 Database users – page 29

 

– Name the four (4) database users?

 

• End Users
• Application Programmers
• Database Administrators
• Data Administrator

 

4.1 End users

 

End-users capture data in the database and extract information from the database using database management system software.

 

4.2 Application programmers

 

Application programmers are responsible for creating, maintaining, updating and managing the application and DBMS software which the end-users use to interact with the physical database

 

4.3 Database administrators

 

The database administrator is responsible for managing and controlling the organisations databases.

 

4.4 Data administrator

 

The data administrator, also called a database analyst, is responsible for managing and controlling the data in the organisations databases.

 

5 Database management systems (DBMS) – page 30

 

5.1 DBMS Functions

 

A DBMS is a set of software that provides a (UI) user ‘friendly’ interface for all data interactions between the user and the physical database.

 

– What must a database allow end users to do (Mark 2)

 

• DBMS end users to capture, maintain, delete and manipulate

 

– What must a database allow administrator/s allow to do (Mark 4)

 

• DBMS administrator/s to organise, store and retrieve data
• DBMS administrator/s to execute queries and generate outputs
• DBMS administrator/s to share data between users simultaneously
• DBMS administrator/s to manage and control the movement
• DBMS administrator/s to analyse and monitor

 

– What must a a database allow application programmers to do (Mark 2)

 

• DBMS application programmers to design, create and maintain

 

5.2 Three-level database architecture – page 31

 

ANSI-SPARC (American National Standards Institute [ANSI] – Standards Planning and Requirements Committee [SPARC]) suggested a three-level database architecture, namely an external level, level and level (Boczko 2007:314). This three-level database architecture is now commonly used in modern DBMS frameworks and is based on the different views of data in a DBMS.

 

– Name the Three-levels of database architecture (Mark 3)

 

• External Level
• Conceptual Level
• Internal Level

 

– Define External Level

 

The external level, also called the user view, is the individual end-user’s view of the data and the database.

 

– Define Conceptual Level

 

The conceptual level is a complete view of the entire database, that is, a view of all the data from

which the user views can be derived.

 

– Define Internal Level

 

The internal level, also called the physical view, is the low-level view of how the data is physically

stored on a storage device such as a magnetic hard drive disk.

 

5.3 DBMS key components

 

There are two main components in creating any database

 

– Define Data dictionary

 

A data dictionary is a collection of descriptions of the data objects or items in a data model for the benefit of programmers and others who need to refer to them.

It is like storing all up-to-date information about the objects like tables, columns, index, constraints, functions etc.

 

Amongst others, the information a data dictionary contains include

 

• What data is stored in the database
• For each data field in the database information such as:
• the name and description of the data field
• other names the data field may have
• a range of acceptable values
• the data type (type of data stored, ie numeric, alphabetic, alphanumeric, date, etc)
• the field length (the number of characters that can be entered into the data field)
• the software and records it is used in
• the source of the data field
• outputs in which it is used
• The names and descriptions of the database files.
• For each database file, a list of attributes, primary keys and foreign keys included
• The authorised user groups for the database files and/or data fields

 

– Define Database languages

 

A data definition language (DDL) is the common standard for commands that define the different structures in a database. DDL statements create, modify, and remove database objects such as tables, indexes, and users.

 

– What does DDL stand for (Mark 1)

 

Data definition language

 

– What does DML stand for (Mark 1)

 

Data manipulation language

 

– What does DCL stand for (Mark 1)

 

Data control language

 

Database languages are made up of

 

DDL – a data definition language

 

1. Creates, modifies & deletes databases and database objects
2. Defines and describes the database model
3. Creates the data dictionary

 

DCL –  a data control language

1. Controls the security and user access to the database

 

DML –  a data manipulation language

1. Inserts, deletes, modifies and maintains the stored data

 

A data query language

1. Runs queries from the database to retrieve data
2. It asks questions of the database, to provide information

(A query is a request for information from a database)

 

What is the difference between DML and DDL?

 

DML is used for the data stored in the DB;

DDL is used on the database objects and structure

 

We will return to this in section 6.3

 

6 Physical Database – page 33

 

This is an organised set of related data that is managed and stored electronically.

 

Through a DBMS different application software’s and users can access the same data and variety of other data in the database

 

The end result that we require from a database :

1. Is to minimise duplicate data
2. Is to minimise or avoid data modification issues
3. Is to simplify queries

 

6.1 Database Models

 

– Name the five (5) types of database models (Mark 5)

 

• Hierarchical model
• Network model
• Relational model
• Object-oriented model
• Multidimensional model

 

– Define Hierarchical model

 

This model was used in early databases and, as the name indicates, the data is structured in a hierarchical (upside down tree-like) structure.

 

• Used in early dbs;
• Data is structured in hierarchy;
• One-to-many relationship
• Parent can have many children;
• Child can have only one parent

 

– Define Network model

 

The network model supports many-to-many relationships, that is, data may be accessed by following several paths

 

• Many-to-many relationships;
• Child with multiple parents,
• Parents with multiple children,
• Relationships between children;
• Nearly obsolete

 

– Define Relational model

 

In a relational model, data is stored in two-dimensional rows and columns (ie tables).

 

 – Define Object-oriented model

 

In an object-oriented model, the data and the operations to be performed on the data are both stored in the database. This database model can furthermore store and process a wider range of data types than only text and numerical data – it also stores and processes images, audio and video data

 

• Can also store and process multimedia objects;
• Used for specialised DBs;
• Expensive to implement

 

– Define Multidimensional model

 

• Similar to a relational DB;
• Data stored in three or more dimensional tables creating a cube-like structure;
• Mainly used for data warehouses; makes OLAP and BIS possible

 

6.2 Centralised and distributed databases – page 36

 

The physical location of an organisations databases will depend on its specific business needs and requirements. We can classify a database according to its physical storage location as either a centralised or a distributed database.

 

– Name the two (2) physical names for databases (Mark 2)

 

• Centralised Database
• Distributed Database

 

– Define a centralised database (Mark 2)

 

When using a centralised database, the database is physically stored in one central location (ie it is on one server).

 

– Define a Distributed database (Mark 2)

 

When using a distributed database, there are several interlinked databases stored in several computers in the same (eg headquarters) or different locations (eg branches).

 

A distributed database can reside on network servers on the internet, on corporate intranets or extranets, or on other company networks.

 

A Distributed database is a database in which data is stored in storage devices that are not located in the same physical location but the database is controlled using a central database management system (DBMS).

 

– Name the two types of Distributed databases (Mark 2)

 

• Partitioned database
• Replicated database

 

– Define Partitioned database

 

Partitioning is the process of splitting your data into multiple instances (smaller portions). It allows for much larger databases, using the total memory of many computers

 

– Define Replicated database

 

Replication involves using specialized software that looks for changes in the distributive database. Once the changes have been identified, the replication process makes all the databases look the same (synchronising or duplicating).

 

The replication process can be very complex and time-consuming depending on the size and number of the distributive databases.

  

– Name the two ways of replicating a database (Mark 2)

 

• Duplicating a database
• Synchronising a database

 

– Define Duplicating a database

 

A duplicated database is a copy of the source database. In duplication, the master (original) database is copied to the other locations, normally at a specific frequency and time, and will overwrite the database at the distributed locations.

 

– Define Synchronising a database

 

Synchronising a database is also called mirroring usually with the purpose of keeping the two locations identical to each other. (i.e. the master database can update the distributed database and the distributed database can update the master database).

 

6.3 Relational database terminology – page 37

 

A relational database is a special kind of database designed to save space and cut down on errors by avoiding the duplication of data.

 

• They consist of a number of linked tables, each of which contains its own set of columns and rows.
• Each table has a column that contains a different value for each row in the table.
• This column is called the primary key
• There will only be one row in the table for which the primary key contains that value, eg invoice number
• When a row in a table refers to a single row in another table, in that referring table this is called a foreign key

 

What is the disadvantage of the relational DB model?

 

Slower than the network and hierarchical models because it uses more processing power to query data

 

List the benefits of the relational DB model.

 

• Data can be accessed, inserted and / or deleted without changing the DB structure;
• DB structure can be easily customised for most type of data storage;
• Data does not need to be duplicated;
• Most users easily understand the structure; easy to search for and extract data from the DB

  

– What is a Data Value (Mark 2)

 

A data value is a character (a single number, letter or special character) or a group of related characters used to populate the data field.

 

– What is a Data Field (Mark 2)

 

A data field contains a single data value and is the smallest unit of data that can be accessed in a database.

 

– What is a attribute (Mark 2)

 

An attribute, commonly known as a column, represents one unique characteristic of a single database table.

 

– What is a field name (Mark 2)

 

All attributes have a unique name known as a field name, which labels the data stored in the attribute.

 

– What is a data Record (Mark 2)

 

A data record is a set of logically related data fields about a single member or item.

 

– What is a data Record (Mark 2)

 

Each file has a unique data field (known as the primary data field) that can be used to uniquely identify each data record in a data file.

 

– What is a foreign key (Mark 2)

 

When a primary data field of a database file is entered into another database fi le to create a relation between the two database files, the primary data field in the other database file is known as a foreign key.

 

– What is a database file (Mark 2)

 

A database file, also known as a database table, is an organised collection of related data records.

 

Each database file contains related records – that is, the records in the database file have a common theme. There are different types of database files, namely

 

• master files,
• transaction files,
• reference files
• history files.

 

– Define Master File

 

A master file contains data records of a relative permanent nature (ie they do not change regularly) about the organisations resources and subjects (ie customers, suppliers, inventory, employees, etc).

 

– Define Transaction File

 

A transaction file contains data records relating to the daily individual activities of the organisation (eg the organisations sales). A transaction fi le changes regularly as additional transactions are processed.

 

– Define Reference File

 

A reference file is a semi-permanent file containing data records referenced by the transaction file in order to complete a transaction.

 

– Define History Files

 

A history file contains data records about transactions completed in the past.

 

7 Factors to consider when choosing a DBMS and database – page 43

 

The database model type used should support the requirements of the organisation

 

• Future organisational needs;
• The Performance;
• The Cost;
• The Hardware (cost implication);
• The Specialised skills required;
• Number of concurrent users;
• Vendor should be reputable and stable
• Integration with the rest of the organisations software;
• The DB size;