SU 2 The database environment Flashcards
Database environment consists of 3 components
- Users of the database
- Database management systems (DBMS)
- Physical database
Advantages of using a database environment
- Reduce date redundancy
- Reduced costs for date entry and data storage
- Data integrity is maintained and improved
- Improved data and information security
- Application software independence
- Standardization of data structures, data access, system software and file format
- Improved data access
Disadvantages of using a database environment
- Start-up and operating costs
- Complex to design and use
- Time consuming to design
- Database or database management software failure
Database users
- End-users
- Application programmers
- Database administrator
- Data administrator
- Database management systems (DBMS)
End-users
Capture data in the database and extract information form the database using database management system software
Application programmers
Responsible for creating, maintaining, updating and managing the application and DBMS software
Database administrator
Responsible for managing and controlling the organisation’s databases
Database administrator functions
- Implement and maintain database management standards and conventions
- Ensure applications software complies to database management standards and conventions
- Define the database structures
- Design and create databases in line with database management standards and conventions.
- Implement, maintain and evaluate database access policies and security controls
- Monitor data and database security and access.
Data administrator
Also called database analyst. Responsible for managing and controlling the data in the organisation’s databases
Database management systems (DBMS)
Integrated set of software that provides a user-friendly interface to the users for all data interactions between the user and the physical database
DBMS functions
- design, create and maintain the database structure and the database
- control the organisation, storage and retrieval of data in the database
- capture, maintain and manipulate the data in the database
- share data between multiple users simultaneously
- execute queries and generate outputs
- control the movement of the data between authorised users and the database
- control and monitor access to the database
- analyse and monitor database performance
Three-level database architecture
- External level
- Conceptual level
- Internal level
External level
User view, is the individual end-user’s view if the data and the database
Conceptual level
Complete view of the entire database
Internal level
Physical view, is the low-level view of how the data is physically stored on a storage device
DBMS Key components
- Data dictionary
* Database languages
Database dictionary
centralized file containing detailed information about the database and the data contained in the database
Information a database dictionary contains
- What data is stored in the database
- For each data field in the database, information
- Names and descriptions of the database files
- Each database file a list of attributes, primary keys and foreign keys included
- Authorized user groups for the database files and/or data fields
Database language
Database users use different database languages to interact with the database.
Database languages
- Data definition language
- Data control language
- Data manipulation language
- Data query language
Data definition language (DDL)
Used to define a database and includes commands to:
- create, modify and delete the database and database objects
- define and describe the data structure of the database according to the database model used
- create the data dictionary
Data control language (DCL)
Controls the security and user access to the database objects and data in the database
Data manipulation language (DML)
Routine operation of the database to insert, delete, modify and maintain the data stored on the database
Data query language
Used to retrieve data from the database
Physical database
Organised collection of related data that is managed and stored electronically and can provide data to different application software.
Data models
- Hierarchical model
- Network model
- Relational model
- Object- oriented model
- Multidimensional model
Hierarchical model
Used in early databases and, as the name indicated, the data is structure in a hierarchical (upside down tree-like) structure
Network model
supports many-to-many relationships, data may be accessed by following several paths
Relational model
data is stored in two-dimensional rows and columns
Disadvantage of relational model
slower than the network and hierarchical models because it uses more processing power to query data
Advantages of relational databases
- Data can be accessed, inserted and deleted without changing the database structure
- Can be easily customised for most types of data storage
- Data does not need to be duplicated
- Most users easily understand the structure
- It is easy to search for and extract data from the database
Object-oriented model
data and the operations to be performed on the data are both stored in the database
Multidimensional model
stores data in a three-or more dimensional table, creating a cube-like data structure
Centralised database
Physically stored in one central location
Distributed database
Several interlinked databases stored in several computers in the same or different locations
Partitioned database
Split into smaller portions, part applicable to the user is made available on the location closest to the user
Replicated database
The whole original database is copied to the different locations.
Data value
Character or a group of related characters used to populate a data field
Data field
Contains a data value and is the smallest unit of data that can be accessed in a database
Attribute
Commonly known as a column represents one unique characteristic of a single database file
Field name
All Attributes have a unique name known as a field name
Data record
Set of logically related data fields about a single member or item
Primary data field
Unique data field that can be used to uniquely identify each data record in a database file (primary key)
Foreign key
When a primary data field of a database file is entered into another database file to create a relation between the two database files.
Database file
Also known as a database table, organised collection of related data records
Different types of database files
- Master file
- Transaction file
- Reference file
- History file
Master file
Contains data records of a relative permanent nature about the organisations resources and subjects
Transaction file
Contains data records relating to the daily individual activities of the organisation. Changes regularly as additional transactions are processed.
Reference file
semi-permanent file containing data records referenced to by the transaction file in order to complete a transaction.
History file
Contains data record about transactions completed in the past