Unit 8.1 Basic Database Concepts

Pro’s

Con’s

Easy setup - one table

Lots of redundant data as its repeated

All data in one place so it may be easier to find

Hard to manage/maintain & expand in future

Data access can be slow as there is more to search through

Pro’s

Con’s

Less redundant data so smaller file size

More complex structure as more tables need to have relationships set up

Easier to manage/maintain/expand in future

Data spread out between multiple tables

Faster data access as less redundant data

More technical knowledge and skills needed due to harder setup

Term

Meaning

Example

Entities

Person/Place/thing/object/concept about which data can be collected and stored

Customers/Houses/Cars/Drs/Teachers/students/Products

Attribute

Single part of an entity

If Entity is House, Attribute=Sale Price, Address, Property Type, Bedroom/Bathroom No. etc.

Table

Contains info about an entity and is made up of records and fields

If Entity is Customers, Table contains name, average spend, payment type etc.

Records (Tuples)

Single row within table, collection of fields about single item/event, must contain one Primary Key

This table has records - from Entities to Examples is a record. - Horizontal line

Fields

Column within a table and is separate part of a record

Lists of names/numbers, this table has fields such as terms, meaning and example. - Vertical line

Primary Keys

Field in a table that means each record can be uniquely Identified

Patient No, NI No, Student No, Candidate No, Bank A/c No, Driver No, Membership No. (Cant be names as multiple people can have same name)

Candidate Keys

Field(s) within table that uniquely ID’s each record, primary key can be selected from one of them

Email address & Phone no. are candidate keys as unique to a person, usable as primary keys however these can change so customer no. would be more appropriate

Secondary Keys

Key fields used in addition to Primary Key to locate specific info within database, more than one can be used.

Student Table, Student ID best for Primary Key but harder to remember - so surname becomes Secondary Key as its easier to remember

Foreign Keys

Field in one table that links to a Primary Key in another table

See below 3 tables - Foreign Key links to a Primary Key using a relationship - common field in multiple tables

Composite Keys

(Not on specification but good to know)

If one Primary Key cant be found, multiple fields can be added together to create a unique identity. Usually formed in 1NF but is taken out during 2NF.

Database about questions - Composite Key would be Topic, Question and Answer as this makes the record unique but this causes issues with 2NF characteristic of all non-key fields depend on ALL key fields (see 8.2)