conception d’un schéma relationnel.

Laisser une réponse

Les dépendances fonctionnelles.

Je viens de suivre un cours trés intéressant de michel freville du cnam sur la conception d’un schéma relationnel, j’y ai appris la méthode « ancienne » pour décomposer les relations ( autrement dit les tables ). Il existe tout un processus en commençant par déterminer les dépendances fonctionnelles. Il s’agit de déterminer les liens sémantiques entre les attributs ou les sous ensemble d’attributs d’une relation. Par exemple : La puissance d’une voiture est dépendante de l’immatriculation du véhicule. Si je connais l’immatriculation, je peux déduire la puissance. c’est une dépendance fonctionnelle. On doit déterminer la couverture minimale des dépendances fonctionnelles. c’est à dire, les seules dépendances qui restent quand on a supprimé toute celle qui se déduise logiquement. Par exemple, l’immatriculation définit la marque, la marque définit le pays. Une relation inutile à supprimer est donc l’immatriculation définit le pays… On est capable de retrouver cette dépendance fonctionnelle par transitivité. A partir de la couverture minimale, on est capable de déterminer la clé primaire de la relation en produisant un graphe des dépendances fonctionnelles. c’est assez génial! Je ne détaille pas. Il existe aussi un algorithme de BERNSTEIN qui permet de passer d’un graphe des dépendances fonctionnelles à un schéma relationnelle compatible avec la 3 eme forme normale.

Les formes normales.

Les formes normales ne sont que des règles qui permettent de déterminer le niveau de redondance et d’intégrité d’un schéma relationnelle. Un diagramme de classe conçu par l’analyse UML ( objet du monde réel distincts ) si il est conçu intelligemment avec une analyse poussée supprimant toute redondance d’information conduit à un schéma relationnel qui respecte la quatrième forme normale. Si l’analyse n’est pas poussée assez loin, on peut se retrouver à ne pas respecter la 4 eme, la 3 eme, la seconde ou la première forme normale.

Première forme normale :

Une relation R est en 1ière forme normale, si tout attribut
de R contient une valeur atomique.

exemple :
Membre du projet(NumeroMembre,Nom,Ville,NumerosDesProjets) est dénormalisée car NumérosDesProjets contient plusieurs Numéros de Projets pour un seul membre.
(‘1′,’Jean’,’Lyon’,’1,2,7′) (‘2′,’corinne’,’Marseille’,’1,2,9′)…

Pour passer en premiere forme normale:
MembreDuProjet(NumeroMembre,Nom,Ville,NumeroProjet)
(‘1′,’jean’,’Lyon’,’1′) (‘1′,’jean’,’Lyon’,’2′) (‘1′,’jean’,’Lyon’,’7′) (‘2′,’corinne’,’Marseille’,’1′)…

remarque : en premiere forme normale, on est oblige de changer de cle primaire : NumeroMembre ne suffit plus, on doit ajouter NumeroProjet.

Deuxième forme normale :

- Une relation R est en 2ième forme normale si et
seulement si :
1) Elle est en 1ière forme normale.
2) s’il existe une dépendance fonctionnelle totale reliant la clé à chaque attribut non clé.

remarque : la dependance fonctionnelle totale indique que pour une cle primaire compose, l’attribut doit etre dépendant de toute la cle composée, pas seulement d’une partie.

Pour passer MembreduProjet en 2 eme forme normale ( en fait on passe directement en 3 eme forme normale )
Employe(NumeroEmploye,Nom,ville)
Appartenance(NumeroEmploye,NumeroProjet)

Troisième forme normale :

- Une relation R est en 3ième forme normale si et
seulement si :
1) Elle est en 2ième forme normale.
2) Aucun Attribut non clé ne dépend d’une clé quelconque par transitivité.

exemple :EmployeDuDepartement(NumeroEmploye,Nom,prenom,age,NumeroDepartement,DescriptionDepartement ) est en deuxieme forme normale.
En troisième forme normale, cela donne :
Employe(NumeroEmploye,Nom,prenom,age)
Departement(NumeroDepartement,DescriptionDepartement)

