1.3.2 Databases Flashcards
What is a relational database?
A database where separate tables are made for each entity and relationships between entities are represented by foreign keys
What is an attribute?
A characteric of an entity, it is also the column of a database
What is a flat-file database?
A data base where a single table data structure is used to store all the data
What is a primary key?
A primary key is a unique identifier for each record in the table.
What is a record?
A record is a row in a data base, typically about one entity
How do we show which attribute is the primary key?
By underlining it
What is a foreign key?
A foreign key is the attribute which links two tables together.
The foreign key will exist in one table as the primary key and act as the foreign key in another.
How do we show which attribute is the foreign key?
By using an asterick
What is a secondary key?
A key that can be used as an alternative index to access or sort records in the table in a quicker, but less accurate way than the primary key
What is entity-relationship modelling?
A mthod of abstractly describing the data tables and the relationships between them visually.
They can be used to reduce redundacy and construct a relational database
What is a one-to-one entity relationship?
Each entity can only be linked to one other entity, such as the relationship between a husband and wife.
The husband entity can only be associated with one wife entity and vice versa.
What is a one-to-many entity relationship?
One table can be associated with many other tables, such as a mother having multiple children.
Similarly, multiple child entities can be linked to the same mother entity.
What is a many-to-many entity relationship?
One entity can be associated with many other entities and the same applies the other way round.
An example is students and courses - each student can enrol in more than one course and each course can have more than one student
What is normalisation? (databases)
Normalisation tries to accomplish the following things:
● No redundancy (unnecessary duplicates).
● Consistent data throughout linked tables.
● Records can be added and removed without issues.
● Complex queries can be carried out.
What is indexing?
The process of creating an index of primary keys such that the location of any record can be retrieved given its primary key
What is referential integrity?
The idea of keeping a database consistent by ensuring that any changes made to data or relationsips associated with a table are accounted for in all the linked tables
What are 3 benefits of electronic databases?
Easier to retrieve, add, delete, update and modify data
Easier to back up and make copies of data
Can be accessed by, multiple people at the same time
How can tou capture data?
Paper base forms
OCR
OMR
Explain capturing data using paper based forms?
Data input via this method is manual. It involves a human reading the form and typing the infomation into a computer based system.
How can you try avoid errors when using paper based forms to capture data? (5 points)
Every part of the form is clearly labelled
Instructions to complete the form in black pen
Instructions to complete the form in capitial letters
Use of tick boxes
Squares for entering each letter separately
Explain capturing data using OCR?
To help speed up and automate data input, you can use optical character recognition.
This automatically reads text by interpreting the shape of the letters, its works better with printed text than handwriting.
What does OCR stand for?
Optical character recognition
What are 2 examples of OCR being used?
The post offcie uses OCR software to read postcodes and route mail
Road cameras use automatic number plate recognition software to handle congestion charging and identify drivers who are speeding
What does OMR stand for?
Optical mark recognition
Explain capturing data using OMR?
This is often used for multiple choice tests and lottery tickets.
It is very fast and efficent way of collecting data and inputing it in a databse while signifcantly reducing the possibility of human error
What are some examples of OMR?
Magnetic stripes
Chips and pin
Barcodes
QR codes
Sensors
What is SQL?
A common query langague for all databases is the structured query language (SQL). It allows for retrieval of data using commands like SELECT, FROM and WHERE.
What is QBE?
Developed alongside sql, QBE was the first graphical query language making use of visual tables where the user would enter commands and conditions.
Once a query is built using QBE, it is converted into statements that can be excuted agaisnt the database.
What is a pro of using QBE over SQl?
By ultising QBE, the user doesnt need to remember the finer details of SQL syntax
What are the similarities between SQL and QBE?
Specify tables
Specify fields
Specify critirea
Specify output sorting
Use boolean expressions
What is DBMS?
Database management system
What does DBMS provide?
It hides the underlying structure of the data and ensures it remains integral by:
Preventing the creation of duplicate primary keys
Enforcing validation keys
Providing secure access
Providing encryption
Providing program data independence
Managing multiple users
Describe CSV ( comma seperated values)
Each record is stored on a seperate line in the filem and each field is seperated by a comma
As the structure is fixed and known, import routines can be written to extract the data from a CSV file
Many systems allow data to be output in CSV format
What are similairities between JSON and XML?
Both are human-readable, open formats for structuring data
Both common standards for storing and transporting data
What are some manual ways to exchnage data?
Memory stick
Optical media
Email
Removable hard disk
Paper based
What are the 12 SQL commands?
SELECT
FROM
AND OR
WHERE
LIKE
JOIN ON
DELETE
INSERT
DROP
ORDER BY
UPDATE
CREATE
Explain the select command
SELECT (attributes you want)
Seperate attributes by commas
* means all attributes
Explain the from command
FROM (table name)
Explain the where command
WHERE (condition)
Explain the like command
LIKE
LIKE ‘J%a’ means words starting with J and ending with a
Explain the and / or commands
AND ( add to your condition)
OR ( add to your condition)
Explain the delete command
DELETE FROM <table name>
WHERE <field> = <condition></condition></field>
Explain the insert command
INSERT INTO <table name> (<field1>,<field2>,<.....>)
VALUES (<value1>,<value2>,<......>)</value2></value1></field2></field1>
Explain the drop command
DROP TABLE <table name>
Used to delete an existing table in a database
Explain the join command
JOIN <table name>
ON <table name>, <field> = <table name>, <field>
Can be used to combine data from two or more tables by specifying common field between them</field></field>
Explain the order by command
ORDER BY ( ASC or DSC)
Default is ascending
Explain the update command
UPDATE <table name>
SET <field> = <value>, <.....>, <.....>,
WHERE <field> = <condition></condition></field></value></field>
Explain the create command?
CREATE TABLE <table name>
(
<attribute 1> <data> <primary>,
<attritube 2> <data> ,
...
)</data></primary></data>
What details must be specified abput each attribute when creating a table?
Whether it is the primary key
Its data type
Whether it must be filled in (‘Not Null’)
What are the possible data types of a database?
CHAR(n): this is a string of fixed length n VARCHAR(n): this is a string of variable length with upper limit n
BOOLEAN: TRUE or FALSE values INTEGER/INT: integer
FLOAT: number with a floating decimal point DATE: the date in the format Day/Month/Year TIME: the time in the format Hour/Minute/Second
CURRENCY: sets the number as a monetary amount
