Проектирование базы данных районной оператора связи. Подсистема «Абонентский отдел»

 

10. Схема отношения Выбор способа оказания услуги (selecting the method of providing the service) 

 

11. Схема отношения Отправка уведомления (sending a notification) 

 
 

12. Схема отношения Консультация абонента (subscriber consultation) 

 

13. Схема отношения Поиск информации (information retrieval) 

4. Создание базы данных 

Физическое проектирование БД  на PostgreSQL 

Создание схемы: Create schema My_BD;

1. Отношение subscriber

                  Create table My_BD.subscriber  

SUB_ID bigint constraint con_1 check (E_ID between 0000000000000000 and 9999999999999999) primary key,

SUB_Login text NOT_NULL,

SUB_Surname text NOT_NULL,

SUB_Name text NOT_NULL,

SUB_Patronymic NOT_NULL,

SUB_Passport integer NOT_NULL,

SUB_Balance real constraint con_2 check (SUB_Balance between -10,00 and 999999,99),

SUB_Born date,

SUB_Telephone integer NOT_NULL,

SUB_Number bigint constraint con_1 check (SUB_Number between 0000000000000001 and 9999999999999999)

); 

2. Отношение services

           Create table My_BD.services 

SER_Type text primary key,

SER_Cost real NOT_NULL,

SER_Name text NOT_NULL,

SER_ Description text NOT_NULL

); 

3. Отношение document

           Create table My_BD.document 

DOC_Type text primary key,

DOC_Name text NOT_NULL,

DOC_Date date,

DOC_Content text NOT_NULL

); 

4. Отношение document search

      Create table My_BD.documentsearch 

DOC_Type text, foreign key (DOC_Type) references My_BD.Document (DOC_Type),

DOC_Name text, foreign key (DOC_Name) references My_BD.Document (DOC_Name),

DOC_Date date, foreign key (DOC_Date) references My_BD.Document (DOC_Date)

);

5. Отношение data storage

      Create table My_BD.data storage 

DOC_Type text, foreign key (DOC_Type) references My_BD.Document (DOC_Type),

DS_samples text NOT_NULL,

SUB_ID bigint, foreign key (SUB_ID) references My_BD.subscriber (SUB_ID),

SER_Type text, foreign key (SER_Type) references My_BD.services (SER_Type),

SUB_Number bigint, foreign key (SUB_Number) references My_BD.subscriber (SUB_Number)

); 

6. Отношение staff

      Create table My_BD.staff 

ST_Contractnumber bigint primary key,

ST_Personaldata text NOT_NULL,

ST_Specialty text NOT_NULL,

      ST_Qualification text NOT_NULL,

      ST_Date date,

      ST_Servicerecord,

      ST_Post text NOT_NULL

); 

7. Отношение software

      Create table My_BD.software 

      SW_Type text primary key,

      SW_Name text NOT_NULL,

      SW_Functional text NOT_NULL,

      SW_ Userguide text NOT_NULL

); 

8. Отношение range of services

      Create table My_BD.rangeofservices 

SER_Type text, foreign key (SER_Type) references My_BD.services (SER_Type),

SER_Cost real, foreign key (SER_Cost) references My_BD.services

(SER_Cost),

SER_Name text, foreign key (SER_Type) references My_BD.services (SER_Type)

); 
 

 

9. Отношение tariffchange

      Create table My_BD.tariffchange 

TC_Type text NOT_NULL,

TC_Name text NOT_NULL,

SUB_Telephone integer, foreign key (SUB_Telephone) references My_BD. subscriber (SUB_Telephone),

TC_Reason text NOT_NULL

); 

     10. Отношение selecting the method of providing the service

      Create table My_BD.selectservice 

SMPS_Bystaff text NOT_NULL,

SMPS_Withoutstaff text NOT_NULL

); 

     11. Отношение sending a notification

      Create table My_BD.sendnotification 

SN_Type text NOT_NULL,

SN_Date date,

SN_ Report text NOT_NULL

); 
 

     12. Отношение subscriber consultation

      Create table My_BD.subscriberconsultation 

SC_Type text NOT_NULL,

SC_Quality integer NOT NULL,

SC_Report date

); 

     13. Отношение information retrieval

Create table My_BD.informationretrieval 

TC_Type text, foreign key (TC_Type) references My_BD.tariffchange (TC_Type),

SER_Type text, foreign key (SER_Type) references My_BD.services (SER_Type),

DOC_Type text, foreign key (DOC_Type) references My_BD.document (DOC_Type),

IR_Date date

);