Skip to main content

Questions tagged [relational-theory]

On this site this tag applies to questions about Relational Model theory. The Relational Model for database management is an approach to managing data using a structure and language consistent with first-order predicate logic. In the Relational Model of a database, all data is represented in terms of tuples, grouped into relations. A database organized in terms of the Relational Model is a relational database.

Filter by
Sorted by
Tagged with
92 votes
5 answers
25k views

Why can't RDBM's cluster the way NoSQL does?

One of the big plusses for nosql DBMS is that they can cluster more easily. Supposedly with NoSQL you can create hundreds of cheap machines that store different pieces of data and query it all at ...
fregas's user avatar
  • 1,051
36 votes
5 answers
46k views

How should I design a relationship table for friendship?

If A is a friend of B, then should I store both values AB and BA, or one is enough? What are the advantages and disadvantages of both methods. Here is my observation: If I keep both then I have to ...
roxrook's user avatar
  • 485
36 votes
6 answers
12k views

Arbitrarily ordering records in a table

A common need when using a database is to access records in order. For example, if I have a blog, I want to be able to reorder my blog posts in arbitrary order. These entries often have lots of ...
Tom Marthenal's user avatar
30 votes
3 answers
8k views

Why does ANSI SQL define SUM(no rows) as NULL?

The ANSI SQL standard defines (chapter 6.5, set function specification) the following behaviour for aggregate functions on empty result sets: COUNT(...) = 0 AVG(...) = NULL MIN(...) = NULL MAX(...) = ...
Heinzi's user avatar
  • 3,155
29 votes
3 answers
24k views

Proper Use of Lookup Tables

I'm having trouble figuring out exactly how to place good boundaries for when and where to use lookup tables in a database. Most sources I've looked at say that I can never have too many but, at some ...
Brad Turner's user avatar
27 votes
3 answers
14k views

How to have a one-to-many relationship with a privileged child?

