Modalité du TP
Si je ne peux contrôler votre travail à l'aide d'une application de prise en main à distance je souhaiterais que vous ayez réalisé une capture d'écran du tableau des relations de votre base de données. Je pourrais également récupérer votre base de donnée et les différents documents demandés dans ce TP.
1 .Création d'une base de données :
Une société souhaite faire de la gestion des ressources humaines sur une population de consultant ayant des compétences pointues.
Pour cela elle souhaite disposer d'une liste de ce personnel dans laquelle pourra renseigner les informations suivantes :
Nom, prénom, photos, sexe, nationalité, date de naissance, date de première embauche dans la société, date de dernière embauche dans la société, Business Unit, date de départ de la société, email, adresse, code postal, ville, pays, note (un champs commentaire), situation familiale.
La situation familiale indique si la personne est dans un statut marié ou en couple ou célibataire.
La BU indique le nom de l'équipe dans laquelle la personne travaille.
Vous devrez créer une table listant les différentes BU.
Chaque salarié ne peut travailler que dans une seule BU.
Une BU peut accueillir évidemment plusieurs salariés aussi je vous laisse le soin de créer les relations entre les tables pour appliquer cette intégrité référentielle.
On souhaite suivre l'évolution salariale de cette population.
Aussi on enregistrera dans une table annexe l'évolution salariale du consultant.
On enregistrera donc son salaire avec la date de début avec la date de début la date de fin sur laquelle il est resté avec ce salaire.
On souhaite suivre les capacités linguistiques de cette population.
En particulier on a une série de langue pour laquelle on souhaite connaître les compétences de tous les salariés.
Je vous laisse le soin de créer une table listant les langues que l'on va suivre.
Pour créer cette relation M2M entre les langues et le personnel vous aurez besoin de créer une table auxiliaire dans laquelle vous enregistrez en plus de la langue et du salarié son niveau sur la langue. Ce niveau sera une valeur comprise entre 1 et 10.
Vous placerez dans votre base de données des valeurs dans les différentes tables afin de tester l'enregistrement de vos données mais aussi afin de pouvoir réaliser des requêtes SQL et pouvoir les valider rapidement.
Il faut généralement saisir au moins 5 valeurs dans chaque table afin de pouvoir réaliser des tests.
Travail attendu
Vous procéderez à la création des tables et des relations entre les différentes tables.
Je souhaite dans votre compte rendu une capture d'écran sur l'affichage de la relation dans la base de données.
Je peux également être amené à contrôler le respect des différentes consignes dans votre base de données.
Je vous mets à disposition des captures d'écran d'une application réalisée qui est à l'origine de ce devoir pour vous inspirer…. http://www.winform.fr/blog/Lists/Photos/capture%20relation%20png.png
Cliquer ici pour la capture HD
Créer une requête permettant d'afficher toutes les personnes ayant des compétences en anglais.
Créer une requête pour connaître la masse salariale annuel des salariés de l'entreprise par rapport à leur salaire connu à la date du 1er janvier 2019.
Créer une requête pour donner la liste des salariés qui sont nés après le 1er janvier 1970.
Créer une requête pour avoir la liste des consultants habitant Paris.
Vous aurez à créer un classeur un classeur Excel qui ira importer les données en provenance de la base Access.
En particulier je vous laisse le soin à l'aide de ce classeur Excel de créer un tableau croisé dynamique qui permettra de connaître le nombre de personnes ayant des compétences dans chacune des langues parlés par mes salariés.
En seconde partie de ce devoir je vous demande de recréer les tables et les relations que vous avez faites précédemment dans la base de données Access dans votre serveur SQL Server
2. Configuration SQL Server
La question suivante est complètement autonome elle consiste
à produire une procédure document dans le changement de port d'une instance
SQL.
Vous réaliserez une procédure avec les différentes
captures d'écran afin de montrer comment vous avez changé le port de votre
instance SQL installée sur votre machine.
Je souhaite que le changement de port se fasse avec un port
fixe que vous aurez choisi en respectant les consignes de l’IANA et afin de vous
assurer que ce port ne rentre pas en conflit avec d'autres applications qui
pourrait être installées sur votre machine.
À l'issue de votre modification vous aurez redémarrer le
service SQL et vous aurez à tester la connexion à l'aide d'une application tel
SSMS sur votre moteur de base de données.
Vous devrez modifier votre chaîne de connexion pour
indiquer le numéro du port sur lequel vous souhaitez vous connecter.
Quel modification devez-vous faire sur votre serveur pour
accepter les connexions entrantes distantes pour SQL Server ?
3. Administration SQL Server
Donner
un script en batch ou powershell qui permet de redémarre votre service SQL
Server.
4. Gestion des droits sur une base SQL
Dans l’exemple précédent, on
souhaite sécuriser la table des salaires, seul les membre d’un groupe local
nommé ComptaRH, ansi qu’un user SQL nommé AccessRH doit y avoir accés.
a. Créer le groupe local sur
votre machine à l’aide d’une ligne de commande
b. Créer une connexion SQL qui
pointe vers ce groupe local
c. Créer un utilisateur nommé
AccessRH sur le serveur SQL
d. Créer un utilisateur dans la
bdd qui pointe vers l’utilisateur précédent et une connexion qui pointe vers le
groupe.
e. Créer un schéma de base de
donnée nommé Confidentiel et placer la table contenant les informations sur salaire
dans ce schémas.
f.
Sécurisé
le schéma pour autoriser ComptaRH et AccessRH en lecture écriture dessus.
g. Créer sur votre machine un
groupe nommé AdmSQL et donner à ce
groupe un accès complet à la gestion du serveur SQL.
4 Pour le fun....
Dans une fenêtre de scripts
SQL lancez …
Tester le script suivant sur votre machine
USE master ;
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\saledat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB ) ;
GO
En cas d’échec, vérifier si l’emplacement demandé pour le
stockage de la base de données existe, le cas échéant demander la création de
la base de données dans le dossier c:\SQLDB en modifiant le script.
En cas de succès, refaites l’exercice pour créer la base nommé
SALES2 dans c:\SQLDB
Expliquer le résultat.