DROP SCHEMA IF EXISTS AMIS; 
CREATE SCHEMA AMIS; 
USE AMIS;

CREATE TABLE PERSONNE(
       idPersonne INT primary key, 
       nomPersonne TEXT,
       age INT); 
       
CREATE TABLE AMI(
       idPersonne1 INT, 
       idPersonne2 INT, 
       primary key (idPersonne1, idPersonne2), 
       foreign key (idPersonne1) 
       	       references Personne(idPersonne),
       foreign key (idPersonne2) 
       	       references Personne(idPersonne)
       ); 

CREATE TABLE FAMILLE (
       idPersonne1 INT, 
       idPersonne2 INT,
       primary key (idPersonne1, idPersonne2), 
       foreign key (idPersonne1) 
       	       references Personne(idPersonne),
       foreign key (idPersonne2) 
       	       references Personne(idPersonne)
);

insert into PERSONNE values (1, 'Elvia', 19);
insert into PERSONNE values (2, 'Farouk', 19);
insert into PERSONNE values (3, 'Sam', 19);
insert into PERSONNE values (4, 'Tiffany', 19);
insert into PERSONNE values (5, 'Nadia', 14);
insert into PERSONNE values (6, 'Chris', 12);
insert into PERSONNE values (7, 'Kris', 10);
insert into PERSONNE values (8, 'Bethany', 16);
insert into PERSONNE values (9, 'Louis', 17);
insert into PERSONNE values (10, 'Austin', 22);
insert into PERSONNE values (11, 'Gabriel', 21);
insert into PERSONNE values (12, 'Jessica', 20);
insert into PERSONNE values (13, 'John', 16);
insert into PERSONNE values (14, 'Alfred', 19);
insert into PERSONNE values (15, 'Samantha', 17);
insert into PERSONNE values (16, 'Craig', 17);

insert into AMI values (1, 3);
insert into AMI values (1, 2);
insert into AMI values (2, 4);
insert into AMI values (3, 9);
insert into AMI values (4, 9);
insert into AMI values (2, 6);
insert into AMI values (6, 7);
insert into AMI values (6, 10);
insert into AMI values (6, 13);
insert into AMI values (7, 5);
insert into AMI values (7, 8);
insert into AMI values (5, 8);
insert into AMI values (9, 11);
insert into AMI values (9, 12);
insert into AMI values (11, 12);
insert into AMI values (12, 15);
insert into AMI values (10, 15);
insert into AMI values (15, 13);
insert into AMI values (13, 16);
insert into AMI values (16, 14);

insert into FAMILLE values(2, 4);
insert into FAMILLE values(4, 2);
insert into FAMILLE values(6, 4);
insert into FAMILLE values(11, 9);
insert into FAMILLE values(9, 7);
insert into FAMILLE values(8, 7);
insert into FAMILLE values(10, 13);
insert into FAMILLE values(12, 15);
insert into FAMILLE values(15, 12);
insert into FAMILLE values(14, 5);
