1.3.2 Databases

Question 1

How to achieve first normal form

Answer 1

Data should be Atomic – i.e. cannot be broken down further. Therefore there must be:
There are no columns with repeated or similar data
Each row is unique i.e. it has a primary key
Each field has an unique name
atomic does not mean ‘single word’. It simply means that it would make no sense to reduce the data item any further as it would lose its meaning.
i.e. Chipping Campden School is a valid, atomic school name even though it contains three words

Question 2

How to achieve second normal form

Answer 2

Only applies to tables with a COMPOUND KEY
If there is a single primary key that table is already in 2NF
For tables with a COMPOUND KEY, every non key attribute must depend on the WHOLE KEY.

Question 3

How to achieve third normal form

Answer 3

Attributes must already be in 2NF. There are no non-key attributes that depend on other non key attributes
What this is trying to do is to spot yet another source of redundant data. If the value of an attribute can be obtained by simply making use of another attribute in the table, then it does not need to be there. Loading that attribute into another table and linking to it will make the database smaller.

Question 4

Why is data normalised

Answer 4

The database does not have redundant data, it is smaller in size so less money needs to be spent on storage
Because there is less data to search through, it is much faster to run a query on the data
Because there is no data duplication there is better data integrity and less risk of mistakes.
Because there is no data duplication there is less chance of storing two or more different copies of the data
One change can be made which can instantly be cascaded across any related records.

Question 5

Name the problems with normalisation

Answer 5

You need to be careful with trying to make data atomic. Just because you can split some types of data further, it isn’t always necessarily the correct thing to do. For example, telephone number might contain the code followed by the number 01234 567890. It wouldn’t be sensible to separate out these two items.
You can end up with more tables than an unnormalised database
The more tables and the more complex the database, the slower queries can be to run
It is necessary to assign more relationships to interact with larger numbers of tables
With more tables, setting up queries can become more complex

Question 6

Name the types of keys used in data bases.

Answer 6

The Primary key which is a unique identifier

A foreign key forms a link between entities

A compound key can be formed from two or more entities to make a unique record

Question 7

What does DBMS

Answer 7

Data base management system

Question 8

What is DBMS used for

Answer 8

This is a tool to manage a database

Question 9

What are some of the key features DBMS

Answer 9

A command language that allows you to create, delete and alter the database (data description language or DDL)
A way of documenting all the internal structures that make up the database (data dictionary)
A language to support the manipulation and processing of the data (data manipulation language or DML)
Support the ability to view the database from different viewpoints according to the requirements of the user
Provide some level of security and access control to the data

Question 10

Define Data dictionaries

Answer 10

A ‘data dictionary’ describes the structure and attributes of data ‘items’ to be used within a software application (usually a database).

Question 11

Name the main features of a Data dictionaries

Answer 11

A data dictionary includes the names and descriptions of the entites (tables) and the attributes (fields) contained in each table. It also documents information about the data type, field length and other things such as validation.
The main purpose of the data dictionary is to provide metadata, or information about data. Technically, it is a database about a database.
There is no one set standard in terms of layout or the level of detail to which a data dictionary should be written.
Software development teams need a comprehensive data dictionary to refer to during the development and maintenance of a new database. This is so that they are all working using the same data formats when reading or writing data.

Question 12

Define DML

Answer 12

Data manipulation language

Question 13

What is the use of DML

Answer 13

The language is used in the manipulation of the data such as inserting deleting

Question 14

Name some of the key functions of DML

Answer 14

Insert
Delete
Update
Process data

Question 15

Define DDL

Answer 15

Data description language

Question 16

What is the use of DDL

Answer 16

One of the basic functions of a RDBMS is to provide a method of creating a database from scratch

Question 17

Name some of the key functions of DDL

Answer 17

The language allows tables to be defined in terms of :
Field names
Data type
Data size / length
Validation rules
Default values
Presence check
Auto incrementing requirement
Indexing requirement
Primary key

Question 18

What is the name of the structure used in databases

Answer 18

There are three fundamental views and they are useful to different groups of people. It is called the three-level architecture schema.

Question 19

Name the three types of views

Answer 19

Internal or physical view

Conceptual views

External views

Question 20

How does external view work

Answer 20

This view exposes all the internal details of how the data is stored on the physical computer. For example the data structures used in memory, the nature of the indexes used for searching, what physical devices are used to store the database and so on.
At this level the operating system is interacting with the database.

Question 21

How does Conceptual view work

Answer 21

At this level, there is no concern about the physical nature of the database, but rather the concept behind the database. It shows:
a model of all the information within the database the kind of records it holds the type of authorisation and validation methods used.This is the kind of view the database designer is using to set out the whole database

Question 22

How does External view work

Answer 22

This view sets out what an authorised user can see of the database. It sets out the data they are allowed to see and their authority level to change the records. For example consider a bank database.

Question 23

Define SQL

Answer 23

Structured Query Language

Question 24

What are the main uses of SQL

Answer 24

Some RDBMS may have some custom SQL extensions peculiar to itself but it will support all the standard functionality that ANSI compliant SQL offers.
There are many commercial RDBMS such as Oracle and DB2 and there are free open source RDBMS available such as MySQL

Question 25

Give some examples of SQL

Answer 25

SELECT {fields to be extracted} FROM {table}

SELECT * FROM ‘customers’;

SELECT * FROM customers

WHERE CustID=2;

DELETE FROM ‘customers’ WHERE CustID = 1;