New answers tagged

1

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

You can create a table ProductPrice: CREATE TABLE ProductPrice ( ProductID int NOT NULL, ProductSetID int NOT NULL, StoreID int NOT NULL, Price numeric(10,2) NOT NULL, PRIMARY KEY (ProductID, ProductSetID, StoreID), FOREIGN KEY (ProductID, ProductSetID) REFERENCES ProductProductSet, FOREIGN KEY (StoreID, ProductSetID) REFERENCES Store ) ...


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 ...


2

If your attribute item_number is unique, you can leave it in your original table even in case it can have null values. In fact the PostgreSQL manual says: For the purpose of a unique constraint, null values are not considered equal. So this could be right solution: CREATE TABLE product ( sku text PRIMARY KEY, name text ...


8

1. Assuming that sku and item_number will always hold unique values I consider that you have come up to the answer by yourself, since an item_number is an optional attribute (or column at the implementation phase), i.e., it does not apply to all the product occurrences, therefore, from a logical point of view, it decidedly cannot (should not) be declared as ...


3

Without any knowledge about the workload of the database, is almost impossible to answer to a question like this. How often those “extra” data will be used? What are the most common queries? How frequent they are? and how much it is important the speed of the execution? The general advice is always the best one for me: make things as simple as possible, ...


2

First of all, note that the original relation is already in Third Normal Form, since each attribute is prime (each attribute is a key, actually), so that the definition of 3NF is respected. Then, note that the algorithm is incomplete. The steps are: Find a minimal basis of F, say G For each groups of FD with the same left part, X → A1, X → A2, ..., ...


2

Just looking at an example of a table is a wrong way of normalizing. You should understand the meaning of the fields to properly normalize. For instance you should know if the salary is the same for all the persons that have a certain role, or if it differs from person to person (in the above example we cannot know, since each role has only a person, so that ...


3

There are several models for hierarchical data in relational databases. These include Adjacency List. This is your suggestion above. Path Enumeration. This is the DBA's suggestion. Nested Set Closure Table Each has a different insert, delete, neighbour-read and set-read characteristic. You will have to determine which, on average, is best for your ...


3

A. Canonical Cover The example is almost a canonical cover, since in a canonical cover each dependency has only an attribute on the right part, so the dependency C → DF must be replaced by the two dependencies: C → D C → F B. Second step of the 3NF Synthesis Algorithm The step says to divide the functional dependencies in groups with the same left ...



Top 50 recent answers are included