Unit 7 Relational databases and SQL

Question 1

When are databases used?

Answer 1

• Databases are used in many business situations
• They are also used at the back-end of many cloud services

Question 2

Explain some places where databases are used

Answer 2

School use:
- learning platforms such as Google Classroom
- management information systems such as SIMS or iSAMS

Organisation and business use:
- customer relationship management (CRM) software
- online calendars such as Google calendar
- the backend of most website to dynamically generated pages

Personal use:
- social media data
- multiplayer games

Question 3

What is a database?

Answer 3

• A database is a structured way to store data so that it can be retrieved using queries
• Contains one or more tables

Question 4

Data types

Answer 4

Just as when programming variables, database fields need to have a data type. The possible database data types to choose from are:
- Integer (whole number)
- Real, float, decimal (number with a decimal component)
- Data, Time, Datetime (to store dates and times)
- Char (fixed length string up to 8,000 characters)
- Varchar (variable length string up to 8,000 characters)
- Text (variable length string up to 2 GB of data)

Question 5

Flat file databases

Answer 5

• A flat file database stores a single table of data inside a single text file
• Flat file databases are often stored using a CSV (comma separated values) format
• Each record appears on a separate line, each field is separated by a comma
• This format is very easy to set up, however, it is hard to manage for anything but the simplest of data sets

Question 6

What is a primary key?

Answer 6

A field that stores unique data for each record in a table

Question 7

Relational databases

Answer 7

• A relational database contains multiple tables that are linked together using relationships
• Relational databases allow us to design tables that reduce inconsistencies and eliminate data redundancy

Question 8

What is data redundancy?

Answer 8

When there is unnecessary data repetition in a database

Question 9

What is a foreign key?

Answer 9

A field in a table that references the primary key of another table

Question 10

What are the three possible relationship between tables in a database?

Answer 10

Relationships between tables can be:
- one-to-one
- many-to-many
- one-to-many

Question 11

What does SQL stand for?

Answer 11

Structured Query Language

Question 12

What is SQL?

Answer 12

• A language which allows you to create, query, update and delete data to and from databases
• SQL is used with most Database Management Systems, including MySQL, SQL Server and MS Access
• SQL queries can be used within high level programming languages such as Python

Question 13

Writing a query using SQL

Answer 13

The SQL syntax for querying a database is:
SELECT… list the fields to be displayed
FROM… specify the table name
WHERE… list the search criteria

Question 14

What are the benefits of using relational databases?

Answer 14

They eliminate data redundancy and inconsistency

Question 15

What are the benefits of using relational databases?

Answer 15

They eliminate data redundancy and inconsistency

Question 16

What is SQL?

Answer 16

A language used for querying and updating database tables in a relational database

Question 17

When is the SELECT statement used?

Answer 17

To extract a collection of fields from one or more tables in a database

Question 18

What is the syntax of the SELECT statement?

Answer 18

SELECT list of fields to be displayed
FROM list the table or tables where the data will come from
WHERE list of search criteria

Question 19

ORDER BY

Answer 19

list the fields that the results are to be sorted on (either ASC or DESC)

Question 20

What is the default setting for ORDER BY?

Answer 20

Ascending

Question 21

What does the INSERT INTO statement do?

Answer 21

Used to insert a new record into a database table

Question 22

What is the syntax for the INSERT INTO statement?

Answer 22

INSERT INTO tableName (column1, column2,…)
VALUES (value1, value2,…)

Question 23

What does the DELETE statement do?

Answer 23

Used to delete a record from a database table

Question 24

What is the syntax for the DELETE statement?

Answer 24

DELETE FROM tableName
WHERE column = value

Question 25

What does the UPDATE statement do?

Answer 25

Used to amend a record in a database table

Question 26

What is the syntax for the UPDATE statement?

Answer 26

UPDATE tableName
SET column1 = value1, column2 = value2, …
WHERE column = value

Question 27

What problems can data redundancy cause?

Answer 27

• An unnecessary amount of data storage capacity required to store the duplicated data
• An increased risk of having inaccurate data
• It is more time consuming and problematic to update the database