Création de la base Oracle

Question

Implémentez la base mediatheque correspondant aux fichiers ci-après.

Vous créerez 1 fichier mediatheque.sql qui appellera les 4 fichiers create.sql, insert1.sql, insert2.sql et insert3.sql.

1
-- CREATE TABLE
2
3
create table CLIENT (
4
	num number,
5
	nom varchar(20),
6
	prenom varchar(20),
7
	adresse varchar(128),
8
	date_nais date,
9
	tel varchar(20),
10
	sexe char check (sexe in ('m','f')),
11
	constraint PK_CLIENT primary key (num)
12
);
13
14
create sequence client_seq
15
INCREMENT BY 1
16
START WITH 1
17
NOMAXVALUE
18
NOCYCLE
19
CACHE 10;
20
21
create table FACTURE (
22
	num number,
23
	date_etabli date,
24
	client number,
25
	constraint PK_FACTURE primary key (num),
26
	constraint FK_FACTURE_CLIENT foreign key (client) references CLIENT(num)
27
);
28
29
create sequence facture_seq
30
INCREMENT BY 1
31
START WITH 1
32
NOMAXVALUE
33
NOCYCLE
34
CACHE 10;
35
36
create table PRODUIT (
37
	num number,
38
	designation varchar(128),
39
	prix number,
40
	stock number,
41
	constraint PK_PRODUIT primary key (num)
42
);
43
44
create sequence produit_seq
45
INCREMENT BY 1
46
START WITH 1
47
NOMAXVALUE
48
NOCYCLE
49
CACHE 10;
50
51
create table LIGNE_FACT (
52
	facture number,
53
	produit number,
54
	qte number,
55
	constraint FK_LIGNE_FACT_FACTURE foreign key (facture) references FACTURE(num),
56
	constraint FK_LIGNE_FACT_PRODUIT foreign key (produit) references PRODUIT(num),
57
	constraint PK_LIGNE_FACT primary key (facture, produit)
58
);
1
-- INSERT INTO CLIENT
2
3
insert into CLIENT(num, nom, prenom, adresse, date_nais, tel, sexe) values (
4
	client_seq.NEXTVAL,
5
	'coulomb',
6
	'francois',
7
	'4, rue liberte',
8
	to_date('02121980','DDMMYYYY'),
9
	'06456780',
10
	'm'
11
);
12
13
insert into CLIENT(num, nom, prenom, adresse, date_nais, tel, sexe) values (
14
	client_seq.NEXTVAL,
15
	'bernard',
16
	'dupont',
17
	'120, square zola',
18
	to_date('19081972','DDMMYYYY'),
19
	'01345678',
20
	'm'
21
);
22
23
insert into CLIENT(num, nom, prenom, adresse, date_nais, tel, sexe) values (
24
	client_seq.NEXTVAL,
25
	'corda',
26
	'nathalie',
27
	'66, bv napoleon III',
28
	to_date('02101977','DDMMYYYY'),
29
	'06455790',
30
	'f'
31
);
32
33
insert into CLIENT(num, nom, prenom, adresse, date_nais, tel, sexe) values (
34
	client_seq.NEXTVAL,
35
	'pierre',
36
	'alexandre',
37
	'1, place des martyres',
38
	to_date('19081972','DDMMYYYY'),
39
	'01645870',
40
	'f'
41
);
42
43
commit;
1
-- INSERT INTO PRODUIT
2
3
insert into PRODUIT(num, designation, prix, stock) values(
4
	produit_seq.NEXTVAL,
5
	'DVD Matrix',
6
	23.5,
7
	100
8
);
9
10
insert into PRODUIT(num, designation, prix, stock) values(
11
	produit_seq.NEXTVAL,
12
	'DVD seigneur des anneaux',
13
	49.30,
14
	100
15
);
16
17
insert into PRODUIT(num, designation, prix, stock) values(
18
	produit_seq.NEXTVAL,
19
	'CD Album Yanni',
20
	25.9,
21
	100
22
);
23
24
commit;
1
-- INSERT INTO FACTURE, LIGNE_FACT
2
3
-- Facture 1
4
5
insert into FACTURE(num, client, date_etabli) values(
6
	facture_seq.NEXTVAL,
7
	2,
8
	to_date('20122000','DDMMYYYY')
9
);
10
11
insert into LIGNE_FACT(facture, produit, qte) values(
12
	facture_seq.CURRVAL,
13
	1,
14
	1
15
);
16
17
insert into LIGNE_FACT(facture, produit, qte) values(
18
	facture_seq.CURRVAL,
19
	2,
20
	2
21
);
22
23
-- Facture 2
24
25
insert into FACTURE(num, client, date_etabli) values(
26
	facture_seq.NEXTVAL,
27
	2,
28
	to_date('01012001','DDMMYYYY')
29
);
30
31
insert into LIGNE_FACT(facture, produit, qte) values(
32
	facture_seq.CURRVAL,
33
	1,
34
	2
35
);
36
37
insert into LIGNE_FACT(facture, produit, qte) values(
38
	facture_seq.CURRVAL,
39
	3,
40
	1
41
);
42
43
insert into LIGNE_FACT(facture, produit, qte) values(
44
	facture_seq.CURRVAL,
45
	2,
46
	2
47
);
48
49
-- Facture 3
50
51
insert into FACTURE(num, client, date_etabli) values(
52
	facture_seq.NEXTVAL,
53
	2,
54
	to_date('17112004','DDMMYYYY')
55
);
56
57
insert into LIGNE_FACT(facture, produit, qte) values(
58
	facture_seq.CURRVAL,
59
	1,
60
	2
61
);
62
63
insert into LIGNE_FACT(facture, produit, qte) values(
64
	facture_seq.CURRVAL,
65
	2,
66
	4
67
);
68
69
insert into LIGNE_FACT(facture, produit, qte) values(
70
	facture_seq.CURRVAL,
71
	3,
72
	1
73
);
74
75
-- Facture 4
76
77
insert into FACTURE(num, client, date_etabli) values(
78
	facture_seq.NEXTVAL,
79
	3,
80
	to_date('14012004','DDMMYYYY')
81
);
82
83
insert into LIGNE_FACT(facture, produit, qte) values(
84
	facture_seq.CURRVAL,
85
	2,
86
	1
87
);
88
89
insert into LIGNE_FACT(facture, produit, qte) values(
90
	facture_seq.CURRVAL,
91
	3,
92
	1
93
);
94
95
-- Facture 5
96
97
insert into FACTURE(num, client, date_etabli) values(
98
	facture_seq.NEXTVAL,
99
	4,
100
	to_date('19022004','DDMMYYYY')
101
);
102
103
insert into LIGNE_FACT(facture, produit, qte) values(
104
	facture_seq.CURRVAL,
105
	1,
106
	2
107
);
108
109
insert into LIGNE_FACT(facture, produit, qte) values(
110
	facture_seq.CURRVAL,
111
	3,
112
	3
113
);
114
115
insert into LIGNE_FACT(facture, produit, qte) values(
116
	facture_seq.CURRVAL,
117
	2,
118
	4
119
);
120
121
-- Facture 6
122
123
insert into FACTURE(num, client, date_etabli) values(
124
	facture_seq.NEXTVAL,
125
	4,
126
	to_date('17032004','DDMMYYYY')
127
);
128
129
insert into LIGNE_FACT(facture, produit, qte) values(
130
	facture_seq.CURRVAL,
131
	1,
132
	7
133
);
134
135
insert into LIGNE_FACT(facture, produit, qte) values(
136
	facture_seq.CURRVAL,
137
	2,
138
	3
139
);
140
141
insert into LIGNE_FACT(facture, produit, qte) values(
142
	facture_seq.CURRVAL,
143
	3,
144
	1
145
);
146
147
commit;
Indice
Exécution de fichiers SQL
Solution
1
-- mediatheque.sql
2
@create.sql
3
@insert1.sql
4
@insert2.sql
5
@insert3.sql
6
COMMIT;