/*
 * A taper ou 
 * a lancer par \. create.sql
 * juste après lancement mysql
 *
 * NB. Sous Oracle float (9,2) devient decimal (9,2)
 */

  drop database store;
 create database store;
 use store;
 
 
create table employee (
	enum varchar (5) not null primary key,
	ename varchar (20),
	salary float (9,2),
	address varchar(10),
	dept varchar (5)
);

/*
describe employee;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| enum    | varchar(5)  | NO   | PRI | NULL    |       |
| ename   | varchar(20) | YES  |     | NULL    |       |
| salary  | float(9,2)  | YES  |     | NULL    |       |
| address | varchar(10) | YES  |     | NULL    |       |
| dept    | varchar(5)  | YES  | MUL | NULL    |       |
+---------+-------------+------+-----+---------+-------+
*/


create table department (
	dnum varchar (5) not null primary key,
	dname varchar(15),
	floor int(2),
	mgr varchar(5) not null references employee
		on delete restrict
		on update cascade
);

/*
describe department;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| dnum  | varchar(5)  | NO   | PRI | NULL    |       |
| dname | varchar(15) | YES  |     | NULL    |       |
| floor | int(2)      | YES  |     | NULL    |       |
| mgr   | varchar(5)  | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
*/


alter table employee 
add constraint fkemp foreign key (dept)
	references department (dnum)
		on delete set null
		on update cascade;
        
        

create table product (
	pnum varchar (5),
	pname varchar(10),
	weight int(2),
	price float (7,2),
	address varchar(10),
	constraint pk primary key  using btree (pnum)
);

/*
describe product;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| pnum    | varchar(5)  | NO   | PRI |         |       |
| pname   | varchar(10) | YES  |     | NULL    |       |
| weight  | int(2)      | YES  |     | NULL    |       |
| price   | float(7,2)  | YES  |     | NULL    |       |
| address | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
*/


create table sell (
	dnum varchar (5) not null,
	pnum varchar (5) not null,
	qty int(2),
	constraint sp primary key (dnum, pnum),
	constraint sfd foreign key (dnum) references department (dnum)
		on delete cascade on update cascade,
	constraint sfp foreign key (pnum) references product (pnum)
		on delete cascade on update cascade
);

/*
describe sell;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| dnum  | varchar(5) | NO   | PRI | NULL    |       |
| pnum  | varchar(5) | NO   | PRI | NULL    |       |
| qty   | int(2)     | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
*/


/* Créations d'indexes */

Create index s_p_ind on sell (pnum);
Create index s_d_ind on sell (dnum);
create unique index d_ind on department (dname);

/*
Query OK, 11 rows affected (0.06 sec)
Records: 11  Duplicates: 0  Warnings: 0

Query OK, 11 rows affected (0.23 sec)
Records: 11  Duplicates: 0  Warnings: 0

Query OK, 5 rows affected (0.33 sec)
Records: 5  Duplicates: 0  Warnings: 0
*/

