Questions tagged [database-agnostic]

Only use this tag when the question does not depend on a particular database management system.

Filter by
Sorted by
Tagged with
56
votes
7answers
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 ...
32
votes
15answers
25k views

How to implement a 'default' flag that can only be set on a single row

For example, with a table similar to this: create table foo(bar int identity, chk char(1) check (chk in('Y', 'N'))); It doesn't matter if the flag is implemented as a char(1), a bit or whatever. I ...
32
votes
6answers
9k 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 ...
21
votes
4answers
4k views

Why won't some DBMS's allow rollback for certain DDL statements?

Recently I found out that MySQL doesn't support rollback of DDL such as "alter table"... Being used to PostgreSQL, that struck me as odd, but a friend of mine told me that even Oracle doesn't allow it....
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 ...
17
votes
2answers
4k views

Open Source Business Intelligence/DWH solutions [closed]

I wonder that this question hasn't already been asked. Google only has very few results for me that don't show a high quality tool What are some Open Source (also free is ok) solutions for Data ...
15
votes
5answers
542 views

Indexing from start or when performance problem arises?

My question is regarding use of indexes. Should I start indexing right from the start or when performance problem arises? We can also create temporary index while executing a query. What are the ...
11
votes
5answers
2k 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, ...
10
votes
2answers
650 views

Which areas of a DBA's knowledge should a developer delve into? [closed]

I must admit the question is quite broad, so I'll try narrowing it down a bit. In our company we are 3-4 developers and have some SQL Server based installations running at our customer's sites (...
9
votes
3answers
2k views

Multidimensional indices in databases

Which databases use real multidimensional indices? Is oracle ever using several indices to get data from tables, or will it always take the one that seems to have the highest selectivity? How about ...
8
votes
2answers
1k views

Do agile software development approaches apply to SQL?

I would like to learn if agile software development methods/principles/patterns are valid to SQL programming as well. If yes, where would be a good place to start learning about that? Are there any ...
7
votes
4answers
308 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 ...
6
votes
1answer
1k views

Password hashes: Fixed-length binary fields or single string field?

I'm currently having am amusing debate with a friend, and we simply can't agree on the best method to store salted passwords in a database. The two options on the table are: Storing the hash and the ...
5
votes
2answers
2k views

Group_by and having vs Joins when table is self joined 5-6 times

Now the situation due to which this question came to me. Schema is Table User_Read_Book user_id | book_id Now I want to get users who have read certain books. Say give me users who have ...
4
votes
4answers
900 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 - ...
4
votes
0answers
324 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;...
3
votes
3answers
625 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 ...
3
votes
2answers
500 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-...
3
votes
3answers
143 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. ...
3
votes
2answers
4k 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 ...
3
votes
1answer
828 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 ...
2
votes
2answers
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 ...
2
votes
2answers
1k 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
2answers
2k 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 ...
2
votes
2answers
384 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 ...
2
votes
1answer
784 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 ...
2
votes
3answers
1k 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
1answer
180 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 ...
2
votes
2answers
698 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 ...
2
votes
1answer
2k 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 ...
2
votes
2answers
626 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 ...
2
votes
1answer
58 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; ...
1
vote
1answer
1k views

Pros and Cons of having additional columns in join table of many to many relationship

I searched but did not find any discussions on this topic. Are there any pros and cons on creating many extra columns on joined table? Does it affect speed of data retrieval because we are joining 3 ...
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
3answers
248 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 ...
1
vote
1answer
433 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
2answers
2k 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. ...
1
vote
1answer
161 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 ...
1
vote
2answers
194 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 ...
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 (...
1
vote
3answers
1k views

Select only neighbour rows who pass a where-test

I want to apply a selection process to my data, that will not include rows which should be accepted by my WHERE clause but who follow after a row which fails the WHERE. Example. I have a list of gps ...
1
vote
1answer
464 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 | ----------------...
1
vote
1answer
113 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 ...
1
vote
1answer
167 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)....
0
votes
1answer
67 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? ...
0
votes
1answer
18 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 ...
0
votes
1answer
30 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 ...
0
votes
2answers
209 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 ...
0
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
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 ...