Study your flashcards anywhere!

Download the official Cram app for free >

  • Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

How to study your flashcards.

Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key

Up/Down arrow keys: Flip the card between the front and back.down keyup key

H key: Show hint (3rd side).h key

A key: Read text to speech.a key

image

Play button

image

Play button

image

Progress

1/37

Click to flip

37 Cards in this Set

  • Front
  • Back

What is a Data Dictionary

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.

ERD

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.

De-normalisation

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.

Normalisation

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.

1NF

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].

2NF

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.

3NF

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:


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

Locking

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/

Deadlock scenario

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/

Concurrent updates

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/

Three-tier architecture

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.

Presentation tier

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.

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

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.

Data tier

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.

n-tier architecture

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

Protection from SQL Injection

•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).

Web based database

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.

Object Oriented

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.

Relational Database

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

Why is a spreadsheet NOT a Database?

- Doesn't have relationships


- Writing queries (especially advanced)


- Role management e.g admin/user

Homogeneous

1 database language e.g SQL

Heterogeneous

Multiple database languages e.g MYSQL, Oracal etc.

Distributed Database

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.



Distributed database (Pros)

- local control of data


- increased database capabilities


- system availability


- improved performance




if performing well - ACID

Distributed Database (Cons)

- Databases need to be synced otherwise copies


- complicated security and back up recovery


- more expensive

ACID

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

DATA WAREHOUSES

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.

Role of DBA

Access privileges


Security - Views are most common


Disaster planning - recovery etc


Archiving - moving data to archives


DBMS maintenance


Data dictionary management

OLTP

Online transaction processing.




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


Real time, live, 24/7

OLAP

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

Forward Recovery

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.

Backwards Recovery

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.

Security

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

Data visualisation

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.

SQL Injection

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).

Multi-valued Attributes

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.

Composite Attributes

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.