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.
442
questions
3
votes
1
answer
68
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 ...
- 133
0
votes
1
answer
28
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 ...
0
votes
0
answers
25
views
Best database relation for the two way location sharing
I am trying to create a database relation for location sharing app.The project should meet the following conditions.
A shares the location to B and they can see each other’s location
A shares the ...
1
vote
1
answer
26
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 ...
- 113
-4
votes
2
answers
89
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
1
answer
25
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 ...
- 1
2
votes
0
answers
38
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 ...
- 37
0
votes
1
answer
32
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 ...
- 101
6
votes
1
answer
742
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".&...
- 175
0
votes
0
answers
100
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 ...
1
vote
1
answer
106
views
Simple and Composite Candidate Key and Nulls
One of the differences between a candidate key and a primary key is that "candidate keys can contain nulls." I have been unable to find a more precise definition of what this means. One ...
- 119
0
votes
1
answer
70
views
Relational Database - How to handle many-to-many-to-many relation
I am creating an ERD where i have the following strucutre:
A tenant can have multiple users.
A user can be in multiple tenants.
A user has multiple permissions.
What is the best way to also have the ...
- 11
-1
votes
1
answer
134
views
Why can't Many to Many relationships exist in relational database management systems?
While reading Oracle 11g Sql by Joan Casteel, the author mentions that many to many relationships couldn't exist in relational databases. I was wondering what the reasoning behind this fact was.
- 11
1
vote
1
answer
240
views
How to insert various records from multiple tables with a condition
I am creating a relational database and I have 2 tables currently ( Staff and Student)
Staff Looks Like this
StaffID
Class
TotalPhonesConfiscated
1
S201
1
2
S202
0
3
S203
1
Student Looks like ...
- 13
1
vote
1
answer
14
views
Creating relations that makes sense in a many 2 many 2 many format or is the solution badly designed?
I'm trying to get better at databases and started a hobby project for this.
So this is the flow I'm trying to achieve.
A user can have one or more portfolio
A portfolio belongs to that user
A ...
- 113
-1
votes
1
answer
88
views
How can I prove / disprove If A ↠ B then AC ↠ B
If A ↠ B then AC ↠ B is it true or false and if so can have example why?
- 1
-2
votes
2
answers
48
views
Database schema - Set Limit values in correlation to groups and/or it's members
Provided there are pre-existing tables (diagram attached below). They each have numerous use-case as far as potentially related attributes. Still, I am at a roadblock following best practices with ...
0
votes
2
answers
36
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 ...
- 101
0
votes
0
answers
54
views
Is this a solid database design that passes 3rd normal form?
I am a tennis coach and created a small database to track student testing. I have several programs( beginner, intermediate, advanced). And Throughout the year/season, they are divided into four 8 ...
- 1
0
votes
1
answer
60
views
Data model for entities with a relationship to both guest users and authenticated Users
I am working on an application in which users can report security incidents.The idea is that everyone in an organisation has an account and can report an incident by logging in. Some of the users have ...
0
votes
1
answer
56
views
Must tuples of a relation and their referents be in a one-to-one relationship?
Figures 3.a and 3.b, section 1.4, page 381 of Edgar Codd’s 1970 landmark paper ‘A Relational Model of Data for Large Shared Data Banks’ presents a set of relation schemas (unnormalized in figure 3.a ...
- 71
0
votes
0
answers
78
views
Review of my Grade Calculator Database Design
I'm currently making a Grade Calculator which will be used by students of a university to calculate their own grades. I'd greatly appreciate feedback on the design I've made. Do you see any obvious ...
0
votes
2
answers
57
views
Unique relationship attribute for every pair of entities
I have entities "Student" and "Course" and they are connected with relationship many-to-many called "Exam". The relationship "Exam" has an atribute "Grade&...
- 3
0
votes
1
answer
72
views
How can I categorize entites by their age in an entity relational diagram?
I am designing an entity relational diagram and I need to categorize entities in groups by their age.
What I have is an entity of Person and some personal attributes about it. But I want to have ...
user226621
3
votes
2
answers
184
views
Why does metadata have to stored in table cells according to Codd's 12 rules
Among Codd's 12 rules for Relational Database Management Systems is Rule 1, which states the following:
Rule 1: Information rule
All information(including metadata) is to be
represented as stored ...
0
votes
0
answers
28
views
How does DBMS ensure durability without flushing
I was watching this video, and at a certain point (don't recall the exact minute) the speaker says that the DBMS doesn't immediately flush the file when updates happen. He explains that IO operations ...
- 115
1
vote
3
answers
277
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 -&...
- 152
0
votes
2
answers
1k
views
How to normalize a table with multiple cells that have multiple values?
So I'm doing a task where I take a massive list (30,000+) of movies on Wikipedia that have multiple columns (such as the films name, the genre, the cast, the plot etc.) and upload it into ...
- 13
1
vote
3
answers
155
views
Is it allowed to for two tables to have two types of relationships between them? [duplicate]
I have been going back and forth working with relational and no-sql databases for some time now and my questions may be the result of mixing the way of thinking about how to approach a problem.
So ...
- 119
0
votes
1
answer
104
views
Is Star/Snowflake database design good for applications? What is the alternative?
I need to solve an exercise for a pharmaceutical company, they ask me to create a database structure from scratch given a few informations.
Because I want it to be scalable and "future proof"...
- 1,165
-1
votes
1
answer
32
views
Job Opportunity and difficulty (or not) for me to learn to use msaccess and surveygizmo/alchemer [closed]
First, if my question fits better elsewhere, please advise.
My question is: Based on your experience with relational databases like MSaccess and perhaps survey tools like Alchemer (formerly Survey ...
0
votes
1
answer
1k
views
Immutable field/cell in Postgres
Is there a way to create an immutable field/cell in a Postgres record? AKA, only allow the field to be written once, perhaps just on insert?
Use case: I have a "uuid" field for a record, and ...
- 121
-1
votes
1
answer
21
views
Help designing a Table(s) with a single message to multiple placeholder values, with new placeholders added occasionally [closed]
The basic situation is I have a single 'message' table, and currently our design for this is a single row, with all placeholde values stored against the row at generation, so they are available for ...
- 3
4
votes
3
answers
2k
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 ...
- 159
0
votes
2
answers
406
views
database management system vs database vs tables vs file [duplicate]
I have been trying to understand the way a database system works. Digging through the internet, I have come to following picture.
Database system is required because we need a way to store data, ...
- 3
0
votes
1
answer
100
views
"Tuples in a weak entity set are partitioned according to their relationship with tuples in a strong entity" - meaning?
http://web.cs.ucla.edu/classes/fall04/cs143/solutions/chap2.pdf
In the above link, there is the following statement - "Tuples in a weak entity set are partitioned according to their relationship ...
2
votes
1
answer
590
views
Incorrect relationships formed in EER diagram by reverse engineering the database script
I have a database with 6 tables. I tried to view the relationships between the tables by using EER diagram. I observed that all the one-to-one relationships were converted into one-to-many ...
- 129
0
votes
1
answer
1k
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 ...
- 101
3
votes
2
answers
738
views
One and only one relationship - ER diagram to relational schema
I'm taking this term a DB course as part of my CS studies.
This week we began studying the subject of ER diagrams, which I find a bit confusing.
I have some uncertainty about the picture above (Note: ...
- 33
0
votes
2
answers
53
views
Multiple Primary-Foreign Key connections between Tables Redundant?
I have two tables, Reviews and Critic
I'm pretty new to RDBMS and I was wondering if the connection between Review.rID and Critic.Review is redundant?
My reasoning is that a single critic can have ...
2
votes
1
answer
82
views
In what cases is using ClickHouseDb and the like a neccessity?
An open source for website analytics - https://github.com/plausible/analytics
They use Postgresql and ClickHouseDb.
When it comes to web analytics, there're tons of events going that need to be ...
- 23
-1
votes
4
answers
251
views
How to store a (record which holds a) reference to any other column (attribute) in another table (relation)
TL;DR: If the database schema should hold all the business logic, how is it be possible to specify that an attribute type is a reference to a specific attribute, instead of a specific record (as is ...
0
votes
1
answer
26
views
How to add an additional table over two one to many relations tables?
I have two tables TableA and TableB which have a relation of one to many
Later I am told that there must be an additional table that links the initial tables with additional information. To carry ...
- 103
0
votes
0
answers
30
views
Can a table be un-normalizable or am I misunderstanding the theory
I was considering normalizing a flat table (SQLite if its relevant) I'd been working with in a small personal project and it just didn't seem to make sense.
Consider an example table like below. Each ...
- 1
1
vote
2
answers
924
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 ...
- 113
1
vote
1
answer
57
views
How can I calculate the cost of the query, if there is a `between` condition?
How can I calculate the cost of the query, if there is a between condition?
Select *
From A
Where A.id between 10 and 50
If Index not exists and there is only one condition:
Search in B+ tree to ...
- 197
3
votes
2
answers
831
views
Incorrect result when using 'case when count()=0' & 'where'
I would very appreciate if someone could help me out with this query.
I can't seem to get the correct result when using case when count()=0 & where
I work with SQL Server, with a DB of a system ...
- 197
1
vote
1
answer
33
views
Storage strategy for combination of all table items
Let's say I've one table with items with the following fields:
id_item
name
description
& one table with the user information, following like:
id_user
email
name
Now I'd like to let vote my ...
- 11
2
votes
3
answers
2k
views
How do you determine the direction of a relationship?
Suppose I have the following relationship diagram:
How would you describe the relationship determined by the red line?
Reading from left to right you would say each order has only one customer so it ...
- 23
1
vote
1
answer
45
views
Is it possible to enforce corresponding hierarchies?
I constructed the following toy example to illustrate my question.
The left side of the schema describes a an abstract product consisting of its parts.
The right side of the schema describes actual ...
- 141