Section 4 - Exchanging Data Flashcards by Rose Rutledge

Q

SQL

What does SQL stand for?

A

Structured Query Language

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

SQL

What type of language is SQL and what is it used for?

A

A declarative
language used for querying and updating tables

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

SQL

Name each statement used in SQL

4 statements

A

SELECT
FROM
WHERE
ORDER BY

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

SQL

What does the SELECT statement do?

A

List the fields to be displayed

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

SQL

What does the FROM statement do?

A

List the table or tables the data will come from

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

SQL

What does the WHERE statement do?

A

List the search criteria

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

SQL

What does the ORDER BY statement do?

A

List the fields that the results are to be sorted on (default is Ascending order)

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

SQL

Name each condition used in SQL

A

Python conditions
IN
LIKE
BETWEEN…AND
IS NULL
AND
OR
NOT

Python conditions: =, >, <, !=, >=, <=

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

SQL

What does the IN condition do?

A

Equal to a value within a
set of values

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

SQL

What does the LIKE condition do?

A

Similar to

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

SQL

What does the IS NULL condition do?

A

Field does not contain
a value

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

SQL

What does the BETWEEN…AND condition do?

A

Within a range, including
the two values which
define the limits

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

SQL

What does the AND condition do?

A

Both expressions must
be true for the entire
expression to be judged
true

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

SQL

What does the OR condition do?

A

If either or both of the
expressions are true,
the entire expression is
judged true

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

SQL

What does the NOT condition do?

A

Inverts truth

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

SQL

What does the JOIN statement do?

A

Provides an alternative method of combining rows from two or more tables, based on a common field between them

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

SQL

How do you combine data with the JOIN statement?

A

SELECT Song.SongTitle, Artist.ArtistName, Song.MusicType
FROM Song
JOIN Artist
ON Song.ArtistID = Artist.ArtistID
WHERE Song.MusicType = “Art Pop”

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

SQL

How do you combine data without the JOIN statement?

A

SELECT Song.SongTitle, Artist.ArtistName, Song.MusicType
FROM Song, Artist
WHERE (Song.ArtistID = Artist.ArtistID) AND (Song.MusicType = “Art Pop”)

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

Compression, Encryption and Hashing

What are the two types of compression?

A

Lossy and Lossless

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

Compression, Encryption and Hashing

What are the differences between lossless and lossy compression?

A

Data lost in lossy, none lost in lossless
Lossy compression cannot be done on text, but lossless compression can be on done any file

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

Compression, Encryption and Hashing

What are the two algorithms used for compression?

A

Run Length Encoding
Dictionary-Based Encoding

Q

Compression, Encryption and Hashing

What is Run Length Encoding?

A

A form of lossless compression that compresses data by specifying the number of times a character/pixel colour repeats followed by the value of the character/pixel

Q

Compression, Encryption and Hashing

What is Dictionary-Based Encoding?

A

A form of lossless compression that compactly represents repeated values to represent information without copying data

Q

Compression, Encryption and Hashing

What are the two types of encryption?

A

Symmetric and Asymmetric

# Compression, Encryption and Hashing What is symmetric encryption?

Uuses the same key to encrypt and decrypt data, which is sent with the encrypted data

# Compression, Encryption and Hashing What is asymmetric encryption?

Uses different keys to encrypt and decrypt data - a public key and a private key respectively.

# Compression, Encryption and Hashing What is hasing?

A mapping between an arbitrary length input and a usually fixed length or smaller output This is one way

# Compression, Encryption and Hashing What is hasing used for?

* Storing passwords in a database * Hash tables

# Database Concepts What is a relational database?

Multiple linked tables

# Database Concepts What is a flat-file database?

Single table

# Database Concepts What are the advantages/disadvantages of a flat-file database compared to a relational database?

* Takes longer to search * Bigger * Harder to maintain

# Database Concepts What are the four key types?

1. Primary key 2. Secondary key 3. Foreign key 4. Composite key

# Database Concepts What is a primary key?

Unique value/indentifier that identifies a particular record (primary keys are always underlined in the entity description)

# Database Concepts What is a foreign key?

Link to a different table's primary key

# Database Concepts What is a composite key?

A combination of two keys (A foreign and primary combo is called a composite primary key)

# Database Concepts Draw out an entity relationship diagram for: * one-to-one * one-to-many * many-to-many

Check textbook page 84 (search 93)

# Database Concepts What are the features of 1st Normal Form (1NF)?

* Data is atomic * No repeated groups of attributes

# Database Concepts What are the features of 2nd Normal Form (2NF)?

* No partial dependencies

# Database Concepts What are the features of 3rd Normal Form (3NF)?

* No non-key dependencies

# Transaction Processing What is referential integrity?

No foreign key in one table can reference a non-existent record in a related table

# Transaction Processing What are the ACID principles?

* Atomicity: each transaction must fully complete * Consistency: data stays consistent before and after the transaction (relates to referential integrity) * Isolation: one transaction will not interfere with another transaction (relates to record locking) * Durability: transactions saved after crashes

# Transaction Processing What is record locking?

The prevention of simultaneous access to objects in a database in order to prevent updates being lost/overwritten Anyone else attempting to retrieve the same record is denied access until the transaction is completed or cancelled

# Transaction Processing What is redundancy in a database?

Repeated copies of database information to prevent the loss of data If one system fails, the backup system automatically takes over and processing can continue

# Transaction Processing What are the different ways of capturing data?

* Web forms * Manually * Optical Character Recognition (OCR) * Magnetic Ink Character Recognition (MICR) * Smart card readers * Scanners

# Transaction Processing What are the two ways of storing data?

* XML * JSON | There are many other ways, but these two are the most memorable

# Database Concepts What is a secondary key?

An identifier other than the primary key that identifies a record * Useful for indexing commonly searched-for records (e.g. surname) (secondary keys are always in italics in the entity description)

# Database Concepts What is an entity?

A category of object, person, event, or thing of interest about which data needs to be recorded | An entity could be club members or concert venues

# Database Concepts How do you write an entity description?

Entity (data1, data2, ...) Example: Customer (custID, title, forename, surname, email)

# Database Concepts Which record is the primary key? Product (productID, title, subject, level, price)

productID

# Database Concepts Which records are the composite primary key? OrderLine (OrderNumber, OrderLine, ProductID, ProductName)

OrderNumber, OrderLine

# Database Concepts How is a relation formed between entities?

At least one entity must contain a foreign key for the other entity A foreign key always goes on the “many” side of a relationship | e.g. custID from Customer in the entity description of Subscription

# Database Concepts Can entities with a many-to-many relation be directly linked?

No A link table must be placed between them This link table is always on the "many" side of the relation, with the original two tables becoming a "one" side of this new relation