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.
9
votes
2answers
673 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
127 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
102 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
39 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
91 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
83 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
74 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
161 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
47 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
17 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
55 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
61 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→...
5
votes
1answer
81 views
For BCNF decomposition, is it okay to have a relation with none of the original functional dependencies?
Given the relation R[c, f, g, h, e, j, a, b, d, i] with the below functional dependencies I have come up with the below solution. However, I am unsure about it, considering R8 has none of the listed ...
2
votes
1answer
39 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 ...
2
votes
1answer
30 views
Are these relations in BCNF?
I have a homework question that I am trying to understand but having a tough time with this question. Any explanation or even pointers would be very appreciated (no exact answer expected - I am trying ...
2
votes
1answer
150 views
Is sql_variant compatible with First Normal Form (1NF)?
1NF rule says that we should not keep data of different types in one single column. Does it mean that sql_variant is not compatible with first normal form hence should not be used?
2
votes
1answer
57 views
Questions Concerning the Chase Test
For the question above, my work for each of the questions is below the concerns. Here are my main concerns:
Does the order in which you use the relations matter?
Can you end up with less tuples with ...
1
vote
1answer
36 views
Question concerning minimal/cannonical cover
For the question above, I am confused with how to approach simplifying the left hand side of BDCE->A. Do I get rid of E, as D depends on it, or do I get rid of CD as well, as CD->A?
Here's what I ...
4
votes
3answers
218 views
What normalization rules does this table break
Our previous DBA got fed up with the development teams frequent requests to change the database schema to add and delete columns. He then advised to the developers that he would create simple tables ...
1
vote
1answer
807 views
How to represent NULL in relational algebra?
In the 3rd part of the question below:
Study the tables given below and write relational algebra expressions for the queries that follow.
STUDENT(+ROLLNO, NAME, AGE, GENDER, ADDRESS, ADVISOR)
...
3
votes
1answer
87 views
Is it Important to Understand Each Normal Form?
I have been studying database design and programming for quite some time now, but I still can't get a grasp of understanding each individual normal form (that is, 1NF, 2NF and 3NF).
Seeing as anytime ...
0
votes
1answer
30 views
Relations and Sets
From my understanding, One = {1,2,3,4,5...20}, and then if Two = {1}, then One won't map into Two. On that token, how come the answer is d instead of a?
2
votes
1answer
62 views
Assessing the normal form for a relation with a multi-valued attribute
Assume that you have designed the following schema of relations:
COURSES (CID, CNAME)
STUDENTS(SID, SNAME, FAV_CIDS)
However, you can not have a relation with set valued attributes (Did not really ...
3
votes
0answers
196 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
vote
1answer
115 views
Equivalent of SQL’s “INNER JOIN USING” in relational algebra
I'm following a course in databases (Standford introduction to databases on YT) and, at some point, it's mentioned that t1 INNER JOIN t2 USING attr1, attr2 is equivalent to the NATURAL JOIN symbolized ...
2
votes
2answers
51 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 ...
2
votes
1answer
44 views
If A -> B is augmented with C, is AC -> B legal?
Given the augmentation axiom and the decomposition axioms of functional dependencies and a relation of R(A,B,C) with an FD of:
A -> B
I can augment A -> B with C to get AC -> BC.
Can I then ...
2
votes
1answer
101 views
Assessing statement about third normal form
Consider the following statements:
A relation R, is in 3NF if every non-prime attribute of R, is fully functionally dependent on some key of R.
State True or False.
The answer given ...
3
votes
1answer
93 views
In which normal form is the relation R?
Hello Community,
To prepare for my exam I have completed many exercises. Most of them were solvable without any problems. But the following task I could not solve until now. It is:
You are given ...
3
votes
2answers
3k views
How does normalization fix the three types of update anomalies?
I have been reading 'Database Systems: A practical approach to design, implementation and management' by Connolly and Begg in order to understand database normalization (chapter 14). I now better ...
5
votes
2answers
1k views
Difference between functional dependency and join dependency
By definition, a functional dependency is defined as
Let X and Y be subsets of attributes of a relation R. A functional dependency X → Y holds over R if and only if for any instance r of R,
...
3
votes
0answers
145 views
Difference Between Implication and Conjunction in Domain Relational Calculus
I was reading my notes, and I came across this:
Another example would be to find the names of the lecturers teaching all modules. We might reach for the following incorrect expression:
{<N&...
4
votes
1answer
95 views
Do my relational algebra operations produce the desired results?
I have these tables:
Sailors
sid sname rating age
--- ------ ------ ---
22 dustin 7 45
31 john 8 55
58 ben 10 35
Boats
...
1
vote
1answer
51 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 ...
2
votes
1answer
125 views
BCNF decomposition excercise
I have a relation R(A,B,C,G,H,I) with the functional dependencies (FDs for brevity) F = {A→B; A→C; C,G→H; C,G→I; B→H}. I want to decompose it into BCNF.
So, we have AG as candidate key. I consider ...
0
votes
0answers
269 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 ...
1
vote
0answers
128 views
Quantifiers in Tuple Relational Calculus (Existencial and Universal)
Suppose this state for the relation schema Company
I'm stuck trying to understand how, why and when I should use Quantifiers (Existencial and Universal)
Examples:
Here I understand how the ...
6
votes
2answers
1k views
How are joins Commutative and Associative?
I have read all over the place that joins are associative and commutative.
So A join (B join C) should be the same as (A join C) join B.
But I have a really hard time understanding how this can be ...
3
votes
2answers
169 views
Is combining independent tables called normalisation?
So, at workplace, we currently store our audit logs in different tables, depending on what it is e.g. login/access info, configuration changes, etc.
These are all independent data with no foreign ...
3
votes
3answers
101 views
How to handle variations of the same data point?
I have a database with three tables:
books,
authors and
books_authors
books_authors is a “junction” table (i.e., it represents a conceptual level many-to-many relationship), so if it has a row ...
2
votes
0answers
103 views
Understanding multivalued dependencies
This is my first time studying multivalued dependencies and there is a question that was posted by my lecturer as a class exercise. I have an answer, but I'm not 100% sure it was right. Please let me ...
1
vote
1answer
844 views
Converting a relation into BCNF
I understand that to convert to BCNF, we need to take into account all violations and decompose as necessary with each FD.
For example, if we had R(A,B,C,D) with FD's AB->C, B->D, C->A. We can ...
3
votes
0answers
2k views
How do you perform the chase test?
Trying to follow the chase algorithm as I understand it,
If two rows agree in the left side of a functional dependency (FD for brevity), make
their right sides agree too.
Always replace a ...
3
votes
1answer
2k views
Converting SQL to Relational Algebra / Calculus
I have designed a schema and generated a few SQL queries. I'm using PostgreSQL.
For example:
CREATE TABLE train
(
train_code SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE journey
(
...
1
vote
2answers
926 views
Find the highest graded student in each state, using Relational Algebra
Suppose I have a table of students, containing their ID, grade and state:
-------------------------
| id | grade | state |
------------------------
| 1 | 83 | CA |
| 2 | 94 | TX ...
-1
votes
1answer
239 views
Normalizing with “Relational Database Tools” [closed]
I am trying to use the Relational Database Tools to check if my schema is in Boyce-Codd Normal Form and to understand the relevant functional dependencies.
However, I am having trouble "converting" ...
2
votes
0answers
26 views
Do Selection attributes have to appear in Projection list in Relational Algebra
In extended relational algebra (aggregation being allowed) do you have to project all attributes that you use in a statement? So for example in the query below the attributes A.FO_ID and A.Arrival are ...
26
votes
6answers
2k views
Why the term “relation(al)”?
In english, we might talk about the relation between, say, Bob and Tim. Perhaps they're cousins. The term "relation" in this context makes sense to me.
In the context of relational databases, I ...
1
vote
2answers
2k views
Counting distinct entries in a column using relational algebra
So I have a table similar to this one. Each user has posted a review about one or more hotels(A,B,C,D) but on different dates so there are no duplicate tuples even though a person might have reviewed ...
1
vote
1answer
72 views
Finding fuctional dependencies and candidate keys
I have number of files, each containing all reviews for a specific hotel (one file corresponds to one hotel, e.g. hotel_2194.dat). Each review is of the type:
<Author>PERKEISHA
<Content> ...
