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
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
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
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
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
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
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
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
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
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
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
2 votes
3 answers
1k views

Heterogeneous ordered list of value

I have the following object model: class A { List<Thing> orderedListOfThings; } interface Thing { } class X implements Thing { int attr; } class Y implements Thing { String ...
gontard's user avatar
  • 23
1 vote
2 answers
3k views

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

Fundamental question that I cannot seem to find an answer on. I have a database that stores line items from receipts along with the username and receipt number. Right now the lineItem column is just ...
Bunny's user avatar
  • 113
1 vote
2 answers
8k views

Find the highest graded student in each state, using Relational Algebra

Suppose I have a table of students, containing their ID, grade and state: ------------------------- | id | grade | state | ------------------------ | 1 | 83 | CA | | 2 | 94 | TX ...
SS''s user avatar
  • 155
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
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
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
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
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
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
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
5 votes
1 answer
7k views

Foreign Keys with Composite Key

Imagine I have a table with the following dependency: (Name, BoughtFrom, TimeBought) --> Cost Where the three attributes in bold form a composite primary key for the table. Now then, I want to ...
J86's user avatar
  • 331
4 votes
2 answers
283 views

Is combining independent tables called normalisation?

So, at workplace, we currently store our audit logs in different tables, depending on what it is e.g. login/access info, configuration changes, etc. These are all independent data with no foreign ...
yihtserns's user avatar
  • 143
2 votes
1 answer
219 views

Is sql_variant compatible with First Normal Form (1NF)?

1NF rule says that we should not keep data of different types in one single column. Does it mean that sql_variant is not compatible with first normal form hence should not be used?
igelr's user avatar
  • 2,132
2 votes
1 answer
5k views

3NF Decomposition - I'm not sure about this example

I am trying to make sense of an example of 3NF decomposition using the 4-step algorithm mentioned by Ullman here, but I'm not understanding what my lecturer is doing with the last step (or, worse, I'm ...
Tobia Tesan's user avatar
2 votes
2 answers
2k views

Dependency preserving decomposition

Let be the following relational scheme: F={AB → C; AC → B; AD → E; B → D; BC → A;E → G} Is the following decomposition without loss of dependency? R¹=(A,B),R²=(B,C),R³=(A,B,D,E),R⁴=(E,G) ...
Revolucion for Monica's user avatar
1 vote
3 answers
386 views

Does relational design discourage splitting tables unnecessarily?

It's been a long time since I studied relational design, but I had a vague memory that it encourages not splitting a table unnecessarily. For instance, given the functional dependencies K -> A K -&...
bluenote10's user avatar
1 vote
1 answer
86 views

Effectively modelling extended classes

I have 3 different types of content for a website, 'Article', 'Video', 'Review'. Each of those three types of content contain common fields and type specific fields. For this, I have created the ...
Aaron Murray's user avatar
1 vote
1 answer
3k views

Relational algebra question

Here is the relational schema given : employee (person-name , street, city) works (person-name, company-name, salary) company (company-name, city) manages (person-name, manager-name) Q: Find the ...
Mr.Anubis's user avatar
  • 113
0 votes
2 answers
811 views

Parent/child relationship or not if the name of the field implies the relationship?

I am working with the harmonized system and try to catalog this in a MySQL database. This works for example like this: 01 LIVE ANIMALS 0101 horses, asses, mules and hinnies, live 0102 bovine ...
please delete me's user avatar
0 votes
1 answer
433 views

sub and sub-sub item organization system [duplicate]

So I am looking to find the best database design for the following situation. I will refer to my entities as 'articles' as that would best/most-easily describe them. So the articles have already ...
ambe5960's user avatar
  • 101
0 votes
2 answers
775 views

Help with preventing double bookings with variable lengths

I'm designing a sample database for a venue where members can create bookings for venues. I'm looking for a method to prevent double bookings (possibly by creating a button on a form that checks ...
Illian's user avatar
  • 1
-1 votes
2 answers
783 views

What are the possible risks involved in partitioning large database tables? [closed]

There has been much talk about the benefits of table partitioning in Relational DBMS. I would like to know: Are there any risks to table partitioning ? What measures are there to take to minimize ...
Computernerd's user avatar