SQLize Online / PHPize Online  /  SQLtest Online

create table Residents( id_the_tenant number primary key not null, last_name varchar2(255) not null, --атомарность first_name varchar2(255) not null, passport_data char(10) not null, date_of_birth date, preferential_discount number not null --возможны мат. операции ); create table Payment_for_services( id_services number primary key not null, receipt_number number not null, --нет инта, есть намбер date_of_application date, client number not null, --пк типа намбер personal_account char(20), name_of_service varchar2(255) not null, constraint client_fk foreign key (client) references Residents(id_the_tenant) ); create table Housing_and_utilities_departments( id_department number primary key not null, --id лучше намбер department_speciality varchar2(255) not null, department_security_level char(1) not null, --типа 1-5 department_address varchar2(255) not null, contact_details varchar2(255) ); create table Staff( id_employee number primary key not null, --id лучше намбер last_name varchar2(255) not null, --атомарность first_name varchar2(255) not null, date_of_birth date, phone_number varchar2(16) not null, --для красивого номера email varchar2(255) not null, department number not null, --пк типа намбер constraint department_fk foreign key (department) references Housing_and_utilities_departments(id_department) ); create table Services( id_services number primary key not null, --id лучше намбер types_of_services number not null, --пк типа намбер cost_of_services number not null, --возможны мат. операции data_of_the_service_provider number not null, --это не дата + тип пк service_execution_time date not null, discount number, --возможны мат. операции department number not null, --пк типа намбер constraint types_of_services_fk foreign key (types_of_services) references Payment_for_services(id_services), constraint data_of_the_service_provider_fk foreign key (data_of_the_service_provider) references Staff(id_employee), constraint department2_fk --имя фк не повторяется foreign key (department) references Housing_and_utilities_departments(id_department) ); create table Complaints( --жалобы id_complaint number primary key not null, --id жалобы resident number not null, --жилец, который подал complaint_text varchar2(255) not null, --текст жалобы date_of_complaint date not null, --дата status varchar2(20) not null, --статус department number not null, --департамент employee number, --кто назначен constraint department3_fk --имя фк не повторяется foreign key (department) references Housing_and_utilities_departments(id_department), constraint resident_fk foreign key (resident) references Residents(id_the_tenant), constraint employee_fk foreign key (employee) references Staff(id_employee) ); --Замена типа данных пк alter table Services drop CONSTRAINT types_of_services_fk; --удаляем фк alter table Payment_for_services --удаляем пк drop primary key; alter table Payment_for_services modify id_services varchar2(10); --меняем тип пк alter table Services modify types_of_services varchar2(10); --меняем тип фк alter table Payment_for_services --добавляем пк add CONSTRAINT id_services_pk primary key (id_services); alter table Services add CONSTRAINT types_of_services_fk --добавляем фк обратно foreign key (types_of_services) references Payment_for_services(id_services);