/* * 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; drop table if exists employee;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 | |+---------+-------------+------+-----+---------+-------+*/drop table if exists department;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; drop table if exists product;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 | |+---------+-------------+------+-----+---------+-------+*/drop table if exists sell;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: 0Query OK, 11 rows affected (0.23 sec)Records: 11 Duplicates: 0 Warnings: 0Query OK, 5 rows affected (0.33 sec)Records: 5 Duplicates: 0 Warnings: 0*/