CS8: Relational Databases & SQL

Question 1

What is a database?

Answer 1

A database is a way of organising data in a structured way.

Question 2

What is the difference between a relational database and a flat file database?

Answer 2

A flat file database’s tables have no relations and no keys, whereas a relational database can have connections via primary and foreign keys.

Question 3

What is a table?

Answer 3

A series of records composed in an organised way

Question 4

What is a record?

Answer 4

Usually represented by rows, a record is data that has multiple parts to it. All information on a single user on a database would be a record.

Question 5

What is a field?

Answer 5

Usually represented by columns, fields are a small subsection of data in a record. An example might be a user’s phone number.

Question 6

What are primary and foreign keys?

Answer 6

A primary key is a unique identifier for each record in a table.

A foreign key is a primary key from another table, and is used to refer to a record in another table.

Question 7

What is a database relationship?

Answer 7

A database relationship describes how tables in a relational database connect to one another. They are formed using primary & foreign keys.

Question 8

What does SQL stand for, and what is it used for?

Answer 8

SQL, or structured query language, is a programming language used to find and manipulate data in a database.

Question 9

What are some of the “good-practice” rules of SQL?

Answer 9

Good-practice rules:

1. Write commands in CAPITALS
2. Write tables as tbl[name] (e.g. tblDownloads)
3. Put new commands on a new line

Question 10

How do you query a database?

Answer 10

SELECT which fields you want to see
Where they are FROM
and WHERE certain conditions are met

SELECT * (* = all)
FROM tblRegistry
WHERE date > “01/01/2001”

Question 11

What are the three primary logic operations?

Answer 11

Where condition one is true AND condition two is true
Where condition one is true OR condition two is true
Where field is BETWEEN value one AND value two

WHERE time >= “17:30” AND MemberID < “50”
WHERE time >= “17:30” OR MemberID < “50”
WHERE time BETWEEN “17:30” AND “23:00”

Question 12

When working with multiple tables in a database, how do you refer to fields?

Answer 12

You refer to them as table_name.field_name

i.e. tblDownloads.DownloadTime

Question 13

When working with multiple tables in a database, how do you form a relationship?

Answer 13

You specify in the WHERE segment that tableone_name.Foreign_Key = tabletwo_name.Primary_Key

For example:
WHERE tblDownloads.MemberID= tblMembers.MemberID

Question 14

How do you insert data using SQLite?

Answer 14

You choose the table you want to INSERT INTO, and the (fields you want to insert)
You then input the VALUES you want to insert

INSERT INTO tblDownloads (Date, Track)
VALUES (“03/07/2008”, “Mulberry Sky”), (“23/02/2013”, “Time of dusk”);

Question 15

How do you update data using SQL?

Answer 15

You choose the tables you want to UPDATE
And choose the fields you want to SET
Then choose WHERE you want the values to be updates.

UPDATE tblUsers
SET password = “password123”
WHERE UserStatus = “empty”;

Question 16

How do you delete data using SQL?

Answer 16

You choose the table you want to DELETE FROM
and choose the conditions WHERE you want things to be deleted

DELETE FROM tblEmployees
WHERE time_of_employment < “03-03-2001”;

Question 17

What is a one to many connection?

Answer 17

A connection where one value can appear multiple times on one table, but only once on another.

For example, a member of a gym can only appear once on a members table, but multiple times on a downloads table