I want to have a one-to-many relationship in which for each parent, one or zero of the children is marked as a “favorite.” However, not every parent will have a child. (Think of the parents as ...
cubetwo1729's user avatar
26 votes
6 answers
2k views

Why the term "relation(al)"?

In english, we might talk about the relation between, say, Bob and Tim. Perhaps they're cousins. The term "relation" in this context makes sense to me. In the context of relational databases, I ...
Adam Zerner's user avatar
21 votes
5 answers
13k views

What standard should I follow when naming tables and views?

What standard should I follow when naming tables and views? For instance, is it a good idea to put something like tbl_ at the beginning of table names? Should I designate code/lookup tables in some ...
Beth Lang's user avatar
  • 952
19 votes
1 answer
32k views

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

Say I have a table called User_FriendList, which has the following characteristics: CREATE TABLE User_FriendList ( ID ..., User_ID..., FriendList_IDs..., CONSTRAINT User_Friendlist_PK ...
yeln's user avatar
  • 293
19 votes
2 answers
37k views

Designing a user authenication (Roles & Rights) module

I am trying to model a User Authentication module for a MS SQL Server database that will be the back end to a Delphi UI Application. Basically, I want to have user accounts where the user belongs to ...
Johnny Holmes's user avatar
14 votes
2 answers
7k views

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

Short version I have to add a fixed number of additional properties to each pair in an existing many-to-many join. Skipping to the diagrams below, which of Options 1-4 is the best way, in terms of ...
Michael Underwood's user avatar
13 votes
3 answers
154k views

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

I have three tables students table ------------------------------------ id(PK, A_I) | student_name | nationality teachers table ------------------------------------ id(PK, A_I) | teacher_name ...
Baba Kamdev's user avatar
13 votes
2 answers
2k views

How to structure a model to properly and efficiently represent tree-like data on relational databases?

Based on Traversing tree-like data in a relational database using SQL question, I would like to know how the way regularly used to describe tree-like data on relational databases considering physical ...
Maniero's user avatar
  • 2,728
12 votes
6 answers
9k views

Explaining 2NF vs 3NF with an example

I have a problem with second normal form (2NF) and I have been unable to solve it by using Google. It is making me crazy because I am a teacher and I don't want to teach wrong stuff to my students. ...
finsalscollons's user avatar
11 votes
3 answers
2k views

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

I’m in a permanent discussion with the developers of the company where I work because they say it is better to get rid of relationship enforcement (via FOREIGN KEY constraint definitions) in a ...
ReynierPM's user avatar
  • 1,774
11 votes
2 answers
11k views

Is one-to-one relationship normalized?

Consider we have a large set of statistical data for a record; e.g. 20-30 INT columns. Is it better to keep the entire set in one table as they all belong to a record OR creating a another table ...
Googlebot's user avatar
  • 4,531
11 votes
2 answers
8k views

What are the practical reasons behind learning relational algebra?

Learning how to formulate query-like expressions in relational algebra is a traditional part of many, perhaps most, "Introduction to Databases" courses. This is usually justified by the assertion ...
Gordon Royle's user avatar
11 votes
5 answers
2k views

First Normal Form: Definitive Definition

I am trying to get a definitive version of what is First Normal Form. Everything I read has a slightly different spin. Many authorities, such as Date, say that by definition a relation is always in ...
Manngo's user avatar
  • 2,849
10 votes
2 answers
23k views

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

I have three tables in database: trips(trip_id(pk), trip_name(unique), user_id(fk)) places(place_id(pk), place_name(unique)) trips_places_asc(trip_id(fk), place_id(fk)) Since many trips can ...
xyz's user avatar
  • 257
10 votes
1 answer
5k views

Friends relations in MySQL

I'm developing a friendship relation in MySQL where friend relation is mutual. If A is friend of B, then B is friend of A. If one of the users end friendship then relation drops. I want to learn which ...
kent ilyuk's user avatar
10 votes
1 answer
1k views

Convert query in words to relational algebra

I have a follow-up question to a question I have previously asked on SO. Instead of the query from my initial question, I want to convert the following into relational algebra: List the names and ...
bawse's user avatar
  • 201
9 votes
2 answers
6k views

Is a table without a primary key normalized?

In a lecture, my lecturer showed us a table without a primary key. Upon questioning, he said that in 3NF when you remove transitive dependencies it's ok to then have a table without a primary key. ...
Alex's user avatar
  • 93
9 votes
3 answers
2k views

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

As I understand it, third normal form (3NF) basically means there should be exactly one key. If a table with say an auto-increment id column also has a column known to be unique and not null, eg ...
Bohemian's user avatar
  • 713
9 votes
3 answers
4k views

How does Yelp efficiently calculate distance in the database?

For example, say I have a table: Business(BusinessID, Lattitude, Longitude) All are indexed of course. Also there are 1 million records Say I want to find businesses closest to 106,5, for example, ...
user4951's user avatar
  • 1,365
9 votes
2 answers
1k views

How can I losslessly decompose this table?

I have a table with the following PK (primary key): child_id integer parent_id integer date datetime child_id and parent_id are FKs (foreign keys) to entity tables. The child table also ...
trevor's user avatar
  • 99
9 votes
2 answers
2k views

Best relational database structure for this data

I'm in the process of creating a database scheme for the following scenario: There are users Users have roles (such as "Developer" or "CEO") Roles have applications (such as "Topdesk") Applications ...
Luc's user avatar
  • 193
8 votes
5 answers
6k views

What normal form does a surrogate key violate?

I have following question: "What normal form does a surrogate key violate?" My thought was the 3rd normal form, but I'm not quite sure it's just an assumption i am making. Could someone explain that ...
Marco's user avatar
  • 91
8 votes
2 answers
21k views

MySQL Workbench, how to refresh EER diagram?

I'm using MySQL Workbench to design my MySQL database and model how entities would look like and how they would relate to each other. Now, sometimes I use Doctrine ORM. When using Doctrine ORM, I ...
otporan's user avatar
  • 203
8 votes
1 answer
8k views

MongoDB Manual References vs DBREF

I've been researching MongoDB and trying to see if it would be a fit for my company's product catalog. I came across database references: http://docs.mongodb.org/manual/reference/database-references/ ...
Darius's user avatar
  • 273
8 votes
2 answers
11k views

How are joins Commutative and Associative?

I have read all over the place that joins are associative and commutative. So A join (B join C) should be the same as (A join C) join B. But I have a really hard time understanding how this can be ...
Justin's user avatar
  • 183
8 votes
2 answers
8k views

Two relationships to same table

i have one transaction table, each transaction has 5-6 contacts involved. each contact has the same fields like name, address, cell, email etc. So the best thing I guess is to use the same contact ...
Ezi's user avatar
  • 652
8 votes
2 answers
3k views

What's a good relational structure for units and complex unit conversions?

My company is in the Energy industry and I need to come up with a good way to represent the conversion of units of measurement. I've done some searching and have yet to find a good article covering ...
Jaxidian's user avatar
  • 472
8 votes
3 answers
2k views

Definition for "logical difference"?

I'm currently reading "SQL and Relational Theory" by C.J. Date. And although I'm quite far in the book, I've got a few basic questions. I want to know what the term "logical difference" means, The ...
NomenNescio's user avatar
7 votes
2 answers
1k views

Does the concept of candidate key exist only in theory?

I know the concept of candidate key in RDBMS theory, but do candidate keys really exist in actual SQL engines? I mean is there any way to designate a particular column or set of columns as a candidate ...
RajS's user avatar
  • 249
7 votes
3 answers
2k views

What are object-relational databases, and why is this model necessary in spatial databases?

Over in GIS SE, many of us use ESRI geodatabases. ESRI describes geodatabases as being object-relational. What are object-relational databases, and why is it necessary to use this model in spatial ...
User1974's user avatar
  • 1,477
7 votes
4 answers
9k views

First Normal Form, why is it good and how does it reduce redundancy

I asked a similar question on SO and was advised to ask the type of question here. This is for a course on relational databases. A sample problem asks "how come every table in a relational database ...
Celeritas's user avatar
  • 893
7 votes
3 answers
2k views

How to express this constraint in a database schema?

I have the following functional dependencies which are in BCNF: a,b -> c a -> d b -> d With the additional constraint, that no a and b should be combined with a c, where a and b have ...
johannes's user avatar
  • 173
7 votes
1 answer
5k views

How to use a relational database to store all users who belong to a group, and all groups which a user belongs to?

This is a beginner question: A user can belong to many groups, and a group can contain many users. Let's say the two tables look something like this: user_id user_name user_email group_id ...
bernk's user avatar
  • 247
7 votes
4 answers
853 views

Database Design for updatable sequential records

I am working on creating a data model for storing data related to production tracking. I work for an engineering firm that models and analyzes data for our clients. There are several steps in the ...
Brian's user avatar
  • 215
6 votes
1 answer
774 views

Unused columns in WHERE clause

I found this query in our codebase: DELETE FROM "Foo" WHERE ("Foo"."Id", "Foo"."CreatedAt") IN (SELECT "f"."Id", "f".&...
lonix's user avatar
  • 195
6 votes
2 answers
1k views

What are the practical uses for relational calculus?

In my Database Design course we are learning both Relational Algebra and Relational Calculus. I can see where Relational Algebra could be useful since it is closely tied to SQL. Our professor said ...
joshuadrs's user avatar
6 votes
1 answer
10k views

Find the highest graded student using Tuple Relational Calculus

Suppose I have a table of students, containing their ID and their grade: ----------------- | id | grade | ----------------- | 1 | 83 | | 2 | 94 | | 3 | 92 | | 4 | 78 | ...
CodyBugstein's user avatar
6 votes
2 answers
24k views

How does normalization fix the three types of update anomalies?

I have been reading 'Database Systems: A practical approach to design, implementation and management' by Connolly and Begg in order to understand database normalization (chapter 14). The three DB ...
the_butler's user avatar
6 votes
3 answers
2k views

How to normalize Time Sheet data?

I am having a terrible time finding an appropriate way to store this data in a database. The current excel (simple) version looks something like this: https://i.sstatic.net/BEZOw.png Staff and ...
James's user avatar
  • 163
6 votes
3 answers
20k views

Relational Design - Multiple tables into one foreign key column?

Take a table that tracks meat purchases. It has a "meat_id" foreign key column to indicate what type of meat the purchase was. But, the different types of meat are unique in some way (such as USDA ...
tgxiii's user avatar
  • 255
6 votes
2 answers
5k views

Relational Design - One table, two foreign keys or Two tables, one foreign key each

Looking for some advice related to optimal design in the following scenario. There is a Cases table (represents cases of inventory) There is a LocationInventory table (represents locations with ...
crichavin's user avatar
  • 403
6 votes
1 answer
1k views

How should I model a worflow of status/activities ? (rules like "if is 3 go to 4, if is 4 go to 5" etc)

I'm trying to model a workflow (status and activities) to create "rules". Let me give you an example. The main "object" let's say are "vehicle requirements", I will have this table: idRequirement, ...
Allende's user avatar
  • 247
6 votes
2 answers
4k views

Difference between functional dependency and join dependency

By definition, a functional dependency is defined as Let X and Y be subsets of attributes of a relation R. A functional dependency X → Y holds over R if and only if for any instance r of R, ...
fer's user avatar
  • 91
6 votes
1 answer
418 views

Why should foreign keys be implemented in my relational database? [duplicate]

Earlier today, I sparked a rather unexpected debate with my professional peers on the topic of foreign keys in our company's databases. In a nutshell, a software engineer who is senior to me stated ...
nasukkin's user avatar
  • 161
5 votes
3 answers
3k views

Why Cassandra is not considered a relational database?

Read this answer Relational databases are based on the relational model, an intuitive, straightforward way of representing data in tables. In a relational database, each row in the table is a record ...
overexchange's user avatar

1
2 3 4 5
10