검색어 입력폼

데이터베이스 프로젝트용 보험시스템 프로그램 소스

저작시기 2005.12 |등록일 2006.09.18 파일확장자압축파일 (zip) | 가격 3,000원

소개글

데이터베이스 프로젝트용 보험설계시스템의 MFC 프로그램 소스
관련 PT자료는 따로 올려져 있음
DB생성파일이 빠져있었습니다. 다음은 DB생성 SQL입니다.

CREATE DATABASE DBPROJECT;

/* STAFF와 INSUREDPERSON의 부모에 해당하는 공통인적사항에 관한 테이블*/
CREATE TABLE PERSONALINFO(
SerialNo char(13,0) NOT NULL,
PersonName char(25) NOT NULL,
Address char(300) NULL,
HomePhone char(13) NULL,
MobilePhone char(13) NULL,
OfficePhone char(13) NULL,
Email char(100) NULL,
Fax char(13) NULL,

CONSTRAINT PersonalInfoPK PRIMARY KEY(SerialNo),
);

/*INSUREDPERSON에 할당되는 담당직원에 관한 테이블*/
CREATE TABLE STAFF(
SerialNo char(13,0) NOT NULL,
BranchArea char(30) NULL,
BranchStore char(30) NULL,
StorePhone char(13) NULL,

CONSTRAINT StaffPK PRIMARY KEY(SerialNo),
CONSTRAINT StaffFK FOREIGN KEY(SerialNo)
REFERENCES PERSONALINFO(SerialNo)
ON DELETE CASCADE
ON UPDATE CASCADE
);

/*차량의 제조사에 관한 테이블*/
CREATE TABLE MAKERS(
MakerSerialNo int NOT NULL IDENTITY(1,1),
MakerName char(30) NOT NULL,
CarName char(30) NOT NULL,
ModelName char(30) NOT NULL,
IsAuto bit NOT NULL DEFAULT 0,
Premium int NOT NULL,
MakedYear numeric(4,0) NULL,

CONSTRAINT MakersPK PRIMARY KEY(MakerSerialNo),
CONSTRAINT MakerAK UNIQUE(CarName,ModelName,MakedYear,IsAuto)
);

/*개개의 차량 인스턴스에 관한 테이블*/
CREATE TABLE CARS(
CarSerialNo char(15) NOT NULL,
MakerSerialNo int NOT NULL,

CONSTRAINT CarsPK PRIMARY KEY(CarSerialNo),
CONSTRAINT CarsFK FOREIGN KEY(MakerSerialNo)
REFERENCES MAKERS(MakerSerialNo)
ON DELETE NO ACTION
ON UPDATE CASCADE
);

/*직업의 종류에 관한 테이블*/
CREATE TABLE JOBS(
JobCode int NOT NULL IDENTITY(1,1),
JobName char(30) NOT NULL,
RiskRate int NOT NULL DEFAULT 0,

CONSTRAINT JobsPK PRIMARY KEY(JobCode),
CONSTRAINT JobsAK UNIQUE(JobName)
);

/*피보험자에 관한 테이블*/
CREATE TABLE INSUREDPERSON(
SerialNo char(13,0) NOT NULL,
Career int NULL DEFAULT 0,
TrafficViolationRate int NULL DEFAULT 100,
DiscountExtraRate int NULL DEFAULT 100,
EngagedJob int NULL,
ChargeStaff char(13,0) NOT NULL,
OwnerCar char(15) NOT NULL,

CONSTRAINT InsuredPersonPK PRIMARY KEY(SerialNo),
CONSTRAINT InsuredPersonFK FOREIGN KEY(SerialNo)
REFERENCES PERSONALINFO(SerialNo)
ON DELETE NO ACTION
ON UPDATE CASCADE,
CONSTRAINT InsuredPerson_StaffFK FOREIGN KEY(ChargeStaff)
REFERENCES STAFF(SerialNo)
ON DELETE NO ACTION
ON UPDATE CASCADE,
CONSTRAINT InsuredPersonAK UNIQUE(OwnerCar),
CONSTRAINT InsuredPerson_CarFK FOREIGN KEY(OwnerCar)
REFERENCES CARS(CarSerialNo)
ON DELETE NO ACTION
ON UPDATE CASCADE,
CONSTRAINT InsuredPerson_JobFK FOREIGN KEY(EngagedJob)
REFERENCES JOBS(JobCode)
ON DELETE NO ACTION
ON UPDATE CASCADE
);

