Questions tagged [database-theory]
The database-theory tag has no usage guidance.
119 questions
0
votes
1
answer
46
views
Is snapshot isolation only potentially different from serializability if there are "cycles" between transactions with reads and writes?
I'm trying to figure out a better intuition for what exactly are the sorts of consistency anomalies that snapshot isolation allows to happen. The description on Wikipedia says:
In databases, and ...
- 101
0
votes
2
answers
77
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 ...
- 101
-1
votes
1
answer
51
views
How do database snapshots work?
How do database engines take consistent snapshots when writes could be happening at any time?
(A) Does the database engine block writes one table at a time while taking snapshots?
(B) Does the ...
- 99
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
1
answer
96
views
Doubt on 2NF decomposition
I have read that 1NF, 2NF and 3NF decompositions are lossless and dependency-preserving.
Consider this example on a relation R(A,B,C,D) with functional dependencies set as FD ={ AB->CD, A->C, BC-...
- 11
4
votes
1
answer
170
views
T-SQL: Iteration vs. Set-Based Operations When Independence of Ancillary Data is Needed
BACKGROUND
I am using Microsoft SQL Server Management Studio v18.9.1 on Windows 10.
I've created a database to store test results of our company's widget (not yet being produced). I am working on a ...
- 197
1
vote
0
answers
80
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-...
- 603
0
votes
0
answers
350
views
Weak Entity with Primary Key?
I have the following E-R diagram to refer to:
I have a question posed to me that entity 4 IS a weak entity due to one of its relations. However, from what I see, entity 4 has a composite primary key ...
-1
votes
1
answer
732
views
Why can't Many to Many relationships exist in relational database management systems?
While reading Oracle 11g Sql by Joan Casteel, the author mentions that many to many relationships couldn't exist in relational databases. I was wondering what the reasoning behind this fact was.
- 11
0
votes
1
answer
385
views
Why is SQL not considered a programming language?
If a database is just a computer system that implements a database management system, and we use SQL to tell it what to do, why don't we consider SQL to be a programming langauge?
From Wikipedia:
A ...
- 11
0
votes
1
answer
22
views
Convert generalization in ERD to Relationnal Database having no additional column
I've a quite simple problem but the litterature I came accross didn't seem to dwell on.
Let's say I have an entity OFFER which represents an Offer on a Product, for each product, one offer can be &...
3
votes
1
answer
1k
views
Compensation Log Records (CLR) in UNDO-Phase of Database Recovery
The compensation logs' redo-information corresponds to the undo-information of the log entry that made their creation necessary during the undo-phase.
That sounds to me like the CLRs redo information ...
- 33
1
vote
1
answer
175
views
Is COW (copy-on-write) based atomic commit widely used in mainstream databases?
The database is not a flat structure like a file system, and generally has three to four levels of index levels. If you simply use COW, you will have obvious write amplification every time you modify ...
- 111
-1
votes
2
answers
1k
views
Are junction / join tables always necessary
I have tried looking across other answers for this, so far I have not quite found what I am after. While there are multiple questions regarding junction tables, none seem to address whether a junction ...
- 1
0
votes
0
answers
55
views
What do I call a relational schema without the constraints?
It is common in db theory to define a ‘relation schema’ as consisting of a relation name, the (names of the) attributes, as well as constraints on the relation. A ‘database schema’ is then a set of ...
- 101
0
votes
1
answer
66
views
the actual writes only after the transaction enters the committed state
The text below is from Database System Concepts by Silberschatz. It says that the actual writes take place only after the transaction enters the committed state.
My question: When the transaction has ...
- 111
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
0
votes
1
answer
940
views
Association Tables
I can use an association (or ‘linking’) table to model a many-to-many relationship, eg Student <- Enrolment ->Course, where an enrolment describes an essential relationship between students and ...
- 103
0
votes
1
answer
281
views
View serializable schedule or not
Could you help me understand why the schedule on the picture is view serializable without any "blind write"?
Also, should the results be the same if two schedules are view equivalent?
I did ...
6
votes
2
answers
336
views
Alternatives to storing a record with exactly n multiple foreign keys from the same foreign table, where the relationships can't be repeated
Say there are entities called singulars, and entities called relationships.
It takes exactly two singulars to make up a relationships entity.
That pair of singulars can't be repeated elsewhere in ...
- 163
-1
votes
2
answers
533
views
Theoretical - Given 2 Tables, is it possible to calculate the size of the resulting dataset after a join?
This may not be the most appropriate exchange for this, so my apologies in advance.
I was posed this question during an interview prep session for an SQL developer role and was not given the ...
- 121
0
votes
0
answers
1k
views
How functional dependency imply multi valued dependency?
I understand following informal definition of multi valued dependency:
If multi valued dependency is α↠β, then whenever the tuples (a,b,c) and (a,d,e) exist in relation r, the tuples (a,b,e) and (...
- 249
1
vote
1
answer
121
views
Can I rely on the MSDTC to guarantee a distributed transaction was completed?
I've come across a bug using the TransactionScope from the .NET library, where we're getting a TransactionInDoubt exception. After doing a lot of research into something I don't know much about, I've ...
- 111
1
vote
1
answer
570
views
What is a “part-key”?
I'm a beginner and student with regards to database design. The tutor recently discussed “part-keys”. I am struggling to find any information on what a part-key is, and I suspect it probably goes by ...
0
votes
0
answers
196
views
Condition table theory
Is there a good way to create a "table of conditions"?
I have a huge table, where 4-5 columns are considered in a set of rules, and based on those columns default values must appear in other columns. ...
- 133
-1
votes
2
answers
117
views
Does this have a name?
Does this have a name?
I don't really know how to describe the situation, and furthermore, is this a design flaw?
T4 has a FK to T3, and to T1, but I can get to T1 by T3.
The T1, T2 refers to MUST ...
0
votes
1
answer
166
views
PostgreSQL CREATE TYPE using multiple relations?
I'm becoming familiar with PostgreSQL's ability to define a data type https://www.postgresql.org/docs/current/sql-createtype.html using CREATE TYPE syntax.
It appears to me that this type does not ...
- 537
20
votes
1
answer
2k
views
What exactly is the 'online' in OLAP and OLTP?
I'm a bit confused because I'm questioning the definition of 'online' in OLTP and OLAP. I used to think that 'online' here means that we want our answer in bounded time and based on the data available ...
- 537
0
votes
1
answer
76
views
Analyzing a query plan for a query with TWO predicates, but index only has ONE
Note: This is from an introductory database systems course at my university so I'm not entirely sure if it matters, but for what it's worth we are being taught MySQL.
One homework question I'm doing ...
- 1
2
votes
1
answer
3k
views
Understanding repeatable read isolation level
Wikipedia defines following terms:
With a lock-based concurrency control DBMS implementation, serializability isolation level requires read and write locks (acquired on selected data) to be ...
- 249
1
vote
0
answers
171
views
Understanding connection between different database concurrency control protocols
I have came across following diagram related to timestamp based concurrency control protocols in database systems (as summarized in these sldes or detailed in the book by Korth et al.):
I don't know ...
- 249
1
vote
0
answers
30
views
Understanding what does it mean when we say certain rule ensures conflict serializabiliy
Database book by Korth explains validation protocol as follows :
To perform the validation test, we need to know when the various phases of transactions took place. We shall, therefore, associate ...
- 249
2
votes
1
answer
62
views
Secondary composite-key pages access
we have this relation
Store: [SID, City, EID]
Employee: [EID, Name, Salary, SID]
Article: [AID, Name, Producer, Price]
Inventory: [AID, SID, Count]
Invoice: [IID, SID, Customer]
Item: [IID, SID, AID, ...
- 23
4
votes
1
answer
305
views
How do graph databases differ from older navigational (hierarchical/network) databases?
I'm trying to improve my knowledge of graph databases, in particular DGraph. Are graph DBMSs just a new generation of pre-relational hierarchical/network model DBMSs, or is there something new at a ...
- 143
2
votes
1
answer
250
views
Confusion with Tuple Relation Calculus Operations
I came upon a question like,
Given the following relational schemas
Student (studId, name, age, sex, deptNo, advisor)
Department (deptId, DName, hod, phoneNo)
Which of the following will be the ...
- 123
1
vote
0
answers
116
views
NuoDB - Where's the catch?
I've been reading on the promise of an ACID compliant database management system without sacrificing horizontal scaling capabilities.
Where is the catch? Which part of the CAP theorem did they choose ...
- 23
3
votes
0
answers
922
views
Counting in Tuple Relational Calculus
Given the following schema:
Staff (sid, sname)
Project (pid, pdesc, pfrom, pto)
Asset (aid, acat, adesc)
Workfor (sid, pid, wfrom, wto)
Assignment (aid, sid, pid, afrom, ato)
Note: The bold ...
- 131
-1
votes
1
answer
129
views
What does FHQT or FQA mean?
I was reading this post by Andres Pascal, and I came accross a few new acronyms,
As I understand, you need to search strings by similarity (using levenshtein or any other metric distance). In that ...
- 64.6k
2
votes
2
answers
62
views
Is there a name for this pattern, representing one-to-many relationships by adding columns with an index to a single table?
So a domain expert wants the following relation:
CarOwners(owner_id, name, address, car_1_registration, car_2_registration, car_3_registration, car_4_registration)
The reason given that most people ...
1
vote
1
answer
893
views
How to prove "A->B,C then A->B and A->C" from Armstrong Axioms
I know that this is true but I don't understand how to prove it.
The Problem:
Prove or disprove that if A->B,C, then A->B and A->C
This answer is similar but it does not prove the relation using ...
- 121
0
votes
0
answers
1k
views
3NF Decomposition Check
I've solved, or I think I have, two book problems an 3NF, but I'm still a bit unsure about my understanding of it all and want to be sure I did it correctly.
For the first one I have
R(ABCD)
and ...
- 169
0
votes
2
answers
290
views
How does a database conciliate data to files on distributed filesystems?
The question title might be a little misleading, but I was wondering what happens internally in a database implementation when the file used for storage are placed on a distributed filesystem (like ...
- 229
4
votes
3
answers
1k
views
Understanding data abstraction levels
The three-level ANSI SPARC Database Architecture suggests three data abstraction levels, namely, external, conceptual, and internal levels.
If I understand correctly, the external level represents ...
- 225
1
vote
0
answers
28
views
In the field of Probabilistic Databases, how can one compute the lineage and the safeness of a boolean query over a tuple-independent table?
Suppose we have a tuple-independent database (TID) named D and we have to evaluate if a boolean query Q(D) is safe or not safe using the intensional query evaluation approach. I suppose we have to ...
2
votes
2
answers
2k
views
Transaction recovery and rollback
As I understand:
UNDO: Undoing a write item operation consists of examining its log entry [write_item, T, X, old_value, new_value] and setting the value of item X in the database to old_value. UNDO ...
- 177
3
votes
3
answers
37k
views
Difference between candidate key & primary key
What is the differences between candidate key and primary key?
- 63
1
vote
1
answer
3k
views
Proof that if a relation R is in 3NF and if every key in R is simple, then R is in BCNF?
"A key is simple if it consists of only one attribute". Prove that if a relation R is in 3NF and if every key in R is simple, then R is in BCNF. Your proof should be general, e.g., it should not ...
- 13
1
vote
2
answers
4k
views
B+ Tree structure with buckets (Begginer question)
I am enrolled in a course and I am confused about few concepts in the structure of the B+Tree. I know what it does and what's the difference between it and BTree, but I got a bit conflicted when I saw ...
-1
votes
2
answers
114
views
Why are there so many definitions of database?
In my lecture today, I heard that a database is a collection of related data that is organized.
From Google I came across these:
"A database is a collection of data, typically describing the ...
4
votes
1
answer
77
views
Question about cost optimization formula for SELECT
I am a student in computer science and I have a question, which refers to cost optimization. I have to read the book (Fundamentals of Database Systems from Elmasry and Navathe) and try to present the ...
- 143