Make a database management system for survey (part-1)

Make an expanding database for survey where infinite answer and huge number of question and people involved than it is quite hard to make the design for survey database.

It is most wise to create fewer table and more views for ur required operation, first thing first , we have to create an entity relationship diagram for design.

see the basic model for ER-diagram of a survey database manage ment system.

In our project database side have five table. To display data we make user interface. By user interface we show all data in different way. An user  can easily know any information of the citizen. User can easily insert , delete,update, and search data from table. User can input question and answer. In citizen table citizen id is unique key.In citizeninfo table citizenid is unique key.citizenid is foreign key citizeninfo table. By this foreign key we show all information from two table.In question table question id is primary key and answer table answer id is primary key.Question id is foreign key on the answer table.To maintain these table have survey table.In survey table question id ,answer id, citizen id and survey number.By this table we know all information about citizen.

so the basic diagram is

now about the operation we have to use some views . so see the sql query

CREATE TABLE CITIZEN
 (AGE          VARCHAR2(15),
CITIZENID       NUMBER(15) NOT NULL,
  SEX        VARCHAR2(15),
 SALARY         VARCHAR2(15),
 PRIMARY KEY (CITIZENID)
) ;

CREATE TABLE CITIZENINFO
  (FNAME           VARCHAR2(15),
 LNAME            VARCHAR2(15),
 REGDATE         VARCHAR2(35),
 BDATE            VARCHAR2(35),
 ADDRESS         VARCHAR2(35),
 OCCUPATION     VARCHAR2(15),
 CITIID      NUMBER(15) NOT NULL,
  PRIMARY KEY (CITIID),
  FOREIGN KEY (CITIID) REFERENCES CITIZEN (CITIZENID) ON DELETE CASCADE
) ;

CREATE TABLE QUESTION
( QUESTIONID          NUMBER(15) NOT NULL,
  DESCRIP           VARCHAR2(45) NOT NULL,
 PRIMARY KEY (QUESTIONID)
 ) ;

CREATE TABLE ANSWER
( QUESTIONID          NUMBER(15) NOT NULL,
  ANSWERID          NUMBER(15) NOT NULL,
  ANS        VARCHAR2(45),
  PRIMARY KEY (ANSWERID),
   FOREIGN KEY (QUESTIONID)  REFERENCES QUESTION (QUESTIONID) ON DELETE CASCADE) ;

CREATE TABLE SURVEY
( QUESTIONID          NUMBER(15) NOT NULL,
  ANSWERID          NUMBER(15) NOT NULL,
CITIZENID        NUMBER NOT NULL,
SURVEYNO         NUMBER NOT NULL,
 PRIMARY KEY (SURVEYNO),
 FOREIGN KEY (QUESTIONID) REFERENCES QUESTION (QUESTIONID) ON DELETE CASCADE,
FOREIGN KEY (CITIZENID ) REFERENCES CITIZEN (CITIZENID) ON DELETE CASCADE,
FOREIGN KEY (ANSWERID ) REFERENCES ANSWER (ANSWERID) ON DELETE CASCADE
) ;

//create some view
CREATE VIEW ALLC AS
SELECT C.CITIZENID ,I.FNAME,I.LNAME,C.AGE,C.SEX,
I.ADDRESS,I.OCCUPATION,
C.SALARY,I.REGDATE,I.BDATE FROM CITIZEN C,CITIZENINFO I
WHERE C.CITIZENID=I.CITIID;
commit;

CREATE VIEW ALLA AS
SELECT C.CITIZENID ,I.FNAME,I.LNAME,C.AGE,C.SEX,
I.ADDRESS,I.OCCUPATION FROM CITIZEN C,CITIZENINFO I
WHERE C.CITIZENID=I.CITIID;
commit;

CREATE VIEW CIDN AS
SELECT C.CITIZENID ,I.FNAME,I.LNAME
FROM CITIZEN C,CITIZENINFO I
WHERE C.CITIZENID=I.CITIID;
commit;

CREATE VIEW IMP AS
SELECT C.CITIZENID ,C.AGE,C.SEX ,I.OCCUPATION,
C.SALARY FROM CITIZEN C,CITIZENINFO I
WHERE C.CITIZENID=I.CITIID;
commit;

CREATE VIEW MALE AS
SELECT C.CITIZENID ,I.FNAME,I.LNAME,C.AGE,
I.ADDRESS,I.OCCUPATION,
C.SALARY FROM CITIZEN C,CITIZENINFO I
WHERE C.CITIZENID=I.CITIID AND C.SEX='MALE';
commit;

CREATE VIEW FEMALE AS
SELECT C.CITIZENID ,I.FNAME,I.LNAME,C.AGE,
I.ADDRESS,I.OCCUPATION,
C.SALARY FROM CITIZEN C,CITIZENINFO I
WHERE C.CITIZENID=I.CITIID AND C.SEX='FEMALE';
commit;

CREATE VIEW X AS
SELECT CITIZENID FROM CITIZEN WHERE CITIZENID NOT IN
(SELECT CITIZENID FROM SURVEY);
COMMIT;

create view result as
SELECT CITIZENID ,AGE,SEX,
SALARY FROM CITIZEN
WHERE CITIZENID IN
(SELECT CITIZENID FROM SURVEY);
commit;

in next part we will show how these are implement in c#.
u can get full sourcecode here with oracle 10g and visual studio 08 c# 3.5

Advertisements

About kishor datta gupta

Graduate Research Assistant at University of Memphis Software Engineer at Silicon Orchard LTD. Former Research Assistant at Lamar University Former Software Engineer at Samsung R&D Institute Bangladesh Studies Ph.D. Computer Science at University of Memphis Studied Masters of Science in Computer Sciences at Lamar University Studied BSC in CSE at Khulna University of Engineering and Technology Studied HSC (completed) at Chittagang college 04-06 Studied High school at ST. Placid's High School'04 Studied Junior Secondary School at Saint Mary's School Lives in Memphis, Tennessee
This entry was posted in DATABASE, Winform app and tagged , , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s