Databases Flashcards
What are end user requirements
End user requirements should clearly state the tasks that users wish to perform.
In a school for example office staff would want to be able to:
Insert new records for new pupils
Update pupil information if they move house
Find the timetable for a specific pupil
Check the attendance record of a pupil
Functional requirements
Functional requirements should relate to the tasks that the database system will perform, usually in the form of searching and sorting (queries).
Functional requirements may also provide detail around the data that must be held in the database.
In a school for example the database would need to be able to:
Store pupil data – name, address, medical information, contact details etc.
Create forms to add data
Produce sorted lists of information
Be queried to find information quickly
GDPR
On the 25th May 2018, the European Union’s General Data Protection Regulation (2016) legislation came into force. The GDPR will apply here until the UK leaves the EU. It is likely that new UK legislation will have to be drawn up after Brexit. The implications for individuals and business of the GDPR legislation means that the data they hold must be:
processed lawfully, fairly and in a transparent manner in relation to individuals
used for the declared purpose only
limited to the data needed for the declared purpose
accurate
not kept for longer than necessary
held securely
Entity and attributes
a database have tables and fields.
These are now known as entities and attributes.
Entities can also have a relationship so that they are linked.
An entity becomes a table and it is used to describe a person or an item.
The attributes become the fields and are the information that is stored about the entity.
Entity and attributes example
School Database
Entity = Pupils
Attributes = firstname, surname, dob, contact, SCN etc
Dentist Database
Entity = Patient
Attributes = firstname, surname, mobile, history, medical, dob, nextAppointment etc.
Relationships
A table in a database holds the attributes for one entity only. Data can be added in the form of records.
Rather than storing information in one table, relational databases store data across several tables.
Using relational databases that link tables using primary and foreign keys helps to avoid issues like insertion, deletion and update anomalies.
In a relational database, it is necessary to define the relationship that exists between different entities.
PK and FK
PK = Primary Key
A PK is used to uniquely identify a record within a database. It is always unique. It cannot be left blank.
FK = Foreign Key
A FK is the PK of another table that appears in the table. It is used to create a link between the tables and ensure data integrity.
Attribute types examples
— text
— number
— date
— time
— Boolean
Validation examples
— presence check
— restricted choice
— field length
— range
What is presence check
Makes the person using the database enter something into this field.They cannot leave it blank. For example, when signing up for a new account on a social media platform, you cannot leave the password field empty
What is restricted choice
Cuts down on mistakes by only letting you select an option from a menu or list.
What is field length
Field length restricts the number of characters typed. This can cut down on the total data requirements of the table.
A typical length check could be used on a National Insurance number field to only allow up to nine characters. This can also stop people from mistyping their NI number by adding in too many characters.
What is range
A range check makes sure data entered is within certain limits. This could be used to make sure the user enters a number ˃=1 and ˂=12 if they were entering their month of birth, or that they enter a number ˃=11 and ˂=16 for the database of school pupils between these ages.
What is a data dictionary
Data dictionaries are created during the design phase to define the structure of a database.
Data dictionaries contain the following:
Entity name
Attributes of the entity
Data type for each attribute
Identify PK and FK
Size of attribute
Validation rules for attributes
