New answers tagged

2

As @David-Spillet mentioned, I would recommend against the IDENTITY column in the junction table. I would recommend creating a clustered index with a primary key on both columns, such as: CREATE TABLE dbo.WordRelationship ( HeaderWordId INT NOT NULL, OtherWordId INT NOT NULL, CONSTRAINT PK_WordRelationship PRIMARY KEY ...


4

That is pretty much how you have to do it. The relationship table for a many-to-many link like that is often called a junction table (though goes by many other names on occasion: map table, mapping table, bridge table, ...). If you have more than one class of relationship between words (abbreviation, antonym, synonim, ..) than that should be in the junction ...


4

You have at most 1000 rows from the natural join of R1 and R2 because there are two possible cases: either 1) all the values of R1.C are present in R2.C (i.e. R1.C is a “foreign key” for R2), or, 2) there are values in R1.C not present in R2.C. In the first case you have exactly 1000 rows in the join, since the for each row of R1 there is exactly one row ...


6

You are right, the design allows inconsistencies, exactly what you notice. A ProjectRealm may be referring through Project to a company and through CompanyRealm to another company. This is not uncommon, it appears when there is a triangular or a "diamond" shape in the relationships: Realm Company \ / \ \ / \ ...


1

Not knowing your full situation, I recommend something like this. The foreign keys should create the dependencies you need. If your SQL structure is already in a tabular format, you might consider putting this information into temporary tables--like the ones below. But it looks like your tables are there according to your assertion comment. From what you ...


2

When you have a relation R with a set of functional dependencies F and a decomposition of R in R1...Rn, you must consider two different concepts: The closure of the set of functional dependency F. This closure, called F+, is the set of all the dependencies derived from F, by applying, until possible, a set of rules called “Armstrong’s axioms”. This set can ...


5

The last two lines are an abbreviated way of solving the problem without recurring to the complete algorithm to check for dependency preservation. In particular the teacher noted that combining the dependencies that you can obtain from R1 and R3 you cannot obtain C, which is essential to get A in the dependency BC → A. This dependency can never be derived ...


1

I'd better say F¹⁺=∅ F²⁺=∅ F³⁺={AD → E, B → D, AB → D,AB → E} F⁴⁺={E → G} and (F¹⁺ U F²⁺ U F³⁺ U F⁴⁺ ) != F


2

The relation is actually in 3NF (so that it is also in 1NF and 2NF). The reason is that each attribute of the relation is prime, that is, it belongs to a (candidate) key (the are four keys in this relation: (A X), (A Z), (X Y), (Y Z)). The definition of the 2NF (which has only an historical interest), is the following (Database System Concepts, 6th edition, ...


1

Community Wiki answer generated from comments on the question by Renzo and ypercubeᵀᴹ The definition of functional dependencies requires that they must hold for every possible instance. From an instance, you cannot find the functional dependencies that hold in a certain relation schema. You also missed A → D.


3

Your decomposition is not correct, since in R2 you still have dependencies that violates the BCNF, for instance UtilisateurID → Nom (UtilisateurID is not a key of that relation). The problem is that your algorithm is not correct. When you find a dependency X → A that violates the BCNF, you should decompose a relation in two relations, the first with X+, not ...


1

Normal forms are used to eliminate or at least reduce redundancy in data, as well as to eliminate the so called insertion/deletion anomalies. The BCNF eliminates redundancies and anomalies, but decomposing a relation in BCNF sometimes has the unpleasant effect of causing the loss of one or more functional dependencies during the process. For this reason ...


0

Here's a condition that will help you in the future to check if a relation is in 3NF (it checks 2NF implicitly, i.e can be applied on any relation directly without checking 2NF) : A relation is in 3NF if for every non-trivial functional dependency(X is not a superset of Y in X->Y) the following 2 conditions hold 1) Either X is a superkey 2) or Y is a ...



Top 50 recent answers are included