Computer Science - 7 Relational Databases and SQL - 1 Databases & 2 Relational Databases

Table name

describes the content of a table

Table

arrangement of data made up of horizontal rows and vertical columns

Field

single characteristic of data that appears in a table as a column

Field name

name that identifies a field

Record

collection of related data elements

Data types [7]:

- Integer

- Real/float/decimal

- Date/Time/Datetime

- Char

- Varchar

- Text

Varchar

string of variable length

Char

string of fixed length

Text

string of variable length that can be very long

Flat file database

database which consists of just one table in a single text file

What format is a flat file database stored in?

CSV

CSV

Comma Separated Values

Problems with flat file databases [2]:

- redundancy

- inconsistency

Redundancy [2]

- takes up unnecessary memory or storage

- takes longer to search

Inconsistency [1]

- difficult to search and sort data

Primary key

field that uniquely identifies each record in a table

ID number

number that is unique for each record - stored as autonumber or int

Autonumber

field that automatically adds a unique number to the primary key field for each record in the table

Relational database

database that represents data as a collection of tables with relationships between them

Relationship

links between tables

Relation

another word for each table in a relational database

Why use a relational database? [2]

- reduce redundancy

- reduce inconsistency

Foreign key

field in a table that references the primary key of another table

Types of relationships [3]:

- one-to-one

- one-to-many

- many-to-many

One-to-one

each entity in the relationship will have exactly one related entity

One-to-many

entity on one side of the relationship can have many related entities, but an entity on the other side will have a maximum of one related entity

Many-to-many

entities on both sides of the relationship can have many related entities on the other side