Tag Info

Hot answers tagged

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

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

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

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


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

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


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


3

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). It may also help to have an example you can really wrap your head around. The only time ...


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

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

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

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

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

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.



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