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
0 votes
2 answers
348 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 ...
0 votes
1 answer
28 views

primary key referened by only single foreign key of another table among multiple tables that can reference it

Let's say I have table Account that contains username and password of users to login:- account ( account_id BIGINT PRIMARY KEY, username VARCHAR(30), password VARCHAR(30) ) -- I have two ...
0 votes
0 answers
16 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 ...
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 ...
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 ...
0 votes
1 answer
68 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 ...
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 ...
0 votes
2 answers
76 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 ...
0 votes
0 answers
45 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 , ...
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 vote
1 answer
63 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 ...
0 votes
3 answers
33 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 ...
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 ...
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 ...
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 ...
-2 votes
2 answers
95 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 ...
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 ...
0 votes
2 answers
84 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 ...
2 votes
1 answer
387 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 ...
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
517 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 ...
0 votes
1 answer
63 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 ...
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 ...
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 ...
1 vote
1 answer
380 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 ...
0 votes
0 answers
143 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 ...
1 vote
1 answer
43 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, ...
0 votes
2 answers
222 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 ...
1 vote
1 answer
315 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 ...
2 votes
0 answers
47 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 ...
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 ...
0 votes
1 answer
53 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 ...
-2 votes
3 answers
1k 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
48 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-...
0 votes
0 answers
143 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 ...
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 ...
3 votes
1 answer
718 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 ...
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 ...
0 votes
1 answer
49 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 vote
1 answer
543 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 ...
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 ...
0 votes
1 answer
77 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 ...
0 votes
1 answer
119 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 ...
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
148 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 ...
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 ...
2 votes
0 answers
62 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 ...
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 ...
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".&...
2 votes
1 answer
815 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 ?

1
2 3 4 5
10