Hot answers tagged

8

You're right on the money with the possible candidate keys, vikkyhacks. Overlapping candidate keys are composite (consist of more than one attribute) candidate keys with at least one attribute in common. So your overlapping candidate keys are NM and NO (they share N). Additional explanation of the above, originally left in comments: All overlapping ...


8

A functional dependency is exactly what the term implies - the output of the function is always determined by the input. If for example we have a function f(), and provide variable x, and we always receive output y, then y is functionally dependent on x. You can think of this like a simple graphing function 2x + 1 = y Plugging some sample values into the ...


7

The 2012 Books Online topic "Get Information About a View" states that the permissions required for this specific task are: VIEW DEFINITION permission on the database; and SELECT permission on sys.sql_expression_dependencies Note that database-level VIEW DEFINITION is required to allow the user to see information in sys.sql_expression_dependencies; ...


4

The mistake is in your understanding of transitive dependency. From wikipedia: Transitive dependency In mathematics, a transitive dependency is a functional dependency which holds by virtue of transitivity. A transitive dependency can occur only in a relation that has three or more attributes. Let A, B, and C designate three distinct attributes (or ...


4

A candidate key is a set of attributes that constitute a minimal superkey. Two candidate keys, A and B, are said to overlap if they have some attributes in common, i.e.: A ∩ B is non-empty. In your case, MN and NO would be overlapping candidate keys in R. Because of the minimality (irreducibility) requirement one candidate key can never be a subset of ...


4

I don't think there's a surefire method to find everything. After all, they could have access to things merely by virtue of being in a specific server or database role, or even a Windows AD group (you didn't specify if this is a SQL auth login or a Windows login). There also isn't a surefire way to identify what might break if this is changed - for example, ...


4

but the index names are NULL (and index id is 0) That's because they are heaps. BOL Reference on sys.indexes One way to get these off of that partition scheme would be to create a clustered index for those tables and specify a different filegroup or another partition scheme. Then once you have those tables off of that partition scheme you should be ...


4

There are so many things outside of SQL Server's metadata that may depend on the size of a column, it's not even funny. Here are a few things you'll need to check: Any variable or parameter declarations in stored procedures, dynamic SQL or ad hoc SQL that may pass values to that column, filter on it, search from it, or get assigned from it. Any references ...


3

From a set of FDs, you can only infer a set of candidate keys. A "primary key" is one particular member of that set of candidate keys that you elect to be "primary". And there are no scientific rules that tell you which one you should choose - you can pick just any key and label it "primary". In your first example, you should end up with a set of ...


3

Under the assumptions that you have some columns (say x and y in your example), that you don't know if they are functionally equivalent or not - and that these columns do not have any NULL values (which would complicate things), you can use: SELECT u, v, w, -- the grouping columns AVG(z) AS z_avg, -- the ...


2

A,B and C are sets of attributes. All examples I've seen in books are like Employee ↠ Project | Dependent, where Employee simultaneously determines two attributes. A ↠ B | C is a special notation different from A ↠ BC. It's useful because MVDs always come in pairs. It says that both A ↠ B and A ↠ C hold. This implies that the relation equals AB JOIN ...


2

The important thing about the 2NF is that in each (non trivial) dependency the determinant should not be a proper subset of a key. In the example, the determinant of AB->C is the full key, while the determinant of C->D is C, which is no part of any key. So the schema is obviously in 2NF.


2

The only candidate key of your relation is {D DA HA L NF} (perhaps with R you mean D?) You can verify this by calculating the closure of those attributes, {D DA HA L NF}+, and seeing that it contains all the attributes, while, if you remove any one of them, the closure of the remaining set does not contains all the attributes (this is the definition of a ...


2

Does the relation create information redundancy? Yes, for instance you have the information on the file (name, size, data creation, etc.) repeated for different accesses to the same file. What would a decomposition of R without loss of information and without loss of dependency be? The following decomposition is both in Third Normal Form and in ...


1

This is rather easy. AB → C B → D therefore: AB → C AB → D and: AB → CD Next: AB → CD CD → E therefore: AB → E with: AB → C we get: AB → CE And finally: CE → GH thus: CE → G and with the (previous): AB → CE AB → G


1

A FD is a MVD "in disguise". If an FD holds then a certain MVD also holds. With MVDs 1 is a special case of multi(ple). This can be seen from the rules of inference for MVDs at the definition link: RA → B implies A ↠ B. Hint: t1 & t2 can be t3 & t4. Hint: In the example there are only two tuples, so t1 & t2 must be those, and you know the MVD ...


1

We have X (A, B, D, E, F, G, H, J) with A and B as primary key Y (K, L, M, N, O, P, Q, R) with K and L as primary key With functional dependencies: A -> G, H B -> D, E, F E -> F H -> G L -> N, O, P, Q N -> Q 2NF Normalization R1((A,B) (PK), J) R2(A (PK) ,G,H) R3(B (PK) ,D,E,F) R4((K,L) (PK), M,R) R5(L (PK) ,N,O,P,Q) 3NF ...


1

For convenience, define the (implied) numbering of your question: A->D BD->E AC->E DE->B Then substituting 1 and 3 into 4 gives (A)(AC) -> B which reduces to just 5. AC -> B.


1

Given that you can not afford to drop and recreate the table, this related answer would be a better fit: Best way to populate a new column in a large table? You might drop expendable indexes and recreate them when you are done (if they aren't completely expendable). And all the general advice for performance optimization applies. There is not much more ...


1

The first, you should read the NORMALIZATION concepts (1NF,2NF,3NF,...) after that you can use them to verify your dependency diagram. So, I'm talking about some basic steps to help you convert ERD, which I often do: 1- Identify objects (objects are WHO, WHERE, WHAT, WHEN ), you imagine that they are exited and can be defined. 2- Identify natural keys of ...


1

I would use (title,prod_date) as the primary key, since movies are not uniquely identified by their title alone (remakes, for example). In my opinion, the first diagram is preferable, the sub-attribute approach in the second diagram seems a bit convoluted to me.


1

I'd put in stock something like movie_product (assuming you are not going to sell anything but movies) which can look like movie_product (movie_product_id , movie_id, format_id, manufacturer_id, date_produced, --maybe some other attributes ) You need to decide yourself what is important to store in the context of your domain (say, you don't care about ...


1

Based on the relation you provided, for BCNF we know that {T} and {U} are candidate keys that functionally determine {V}. The transitive dependency would occur if {U} was not a candidate key, but remained a determinant.


1

You can use pg_depend table to resolve that :  select objid::regclass from pg_depend where refobjid = (select oid from pg_namespace where nspname='mySchema');


1

here's a quick and ugly query that make the job : with nsp_oid as ( select oid from pg_namespace where nspname='mySchema' ), dep_oid as ( select p.objid as oid from pg_depend p join nsp_oid n on refobjid = n.oid ), rew_oid as( select p.objid as oid from pg_depend p join dep_oid d on p.refobjid = d.oid where ...


1

The first three FD's combine to give MNOP -> L so MNOP is one possible candidate key, CK1. Similarly the last three FD's combine to give NOPL -> M so NOPL is another possible candidate key, CK2. However CK1 and CK2 have columns NOP in common, making them overlapping candidate keys.


1

First, a name like 'entities' is too vague. The common name for org or individual is 'parties'. Second, people can have zero, one, or many addresses, and can share the same address. It's a many2many relationship. I would do it like this (using STI here, but break it out to CTI if you like): PARTIES id type (org or individual) name ADDRESSES id type ...


1

The union between A and be is a superkey. (A candidate key is a superkey but a superkey could be not a candidate key) Moreover, remember that a candidate key is a particular type of superkey, so you shouldn't worry about if it is "both" key and superkey.



Only top voted, non community-wiki answers of a minimum length are eligible