Chapter 8 - Databases Flashcards
Data base
Structured collection of items of data that can be accessed by different applications programs
Relational database
A database where the data items are linked by internal pointers
Table
Group of similar data, in a database, with rows for each instance of an entity and columns for each attribute
Record
A row in a table in a database
Field
Column in a database
Tuple
One instance of an entity, which is represented by a row in a table
Entity
Real - life object that is represented as a table
Attribute
An individual data item stored for an entity eg. Person attributes could be name, address etc
Candidate key
An attribute or smallest set of attributes in a table where no tuple has the same value
Primary key
Unique identifier for a table. Special case for a candidate key
Secondary key
Key that is an alternative to the primary key
Foreign key
Set of attributes in one table that refer to the primary key in another table
Relationship
Situation in which one table in a database has a foreign key that refers to a primary key in another table in the database
Referential integrity
Property of a database that does not contain any values of a foreign key that are not matched to the corresponding primary key
Index
Data structure built from one or more columns in a database table to speed up searching for data
E-R model/diagram
Entity - Relationship
Graphical representation of a database and the relationship between the entities
Normalisation
Process of organising data to be stored in a database into two or more
Types of normalisation (3)
1NF
2NF
3NF
Normalisation
The process of organising data to be stored in a database into two or more tables and relationships between the tables, so that data redundancy is minimised
1NF
First Normal Form
Entities do not contain repeated groups of attributes
2NF
Entities are in 1NF and any non-key attributes depend on the primary key. There are no partial dependencies
3NF
Third Normal Form
Entities are in 2NF and all non-key attributes are independent. The table contains no non-key dependencies
DBMS
Database Management System
System software for the definition, creation and manipulation of a database
Data dictionary
Set of data that contains metadata (data about other data) for a database
Data modelling
The analysis and definition of the data structures required in a database and to produce a data model
Logical schema
A data model for a specific database that is independent of the DBMS used to build that database
Access rights
Permissions given to database users to access, modify or delete data
Developer interface
Feature of a DBMS that provides developers with the commands required for definition, creation and manipulation of a database
SQL
Structured Query Language
The standard query language used with relation databases for data definition and data modification
Query processor
Feature of a DBMS that processes and executes queries written in SQL
DDL
Data Definition Language
A language used to create, modify and remove the data structures that form a database
DML
Data Manipulation Language
Language used to add, modify, delete, and retrieve the data stored in a relational database
SQL script
List of SQL commands that perform a given task, often stored in a file for reuse
Composite key
Set of attributes that form a primary key to provide a unique identifier for a table
(Combination of different keys)
What is a file
File is a collection of items of data that can be structured as a collection of records where each record is made up of fields containing data about the same thing
Problem with a file based approach (2)
If the records are to be processed by another program, that program must be written to the exact same record structure.
If the structure is changed by one program, the other program must be rewritten as well, causing problems if updating programs is not carefully managed
Limitations of a file based approach (3)
Storage space is wasted when data items are duplicated by the separate applications and some data is redundant
Data can be altered by one application and not by another, becoming inconsistent
Enquiries available can depend on the structure of the data and the software used so that the data is not independent
Advantages of a relational database compared to a file based approach (5)
Reduced data redundancy
Improved data integrity/consistency/referential integrity
Allows for program-data independence
Complex queries can be executed
How do relational databases keep the number of copies to a minimum
Using pointers between tables
How do databases ensure the consistency of data
Updating is controlled or automatic
Types of keys (4)
Candidate key
Primary key
Secondary key
Foreign key
Forms of relationships (4)
1:1
1:m
m:1
m:m
(Can be mandatory or optional)
How a DBMS addresses the limitations of a file based approach:
Data redundancy issue
By storing data in separate linked tables, which reduces duplication of data as most items are only stored once. Items of data used to link tables (foreign keys) are stored more than once. DBMS flags any possible errors when any attempt is made to accidentally delete this type of item
How a DBMS addresses the limitations of a file based approach:
Data inconsistency issue
Solved by storing most items of data only once, allowing updated items to be seen by several applications. Due to the consistency, there is improved data integrity.
How a DBMS addresses the limitations of a file based approach:
Data dependency issue
Data is independent of the application using the database, so changes made to the structure of the data will be managed by the DBMS and have little/no effect on the applications using the database. Any fields/tables added/removed will not affect the applications that don’t use them as each application only has access to the fields/tables it requires.
DBMS approach
It uses a data dictionary to store the metadata, including the definition of tables, attributes, relationships between tables and any indexing. The data dictionary can also define the validation rules used for the entry of data and contain data about the physical storage of the data.
It also makes use of data modelling
It helps to provide data security to prevent the unwanted alteration, corruption, deletion or sharing of data with others that have no right to access it
Benefit of using a data dictionary
It improves the integrity of the data stored, helping to ensure that it is accurate, complete and consistent
Security measures taken by DBMS (6)
Using usernames & passwords to prevent unauthorised access to the database
Using access rights to manage actions authorised users can take (read/write/delete or read only etc.)
Using access rights to manage parts of the database they have access to
Automatic creation and scheduling of regular backups
Encryption of the data stored
Automatic creation of an audit trail or activity log to record the actions taken by users of the database
DBMS software tools (2)
Developer interface
Query processor
Data types for attributes (SQL)
CHARACTER
VARCHAR(n)
BOOLEAN
INTEGER
REAL
DATE
TIME
SQL (DML) maintenance commands (3)
INSERT INTO - adds new row to table
DELETE FROM - removes row from table
UPDATE - edits row in table
SQL (DML) query command (8)
SELECT FROM - fetches data from database
WHERE - includes only rows in a query that match a given condition
ORDER BY - sorts results numerically or alphabetically
GROUP BY - arranges data into groups
INNER JOIN - combines rows from different tables if the join condition is true
SUM - returns sum of all values in a column
COUNT - counts number of rows where column is not NUL
AVG - returns average for a column
