Partiel M1- CX
Modèles de données et SQL
(2h30 – Jean-Christophe RANGON pour DC Paris)
CORRECTION
- Exercice 1 (3 points)
Réalisez le MCD pour notre base de donnée BANQUE.
Les relations nommées, les cardinalités ainsi que les association déduites doivent apparaitre sur
votre MCD.
Effectuez une copie d’écran lisible et collez la dans le document texte où vous mettez vos
réponses.
- Exercice 2 (3 points)
Réalisez le MLD pour notre base de donnée BANQUE.
Effectuez une copie d’écran lisible et collez la dans le document texte où vous mettez vos
réponses.
Autre notation moderne possible (workbench):
- Exercice 3 (3 points)
Réalisez le MPD pour notre base de donnée BANQUE.
Collez sur votre feuille de réponse les requêtes de création de toutes les tables
de votre base de données.
-- phpMyAdmin SQL Dump
-- version 5.1.2
-- https://www.phpmyadmin.net/
--
-- Host: localhost:8889
-- Generation Time: Feb 16, 2023 at 06:41 PM
-- Server version: 5.7.24
-- PHP Version: 7.4.16
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `banque`
--
-- --------------------------------------------------------
--
-- Table structure for table `agence`
--
CREATE TABLE `agence` (
`id_agence` int(11) NOT NULL,
`nom` varchar(60) NOT NULL,
`ville` varchar(60) NOT NULL,
`actif` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Table structure for table `client`
--
CREATE TABLE `client` (
`id_client` int(11) NOT NULL,
`nom` varchar(45) NOT NULL,
`prenom` varchar(45) NOT NULL,
`ville` varchar(45) NOT NULL,
`id_agence` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Table structure for table `compte`
--
CREATE TABLE `compte` (
`id_compte` int(11) NOT NULL,
`solde` float NOT NULL DEFAULT '0',
`id_client` int(11) NOT NULL,
`id_agence` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Table structure for table `emprunt`
--
CREATE TABLE `emprunt` (
`id_emprunt` int(11) NOT NULL,
`montant` double NOT NULL,
`id_agence` int(11) NOT NULL,
`id_client` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `agence`
--
ALTER TABLE `agence`
ADD PRIMARY KEY (`id_agence`);
--
-- Indexes for table `client`
--
ALTER TABLE `client`
ADD PRIMARY KEY (`id_client`),
ADD KEY `id_agence` (`id_agence`);
--
-- Indexes for table `compte`
--
ALTER TABLE `compte`
ADD PRIMARY KEY (`id_compte`),
ADD KEY `id_agence` (`id_agence`,`id_client`),
ADD KEY `id_client` (`id_client`);
--
-- Indexes for table `emprunt`
--
ALTER TABLE `emprunt`
ADD PRIMARY KEY (`id_emprunt`),
ADD KEY `id_agence` (`id_agence`),
ADD KEY `id_client` (`id_client`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `agence`
--
ALTER TABLE `agence`
MODIFY `id_agence` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `client`
--
ALTER TABLE `client`
MODIFY `id_client` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `compte`
--
ALTER TABLE `compte`
MODIFY `id_compte` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `emprunt`
--
ALTER TABLE `emprunt`
MODIFY `id_emprunt` int(11) NOT NULL AUTO_INCREMENT;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `client`
--
ALTER TABLE `client`
ADD CONSTRAINT `client_ibfk_1` FOREIGN KEY (`id_agence`) REFERENCES `agence` (`id_agence`);
--
-- Constraints for table `compte`
--
ALTER TABLE `compte`
ADD CONSTRAINT `compte_ibfk_1` FOREIGN KEY (`id_agence`) REFERENCES `agence` (`id_agence`),
ADD CONSTRAINT `compte_ibfk_2` FOREIGN KEY (`id_client`) REFERENCES `client` (`id_client`);
--
-- Constraints for table `emprunt`
--
ALTER TABLE `emprunt`
ADD CONSTRAINT `emprunt_ibfk_1` FOREIGN KEY (`id_agence`) REFERENCES `agence` (`id_agence`),
ADD CONSTRAINT `emprunt_ibfk_2` FOREIGN KEY (`id_client`) REFERENCES `client` (`id_client`);
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
- Exercice 4 (1 points)
Collez sur votre feuille de réponse une copie d’écran lisible du MPD.
Collez ensuite sur votre feuille de réponse le dictionnaire des données
- Instructions:
Créez une agence:
nom: ‘HOTEL DE VILLE’
ville: ‘Paris’
actif: 2000000
Créez un client de l’agence nouvellement crée
nom: ‘DUBOIS’
prenom: ‘JEAN’
ville: ‘Puteaux’
Créez un compte pour notre nouveau client dans l’agence:
solde: 25000
Créez un emprunt pour Jean Dubois:
montant: 600000
Imprimez sur votre feuille de réponses les requêtes utilisées pour
peupler la base avec ces données:
INSERT INTO `agence` (`id_agence`, `nom`, `ville`, `actif`) VALUES
(1, 'HOTEL DE VILLE', 'Paris', 2000000);
INSERT INTO `client` (`id_client`, `nom`, `prenom`, `ville`,
`id_agence`) VALUES
(1, 'DUBOIS', 'JEAN', 'Puteaux', 1);
INSERT INTO `compte` (`id_compte`, `solde`, `id_client`, `id_agence`)
VALUES
(1, 25000, 1, 1);
INSERT INTO `emprunt` (`id_emprunt`, `montant`, `id_agence`,
`id_client`) VALUES
(1, 600000, 1, 1);
- Exercice 5 (10 points)
Donnez, sur votre feuille de réponse, les requêtes permettant de:
• Afficher la liste des clients dont le NOM se termine par ‘s’. (3 points)
SELECT * FROM `client` WHERE nom like '%s';
• Afficher la liste des agences de Paris (3 points)
SELECT * FROM agence WHERE ville='Paris';
• Afficher la liste de clients ayant un emprunt dans une agence de Paris.
(4 points)
SELECT DISTINCT c.nom, c.prenom FROM emprunt e inner join client c on e.id_client=c.id_cl
ient inner join agence a on a.id_agence=c.id_agence WHERE a.ville='Paris';
FIN
