Database Management

Front 1

What is a Data Dictionary

Back 1

A file that defines the basic organisation of a database. A data dictionary contains a list of all files in the database, the number of records in each file, and the names and types of each field.

Most database management systems keep the data dictionary hidden from users to prevent them from accidentally destroying its contents.

Data dictionaries do not contain any actual data from the database, only bookkeeping information for managing it.

Without a data dictionary, however, a database management system cannot access data from the database.

Front 2

ERD

Back 2

An entity-relationship diagram, or ERD, is a chart that visually represents the relationship between database entities. ERDs model an organisation's data storage requirements with three main components: entities, attributes, and relationships.

Front 3

De-normalisation

Back 3

The process of attempting to optimise the read performance of a database by adding redundant data or by grouping data.

In some cases, de-normalisation is a means of addressing performance or scalability in relational database software.

Front 4

Normalisation

Back 4

The process of organizing the columns (attributes) and tables (relations) of a relational database to minimise data redundancy.

Has 4+ Normal Forms

(1NF),(2NF),(3NF),(BCNF = 4 1/2) and 4NF

(5NF)(6NF) - doesn't matter - Amit told me 6NF is a joke. haha

Informally, a relational database table is often described as "normalized" if it meets Third Normal Form.

Front 5

1NF

Back 5

A table is in first normal form if and only if it is "isomorphic to some relation", which means, specifically, that it satisfies the following five conditions:

There's no top-to-bottom ordering to the rows.

There's no left-to-right ordering to the columns.

There are no duplicate rows.

Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).

All columns are regular [i.e. rows have no hidden components such as row IDs, object IDs, or hidden timestamps].

Front 6

2NF

Back 6

A table is in 2NF if it is in 1NF and every non-primary attribute of the table is dependent on the whole of every candidate key of that column.

Front 7

3NF

Back 7

Is a normal form used in normalising a database design to reduce the duplication of data and ensure referential integrity by ensuring that:

1. The entity is in second normal form.

2. All the attributes in a table are determined only by the candidate keys of that table and not by any non-prime attributes.

Helpful video on how to normalisation:

https://www.youtube.com/watch?v=fg7r3DgS3rA

Front 8

Locking

Back 8

A lock is used to “lock” some data in a database so that only one database user/session may update that particular data.

database locks exist to prevent two or more database users from updating the same exact piece of data at the same exact time.

MORE INFO:

http://www.programmerinterview.com/index.php/database-sql/database-locking/

Front 9

Deadlock scenario

Back 9

Is a situation that occurs when two or more different database user/sessions have some data locked, and each database user/session requests a lock on the data that another, different, session has already locked.

MORE INFO:

http://www.programmerinterview.com/index.php/database-sql/database-deadlock/

Front 10

Concurrent updates

Back 10

Something that can happen when multiple database sessions are permitted to update the same data at the same time.

MORE INFO:

http://www.programmerinterview.com/index.php/database-sql/concurrent-update-problem/

Front 11

Three-tier architecture

Back 11

A client–server software architecture pattern in which the user interface (presentation), functional process logic ("business rules"), computer data storage and data access are developed and maintained as independent modules, most often on separate platforms.

Front 12

Presentation tier

Back 12

This is the topmost level of the application. It displays information related to such services as browsing merchandise, purchasing and shopping cart contents. It communicates with other tiers by which it puts out the results to the browser/client tier and all other tiers in the network.

It is a layer which users can access directly such as a web page, or an operating systems GUI.

Front 13

Application tier (business logic, logic tier, or middle tier)

Back 13

The logical tier is pulled out from the presentation tier and, as its own layer, it controls an application’s functionality by performing detailed processing.

Front 14

Data tier

Back 14

The data access layer should provide an Application Programming Interface (API) to the application tier that exposes methods of managing the stored data without exposing or creating dependencies on the data storage mechanisms.

Front 15

n-tier architecture

Back 15

Is a client–server architecture in which presentation, application processing, and data management functions are physically separated. The most widespread use of multi-tier architecture is the three-tier architecture.

Heterogeneous

Front 16

Protection from SQL Injection

Back 16

•Ensuring Web Servers only have read permissions to the databases/tables they need.

•Assigning Roles in the DBMS to only allow certain users to connect from certain machines.

•Where possible add a second layer of authentication to applications who need toperform write queries (Use separate connection factories).

Front 17

Web based database

Back 17

