Chapter 8

Question 1

What is a database?

Answer 1

Database is a structured collection of data that can be accessed by different applications programs. It allows efficient storage, retrieval and cross checking of data.
Data is structured as a collection of records. Each record is made up of fields, containing data about the same thing.

Question 2

Flat Files:

Answer 2

Originally all data was stored in one table in a file.

o Very large files in size
o Difficult to process
o Difficult to modify structure (e.g. add a field)

Question 3

Limitations of a file-based approach to store data:

Answer 3

Data redundancy // data is repeated in more than one file.
Data dependency // changes to data means changes to programs accessing that data.
Lack of data integrity // entries that should be the same can be different in different places.
Lack of data privacy // all users have access to all data if a single flat file.

Question 4

Database structure:

Answer 4

A database contains one or more tables.

Data is stored in rows and columns;
Each row of the table is known as record.
Each column (data item) of the table is known as field.

Question 5

What is an alternative to flat files?

Answer 5

RELATIONAL DATABASES; a database in which the

data items are linked by internal pointers.

Question 6

What data types are used in a database?

Answer 6

A field only has 1 datatype.
Data type can be text, alphanumeric, numeric, boolean,
date/time.

Question 7

Relational databases:

Answer 7

Relational Databases use two or more tables linked
together (to form a relationship) and do not store all the data in the same table.

Data is stored in Tables called ENTITIES.
Each record is called a TUPLE (row).
A data item is called an ATTRIBUTE (column).
Each record has a unique primary key field.
Primary key in one entity is the foreign key in another entity.

Question 8

When is a relationship formed in a relational database?

Answer 8

It is an association between entities.
A relationship is formed when our two tables are joined together.
A relationship is formed when one table in a database has a foreign key that refers to a primary key in another table in the database.
Relationships may be mandatory(I I) or optional(o).

Question 9

E-R diagrams (entity relationship):

Answer 9

1. one-to-one (1:1)
2. one-to-many (1:M)
3. many-to-many (M:N)

!Many-to-many relationships should not be present when designing a relational database as they can lead to unnecessary storage of the same data more than once (duplicated) and can make it difficult to avoid errors such as update, deletion or insertion anomalies!.

Question 10

What is a primary key?

Answer 10

Uniquely identifies each row of the entity/table.
Can be used as a foreign key in another table to form a
link.
The attribute must always contain a value for the record to be inserted into the table.

Question 11

What is a secondary key?

Answer 11

An attribute which is used to access the data in some way other than by using the primary key.
The secondary key is not the primary key in another table.

Question 12

What is a foreign key?

Answer 12

A set of attributes that refer to the primary key in another table.
The means by which the tables are linked together and
relationships are formed.

Question 13

Referential integrity:

Answer 13

Makes sure that tables do not try to reference data which does not exist (a value of one attribute of a table exists as a value of another attribute in a different table).
Every foreign key value has a matching value in the corresponding primary key.
A primary key cannot be deleted unless all dependent records are already deleted → Cascading delete.
A primary key cannot be updated unless all dependent records are already updated → Cascading update / edit.

Question 14

Resolving many-to-many:

Answer 14

It is necessary to create a third entity. The new table/entity will have two one-to-many relationships.

Question 15

E-R diagram cardinality:

Answer 15

The type of relationship and whether it is mandatory or optional gives the cardinality.

Question 16

What is database normalisation?

Answer 16

Database Normalisation is a technique of organizing the data in the database.
o Eliminating redundant (useless) data.
o Ensuring data dependencies make sense i.e data is logically stored.

Question 17

The 3 steps of normalisation:

Answer 17

First Normal Form – 1NF
o No repeated groups of attributes (All attributes should be atomic).

▪ Second Normal Form – 2NF (in 1NF and)
o Entities are in 1NF and there are no partial dependencies (Any non-key attributes depend upon the primary key).

▪ Third Normal Form – 3NF (in 2NF and)
o Entities are in 2NF and non-key attributes are independent (in other words: No non-key dependencies).

Question 18

How has relational data overcome problems with a field-based approach?

Answer 18

▪ By storing data in (separate) linked tables data redundancy is reduced.
▪ Compatibility / data integrity issues are reduced as data only needs to be updated once / is only stored once.
▪ Unwanted or accidental deletion of linked data is prevented as the DBMS will flag an error.
▪ Program - data dependence is overcome.
▪ Changes made to the structure of the data have little effect on existing programs.
▪ Ad-hoc / complex queries can be more easily made as the DBMS will have a query language/ QBE form.
▪ Unproductive maintenance is eliminated as changes only need to be made once (rather than changing multiple programs).
▪ Fields can be added or removed without any effect on existing programs (that do not use these fields).
▪ Security / privacy of the data is improved as each application only has access to the fields it needs.
▪ There is better control of data integrity as the DBMS (uses its Data Dictionary) to perform validation checks on data entered.

Question 19

DBMS

Answer 19

DataBase Management System
Improves the integrity of data stored, helping to ensure that it is accurate, complete and consistent.

Systems software for the definition creation and manipulation of a database.
Uses Data Management (the organisation and maintenance of data in a database to provide the information required), Data Dictionary (stores metadata about data for a database) and Data Modelling (to show the data structure of a database).

