8GpMC44S

1/*
2
3Creation de tp1 et de l'ensemble des tables par:
4\i script_tables.sql
5
6*/
7CREATE DATABASE tp1;
8\c tp1
9
10DROP TABLE IF EXISTS  pays cascade;
11CREATE TABLE pays (
12	code  CHAR(2) NOT NULL PRIMARY KEY,
13	nom VARCHAR(50) NOT NULL
14	);
15\copy pays from 'pays.txt'
16
17
18DROP TABLE IF EXISTS  classe cascade;
19CREATE TABLE classe (
20	num  INT  NOT NULL PRIMARY KEY,
21	libelle VARCHAR(30)  NOT NULL
22	);
23
24\copy classe from 'classe.txt'
25
26
27DROP TABLE IF EXISTS  societe cascade;
28CREATE TABLE societe (
29	id  INT  NOT NULL PRIMARY KEY,
30	nom VARCHAR(40)  NOT NULL,
31	ville VARCHAR(20),
32	pays CHAR(2)  NOT NULL REFERENCES pays
33	);
34
35\copy societe from 'societe.txt'
36
37
38DROP TABLE IF EXISTS  marque cascade;
39CREATE TABLE marque (
40	id  INT NOT NULL  PRIMARY KEY,
41	nom VARCHAR(30)  NOT NULL,
42	classe INT  NOT NULL REFERENCES classe,
43	pays CHAR(2)  NOT NULL REFERENCES pays,
44	prop INT  NOT NULL REFERENCES societe
45	);
46
47\copy  marque from 'marque.txt'
48
49
50
51DROP TABLE IF EXISTS  enr cascade;
52CREATE TABLE enr (
53	marque  INT  NOT NULL REFERENCES marque,
54	num INT  NOT NULL, 
55	pays CHAR(2)  NOT NULL REFERENCES pays,
56	deposant INT  NOT NULL REFERENCES societe,
57	date_enr DATE NOT NULL,
58	CONSTRAINT cle_enr PRIMARY KEY (num,pays)
59	);
60
61\copy enr from 'enr.txt'
62
63
64DROP TABLE IF EXISTS  vente cascade;
65CREATE TABLE vente (
66	marque  INT  NOT NULL REFERENCES marque,
67	vendeur INT  NOT NULL REFERENCES societe,	
68	acquereur INT   NOT NULL REFERENCES societe,
69	date_vente DATE  NOT NULL                         
70	);
71
72\copy vente from 'vente.txt'
73
74
75
76
77grant select on table classe to public;
78grant select on table enr to public;
79grant select on table marque to public;
80grant select on table pays to public;
81grant select on table societe to public;
82grant select on table vente to public;