unit 7 - relational databases and SQL

Question 1

what are some examples of organisations that use databases?

Answer 1

• schools - google classroom
• businesses - google calendar
• personal use - social media data, multiplayer games

Question 2

what is a database?

Answer 2

a structured way to store data so that it can be retrieved using queries

Question 3

what is a table?

Answer 3

it stores records and fields in an organized manner

Question 4

what is a record?

Answer 4

an individual collection of data for one person/object, a row in a table

Question 5

what is a field?

Answer 5

one item of data, a column in a table

Question 6

what is the date, time variable used for?

Answer 6

to store dates and times eg. DOB (date), time a message was sent (datetime)

Question 7

what is a varchar?

Answer 7

• variable length string (can contain letters, numbers, and special characters)
• up to 8000 characters) eg. first name or telephone number

Question 8

what is a flat-file database?

Answer 8

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

Question 9

what are the advanatges and disadvantages of flatfile databases?

Answer 9

+ easy to set up

• hard to manage for anything but the simplest of data sets

Question 10

what are some issues with flatfile databases and how can these be solved?

Answer 10

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

Question 11

what is a primary key?

Answer 11

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)

Question 12

what is a relational database? and what are the advnatges of these?

Answer 12

• 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

Question 13

what is a foreign key?

Answer 13

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

Question 14

what are the three different relationship types in databases?

Answer 14

• one-to-one
• many-to-many
• one-to-many

Question 15

what is SQL?

Answer 15

structured query language - a language that allows you to create, query, update and delete data to and from databases
- used in DBMS

Question 16

what does the SELECT statement do?

Answer 16

list the fields for the data to be displayed

Question 17

what does the FROM statement do?

Answer 17

specify the table name that you are querying

Question 18

what does the WHERE statement do

Answer 18

list the search criteria - like conditions for what you’re querying for
- operators =, ≠, >,<, ≥, ≤, AND, OR, NOT can be used in this statement

Question 19

how do you select all the fields of a table?

Answer 19

SELECT *

FROM members

Question 20

what does the BETWEEN statement do?

Answer 20

conditional like the WHERE statement but gives a range instead

Question 21

what does the LIKE statement do?

Answer 21

searches for a pattern
eg. SELECT * FROM members WHERE surname LIKE ‘H*’

Question 22

how do you update records?

Answer 22

• 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

Question 23

how do delete records?

Answer 23

• 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’

Question 24

how do you join two tables?

Answer 24

a query can be made where info is selected from two tables