Databases Flashcards
What is a primary key?
A field or fields that uniquely identify a record, e.g. the PIN uniquely identifies a student.
What is a foreign key?
A foreign key is a field in a table that is the primary key in another table and is used to form a link between the two.
What are indexes?
If a field is going to be regularly used as part of a search (e.g. a student name), then this can be set to Indexed to allow searches to be performed much more quickly.
A table can have several indexes if required. A downside of this is that more memory is required to hold each index and every time a record is added or deleted the index needs to be updated which can add a very slight delay.
For this reason only fields which are regularly used in searches should be indexed
What is data consistency?
A well-designed (normalised) database will never have the same information recorded more than once
This ensures that there is always data consistency. If we do have data recorded in multiple places then we have to have excellent systems in place to ensure the data is updated in all places when it is changed.
If the same information was held in more than one place in the database then it could lead to data inconsistency if the data was changed in one place but not in the other.
What is data redundancy?
If data is held in more than one place in the database then not only can it lead to inconsistency but it is also a waste of disk space. It is also wastes processing time to update these different copies.
The data doesn’t need to be held more than once and so any copies of the data are redundant.
What is data independence?
“A database should act as a repository or pool of data, separate from the applications that deal with it.”
e.g. could be used to enter new customer details, customers able to login via a website, editing and changing customer details
What is a database?
An organised collection of data
What are the benefits of a relational database compared to a flat file database?
A relational database allows data to be held in multiple tables that can have relationships between them;
A well-designed (normalised) database will not have any of the problems associated with flat-file databases (redundancy, inconsistency, etc.);
By splitting the single table in the example into 3 tables it is possible to hold exactly the same information without the problems we previously identified.
What is data normalisation?
Normalisation is a database design technique which tries to ensure that a database is designed correctly, avoiding the various problems we have seen with inconsistency and redundancy.
Give the definitions of 1st normal form, 2nd normal form and 3rd normal form
The data goes through 3 stages to produce a normalised database
First Normal Form: A table is in first normal form if it contains no repeating attributes
Second Normal Form: A table is in second normal form if it is already in first normal form and each field is dependant on the whole primary key (has no partial key dependencies)
Third Normal Form: A table is in third normal form if it is already in second normal form and it contains no non-key dependencies.
If all tables in the database are in third-normal form then the database is normalised.
Explain the steps to be taken if there is a many-to-many relationship in an ERD (Entity Relationship Diagram)
Many-to-Many relationships don’t work in databases
- Create a new entity to replace the many-to-many
- on each side create two one-to-many relationships
Explain the role of a DBMS
The DBMS is an application program that provides an interface between the operating system of the computer and the user in order to make access to the data as simple as possible. It has several other functions:
Data storage, retrieval and update. The DBMS must allow users to store, retrieve and update information as easily as possible, without having to know much about the internal structure of the database;
Allows access to queries and reports;
Creation and maintenance of the data dictionary;
Managing the facilities for sharing the database;
Backup and recovery. The DBMS must provide the ability to recover the database in the event of failure (see later);
Security. The DBMS must handle password allocation and checking, and the view of the database that a given user is allowed.
What is a data dictionary?
The Data Dictionary is a database which holds information about the database.
For example there will be a table that holds information about every table in the database. A table that holds information about every field in the database.
Other information in the Data dictionary:
the characteristics of each data item such as length and data type.
any restrictions on the value of certain columns (validation rules).
the relationships between data items.
which programs access which items of data, and whether they merely read the data or change it.
What is data warehousing?
A Data Warehouse represents an ideal vision of maintaining a central repository (large database) of all the useful data in an organisation;
It is very structured, usually using large scale relational databases that can be queried in traditional ways (e.g. SQL);
Transactions, day-to-day operational data, etc. are fed into the data warehouse on a daily basis;
These transactions will usually come from multiple very different sources and systems, often on a national or world-wide level.
The data isn’t generally overwritten so that patterns and trends over long periods can be considered;
Data Mining can be used to search through the data in the Data Warehouse to look for patterns or trends.
What is big data?
Data sets are growing rapidly in part because of all the data being gathered by the vast numbers of cheap devices such as cameras, sensors, mobile devices, etc.
The size of these data-sets grow rapidly as the Internet of Things (IOT) continues to increase where cars, gadgets, buildings, clothing, etc. have electronics embedded and send back data constantly;
This huge amount of largely unstructured data is stored but not in a formal well-designed data warehouse database and this is why Big Data is very different from Data Warehousing.
