Databases

Question 1

What is a database?

Answer 1

An organised collection of data

Question 2

What is a DBMS?

Answer 2

Database management system

software that interacts with end users, applications and the database itself to capture and analyse the data

Question 3

What are the 4 core database functionalities?

Answer 3

1. Data definition
   a. Creation, modification and removal of definitions that define the organization of the data
2. Update
   a. Insertion, modification and deletion of actual data
3. Retrieval
   a. Providing information in a form directly useable or for further processing by other applications
4. Administration
   a. Registering and monitoring users, enforcing data security, monitoring performance, maintaining data integrity etc..

Question 4

What is a database server?

Answer 4

Dedicated computers that hold the actual database and run only the DBMS and related software.

Question 5

Name 2 types of database

Answer 5

1. Relational

2. NoSQL

Question 6

What is a relational database?

Answer 6

e.g. oracle, mySQL

Composed of:

1. Tables (row and column)
2. records (row)
3. Fields (part of a record containing a single piece of info)

Relationships between tables are represented by foreign keys

Question 7

What is ACID?

Answer 7

Set of properties of a database transaction to guarantee validity even in the event of power failures or errors

1. Atomicity- each transaction treated as one unit either completes or complete fail (no partial)
2. Consistency- either creates a new state or returns dat a to original state
3. Isolation- each in process or not committed must be isolated from any other transaction
4. Durability- Guarantees that once a transaction has been completed it will remain committed regardless of error/power failure

Question 8

What is SQL?

Answer 8

Structured query language- used to extracting data and manipulating a database

Question 9

What is noSQL?

Answer 9

A way of storing and retrieving data that is not based on tabular relations used in relational databases

Question 10

What are the 4 types of noSQL databases?

Answer 10

1. key-value
2. Document e.g. MongoDB uses xml or json
3. column
4. graph

Question 11

What are the 7 steps of database design?

Answer 11

1, Determine the purpose for storing the data

2. Find and organise the information required to be stored e.g. names and types of items
3. Divide information into tables- major subject
4. Define columns for each subject base on the information that needs to be stored
5. Specify primary keys (usually numbers)
6. set up relationships- one to one, one to many etc.
7. apply normalization rules and make adjustments as necessary

Question 12

What is database normalization?

Answer 12

The process of structuring relational databases to reduce data redundancy and undesirable characteristics like insertion, update and deletion anomalies.

Usually normalized to 3rd normal form

Question 13

To be in 1st normal form which rules must apply?

Answer 13

1. It should only have single(atomic) valued attributes/columns.
2. Values stored in a column should be of the same domain
3. All the columns in a table should have unique names.
4. And the order in which data is stored, does not matter.

Question 14

To be in 2nd normal form which rules must apply?

Answer 14

1. It should be in the First Normal form.
2. And, it should not have Partial Dependency.
   a. Partial Dependency exists, when for a composite primary key, any attribute in the table depends only on a part of the primary key and not on the complete primary key.
   b. When you have a composite key to uniquely identify rows in a table and one or more columns are only dependent on part of the composite key and not both
   i. I.e. student score example- the teacher is only dependent on the subject not the student
   c. Removing patient dependency field from the scores table and placing it in the subjects table

Question 15

To be in 3rd normal form which rules must apply?

Answer 15

1. It should be in second normal form
2. And, it doesn’t have transitive dependency
   a. When non-prime key attributes depend on other non-prime attributes rather than depending upon the prime attributed or primary key
   b. i.e. exam ID needs an independent table