databases Flashcards
what is a
database
an organised collection of data
organising
data in a database is
- easy adding
- ability to modify
- deletion
- searching
electronic databases =
- easy to add, retrieve, delete, update and modify data
- easy to back up
- can be accesed by multiple poeple at the same time from different locations
flat file
A flat file is a database that consists of a single file. The flat file will most likely be based around a single entity and its attributes
advantages of
flat file
- quick to set up
- require little expertise to maintain
- suitable for small amounts of data
eg: storing contact detales, playlist
drawbacks of
flat file
- take up uncessary space
- be slow to query
- become increasingly difficult to maintain
relationship
- recognisesthe differences between entities by creating different tables for each entity
- need a common field for the relationship to work
the link between two tables
Primary Key
A primary key is a unique identifier for each record in the table
Foreign Key
A foreign key is the attribute which links two tables together. The foreign key will exist in one table as the primary key and act as the foreign key in another
Secondary Key
A secondary key allows a database to be searched quickly
One-to-one
Each entity can only be linked to one other entity
relationship between a husband and wife. 1 wife to 1 husband
One-to-many
One table can be associated with many other tables, such as a mother having multiple children. Similarly, multiple child entities can be linked to the same mother entity.
Many-to-many
One entity can be associated with many other entities and the same applies the other way round. An example is students and courses - each student can enrol in more than one course and each course can have more than one student.
normalisation
The process of coming up with the best possible layout for a relational database
normalisation tries to acomplish:
No redundancy (unnecessary duplicates).
● Consistent data throughout linked tables.
● Records can be added and removed without issues.
● Complex queries can be carried out.
first normal form (1NF)
There must be no attribute that contains more than a single value
1) all field names must be unique
2) values in feilds should be from the same domain
3) values in fields should be atomic (single value)
4) no two records can be identical
5) each table needs primary key
second normal form (2NF)
1) already in 1NF
2) any partial dependencies have been removed
to fix
many to many tables
1) create link tables
2) assigning the primary key from the two linking tables as the composite key from the new linking table
3) flip the M:M crows feet to become two 1:M relationships joined by the new table
third normal form 3NF
1) data already in 2NF
2) any transitive dependances (non-key) has been removed
all keys are dependant on the key, the whole key and nothing but the key
why should
all field names must be unique
1NF
to avoid confusion about which record should be retrieved or updated
1NF
values in feilds should be from the same domain
because
each feild must contain the same type of data
values in fields should be atomic (single value)
because
each field should only contain a single item of data
1NF
no two records should be identical
because
duplicarte data take up space unnecisarly and can cause inconsistances if updates are not performed on all of them
1NF
each table needs primary key
because
a field or several fields (composite key) that gives a record of a unique identity
2NF
needs to already be in 1NF
a partial dependancy means one or more feilds depend on only part of the primary key
2NF
remove of partial dependances
because
this sitch can occur if the primary key is a composite key comprised of more than 1 field
2NF
fix any M:M relationships
because
we are aiming to make sure every table seves a single purpose
3NF
remove any transitive dependances
to ensure that non-key fields are not dependant on each other
