ةيسنوتلا ةيروهمجلا
ثحبلاو
يلاعلا
ميلعتلا
ةرازو
لوخدلل ةينطولا تارظانملا
نيسدنهملا
نيوكت لحارم ىلإ
2019
ةرود
REPUBLIQUE TUNISIENNE
Ministère de l'Enseignement Supérieur,
de la Recherche Scientifique
Concours Nationaux d’Entrée
aux Cycles de Formation d’Ingénieurs
Session 2019
يملعلا
Altérative de Correction
Concours Biologie
Epreuve d’Informatique
PROBLEME 1
1.
def LoadFile(fname):
with open(fname) as f:
keys = f.readline().strip().split("#")
values = [[int(x) for x in l.split("#")] for l in f]
return {k:list(v) for k,v in zip(keys,zip(*values))}
2.
def CountValues(DEST, obs):
return len(set(DEST[obs]))
3.
def EvalDistr(DSET):
decision = DSET.get("decision", [])
if len(decision) == 0:
return {0:0.5, 1:0.5}
p0 = decision.count(0) / len(decision)
return {0:p0, 1:1-p0}
4.
def IsPure(DSET):
Publicité
return 1 in EvalDistr(DSET).values()
5.
def IsQualitative(DSET):
return {k:set(v) <= {0,1} for k,v in DSET.items()}
6.
def Cut(DSET, obs, S = 0.5):
keys = set(DSET)
keys.remove(obs)
DSET1, DSET2 = {}, {}
for k in keys:
vals1, vals2 = [], []
vals = DSET[k]
for i in range(len(vals)):
if DSET[obs][i] >= S:
vals1.append(vals[i])
Concours Biologie- Session Juin 2019 Epreuve d’Informatique Page 1/5
else:
vals2.append(vals[i])
DSET1[k], DSET2[k] = vals1, vals2
oth_obs = keys.pop()
p0 = len(DSET1[oth_obs])/len(DSET[obs])
return [DSET1, DSET2], [p0,1-p0]
7.
def Impurity(DSET, obs, S= 0.5):
[DSET1, DSET2], [p1, p2] = Cut(DSET, obs, S)
return p1 min(EvalDistr(DSET1).values()) + p2 min(EvalDistr(DSET2).values())
8.
def SortObs(DSET, obs):
return sorted((v,d) for v, d in zip(DSET[obs],DSET["decision"]))
9.
def BestCut(DSET, obs, Qual):
if Qual[obs]:
return (0.5, Impurity(DSET, obs))
else:
Lc = SortObs(DSET, obs)
Lseuil = []
if IsPure(DSET):
Lseuil.append(max(Lc)[0])
else:
Publicité
for i in range(len(Lc)-1):
vc, dc = Lc[i]
vn, dn = Lc[i+1]
if dc != dn:
Lseuil.append((vc + vn)/2)
sBest = min(Lseuil, key = lambda s : Impurity(DSET, obs, s))
return (sBest, Impurity(DSET, obs, sBest))
PROBLEME 2
Partie 1 : algèbre relationnelle
1. Π
𝑑𝑠_𝑖𝑑,𝑑𝑠_𝑛𝑎𝑚𝑒,𝑑𝑠_𝑑𝑒𝑠𝑐𝑟𝑖𝑝𝑡𝑖𝑜𝑛 (𝜎𝑓𝑜𝑟𝑚𝑎𝑡=′𝑐𝑠𝑣′(𝐷𝑎𝑡𝑎𝑆𝑒𝑡))
2. Π𝑐𝑙𝑠_𝑑𝑒𝑐𝑟𝑖𝑝𝑡𝑖𝑜𝑛( 𝜎𝑙𝑎𝑛𝑔𝑢𝑎𝑔𝑒=′𝑃𝑦𝑡ℎ𝑜𝑛′𝑒𝑡 𝑐𝑎𝑡𝑒𝑔𝑜𝑟𝑦=′𝐾𝑁𝑁′(𝐶𝑙𝑎𝑠𝑠𝑖𝑓𝑖𝑒𝑢𝑟 ⋈𝑐𝑙𝑠_𝑖𝑑 𝐶𝑜𝑚𝑏𝑖𝑛𝑒⋈𝑚_𝑛𝑎𝑚𝑒 𝑀𝑒𝑡ℎ𝑜𝑑)
Partie 2 : SQL
3.
UPDATE DataSET
SET format = 'docx'
WHERE format = 'csv';
Concours Biologie- Session Juin 2019 Epreuve d’Informatique Page 2/5
4.
DELETE FROM Classifieur
WHERE language = 'PASCAL';
5.
SELECT ds_id
FROM Classifieur
WHERE error_rate < 0.3;
6.
SELECT ds_name
FROM DataSET
WHERE nb_instances = (
SELECT MAX(nb_instances)
FROM DataSet
);
7.
SELECT ds_id, ds_name
FROM DataSet AS D
WHERE NOT EXISTS
(
SELECT * FROM Classifieur AS C
WHERE C.ds_id = D.ds_id AND language != 'Python'
Publicité
);
8.
SELECT ds_id
FROM DataSet
WHERE ds_id NOT IN
(
SELECT ds_id
FROM Classifieur
);
9.
SELECT COUNT(*)
FROM Classifieur
WHERE error_rate > (SELECT AVG(error_rate) FROM Classifieur);
10.
SELECT D.ds_id, COUNT(DISTINCT M.m_name) AS NB_M
FROM DataSet AS D,
Classifieur AS C,
Combine AS CM,
Method AS M
WHERE (D.ds_id = C.ds_id) AND
(C.cls_id = CM.cls_id) AND
(CM.m_name = M.m_name)
GROUP BY C.cls_id ;
Concours Biologie- Session Juin 2019 Epreuve d’Informatique Page 3/5
11.
SELECT * FROM DataSet
WHERE ds_id IN
(
SELECT ds_id FROM Classifieur
WHERE error_rate =
(
SELECT MIN(error_rate)
FROM Classifieur
)
);
12.
SELECT D.ds_id, ds_name
FROM DataSet AS D,
Classifieur AS C
Publicité
WHERE (D.ds_id = C.ds_id) AND (language = 'Java')
GROUP BY ds_id
HAVING COUNT(*) >= 3;
Partie 3 : sqlite3
13.
import sqlite3
cnx = sqlite3.connect('C1.db')
cur = cnx.cursor()
sql_tbl = """
CREATE TABLE Method
(
m_name TEXT PRIMARY KEY,
category TEXT,
m_description TEXT
);
"""
cur.execute(sql_tbl)
sql_ins = """
INSERT INTO Method Values(?,?,?)
"""
lst_data = [(k,) + tuple(v) for k,v in dict_M.items()]
cur.executemany(sql_ins, lst_data)
cnx.commit()
sql_sel = """
SELECT ds_id, AVG(error_rate) AS M_erreur
FROM Classifieur
GROUP BY ds_id
ORDER BY ds_id
"""
cur.execute(sql_sel)
x, y = zip(*cur.fetchall())
Concours Biologie- Session Juin 2019 Epreuve d’Informatique Page 4/5
import matplotlib.pyplot as plt
plt.plot(x,y)
plt.show()
cnx.close()
Concours Biologie- Session Juin 2019 Epreuve d’Informatique Page 5/5