/*보험상품에 관한 테이블*/
CREATE TABLE PRODUCT(
ProductID int NOT NULL IDENTITY(1,1),
ProductName char(30) NOT NULL,
PackageProductID int NULL,

CONSTRAINT ProductPK PRIMARY KEY(ProductID),
CONSTRAINT ProductFK FOREIGN KEY(PackageProductID)
REFERENCES PRODUCT(ProductID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT ProductAK UNIQUE(ProductName)
);

/*상품의 구성요소중 담보부품들*/
CREATE TABLE WARRANTY(
WarrantyID int NOT NULL IDENTITY(1,1),
WarrantyName char(60) NOT NULL,
WarrantyRate int NULL,
WarrantyFee int NOT NULL,
WarrantyPrice int NULL,

CONSTRAINT WarrantyPK PRIMARY KEY(WarrantyID),
CONSTRAINT WarrantyAK UNIQUE(WarrantyName)
);

/*상품의 구성요소중 특약부품들*/
CREATE TABLE SPECIALCONTRACT(
SpecialContractID int NOT NULL IDENTITY(1,1),
SpecialContractName char(60) NOT NULL,
SpecialContractRate int NULL,
SpecialContractFee int NOT NULL,
SpecialContractPrice int NULL,
ApplyAgreement text NULL,

CONSTRAINT SpecialContractPK PRIMARY KEY(SpecialContractID),
CONSTRAINT SpecialContractAK UNIQUE(SpecialContractName)
);

CREATE TABLE PRODUCTWARRANTY(
ProductID int NOT NULL,
WarrantyID int NOT NULL,

CONSTRAINT ProductWarrantyPK PRIMARY KEY(ProductID, WarrantyID),
CONSTRAINT ProductWarranty_ProductFK FOREIGN KEY(ProductID)
REFERENCES PRODUCT(ProductID)
ON DELETE NO ACTION
ON UPDATE CASCADE,
CONSTRAINT ProductWarranty_WarrantyFK FOREIGN KEY(WarrantyID)
REFERENCES WARRANTY(WarrantyID)
ON DELETE NO ACTION
ON UPDATE CASCADE
);

CREATE TABLE PRODUCTSC(
ProductID int NOT NULL,
SCID int NOT NULL,

CONSTRAINT ProductSCPK PRIMARY KEY(ProductID, SCID),
CONSTRAINT ProductSC_ProductFK FOREIGN KEY(ProductID)
REFERENCES PRODUCT(ProductID)
ON DELETE NO ACTION
ON UPDATE CASCADE,
CONSTRAINT ProductSC_SCFK FOREIGN KEY(SCID)
REFERENCES SPECIALCONTRACT(SpecialContractID)
ON DELETE NO ACTION
ON UPDATE CASCADE
);

CREATE TABLE CONTRACT(
ContractID int NOT NULL IDENTITY(1,1),
InsureStartDate datetime NOT NULL,
InsureEndDate datetime NOT NULL,
InsureFee int NOT NULL,
PayMethod int NOT NULL DEFAULT 0,
ContractStatus bit NOT NULL DEFAULT 0,
InsuredPersonID char(13,0) NOT NULL,
ProductID int NOT NULL,

CONSTRAINT ContractPK PRIMARY KEY(ContractID),
CONSTRAINT Contract_PersonFK FOREIGN KEY(InsuredPersonID)
REFERENCES INSUREDPERSON(SerialNO)
ON DELETE NO ACTION
ON UPDATE CASCADE,
CONSTRAINT Contract_ProductFK FOREIGN KEY(ProductID)
REFERENCES PRODUCT(ProductID)
ON DELETE NO ACTION
ON UPDATE CASCADE
);

컴파일 실행환경

MFC, C++, Window98이상

압축파일내 파일목록

DBProject_CreateTable.sql
CarInsure.zip
다운로드 맨위로