32 votes

What exactly is the 'online' in OLAP and OLTP?

It is simply a remnant of olden times, when it was used in contrast to batch processing. "Online" here means "interactive", that is, requests to the database are processed as they come and responses ...
  • 22.4k
24 votes
Accepted

Are junction tables a good practice?

If you put everything in one table, you will have a bigger, redundant table. If all the tables are properly indexed, the 3 tables solution will be fast, because a small number of rows will be read ...
16 votes

Are junction tables a good practice?

Junction tables are a standard practice in relational database design. If you have a many-to-many relationship between two entities, the standard way to represent them is with three tables. Two of the ...
  • 4,118
11 votes

Are junction tables a good practice?

"Please assume that all related fields are properly indexed between them." No, I won't do that. I see too many users who have never heard of "composite" indexes, much less understand their ...
  • 70.5k
9 votes
Accepted

Alternatives to storing a record with exactly n multiple foreign keys from the same foreign table, where the relationships can't be repeated

Are there official terms for this type of scenario? Yes. This is a Symmetric Relation. And "relation" here has the same meaning as in "Relational Database". An RDBMS is a database management ...
8 votes

What is meant by a blind write in a schedule?

If there is no read that happens prior to the first write then it is said to be a blind write. Example. Consider the following schedule: W3(X) is a blind write, as there is no read before write [R3(...
  • 181
8 votes

Mathematical equality of two SQL statements

What is the mathematical equality of two SQL statements? For me two queries are equivalent if, when given both the same of any dataset, they return the same result set. As you pointed out, SQL ...
  • 380
8 votes
Accepted

Difference between candidate key & primary key

I think this post does a pretty good job of explaining the difference. To summarize: Candidate Key – A Candidate Key can be any column or a combination of columns that can qualify as unique key in ...
7 votes
Accepted

Understanding data abstraction levels

Although the definitions of the schemas or levels of representation of the ANSI/SPARC architecture did not reach the desired precision, they are only three, and they are named external, conceptual and ...
  • 8,270
7 votes

Alternatives to storing a record with exactly n multiple foreign keys from the same foreign table, where the relationships can't be repeated

To long for a comment, so I'll just add this as a complement to David's solution. Even though the question was of theoretical nature, it may be of interest to see how that can be implemented in real ...
6 votes
Accepted

What's the term that describes filtering query that makes use of indexes

Sargable (or sometimes sargeable). It's not really a word, it's made up of Search ARGument, and when a WHERE clause is sargable, that mean's it's possible for it to use an index. It doesn't mean it ...
6 votes
Accepted

What are the practical uses for relational calculus?

I agree with @Erik, these instruments are essentially equivalent, but I am going to add a few more ideas about this topic. Context In order to provide more context, it is necessary to have an ...
  • 8,270
5 votes

What are the practical uses for relational calculus?

https://en.wikipedia.org/wiki/Relational_calculus: The relational algebra and the relational calculus are essentially logically equivalent: for any algebraic expression, there is an equivalent ...
  • 4,722
5 votes
Accepted

Why are multivalued dependencies named that way?

A multivalued dependency A->->B means that each value of A determines a set of values of B (and not a single value of B as in functional dependencies). For instance, suppose that programmers have an ...
  • 3,965
5 votes

what does "\" symbol mean?

The \ symbol is often used in mathematics for set difference, besides dash or minus symbol (-) Since tables and relations are very similar to sets and the operations of union, intersection and ...
4 votes

Why are there so many definitions of database?

If you compare any two authorative references for a single subject they will employ different words even though they convey the same meaning. This is just the nature of English - it has a very rich ...
4 votes
Accepted

Transaction recovery and rollback

Assuming that R means Read and W means Write, then the work you're proposing is like this: W1(x) C That's it. Reads are irrelevant for recovery. And a crash denotes the end, you can't have a write ...
  • 51.1k
4 votes
Accepted

Why is SQL not considered a programming language?

I suppose it is a matter of definition(s) and whom you may ask. Some start would be whether the language is Turing complete. SQL92 is not, but extended with recursive CTE and Window functions you can ...
3 votes

Do Nested Transactions commit?

Is it ok to use the word "commit" for nested transactions? I think so. The syntax is still COMMIT TRANSACTION or COMMIT WORK. How 'commits' of nested transactions are handled is implementation-...
  • 73.8k
3 votes

Do Nested Transactions commit?

I don't think there is a problem using the word commit and personally I don't see a better wording for the concept. Most database systems do not support nested transactions, the only one I could find ...
  • 15.5k
3 votes
Accepted

Minimal 3NF normalization issues

A. Canonical Cover The example is almost a canonical cover, since in a canonical cover each dependency has only an attribute on the right part, so the dependency C → DF must be replaced by the ...
  • 3,965
3 votes

Mathematical equality of two SQL statements

It is impossible to check semantic equivalence in finite time by definition, see Rice's theorem: for any non-trivial property of partial functions, there is no general and effective method to ...
3 votes

Simultaneous money withdrawls from a distributed/replicated database

Short answer In a distributed system, you can't be prevented from simultaneous withdrawals. If you want to prevent simultaneous transactions then don't use a distributed system. Clarification By "...
3 votes

Understanding data abstraction levels

I understand the three layers as: Everything the user sees. Basically the relevant parts of the middle layer spiced with some views. A logical or ER-model, this can be 3NF or similar - I would call ...
  • 369
3 votes
Accepted

How to prove "A->B,C then A->B and A->C" from Armstrong Axioms

Just two steps: you can use reflexivity for B C -> B and then transitivity from A -> B C and the previous dependency. Analogous solution for the second case.
  • 3,965
3 votes
Accepted

Compensation Log Records (CLR) in UNDO-Phase of Database Recovery

Compensation log records (CLRs) are not written to the log during normal operation. They are produced while processing a rollback, either application-requested or due to a crash. Since their only use ...
2 votes

What is a "database"?

Codd's seminal paper was titled A relational model of data for large shared data banks. What he called a "data bank" we would term a database. I like his imagery, however. It implies a place where ...
2 votes

What is a "database"?

I worked for Ashton-Tate during the development of dBASE Direct/36 and dBASE IV, using my dBASE III Plus knowledge to code a small program to aid in testing of dBASE Direct/36 (interface to an IBM ...
2 votes
Accepted

Why is a functional dependency described as a "one-to-many" relationship?

I think that the FD can't be described as "One-to-one" relationships. In One-to-one relationships, the X value identifies a Y value and vice-versa. In FD this doesn't happen. In fact, the Y values can ...
  • 225
2 votes

Mathematical equality of two SQL statements

dba user Lennart pointed me to this project: http://cosette.cs.washington.edu/ Cosette is an automated prover for checking equivalences of SQL queries. It formalizes a substantial fragment of SQL ...
  • 1,351

Only top scored, non community-wiki answers of a minimum length are eligible