Database Definition Flashcards
What is an entity?
An entity is the object / thing of interest about which data is to be recorded (a table in SQL).
What is an attribute?
An attribute is property of an entity (a field in SQL).
What is a relationship?
A relationship is the link or association between entities (primary keys ←→ foreign keys)
What is a key (in a database)?
- A key is the name given to a special field in a database where the values in the field can be used to identify particular records, e.g. CustomerID.
- Sometimes a field that is already in the database can be used as the key field.
What is a primary key?
A key which has a unique value for each record, and acts as a unique identifier for each record
Give three problems with a flat file database:
- One small change can involve tediously changing many records
- Deleting the only instance of a record containing a particular category will also delete that category
- You can’t add a new category until you create a record that contains that particular category
How do you solve the problems with flat file databases?
- Relational databases that are made up of two or more linked tables.
- Linked tables can be used to find records which are linked together by using the value of the shared key column. They are a feature of relational databases.
- This shared key column acts as the primary key in the table in which its defined, and as a foreign key in the table it is used.
What is a flat file database?
A simple store of information
- Columns are called fields
- Rows are called records
- A table is a group of records
What does DDL stand for (in SQL)?
Data Definition Language
What does DML stand for (in SQL)?
Data manipulation language
What is a foreign key?
An attribute in one table that is the primary key field of another table.
It exists to make a link between the tables.
The name is misleading as it don’t have to be a key in the foreign table
What is a composite key?
A key composed of two or more attributes that together uniquely identify a record.
When is normalisation (database) achieved?
- When there is no redundant data and all related data is stored together
- (When each attribute depends on the key, the whole key and nothing but the key)
What is the saying for databases to be fully normalised?
“Each attribute must depend on the key, the whole key, and nothing but the key”
What are the requirements for 1st normal form?
- Every record has a primary key
- No repeating groups → must include a copy of the primary key in the new table (creating a composite key)
- The data in each field must be atomic (i.e. data cannot be sensibly subdivided e.g. name → fname & sname)
What is a repeating group?
A set of attributes whose structure repeats between rows
Requirements for 2nd normal form
No partial dependencies
this means…
- Do all non-key attributes depend on all parts of the composite key?
- If not, take them out and create a new entity.
(So check all tables with composite keys)
Requirements for 3rd normal form
No non-key dependencies
- All non-key attributes are checked to see if they are only dependent on the primary key.
How do you signify the key in an entity relationship diagram?
Underlining
Problems that can occur with databases that are not fully normalised
- Redundant/duplicated data may waste storage space
- If data is stored more than once then it could be inconsistent // two copies of the ‘same’ data item might store different values
- If data is stored more than once then each copy of the data would need to be updated if it changed
- It might not be possible to store data about one type of entity without creating a record for another type of entity // if a record for one type of entity does not exist then it might not be possible to store data about another type of entity
- When a record for one type of entity is deleted it might delete the data about another type of entity // it might not be possible to delete a record for one type of entity without deleting the data about another type of entity
- May be difficult to select/edit data if it is not atomic // if there are repeating groups
