Questions tagged [database-theory]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
1answer
33 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 ...
-1
votes
0answers
13 views

Understanding sparsity in OLAP — MOLAP vs ROLAP approaches?

OLAP is typically divided in MOLAP or ROLAP. In OLAP, we tend to build a data cube ("OLAP cube"). MOLAP is based on multidimensional arrays and a cube data type. ROLAP is based on the relational model ...
15
votes
1answer
1k 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 ...
0
votes
1answer
23 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 ...
0
votes
1answer
115 views

What is the common symbol of an arbitrary query?

For a theoretical database paper i tried to figure out if there is a is general symbol for an arbitrary Query that is used in literature. Can anyone tell me if there is any symbol?
0
votes
0answers
22 views

Understanding non repeatable read anomaly in databases

This paper discusses in detail ANSI isolation levels discussed in ANSI SQL standard on page 84. Paper introduces following shorthand for describing histories which contain reads, writes, commits and ...
0
votes
0answers
34 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 ...
0
votes
0answers
11 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 ...
1
vote
0answers
17 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 ...
2
votes
1answer
40 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, ...
3
votes
1answer
54 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 ...
2
votes
1answer
44 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 ...
1
vote
0answers
60 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 ...
3
votes
0answers
231 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 ...
-1
votes
1answer
36 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 ...
2
votes
2answers
52 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
1answer
75 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 ...
0
votes
0answers
296 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 ...
0
votes
2answers
66 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 ...
1
vote
0answers
19 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 ...
1
vote
2answers
764 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 ...
0
votes
1answer
12k views

Difference between candidate key & primary key

What is the differences between candidate key and primary key?
1
vote
1answer
1k 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 ...
0
votes
1answer
767 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
2answers
110 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
1answer
66 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 ...
1
vote
1answer
70 views

what does “\” symbol mean? [closed]

I am new to database learning and came across this \ symbol. I googled but couldn't find what it meant. It was used in the context Q = R\S, where Q, R, and S are relations. (The = sign here means "...
0
votes
1answer
256 views

Grouping operator in DBMS (γ) is duplicate-impervious. Why?

I'm reading Database Systems by Ullman where its mentioned that γ is duplicate-impervious. I don't understand why a simple group operation should cause elimination of duplicates. Aliter, Duplicate-...
2
votes
1answer
230 views

Minimal 3NF normalization issues

I'm trying to learn how to normalize to 3NF but the example that my teacher gave us doesn't really help. The algorithm I always used is this one: Find a canonical cover F_c for F For each FD X->Y in ...
3
votes
1answer
374 views

Why are multivalued dependencies named that way?

I'm doing a relational design course on Stanford open courseware. I understand why functional dependencies are named that way - A->B means B is specified when A is specified, or in other words, the ...
1
vote
0answers
33 views

What is stable history in a database?

I am reading the paper Making geo-replication fast as possible, consistent when necessary by Li et al. in which they propose a geo-replication system called "RedBlue Consistency". They have a table ...
1
vote
1answer
49 views

Tuple calculus: unsure if my reasoning is correct

I'm currently working through a tuple calculus question and I ran into a situation where I have two answers that seem correct to me and I am unsure if my logic is wrong or if they are both correct ...
7
votes
2answers
371 views

What are the practical uses for relational calculus?

In my Database Design course we are learning both Relational Algebra and Relational Calculus. I can see where Relational Algebra could be useful since it is closely tied to SQL. Our professor said ...
2
votes
3answers
5k views

What is meant by a blind write in a schedule?

I know that every conflict-serialisable schedule is view-serialisable but the converse is not true. I read in a webpage that when a schedule is view-serialisable but not conflict-serialisable then ...
2
votes
2answers
757 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
73 views

Word for describing how normal (technical term) a database is

Database Normalization is the process of changing the structure of a formal database schema in order to minimize redundancy. Database theory describes some states of normalization in order to make ...
6
votes
4answers
10k views

Are junction tables a good practice?

Say I have a large table that holds the user's info and another table that holds several locations. Then I use another table that holds the user_id and the location_id. In order to retrieve the data ...
0
votes
1answer
42 views

Usage of non standardized queries

So this is more of a theory question than practical but I'd appreciate any help. My friend and I are doing a project to learn new stuff and have fun. He's a coder and I like to think that I'm good ...
9
votes
5answers
2k views

Mathematical equality of two SQL statements

Is there a way to get check the mathematical equality of two SQL statements? I have two SQL statements: SQL_STATEMENT_1 SQL_STATEMENT_2 Running both statements on data and comparing the output does ...
1
vote
1answer
300 views

Normalization to 2NF and 3NF

I solved this example to determine 2NF and 3NF. I would like to check if my answer is correct. Can somebody check the 2NF and 3NF of this example, please? R( A B C D E F G H I J) with functional ...
1
vote
2answers
2k views

Why is a functional dependency described as a “one-to-many” relationship?

As far as I know, the definition of a functional dependency reads that, given the attributes X and Y, "each X value is associated with precisely one Y value". But then the author of the article that I ...
1
vote
2answers
137 views

Multidimensional aggregates

Suppose one wanted to cache simple aggregate data over a table or grouped parts of a table, one could simply make a table with the aggregated results. If the original table is only appended to, it is ...
1
vote
1answer
137 views

What's the term that describes filtering query that makes use of indexes

I am looking for a technical term that describes the type of query in SQL that makes use of indexes. I completely forgot the name and can not look it up anymore (there's even a Wikipedia article about ...
0
votes
1answer
49 views

Database relationship refresher

All, After several years of programming applications, I'm looking to refresh my understanding of databases. What I knew to begin with was fairly basic, and honestly, it's disappeared over time. I ...
4
votes
2answers
81 views

Simultaneous money withdrawls from a distributed/replicated database

Lets say I have $10 in my bank account. The database is replicated in North America and Asia. At the exact same time I do a withdrawl of $10 in N. America and in Asia, meaning that my withdraw would ...
4
votes
1answer
356 views

When should I use a new table?

If a database is to hold a large amount of eg tickets for concerts, should I make a new table for each concert, then keep a table connecting venues with concerts? I would like each ticket ID for ...
2
votes
1answer
39 views

TRC: Select an ID if it appears on all measurements

I have these two "tables": Station (station_id, city) // Stations details Rainfall (station_id, date, amount) // Rainfall measurements With Tuple Relational Calculus (TRC), I need to ...
3
votes
1answer
369 views

Boyce-Codd Normal Form

I am bit confused with BCNF and would appreciate any clarification. I have a table The primary key is a composite of all its attributes. Is this table in BCNF? My assumption it is not, because BCNF ...
0
votes
1answer
2k views

Get the value of one attribute from the reference key of another table?

I have two tables named as ps_order_detail and ps_product_lang in MySQL. I want to get the product name from one table whose reference key is in another table. I have two tables. First the ...
3
votes
3answers
1k views

Why does data get corrupted in “large” text files - Reasons to use database

I am just starting to learn about databases and using mysql. I read that databases are more reliable than having large ascii text files and that ascii text files are more prone to corruption as they ...