Question 20

DBMS security measures:

Answer 20

▪ Username & passwords.
▪ Access rights to manage the parts of the database.
▪ Access rights to manage the actions authorised users can take (read/write/delete, or read only, or append only).
▪ Automatic creation and scheduling of regular back-ups.
▪ Encryption of data stored.
▪ Automatic creation of an audit trail or activity log to record the actions taken by users of the database.

Question 21

Developer interface:

Answer 21

o Creates a table.
o Sets up relationships between tables.
o Creates a form.
o Creates a report.
o Allows to create queries in SQL (rather than using query-by-example).

Question 22

Query processor:

Answer 22

o Allows the user to enter criteria.
o Searches for data which meets the entered criteria.
o Organises the results to be displayed to the user.
o Includes DDL interpreter, DML compiler and query evaluation engine.

Question 23

Queries:

Answer 23

To store and filter data to find specific information.

Use query languages or graphical methods to interrogate the data.

Question 24

Query Language:

Answer 24

Used only to write specific queries.
User can define precisely what is required in a database.
SQL is a popular query language.

Question 25

DATA DEFINITION LANGUAGE

DDL

Answer 25

▪ Used to create, modify and remove the data structures that form a relational database.
▪ These commands only create the relational database structure. They do not put any data into the database.
▪ Use of SQL.

CREATE DATABASE
CREATE TABLE 
ALTER TABLE 
PRIMARY KEY
FOREIGN KEY…REFERENCES…

Question 26

DATA MANIPULATION LANGUAGE

DML

Answer 26

▪Used to add, modify, delete and retrieve the data stored in a relational database.
▪ Used to work with the data stored in the relational database.
▪ Use of SQL.

SELECT FROM
WHERE
ORDER BY
GROUP BY
INNER JOIN
SUM
COUNT
AVG

INSERT INTO
DELETE FROM
UPDATE

Question 27

SQL data types:

Answer 27

CHARACTER 
VARCHAR (n) 
BOOLEAN; SQL uses the integers 1 and 0
INTEGER 
REAL 
DATE; usually formatted as YYYY-MM-DD
TIME

Question 28

Advantages of an electronic database (compared to a paper-based):

Answer 28

▪ Data can be easily sorted in a specific way (e.g.: alphabetically, or from highest to lowest).
▪ Data can be found quickly.
▪ Data can be filtered to retrieve only data we require.
▪ Data can be easily shared over the Internet (or portable device).
▪ Data can be validated to make sure is realistic and possible.
▪ A backup copy of the database can be easily made.
▪ It saves space.

Question 29

SQL (DML) to edit a field:

Answer 29

UPDATE (table name)
SET (new data to be put in field)
WHERE (to be updated/previous data from field) ;

Question 30

SQL (DML) to delete ALL rows/records from a table:

Answer 30

DELETE FROM (table name)

Question 31

SQL (DML) to delete A row/record from a table:

Answer 31

DELETE FROM (table name)
WHERE (criteria) ;

Question 32

SQL (DML) to input new row(s) to a table:

Answer 32

INSERT INTO ‘table name’ (field(s))

VALUES (data to be inserted) ;

Question 33

SQL (DML) to count/find the average/sum:

Answer 33

SELECT COUNT(field name)
FROM table name;

SELECT AVG()
FROM

SELECTSUM()
FROM

Question 34

SQL (DML) when 2 tables are inner joined:

Answer 34

SELECT STUDENT.FirstName, STUDENT.LastName, STUDENT-QUALIFICATION.QualCode

FROM STUDENT, STUDENT-QUALIFICATION

WHERE STUDENT-QUALIFICATION.Grade = ‘A’ AND STUDENT.StudentID = STUDENT QUALIFICATION.StudentID;

Question 35

SQL (DML) to group or order by a specific field:

Answer 35

SELECT NAME, SUM(SALARY)
FROM CUSTOMERS
GROUP BY NAME;

SELECT *
FROM CUSTOMERS
ORDER BYNAME, SALARY;

SELECT *
FROM CUSTOMERS
ORDER BY NAME DESC;

(* means that it is referring to all the fields)
(Desc - descending/ Asc - ascending)

Question 36

SQL (DDL) alter a table (add a field):

Answer 36

ALTER TABLE USER

ADD Country VARCHAR;

Question 37

SQL (DDL) to create a table:

Answer 37

CREATE TABLE Programmes (
                ID INT NOT NULL,
                Title VARCHAR(20),
                Genre VARCHAR(20),
                Duration INT,
                PRIMARY KEY (ID)
);

Question 38

SQL (DDL) to create a database:

Answer 38

CREATE DATABASE BBCShows;

Question 39

SQL (DDL) to add a primary and foreign key:

Answer 39

CREATE DATABASE BandBooking; 
CREATE TABLE Band ( 
               BandName VARCHAR(25), 
               NumberOfMembers INT
); 
ALTER TABLE Band ADD PRIMARY KEY(BandName) ; ALTER TABLE Band-Booking ADD FOREIGN KEY BandName REFERENCES Band (BandName);