10 - Fundamentals of Databases

Question 1

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?

Answer 1

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

Question 2

State the properties that the relations in a fully normalised database must have.

Answer 2

• 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.

Question 3

What is the name given to a key that is made up of multiple attributes?

Answer 3

Composite key

Question 4

Why is it important for a database to be in third normal form?

Answer 4

• Eliminate update anomalies
• Eliminate insertion anomalies
• Eliminate deletion anomalies
• Reduce data redundancy

Question 5

What is a Relational Database?

Answer 5

A Relational Database is a Database which stores information in Relations/Tables. Each Relation will consist of Records/Rows and Attributes/Columns.

Question 6

What is SQL?

Answer 6

SQL is Structured Query Language, a language for communicating with Relational Databases.

Question 7

What is a Primary Key?

Answer 7

A Primary Key is a unique identifier for each Record in a Relation.

Question 8

What is a Foreign Key?

Answer 8

A Foreign Key is an identifier for a Record in another Relation.

Question 9

What is a Composite Key?

Answer 9

A Composite Key is a Key (Primary or Foreign) which consists of multiple Attrributes.

Question 10

What is First Normal Form?

Answer 10

A Relation is in First Normal Form if it has no repeating Attribute or group of Attributes.

Question 11

What is Second Normal Form?

Answer 11

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.

Question 12

What is Third Normal Form?

Answer 12

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.

Question 13

Why do Databases implement Record Locks?

Answer 13

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.

Question 14

What is DeadLock?

Answer 14

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.

Question 15

What is an entity

Answer 15

Any real world object or conceptual object defined inside a database

Question 16

What is a record

Answer 16

A collection of data for one object / person or thing. (This is usually the row for visual reference)

Question 17

What is a field

Answer 17

Used to provide a database with category headings for each item

Question 18

What is a primary key

Answer 18

A field in a database that acts as a unique identifier for each record

Question 19

What is a composite primary key

Answer 19

The combination of 2 attributes which are unique identifiers for the record.

Question 20

What is referential integrity

Answer 20

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.

Question 21

What is Data integrity

Answer 21

The accuracy and reliability of data

Question 22

What is data inconsistency

Answer 22

Data that isn’t updated correctly or updated at all leads to data becoming inconsistent and lacks integrity

Question 23

What is data redundacy

Answer 23

Unnecessary data repetition

Question 24

What is a DBMS

Answer 24

a software package that allows the database administrator to maintain one or more relational databases. Some provide user interface.

Question 25

What are the advantages of relational databases

Answer 25

Data is more consistent and avoids redundant data. This also means that chance of errors being inputted are less likely.

Question 26

What is normalization (databases)

Answer 26

a technique used to help reduce data duplication when designing data structures/ also resulting in an improvement in data integrity.

Question 27

What is SQL?

Answer 27

Structured Query Language

Question 28

Give an example of The command CREATE TABLE with 3 fields ( name/ species/ PetID)

Answer 28

CREATE TABLE tbAnimals (PRIMARY PetID VARCHAR(10)/ species VARCHAR(10)/ species VARCHAR(10));

Question 29

Use a select statement to select all names in alphabetic order where age

Answer 29

SELECT tblAnimals.name/From tblAnimals/ORDER BY tblAnimals.name ASC;

Question 30

Use an insert into statement to insert a new dog called Barry with the Pet id of 2A

Answer 30

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”;

Question 31

Use a delete statement to delete Barry the dog (2D) from the database

Answer 31

DELETE FROM tblAnimals/WHERE PetID = “2D”;

Question 32

Use a drop statement to Drop the table tblAnimals

Answer 32

DROP TABLE tblAnimals;

Question 33

Use an ALTER STATEMENT to add a new field/ OwnerName Varchar

Answer 33

ALTER TABLE tblAnimals ADD OwnerName VARCHAR(10);