10 - Fundamentals of Databases Flashcards
What does it mean for relations to be in Third Normal Form.
Why is it important that the relations in a relational database are in Third Normal Form?
3NF means every non-key attribute is dependent on the key, the whole key and nothing but the key. There are also no repeating groups.
This is important to eliminate update anomalies, eliminate insertion anomalies, eliminate deletion anomalies, eliminate data inconsistency and minimise data duplication.
So help me COD!!!! :()
Footnote - ooo my foot hurts
State the properties that the relations in a fully normalised database must have.
- There are no repeating groups of attributes.
- All non-key attributes depend on the whole key.
- All non-key attributes depend on nothing but the key.
What is the name given to a key that is made up of multiple attributes?
Composite key
Why is it important for a database to be in third normal form?
- Eliminate update anomalies
- Eliminate insertion anomalies
- Eliminate deletion anomalies
- Reduce data redundancy
What is a Relational Database?
A Relational Database is a Database which stores information in Relations/Tables. Each Relation will consist of Records/Rows and Attributes/Columns.
What is SQL?
SQL is Structured Query Language, a language for communicating with Relational Databases.
What is a Primary Key?
A Primary Key is a unique identifier for each Record in a Relation.
What is a Foreign Key?
A Foreign Key is an identifier for a Record in another Relation.
What is a Composite Key?
A Composite Key is a Key (Primary or Foreign) which consists of multiple Attrributes.
What is First Normal Form?
A Relation is in First Normal Form if it has no repeating Attribute or group of Attributes.
What is Second Normal Form?
A Relation is in Second Normal Form if it is in First Normal Form and there are no Partial Key Dependencies.
A Partial Key Dependency is when an Attribute is dependent upon only part of a Composite Key.
What is Third Normal Form?
A Relation is in Third Normal Form if it is in Second Normal Form and there are no Non-Key Dependencies.
A Non-Key Dependency is when an Attribute is dependent upon another Attribute which is not part of the Primary Key.
Why do Databases implement Record Locks?
Record Locks prevent multiple users from changing the same Record in a Relation at the same time which can lead to inconsistent results or corrupt data.
What is DeadLock?
DeadLock is when cyclical dependency appears between the Record Locks that users have created whilst interacting with a Database. For example user A may have locked Table 1 whilst waiting for user B to unlock Table 2, whilst user B is waiting for Table 1 to be unlocked to continue.
What is an entity
Any real world object or conceptual object defined inside a database
What is a record
A collection of data for one object / person or thing. (This is usually the row for visual reference)
What is a field
Used to provide a database with category headings for each item
What is a primary key
A field in a database that acts as a unique identifier for each record
What is a composite primary key
The combination of 2 attributes which are unique identifiers for the record.
What is referential integrity
When a record is added to a table and a value is entered into a foreign key field/ the value exists in the primary key field of the related table. That forms a relationship between the two tables.
What is Data integrity
The accuracy and reliability of data
What is data inconsistency
Data that isn’t updated correctly or updated at all leads to data becoming inconsistent and lacks integrity
What is data redundacy
Unnecessary data repetition
What is a DBMS
a software package that allows the database administrator to maintain one or more relational databases. Some provide user interface.
What are the advantages of relational databases
Data is more consistent and avoids redundant data. This also means that chance of errors being inputted are less likely.
What is normalization (databases)
a technique used to help reduce data duplication when designing data structures/ also resulting in an improvement in data integrity.
What is SQL?
Structured Query Language
Give an example of The command CREATE TABLE with 3 fields ( name/ species/ PetID)
CREATE TABLE tbAnimals (PRIMARY PetID VARCHAR(10)/ species VARCHAR(10)/ species VARCHAR(10));
Use a select statement to select all names in alphabetic order where age
SELECT tblAnimals.name/From tblAnimals/ORDER BY tblAnimals.name ASC;
Use an insert into statement to insert a new dog called Barry with the Pet id of 2A
INSERT INTO tblAnimals(PetID/ name/ species)VALUES (“2A”/ “Barry”/ “Dog”);/Use an UPDATE statement to change a a dog called Barry’s pet id to 2D/UPDATE PetID/SET PetID = “2D”/WHERE name = “Barry”;
Use a delete statement to delete Barry the dog (2D) from the database
DELETE FROM tblAnimals/WHERE PetID = “2D”;
Use a drop statement to Drop the table tblAnimals
DROP TABLE tblAnimals;
Use an ALTER STATEMENT to add a new field/ OwnerName Varchar
ALTER TABLE tblAnimals ADD OwnerName VARCHAR(10);