Database Design and Development Flashcards
What are End-User Requirements in databases?
End-User Requirements are the things that the end-users want the database to be able to do.
What are Functional Requirements in databases?
Functional Requirements are processes that a database must be able to carry out.
eg. the fields that the database must have.
What are databases made of?
Databases are made of entities and attributes.
Entities become tables during implementation. What are entities?
Entities are:
- a person
- a place
- an object
- a thing
Attributes become fields during implementation. What are attributes?
Attributes are characteristics of entities. Examples of attributes are:
- firstName
- lastName
- address etc.
What are Primary Keys (PK)?
Primary Keys are unique identifiers of entities.
What are Foreign Keys (FK)?
Foreign Keys are what links two or more tables together.
What is it necessary to show in databases?
Relationships between tables
What do data dictionaries do?
Data dictionaries define the structure of the database.
What do data dictionaries hold information on?
Data Dictionaries contain;
- Names of entities
- Names of attributes
- Data types of attributes
- Size of attributes
- Indication of primary keys or foreign keys
What are the field types and what do they hold?
Text - holds any character/number
Number - holds only numbers (but not telephone numbers)
Boolean - True/False
Date - stores dates
Time - stores times
What are the four types of input validation?
- Presence Check
- Restricted Choice
- Field Length
- Range Check
What is a Presence Check?
Presence Checks are pieces of validation which make any data in a field compulsory.
What is a Restricted Choice?
Restricted Choice limits the amount options a user can input thus cutting down the number of execution errors.
What is Field Length?
Field Length restricts the amount of characters a field can hold.
What is Range Check?
Range makes sure that the numbers inputted are between two quantities.
What do we state when making queries at this level?
Field(s)
Table(s)
Sort Order
Criteria
What does SQL stand for?
Structured Query Language
In SQL, what is used to mean “all”?
- (asterisk)
What command is used to search a database for information generally?
SELECT field(s) FROM table(s);
eg SELECT firstName FROM pupils
What command is used to search a database for more refined information?
SELECT field(s) FROM table(s)
WHERE field = description;
eg. SELECT firstName FROM pupils
WHERE firstName = “Darren”;
What syntax is used to search a database of more than one condition?
AND / OR / NOT
eg. SELECT firstName FROM pupils
WHERE firstName = “Darren” OR firstName = “Ian”;
What syntax is used to search a database of numbers between two extremes?
< / > / = / <= / >=
eg. SELECT firstName FROM pupils
WHERE age > 9
What are the two ways of ordering fields in a database and what is their syntax in SQL?
Ascending / ASC (Orders a field from lowest to highest)
Descending / DESC (Orders a field from highest to lowest)
What command is used to order a database?
SELECT field(s) FROM table
ORDER BY field (ASC / DESC)
eg. SELECT firstName FROM pupils
ORDER BY firstName ASC
What is the maximum number of fields a database can be ordered by?
2
What command is used to add information to a database?
INSERT INTO table
VALUES (new values in order with commas separating them)
eg. INSERT INTO pupil
VALUES (077868, Darren, O’Hare, Maths, 15)
What command is used to add information to a database if we only want to add information to specific fields?
INSERT INTO table
VALUES (new value, new value, new value)
eg. INSERT INTO pupil
VALUES (077868, Darren, Maths)
What command is used to update incorrect information of a database?
UPDATE table
SET field = “new value”
WHERE field = “old value”
eg. UPDATE pupil
SET firstName = “Adam”
WHERE firstName = “Darren”
In the Testing phase of Database Design and Development, what are the two thing we can test?
Fitness for Purpose
Query Design and SQL versus result
Why is it important to make an “expected result” when testing a databases’ Query Design and SQL versus result?
To compare the results to try and achieve the same result, if not there may be problems with the query design and/or implementation.
What phase of the Database Design and Development would be revisited if the Testing didn’t match with the expected result?
The Design Phase followed by the Implementation Phase.
What be the definition of a database which meets the end-user and functional requirements?
Fit for Purpose