Pour un complément d’information sur les formes normales, je vous renvoie vers le livre de Andreas Meier – introduction pratique aux bases de données relationnelles.

A bientôt.

Spécification d’un mode de récupération.

Laisser une réponse

Un mode de récupération est une option de configuration de bases de données qui contrôle la façon dont les transactions ( insert,update,delete ) sont journalisés dans le fichier de log, s’il est effectué une sauvegarde du journal des transactions et quelles sont les options de récupération disponible. Le mode de récupération retenu pour votre base de données possède des implications à la fois sur la récupération de la base de données et sur les performances, en fonction de la journalisation effectuée ou non par le mode.

SQL Server 2005 propose trois mode de récupération pour les bases de données :
- complet
- simple
- journalisé en bloc

Dans le mode de récupération complet, le moteur de bases de données journalise toute les opérations sans jamais les tronquer. Ce mode permet de restaurer une base de données au point de défaillance. Il s’agit du mode recommandé pour une base OLTP fortement sollicité. Le journal est vidé à chaque sauvegarde. Toutes les opérations sont journalisés dans le journal.

Dans le mode de récupération simple, le moteur de base de données limite la journalisation de la majorité des transactions et tronque le journal de transaction ( suppression des données ) après chaque point de vérification ( toutes les secondes ). Ce mode ne permet pas de sauvegarder et de restaurer le journal de transaction. Il s’agit d’un mode facile à maintenir car on ne gère guère le journal de transaction, par contre, il ne permet pas de revenir à l’instant t de la défaillance ce que permet la sauvegarde régulière du journal.

Dans le mode de récupération journalisé en bloc, le moteur de base de données journalise de façon minimal les opérations de masse comme select into et bulk insert ( BCP également ). Dans ce mode, si une sauvegarde de journal contient une quelconque opération en bloc, vous pouvez restaurer la base de données à la fin de la sauvegarde du journal, pas à un point déterminé comme dans la journalisation complète. Ce mode doit être employé lors de grosses opérations en bloc.C’est le mode recommandé pour la business intelligence.

EXEMPLE :

ALTER DATABASE MaBase SET RECOVERY { FULL | BULK_LOGGED | SIMPLE }

Comment définir des paramètres optionnels pour une procédure stockée ?

1 réponse

Ce matin, j’ étais en discussion avec un prospect, et il me pose la question suivante : savez-vous comment définir un paramètre optionnel pour une SP ?

Ma réponse à froid : tous les paramètres sont obligatoires en .Net mais je place un IF dans la procédure stockée afin que si la valeur soit égale à 0 on n’ effectue une requête sans le paramètre.

En fait, il est parfaitement possible d’ avoir un paramètre optionnel, c’ est à dire que l’ on ne déclare pas dans le code ASP.NET. Il suffit d’ affecter une valeur par défaut au paramètre de la procédure par exemple

CREATE PROCEDURE ListeSalarie

@Age int = 18

AS

SELECT Nom,Matricule FROM Salarie WHERE Age>@Age;

Par cette procédure, vous pouvez appeler la procédure ListeSalarie sans paramètre pour avoir la liste des salarie supérieur à l’ age légal de la majorité.

Par cette procédure, vous pouvez appeler la procédure ListeSalarie avec un paramétre @age pour avoir la liste des salarie supérieur à cette @age donné.

DBCC CHECKDB

Laisser une réponse

Tout DBA de production qui se respecte exécute DBCC CHECKDB avant chaque sauvegarde de nuit.

La commande DBCC CHECKDB effectue différents contrôles sur une base de données afin de vérifier l’allocation, l’intégrité structurelle et l’intégrité logicielle de tous les objets de la base de données.

exemple :

DBCC CHECKDB WITH PHYSICAL_ONLY pour une vérification matériel de la base de données.
DBCC CHECKDB pour une vérification complète.

