Créer les tables ci-dessus en précisant les clés primaires et les clés étrangères (Code_Module et Code_Filière sont générés automatiquement par le système, sauf que le premier est toujours paire et l’autre est impaire)
Ajouter les contraintes suivantes:
USE MASTER
CREATE DATABASE exercice 4
GO
--
--Creation du tables
USE TP4S1
CREATE TABLE Filiere (codeFiliere INT PRIMARY KEY IDENTITY(1,2), libelleFiliere VARCHAR(30))
CREATE TABLE Module (codeModule INT PRIMARY KEY IDENTITY(2,2), libelleFiliere VARCHAR(30))
CREATE TABLE Groupe (codeGroupe VARCHAR(10) PRIMARY KEY, annee INT, codeFiliere INT FOREIGN KEY REFERENCES Filiere(codeFiliere))
CREATE TABLE Stagiaire (codeStagiaire INT PRIMARY KEY, nomStg VARCHAR(10), prenomStg VARCHAR(10), dateNaiss DATE, telFixe VARCHAR(30), telPort VARCHAR(30), email VARCHAR(30),
codeGroupe VARCHAR(10) FOREIGN KEY REFERENCES Groupe(codeGroupe))
CREATE TABLE Note (codeStagiaire INT FOREIGN KEY REFERENCES Stagiaire(codeStagiaire), codeModule INT FOREIGN KEY REFERENCES Module(codeModule), note1 FLOAT, note2 FLOAT, note3 FLOAT,
PRIMARY KEY (codeStagiaire, codeModule))
GO
--
--Contrainte annee vaut 1 ou 2
ALTER TABLE Groupe
ADD CONSTRAINT ck_annee CHECK (annee=1 OR annee=2)
GO
--
-- Tél_Fixe commence par 05 et Tél_Portable par 06, le deux ne peuvent pas dépasser 10 chiffres.
ALTER TABLE Stagiaire
ADD CONSTRAINT ck_telFixe CHECK(telFixe LIKE '05________')
GO
--
ALTER TABLE Stagiaire
ADD CONSTRAINT ck_telPort CHECK(telPort LIKE '06________')
GO
--
--L’e-mail doit contenir @ et . "point"
ALTER TABLE Stagiaire
ADD CONSTRAINT ck_email_Stagiaire CHECK (email LIKE '%@%.%')
GO
--
-- note1, note2 et note3 sont comprises entre 0 et 20, s’elles ne sont pas remplies elles valent 0.
ALTER TABLE Note
ADD CONSTRAINT ck_note1 CHECK ( note1>=0 AND note1<=20)
ALTER TABLE Note
ADD CONSTRAINT df_note1 DEFAULT 0 for note1
GO
ALTER TABLE Note
ADD CONSTRAINT ck_note2 CHECK ( note2>=0 AND note2<=20)
ALTER TABLE Note
ADD CONSTRAINT df_note2 DEFAULT 0 for note2
GO
ALTER TABLE Note
ADD CONSTRAINT ck_note3 CHECK ( note3>=0 AND note3<=20)
ALTER TABLE Note
ADD CONSTRAINT df_note3 DEFAULT 0 for note3
--
--Ajouter une colonne Moyenne à la table Note qui vaut : (Note_1 + Note_2 + Note_3)/3
ALTER TABLE Note
ADD Moyenne AS (note1+note2+note3)/3
GO
--Les colonnes (Nom_Stgr, Prénom_Stgr, Date_Naissance) ne peuvent pas être, toutes les trois, redondantes.
ALTER TABLE Stagiaire
ADD CONSTRAINT uq_stagiaire UNIQUE (nomStg, prenomStg, dateNaiss)
GO
--Code_Groupe dépend de l’année, s’il s’agit d’un groupe de première année alors le code ressemble à G[un caractère entre A et H] et G[un chiffre entre 1 et 8].
ALTER TABLE Groupe
ADD CONSTRAINT ck_codeGroupe CHECK ( (annee=1 AND codeGroupe LIKE 'G[A-H]') OR (annee=2 AND codeGroupe LIKE 'H[1-8]'))
GO
Ajouter un commentaire
Veuillez vous connecter pour ajouter un commentaire.
Pas encore de commentaires.