Stack Overflow
Stack Overflow

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
38
votes
4 answers

Where should I store a foreign key?

If I have a relationship between two tables (both tables have their own primary keys) what should guide my decision as to which table should store the foreign key? I understand that the nature of the relationship probably matters (one-to-one,…
vicatcu
  • 5,407
  • 7
  • 41
  • 65
38
votes
3 answers

Join one row to multiple rows in another table

I have a table to entities (lets call them people) and properties (one person can have an arbitrary number of properties). Ex: People Name Age -------- Jane 27 Joe 36 Jim 16 Properties Name Property ----------------- Jane Smart Jane …
Ghostrider
  • 7,545
  • 7
  • 30
  • 44
38
votes
17 answers

Is there a good alternative to SQL*PLUS for Oracle?

I am not a fan of using SQL*PLUS as an interface to Oracle. I usually use yasql, but it hasn't been updated since 2005 and can do with some improvements. A quick Google search shows yasql and SQLPal. I am using linux, so SQLPal is not an option. …
gpojd
  • 22,558
  • 8
  • 42
  • 71
38
votes
2 answers

FactoryGirl + Faker - same data being generated for every object in db seed data

I am using FactoryGirl and Faker to generate user objects in my seeds.rb file but for some reason the exact same user is being created and rake db:seed is failing because of an email uniqueness validation. Factory for users: #users.rb require…
sixty4bit
  • 7,422
  • 7
  • 33
  • 57
38
votes
4 answers

Grant privileges for a particular database in PostgreSQL

I'm moving from MySQL to PostgreSQL and have hit a wall with user privileges. I am used to assigning a user all privileges to all tables of a database with the following command: # MySQL grant all privileges on mydatabase.* to 'myuser'@'localhost'…
Joe Lapp
  • 2,435
  • 3
  • 30
  • 42
38
votes
4 answers

Should you make a self-referencing table column a foreign key?

For example to create a hierarchy of categories you use a column 'parent_id', which points to another category in the same table. Should this be a foreign key? What would the dis/advantages be?
meleyal
  • 32,252
  • 24
  • 73
  • 79
38
votes
5 answers

Range query for MongoDB pagination

I want to implement pagination on top of a MongoDB. For my range query, I thought about using ObjectIDs: db.tweets.find({ _id: { $lt: maxID } }, { limit: 50 }) However, according to the docs, the structure of the ObjectID means that "ObjectId…
user1082754
38
votes
5 answers

Show all tables inside a MySQL database using PHP?

I'm trying to show all the tables in my database. I've tried this: $sql = "SHOW TABLES"; $result = $conn->query($sql); $tables = $result->fetch_assoc(); foreach($tables as $tmp) { echo "$tmp
"; } but it only gives me one table name in a…
Jadar
  • 1,643
  • 1
  • 13
  • 25
38
votes
5 answers

Select rows based on last date

I have a table named Course in a Postgres database: How can I select rows which have course name with latest date? I mean if I have two same course names for one ID, I should only show the latest one as the below result. Simply, I want only to show…
Aan
  • 12,247
  • 36
  • 89
  • 150
38
votes
6 answers

Do conditional INSERT with SQL?

I have a database that is updated with datasets from time to time. Here it may happen that a dataset is delivered that already exists in database. Currently I'm first doing a SELECT FROM ... WHERE val1=... AND val2=... to check, if a dataset with…
Elmi
  • 5,899
  • 15
  • 72
  • 143
38
votes
8 answers

How can I create database tables from XSD files?

I have a set of XSDs from which I generate data access classes, stored procedures and more. What I don't have is a way to generate database table from these - is there a tool that will generate the DDL statements for me? This is not the same as…
Oded
  • 489,969
  • 99
  • 883
  • 1,009
38
votes
2 answers

How do I create a rails migration to remove/change precision and scale on decimal?

I am trying to remove the precision and scale attributes from decimal (PostgreSQL NUMERIC) fields in my database? The fields: t.decimal "revenue_per_transaction", :precision => 8, :scale => 2 t.decimal "item_quantity", :precision => 8,…
Richard Burton
  • 2,230
  • 6
  • 34
  • 49
38
votes
8 answers

Flags in a database rows, best practices

I am asking this out of a curiosity. Basically my question is when you have a database which needs a row entry to have things which act like flags, what is the best practice? A good example of this would be the badges on stack overflow, or the…
Evan Teran
  • 87,561
  • 32
  • 179
  • 238
38
votes
6 answers

CouchDB backups and cloning the database

We're looking at CouchdDB for a CMS-ish application. What are some common patterns, best practices and workflow advice surrounding backing up our production database? I'm particularly interested in the process of cloning the database for use in…
Kyle Burton
  • 26,788
  • 9
  • 50
  • 60
38
votes
7 answers

Select Query by Pair of fields using an in clause

I have a table called players as follows: First_Id Second_Id Name 1 1 Durant 2 1 Kobe 1 2 Lebron 2 2 Dwight 1 3 Dirk I wish to…
user1412952
  • 381
  • 1
  • 3
  • 3
1 2 3
…
99 100