33 votes
Accepted

Why the term "relation(al)"?

First of all, I highly recommend the scientific paper in which Dr. Edgar Frank Codd published the relational framework to the general public in 1970, i.e., A Relational Model of Data for Large Shared ...
user avatar
  • 8,230
29 votes
Accepted

Designing a friendships database structure: Should I use a multivalued column?

Managing an individual piece of information Assuming that, in your business domain, a User can have zero-one-or-many Friends; a Friend must first be registered as a User; and you will search for, and/...
user avatar
  • 8,230
16 votes

Why the term "relation(al)"?

The interesting thing behind 'relational database' is, that it does not (primarily) refer to the relations between tables, as you might expect, but it refers to the relation of multiple properties (...
user avatar
  • 1,408
16 votes
Accepted

Is a table without a primary key normalized?

If a relation does not have any candidate key (and the primary key is just one of the candidate keys), then it can have duplicate rows, so in fact it is not a relation! (since relations are always ...
user avatar
  • 3,965
15 votes
Accepted

How to insert values into a table from two different tables?

You would write the query like this insert into classroom (date, teacher_id, student_id) select '2014-07-08', t.id, s.id from teachers t,students s where t.teacher_name = 'david' and s.student_name = ...
user avatar
13 votes

MongoDB Manual References vs DBREF

Short version: use a manual reference, not a DBRef. Explanation: There is no particular benefit to the use of a DBRef beyond giving you the collection and database that the referenced document ...
user avatar
  • 9,085
13 votes
Accepted

Integrity constraints in a relational database - should we overlook them?

If, as stated in your post, the intention is to create a relational database (RDB for brevity) and, therefore, it is expected that it functions as such, the short answer is: No, you should not ...
user avatar
  • 8,230
13 votes
Accepted

Unused columns in WHERE clause

Both queries are ridiculously complicated. Use DELETE FROM "Foo" WHERE "CreatedAt" < current_timestamp;
user avatar
  • 33.3k
10 votes
Accepted

What are the practical reasons behind learning relational algebra?

When Codd defined the relational model he defined a set of operators which could be applied to relations. In specifying a relational algebra, much like specification of an integer algebra, we are ...
user avatar
  • 3,498
10 votes

Why the term "relation(al)"?

The term "relational" comes from mathematics and has nothing to do with relationships between entities. I'm not a mathematician (whereas Codd had a PhD in Mathematics) and so won't elaborate, but ...
user avatar
  • 3,498
10 votes

Does using composite keys violate 2NF?

Your definition of 2NF is not quite correct. 2NF is when a relation is in 1NF and it has no partial dependencies, meaning there are no predicates (columns) that depend on only part of a multi-part ...
user avatar
  • 12.3k
10 votes
Accepted

Why Cassandra is not considered a relational database?

Cassandra is not an RDBMS because it does not support the relational data model. The fundamental assumption of the relational model is that all data is represented as mathematical n-ary relations, an ...
user avatar
9 votes

Arbitrarily ordering records in a table

It is very simple. You need to have a "cardinality hole" structure: You need to have 2 columns: pk = 32bit integer order = 64bit bigint (not double) Insert/update When inserting the first new ...
user avatar
9 votes
Accepted

How to insert values in junction table for many to many relationships?

You want to maintain atomicity of the action of storing a new place and trip, along with the intersection between the two. In MySQL, you do this with a START TRANSACTION / COMMIT transactional ...
user avatar
  • 12.3k
9 votes

Explaining 2NF vs 3NF with an example

Your relation is in 3NF, (and not only in 2NF), since as you say the only non prime attribute is Grade, which only appears on the right hand side of your FDs. The relation is not in BCNF, because the ...
user avatar
  • 1,610
9 votes

How can I represent discrete ages in a relational database?

The ages are discrete values OK. a Person can have any number of the 18 (assuming 0-17 years old) assigned to their account. So it's a many-to-many relationship? If so, you just decompose your ...
user avatar
  • 113
9 votes

Integrity constraints in a relational database - should we overlook them?

The basic premise of your developers is absolutely wrong. Foreign keys will impact slightly the performance of the DML of your system. They are not used at all in queries thus have no effect on their ...
user avatar
  • 2,099
9 votes
Accepted

Why is it considered that a set unnormalizes a database absolutely?

The reference for this post is an amazing book called Database System Concepts 6th Edition which I recommend you read. In the book, page 328, it states: A domain is atomic if elements of the ...
user avatar
8 votes

What normal form does a surrogate key violate?

The accepted answer is incorrect; the answers given by @sqlvogel and @gbn are correct. Surrogate keys are non-domain-driven keys that stand-in for natural keys (those with functional dependencies ...
user avatar
8 votes

If a table with a surrogate key has a column known to have unique non null values (eg SSN), does it violate 3NF?

A relation R is in third normal form if every non-prime attribute of R is non-transitively dependent on each candidate key of R E.F.Codd, 1971, Further Normalization of the Data Base Relational ...
user avatar
  • 3,653
8 votes

Relation between first name and last name

I think the example is a very theoretical one. I have never seen any real world examples of separate FirstNames and LastNames tables. Of course you can normalize your database this way, but it wouldn'...
user avatar
  • 1,405
8 votes

First Normal Form: Definitive Definition

Preliminary The definition of normal form (which from the presentation of “Further Normalization of the Data Base Relational Model” in 1971 is known as first normal form) and the definition of the ...
user avatar
  • 8,230
8 votes

Does the concept of candidate key exist only in theory?

As far as I know, no SQL database management system (DBMS) supplies the CANDIDATE KEY keyword as such, but (as I consider that you are suggesting in the question) that does not mean that the notion (...
user avatar
  • 8,230
8 votes
Accepted

RDBMS: Proper way to store data - comma separated variables or in different fields or tables?

Why storing data as a string a problem : Storing multiple datums as (in this case comma separated) strings is bad practice because: First reason: It breaches Codd's second rule (called the "...
user avatar
7 votes
Accepted

Database design: Normalizing a "(many-to-many)-to-many" relationship

Option 1 *This doesn't seem like a great idea to me, because it complicates the SQL to select all properties applied to a feature… It does not necessarily complicate query SQL (see conclusion ...
user avatar
  • 37.5k
7 votes

Difference between an Entity and a Relation

"Entity" is a real world concept, such as "a person" in your example. "Relation" is a set of tuples (records), each representing a model of an entity. In your example each tuple represents ...
user avatar
  • 21.6k
7 votes

Why the term "relation(al)"?

It's an intuitive name when you think of them with natural keys. You can think of a cell value as representing an entity. Relation: Employee |--------+------------+--------| | name | job | ...
user avatar
  • 279
7 votes
Accepted

Is combining independent tables called normalisation?

No, the task you are being asked to fulfill —arranging (a) columns that belong in two or more distinct base tables in (b) a single base table— is not called normalisation (nor normalization for those ...
user avatar
  • 8,230
7 votes

Is saving disk space still considered an advantage of normalization?

Normalization was never about saving disk space. In his 1971 normalization paper E.F.Codd wrote: "It is important to remember that we are not making a case for or against any physical storage ...
user avatar
  • 3,653
6 votes

How to insert values into a table from two different tables?

The easiest way you can do this is using sub queries: INSERT INTO classroom(teacher_id,student_id) VALUES ((SELECT id FROM students WHERE s_name='sam'), (SELECT id FROM teacher WHERE t_name='david'...
user avatar
  • 300

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