Questions tagged [rdbms]
Relational Database Management System, a widely used type of database management system characterised by its extensive use of joining as a core operational principle.
225 questions
0
votes
2
answers
110
views
How can a transaction view uncommitted changes made by another transaction in UNCOMMITTED READ
I know this is probably a silly question, It is my first, It seems so fundamental that I can't find the answer anywhere, because it must be so straight forward no one explained it.
I want to ...
- 103
0
votes
0
answers
21
views
Parent and child vs. flat structure in transactions
I am designing a system that records stock transactions for a product. Some product types use serial numbers for traceability and some products types do not serialize items.
When recording stock ...
- 1
0
votes
0
answers
31
views
Supplier's Products and Internal Products - Separate Product Tables?
I'm building an ERP system where we can import our suppliers' product data before purchasing their products. Under the current design, supplier products are stored in a separate table and when we ...
- 1
1
vote
0
answers
29
views
Composite key with a auto-incremented column
I'm using Oracle, but this may apply to other Relational DBs, as well.
Suppose I have a table T with columns C1, C2, C3, C4. Note: C4 is numeric.
Is it possible to make C2, C3, and C4 a composite key ...
- 111
0
votes
1
answer
54
views
Difference between Authentication, session, outer and current user in postgres
I know about the users in postgres but don't have correct idea about them.
The difference I know is
Authenticated user - The user who enters his credentials to login into postgres database.
Session ...
- 55
-1
votes
2
answers
60
views
RDBMS vs NoSQL decision making
I am pretty novice when it comes to data store, and want to learn few tricks.
I want to know how to decide between RDBMS and a NoSQL store.
Lets take example of a system where data size is pretty big, ...
- 123
0
votes
1
answer
121
views
What is the performance implication of composite index column ordering on inserts?
I have a table with 4 columns, in which one of them is primary key, one is time_created, ID of the upstream service (say ID) and the type of upstream service (say service_name). This table is append ...
- 1
2
votes
1
answer
200
views
Use case of ALTER EXTENSION ADD/DROP in upgrade script of extensions
I am recently looking at the pg_stat_statements upgrade script and found out these set of commands here
/* First we have to remove them from the extension */
ALTER EXTENSION pg_stat_statements DROP ...
- 55
-3
votes
1
answer
216
views
Getting in this error what i have to change Column check constraint 'machine_chk_2' references other [closed]
CREATE TABLE machine.machine (
m_id INT PRIMARY KEY,
mname VARCHAR(20) NOT NULL,
mprice FLOAT CHECK (mprice > 0),
mcost FLOAT CHECK (mcost < mprice)
);
2
votes
0
answers
39
views
Where sk = null clause gives an error when used in a query but not in a stored procedure in teradata
In teradata sql I get an error when I use
sel *
from table
where sk = null
However if I include this in a stored procedure and pass a null value in the parameter I don't.
replace procedure(In name ...
- 21
1
vote
1
answer
53
views
Why is the following decomposition into 2NF wrong?
Consider the relation R(A, B, C, D, E), with the functional dependencies being {BE->A, D->E}. What is R's decomposition in 2NF?
The answer is R1(D, E); R2(B, E, A); R3(B, C, D)
I've derived that ...
watson
-3
votes
1
answer
66
views
MYSQL workbench like gui tool for oracle 10 g or more
I am a java full stack developer who want to write oracle sql queries to simplify writing is there any mysql workbench like similar gui tool for oracle by oracle or any other please sugest
1
vote
1
answer
495
views
What makes many NoSQL databases non-ACID compliant when both SQL and NoSQL can scale horizontally
A lot of the difference between SQL and NoSQL that is talked about focuses on the fact that SQL support ACID properties while in many NoSQL dbs ACID is compromised.
I'm unsure of the reason but ...
- 127
0
votes
1
answer
419
views
NoSQL vs SQL during sharding when a single read operation only hits one shard
I've a use case where I'm stuck between figuring out whether to use SQL or NoSQL db.
The db has 2 fields - A (PK), B
A is 8 char-long, while B is 100-400 char long.
The operations are simply:
Write: ...
- 127
-2
votes
2
answers
174
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 ...
2
votes
2
answers
152
views
How is data read in Cassandra when some columns are not mandatory compared to RDBMS?
I am not sure how to put my question in a proper databse terminology. But here it is.
My understanding of data reading in relational database:
In relational databases, as all columns are present, so ...
- 123
1
vote
1
answer
33
views
RDBMS performance on updating an index key(non primary)
I am creating a scheduler service for which I am using RDBMS(Mariadb, engine:InnoDB), which has following schema for jobs table
CREATE TABLE `jobs` (
`id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,...
- 111
0
votes
0
answers
222
views
Top reasons for temp tablespace to go offline
It was marked offline and it is fixed already.
We want to know the list of causes that marks the temp tablespace offline.
0
votes
1
answer
52
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 ...
- 131
0
votes
2
answers
905
views
What's inside a redo record in Oracle's Redo log?
While reading the Redo log wiki page, I was confronted with the following statement:
For example, if a user UPDATEs a salary-value in a table containing employee-related data, the DBMS generates a ...
- 131
0
votes
1
answer
42
views
In RDBMS(MySQL..), the more complexity was added to the query, the higher time the query will take?
The complexity includes these factors:
Number of joined tables
Number of subqueries
Number of aggregate functions
Number of groups by, the columns in group by, the window functions
Number of where ...
- 3
1
vote
2
answers
460
views
Update Ledger Table In double entry accounting when there are millions of entries
I am creating an double entry accounting software where I have ledger table along with other tables. My cash account ledger table for is similar to following:
ledger_id
ledger_name
...
2
votes
1
answer
2k
views
What difference between columnar and wide column databases?
What difference between columnar database (AWS Redshift, Azure Synapse, GCP Bigquery, Snowflake) and wide column database (AWS Keyspaces, Azure CosmosDB, GCP Bigtable, Hbase, Cassandra)?
What I ...
- 71
1
vote
1
answer
62
views
How does MySQL maintain confidentiality of files?
In a DBMS like MySQL, are the underlying files that contain the data encrypted since we want to have role and user based control? If they are encrypted, what encryption keys and algorithms are used? ...
- 111
-1
votes
1
answer
305
views
How to handle a constantly changing schemas and tightly coupled entities (ORM hell)?
Essentially my team's ended up in a situation where Object A, B, C, D, and E (greatly simplified *sigh*) are different Java objects and each is represented by a single table interconnected with other ...
- 99
1
vote
2
answers
826
views
MySQL distinct select for emoji always return 1 emoji out of 2
I have these emojis stored in a reaction column. 😏, 😇
If I run this query:
SELECT DISTINCT reaction
FROM chat_reactions
WHERE chat_id = 593
it only selects the first one. My column's collation ...
- 111
2
votes
1
answer
108
views
Are there better alternatives to a time series databases for managing financial transactions?
We are considering using AWS Timestream to store financial transactions from various sources for users. Any solution needs to be able to support ~4k transactions per user per year for 1 million users. ...
- 21
0
votes
1
answer
106
views
About Transaction knowledge
Am teaching myself SQL. And I never worked in any enterprise company but I have watched and learned some tutorials about web development and I see crud applications used to inject data into a database ...
-2
votes
1
answer
668
views
What prevents relational databases from being optimized for writes rather than reads?
I noticed that most of the popular relational databases systems such as PostgreSQL, MySQL, MariaDB, SQLite, etc. are oriented towards reads rather than writes. It seems that many of these database ...
- 107
0
votes
1
answer
338
views
How many keys in mySql are too many?
I am currently designing a MySQL database that has 212 tables. Some of the tables has 5-7 foreign keys. Is this going to hamper database query performance in the long run? How many foreign keys are ...
0
votes
1
answer
57
views
In general if you were to index all columns would that be the same as indexing no columns?
The title likely sums up my question, but to be clear, I'm specifically thinking RDBs.
I have no intention of doing this, I've just been curious and haven't found a clear answer in my attempts at ...
- 127
9
votes
3
answers
6k
views
Why is it more difficult to horizontally scale a relational database than a NoSQL database like MongoDB?
Is the main reason for the difficulty in horizontally scaling / distributing an RDBMS because of an adherence to ACID transactions? Is it the fact that multiple tables are so interconnected, or ...
- 201
0
votes
0
answers
20
views
Should I use many database in one application?
Currently I am working on a project of a management software. This software is currently in built with an old technology (Codeigniter-PHP). My job is to convert this software to latest technology (...
1
vote
3
answers
519
views
SQL or/with Nosql
I am designing a mobile app with basic functionality such as sign-up, Auth, posts, real time messages etc. I think for this kind of work Nosql should work better. But over a long period of time I want ...
- 23
2
votes
1
answer
3k
views
Why do we need a Unique key when we have a Primary key?
I tried to Google this question but all I found were differences between primary and unique keys, why do we actually need a unique key when we have a primary key that can identify any single record, ...
- 31
0
votes
0
answers
464
views
E-R diagram 1-to-1 mandatory relationship for two superclass entities to relational schema
How are two superclass entities that are connected to a mandatory 1-to-1 relationship converted to relational schemas? These two superclass entities are also abstract, meaning that they will not have ...
- 101
0
votes
1
answer
262
views
Right way to store several addresses and select one as main/principal
I'm working in a little model and I'm struggling with a simple question.
How should I store a list of addresses/mails/phones and select one as principal?
Example I'm thinking now.
AddressEntity:
Id
...
- 103
1
vote
0
answers
55
views
Is it a bad idea to make redundant rows when 2 columns reference to the same table?
Suppose we have this database design
Users table:
- id
- name
Friends table:
- id
- user_id
- friend_user_id
Let's say USER 1 befriended USER 2, this is what my application logic will execute:
...
- 111
0
votes
2
answers
449
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
1
answer
1k
views
create a trigger to update status on parent table based on values of child table
Table order
columns - id status
1 confirmed
Table trays
columns - id,orderid(fk),status
1 1 done
2 1 processing
I have to update the status of ...
- 25
0
votes
0
answers
53
views
RDB data combination table modeling
There's data and if I combine it, I need to get the corresponding result.
If I choose A and I got B,
If I choose B, I should also get A.
I want to save both data and combinations in DB, but it's hard ...
- 1
0
votes
1
answer
94
views
Relational or non-relational when dealing with highly relational, big data [closed]
I'm working on a project (in Java) that will be processing a large event stream (GBs every minute). This data is heavily processed to find relationships between events. This is done through a ...
- 103
3
votes
1
answer
615
views
Is using of subqueries more efficient than using Limit offset and order by in MySQL?
For instance, we have a problem of finding the employee with the second highest salary in the table.
This is my table
id name dept salary
1 Ram HR 10000
2 Amrit MRKT 20000
3 ...
- 93
4
votes
3
answers
592
views
Inconsistency in Relational Databases
I generally get confused when the term consistency is used. NoSQL tutorials always refer to the reads whereas Relational tutorials refer to a consistent state (ensuring referential integrity ...
- 161
0
votes
1
answer
1k
views
MS Northwind Traders DB Inventory Transactions Entity relationship question
Trying to understand Northwind Traders' db schema but I cannot comprehend the relationship between:
Inventory Transactions<--->Purchase Order details
Inventory Transactions<--->Order ...
- 1
0
votes
1
answer
175
views
What could be the best DBMS to store airbnb data for price prediction [closed]
I have a school project in which I need to predict the airbnb listings prices in 5 different cities.
So I have about 400,000 records each with 19 columns.
All the examples on the internet that are ...
- 103
26
votes
4
answers
7k
views
If NoSQL stands for "Not only SQL", is SQL a subset of NoSQL?
The definition is a bit confusing - basically I'm asking if SQL is a subset of the NoSQl family:
I'm asking this because "Not-only" means NoSQL is much larger, but still includes SQL as a ...
- 483
1
vote
2
answers
2k
views
Best practice for "recycling" rows on PostgreSQL (UPDATE vs INSERT + DELETE)
I searched responses on "UPDATE vs INSERT + DELETE" in general terms and the consensus is UPDATE is faster, as expected.
My question is centered on a somewhat niche use case. Let's say you ...
- 111
0
votes
0
answers
57
views
Suggestion for NoSQL database for global users
We are planning to build a social media application, lets say similar to Instagram but at much smaller scale, of course.
The users could be from many geographies like US, Europe, Asia who could ...
- 101
1
vote
1
answer
64
views
How to get the row count from a table related to other table? [closed]
I am new to mysql and database concepts and I will try to explain my question through these two minimal tables, here is the scenario for which I need to write a query,
Table : Fruits
| FruitId | ...
- 13