Databases Design and Development Flashcards
End User Requirements
The requirements are what the users expect to be able to do using the database.
Functional Requirements
Processes that the database system has to be able to do.
What does ER stand for in ER diagrams
Entity Relationship
What are ER diagrams used for ?
Used to represent the relationship between tables.
One-to-Many Cardinality
When one table can be present in many other tables.
You also need a verb to describe the relationship.
3 types of validations in a database
- Length Check
- Range Check
- Restricted Choice
- Presence Check
- Lookup
Presence Check
When a field cannot be left empty, the primary key must have a presence check
Length Check
Set number of characters that can be entered.
Eg; phoneNumber = 11
Restricted Choice
Can only choose a limited selection of options
Eg Countries In The UK
Range Check
Numbers only
When the number needs to be within a range
Eg HotelRating = 1 - 5
What is Referential Intergrity ?
Whenever a Foreign Key is used, it must reference a valid and existing Primary Key in the first table.
Can be used to cascade changes made to the database.
GDPR ensures the data held must be:
Must be accurate Held Securely Not kept longer than necessary Processed lawfully Used for declared purpose only
Search a record in SQL
SELECT
FROM
WHERE
Sorting a record in SQL
SELECT
FROM
WHERE
ORDER BY (DESC or ASC)
Why are two tables created ?
To avoid data duplication and anomalies
How are two tables linked
By the Foreign Key
Deleting a record in SQL
DELETE FROM
WHERE
Editing a record in SQL
UPDATE
SET
WHERE
Advantages of using a database
Search efficiently
Sort efficiently
Requires less storage
Adding a new record in SQL
INSERT INTO
VALUES
3 types of fields
- Numeric
- Text
- Boolean
- Date
- Time
What is the difference between a flat file and relational database ?
Flat File: One table
Relational Database; Multiple tables linked together.
What is a Foreign Key ?
When the primary key is used in another table
What is the advantage of linking tables ?
Helps to reduce input errors and data inconsistency
Disadvantages of using databases
Viruses
Hackers
