oracle komutları
create table tbl_departman(
departman_id integer GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
departman varchar2(20) not null,
constraint dep_pk primary key (departman_id)
)
create table tbl_calisan (
calisan_id integer GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
ad varchar2 (25) not null,
soyad varchar2 (30) not null,
tc char(11) unique not null,
cinsiyet char(1) check (cinsiyet ='k' or cinsiyet ='e'), -- k ve e
sicil_no char(12) unique not null,
iban char(16) unique,
d_tarih date ,
constraint clsn_pk primary key (calisan_id)
)
create table tbl_calisan_departman (
cal_dep_id integer GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
calisan_id integer,
departman_id integer,
constraint cd_pk primary key (cal_dep_id),
constraint c_fk foreign key (calisan_id) references tbl_calisan(calisan_id),
constraint d_fk foreign key (departman_id) references tbl_departman(departman_id)
)
)
create table tbl_calisan_iletisim (
cal_iletisim_id integer GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
cal_telefon char(11) unique not null ,
cal_mail varchar2 (40) unique ,
cal_adres varchar2 (200),
calisan_id int unique ,
constraint ci_pk primary key (cal_iletisim_id),
constraint ci_fk foreign key (calisan_id) references tbl_calisan(calisan_id)
)
create table tbl_mesai (
mesai_id integer GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
calisma_turu char(2) default 'hi' check (calisma_turu = 'hi'or calisma_turu = 'hs') ,
baslama_tarihi date,
bitis_tarihi date,
baslama_saati date,
bitis_saati date,
constraint mesai_pk primary key (mesai_id)
)
create table tbl_calisan_mesai (
calisan_mesai_id integer GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1) ,
calisan_id integer ,
mesai_id integer,
constraint cm_pk primary key(calisan_mesai_id),
constraint cmc_fk foreign key (calisan_id) references tbl_calisan (calisan_id),
constraint cmm_fk foreign key (mesai_id) references tbl_mesai (mesai_id)
)
create table tbl_maas (
maas_id integer GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
maas float default 1800 check (maas >= 1800),
prim float default 0,
maas_tarihi date ,
calisan_id integer,
constraint maas_pk primary key(maas_id),
constraint maas_fk foreign key (calisan_id) references tbl_calisan (calisan_id)
)
create table tbl_kategori (
kategori_id integer GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
kategoriler varchar2(40) not null,
constraint kategori_pk primary key(kategori_id)
)
create table tbl_urun (
urun_id integer GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
marka varchar2 (30) not null,
m_model varchar2 (20) not null,
ozellik varchar2(300), -- siyah, büyük, küçük, ...
kategori_id integer,
constraint urun_pk primary key(urun_id),
constraint kat_fk foreign key (kategori_id) references tbl_kategori (kategori_id)
)
create table tbl_musteri (
mus_id integer GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
mus_ad varchar2 (30) not null,
mus_soyad varchar2 (30) not null,
mus_adres varchar2 (200),
mus_tel char(11) unique not null,
mus_mail varchar2(30) unique not null,
mus_cinsiyet char(1) check (mus_cinsiyet ='e' or mus_cinsiyet ='k'),
mus_d_tarih date ,
constraint mus_pk primary key(mus_id)
)
create table tbl_odeme (
odeme_id integer GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1) ,
odeme_turu varchar2 (20) check (odeme_turu ='kredi kartı' or odeme_turu ='peşin'
or odeme_turu ='taksit' or odeme_turu ='EFT‘ or odeme_turu ='havale'),
constraint odeme_pk primary key(odeme_id)
)
create table tbl_servis (
servis_id integer GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1) ,
barkod char(8) unique not null,
ariza varchar2(400) not null,
tah_al_tarih date not null, -- alınma
tah_tes_tarih date , -- teslim
is_sigorta char(1) default '0' check(is_sigorta='0' or is_sigorta='1'),
mus_id integer,
odeme_id integer,
calisan_id integer,
urun_id integer,
constraint servis_pk primary key(servis_id),
constraint mus_fk foreign key (mus_id) references tbl_musteri (mus_id),
constraint odm_fk foreign key (odeme_id) references tbl_odeme (odeme_id),
constraint cls_fk foreign key (calisan_id) references tbl_calisan (calisan_id),
constraint urn_fk foreign key (urun_id)references tbl_urun (urun_id)
)
create table sigorta (
sigorta_id integer GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
s_baslama_tar date ,
s_bitis_tar date ,
s_aciklama varchar2(400),
servis_id integer unique,
constraint sigorta_pk primary key(sigorta_id),
constraint sigorta_fk foreign key (servis_id) references tbl_servis (servis_id)
)
create table tbl_teslim (
teslim_id integer GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
teslim_tar date ,
teslim_alan varchar2 (40),
calisan_id integer,
servis_id integer,
constraint teslim_pk primary key(teslim_id),
constraint tescal_fk foreign key(servis_id) references tbl_servis (servis_id),
constraint tessrv_fk foreign key(calisan_id) references tbl_calisan (calisan_id)
)
create table tbl_ucret (
ucret_id integer GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1) ,
ucret_turu varchar2 (20) not null ,
ucret float ,
uc_aciklama varchar2(400),
constraint uc_pk primary key (ucret_id)
)
create table tbl_ucret_servis (
uc_servis_id integer GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
ucret_id integer,
servis_id integer,
constraint ucserv_pk primary key (uc_servis_id),
constraint uc_uc_fk foreign key (ucret_id) references tbl_ucret (ucret_id),
constraint srv_srv_fk foreign key (servis_id) references tbl_servis (servis_id)
)
create table tbl_durum (
durum_id integer GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1) ,
durum varchar2(40) check (durum = 'giriş yapıldı' or durum = 'beklemede' or
durum = 'muayenede' or durum = 'tamir edilemedi' or durum = 'teslim edildi'),
constraint durum_pk primary key (durum_id)
)
create table tbl_servis_durum (
servis_durum_id integer GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
durum_tar date default sysdate,
servis_id integer,
durum_id integer,
constraint srv_durum_pk primary key (servis_durum_id),
constraint srv_durum_fk foreign key (servis_id)references tbl_servis (servis_id),
constraint srv_dur_fk foreign key (durum_id)references tbl_durum (durum_id)
)
create table tbl_anket (
anket_id integer GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1) ,
ank_puan float default 0 check (ank_puan between 1 and 5),
servis_id integer,
constraint anket_pk primary key (anket_id),
constraint anket_fk foreign key (servis_id)references tbl_servis (servis_id)
)
create table tbl_sorular (
soru_id integer GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
sorusu varchar2 (200) not null,
constraint soru_pk primary key (soru_id)
)
create table tbl_ank_sorular (
ank_sorular_id integer GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
puan float default 0 check (puan between 1 and 5),
soru_id integer,
anket_id integer,
constraint ank_soru_pk primary key (ank_sorular_id),
constraint ank_soru_fk foreign key (soru_id) references tbl_sorular (soru_id),
constraint ank_ank_fk foreign key (anket_id) references tbl_anket (anket_id)
)
komutları çalıştıralım:
sql server komutları
-- create database db_teknikservis
create table tbl_departman (
departman_id int primary key identity,
departman nvarchar(20) not null,
)
-- 18 yaşından küçük çalışan olmasın kontrol
create table tbl_calisan (
calisan_id int primary key identity,
ad nvarchar (25) not null,
soyad nvarchar (30) not null,
tc char (11) unique not null,
cinsiyet char (1) check (cinsiyet ='k' or cinsiyet ='e') , -- k ve e
sicil_no char (12) unique not null,
iban char (16) unique,
d_tarih date check (d_tarih < getdate() )
)
create table tbl_calisan_departman (
cal_dep_id int primary key identity,
calisan_id int foreign key references tbl_calisan (calisan_id),
departman_id int foreign key references tbl_departman (departman_id)
)
create table tbl_calisan_iletisim (
cal_iletisim_id int primary key identity,
cal_telefon char(11) unique not null ,
cal_mail nvarchar (40) unique ,
cal_adres nvarchar (200),
calisan_id int unique foreign key references tbl_calisan (calisan_id),
)
-- baslangıç-bitis tar trigger ile ayarlanacak
create table tbl_mesai (
mesai_id int primary key identity,
calisma_turu char(2) default 'hi' check
(calisma_turu = 'hi'or calisma_turu = 'hs') , -- hi, hs, hafta ici, hafta sonu
baslama_tarihi date,
bitis_tarihi date,
baslama_saati time,
bitis_saati time
)
create table tbl_calisan_mesai (
calisan_mesai_id int primary key identity,
calisan_id int foreign key references tbl_calisan (calisan_id),
cal_mesai_id int foreign key references tbl_mesai (mesai_id)
)
create table tbl_maas (
maas_id int primary key identity,
maas smallmoney default 1800 check (maas >= 1800),
prim smallmoney default 0,
maas_tarihi datetime check (maas_tarihi <= getdate ()),
calisan_id int foreign key references tbl_calisan (calisan_id)
)
create table tbl_kategori (
kategori_id int primary key identity,
kategoriler nvarchar(40) not null
)
create table tbl_urun (
urun_id int primary key identity,
marka nvarchar (30) not null,
model nvarchar (20) not null,
ozellik text, -- siyah, büyük, küçük, ...
kategori_id int foreign key references tbl_kategori (kategori_id)
)
create table tbl_musteri (
mus_id int primary key identity,
mus_ad nvarchar (30) not null,
mus_soyad nvarchar (30) not null,
mus_adres nvarchar (200),
mus_tel char(11) unique not null,
mus_mail nvarchar(30) unique not null,
mus_cinsiyet char(1) check (mus_cinsiyet ='e' or mus_cinsiyet ='k'),
mus_d_tarih date check (mus_d_tarih < getdate ())
)
create table tbl_odeme (
odeme_id int primary key identity,
odeme_turu nvarchar (20)
check (odeme_turu ='kredi kartı' or odeme_turu ='peşin'
or odeme_turu ='taksit' or odeme_turu ='EFT'
or odeme_turu ='havale')
)
-- barkod triger ile üretilecek
create table tbl_servis (
servis_id int primary key identity,
barkod char (8) unique not null,
ariza text not null,
tah_al_tarih datetime not null
check ( tah_al_tarih >= getdate ()) , -- alınma
tah_tes_tarih datetime not null
check ( tah_tes_tarih >= getdate ()) , -- teslim
is_sigorta bit default 0 ,
mus_id int foreign key references tbl_musteri (mus_id),
odeme_id int foreign key references tbl_odeme (odeme_id),
calisan_id int foreign key references tbl_calisan (calisan_id),
urun_id int foreign key references tbl_urun (urun_id)
)
create table sigorta (
sigorta_id int primary key identity,
s_baslama_tar date check (s_baslama_tar < getdate ()),
s_bitis_tar date check (s_bitis_tar > getdate ()),
s_aciklama text,
servis_id int unique foreign key references tbl_servis (servis_id)
)
create table tbl_teslim (
teslim_id int primary key identity,
teslim_tar datetime check (teslim_tar <= getdate ()),
teslim_alan nvarchar (40),
calisan_id int foreign key references tbl_calisan (calisan_id),
servis_id int foreign key references tbl_servis (servis_id)
)
create table tbl_ucret (
ucret_id int primary key identity,
ucret_turu nvarchar (20) not null ,
ucret money default 0,
uc_aciklama text
)
create table tbl_ucret_servis (
uc_servis_id int primary key identity,
ucret_id int foreign key references tbl_ucret (ucret_id),
servis_id int foreign key references tbl_servis (servis_id)
)
create table tbl_durum (
durum_id int primary key identity,
durum nvarchar(40) check (
durum = 'giriş yapıldı' or
durum = 'beklemede' or
durum = 'muayenede' or
durum = 'tamir edilemedi' or
durum = 'teslim edildi')
)
create table tbl_servis_durum (
servis_durum_id int primary key identity,
servis_id int foreign key references tbl_servis (servis_id),
durum_id int foreign key references tbl_durum (durum_id),
durum_tar datetime default getdate ()
)
-- puanların ortalaması yazılacak
create table tbl_anket (
anket_id int primary key identity,
ank_puan float default 0 check (ank_puan between 1 and 5),
servis_id int foreign key references tbl_servis (servis_id)
)
create table tbl_sorular (
soru_id int primary key identity,
sorusu nvarchar (200) not null,
)
create table tbl_ank_sorular (
ank_sorular_id int primary key identity,
soru_id int foreign key references tbl_sorular (soru_id),
anket_id int foreign key references tbl_anket (anket_id),
puan float default 0 check (puan between 1 and 5)
)
Hiç yorum yok:
Yorum Gönder