unit 7 - relational databases and SQL Flashcards
what are some examples of organisations that use databases?
- schools - google classroom
- businesses - google calendar
- personal use - social media data, multiplayer games
what is a database?
a structured way to store data so that it can be retrieved using queries
what is a table?
it stores records and fields in an organized manner
what is a record?
an individual collection of data for one person/object, a row in a table
what is a field?
one item of data, a column in a table
what is the date, time variable used for?
to store dates and times eg. DOB (date), time a message was sent (datetime)
what is a varchar?
- variable length string (can contain letters, numbers, and special characters)
- up to 8000 characters) eg. first name or telephone number
what is a flat-file database?
stores a single table of data inside a single text file
- often stored using a CSV (comma separated values) format
- each record appears on a separate line
- each field is separated by a comma
what are the advanatges and disadvantages of flatfile databases?
+ easy to set up
- hard to manage for anything but the simplest of data sets
what are some issues with flatfile databases and how can these be solved?
data often has to be repeated, leading to:
- inconsistencies in the data - makes it hard to search or sort the data
- causes redundant data - the databases use more memory or storage than it needs to + it may take longer to search
can be solved by using relational databases
what is a primary key?
a field that stores unique data for each record in a table
- first names, last names and DOBs are not unique
- a mobile number (varchar) is unique so can used as a primary key, or an ID number (int or auto-number)
what is a relational database? and what are the advnatges of these?
- contains multiple tables and each table has links known as relationships
- each table is known as a relation
+ allows us to design tables that reduce inconsistencies and eliminate data redundancy
what is a foreign key?
a field in a table that references the primary key of another table
- each table has a primary key which is used to uniquely identify each record
- a second table can refer to the info in the first table by referring to its primary key but in this case it is referred to as a foreign key
what are the three different relationship types in databases?
- one-to-one
- many-to-many
- one-to-many
what is SQL?
structured query language - a language that allows you to create, query, update and delete data to and from databases
- used in DBMS
what does the SELECT statement do?
list the fields for the data to be displayed
what does the FROM statement do?
specify the table name that you are querying
what does the WHERE statement do
list the search criteria - like conditions for what you’re querying for
- operators =, ≠, >,<, ≥, ≤, AND, OR, NOT can be used in this statement
how do you select all the fields of a table?
SELECT *
FROM members
what does the BETWEEN statement do?
conditional like the WHERE statement but gives a range instead
what does the LIKE statement do?
searches for a pattern
eg. SELECT * FROM members WHERE surname LIKE ‘H*’
how do you update records?
- data stored in records can be changed + more than one record can be changed at a time
- UPDATE statement -> UPDATE Dogs SET Age = 4 WHERE Age = 3
how do delete records?
- the WHERE criteria allows more than one record to be deleted at a time
- if using tables with relationships that these aren’t affected by deletion
- use the DELETE FROM …. WHERE eg. DELETE FROM Dogs WHERE Breed = ‘Labrador’ AND Colour = ‘Brown’
how do you join two tables?
a query can be made where info is selected from two tables
