Database Design and Management

Question 1

What are some common relation databases?

Answer 1

-Oracle
-SQL Server
-Informix
-DB2
-PostgreSQL (open source)
-MySQL (open source)
-MongoDB (open source, noSQL)
-SQLite (open source)

Question 2

What are the pros and cons of indexes?

Answer 2

Speeds up read speed, slows down write speed

Question 3

What is a data dictionary?

Answer 3

A list with a description of tables, fields, and columns.

Question 4

What is a data model?

Answer 4

A data model is an abstract model that organizes elements of data and standardizes how they relate to one another and to the properties of real-world entities.

Question 5

What are the common field types?

Answer 5

-Short: integer from -32k to 32k
-Long: integer from -2.1B to 2.1 B
-Float: decimals with 6 digits of accuracy
-Double: decimals with 15 digits of accuracy
-Text: Alphanumeric characters
-Date: Date and time
-BLOB: Binary Large Object (stores geometry)
-GUID: Global Unique Identifier
-Raster: Allows raster to be saved in a field

Question 6

What is a Primary Key (PK) and a Foreign Key (FK)?

Answer 6

-Primary Key: the main unique identifier field in a table
-Foreign Key: the field in another table that has the same value as the primary, so it knows which record relates from one table to another

Question 7

What are the types of cardinalities?

Answer 7

-One-to-one (table join): one parcel has one owner
-One-to-many (relate): one parcel has many owners
-Many-to-one (table join): many parcels have the same owner
-Many-many (relate): many parcels have many owners

Question 8

What are the destination and origin tables?

Answer 8

Origin table is joined to the destination table.

Question 9

What is the difference between an achieve and a backup?

Answer 9

Backups are a short term safety net. Archives are saved on a completely different disc. An archive is a library of historic data. Backups are usually overwritten on a daily or weekly basis, archives are not.

Question 10

What is the difference between incremental backups and full backups?

Answer 10

Full backups only happen once. Incremental backups only back up recent updates.

Question 11

What is database compression?

Answer 11

In information theory, data compression, source coding, or bit-rate reduction is the process of encoding information using fewer bits than the original representation.

Question 12

What is database transaction?

Answer 12

A transaction is a unit of work that is performed in its entirety on a database. Multiple SQL statements are run individually, but if they are part of a transaction, either all of the statements succeed or none of them succeed.

Question 13

What is database normalization?

Answer 13

a process where organizes data and tables so it can be added and updated efficiently.