Questions tagged [database-agnostic]
Only use this tag when the question does not depend on a particular database management system.
60
questions
0
votes
1answer
39 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
...
0
votes
2answers
50 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 | ...
0
votes
1answer
86 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 ...
0
votes
1answer
30 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 ...
-2
votes
1answer
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
vote
1answer
134 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)....
2
votes
1answer
51 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
3answers
593 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 ...
0
votes
1answer
98 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, ...
3
votes
3answers
133 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.
...
1
vote
2answers
1k 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. ...
2
votes
2answers
656 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 ...
7
votes
4answers
209 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 ...
-1
votes
3answers
2k 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
1answer
92 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?
18
votes
2answers
2k 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 ...
11
votes
5answers
1k 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, ...
2
votes
1answer
719 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 ...
55
votes
7answers
20k 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 ...
0
votes
1answer
111 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 ...
4
votes
4answers
747 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 - ...
1
vote
1answer
129 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 ...
0
votes
1answer
66 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?
...
1
vote
1answer
379 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 |
----------------...
0
votes
1answer
713 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 ...
2
votes
2answers
623 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, ...
-2
votes
1answer
133 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 ...
-2
votes
2answers
88 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 ...
2
votes
3answers
720 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 ...
2
votes
2answers
841 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 ...
0
votes
0answers
77 views
Storage strategy for polynomials
What is the most space efficient way to store arbitrary length univariate polynomials? I have a large amount of polynomials that look like
3*x^7 + 12*x - 13
9*x^32 - x^14 + 2*x^3 - 9
...
Each ...
2
votes
1answer
171 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 ...
4
votes
0answers
273 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;...
0
votes
1answer
200 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
...
3
votes
1answer
536 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 ...
0
votes
1answer
47 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 ...
2
votes
2answers
541 views
Associating on the ID of an association table
Sorry about the title, I couldn't find anything better. Suggestions are welcome.
Say we have two tables, Suppliers and Products.
The same product can come from many different suppliers, so we create ...
0
votes
1answer
52 views
Max function and group by getting differenet count [closed]
Statement 1 :
Select Load_Date , Count(*) Last_Count From <Table_Name> group by Load_Date;
Statement 2 :
Select max(Load_Date) , Count(*) Last_Count From <TABLE_NAME>;
Statement 1 ...
2
votes
2answers
3k 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 ...
2
votes
2answers
212 views
List of fallback values in SQL [closed]
My question is what would be the best way to handle storing a list of values where the next serves as a fall-back. With a completely variable number of fall-backs (specified by our user).
I'm aware ...
0
votes
0answers
38 views
How to distribute/release/publish a database schema
I want to distribute/release/publish a database schema to multiple 3rd parties (e.g. I'll produce a zip for them to download and they'll set up their database using it). They aren't all using the same ...
2
votes
2answers
538 views
How to establish a Two-to-One table relationship (PK-FK-CK)?
My question is about DB design and its principles. I am working on a Python application where objects are mapped to database tables.
I have Projects and People objects, mapped to their respective ...
0
votes
2answers
356 views
How do you store tabular data in an RDBMS?
First: I really haven't got any idea how to google for this. If you got one, leave a comment.
If I want to store arbitrarily large tables in a database, how should I set up my database tables?
...
1
vote
2answers
193 views
Usage of foreign key when the referenced table data will not change
I have to design a table where user has 10 fields and 6 are dropdown. Some are single selections and some multiple. Dropdown data is supposed to change very rarely.
For e.g., the user is presented ...
29
votes
6answers
6k views
Arbitrarily ordering records in a table
A common need when using a database is to access records in order. For example, if I have a blog, I want to be able to reorder my blog posts in arbitrary order. These entries often have lots of ...
1
vote
2answers
1k views
Extensible Asset Database Schema
Background
I work for a decent sized company in the hardware sector. Currently, we have a number of varying assets that need to be tracked. Until recently, this was done by hand as was an (...
2
votes
1answer
1k views
Database design with different user roles connected by different relationships to one organizaiton table
An user is connected to an organization by some role. Lets say there are 2 roles freelancer and employee.
One employee can only be a part of one organization and if he is an employee then he can't be ...
1
vote
1answer
1k views
One address table for many types of addresses
I have an application where I have to store adresses of multiple types of location.
For example, my app requires addresses of people, hotels and event venues.
Should I create separate tables for ...
1
vote
1answer
352 views
Is it possible to query “NOT IN” in NO SQL?
I asked the same thing about a specific NoSql engine (Big Table) on SO.
It seems that Big Table doesn't support NOT IN queries?
Is that possible in any other NoSQL databases?
Will it ever be possible ...
1
vote
3answers
242 views
What are the challenges of administrating an International stock trading database? [closed]
I am a student, and have a question for an assignment:
What problems and challenges do database administrators face when designing systems to trade international stocks in real time and 24 hours a ...