Sachant qu’il n’existe aucun bug pouvant endommager une base de données sql serveur, on peut généralement se limiter à la première option qui détecte les erreurs matérielle pouvant survenir sur la base. Attention, une corruption de base est un évènement dramatique qui doit être détecté au plus tôt afin d’être réparé.

En cas d’erreur sur la base, vous avez deux option:

La plus prudente, c’est de faire appel à une sauvegarde récente et de la restaurer… si l’erreur se reproduit fréquemment, mettez en doute votre matériel!

La deuxième solution, la moins bonne, c’est de réparer la base avec DBCC CHECKDB. Vous devez savoir que si vous procédez ainsi, vous allez perdre des données pour récupérer l’intégrité du fichier.
Pour cela, je vous renvoie à la documentation de référence : http://msdn.microsoft.com/fr-fr/library/ms176064.aspx

[SQL] Concours SQL Pro Рle publipostage (2 ̩toiles).

Laisser une réponse

Comme la date des résultats est dépassée et que je pense que le concours reste à titre pédagogique, je me permet de publier quelques résultats qui peuvent vous instruire.

Aujourd’hui, utilisation des CTE pour multiplie une ligne.

( Première Partie ) Problème N°2 Le publipostage. Pour info, j’ai cherche un moment comment multiplier des données avant de comprendre que c’était les CTE la solution.

Réponse :
Lire la suite →

[SQL] Problème n° 7 – le comptage ( 3 étoiles ).

2 réponses

Je vous encourage à répondre aux nombreux problèmes proposés par sql pro en sql : ici

Aujourd’hui, je vous propose de résoudre un problème par application des fonctions de fenêtrage et de ranking, nouvelle fonctionnalité de sql serveur 2005, dans le but de numéroter des lignes lors d’un SELECT.

Problème : http://sqlpro.developpez.com/Exercices/SQL_AZ_P1.html – Problème n° 7 – le comptage.

Solution :
Lire la suite →

[Concours Sql Pro] un dans dix ( 3 étoiles ).

1 réponse

Article publié avec l’autorisation de l’auteur sql pro.

Cet article est une solution donnée à un problème de joe selko que j’avais résolu en janvier 2007.

Enoncé : ici

Si vous le souhaitez, vous pouvez vous rendre sur le site de Sql Pro, vous cherchez une solution de votre coté et vous regardez si votre résultat est différent de la solution que je propose.
Lire la suite →

Restauration : Présentation générale.

Laisser une réponse

La plupart des opérations de restauration débutent par la re-création de la base de données à un instant précis, puis appliquent des sauvegardes ulterieures pour ramener la base de données à un point précis dans le temps.

exemple :

RESTORE DATABASE PUBS FROM DISK 'C:\DEMOPUBS.BAK' WITH REPLACE

WITH RECOVERY : la base de données est mise en ligne.
WITH NORECOVERY : la base de données reste fixée à RESTORING, on peux appliquer une nouvelle restauration sur la base.

Sauvegarde différentielle.

exemple :
RESTORE DATABASE PUBS FROM DISK ‘C:\DEMOPUBS_FULL.BAK‘ WITH NORECOVERY
RESTORE DATABASE PUBS FROM DISK ‘C:\DEMOPUBS_DIFF.BAK‘ WITH RECOVERY

Restauration d’une sauvegarde de journal de transaction.

Récupérer une base de données sans aucune perte de données serait plus facile si les problèmes arrivait juste apres une sauvegarde, avant meme que l’application n’est effectue une quelconque transaction. Malheureusement, nous n’avons pas cette chance. Aussi dans tout scénario catastrophe, le journal contient toujours des transactions qui n’ont pas ete sauvegarde.
c’est pour cette raison que la premiere etape de toute operation de recuperation doit toujours consister à emettre une derniere commande backup log. Ce processus capture toute transaction validée non encore sauvegarde et est sauvent nomme sauvegarde de queue de journal. comme vous pouvez executer une commande back up log sur une base de donnée meme en l’indisponibilite de tout fichier de donnees. La sauvegarde de la queue de journal est le dernier journal que vous restaurez dans un processus de restauration. Ainsi, vous ne perdez aucune données.

