Questions tagged [database]

A database is an organized collection of data. It is the collection of schemas, tables, queries, reports, views, and other objects. The data are typically organized to model aspects of reality in a way that supports processes requiring information. Use this tag if you have questions about designing a database. If it is about a particular database management system, (e.g., MySQL), please use that tag instead.

From Wikipedia:

A database is an organized collection of data. It is the collection of tables, queries, reports, views and other objects. The data is typically organized to model aspects of reality in a way that supports processes requiring information, such as modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies.

A large proportion of websites and applications rely on databases. They are a crucial component of telecommunications systems, banking systems, video games, and just about any other software system or electronic device that maintains some amount of persistent information. In addition to persistence, database systems provide a number of other properties that make them exceptionally useful and convenient: reliability, efficiency, scalability, concurrency control, data abstraction, and high-level query languages. Databases are so ubiquitous and important that computer science graduates frequently cite their database class as the one most useful to them in their industry or graduate-school careers.2

The term database should not be confused with Database Management System (DBMS). A DBMS is the system software used to create and manage databases and provide users and applications with access to the database(s). A database is to a DBMS as a document is to a word processor.

Database Languages

Database languages are special-purpose languages, which do one or more of the following:

###ACID In computer science, ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties of database transactions.

  1. Atomicity - Atomicity requires that each transaction be "all or nothing": if one part of the transaction fails, then the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes. To the outside world, a committed transaction appears (by its effects on the database) to be indivisible ("atomic"), and an aborted transaction does not happen.

  2. Consistency - The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof. This does not guarantee correctness of the transaction in all ways the application programmer might have wanted (that is the responsibility of application-level code) but merely that any programming errors cannot result in the violation of any defined rules.

  3. Isolation - The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially, i.e., one after the other. Providing isolation is the main goal of concurrency control. Depending on the concurrency control method (i.e., if it uses strict - as opposed to relaxed - serializability), the effects of an incomplete transaction might not even be visible to another transaction.

  4. Durability - The durability property ensures that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. In a relational database, for instance, once a group of SQL statements execute, the results need to be stored permanently (even if the database crashes immediately thereafter). To defend against power loss, transactions (or their effects) must be recorded in a non-volatile memory.

A few notable DBMSs:

Popular Database tools

Some useful references:

Free online database courses:

193843 questions
7 answers

How does database indexing work?

Given that indexing is so important as your data set increases in size, can someone explain how indexing works at a database-agnostic level? For information on queries to index a field, check out How do I index a database column.
Xenph Yan
  • 83,019
  • 16
  • 48
  • 55
20 answers

What is the "N+1 selects problem" in ORM (Object-Relational Mapping)?

The "N+1 selects problem" is generally stated as a problem in Object-Relational mapping (ORM) discussions, and I understand that it has something to do with having to make a lot of database queries for something that seems simple in the object…
Lars A. Brekken
  • 24,405
  • 3
  • 25
  • 27
24 answers


How do you perform the equivalent of Oracle's DESCRIBE TABLE in PostgreSQL with psql command?
Mr. Muskrat
  • 22,772
  • 3
  • 20
  • 21
27 answers

Insert into ... values ( SELECT ... FROM ... )

I am trying to INSERT INTO a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the SQL engine of the day (MySQL, Oracle, SQL Server,…
Claude Houle
  • 41,064
  • 8
  • 33
  • 42
8 answers

What are the options for storing hierarchical data in a relational database?

Good Overviews Generally speaking, you're making a decision between fast read times (for example, nested set) or fast write times (adjacency list). Usually, you end up with a combination of the options below that best fit your needs. The following…
  • 9,891
  • 6
  • 33
  • 57
21 answers

Which version of PostgreSQL am I running?

I'm in a corporate environment (running Debian Linux) and didn't install it myself. I access the databases using Navicat or phpPgAdmin (if that helps). I also don't have shell access to the server running the database.
Highly Irregular
  • 38,000
  • 12
  • 52
  • 70
46 answers

How do I rename a MySQL database (change schema name)?

How do I quickly rename a MySQL database (change its schema name)? Usually I just dump a database and re-import it with a new name. This is not an option for very big databases. Apparently RENAME {DATABASE | SCHEMA} db_name TO new_db_name; does bad…
  • 11,253
  • 24
  • 74
  • 85
25 answers

Reset identity seed after deleting records in SQL Server

I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each table has to have a primary key and identity…
  • 17,975
  • 51
  • 129
  • 180
13 answers

Optimistic vs. Pessimistic locking

I understand the differences between optimistic and pessimistic locking. Now, could someone explain to me when I would use either one in general? And does the answer to this question change depending on whether or not I'm using a stored procedure…
Jason Baker
  • 192,085
  • 135
  • 376
  • 510
23 answers

Database, Table and Column Naming Conventions?

Whenever I design a database, I always wonder if there is a best way of naming an item in my database. Quite often I ask myself the following questions: Should table names be plural? Should column names be singular? Should I prefix tables or…
  • 74,180
  • 73
  • 171
  • 204
25 answers

MyISAM versus InnoDB

I'm working on a projects which involves a lot of database writes, I'd say (70% inserts and 30% reads). This ratio would also include updates which I consider to be one read and one write. The reads can be dirty (e.g. I don't need 100% accurate…
  • 9,231
  • 5
  • 21
  • 8
15 answers

What's the difference between identifying and non-identifying relationships?

I haven't been able to fully grasp the differences. Can you describe both concepts and use real world examples?
5 answers

Multiple Indexes vs Multi-Column Indexes

What is the difference between creating one index across multiple columns versus creating multiple indexes, one per column? Are there reasons why one should be used over the other? For example: Create NonClustered Index IX_IndexName On…
  • 74,180
  • 73
  • 171
  • 204
10 answers

Difference between scaling horizontally and vertically for databases

I have come across many NoSQL databases and SQL databases. There are varying parameters to measure the strength and weaknesses of these databases and scalability is one of them. What is the difference between horizontally and vertically scaling…
London guy
  • 27,522
  • 44
  • 121
  • 179
10 answers

How to get size of mysql database?

How to get size of a mysql database? Suppose the target database is called "v3".
  • 8,075
  • 4
  • 20
  • 13
2 3
99 100