Relational Databases Flashcards
What is a database?
A collection of organised data that allows access, retrieval, and use of that data.
What is a Relational database?
a collection of related/ linked tables
E.g. all of the student tables for NMHS and subject details tables and specific class details tables.
How is data stored in a DBMS?
Data is grouped into tables (normalisation) and stored in records and fields.
DBMS role
Process data to produce meaningful information for a user by applying, sorting, and/or grouping.
Store data in a way that reduces data redundancy.
Normalisation principles
The need to minimise the duplication of data.
Primary key
Consists of one or more fields that uniquely identifies each record in a table. The data in a primary key is unique to a specific record.
Non-key fields are dependent on the key, and not dependent on any other field.
Factless identifiers are ideal for use as a primary key because they do not change E.g. AutoNumber
Name Examples: Employee ID, Student ID
Foreign key
Tables are linked by joining the primary key from the table on the one-side to the appropriate field, which is called the foreign key, on the many-side.
Composite key
A composite key can be applied in the transaction table to prevent a double transaction.
When a primary key consists of more than one field.
Field
a set of related characters
a single item type of information that appears in every record.
If you want to sort, search, calculate, or report based on an item of information, you should put that item in its own field.
E.g. on a student id card- ‘surname’ is a field
Record
a collection of related fields
a meaningful and consistent way to combine information about something
E.g. everything on a student ID card makes up one record
Tables
a collection of related records.
Lists of rows and columns
E.g. all students enrolled on the SC of NMHS
What are the 3 links between table called?
- One-to-one
- One-to-many
- Many-to-many.
One - to - many
a row in table A can have many matching rows in table B. But a row in table B can have only one matching row in table A.
Only occurs if one of the related columns is a primary key or has a unique constraint
e.g. Each ‘Publisher’ produces many titles. But each ‘Title’ comes from only one ‘Publisher’.
Many - to - many
a row in table A can have many matching rows in table B, and vice versa.
E.g. a single ‘Order’ can include more than one ‘Product’, and a single ‘Product’ can appear on many ‘Orders’.
Solving many to many relationships
The solving of a many-to-many relationship can be achieved by adding a transaction/junction table.
One - to - one
a row in table A can have no more than one matching row in table B.
Only occurs if both of the related columns are primary keys or have unique constraints.
How is data defined?
Data is defined according to a data dictionary that includes field names, types of data, sizes, key(s), required values, and the appropriate validation rules and text messages, default values, formats, and input masks.
These ensure integrity within a table.
Master query
A master query uses fields from all tables and run the query.
When should I use a database instead of a spreadsheet?
Databases are ideal when you have a large amount of related data to store and quickly need to be able to add to, update, query, and create reports about the data. This is fast and efficient and ease of backing up so data is not lost.
Use a spreadsheet instead of a database when you have less data to store but frequently need to perform calculations with the data, create charts, and review statistics.
What is file maintenance?
Procedures that keep data current.
This includes:
- Adding records when new data is obtained
- Modifying records to correct inaccurate data or to update old data with new data
- Deleting records when they no longer are needed
Validation rules
Process of comparing data with a set of rules or values to find out if the data is correct.
Set parameters (or boundaries) for the entry of data.
E.g. In a library system, return data must be greater than today’s date and less than a date in two weeks time.
If a date outside of the boundaries is entered, an error message will pop up for the user.
What is a validity check and name different types?
Many programs perform a validity check that analyses data, either as you enter it or after you enter it, to help ensure that it is correct.
Types include:
- alphabetic check
- numeric check
- range check
- consistency check
- completeness check
- digit check
Data integrity
Identifies the quality of the data, the more errors the data contains, the lower its integrity.
a change made in one table will automatically and accurately be seen/read by all other related tables.
Sharing data
Information in one department can readily be shared with others.