exemple :

RESTORE DATABASE AdventureWorks FROM DISK ‘c: estawf.bak’ WITH NORECOVERY
RESTORE LOG AdventureWorks FROM DISK ‘c: estaw1.trn’ WITH NORECOVERY
RESTORE LOG AdventureWorks FROM DISK ‘c: estaw2.trn’ WITH RECOVERY

sauvegarde.

Laisser une réponse

Merci à serge934 de la communauté developpez.com (http://www.developpez.net/forums/showthread.php?t=372323)

je te joins un script que j’utilise pour mes backups que j’ai mis dans une PSlancée par un travail

USE Master go
–drop table #BckDatabases
CREATE TABLE #BckDatabases ( databasename sysname)
SET nocount ON
declare @BckPath varchar(255)
declare @Prefix varchar(50)
declare @Extension varchar(10)
/****************************/
/* Paramétrage */
/****************************/
SET @BckPath = ‘chemin_backup’
SET @Prefix = cast(year(getdate()) AS varchar)+cast(month(getdate()) AS varchar)+cast(day(getdate()) AS varchar)
SET @Extension = ‘.bak’
INSERT INTO #BckDatabases values(‘base1′)
INSERT INTO #BckDatabases values(‘base2′) etc…
/****************************/
PRINT  »
PRINT ‘Liste des bases à Backuper :’
SELECT * FROM #BckDatabases
declare @CurrentDB sysname
declare @sql varchar(2000)
declare @fileName varchar(255)
SET nocount off
Declare curDB Cursor FOR
SELECT databasename FROM #BckDatabases
open curDBFetch
next FROM CurDB INTO @CurrentDB
while @@fetch_status = 0
begin SET @FileName = @bckPath +@Prefix +@CurrentDB + @Extension
PRINT ‘====================================================================’ PRINT ‘** ‘+@CurrentDB + ‘ Backup Started on ‘+Cast(GetDate() AS Varchar)
SET @sql = ‘BACKUP DATABASE ‘+@CurrentDB + ‘ TO DISK=N »’ + @filename +  » »
print @sql
exec (@sql)
PRINT ‘** ‘+@CurrentDB + ‘ Backup Ended on ‘+Cast(GetDate() AS Varchar)
PRINT ‘====================================================================’ PRINT  »
Fetch next FROM CurDB INTO @CurrentDB
end
deallocate CurDB
DROP TABLE #BckDatabases

tes sauvegardes s’appelleront
20070704_base1.bak
20070704_base2.bak
20070705_base1.bak
20070705_base2.bak
etc..
ensuite j’ai un travail qui supprime les fichiers dont la date est inférieure a 8 jours de la date du jour.

pour s’assurer que la base est bien sauvegarde, il faut :

tu peux le faire en faisant un « restore verifyonly » après ta sauvegarde. En fait ce n’est pas une restauration mais juste une verif.regardes
http://technet.microsoft.com/fr-fr/library/ms188902.aspx

quelques conseils:

le plan doit contenir:
-reindexations des tables les plus usitées => DBCC DBREINDEX …
-sauvegarde du journal de transaction ( et que lui !) après-test place dispo sur tes devices
-test des jobs qui pourraient se lancer pendant ta sauvegarde
-compactage SANS replacement en tête de fichier
-sauvegarde de tes bases COMPLETES (les differentielles plantent toujours a la restauration sauf chez oracle )
-test avec VERIFYONLY
-zip des .bak
-archivages des bak d’un coté et des zip d’un autre (sur un disque DIFFERENT)
PS: attention si tu es sous RAID5, ne garde que le dernier journal de transaction, supprime (del) les anciens.