Inilah Screen Shoot dari CDM data penduduk dan SIM
download file CDMnya disini
Inilah Screen Shoot dari PDM data penduduk dan SIM
download file PDMnya disini
Inilah script untuk dimasukkan ke SQL server
/*==============================================================*/
/* DBMS name: Microsoft SQL Server 2008 */
/* Created on: 23/05/2014 12:56:43 */
/*==============================================================*/
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('KABUPATEN') and o.name = 'FK_KABUPATE_RELATIONS_KECAMATA')
alter table KABUPATEN
drop constraint FK_KABUPATE_RELATIONS_KECAMATA
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('KECAMATAN') and o.name = 'FK_KECAMATA_RELATIONS_KELURAHA')
alter table KECAMATAN
drop constraint FK_KECAMATA_RELATIONS_KELURAHA
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('PENDUDUK') and o.name = 'FK_PENDUDUK_RELATIONS_KELURAHA')
alter table PENDUDUK
drop constraint FK_PENDUDUK_RELATIONS_KELURAHA
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('PENDUDUK') and o.name = 'FK_PENDUDUK_RELATIONS_PEKERJAA')
alter table PENDUDUK
drop constraint FK_PENDUDUK_RELATIONS_PEKERJAA
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('PENDUDUK') and o.name = 'FK_PENDUDUK_RELATIONS_KECAMATA')
alter table PENDUDUK
drop constraint FK_PENDUDUK_RELATIONS_KECAMATA
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('PENDUDUK') and o.name = 'FK_PENDUDUK_RELATIONS_KABUPATE')
alter table PENDUDUK
drop constraint FK_PENDUDUK_RELATIONS_KABUPATE
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('PENDUDUK') and o.name = 'FK_PENDUDUK_RELATIONS_PROPINSI')
alter table PENDUDUK
drop constraint FK_PENDUDUK_RELATIONS_PROPINSI
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('PENDUDUK') and o.name = 'FK_PENDUDUK_RELATIONS_TR_PENDI')
alter table PENDUDUK
drop constraint FK_PENDUDUK_RELATIONS_TR_PENDI
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('PROPINSI') and o.name = 'FK_PROPINSI_RELATIONS_KABUPATE')
alter table PROPINSI
drop constraint FK_PROPINSI_RELATIONS_KABUPATE
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('SIM') and o.name = 'FK_SIM_RELATIONS_PENDUDUK')
alter table SIM
drop constraint FK_SIM_RELATIONS_PENDUDUK
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('STNK') and o.name = 'FK_STNK_RELATIONS_ADMINIST')
alter table STNK
drop constraint FK_STNK_RELATIONS_ADMINIST
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('STNK') and o.name = 'FK_STNK_RELATIONS_PENDUDUK')
alter table STNK
drop constraint FK_STNK_RELATIONS_PENDUDUK
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('STNK') and o.name = 'FK_STNK_RELATIONS_KENDARAA')
alter table STNK
drop constraint FK_STNK_RELATIONS_KENDARAA
go
if exists (select 1
from sysobjects
where id = object_id('ADMINISTRASI')
and type = 'U')
drop table ADMINISTRASI
go
if exists (select 1
from sysindexes
where id = object_id('KABUPATEN')
and name = 'RELATIONSHIP_13_FK'
and indid > 0
and indid < 255)
drop index KABUPATEN.RELATIONSHIP_13_FK
go
if exists (select 1
from sysobjects
where id = object_id('KABUPATEN')
and type = 'U')
drop table KABUPATEN
go
if exists (select 1
from sysindexes
where id = object_id('KECAMATAN')
and name = 'RELATIONSHIP_12_FK'
and indid > 0
and indid < 255)
drop index KECAMATAN.RELATIONSHIP_12_FK
go
if exists (select 1
from sysobjects
where id = object_id('KECAMATAN')
and type = 'U')
drop table KECAMATAN
go
if exists (select 1
from sysobjects
where id = object_id('KELURAHAN')
and type = 'U')
drop table KELURAHAN
go
if exists (select 1
from sysobjects
where id = object_id('KENDARAAN')
and type = 'U')
drop table KENDARAAN
go
if exists (select 1
from sysobjects
where id = object_id('PEKERJAAN')
and type = 'U')
drop table PEKERJAAN
go
if exists (select 1
from sysindexes
where id = object_id('PENDUDUK')
and name = 'RELATIONSHIP_9_FK'
and indid > 0
and indid < 255)
drop index PENDUDUK.RELATIONSHIP_9_FK
go
if exists (select 1
from sysindexes
where id = object_id('PENDUDUK')
and name = 'RELATIONSHIP_10_FK'
and indid > 0
and indid < 255)
drop index PENDUDUK.RELATIONSHIP_10_FK
go
if exists (select 1
from sysindexes
where id = object_id('PENDUDUK')
and name = 'RELATIONSHIP_4_FK'
and indid > 0
and indid < 255)
drop index PENDUDUK.RELATIONSHIP_4_FK
go
if exists (select 1
from sysindexes
where id = object_id('PENDUDUK')
and name = 'RELATIONSHIP_3_FK'
and indid > 0
and indid < 255)
drop index PENDUDUK.RELATIONSHIP_3_FK
go
if exists (select 1
from sysindexes
where id = object_id('PENDUDUK')
and name = 'RELATIONSHIP_2_FK'
and indid > 0
and indid < 255)
drop index PENDUDUK.RELATIONSHIP_2_FK
go
if exists (select 1
from sysindexes
where id = object_id('PENDUDUK')
and name = 'RELATIONSHIP_1_FK'
and indid > 0
and indid < 255)
drop index PENDUDUK.RELATIONSHIP_1_FK
go
if exists (select 1
from sysobjects
where id = object_id('PENDUDUK')
and type = 'U')
drop table PENDUDUK
go
if exists (select 1
from sysindexes
where id = object_id('PROPINSI')
and name = 'RELATIONSHIP_14_FK'
and indid > 0
and indid < 255)
drop index PROPINSI.RELATIONSHIP_14_FK
go
if exists (select 1
from sysobjects
where id = object_id('PROPINSI')
and type = 'U')
drop table PROPINSI
go
if exists (select 1
from sysindexes
where id = object_id('SIM')
and name = 'RELATIONSHIP_7_FK'
and indid > 0
and indid < 255)
drop index SIM.RELATIONSHIP_7_FK
go
if exists (select 1
from sysobjects
where id = object_id('SIM')
and type = 'U')
drop table SIM
go
if exists (select 1
from sysindexes
where id = object_id('STNK')
and name = 'RELATIONSHIP_11_FK'
and indid > 0
and indid < 255)
drop index STNK.RELATIONSHIP_11_FK
go
if exists (select 1
from sysindexes
where id = object_id('STNK')
and name = 'RELATIONSHIP_8_FK'
and indid > 0
and indid < 255)
drop index STNK.RELATIONSHIP_8_FK
go
if exists (select 1
from sysindexes
where id = object_id('STNK')
and name = 'RELATIONSHIP_6_FK'
and indid > 0
and indid < 255)
drop index STNK.RELATIONSHIP_6_FK
go
if exists (select 1
from sysobjects
where id = object_id('STNK')
and type = 'U')
drop table STNK
go
if exists (select 1
from sysobjects
where id = object_id('TR_PENDIDIKAN')
and type = 'U')
drop table TR_PENDIDIKAN
go
/*==============================================================*/
/* Table: ADMINISTRASI */
/*==============================================================*/
create table ADMINISTRASI (
NO_URUT int not null,
NO_SKUM int null,
NO_KOHIR int null,
BBN_KB int null,
PKB int null,
SW_DKLLJ int null,
BIAYA_ADM_STNK int null,
BIAYA_ADM_TNKB int null,
JUMLAH int null,
DITETAPKAN_TGL datetime null,
PETUGAS_PENETAPAN char(256) null,
KOREKTOR char(256) null,
constraint PK_ADMINISTRASI primary key nonclustered (NO_URUT)
)
go
/*==============================================================*/
/* Table: KABUPATEN */
/*==============================================================*/
create table KABUPATEN (
KD_KABUPATEN char(256) not null,
KD_KECAMATAN char(256) null,
NM_KABUPATEN char(256) null,
constraint PK_KABUPATEN primary key nonclustered (KD_KABUPATEN)
)
go
/*==============================================================*/
/* Index: RELATIONSHIP_13_FK */
/*==============================================================*/
create index RELATIONSHIP_13_FK on KABUPATEN (
KD_KECAMATAN ASC
)
go
/*==============================================================*/
/* Table: KECAMATAN */
/*==============================================================*/
create table KECAMATAN (
KD_KECAMATAN char(256) not null,
KD_KELURAHAN char(256) null,
NM_KECAMATAN char(256) null,
constraint PK_KECAMATAN primary key nonclustered (KD_KECAMATAN)
)
go
/*==============================================================*/
/* Index: RELATIONSHIP_12_FK */
/*==============================================================*/
create index RELATIONSHIP_12_FK on KECAMATAN (
KD_KELURAHAN ASC
)
go
/*==============================================================*/
/* Table: KELURAHAN */
/*==============================================================*/
create table KELURAHAN (
KD_KELURAHAN char(256) not null,
NM_KELURAHAN char(256) null,
constraint PK_KELURAHAN primary key nonclustered (KD_KELURAHAN)
)
go
/*==============================================================*/
/* Table: KENDARAAN */
/*==============================================================*/
create table KENDARAAN (
NO_BPKB int not null,
MERK char(256) null,
TYPE char(256) null,
JENIS char(256) null,
MODEL char(256) null,
TH_BUAT int null,
ISI_SILINDER varchar(256) null,
NO_RANGKA int null,
NO_MESIN int null,
WARNA char(256) null,
BHN_BAKAR char(256) null,
WARNA_TRKB char(256) null,
TH_REGISTRASI int null,
constraint PK_KENDARAAN primary key nonclustered (NO_BPKB)
)
go
/*==============================================================*/
/* Table: PEKERJAAN */
/*==============================================================*/
create table PEKERJAAN (
KD_PEKERJAAN char(256) not null,
NM_PEKERJAAN char(256) null,
constraint PK_PEKERJAAN primary key nonclustered (KD_PEKERJAAN)
)
go
/*==============================================================*/
/* Table: PENDUDUK */
/*==============================================================*/
create table PENDUDUK (
NO_PEN int not null,
KD_PENDIDIKAN char(256) null,
KD_KABUPATEN char(256) null,
KD_PEKERJAAN char(256) null,
KD_KELURAHAN char(256) null,
KD_PROPINSI char(256) null,
KD_KECAMATAN char(256) null,
NO_KTP int null,
WARNA char(256) null,
ALAMAT char(256) null,
RT int null,
RW int null,
JENIS_KELAMIN char(256) null,
STATUS char(256) null,
PHOTO image null,
SIDIK_JARI image null,
TGL_LAHIR datetime null,
IRIS_MATA image null,
DNA char(256) null,
GOL_DARAH char(256) null,
constraint PK_PENDUDUK primary key nonclustered (NO_PEN)
)
go
/*==============================================================*/
/* Index: RELATIONSHIP_1_FK */
/*==============================================================*/
create index RELATIONSHIP_1_FK on PENDUDUK (
KD_KELURAHAN ASC
)
go
/*==============================================================*/
/* Index: RELATIONSHIP_2_FK */
/*==============================================================*/
create index RELATIONSHIP_2_FK on PENDUDUK (
KD_KECAMATAN ASC
)
go
/*==============================================================*/
/* Index: RELATIONSHIP_3_FK */
/*==============================================================*/
create index RELATIONSHIP_3_FK on PENDUDUK (
KD_KABUPATEN ASC
)
go
/*==============================================================*/
/* Index: RELATIONSHIP_4_FK */
/*==============================================================*/
create index RELATIONSHIP_4_FK on PENDUDUK (
KD_PROPINSI ASC
)
go
/*==============================================================*/
/* Index: RELATIONSHIP_10_FK */
/*==============================================================*/
create index RELATIONSHIP_10_FK on PENDUDUK (
KD_PEKERJAAN ASC
)
go
/*==============================================================*/
/* Index: RELATIONSHIP_9_FK */
/*==============================================================*/
create index RELATIONSHIP_9_FK on PENDUDUK (
KD_PENDIDIKAN ASC
)
go
/*==============================================================*/
/* Table: PROPINSI */
/*==============================================================*/
create table PROPINSI (
KD_PROPINSI char(256) not null,
KD_KABUPATEN char(256) null,
NM_PROPINSI char(256) null,
constraint PK_PROPINSI primary key nonclustered (KD_PROPINSI)
)
go
/*==============================================================*/
/* Index: RELATIONSHIP_14_FK */
/*==============================================================*/
create index RELATIONSHIP_14_FK on PROPINSI (
KD_KABUPATEN ASC
)
go
/*==============================================================*/
/* Table: SIM */
/*==============================================================*/
create table SIM (
NO_SIM int not null,
NO_PEN int null,
JENIS_SIM char(256) null,
constraint PK_SIM primary key nonclustered (NO_SIM)
)
go
/*==============================================================*/
/* Index: RELATIONSHIP_7_FK */
/*==============================================================*/
create index RELATIONSHIP_7_FK on SIM (
NO_PEN ASC
)
go
/*==============================================================*/
/* Table: STNK */
/*==============================================================*/
create table STNK (
NO_STNK int not null,
NO_URUT int null,
NO_PEN int null,
NO_BPKB int null,
NO_REGISTRASI int null,
constraint PK_STNK primary key nonclustered (NO_STNK)
)
go
/*==============================================================*/
/* Index: RELATIONSHIP_6_FK */
/*==============================================================*/
create index RELATIONSHIP_6_FK on STNK (
NO_PEN ASC
)
go
/*==============================================================*/
/* Index: RELATIONSHIP_8_FK */
/*==============================================================*/
create index RELATIONSHIP_8_FK on STNK (
NO_BPKB ASC
)
go
/*==============================================================*/
/* Index: RELATIONSHIP_11_FK */
/*==============================================================*/
create index RELATIONSHIP_11_FK on STNK (
NO_URUT ASC
)
go
/*==============================================================*/
/* Table: TR_PENDIDIKAN */
/*==============================================================*/
create table TR_PENDIDIKAN (
KD_PENDIDIKAN char(256) not null,
NM_PENDIDIKAN char(256) null,
constraint PK_TR_PENDIDIKAN primary key nonclustered (KD_PENDIDIKAN)
)
go
alter table KABUPATEN
add constraint FK_KABUPATE_RELATIONS_KECAMATA foreign key (KD_KECAMATAN)
references KECAMATAN (KD_KECAMATAN)
go
alter table KECAMATAN
add constraint FK_KECAMATA_RELATIONS_KELURAHA foreign key (KD_KELURAHAN)
references KELURAHAN (KD_KELURAHAN)
go
alter table PENDUDUK
add constraint FK_PENDUDUK_RELATIONS_KELURAHA foreign key (KD_KELURAHAN)
references KELURAHAN (KD_KELURAHAN)
go
alter table PENDUDUK
add constraint FK_PENDUDUK_RELATIONS_PEKERJAA foreign key (KD_PEKERJAAN)
references PEKERJAAN (KD_PEKERJAAN)
go
alter table PENDUDUK
add constraint FK_PENDUDUK_RELATIONS_KECAMATA foreign key (KD_KECAMATAN)
references KECAMATAN (KD_KECAMATAN)
go
alter table PENDUDUK
add constraint FK_PENDUDUK_RELATIONS_KABUPATE foreign key (KD_KABUPATEN)
references KABUPATEN (KD_KABUPATEN)
go
alter table PENDUDUK
add constraint FK_PENDUDUK_RELATIONS_PROPINSI foreign key (KD_PROPINSI)
references PROPINSI (KD_PROPINSI)
go
alter table PENDUDUK
add constraint FK_PENDUDUK_RELATIONS_TR_PENDI foreign key (KD_PENDIDIKAN)
references TR_PENDIDIKAN (KD_PENDIDIKAN)
go
alter table PROPINSI
add constraint FK_PROPINSI_RELATIONS_KABUPATE foreign key (KD_KABUPATEN)
references KABUPATEN (KD_KABUPATEN)
go
alter table SIM
add constraint FK_SIM_RELATIONS_PENDUDUK foreign key (NO_PEN)
references PENDUDUK (NO_PEN)
go
alter table STNK
add constraint FK_STNK_RELATIONS_ADMINIST foreign key (NO_URUT)
references ADMINISTRASI (NO_URUT)
go
alter table STNK
add constraint FK_STNK_RELATIONS_PENDUDUK foreign key (NO_PEN)
references PENDUDUK (NO_PEN)
go
alter table STNK
add constraint FK_STNK_RELATIONS_KENDARAA foreign key (NO_BPKB)
references KENDARAAN (NO_BPKB)
go
Download file My SQL server yang sudah jadi dari hasil PDM disini
Tidak ada komentar:
Posting Komentar