drop table doc cascade constraints;
drop table pat cascade constraints;
drop table vis cascade constraints;

create table doc(
   did number,
   dna varchar2(30),
   spe varchar2(30),
   constraint PK_doc primary key (did));

create table pat (
   pid number,
   pna varchar2(30),
   pad varchar2(30),
   pte number,
   pdb date,
   constraint PK_pat primary key (pid));

create table vis (
   did number,
   pid number,
   dvi date,
   dia varchar2(20),
   typ varchar2(5),
   pri number,
   constraint PK_vis primary key (did,pid,dvi),
   constraint FK_vis_doc foreign key (did)
       references doc(did),
   constraint FK_vis_pat foreign key (pid)
       references pat(pid)); 

alter session set nls_date_language=english;
alter session set nls_date_format='DD-MON-YYYY';


insert into doc values (1,'King','orthoped');
insert into doc values (2,'Jones','dentist');
insert into doc values (3,'Ford','internist');
insert into doc values (4,'Smith','internist');
insert into doc values (5,'Blake','orthoped');
insert into doc values (6,'Simon','face-lifter');

insert into pat values (1,'ALLEN','Wall Street 112',123456,'20-FEB-1942');                          
insert into pat values (2,'WARD','Baker Street 62',654321,'22-FEB-1925');                           
insert into pat values (3,'MARTIN','Butcher Street 77',456789,'28-SEP-1919');                         
insert into pat values (4,'SCOTT','Picadili 12',987654,'09-DEC-1936');                          
insert into pat values (5,'TURNER','Wall Street 56',123789,'08-SEP-1989');                         
insert into pat values (6,'ADAMS','Baker Street 47',987321,'12-JAN-1983');                          
insert into pat values (7,'JAMES','Picadili 56',963852,'03-DEC-1971');                          
insert into pat values (8,'CLARK','Wall Street 56',852741,'09-JUN-1963');                          
insert into pat values (9,'MILLER','Wall Street 112',147258,'23-JAN-1948');                         

alter session set nls_date_format='DD-MON-RR';

insert into vis values (1,1,'18-JAN-97','D2','H',52);
insert into vis values (2,1,'8-JAN-96','D2','H',52);
insert into vis values (3,1,'1-JAN-93','D1','H',2);
insert into vis values (4,1,'18-JAN-93','D2','H',5);
insert into vis values (5,1,'18-JAN-97','D2','H',52);
insert into vis values (1,7,'1-JAN-90','D2','H',12);
insert into vis values (1,9,'1-JAN-99','D2','H',15);
insert into vis values (1,3,'23-FEB-89','D3','O',45);
insert into vis values (3,3,'01-JUN-89','D1',NULL,NULL);
insert into vis values (3,9,'04-NOV-99','D1','O',NULL);
insert into vis values (3,5,'03-JAN-87','D2','H',NULL);
insert into vis values (1,1,'09-MAR-96','D4','O',NULL);
insert into vis values (4,4,'23-DEC-87','D3','H',85);
insert into vis values (4,6,'30-JAN-88','D2','H',89);
insert into vis values (5,7,'14-OCT-96','D4','O',63);
insert into vis values (5,4,'23-OCT-99','D3','O',78);
insert into vis values (3,5,'12-JUN-95','D2','H',54);
insert into vis values (4,4,'23-JAN-98','D1',NULL,NULL);
insert into vis values (5,5,'15-DEC-87','D2','H',65);
insert into vis values (1,9,'13-NOV-94','D1','O',45);
insert into vis values (1,7,'24-MAR-99','D1','O',23);
insert into vis values (2,2,'26-MAR-99','D1','O',23);
insert into vis values (2,1,'27-JAN-99','D2','O',28);
insert into vis values (2,4,'24-MAR-97','D1','H',99);

commit;