say we have a fixed table T. Is there a unique normalized version of T, i.e., does the/a correct normalization, say up to 3NF produce a unique table up to some equivalence? Basically, I am trying to find out how many different correct ways of normalizing a table there are. Thanks.
123304
I"m studying normalization and have been reading through descriptions and examples online. One Concept that isn't clear to me is if the process can be automated at any point. I understand that a system can't tell you if you schema makes any real-world sense, but it seems likely to me that if you feed a program some arguments say, the functional dependencies, it would be able to do some steps for you.  Say you have a relationship R(A,B,C,D)  and you tell your program that A,B->C and A->D and that the primary key is (A,B)  Now if my understanding serves me correctly it should be possible for a program to identify that A->D violates 2NF because D depends on A and A is a subset of the primary key. That the proper decomposition would be R1(A,B,C) and R2(A,D).   The main book i'm learning from and my instructor indicate these normalization's are done all by hand. Is this correct or can some parts be automated?
99570
I've just been adding an Index to a table in SQL Server 2005 and it got me thinking. What is the difference between creating 1 index and defining multiple columns over having 1 index per column you want to index.  Are there certain reasons why one should be used over the other?  For example  Create NonClustered Index IX_IndexName On TableName (Column1 Asc, Column2 Asc, Column3 Asc)   Versus  Create NonClustered Index IX_IndexName1 On TableName (Column1 Asc)  Create NonClustered Index IX_IndexName2 On TableName (Column2 Asc)  Create NonClustered Index IX_IndexName3 On TableName (Column3 Asc)
30786
I can not figure this out.   I started off by making a table and putting the base(2) on one side, the number in the middle and the remainder in the third row and for 2010 I got 11111011010 but I cant find the next two numbers please help and please explain in detail.  Thank you =)   to express 2010 in base 2, we use exactly 8 ones and 3 zeros. find the next two integers in base 2 that use the same number of ones and zeros
93719
I'm struggling with the design of new database that has to use a different collation (Y Collation) than the SQL Server Instance default collation (X Collation). I'm looking for potential problems.  Temp Tables by default will be created with X Collation so any joins on text data types will fail unless I create temp table with Y collations on columns.  What about SQL Server string functions: REPLACE, SUBSTRING, etc.? Will they always be returned in X collation? What about when I use REPLACE(...) COLLATE Y? Do you see any potential data loss or maybe any different problems?
84584
I have revised a procedure that builds a dynamic SQL statement to create and populate a temporary table. The syntax was something like this:  ...'create #temp_' + CAST(GETGUID() AS VARCHAR(36)) ...   I asked a colleague if he knew why a unique identifier was being concatenated to the table name. He told me that in the past there were cases where temporary tables were shared across sessions and the data was messed up.  I know this is not possible since MSDN clearly states:     You can create local and global temporary tables. Local temporary   tables are visible only in the current session, and global temporary   tables are visible to all sessions.   Now (remembering the case) some colleagues think we should use the syntax above as a good practice but I do not like it and need proof there is no such bug or if it was it is already fixed.  Could anyone with experience in SQL Server 2005/2008 tell me if there ever was such case?
43633
I have a problem with second normal form (2NF) and I have been unable to solve it by using Google. It is making me crazy because I am a teacher and I don't want to teach wrong stuff to my students.   Let's have a table with 5 fields.  Gradings = {StudentName, SubjectCode, SubjectName, #Exam, Grade}  Dependencies are this way:  StudentName, SubjectCode, #Exam -> Grade  SubjectCode -> SubjectName  SubjectName -> SubjectCode  Therefore, candidate key 1 is {StudentName, SubjectCode, #Exam} and candidate key 2 is {StudentName, SubjectName, #Exam}.   Prime attributes are {StudentName, SubjectCode, SubjectName, #Exam} and non-prime attributes is Grade  According to the definition of second normal form, a non-prime attribute cannot depend on a part of a candidate key. The only non-prime attribute (Grade) does not depend on a part of a candidate key so this table seems in 2NF .  The problem is that I think something is amiss (and I could be wrong). I think subjects should have their own table.   Gradings = {StudentName, Subject Code, #Exam, Grade}  Subjects = {Subject Code, SubjectName}  But 2NF does not produce this. 3NF is about dependencies between non-prime attributes so it does not produce this either. But it seems to me that this is the right outcome, because it has no redundancy.  I guess if non-prime attribute was defined as "attribute that is not a candidate key", 2NF would produce the desired result. But I have checked this again and again and non-prime attribute is defined as "attribute that does not BELONG to a candidate key".  What am I doing wrong?
67901
I have the following data:        road        |  ID  | other column  | other column |  ------------------+------+---------------+--------------+           MAIN ST           |  1   |     ...       |     ...      | MAIN ST           |  2   |     ...       |     ...      |         MAIN ST           |  3   |     ...       |     ...      | BERRY LN          |  4   |     ...       |     ...      | BERRY LN          |  5   |     ...       |     ...      | BERRY LN          |  6   |     ...       |     ...      | JOHN RD           |  7   |     ...       |     ...      | JOHN RD           |  8   |     ...       |     ...      |   I want all rows with duplicates in the road column removed. It does not matter to me which duplicates are removed. The end result can look something like this:        road        |  ID  | other column  | other column |  ------------------+------+---------------+--------------+           MAIN ST           |  3   |     ...       |     ...      | BERRY LN          |  4   |     ...       |     ...      | JOHN RD           |  8   |     ...       |     ...      |   where the id's are any value, as long as the road column is unique.   Is there a simple way to do this in SQL or T/SQL? I followed the guide , but it only applies to duplicate keys with non-unique rows.
122848
Alright so i have found  really good answer on how to normalize my data.   I implemented @user25658 's code into my own project successfully, trained a linear model and used it to make a prediction.   Say y = 0.4513869 is the predicted normalized value which i would like to de-normalize.  Based on the normalization code below, how can i achieve this?  normalized = (x-min(x))/(max(x)-min(x))
23850
Most of the places, examples for merging of cells in tables are given. But, I could not find a merging of cells in complicated fashion as shown below. This question may summarize all the possible methods of merging and may serve as the reference for all future questions on merging of cells. Experts can add much more complications if required.
30784
How do I create a unique constraint on an existing table in SQL Server 2005?  I am looking for both the TSQL and how to do it in the Database Diagram.
121259
I have the following table:  \begin{tabular}{|p{3cm}|p{1.5cm}|p{1.5cm}|p{1.5cm}|p{1.5cm}|} \hline \centerline {image}  &amp;    \multicolumn{2}{c|}{ABC} &amp; \multicolumn{2}{c|}{DEF} \\ \hline  &amp; \centering A&amp;\centering B&amp;\centering C&amp;\centering D  \\ \hline \centerline 1&amp;&amp;&amp;&amp; \\ \hline \centerline 1&amp;&amp;&amp;&amp; \\ \hline \centerline 1&amp;&amp;&amp;&amp; \\ \hline \centerline 1&amp;&amp;&amp;&amp; \\ \hline \end{tabular}   When I try centering D, I have a problem in the shape of the table. Why is that? How can I center it appropriately?  Thanks.
21755
I am working on a multi-currency application. For the purpose of simplifying this question, we will not be considering currency conversion (hence I have deliberately left AsAt : datetime out of the equation). In a number of places I need to store monetary values (e.g. 1.23 USD). I understand that I need to store both the amount (1.23), and the currency (CurrencyID with FK to Currency table having ISOAlpha3Code = USD). My question is, how should I best encapsulate and isolate these two data from the other columns in the table (TableX) to which the monetary value relates?  As I see it, I have three options:   I store two columns in TableX, one being the Amount (numeric(p,s)) and one being the CurrencyID (FK). I add an XOR check constraint on the two columns to TableX. (DBMS dependent) I create a CLR UDT (or equivalent) representing Currency, storing both Amount and CurrencyID. I store one Currency type column on TableX containing the monetary value. I create a MonetaryAmount table with three columns: MonetaryAmountID (surrogate PK), CurrencyID (FK), and Amount. I add MonetaryAmountID (FK) to TableX.   I dislike #1 because I have used this pattern before and nobody has understood that the two data are part of the one complex datum; also, it's not normalized. I dislike #2 because it breaks 1NF, it is DBMS specific, and it will likely be difficult/expensive to enforce referential integrity on Currency. I'm rather ambivalent towards #3 - it feels over-engineered and will probably be computationally expensive, but it is correctly normalized.  #3 also has the added complexity of whether to place a unique constraint on the (CurrencyID, Amount) combination, thereby making it an ad-hoc reference table and better normalizing it. I believe it would be best not to do this, as whilst MonetaryAmounts may correlate, they are distinct in nature.  Any experience you can share (or alternatives you can suggest) would be appreciated. I would especially like to hear from genuine database architectural gurus.
28051
I have a table which currently has duplicate values in a column.  I cannot remove these erroneous duplicates but I would like to prevent additional non-unique values from being added.  Can I create a UNIQUE that doesn't check for existing compliance?  I have tried using NOCHECK but was unsuccessful.  In this case I have a table which ties licensing information to "CompanyName"  EDIT: Having multiple rows with the same "CompanyName" is bad data, but we can't remove or update those duplicates at this time. One approach is to have the INSERTs use a stored procedure which will fail for duplicates... If it was possible to have SQL check the uniqueness on its own, that would be preferable.  This data is queried by company name. For the few existing duplicates this will mean that multiple rows are returned and displayed... While this is wrong, it's acceptable in our use case. The goal is to prevent it in the future. It seems to me from the comments that I have to do this logic in the stored procedures.
12741
When adding NOT NULL columns with a DEFAULT value - does PostgreSQL optimize this operation?  In case the table has n rows, a not-optimized alter-table-add-column would yield n writes of the default value - which could be very painful, obviously. With the optimization the DB would instantaneously create the new column, store just one copy of the default value which would be returned when no non-default value is found for that column in a suitable index data structure.  For example  such an .
121764
I am new to database design. I am designing a database for an E-commerce website, there are a lot of products to be updated, but while designing for product specification table I do not understand whether I need specify all the specifications in a table or do I need to use different tables for different products?  For example, consider the products Mobile and book, each of these having unique specifications (such as color,size, cost, model for mobile and title, ISBN, author, cost, year_of_publication, etc. for book), if it is only less number of products then it is possible for me to design, but when there is a thousands of products, it takes a lot of time.  Can anyone tell me how to design my database to manage this situation?
53520
While grading some papers and thinking about a question related to well-orders (in particular, pointing a mistake in a solution), I came to think of a reasonable characterization for well-orders. I can immediately see it's true for countable orders, but not for uncountable orders.  Definition. Let $\cal L$ be a first-order language, and $\cal M$ an $\cal L$-structure. We say that $\cal M$ is rigid if $\text{Aut}(\cal M)=\{\rm id\}$.     Conjecture. Let $\cal L=\{&lt;\}$. Then $\mathcal M=\langle M,&lt;\rangle$ is rigid if and only if it is a well-order or its reverse order is a well-order.   One direction is trivial. Well-orders are rigid (and therefore their reversed orders are rigid as well). In the other direction, if $\cal M$ is countable, then it is easy. Suppose it's not a well-order.   If $\cal M$ contains a convex copy of $\Bbb Z$ then fix everything else and shift that copy by $1$. Otherwise $\cal M$ contains a convex copy of $\Bbb Q$ and then we can shift that copy by $1$ (or multiply it by $\frac12$, whatever floats your boat).   The problem is that for uncountable order types the plot thickens and they might be $\kappa$-dense, so neither of the arguments would work (and there is no additive structure - that I know of - that we can exploit like in the countable case).     Question: Does the conjecture hold, or is there some intricate counterexample?
60098
I want to format an SQL table the right way in a Stack&nbsp;Overflow question.  ID Name  1 Tom  I know the example above is not the right way. I even tried an HTML table approach, but it's still wrong. Please help, because I need to include SQL tables later...