YEAR 1 CO1 WEEK 17-18 DATABASES AND NORMALISATION Flashcards
What is a data base?
Is a collection of related data stored in a logical and structured manner
State and describe the parts of a database.
Table/file : collection of related records which contain the same type of information as all the other records.
Record : all of data relating to one item or thing.
Field : stores one item of data in a record.
What is a file(table)?
A collection of related records which contain the same type of info as all other records
Eg info about all students in a class
What is a record?
All data relating to one item or thing
Eg info about one student in a class
What is a field?
Stores one item of data in a record
Eg a single characteristic about a student (DOB , surname)
What are the key points about flat file databases?
Only one table,
No links between them
What are the advantages of a flat file data base?
Easy to set up or understand
What are the disadvantages of a flat file data base?
May have redundant data (repeated).
Potential duplicated data.
Harder to update due to data duplication.
Records are not unique.
Security poor as once gained access all data is readily available.
Harder to perform more complex queries.
Easy to setup and understand.
What is a relational database?
Has more than one table which are linked together by primary and foreign keys
State the advantages of Relational Database and the one disadvantage.
Data only stored once.
Data integrity is maintained.
Easy to modify the format.
Security is better as sections of data can be individually protected using authorisation.
More future proof.
Hard to set up.
What is a primary key?
Unique identifier
what is A foreign key?
Is a primary key in one table which is used as an attribute in another table
/ repeating attribute
What is a compound key?
A combination of fields that makes a record unique
What is a secondary key?
Made on a field that you would like to be indexed for faster searching
May not be unique are used for sorting or searching
What is normalisation (databases)?
Data is split over multiple tables which are related / The process of refining structure of a database to minimise redundancy and improve integrity.
If not normalised potentially has repeated data or inconsistent data.
Describe First Normal Form.
Where there are no repeating attributes and data is atomic.
Atomic data is information which cannot be logically broken down into smaller parts.
Eg. fullname split into forename and surname
Describe Second Normal Form.
If it is in 1NF.
There is no partial key dependencies.
If there is a compound primary key likely not to be in 2NF.
Describe Third Normal Form.
If it is in 2NF.
All data has to be related to the primary key.
What are the three types of integrity(databases)?
Entity integrity
Referential integrity
Domain integrity
What is entity integrity?
Every table must have a primary key which must be unique and not empty
What is referential integrity?
foreign keys must point to the primary of another table but cannot be empty meaning there is no relationship
What is domain integrity?
all attributes in the database are related to the overall domain that the database is working on
What is data redundancy?
refers to the unnecessary duplication of data
what is data consistency?
When data is help in more that one file it should be stored in a consistent way
What is data independence?
This means that users can view data with out having to be aware of the basic structure of the database
Refers to separation of data from program that uses it.
What does ERD stand for?
Entity relationship diagram
What are entities?
they are tables in a database which are made up of multiple attributes
What are Attributes?
Describes the facts, details and characteristics of a entity
What are the three types of relationships?
1 to 1
1 to many
many to many
What is a one to one relationship?
relationships that are one to another and nothing else
What is a one to many relationship?
an example of a one to many is a mother and her children
What is a many to many relationship?
(give an example)
an example of a many to many relationship is a book can be loaned by multiple customers and a customer can loan many books
how can you get rid of many to many relationships?
they can be removed by adding another entity which acts as a link table
-creates one to many relationships
-makes a relationship into 3NF
-reduces data inconsistencies
