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.
380
questions
1
vote
2answers
18 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
36 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
47 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
26 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
32 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?
2
votes
0answers
40 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
38 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
75 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
31 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
35 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
166 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
99 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
12 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
16 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
43 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
31 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
67 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
1k 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
166 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
174 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
47 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
222 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
216 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
91 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
181 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
71 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
98 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
82 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
130 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
50 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
38 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
163 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
68 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
39 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
240 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
1k 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
106 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
31 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
371 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
230 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
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 ...
2
votes
1answer
45 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
119 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
105 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 ...
4
votes
2answers
4k 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
2k 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
164 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
...