In the web development field, three-tier is often used to refer to websites, commonly electronic commerce websites, which are built using three tiers:

A front-end web server serving static content, and potentially some cached dynamic content. In web based application, Front End is the content rendered by the browser.

A middle dynamic content processing and generation level application server, for example Ruby on Rails, Java EE, ASP.NET, PHP, ColdFusion, Perl, Pythonplatform.

A back-end database or data store, comprising both data sets and the database management system software that manages and provides access to the data.

Front 18

Object Oriented

Back 18

Provides for data objects that store in one data structure:

- The data

- Rules and ways about how the data looks, acts, how it can be manipulated, and how it can be accessed.

- Allows for complex data types to be intermingled in one database.

Front 19

Relational Database

Back 19

Made up of tables consisting of rows and columns, each column has a name and single data type.

Relational databases supports

- integers

- floating point

- character strings

- date-time

- currency

Front 20

Why is a spreadsheet NOT a Database?

Back 20

- Doesn't have relationships

- Writing queries (especially advanced)

- Role management e.g admin/user

Front 21

Homogeneous

Back 21

1 database language e.g SQL

Front 22

Heterogeneous

Back 22

Multiple database languages e.g MYSQL, Oracal etc.

Front 23

Distributed Database

Back 23

A database that consists of two or more data files located at different sites on a computer network. Because the database is distributed, different users can access it without interfering with one another.

The DBMS must periodically synchronise the scattered databases to make sure that they all have consistent data.

Front 24

Distributed database (Pros)

Back 24

- local control of data

- increased database capabilities

- system availability

- improved performance

if performing well - ACID

Front 25

Distributed Database (Cons)

Back 25

- Databases need to be synced otherwise copies

- complicated security and back up recovery

- more expensive

Front 26

ACID

Back 26

Atomicity (boolean, either complete transaction or nothing), Consistency (Always in valid state), Isolation (concurrency control), Durability (Once transaction is complete it remains so)

a set of properties that guarantee that database transactions are processed reliably

Front 27

DATA WAREHOUSES

Back 27

Subject oriented: Looking at a specific subject e.g marketing database to find marketing information.

Integrated: Whether data is distributed you still want to have it all in one data warehouse, integrated together.

Time-variant:

Not happening in real time, more like archive data, analysing historic data.

Non-volatile: Read-only, data will not change over time.

Front 28

Role of DBA

Back 28

Access privileges

Security - Views are most common

Disaster planning - recovery etc

Archiving - moving data to archives

DBMS maintenance

Data dictionary management

Front 29

OLTP

Back 29

Online transaction processing.

facilitates and manages transaction-oriented applications, typically for data entry and retrieval transaction processing.

Real time, live, 24/7

Front 30

OLAP

Back 30

Online Analytical Processing.

Performs multidimensional analysis of business data and provides the capability for complex calculations, trend analysis, and sophisticated data modelling.

Data mining, Archiving, Data Warehouse

Front 31

Forward Recovery

Back 31

When recovering the database, you must redo the effects of the previous transactions. This is called Rolling Forward or simple Forward Recovery.

You can use your log file to roll forward all the transaction that was completed but lost due to disk crash.

Front 32

Backwards Recovery

Back 32

restores a journaled database to a prior state. Backward processing starts by rolling back updates to a checkpoint prior to the desired state and replaying database updates forward till the desired state.

used for incorrect transactions or if a transaction was stopped mid stream.

Front 33

Security

Back 33

Encryption - HTTPS

Authentication - the process of ascertaining that somebody really is who he claims to be.

Authorisation - rules that determine who is allowed to do what

Views - Restricts part of the database e.g 1 column or row

Front 34

Data visualisation

Back 34

A general term that describes any effort to help people understand the significance of data by placing it in a visual context.

Patterns, trends and correlations that might go undetected in text-based data can be exposed and recognised easier with data visualisation software.

Front 35

SQL Injection

Back 35

A code injection technique, used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to delete the database contents).

Front 36

Multi-valued Attributes

Back 36

Separated by a comma, this type of attribute can have more than one value at one time. For example, degree of a person is a multi-valued attribute since a person can have more than one degree.

Front 37

Composite Attributes

Back 37

Separated by a space, this type of attribute can be divided into smaller sub-parts. These sub-parts represent basic attributes with independent meanings of their own. For example, take Name attributes. We can divide Name into sub-parts like First_name, Middle_name, and Last_name. Attributes that can’t be divided into sub-parts are called Simple attributes.