4 - 17 Relational Databases And Normalisation

Question 1

What is a relational database?

Answer 1

A relational database is a collection of tables in which relationships are modeled by shared attributes.

Question 2

Describe the structure of a table in a relational database

Answer 2

One row of a table holds one record. Each column in the table represents one attribute.

Question 3

Book (BOOKID, DeweyCode, Title, Author, DatePublished)

Which is the entity name, attributes and primary key

Answer 3

The entity name is shown outside the brackets
The attributes are listed inside the brackets
The primary key is normally underlined but in CAPS in this case.

Question 4

What is a primary key?

Answer 4

The primary key is composed of one or more attributes that uniquely identify a particular record in the table. (When describing an entity this is called an entity identifier)

Question 5

How do you link database tables?

Answer 5

Tables may be linked through the use of a common attribute. This attribute must be a primary key of one of the tables, and is known as the foreign key in the second table.

Question 6

What are the three possible types of relationship between entities?

Answer 6

one-to-one, one-to-many and many-to-many.

Question 7

What is Normalisation?

Answer 7

Normalisation is a process used to come up with the best possible design for a relational database.

Question 8

Give the four criteria that a relational database should be

Answer 8

No data is unnecessarily duplicated
Data is consistent throughout the database. Consistency should be an automatic consequence of not holding any duplicated data. This means that anomalies will not arise when data is inserted, amended or deleted.
The structure of each table is flexible enough to allow you to enter as many or as few items as required
The structure should enable a user to make all kinds of complex queries relating data from different tables.

Question 9

Criteria of 1NF

Answer 9

A table in first Normal Form (1NF) if it contains no repeating attribute or groups of attributes.

Question 10

Represent two entities in standard notation which have a many-to-many relationship - Product and Component

Answer 10

Product (PRODUCTID, ProductName, Costprice)

Component (COMPID, CompName, SupplierID, SupplierName)

Question 11

Criteria of 2NF

Answer 11

A table is in second Normal Form (2NF) if it is in 1NF and contains no partial dependencies. 2NF is best described as partial key dependence test.

Question 12

What is a partial dependency?

Answer 12

A partial dependency is where one or more of the attributes depends on only part of the primary key, which can only occur if the primary key is a composite key.

Question 13

Criteria of 3NF

Answer 13

A table is in third Normal Form (3NF) if it is in second normal form and contains no ‘non-key dependencies’.

Question 14

What is a non-key dependency?

Answer 14

A non-key dependency is one where the value of an attribute is determined by the value of another attribute which is not part of the key.

Question 15

What does 3NF mean?

Answer 15

All attributes are dependent on the key, the whole key and nothing but the key.

Question 16

The importance of normalisation- no data redundancy

Answer 16

One of the aims of normalising a database is to remove the possibility of redundant data from any of the tables. Redundant data is data that appears in more than one database table, which can cause inefficiencies and inconsistencies in the data.

Question 17

The importance of normalisation- Maintaining and modifying the database

Answer 17

DATA INTEGRITY is maintained since there is no unnecessary duplication of data. Eg changing data in one table will change the data in every other table where it applies meaning there will be no possible inconsistencies.

Question 18

The importance of normalisation - Faster sorting and searching

Answer 18

Normalisation will produce smaller tables with fewer fields. The results in faster searching, sorting and indexing operations as there is less data involved.

Question 19

The importance of normalisation - Deleting records

Answer 19

A normalised database with correctly defined relationships between tables will not allow records in a table on the ‘one’ side of a one-to-many relationship to be deleted accidentally.