Database Design and Development

Question 1

What are End-User Requirements in databases?

Answer 1

End-User Requirements are the things that the end-users want the database to be able to do.

Question 2

What are Functional Requirements in databases?

Answer 2

Functional Requirements are processes that a database must be able to carry out.

eg. the fields that the database must have.

Question 3

What are databases made of?

Answer 3

Databases are made of entities and attributes.

Question 4

Entities become tables during implementation. What are entities?

Answer 4

Entities are:
- a person
- a place
- an object
- a thing

Question 5

Attributes become fields during implementation. What are attributes?

Answer 5

Attributes are characteristics of entities. Examples of attributes are:
- firstName
- lastName
- address etc.

Question 6

What are Primary Keys (PK)?

Answer 6

Primary Keys are unique identifiers of entities.

Question 7

What are Foreign Keys (FK)?

Answer 7

Foreign Keys are what links two or more tables together.

Question 8

What is it necessary to show in databases?

Answer 8

Relationships between tables

Question 9

What do data dictionaries do?

Answer 9

Data dictionaries define the structure of the database.

Question 10

What do data dictionaries hold information on?

Answer 10

Data Dictionaries contain;

• Names of entities
• Names of attributes
• Data types of attributes
• Size of attributes
• Indication of primary keys or foreign keys

Question 11

What are the field types and what do they hold?

Answer 11

Text - holds any character/number
Number - holds only numbers (but not telephone numbers)
Boolean - True/False
Date - stores dates
Time - stores times

Question 12

What are the four types of input validation?

Answer 12

• Presence Check
• Restricted Choice
• Field Length
• Range Check

Question 13

What is a Presence Check?

Answer 13

Presence Checks are pieces of validation which make any data in a field compulsory.

Question 14

What is a Restricted Choice?

Answer 14

Restricted Choice limits the amount options a user can input thus cutting down the number of execution errors.

Question 15

What is Field Length?

Answer 15

Field Length restricts the amount of characters a field can hold.

Question 16

What is Range Check?

Answer 16

Range makes sure that the numbers inputted are between two quantities.

Question 17

What do we state when making queries at this level?

Answer 17

Field(s)
Table(s)
Sort Order
Criteria

Question 18

What does SQL stand for?

Answer 18

Structured Query Language

Question 19

In SQL, what is used to mean “all”?

Answer 19

• (asterisk)

Question 20

What command is used to search a database for information generally?

Answer 20

SELECT field(s) FROM table(s);

eg SELECT firstName FROM pupils

Question 21

What command is used to search a database for more refined information?

Answer 21

SELECT field(s) FROM table(s)
WHERE field = description;

eg. SELECT firstName FROM pupils
WHERE firstName = “Darren”;

Question 22

What syntax is used to search a database of more than one condition?

Answer 22

AND / OR / NOT

eg. SELECT firstName FROM pupils
WHERE firstName = “Darren” OR firstName = “Ian”;

Question 23

What syntax is used to search a database of numbers between two extremes?

Answer 23

< / > / = / <= / >=

eg. SELECT firstName FROM pupils
WHERE age > 9

Question 24

What are the two ways of ordering fields in a database and what is their syntax in SQL?

Answer 24

Ascending / ASC (Orders a field from lowest to highest)
Descending / DESC (Orders a field from highest to lowest)

Question 25

What command is used to order a database?

Answer 25

SELECT field(s) FROM table
ORDER BY field (ASC / DESC)

eg. SELECT firstName FROM pupils
ORDER BY firstName ASC

Question 26

What is the maximum number of fields a database can be ordered by?

Answer 26

2

Question 27

What command is used to add information to a database?

Answer 27

INSERT INTO table
VALUES (new values in order with commas separating them)

eg. INSERT INTO pupil
VALUES (077868, Darren, O’Hare, Maths, 15)

Question 28

What command is used to add information to a database if we only want to add information to specific fields?

Answer 28

INSERT INTO table
VALUES (new value, new value, new value)

eg. INSERT INTO pupil
VALUES (077868, Darren, Maths)

Question 29

What command is used to update incorrect information of a database?

Answer 29

UPDATE table
SET field = “new value”
WHERE field = “old value”

eg. UPDATE pupil
SET firstName = “Adam”
WHERE firstName = “Darren”

Question 30

In the Testing phase of Database Design and Development, what are the two thing we can test?

Answer 30

Fitness for Purpose
Query Design and SQL versus result

Question 31

Why is it important to make an “expected result” when testing a databases’ Query Design and SQL versus result?

Answer 31

To compare the results to try and achieve the same result, if not there may be problems with the query design and/or implementation.

Question 32

What phase of the Database Design and Development would be revisited if the Testing didn’t match with the expected result?

Answer 32

The Design Phase followed by the Implementation Phase.

Question 33

What be the definition of a database which meets the end-user and functional requirements?

Answer 33

Fit for Purpose