Home » Developer & Programmer » Designer » Database normalization!
 Database normalization! [message #111114] |
Mon, 14 March 2005 00:27 |
S. M. Waqas Shabir
Messages: 30
Registered: January 2004
|
Member |
|
|
Hi
Please have a look at the attached picture.
--------------------------------Scenario-------------------------
* A province has more than one districts.
* A Project can be initiated in many provinces & Districts.
* "Wing" can be understood as department. i.e Department (wing) can look after more than one project
* PStatus means status of the project either to be satisfactory or problematic
-----------------------------------------------------------------
---------------------------------Script--------------------------
CREATE TABLE Wing
(
WingIDP varchar2(35),
Description varchar2(100),
CONSTRAINT Wing_PK PRIMARY KEY (WingIDP)
);
insert into wing values('1','UN');
insert into wing values('2','hin');
insert into wing values('3','WBA');
insert into wing values('4','IDBF');
insert into wing values('5','JPN');
insert into wing values('6','PCB');
insert into wing values('7','ECL');
insert into wing values('8','ADBP');
CREATE TABLE Province
(
ProvinceIDP varchar2(35),
Description varchar2(100),
CONSTRAINT Province_PK PRIMARY KEY (ProvinceIDP)
);
insert into province values('1','P');
insert into province values('2','S');
insert into province values('3','B');
insert into province values('4','NWFP');
insert into province values('5','FA');
CREATE TABLE District
(
ProvinceIDFP varchar2(35),
DistrictIDP varchar2(35),
Description varchar2(100),
CONSTRAINT District_PK PRIMARY KEY (ProvinceIDFP,DistrictIDP),
CONSTRAINT District_FK FOREIGN KEY (ProvinceIDFP) REFERENCES
Province(ProvinceIDP)
);
insert into District values('1','1','RWP');
insert into District values('1','2','LHR');
insert into District values('2','1','KHI');
insert into District values('2','2','HBD');
insert into District values('3','1','QTA');
insert into District values('4','1','Pesh');
insert into District values('4','2','Atd');
insert into District values('5','1','Ibd');
CREATE TABLE PStatus
(
PStatusIDP varchar2(35),
Description varchar2(100),
CONSTRAINT PStatus_PK PRIMARY KEY (PStatusIDP)
);
insert into PStatus values('1','Satisfactory');
insert into PStatus values('2','Problematic');
CREATE TABLE Project
(
ProjectIDP varchar2(35),
ProjectTitle varchar2(200),
WingIDF varchar2(35),
ProjectBrief varchar2(2000),
PStatusIDF varchar2(35),
IsComplete varchar2(1),
CONSTRAINT Project_PK PRIMARY KEY (ProjectIDP),
CONSTRAINT Project_FK1 FOREIGN KEY (WingIDF) REFERENCES Wing(WingIDP),
CONSTRAINT Project_FK2 FOREIGN KEY (PStatusIDF) REFERENCES
PStatus(PStatusIDP)
);
insert into Project(ProjectIDP,ProjectTitle,WingIDF,PStatusIDF)
values('1','P1','1','1');
insert into Project(ProjectIDP,ProjectTitle,WingIDF,PStatusIDF)
values('2','P2','2','1');
insert into Project(ProjectIDP,ProjectTitle,WingIDF,PStatusIDF)
values('3','P3','1','1');
insert into Project(ProjectIDP,ProjectTitle,WingIDF,PStatusIDF)
values('4','P4','3','2');
insert into Project(ProjectIDP,ProjectTitle,WingIDF,PStatusIDF)
values('5','P5','4','2');
CREATE TABLE ProjectLocation
(
ProjectIDFP varchar2(35),
ProvinceIDFP varchar2(35),
DistrictIDFP varchar2(35),
CONSTRAINT ProjectLocation_PK PRIMARY KEY
(ProjectIDFP,ProvinceIDFP,DistrictIDFP),
CONSTRAINT ProjectLocation_FK1 FOREIGN KEY (ProjectIDFP) REFERENCES
Project(ProjectIDP),
CONSTRAINT ProjectLocation_FK2 FOREIGN KEY (ProvinceIDFP) REFERENCES
Province(ProvinceIDP)
);
insert into ProjectLocation values('1','1','1');
insert into ProjectLocation values('1','2','2');
insert into ProjectLocation values('2','5','1');
insert into ProjectLocation values('3','1','2');
insert into ProjectLocation values('4','3','1');
insert into ProjectLocation values('5','1','1');
insert into ProjectLocation values('5','1','2');
insert into ProjectLocation values('5','2','1');
insert into ProjectLocation values('5','2','2');
insert into ProjectLocation values('5','3','1');
insert into ProjectLocation values('5','4','1');
insert into ProjectLocation values('5','4','2');
insert into ProjectLocation values('5','5','1');
-----------------------------------------------------------------
After viewing the picture and the script do you see any normalization faults. If yes then please help me in not only normalizing it but also making the required quries. e.g,
A select query that shows Project Fields,the corresponding wing (dept.) of the project, status of the project, location of the project (all the provinces & districts in which it is working on)
Many Thanks
|
|
|
|
Goto Forum:
Current Time: Tue Apr 16 06:11:55 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
