SQLize Online / PHPize Online  /  SQLtest Online

create table Rabotniki ( Seria_i_nomer_pasporta varchar2(11) primary key not null, FIO varchar2(100) not null, Data_rojdenia date not null, Mesto_propiski varchar2(100) not null, Telefon char not null, INN varchar2(50) not null ); create table Zapchasti( Naimenovanie varchar2(50) not null, Seriyniy_nomer char primary key not null, Stoimost number not null, Proizvoditel varchar2(50) not null, Kolichestvo number not null ); create table Oborudovanie( Seriyniy_nomer char primary key not null, Brand varchar2(50) not null, Strana_proizvoditel varchar2(50) not null, Data_registracii date not null, Naimenovanie_oborudovania varchar2(50) not null ); create table Uslugi( Naimenovanie_uslugi varchar2(50) not null, Nomer_uslugi number primary key not null, Stoimost number not null, Oborudovanie char not null, Vremnnie_ramki varchar2(50), Zapchasti char not null, CONSTRAINT Oborudovanie_fk foreign key (Oborudovanie) references Oborudovanie(Seriyniy_nomer), CONSTRAINT Zapchasti_fk foreign key (Zapchasti) references Zapchasti(Seriyniy_nomer) ); create table Klient( Telefon char primary key not null, FIO varchar2(100) not null, Mesto_propiski varchar2(100), Nomer_scheta varchar2(50) not null, Nomer_voditelskogo_udostoverenia char not null, email varchar2(50) not null ); create table Avtomobil( Nomer_avtomobilia varchar2(50) primary key not null, Probeg_km number not null, Marka_avtomobilia varchar2(50) not null, Proizvoditel varchar2(50) not null, Data_vipuska date not null, Vladelec_FIO char not null, CONSTRAINT Vladelec_FIO_fk foreign key (Vladelec_FIO) references Klient(Telefon) ); create table Zakaz_klienta( Nomer_avtomobilia varchar2(50) not null, Nomer_uslugi number not null, Rabornik varchar2(11) not null, CONSTRAINT Zakaz_klienta_pk primary key (Nomer_uslugi, Rabornik, Nomer_avtomobilia), CONSTRAINT Rabornik_fk foreign key (Rabornik) references Rabotniki(Seria_i_nomer_pasporta), CONSTRAINT Nomer_uslugi_fk foreign key (Nomer_uslugi) references Uslugi(Nomer_uslugi), CONSTRAINT Nomer_avtomobilia_fk foreign key (Nomer_avtomobilia) references Avtomobil(Nomer_avtomobilia) ); create table Tehnicheskoe_obslujivanie( Nomer_obslujivania number primary key, Nomer_avtomobilia varchar2(50) not null, Nomer_uslugi number not null, Data_obslujivania date not null, CONSTRAINT Nomer_avtomobilia_TO_fk foreign key (Nomer_avtomobilia) references Avtomobil(Nomer_avtomobilia), CONSTRAINT Nomer_uslugi_TO_fk foreign key (Nomer_uslugi) references Uslugi(Nomer_uslugi) ); alter table Avtomobil drop CONSTRAINT Vladelec_FIO_fk; alter table Avtomobil modify Vladelec_FIO varchar2(100); alter table Klient add CONSTRAINT Klient_pk primary key (Telefon, FIO); alter table Avtomobil add CONSTRAINT Vladelec_FIO_fk foreign key (Vladelec_FIO) references Klient(FIO); /*alter table Zakaz_klienta add (Zakaz_date date not null);*/ --select * from Zakaz_klienta;