Databases (Ultimate Deck)

Question 1

Distinction between Data and Information

Answer 1

Data is just raw facts, numbers and figures
(e.g. 1975)

Information is data that has been given a context and meaning.
(e.g. The Vietnam war ended in 1975)

Question 2

Database

Answer 2

An organized collection of structured information.

Question 3

What is the software called that creates, defines, and manipulates a database?

Answer 3

Database Management System (DBMS)

Question 4

What are the things at the top of columns in a database that describe what’s inside that column?

Answer 4

Fields/Attributes

Question 5

What is a row in a database referred to?

Answer 5

A record

Question 6

Benefits of a database

Answer 6

• Enables for processing/querying of data
• Can easily generate reports
• Standardized (can be used by multiple systems)

Question 7

Data Verification (in context of databases)

Answer 7

This is the checking that input matches expected values in database

E.g. login details of a user matches those stored in the database

Question 8

Data Validation (in context of databases)

Answer 8

Checks that input follows rules for the type of input entered

For example: CVV code should be 3 numerical digits

Question 9

Entity

Answer 9

A real-world object or person represented by a record in a database

Question 10

Primary Key

Answer 10

A unique key used to identify a given record in a database

Question 11

Foreign Key

Answer 11

A field in one table that refers to the primary key field in another table. It is used to connect (relational) databases.

Question 12

6 different types of data types

Answer 12

• Text:
  Combination of letters, numbers or symbols. Basically a string.
• Character:
  In individual letter, number or symbol
• Boolean:
  A binary value (True/False, Yes/No)
• Integer:
  A whole number
• Real:
  A decimal number
• Datetime:
  A date and/or time

Question 13

Write the SQL to get ProductName from the table Products where the price is more than 20 but less than 25

Answer 13

SELECT ProductName FROM Products WHERE Price > 20 AND Price < 25

Question 14

Secondary Key

Answer 14

Keys that are also capable of functioning as a primary key. Also known as the alternate key.

Question 15

2 differences between Primary and Secondary Key

Answer 15

Primary Key - Cannot be Null, only one primary key possible

Secondary Key - Can be Null, multiple secondary keys possible

Question 16

Candidate Key

Answer 16

Umbrella term for keys that can uniquely identify each record in a database

Both primary and secondary keys are candidate keys

Question 17

Composite Primary Key

Answer 17

When one field is not enough to uniquely identify records, two fields can function together as the primary key

Question 18

Database schema

Answer 18

A diagram of how data is organized in a relational database.

This means a diagram showing table names, fields, data types of fields, and relationships between tables

Question 19

Records can also be referred to as?

Answer 19

Tuples

Question 20

Referential Integrity

Answer 20

This is a feature of relational databases. What this means is every row has a primary key, and that every value in a foreign key column will be found in the primary key of the table from which it originated.

Question 21

Concurrency, and what does it use to prevent more than one user accessing the same row/record at the same time?

Answer 21

This is the process referred to managing simultaneous updates or transactions at the same time. It ensures updates/transactions occur sequentially to avoid collision between users’ actions.

Row locking

Question 22

Data Dictionary

Answer 22

A file or set of files that stores how the information and tables should be organized and stored in a database

Question 23

How does DMBS provide security to a database? (5 ways)

Answer 23

• Access Rights
• Audit trials (Record of any changes to the database)
• Data/Row Locking
• Encryption
• Backups - Continually updated copes that can be accessed in case the original database is lost or corrupted

Question 24

Transaction

Answer 24

A sequence of one or more SQL statements that are executed as a single unit of work. A transaction can either be committed (applied to the database) or rolled back (undoing something). Essentially, it allows us to make temporary changes, making data integrity easier.

Question 25

Different states in transactions?

Answer 25

Active State
Partially Commited State
Commited State
Failed State
Aborted State
Terminated State

Question 26

What is the acronym for ACID?

Answer 26

Atomicity:
All tasks in a transaction are performed if successful. Else no tasks are performed.

Consistency:
All data written must be valid according to existing rules.

Isolation:
No transactions will collide/interfere with another. Sequential execution of transactions.

Durability:
Once a transaction is complete, the change to the database is permanent, even if there is system failure.

Question 27

Why do we have transactions?

Answer 27

They make sure there is never a case where there were incomplete changes.

Question 28

Log

Answer 28

Records every change making it easier to roll back

Question 29

Data Integrity

Answer 29

Accuracy:
The correct data and relationships are retained and preserved

Completeness:
All data needed is available

Validity:
Data meets all predetermined rules

Question 30

Audit Trail

Answer 30

This records all changes, login attempts, and other security related events to help detect and investigate security breaches.

Question 31

Data Redundancy

Answer 31

This is what happens where the same piece of data is stored in multiple places. It can happen in different or the same tables.

Question 32

Data Redundancy Pros & Cons

Answer 32

Pros:
- Faster data access speeds
(more locations from which data can be accessed)

• Better data protection:
  In case of loss of data, it can be replaced from another location

Cons:
- Data inconsistency:
If data is updated at one location, other locations may not be updated as well

• Takes more memory (more expensive)

Question 33

Normalization

Answer 33

This is the process in which larger tables in a database are divided into smaller tables to reduce data redundancy while ensuring data integrity.

Question 34

What are the different Normal forms, and at each step what do they seek to eliminate?

Answer 34

1st Normal form (1NF)
- Eliminate duplicate columns, columns with multiple types of values
- Create separate tables for each group of related data (with unique primary keys)

2nd Normal Form (2NF)
- Meet all the requirements for 1NF
- Eliminate partial dependency (Columns that are dependent on one attribute of a composite primary key)

3rd Normal Form (3NF)
- Meet all the requirements for 2NF
- Eliminates transitive dependency