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.

Filter by
Sorted by
Tagged with
-2
votes
1answer
30 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
54 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
29 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
54 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 ...
1
vote
1answer
54 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 ...
2
votes
1answer
42 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
30 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
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 ...
1
vote
1answer
33 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,...
2
votes
1answer
163 views

Lossless Join Decomposition Criteria

On Wikipedia, it says: The decomposition is a lossless-join decomposition of R if at least one of the following functional dependencies are in F+ (where F+ stands for the closure for every ...
4
votes
2answers
76 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 ...
2
votes
1answer
224 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 ...
3
votes
1answer
36 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
0answers
14 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....
7
votes
1answer
644 views

Convert query in words to relational algebra

I have a follow-up question to a question I have previously asked on SO. Instead of the query from my initial question, I want to convert the following into relational algebra: List the names and ...
5
votes
1answer
110 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 ...
0
votes
0answers
20 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ₘₐₓ₍...
28
votes
6answers
6k views

Arbitrarily ordering records in a table

A common need when using a database is to access records in order. For example, if I have a blog, I want to be able to reorder my blog posts in arbitrary order. These entries often have lots of ...
0
votes
1answer
89 views

Create one-to-many to two fields in different table

I have a database where I am trying to figure out how to join a table that contains an object called ModelType. The calibration table contains two fields (EffModel and BkgModel) that are based on a ...
0
votes
1answer
86 views

Schema check or “DATE as separate table”

I'm dusting of my SQL, and would love to know if I've got my mental ER-diagram right. http://sqlfiddle.com/#!2/c5c59 Basically, I've got 10-15 different datasets (actividades) from several locations ...
3
votes
1answer
436 views

Method to Obtain Lossless Join Boyce-Codd Normal Form (BCNF) Decomposition

I have been told a way to obtain lossless join BCNF but I don't know how to calculate candidate keys (also called super key[s] in some cases) and trivial dependencies. I have been given the ...
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
36 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
76 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,...
5
votes
3answers
19k views

Relational Design - Multiple tables into one foreign key column?

Take a table that tracks meat purchases. It has a "meat_id" foreign key column to indicate what type of meat the purchase was. But, the different types of meat are unique in some way (such as USDA ...
5
votes
2answers
181 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 ...
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 ...
0
votes
1answer
351 views

How to find functional dependancies from simply looking at a relational schema?

If only given a schema (table header, with primary key indicated), how do I find functional dependencies? My problem is that functional dependencies are determined in terms of the data stored in the ...
1
vote
1answer
263 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 ...
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
181 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 ...
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
12k views

Check if relation is in BCNF

I have the following relation and function dependencies. R = ABCDE F: C->AB, D->A, BE->CE, E->B I have to check whether this relation is in BCNF. Wikipedia states: ...if and only if ...
2
votes
1answer
241 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
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 ...
9
votes
1answer
3k views

Designing a friendships database structure: Should I use a multivalued column?

Say I have a table called User_FriendList, which has the following characteristics: CREATE TABLE User_FriendList ( ID ..., User_ID..., FriendList_IDs..., CONSTRAINT User_Friendlist_PK ...
2
votes
1answer
189 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 ...
2
votes
3answers
88 views

Is Order part of a result set?

This question got me thinking: ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) doesn't use index on (A,B,C) In it the questioner states two functions give the same result set, however they don't ...
2
votes
0answers
263 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
92 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 ...
10
votes
3answers
1k views

Integrity constraints in a relational database - should we overlook them?

I’m in a permanent discussion with the developers of the company where I work because they say it is better to get rid of relationship enforcement (via FOREIGN KEY constraint definitions) in a ...
1
vote
0answers
73 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 ...
7
votes
3answers
1k views

How to express this constraint in a database schema?

I have the following functional dependencies which are in BCNF: a,b -> c a -> d b -> d With the additional constraint, that no a and b should be combined with a c, where a and b have ...
3
votes
0answers
170 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&...
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
vote
1answer
34 views

How to normalise a relation using functional dependencies

G = {{A,B}→{C},{B,D}→{E,F},{A,D}→{G,H},{A}→{I},{H}→{J} }. Which is a key attribute: {A,B} or {A,B,D}?
-1
votes
1answer
112 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 ...
4
votes
1answer
216 views

What can be represented in a relational database that can't be represented in a graph database?

I read Blockeel's article that is comparing different paradigms how to represent data. He mentions following advantages of the relational paradigm in comparison to graph paradigm: They can naturally ...
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→...