Questions tagged [database-agnostic]
Only use this tag when the question does not depend on a particular database management system.
76 questions
3
votes
5
answers
2k
views
How to write an SQL query where the count of 2 different attributes is the same?
I created two different tables and then decided to put them (=) to each other however, what I am confused about is whether (=) is allowed to be used like that. I tried using IN but I didn't know where ...
- 39
1
vote
2
answers
77
views
Correct (as in normalized) way to express a relationship between 3 tables with complex(?) constraints?
I'll be using Postgres for the examples, but feel free to show examples in other databases if needed.
The simplified schema:
create table factory (
id serial primary key,
detail text not null
);
...
- 21
0
votes
1
answer
41
views
Are functional depencies with boolean conditions a thing?
Suppose we have a relation R(A, B, C) with the FD A -> B. Let's say that, for two tuples t and u in R, if both t[C] and u[C] satisfy a boolean condition - say less than a constant C - then t[B] = u[...
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
1
vote
2
answers
80
views
Does clustered index fragmentation happen in relational DBMS?
Is it possible that after a certain pattern of random inserts and deletes leaf data nodes become fragmented with clustered index?
I.e, that the physical order does not reflect the logical order (by ...
0
votes
2
answers
71
views
Join and use most recent data if NULL
Table WSHOSHO
SELECT * FROM WSHOSHO;
Table RRP
SELECT * FROM RRP;
Join to illustrate issue
The results of this join can be used to illustrate the issue:
SELECT * FROM WSHOSHO RIGHT JOIN RRP ON (...
- 165
1
vote
1
answer
113
views
What is the use of the order by ASC keyword in an SQL query?
As most, if not all, implementation of SQL use the ASC keyword by default in an ORDER BY clause when it is omitted.
Are there valid use case for the ASC keyword?
I am NOT asking for psychological ...
- 119
4
votes
1
answer
809
views
How do databases guarantee that two transactions with different isolation levels run concurrently correctly
I wonder how databases guarantee that two transactions with different isolation levels run concurrently correctly. That is, different sessions are allowed to use different isolation levels. For ...
- 49
3
votes
2
answers
2k
views
Read-only transaction anomaly
I've learned about this anomaly from these papers: https://www.cs.umb.edu/~poneil/ROAnom.pdf (original) https://johann.schleier-smith.com/blog/2016/01/06/analyzing-a-read-only-transaction-anomaly-...
- 41
0
votes
1
answer
30
views
Join implementation question
I have a question regarding how joins are implemented. I understand that the specifics will depend on the exact DBMS and the indexes in the system, but I think that the question is general enough to ...
-2
votes
1
answer
407
views
Why do we have to use IS NULL instead of = NULL? [duplicate]
I don't know if this is the case for all database implementations but it is for SQL Server and MySQL which are the two I've worked with.
I've read articles about the topic and the manuals of SQL ...
- 113
0
votes
1
answer
152
views
Index on Multiple Dimensions [duplicate]
I've recently started reading the book Designing Data-Intensive Applications.
This book has revealed that I have been using indexes incorrectly, in that I didn't realize that even if I have an index ...
- 165
-1
votes
1
answer
449
views
JSON vs Record/Struct type in a DB
It seems like some databases have implement a Record/Struct type and others have implemented a Json/Jsonb type to handle nested data structures. A few examples being:
BigQuery Struct
CockroachDB ...
- 131
0
votes
1
answer
36
views
Database system checksumming to prevent network corruption not caught by TCP checksumming
Since TCP checksumming is not sufficient to detect/prevent all corruption, do modern database systems (e.g. SQL Server, MariaDB / MySQL, Oracle, etc.) use any additional checksumming of network ...
- 121
0
votes
2
answers
596
views
What happens if you lose the connection during a huge insert?
What happens when you lose your connection with the DB during a huge insert. Will part of the data be inserted? Will nothing be inserted? What is the reason for this?
The example statement I am ...
2
votes
2
answers
546
views
Use expression in window function that references columns from the current row
Suppose I have the following query that uses window function:
SELECT id
, var
, num
, SUM(var * num) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS calc
FROM (VALUES
...
- 461
0
votes
2
answers
79
views
Group by arbitary monthly time period
I want to group the following data by an user defined period:
+------------+--------+
| DATE | Amount |
+------------+--------+
| 2019-03-12 | 300 |
| 2019-03-15 | 1500 |
| 2019-03-25 | ...
- 103
0
votes
1
answer
1k
views
How to create relationship metadata between tables without a foreign key check?
I use database metadata (primary keys, foreign keys, table names, etc), to generate code and some very complex queries. This metadata resides in theINFORMATION_SCHEMA. I am having problems with ...
- 11
0
votes
1
answer
43
views
Predefined rows conflicting with user entered rows
Is there a pattern to deal with a set of predefined rows in one or more tables that can also have user-defined rows inserted in them?
I could want to distribute an application with a table called ...
- 117
-2
votes
1
answer
44
views
How to group a few row values together to put into a new table? [closed]
I have a table:
+-----------+-------+
| risk | count |
+-----------+-------+
| high | 5 |
| low | 30 |
| medium | 81 |
| very high | 2 |
| very low | 72 |
+------...
- 1
0
votes
1
answer
417
views
How to search for compound words, and get the word parts returned
I need to be able to search for compound words like "menneskerettighedsforkæmperens" (the human rights activist's) and find the words "menneske" (human), "rettighed" (right) and "forkæmper" (activist)....
- 109
1
vote
1
answer
122
views
Vertically align sql tool [closed]
Is there a tool out there that will take sql and align it vertically?
what I have:
SELECT first_name AS foo
,last_name AS bar
,ssn AS bas
,address AS chu
FROM dbo.contacts;
...
3
votes
3
answers
692
views
Why intended integrity constraint cannot be enforced?
What does the following integrity constraint try to enforce? (integrity constraint is primary key and check in this case but I couldn't understand what I should really write).
Explain why the ...
- 33
0
votes
1
answer
259
views
MySQL : Are hash indices useless?
In MySQL, hash indices are only supported by the MEMORY storage engine. By using this engine, data is stored in RAM, not on the hard disk, so it will be lost when MySQL is restarted.
Also, ...
- 101
3
votes
3
answers
157
views
Design for CE Events / Training Database
I'm working on a design for a continuing education training database. Users will register for a CE course/event that could be one day or multiple days. Courses are taught multiple times per year.
...
- 131
1
vote
2
answers
3k
views
Is it bad form to use "LIMIT 1" on queries that should only return one row anyway? [closed]
If you have a query like this:
SELECT * FROM table WHERE primary_key_column = 123 LIMIT 1;
What do you make of the LIMIT 1?
It is technically not necessary, as we are selecting on a unique column. ...
- 153
2
votes
2
answers
5k
views
Reason for NOT IN behaviour [duplicate]
sql> SELECT * FROM runners;
+----+--------------+
| id | name |
+----+--------------+
| 1 | John Doe |
| 2 | Jane Doe |
| 3 | Alice Jones |
| 4 | Bobby Louis |
| 5 | Lisa ...
- 23
7
votes
4
answers
421
views
Is there any security risk in having many foreign keys referencing a single table?
I have a teacher who told me that having a database with a table that's referenced by around 60% of the other tables was a security no-no. The problem is that he failed to explain why it was a ...
0
votes
3
answers
4k
views
Difference between SCHEMA and STRUCTURE? [closed]
What is the difference between database schema and database structure?
Is Schema = Structure + Data?
or
Is Schema just a set of metadata used by the database?
I'm talking in general terms, not ...
0
votes
1
answer
104
views
Can DBA revoke access privilege from the owner of a table? [closed]
If I create a table T, I become the owner of the table.
Can DBA access T?
Can DBA also revoke my access or grant privileges on T?
- 101
18
votes
2
answers
3k
views
Does any DBMS have a collation that is both case-sensitive and accent-insensitive?
Note this question is vendor/version agnostic
It seems to me, as a speaker (typist, writer) of English, reasonable to expect words to be properly cased but not necessarily have the correct accents ...
- 2,622
12
votes
5
answers
3k
views
Convert a date range to an interval description
A requirement in a recent project was to report when a resource would be fully consumed. As well as the exhaustion calendar date I was asked to show the remaining time in English-like format, ...
- 25.1k
2
votes
1
answer
912
views
Execution time with warm up cache
I have a query that takes around 1 second on first execution and 15-50 ms after running the same query again. The only difference between EXPLAIN outputs is that the second execution contains a lot of ...
- 175
55
votes
7
answers
21k
views
If a person's name is Null then how would it break the database?
I was reading this article on BBC. It tells a story of a person named Jenifer Null and how she faces day to day problems while using online databases like booking plane tickets, net banking etc.
I am ...
- 685
0
votes
1
answer
128
views
Unstructured to Structured Data
This is highly a research based question about converting the unstructured textual data to structured data. Are there any systems or tools perform automated process of converting the unstructured data ...
- 65
4
votes
4
answers
1k
views
Is there any scenario where the existence of one or more Indexes in a table can prejudice it?
The title sums it up.
I've learned and always heard that indexes in tables improve CRUD operations. A developer that I met last weekend told me that he does not like Indexes because they are bad - ...
- 591
1
vote
1
answer
203
views
Database agnostic auditing [duplicate]
Background
Various databases have custom implementations for auditing:
SQL Server Audit
Oracle Audit
PostgreSQL Audit Trigger
MySQL Enterprise Audit
Problem
A number of problems exist:
There is ...
- 833
0
votes
1
answer
74
views
Understanding the flaw in the query
Suppose we have a relation
Employee={empId,name,department, salary}
and we are interested in finding the employees who get more salary than anyone in department 5. Would the following query work?
...
- 217
1
vote
1
answer
672
views
Apply Definition of MVD (multivalued dependency) to Example?
Supposedly this multivalued dependency (MVD) and this functional dependency (FD):
{W} ↠ {N,D} and {W} → {N}
hold in this table:
V W N D
=================
| v | w | n | 1 |
----------------...
- 777
0
votes
1
answer
1k
views
how to build statistical database?
According to the book I am reading, a statistical database is a database that permits queries that derive aggregated information but not queries that derive individual information.
At current time, is ...
- 137
2
votes
2
answers
2k
views
Using SQL ORDER BY for complex criteria
Let's say my relation is like so
and I wanted to sort by rank, but A's and B's first. So my relation will look like
At first I tried to just have two relations, where I order both of them by rank, ...
- 23
-2
votes
1
answer
140
views
Create a view for different attributes from different tables
I have three tables:
product(pid,name,category,maker-cid)
purchase(buyer-ssn,seller-ssn,quantity,pid)
person(ssn,name,phone number,city)
How can I create a view to expose only the Buyer name, Seller ...
- 21
-2
votes
2
answers
229
views
Convert rows to columns in SQL [duplicate]
I'm looking for a query to achieve the following:
Current table format
user_id meta_key meta_value
------- -------- ----------
1 address1 abc street
1 city ...
- 101
2
votes
4
answers
2k
views
What are good ways to store decorated objects in a relational database without adding a field for every possible property?
Suppose I have an object, I use a decorator to add properties to this object, and then I want to store the object and its properties in a relational database, and be able to run queries on these ...
- 139
2
votes
2
answers
1k
views
Do Nested Transactions commit? [closed]
I am not happy with the Wikipedia article about nested transactions.
I would not use the word "commit" for a nested transaction.
For me "commit" means "durability". A nested transaction does not ...
- 1,561
2
votes
1
answer
288
views
What is a "cursor"?
I am new to database concepts and I found some sources that try to explain what is a cursor, but I can't understand what they mean. Can someone explain in a very basic manner what a cursor is and what ...
- 205
4
votes
0
answers
446
views
Can a slow SELECT statement in READ COMMITTED isolation level read content from concurrent committed UPDATE?
Let's say we have a big table with hundreds of million of rows (or more).
On one connection, we perform a query requiring full table scan under READ COMMITTED isolation level:
SELECT * FROM bigtable;...
- 147
0
votes
1
answer
285
views
EAV table question
What's the best design for taxonomies in EAV tables?
taxonomy
id autoincrement
name unique
terms
id autoincrement
tax_id references taxonomy(id)
name text unique
value text
...
5
votes
1
answer
2k
views
database DDLs and implicit commit
I've been taught that in RDBMS any DDL always causes an implicit commit, ending any active transaction at the time the DDL is issued.
Now I found this page on the PostgreSQL wiki that states that ...
- 396
0
votes
1
answer
55
views
Which keyword I should use?
I have two tables:
I1 (A, B, C)
I2 (A, C)
I2 has some tuple of data that I1 doesn't have.
Now I want to create a table I3 that only has the data (A, C) that both exists in I1 and I2.
Which keyword ...
- 39