Relational Databases Flashcards
What is the definition of a table?
A method for implementing an entity and attributes as a group of related data
What is the definition of an entity?
An object about which data will be stored
What is the definition of an attribute?
A characteristic or piece of information about an entity, which would be stored as a field in a relational database
What is the definition of a primary key?
An attribute in each table that uniquely identifies each record
What is the definition of a foreign key?
An attribute in a table that is a primary key in another table and is used to link tables together
What is the definition of normalisation?
The process of ensuring that a relational database is structured efficiently
What is the definition of a composite key?
Two or more attributes used in combination to key a primary key
What is the definition of a relationship?
The link created between two entities
How is first normal form achieved?
- Ensuring that a table does not contain repeating attributes or groups
- All the data in the table is atomic
How is second norm form achieved?
• Ensuring database is in the 1NF
• Removing attributes that depend upon part of but not all of the primary key
-> by creating additional tables
How is third normal form achieved?
- Ensuring the database is in 2NF
* Removing non-key attributes that depend upon other non-key attributes by creating additional tables
What is the definition of a relation?
A relation is a data structure which consists of a heading and an unordered set of tuples which share the same type
Name three key differences between flat file and relational databases.
- Flat file databases store all data in one table
- Flat file databases are less secure than relational databases
- Flat file databases are more likely to be subject to data redundancy
- Relational databases are more difficult to maintain
What happens when relationships have been created between tables?
The tables become one database
What technique must you use when deciding on how many tables are needed?
Normalisation
What is the definition of an entity relationship diagram?
A visual method of describing relationships between entities
What should any many-to-many relationships be replaced by?
One-to-many relationships
What happens if not every record can be identified individually?
Relationships between tables cannot be made
What are the three possible solutions for when records have the same primary key?
- Use a unique attribute
- Create a unique attribute
- Use a composite key
When does redundant data occur?
Redundant data occurs when the same field is unnecessarily duplicated in two or more tables
What is data inconsistency?
Data inconsistency is when the same data is stored multiple times
What does it mean to store data at an atomic level?
Storing data at an atomic level means that the data cannot be further decomposed
What are the three characteristics that a relational database design must have to be fully normalised?
- all data must be atomic
- should be no partial dependencies, where a non-key attribute depends upon part of but not all of the primary key
- should be no non-key dependencies, where a non-key attribute depends upon another bon-key attribute
