Questions tagged [relational-theory]
On this site this tag applies to questions about Relational Model theory. The Relational Model for database management is an approach to managing data using a structure and language consistent with first-order predicate logic. In the Relational Model of a database, all data is represented in terms of tuples, grouped into relations. A database organized in terms of the Relational Model is a relational database.
401
questions
0
votes
1answer
13 views
How can I calculate the cost of the query, if there is a `between` condition?
How can I calculate the cost of the query, if there is a between condition?
Select *
From A
Where A.id between 10 and 50
If Index not exists and there is only one condition:
Search in B+ tree to ...
2
votes
2answers
133 views
Incorrect result when using 'case when count()=0' & 'where'
I would very appreciate if someone could help me out with this query.
I can't seem to get the correct result when using case when count()=0 & where
I work with SQL Server, with a DB of a system ...
1
vote
1answer
22 views
Storage strategy for combination of all table items
Let's say I've one table with items with the following fields:
id_item
name
description
& one table with the user information, following like:
id_user
email
name
Now I'd like to let vote my ...
0
votes
0answers
31 views
narrow or wide - how to store huge amount of values
I think, I have two questions maybe in one (because they belong together and the first is priorized while the second is just an idea).
The leading question is:
Given billions of triplets (Timestamp, ...
0
votes
2answers
50 views
How do you determine the direction of a relationship?
Suppose I have the following relationship diagram:
How would you describe the relationship determined by the red line?
Reading from left to right you would say each order has only one customer so it ...
1
vote
1answer
37 views
Is it possible to enforce corresponding hierarchies?
I constructed the following toy example to illustrate my question.
The left side of the schema describes a an abstract product consisting of its parts.
The right side of the schema describes actual ...
1
vote
0answers
36 views
Can there exist Tables with no Non-Prime Attributes? If so, are they always normalised to 3NF?
Can there exist a table with no non-prime attributes?
Here is an example of a table which I believe has no non-prime attributes:
I came to this conclusion because the list of attributes in the ...
1
vote
1answer
39 views
Database design where % of rows have unused attributes/columns
Is there a theory/practice that promotes a design by analysing the usage of attributes of a table, such that given a threshold of unused columns over a percentage of rows would suggest that the ...
1
vote
0answers
46 views
Properties of grouping in relational algebra
The properties of relational algebra (commutativity, associativity, distribution) allow us to take a relational algebra expression and transform/rewrite it into another one which is logically ...
0
votes
0answers
44 views
Relational Algebra - Remove duplicate tuples without count
I was wondering if there is a way to remove the tuples from a table using relational algebra that have the same Value occurring in multiple columns or same column multiple times
e.g.
I want to ...
0
votes
0answers
32 views
Delete duplicate keys with relation algebra without count
Hi I am new to relational algebra. I was wondering if there is a way to remove the tuples from a table using relational algebra that have the same Value occurring in multiple columns or same column ...
0
votes
2answers
27 views
Delete duplicate keys with relation algebra
Hi I am new to databases and relational algebra. I was wondering if there is a way to remove the tuples from a table using relational algebra that have the same keys but different value.
e.g. I want ...
0
votes
1answer
16 views
Creating a relationship between a document and a collection in MongoDB
I have no idea if this is even possible but I'd like to find out if there's some way to create a relationship between a document and a whole collection. I have a user schema that looks something like ...
0
votes
1answer
94 views
BCNF decomposition step without functional dependencies
I am performing the BCNF decomposition for the relation R = (A, B, C, D, E), that has the set of functional dependencies AB -> C, CD -> A, C -> E, and C -> B.
After checking that the ...
0
votes
0answers
86 views
Proof Full Outer Join associative and Commutative
I'm trying to prove to myself that the order of full outer joins doesn't matter, but, in an abstract sense, I'm coming up with nothing.
How would one go about proving that the order of a set of FULL ...
0
votes
0answers
22 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 (...
0
votes
0answers
24 views
SARGABLE predicate must be in DNF. Why?
This famous paper about finding Access Path in Relational Database Management System by Selinger et al. says the following about criteria tree and predicates (at the end of 3rd section).
A sargable ...
0
votes
1answer
34 views
Relational database field name design [closed]
Are there design patterns for naming table fields? For example, I've always used user (table)> email (field), but I see other well-used modes like user (table)> user_email (field). Are there any rules ...
1
vote
0answers
22 views
Using relational algebra to check a condition for all
I encountered this question while revising for my finals exam on database theory.
The following database contains information about car repair workshops. The following tables are used:
...
0
votes
4answers
304 views
Why is it considered that a set unnormalizes a database absolutely?
In order for a relationship to be on 1NF it needs to have all values as atomic, if there's a set it's not even in that first normal form:
But intuitively I think that a table with that set would be ...
0
votes
1answer
71 views
See direct (instead of indirect) info about rented book in my “actors_directors” talbe dropdown list - Access365
I'm building a database of "actors_directors" who are renting "books" from my library - a kind of exercise to learn database basics :-)
This is how my "actors_directors" table looks like. They are ...
1
vote
1answer
38 views
Entity Relationship Diagram - Entity depending on attribute
I have an ER-Diagram containing the entity employees. When employees have high skills they may supervise lower skilled employees. How can I model that in an ER-Diagram?
My initial approach of was to ...
2
votes
1answer
78 views
Why is this relation not in Boyce-Codd Normal Form (BCNF)?
I have the relation (A,B,C,D,E).
The relevant functional dependencies are:
{ E,B,C } ā { D }
{ A,B } ā { C }
{ E,B,D } ā { C }
{ E,A,B } ā { D }
The candidate key is (E,A,B).
I do not understand ...
1
vote
1answer
170 views
Finding all possible minimal covers
I have a relation schema R = {A, B, C} and the following functional dependencies:
A ā B
A ā C
B ā A
B ā C
C ā A
C ā B
How many different minimal covers can I derive from this relation schema? I have ...
1
vote
1answer
70 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 ...
-1
votes
1answer
36 views
How do I bring a relation/table to first normal form (1NF) when one of the attributes/columns can hold any number of values?
For example,
MOVIES (movieid, movietitle)
where movietitle can be any number of words, so I believe it's not atomic.
How do you bring something like that to 1NF?
3
votes
1answer
143 views
Normalization with two potential primary keys
I have the following STUDENT relation in first normal form, where id has been identified as the primary key (email here is also unique for all tuples):
STUDENT(id, email, first_name, last_name)
I ...
2
votes
0answers
50 views
Design issue in transitive dependencies between tables
This is a made-up scenario, but I imagine that it can be of general interest, so I'll post it here. Imagine the following business rule:
A course has a grade scale that determines what grades a ...
4
votes
2answers
121 views
Does a computed column break 3NF (third normal form)?
From my understanding of 3NF, there should not be any transitive dependency.
However, this is what I found from the AdventureWorks2012 database Production.WorkOrder table. Since StockedQty is ...
1
vote
1answer
69 views
Show that functional dependency pseudotransivity holds using closure test
I'm currently studying a basic database course with the course literature Database Systems - The Complete Book1.
In one of the book's exercises you are asked to
Show that the following rules hold,...
3
votes
1answer
44 views
Determining minimal cover of a relation
I am having trouble understanding why a certain set of functional dependencies is not minimal.
We have a relation R(A,B,C,D,E,F,G) with the following dependencies F:
1. A->CDE
2. B->FG
3. AB-&...
2
votes
1answer
730 views
Does using composite keys violate 2NF?
From all of the database related reference books that I have come across, there is a universal rule that state a database table should be at least 3NF.
From what I understand, a table is considered ...
5
votes
1answer
141 views
Why should foreign keys be implemented in my relational database? [duplicate]
Earlier today, I sparked a rather unexpected debate with my professional peers on the topic of foreign keys in our company's databases. In a nutshell, a software engineer who is senior to me stated ...
2
votes
0answers
43 views
Relational Algebra: how to express the combination of aggregation and projection?
Here I have a table, I would like to query the average salary of departments, the SQL statement should be
select dept_name, avg(salary) from employee
I try to express this SQL in relational algebra....
0
votes
0answers
25 views
Write aggregate function in select predicate in relational algebra
Can i write aggregate function in select predicate in relational algebra?
I am trying to find the maximum salary in the table Employee(name,salary)
I am doing this to get max salary.
Ļāāāāįµ£įµ§=Gāāāā...
1
vote
1answer
49 views
Can and should relational database contain tables that don't have primary keys? [duplicate]
So this site says: "All tables in a relational database should have a primary key". Here it also says that every table should have a primary key.
But if I look here and here they say that tables ...
0
votes
1answer
71 views
Decompose a relation to 2NF and preserve all the functional dependencies
For example, consider the following relation:
R(A, B, C, D, E)
With the functional dependencies (FDs) shown below:
{AāB, BCāE, DāC}
Here candidate key is {AD} and the relation is not in second ...
1
vote
0answers
284 views
Is a relation with two attributes always in Boyce-Codd Normal Form (BCNF)?
I went through some tutorials on BCNF and got that a relation with two attributes is always in BCNF.
My question
What if a relation with two attributes has no prime or non-prime attributes? That is,...
9
votes
2answers
2k views
Is a table without a primary key normalized?
In a lecture, my lecturer showed us a table without a primary key. Upon questioning, he said that in 3NF when you remove transitive dependencies it's ok to then have a table without a primary key.
...
4
votes
2answers
286 views
Is saving disk space still considered an advantage of normalization?
I have read a while back that one of the advantages of normalization is to save disk space (since normalization minimizes redundancy), but is saving disk space still considered an advantage of ...
5
votes
2answers
241 views
Does the concept of candidate key exist only in theory?
I know the concept of candidate key in RDBMS theory, but do candidate keys really exist in actual SQL engines? I mean is there any way to designate a particular column or set of columns as a candidate ...
2
votes
0answers
48 views
How To Query Databases in Subject-Predicate Format?
I have csv data in this format:
id fieldname fieldvalue
-- --------- ----------
1 fieldA A1
1 fieldB B1
2 fieldA A2
2 fieldA B2
If I upload this to MySQL, how can I ...
2
votes
1answer
422 views
What is the meaning of “minimal key”?
In the definition, it says minimal key is a minimal set of attributes needed to identify the table. Does the "minimal" part mean "minimal number of attributes" or "cannot be eliminated"?
For example:
...
2
votes
0answers
464 views
How to write group by and having in a relational algebra interpreter?
I am practising writing relational algebra (RA) expressions in RA interpreter from Duke, but I can't figure out how to represent group by or having (from SQL). I have this expression, but I need to ...
4
votes
0answers
112 views
Can the Chase test algorithm only modify attributes on the right side of a functional dependency?
I'm attempting to apply the Chase Algorithm to determine if a decomposition is a lossless join decomposition.
Functional Dependencies = {A ā C, B ā D, C ā D, DE ā C, CE ā A}
Here is my initial ...
2
votes
1answer
224 views
BCNF decomposition does not prevent insert anomaly?
I am in the process of designing a schema for a simple polling app and have tried to normalize it with the decomposition algorithm from this lecture.
Requirements
A poll has a unique id (poll_id), a ...
1
vote
0answers
80 views
Equi Join On Union- and Intersection-Compatible Relations
Suppose that I have two union- and intersection-compatible relations; i.e., R1(A,B) and R2(C,D) as:
Now , I want to find the join of both relations with equality conditions A=C and B=D.
I have ...
1
vote
0answers
19 views
Finding Exclusive Pairings in Relational Algebra
classId | studentId
0 0
0 1
0 2
1 0
1 1
1 2
2 2
How do you find the pairs of students (s1, s2) that ONLY have classes with ...
-1
votes
1answer
209 views
Inserting data into a 3NF database [closed]
I have 6 tables with ~2-15 attributes each, each one of these 6 tables has an ID which is constrained from another table. This 7th table has its own ID and the 6 other table ID's, as well as a date ...
3
votes
0answers
97 views
Third Normal Form (3NF) Decomposition Scenario
I am trying to understand what are the steps to check if a decomposition of table is in 3NF.
There is a specific exercise I'm trying to solve with no success.
Given that R={A1,A2,A3,A4}
and F={A1ā...
