March 3, 2013 at 10:18 pm
I have the data as below. I need to normalise the table with this data.. can someone help me how perfect i can normalise this?
Table A: (Master)
ComputerName
OS
OSVersion
Manufacturer
Table B:
SoftwareName
SoftwareVersion
SoftwareLiscencekey
Installedversion
I have attached data in Attachment.
Requirement:
1. Both tables are having duplicate values.
2. One Software will be used in multiple computers and one computer will use multiple softwares
Please normalise this table and provide me a solution to proceed further.
March 3, 2013 at 11:10 pm
Table A: (Master)
PCID (PK)
ComputerName
OS
OSVersion
Manufacturer
Table B:
PCID (FK)
SoftwareName
SoftwareVersion
SoftwareLiscencekey
Installedversion
?
Or you could go really crazy and create an OS, Manufacturer, Software table - but that'd probably be overkill 🙂
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 4, 2013 at 12:21 am
MyDoggieJessie (3/3/2013)
Table A: (Master)PCID (PK)
ComputerName
OS
OSVersion
Manufacturer
Table B:
PCID (FK)
SoftwareName
SoftwareVersion
SoftwareLiscencekey
Installedversion
?
Or you could go really crazy and create an OS, Manufacturer, Software table - but that'd probably be overkill 🙂
No, no . It will not be an overkill seperate OS, manufacture and software. If it is a serious databse design, I would go with seperate tables for all these entities. Otherwise we will have lot of redundant, ambigous (Windows Server 2008,windowsServer2008 etc) data.
March 4, 2013 at 1:00 am
Hi,
I agree with your point. But if i have more columns you mean to say i need seperate seperate tables?
Can you please mention the subtable columns which can be sperated from master table?
If posible share me as a <b>ER diagram</b>. I am totally confused and stuck up with this logic. there are around 1024 records are there.
Regards,
Mani
March 4, 2013 at 2:18 am
ManiDBLover (3/4/2013)
Hi,I agree with your point. But if i have more columns you mean to say i need seperate seperate tables?
Not really. The I dea is to think of all possible scenarios while doing the design.
The colums OS and manufacturer can be seperated since that can be a seperate entity on its own.
If you have columns like Memory CPU purchase date etc, You can have it in the same table since these details belong to a single instance of the PC.
March 4, 2013 at 3:39 am
ManiDBLover (3/3/2013)
...Requirement:
1. Both tables are having duplicate values.
...
Right now by accident or in the future by design?
...
2. One Software will be used in multiple computers and one computer will use multiple softwares...
Then use a many-to-many join table, like this:
DROP TABLE #Computers
DROP TABLE #Software
DROP TABLE #ComputerSoftware
CREATE TABLE #Computers (IDComputer INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
ComputerName VARCHAR(20) NOT NULL, Manufacturer VARCHAR(20) NOT NULL)
INSERT INTO #Computers (ComputerName, Manufacturer) VALUES
('AA', 'HP'),
('BB', 'HP'),
('CC', 'HP'),
('DD', 'HP'),
('EE', 'HP'),
('FF', 'HP'),
('GG', 'HP')
CREATE TABLE #Software (IDSoftware INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
SoftwareName VARCHAR(20) NOT NULL, SoftwareVersion VARCHAR(20) NOT NULL, isOS BIT NOT NULL, SoftwareLicencekey VARCHAR(20), Installedversion VARCHAR(20))
INSERT INTO #Software (SoftwareName, SoftwareVersion, isOS, SoftwareLicencekey, Installedversion) VALUES
('Windows', '2008', 1, NULL, NULL),
('Windows', 'Vista', 1, NULL, NULL),
('Windows', '7', 1, NULL, NULL),
('Linux', '3', 1, NULL, NULL),
('Unix', '2', 1, NULL, NULL),
('Linux', '2', 1, NULL, NULL),
('MS Office', '2007', 0, 'sfrsfsaf', '3'),
('MS Office', '2007', 0, 'fdgdag', '3'),
('MS Office', '2003', 0, 'ee3444', '3'),
('Abobe reader', '2008', 0, 'gghr667', '1'),
('Adobe reader', '2008', 0, 'terhry567788', '2'),
('MS SQL Server', '2005', 0, '6575578ufu', '2005'),
('VB', '6', 0, 'w3453566', '6')
CREATE TABLE #ComputerSoftware (IDComputerSoftware INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
IDComputer INT NOT NULL FOREIGN KEY REFERENCES #Computers(IDComputer),
IDSoftware INT NOT NULL FOREIGN KEY REFERENCES #Software(IDSoftware),
InstallDate DATETIME DEFAULT GETDATE(),
InstalledBy VARCHAR(20) NULL)
INSERT INTO #ComputerSoftware (IDComputer, IDSoftware, InstalledBy) VALUES
(1, 3, 'Data Migration'),
(2, 1, 'Data Migration'),
(3, 2, 'Data Migration'),
(4, 3, 'Data Migration'),
(5, 4, 'Data Migration'),
(6, 5, 'Data Migration'),
(7, 6, 'Data Migration')
-------------------------------------------------------------------------------------
SELECT c.*, s.*, cs.InstallDate, cs.InstalledBy
FROM #Computers c
INNER JOIN #ComputerSoftware cs ON cs.IDComputer = c.IDComputer
INNER JOIN #Software s ON s.IDSoftware = cs.IDSoftware AND s.isOS = 1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 5, 2013 at 4:25 pm
Below is a start. Please don't skimp on the design phase, it will bite you later. Foreign key relationships are indicated by matching column names.
Be prepared, though: when you start normalizing, tables mutliply even faster than rabbits!
Computers
computer_id int
name varchar()
manufacturer_id smallint
OS_id smallint
deployed datetime
...
Manufacturers
manufacturer_id smallint
name varchar()
...
OSes
OS_id smallint
name varchar() --Sample: 'Windows 7'
base_version varchar() --Sample: '7'
full_version varchar() --Sample: '7.n.<whatever>'
manufacturer_id smallint
...
Software
software_id smallint
name varchar() --Sample: 'SQL Server 2008'
base_version varchar() --Sample: '10'
full_version varchar() --Sample: '10.0.4000'
license_key varchar() --if "company" license
...
Computer_Software
computer_id int
software_id smallint
date_installed datetime
license_key varchar() --if for machine/site, not at company level
...
Edit: Added code tags so my indentation wouldn't be lost.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply