CS8: Relational Databases & SQL Flashcards

You may prefer our related Brainscape-certified flashcards:
1
Q

What is a database?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is a table?

A

A series of records composed in an organised way

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What is a record?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is a field?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What are primary and foreign keys?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is a database relationship?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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

A

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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

How do you query a database?

A

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”

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What are the three primary logic operations?

A

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”

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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

A

You refer to them as table_name.field_name

i.e. tblDownloads.DownloadTime

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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

A

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

For example:
WHERE tblDownloads.MemberID= tblMembers.MemberID

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

How do you insert data using SQLite?

A

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”);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

How do you update data using SQL?

A

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”;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

How do you delete data using SQL?

A

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”;

17
Q

What is a one to many connection?

A

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