Database Structure and Links

What is a compound key?

A compound key is a primary key which consists of two or more fields in order to create a unique identifier. This is required when no single field can be used to uniquely identify a record.

What is a Data dictionary?

A data dictionary is a design notation used to describe the data in the database—the metadata for the database system.

What is a database?

A database is a collection of data stored in a structured, organised manner.

What is a flat file databases?

A flat file database has all data is contained in one file (a table or entity). Data is organised into fields (or attributes) and records (rows or occurrences).

What is a foreign key?

A foreign key is the primary key copied from another file, used to link two tables. At the design stage, foreign keys are usually identified using an asterisk*

What is a primary key?

A primary key is a field which is a unique identifier for each record. At the design stage, primary keys are usually identified by underlining.

What is a surrogate key?

A surrogate key is a data item invented by the user to uniquely identify a record. It is used to avoid complex compound keys and meaningful identifiers.

What is cardinality?

Cardinality is used to describe how the tables are linked and an entity relationship diagram is a graphical representation of relationships between entities. 1:1 1:M M:N

Forms

Database creators can prepare forms layouts specially designed for inputting data, to help improve usability. A well laid-out form, using suitable techniques such as drop-down menus, date pickers, radio buttons and checkboxes, reduces the typing required and makes data input easier and faster.

Queries

Queries allows the user to extract and organise a list of data from a database by incorporating searching, sorting and calculating

What are relational databases?

Relational databases have two or more tables linked together.

Reports

Reports allow the presentation of selected data from the database. The output can be customised in a variety of ways, including displaying only chosen fields and formatting the data in a particular way.

SQL- Structured Query Language

SQL is a language used to access and manipulate databases. • It allows you to add/delete/amend records and fields, search records and sort records. • It is used in conjunction with a server-side language, like PHP, to create database-driven websites

What is a relationship?

The relationship is the link between two tables/entities.

What is field validation?

This describes the checks to be carried out on each attribute. Validation is a series of checks carried out by the computer software to ensure that the data entered is accurate and sensible. Using appropriate validation reduces the chance of error when data is input.

What are some problems with flat files?

• Data duplication: Data duplication occurs when the same item of data is entered in more than one place. • Data inconsistency: Data inconsistency occurs when data is duplicated. If the same data item is entered more than once but not entered in exactly the same way meaning more than one version of the data is stored. • Deletion anomaly: A problem arises when removing data about one thing accidentally deletes data about something else. • Insertion anomaly: A problem arises when it is not possible to add a new record because some of the primary key data is missing. • Update anomaly:A problem arises when data needs to be updated but due to data duplication appears in several locations. If every occurrence of the data item is not updated, then this leads to data inconsistency.

Examples of validation techniques...

• Presence check - ensures field cannot be left empty, good validation for a primary key. • Restricted choice - the user is presented with a list of options to choose from using a drop-down menu, option buttons or similar. • Length check - ensures an appropriate number of characters is input, e.g. minimum of 8 characters, maximum of 20. • Range check - used on numeric fields to ensure number is within lower and upper limits e.g. between 0 and 100. • Foreign Key lookup - used to put data into attributes without retyping it - it references the original location in another table - helps maintain data consistency.

Field Types

• Text - contains any letter, number or symbol • Number (Integer) - contains whole numbers only • Number (Real) - contains numbers with decimal or fractional part • Date -contains day, month and year • Time - contains hours, minutes and seconds • Object (a container) - can contain graphic, audio, video, etc. • Link - contains a hyperlink or path to another resource • Boolean - stores Yes/No or True/False.

Explain good database design...

• avoids data duplication by using linked tables • reduces errors in data entry by using suitable validation techniques • is done by normalisation

What are benefits of relational databases?

• reduces unnecessary duplication of data • reduces the opportunity for data inconsistency • provides better data integrity • overcomes deletion, insertion and update anomalies