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.
453
questions
0
votes
0
answers
10
views
How to decompose this relation (A,B,C) with 1MVD (A-->>B), 1FD (AB ->C) to comply with 4NF?
As the title, how to decompose this relation (A,B,C) having one MVD (A-->>B), one functional dependency AB ->C to comply with 4NF?
I think it's like impossible ? Is that correct? because ...
- 101
1
vote
1
answer
1k
views
Finding all possible minimal covers
I have a relation schema R = {A, B, C} and the following functional dependencies:
A → B
A → C
B → A
B → C
C → A
C → B
How many different minimal covers can I derive from this relation schema? I have ...
CommunityBot
- 1
0
votes
1
answer
2k
views
Data Schema For Stock Control / Multi Source Inventory
I'm working on a project that involves stock control with multiple stock sources and sales channels. The overall hierarchy I've got so far looks like this;
Sales Channels <---- Allocated Stock ...
CommunityBot
- 1
0
votes
1
answer
53
views
Calculating memory size required for an index
Assume I have the following table:
CREATE TABLE t1 (
id int NOT NULL PRIMARY KEY,
rank int NOT NULL,
phrase varchar(100) NOT NULL
);
with 10 billion records in a relational database like ...
- 1,640
0
votes
2
answers
309
views
Database: Splitting tables based on logically grouped optional columns
I keep having this argument with my management about splitting the tables. I prefer splitting a database table to multiple tables based on attributes that can be grouped together. The columns which ...
CommunityBot
- 1
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 ...
- 774
0
votes
2
answers
72
views
How do I identify the SQL server brand via SQL queries
A 3rd party API exposes a endpoint which accepts "SQL" queries. Their examples are simple SELECT statements, however I want to write more complicated queries.
They do not specify which SQL ...
- 26k
0
votes
0
answers
44
views
How to create a schema for a combination of values that needs to be unique
How would I create a schema for the following data:
Dimension A
Attribute X, Attribute Y
positive , positive => Description 1
positive , negative => Description 2
negative , ...
- 256
5
votes
3
answers
5k
views
Database Design - One table for different types of transactions
Considering a procurement system as an example. Would it make sense to have one single table for the following: Quote, Order, Invoice and Credit Memo?
There will be some details that are different. ...
- 1
1
vote
1
answer
60
views
building this not-so-necessary relationship is a good approach?
I created a database for an app, where I have several networks, which have several nodes and elements and also several scenarios, all related to the networks. [see picture below]
My question is: since ...
- 51.7k
0
votes
3
answers
32
views
Which would be best for a scalable environment?
Which is more advantageous, speaking of performance, and best practices?
A table of customers and a table of suppliers and a table of seller
Or
A table that covers the three entities having a field ...
- 218
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 ...
- 774
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 ...
- 84.1k
13
votes
3
answers
153k
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 ...
-2
votes
2
answers
81
views
Why is it a bad idea to have multiple values in a column in a relational database?
One problem I've found is slow database queries. If I had a table that had a column titled customer and another column titled grocery_items that contained multiple grocery items, then a simple query ...
- 8,726
0
votes
1
answer
47
views
What does relational capabilities mean in Codd's Foundation rule?
From Codd's 12 rules (emphasis mine):
For any system that is advertised as, or claimed to be, a relational
data base management system, that system must be able to manage data
bases entirely through ...
- 218
0
votes
2
answers
74
views
What is the reason for the graph dbms being better at friend-of-a-friend queries than relational dbms?
I often read that graph dbms are well suited for social networks. So for example, followers on instagram or linkedin. I read that the SQL dbms performance can degrade if the query involves multiple ...
- 12.9k
2
votes
1
answer
312
views
SQL query not returning all results when too much data
I am a software engineer with a little knowledge about what's going on under the hood of a dbms.
The query presented below finds mutually followed people by two people:
SELECT followed.id
FROM person ...
- 1,640
0
votes
0
answers
55
views
Functional Dependencies and Armstrong notation fundementals [duplicate]
if AB -> C and B -> A does that mean B -> C?
B -> B is trivial and if we do composition with B->A, it becomes (B-> BA), then by transitivity does that mean B->C? but if B -> C ...
0
votes
1
answer
430
views
Correct entity relationship diagram representation of a normalized table with a NOT NULL foreign key
Intro
Hi everyone, this is mainly a classification/theoretical question on the topic of inheritance and normalization techniques in database design and their appropriate representation in entity ...
- 797
0
votes
1
answer
62
views
Is it good practice to join many tables together [User Data Design]?
I'm trying to design a database for storing information about users--say name, email, GPA, classes, and more information in the future.
I believe the proper design involves storing each field as a row ...
- 140
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 ...
- 774
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 ...
- 774
1
vote
1
answer
375
views
In terms of relational databases is a Relvar the equivalent to a Schema?
I've been reading up on relational databases and the term Relvar and schema keeps coming up. From my understanding:
A relvar is a variable holding a relation.
A schema is a logical definition of a ...
CommunityBot
- 1
0
votes
0
answers
130
views
Better way to store stock levels for products?
I currently have 3 tables: locations, products, and stock, which are pretty self-explanatory:
The fields of stock are location_id, product_id, and quantity, which is great... until I add a new ...
- 131
1
vote
1
answer
37
views
Two types of one-to-many relations
first time poster here, and happy 2023!
When talking about different types of entities relationships, I tend to think the following three:
one-to-one
one-to-many / many-to-one
many-to-many
However, ...
- 46.5k
0
votes
2
answers
207
views
Determine Primary and Foreign Key for two tables
I have 2 tables, that is
Patient Treatment
Datetime
Doctor
Patient
Complaint
Description
Medicine
Address
1/1/22 17:05
Name A
Patient K
X
X
X
X
1/1/22 17:11
Name C
Patient L
X
X
X
X
1/1/22 17:27
...
- 78.1k
1
vote
1
answer
264
views
Set Default Value of Column Equal to PK of inserted record, and use it as Foreign Key Reference
I'm using MariaDB 10.3, and what I'm trying to do is an establishment of a 1-to-n relationship as for example specified here in the section "One-To-Many-Relationship". I'm trying to do so ...
- 163
2
votes
0
answers
46
views
For selling items in a game, should the shop and item tables be merged?
I'm developing a game that uses a Postgres database. In the game players can have many different items, and each player can have multiples of an item (example: 200 apples). My item table currently ...
- 121
4
votes
2
answers
6k
views
Relational Model Design - Entity with one attribute
I am trying to design a database relational model for an assignment. First, I design the ER diagram, and then the relational model with tables (schemas).
Here's my question: Is it possible for an ...
- 1
0
votes
1
answer
49
views
How can I transform an exclusive constraint relationship from ER to RM model?
Let say we have two entities TEACHER and COURSE. There are two relationships between them. A TEACHER can teaches a COURSE, and also can learn a COURSE, however, the TEACHER cannot teach and learn a ...
CommunityBot
- 1
-2
votes
3
answers
956
views
Does it make sense to use a non-relational database for reporting/BI/visualisation tools?
Relational databases are well used in reporting/visualisation tools such as Power BI, Tableau...
Non-relational databases are getting popular and many reporting tools allow its connection. However I ...
0
votes
0
answers
47
views
Is there a formalism for the kinds of constraints we can implement with indexed-views? Or at least a rule-of-thumb?
SQL Server’s Indexed Views provide a performant way to implement certain classes of database-scoped constraints - such as ensuring a value is unique over multiple tables - or other non-key, cross-...
- 574
0
votes
0
answers
128
views
How to design a versionable and publishable entity?
I have encountered this problem a couple times. I have an entity that has versions and only one of those versions is the published version. As an example, let's say we have the entities Book and Book ...
- 1
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 ...
- 8,520
3
votes
1
answer
647
views
If an entity's primary key must contain a foreign key, does that make the entity weak?
I'm currently learning about strong and weak entity types in the context of RDBMS. While reading, I came across this statement:
"A weak entity is dependent on a strong entity to ensure its ...
- 46.5k
4
votes
2
answers
295
views
The key of the relation resulting from a full outer join
What is the key of the relation resulting from a full outer join? If there is no key, how is it said that the relational algebra is closed under its operators and the result of each operator is also a ...
- 2,201
0
votes
1
answer
48
views
Postgres: How to express Many-tables to Many-tables relationship?
What I have
I have a schema of listeners defined like so:
create table listeners.ethereum (
id serial primary key not null
);
create table listeners.solana (
id serial primary key not null
);
I ...
- 1,560
1
vote
1
answer
492
views
How to implement disjoint (nonoverlapping) subtype with total completeness in MongoDB?
I designed an entity relationship diagram (ERD) with the goal of creating two child entities (INSTITUTION and SPACECRAFT) of a parent entity (LOCATION), which is distinguished depending on the value ...
- 440
3
votes
1
answer
153
views
Table Structure: 2 Related Entities Share the Same Entity
I was hoping I could get some advice on my table structure. Im writing a simple workout tracker for me and my buddies. These are the requirements:
Multiple users
A user can have multiple workouts
A ...
CommunityBot
- 1
0
votes
1
answer
76
views
How to design a database that merges two similar others?
Disclaimer : I'm new to relational databases, I'm using mysql.
I have two SQL databases which have about 70% tables in common, in which about 70% of fields are common. It could make sense to have only ...
- 286
0
votes
1
answer
116
views
Create one-to-many to two fields in different table
I have a database where I am trying to figure out how to join a table that contains an object called ModelType. The calibration table contains two fields (EffModel and BkgModel) that are based on a ...
CommunityBot
- 1
3
votes
2
answers
4k
views
Delete entry from related tables
I can not use InnoDB engine because of some dependency troubles in LuaSQL and PtokaX.
My current query is something like:
DELETE e.*, m.*
FROM entries e
INNER JOIN magnets m
ON m.eid = e.id
...
1
vote
1
answer
146
views
Keys of a relation
I got relation:
R = { (ABCDEF) , (A B F -> C, B C -> F, F -> A, F -> B, D E -> E, E -> D)}
keys of this relation are:
{BCE}, {EF}
But I don't get it... why? Why not only {EF}.. it got less ...
CommunityBot
- 1
0
votes
1
answer
112
views
Schema check or "DATE as separate table"
I'm dusting of my SQL, and would love to know if I've got my mental ER-diagram right.
http://sqlfiddle.com/#!2/c5c59
Basically, I've got 10-15 different datasets (actividades) from several locations ...
CommunityBot
- 1
2
votes
0
answers
57
views
In a multi-level one-to-many relationship, is it appropriate for the lowest entity to record all IDs in the upper layer?
Top level - company
create table company(
id unsigned tinyint not null auto_increment,
parent_id unsigned tinyint,
)
Next level - department
create table department(
id unsigned smallint not ...
- 46.5k
0
votes
1
answer
34
views
Why would an "articles" table (with no relationship to any other table) have a primary key?
Let's say that I have an articles table that have two columns: title and content, and let's say that this articles table don't have any relationship with any other table.
Why would this articles table ...
- 37.5k
6
votes
1
answer
773
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".&...
- 51.7k
2
votes
1
answer
808
views
If A and B are candidate keys, then AB is candidate key or not ?
If R{abc} is a relation, f{ a->b b->a a->c b->c }, then a and b are candidate keys. What about ab ? Is it a candidate key or is it a super key or both ?
- 24.8k
0
votes
0
answers
328
views
Weak Entity with Primary Key?
I have the following E-R diagram to refer to:
I have a question posed to me that entity 4 IS a weak entity due to one of its relations. However, from what I see, entity 4 has a composite primary key ...
