Telechargé par Karim Elhalloumi

RapportSQLServercomplet

publicité
ABDELKARIM ELHALLOUMI
OMAR SALIMI
Table des matières
(gras centré caractères 24 Times new roman)
.
.
Partie 1. Contexte général du projet
1.1 Cahier des charges
1.2 Diagramme de classes
Partie 2. Manipulation de la base de données SQLServer en PHP
2.1 Passage du Diagramme de classes au modèle relationnel
.
.
.
.
2.1.1 Présentation de l’outil utilisé
.
2.1.2 Présentation du script de la base de données
.
2.2 Connexion à une base de données SQL server à partir du PHP
.
2.2.1 configuration de l’outil utilisé pour assurer la connexion
.
2.2.2 Présentation du script de connexion
.
2.3 Exemple d’opérations CRUD SQLServer/PHP
.
2.3.1 Présentation de la table choisie
.
2.3.2 Présentation des scripts CRUD
.
Références
Annexes
.
.
Partie 1. Contexte général du projet
1.1 Cahier des charges
Notre Mission dans le cadre de ce projet est de créer une application
permettant de gérer les consultation des patients et genere un
oradanance par web il s'agit de définir les responsabilités de la gestion,
mettre à jour les données, organiser des données collectées auprès du
secrétariat afin de concevoir des fichiers de bases pour le Médecin , de
renforcer le contrôle et la confrontation, assurer une meilleure gestion
médicale et une cohérence de l'information et enfin faciliter le travail des
responsables.
Notre application aura comme principale fonctionnalités :
v Gestion et Suivi du Dossier Médical (détaillé)
v Gestion du Fiche Patients.
1.2 Diagramme de classes
1
Partie 2. Manipulation de la base de données SQLServer en PHP
2.1 Passage du Diagramme de classes au modèle relationnel
2.1.1 Génération du script de la base de données
CREATE Database [DiagnosticLabDb];
GO
USE [DiagnosticLabDb]
/****** Object: Table [dbo].[cc_and_oe] Script Date: 6/29/2020 8:23:09 AM
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cc_and_oe](
[ccoe_id] [int] IDENTITY(1,1) NOT NULL,
[cc] [varchar](999) NOT NULL,
[oe] [varchar](999) NOT NULL,
[patient_id] [int] NOT NULL,
2
[status] [int] NULL,
CONSTRAINT [PK__cc_and_o__B878B649AB7FF6E5] PRIMARY KEY CLUSTERED
(
[ccoe_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[diag_symptoms] Script Date: 6/29/2020 8:23:09
AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[diag_symptoms](
[dg_id] [int] IDENTITY(1,1) NOT NULL,
[patient_id] [int] NOT NULL,
[symptom_id] [int] NOT NULL,
[symptom_time] [varchar](10) NOT NULL,
CONSTRAINT [PK__diag_sym__4553E013C0A93B92] PRIMARY KEY CLUSTERED
(
[dg_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[diagnosis_diseases] Script Date: 6/29/2020
8:23:09 AM ******/
SET ANSI_NULLS ON
GO
3
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[diagnosis_diseases](
[id] [int] IDENTITY(1,1) NOT NULL,
[patient_id] [int] NOT NULL,
[disease_name] [varchar](50) NOT NULL,
[disease_id] [int] NOT NULL,
CONSTRAINT [PK__diagnosi__3213E83F14DAD400] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[disease] Script Date: 6/29/2020 8:23:09 AM
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[disease](
[disease_id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](255) NOT NULL,
[description] [varchar](max) NOT NULL,
CONSTRAINT [PK__disease__15627065BC935059] PRIMARY KEY CLUSTERED
(
[disease_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
4
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[doctor] Script Date: 6/29/2020 8:23:09 AM
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[doctor](
[id] [int] IDENTITY(1,1) NOT NULL,
[username] [varchar](255) NOT NULL,
[password] [varchar](200) NOT NULL,
[email] [varchar](255) NOT NULL,
[create_at] [datetime2](0) NOT NULL,
[lock_user] [int] NULL,
CONSTRAINT [PK__doctor__3213E83F88A5DD57] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[doctor_infos] Script Date: 6/29/2020 8:23:09 AM
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[doctor_infos](
5
[doctor_id] [int] NOT NULL,
[doctor_name] [varchar](255) NOT NULL,
[adress] [varchar](255) NOT NULL,
[expertise] [varchar](255) NOT NULL,
[chamber_name] [varchar](255) NOT NULL)
CREATE UNIQUE INDEX index_doctor_id
ON [dbo].[doctor_infos] (doctor_id);
GO
/****** Object: Table [dbo].[medicines] Script Date: 6/29/2020 8:23:09 AM
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[medicines](
[medicine_id] [int] IDENTITY(1,1) NOT NULL,
[medicine_name] [varchar](255) NOT NULL,
[medicine_group] [varchar](255) NOT NULL,
[power] [int] NOT NULL,
[medicine_type] [varchar](255) NOT NULL,
[company_name] [varchar](255) NOT NULL,
CONSTRAINT [PK__medicine__E7148EBB1646909B] PRIMARY KEY CLUSTERED
(
[medicine_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
6
/****** Object: Table [dbo].[note] Script Date: 6/29/2020 8:23:09 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[note](
[patient_id] [int] NOT NULL,
[note] [varchar](max) NOT NULL
)
CREATE UNIQUE INDEX index_patient_id
ON [dbo].[note] (patient_id);
GO
/****** Object: Table [dbo].[prescribed_medicine] Script Date: 6/29/2020
8:23:09 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[prescribed_medicine](
[pm_id] [int] IDENTITY(1,1) NOT NULL,
[patient_id] [int] NOT NULL,
[medicine_id] [int] NOT NULL,
[medicine_time] [varchar](20) NOT NULL,
CONSTRAINT [PK__prescrib__26B10336955AC899] PRIMARY KEY CLUSTERED
(
[pm_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
7
GO
/****** Object: Table [dbo].[prescriptions] Script Date: 6/29/2020 8:23:09 AM
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[prescriptions](
[prescription_id] [int] IDENTITY(1,1) NOT NULL,
[medicine_id] [int] NOT NULL,
[symptom_id] [int] NOT NULL,
[patient_id] [int] NOT NULL,
[medicine_time] [varchar](20) NOT NULL,
[disease_id] [int] NOT NULL,
[note] [varchar](max) NOT NULL,
[prescription_date] [datetime2](0) NOT NULL,
CONSTRAINT [PK__prescrip__3EE444F8B3372199] PRIMARY KEY CLUSTERED
(
[prescription_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[symptoms] Script Date: 6/29/2020 8:23:09 AM
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
8
CREATE TABLE [dbo].[symptoms](
[symptom_id] [int] IDENTITY(1,1) NOT NULL,
[symptom_name] [varchar](255) NOT NULL,
[symptom_type] [varchar](255) NOT NULL,
[description] [varchar](255) NOT NULL,
CONSTRAINT [PK__symptoms__7A85ADB834E5B77E] PRIMARY KEY CLUSTERED
(
[symptom_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[users] Script Date: 6/29/2020 8:23:09 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[users](
[user_id] [int] IDENTITY(1,1) NOT NULL,
[firstName] [varchar](255) NOT NULL,
[lastName] [varchar](200) NOT NULL,
[age] [int] NOT NULL,
[gender] [varchar](20) NOT NULL,
[email] [varchar](255) NOT NULL,
[contact] [varchar](20) NOT NULL,
[password] [varchar](255) NOT NULL,
[blood_group] [varchar](20) NOT NULL,
[address] [varchar](999) NOT NULL,
[user_type] [varchar](20) NOT NULL,
9
[lock_user] [int] NULL,
CONSTRAINT [PK__users__B9BE370F138A85B7] PRIMARY KEY CLUSTERED
(
[user_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'ABC',
N'DEF', 3, 0)
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'Foods',
N'Medicines', 1, 1)
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'Foods',
N'Foods', 2, 0)
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'Food 2',
N'Medicine 2', 3, 0)
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'A', N'A', 2,
0)
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'A', N'A', 2,
0)
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'A', N'A', 2,
1)
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'Abc',
N'Def', 3, 0)
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'New',
N'New', 3, 1)
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES (
N'FeverrnCold', N'Total Bed Rest', 11, 1)
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'ddd',
N'dddd', 12, 1)
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'dddd',
N'dddd', 18, 0)
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'ddd',
N'ddd', 18, 0)
10
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'ddd',
N'dddd', 18, 0)
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'ddd',
N'ddd', 18, 0)
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'ddd',
N'ddd', 18, 0)
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'ddd',
N'dddd', 17, 1)
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES ( N'ddd',
N'dddd', 18, 0)
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES (
N'dddddddddddd', N'dddddddddddd', 18, 0)
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES (
N'dddddddddddddddddddddddddddddddddddddddddddddddddd',
N'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd',
18, 0)
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES (
N'dddddddddddddd', N'ddddddddddddddddd', 18, 0)
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES (
N'dddddddddd', N'ddddddddddddddd', 18, 0)
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES (
N'dddddddddd', N'dddddddddddd', 18, 0)
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES (
N'dddddddd', N'ddddddddd', 18, 0)
INSERT [dbo].[cc_and_oe] ( [cc], [oe], [patient_id], [status]) VALUES (
N'kkkkkkkkkkkkkk', N'kkkkkkkkkkkkkkkk', 18, 1)
Go
INSERT [dbo].[diag_symptoms] ( [patient_id], [symptom_id], [symptom_time])
VALUES ( 1, 1, N'10:26:38')
INSERT [dbo].[diag_symptoms] ( [patient_id], [symptom_id], [symptom_time])
VALUES ( 1, 20, N'09:48:04')
INSERT [dbo].[diag_symptoms] ( [patient_id], [symptom_id], [symptom_time])
VALUES ( 1, 21, N'07:45:35')
11
Go
INSERT [dbo].[diagnosis_diseases] ( [patient_id], [disease_name], [disease_id])
VALUES ( 1, N'Skin Elergy', 0)
INSERT [dbo].[diagnosis_diseases] ( [patient_id], [disease_name], [disease_id])
VALUES ( 1, N'Diabtiease', 0)
INSERT [dbo].[diagnosis_diseases] ( [patient_id], [disease_name], [disease_id])
VALUES ( 1, N'Abdominal Aortic Aneurysm', 0)
INSERT [dbo].[diagnosis_diseases] ( [patient_id], [disease_name], [disease_id])
VALUES ( 1, N'Cancer', 0)
INSERT [dbo].[diagnosis_diseases] ( [patient_id], [disease_name], [disease_id])
VALUES ( 1, N'Blood Presure', 0)
INSERT [dbo].[diagnosis_diseases] ( [patient_id], [disease_name], [disease_id])
VALUES ( 1, N'Head Pain', 12)
Go
INSERT [dbo].[disease] ( [name], [description]) VALUES (N'Skin Elergy', N'asde')
INSERT [dbo].[disease] ( [name], [description]) VALUES (N'Skin Elergy', N'')
INSERT [dbo].[disease] ( [name], [description]) VALUES (N'Diabtiease', N'')
INSERT [dbo].[disease] ( [name], [description]) VALUES (N'Blood Presure', N'')
INSERT [dbo].[disease] ( [name], [description]) VALUES (N'Abdominal Aortic
Aneurysm', N'Abdominal Aortic Aneurysm')
INSERT [dbo].[disease] ( [name], [description]) VALUES (N'Abdominal Cramps
(Heat Cramps)', N'Abdominal Cramps (Heat Cramps)')
INSERT [dbo].[disease] ( [name], [description]) VALUES (N'Back Pain (Low Back
Pain)', N'Back Pain (Low Back Pain)')
INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'Barium Enema',
N'Barium Enema')
INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'COld', N'')
INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'Head Pain', N'')
INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'Fever', N'dfsdf')
INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'cancer', N'')
INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'swine flu h1 n1', N'')
INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'Diabities ss', N'')
12
INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'aaaaaaaaaaaaaaaaaaa',
N'18')
INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'fffffffffffffffff', N'18')
INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'df', N'18')
INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'Cancer', N' Cancer
new')
INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'Cancer', N' Cancer
new')
INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'Cancer1', N' Cancer1')
INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'Cancer1', N' Cancer1')
INSERT [dbo].[disease] ( [name], [description]) VALUES ( N'Diabities New', N'
Diabities New')
Go
INSERT [dbo].[doctor] ( [username], [password], [email], [create_at], [lock_user])
VALUES ( N'root', N'dd', N'[email protected]', CAST(N'2020-0621T23:50:18.0000000' AS DateTime2), NULL)
INSERT [dbo].[doctor] ( [username], [password], [email], [create_at], [lock_user])
VALUES ( N'dd',
N'$2y$10$ShY.QyAUMcAE2MiyJ.GvQO2SapvPoI6fYnm6yzy0k.0I1qzx7yoJS',
N'[email protected]', CAST(N'2020-06-21T23:59:12.0000000' AS DateTime2), NULL)
INSERT [dbo].[doctor] ( [username], [password], [email], [create_at], [lock_user])
VALUES ( N'dd',
N'$2y$10$xvkh1p5WVg548eU9v3THXOdfA4KS4XrHy8TQvwYp4eqT5C/l6rmk.',
N'[email protected]', CAST(N'2020-06-22T00:03:58.0000000' AS DateTime2), NULL)
INSERT [dbo].[doctor] ( [username], [password], [email], [create_at], [lock_user])
VALUES ( N'dd',
N'$2y$10$a2jtN0Fz4mFxv6Bo5rbnfu0Ke87XWAFwxKLoAHlo2QtqcvL9r6e9a',
N'[email protected]', CAST(N'2020-06-22T00:04:07.0000000' AS DateTime2),
NULL)
INSERT [dbo].[doctor] ( [username], [password], [email], [create_at], [lock_user])
VALUES ( N'aa',
N'$2y$10$bMrZd7DarE.XBNOCaPrrNuYMz6nyh23RKaEncXQtc2KKSg85BQeD.',
N'[email protected]', CAST(N'2020-06-22T00:54:37.0000000' AS DateTime2), NULL)
INSERT [dbo].[doctor] ( [username], [password], [email], [create_at], [lock_user])
VALUES ( N'adi',
N'$2y$10$zfddj9XUO1fBqFlHxewx9OD.dltSNSYTcgZScqwW3mrYvoedGv9/i',
N'[email protected]', CAST(N'2020-06-23T19:08:41.0000000' AS DateTime2), NULL)
13
Go
INSERT [dbo].[doctor_infos] ([doctor_id], [doctor_name], [adress], [expertise],
[chamber_name]) VALUES (1, N'A.Elhalloumi', N'Gueliz,residence rihana app 222
marrakech', N'Pediatric - Child', N'Central Hospital Limited')
Go
INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power],
[medicine_type], [company_name]) VALUES (N'Momento', N'Pain', 500, N'Syrup',
N'Square')
INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power],
[medicine_type], [company_name]) VALUES (N'Contain', N'Cold', 500, N'Tablet',
N'Square')
INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power],
[medicine_type], [company_name]) VALUES (N'Fixal', N'Cold', 180, N'Tablet',
N'Square')
INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power],
[medicine_type], [company_name]) VALUES (N'Panadol', N'paracitamol bp and
Caffein', 500, N'Tablet', N'Panadol')
INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power],
[medicine_type], [company_name]) VALUES (N'ddddddd', N'ddddd', 500, N'',
N'dd')
INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power],
[medicine_type], [company_name]) VALUES (N'ddddddd', N'd', 400, N'Tablet',
N'd')
INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power],
[medicine_type], [company_name]) VALUES (N'dddddkkkkkk', N'dddddddkkkkkk',
400, N'Syrup', N'dddd')
INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power],
[medicine_type], [company_name]) VALUES (N'ff', N'ff', 500, N'Tablet', N'ff')
INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power],
[medicine_type], [company_name]) VALUES (N'ddddddd', N'ddd', 500, N'', N'd')
INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power],
[medicine_type], [company_name]) VALUES ( N'ddddddd', N'ddd', 500, N'', N'd')
INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power],
[medicine_type], [company_name]) VALUES ( N'ddd', N'ddd', 500, N'Tablet', N'd')
14
INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power],
[medicine_type], [company_name]) VALUES ( N'ddd', N'ddd', 500, N'Tablet', N'd')
INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power],
[medicine_type], [company_name]) VALUES ( N'ka', N'ka', 20, N'Drop', N'ka')
INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power],
[medicine_type], [company_name]) VALUES ( N'ka', N'ka', 20, N'Drop', N'ka')
INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power],
[medicine_type], [company_name]) VALUES ( N'Disprine', N'Disprine', 0, N'Tablet',
N'Disprine')
INSERT [dbo].[medicines] ( [medicine_name], [medicine_group], [power],
[medicine_type], [company_name]) VALUES ( N'Disprine', N'Disprine', 0, N'Tablet',
N'Disprine')
Go
INSERT [dbo].[note] ([patient_id], [note]) VALUES (1, N'Walk Every Morning')
Go
INSERT [dbo].[prescribed_medicine] ( [patient_id], [medicine_id],
[medicine_time]) VALUES (11, 1, N'1-1-1')
INSERT [dbo].[prescribed_medicine] ( [patient_id], [medicine_id],
[medicine_time]) VALUES (18, 1, N'1-1-1')
INSERT [dbo].[prescribed_medicine] ( [patient_id], [medicine_id],
[medicine_time]) VALUES (18, 2, N'1-1-1')
INSERT [dbo].[prescribed_medicine] ( [patient_id], [medicine_id],
[medicine_time]) VALUES ( 1, 1, N'1-1-1')
INSERT [dbo].[prescribed_medicine] ( [patient_id], [medicine_id],
[medicine_time]) VALUES ( 1, 13, N'1-1-1')
Go
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES (2, 1, 1, N'0-10', 0, N'', CAST(N'2016-08-21T08:02:27.0000000' AS DateTime2))
15
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES (1, 2, 1, N'0-01', 0, N'', CAST(N'2016-08-21T08:05:30.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES (3, 3, 3, N'1-01', 0, N'', CAST(N'2016-08-21T08:39:54.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES (2, 0, 3, N'1-11', 0, N'', CAST(N'2016-08-21T08:44:48.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES (4, 0, 1, N'0-10', 0, N'', CAST(N'2016-08-24T11:18:38.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES (3, 0, 1, N'0-01', 0, N'', CAST(N'2016-08-24T11:19:04.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 1, 0, 1, N'1-11', 0, N'', CAST(N'2016-08-24T11:19:26.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 2, 0, 3, N'1-01', 0, N'', CAST(N'2016-08-24T11:48:39.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 2, 0, 3, N'1-01', 0, N'', CAST(N'2016-08-24T11:50:09.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 4, 0, 6, N'1-01', 0, N'', CAST(N'2016-08-24T11:53:51.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 2, 0, 1, N'0-10', 0, N'', CAST(N'2016-09-02T07:00:26.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 3, 0, 1, N'1-01', 0, N'', CAST(N'2016-09-02T07:01:15.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 2, 0, 1, N'1-11', 0, N'', CAST(N'2016-09-02T07:03:26.0000000' AS DateTime2))
16
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 2, 0, 1, N'1-00', 0, N'', CAST(N'2016-09-02T07:09:00.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 4, 0, 2, N'1-00', 0, N'', CAST(N'2016-09-02T07:09:00.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 1, 0, 2, N'1-00', 0, N'', CAST(N'2016-09-02T07:09:00.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 2, 0, 3, N'1-01', 0, N'', CAST(N'2016-09-02T09:55:58.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 3, 0, 3, N'1-11', 0, N'', CAST(N'2016-09-02T09:55:58.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 4, 0, 11, N'11-1', 0, N'', CAST(N'2017-04-09T10:29:41.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 1, 0, 11, N'10-1', 0, N'', CAST(N'2017-04-09T10:29:41.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 2, 0, 11, N'01-0', 0, N'', CAST(N'2017-04-09T10:29:41.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 1, 1, 1, N'1-11', 0, N'', CAST(N'2020-06-27T21:35:27.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 13, 21, 1, N'11-1', 0, N'', CAST(N'2020-06-27T21:35:27.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 1, 0, 1, N'1-11', 0, N'', CAST(N'2020-06-27T21:37:15.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 13, 0, 1, N'11-1', 0, N'', CAST(N'2020-06-27T21:37:15.0000000' AS DateTime2))
17
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 0, 1, 1, N'1-11', 0, N'', CAST(N'2020-06-27T21:37:15.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 0, 21, 1, N'11-1', 0, N'', CAST(N'2020-06-27T21:37:15.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 0, 20, 1, N'11-1', 0, N'', CAST(N'2020-06-27T21:37:15.0000000' AS DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 1, 1, 1, N'1-11', 0, N'Walk Every Morning', CAST(N'2020-06-28T16:13:01.0000000' AS
DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 1, 1, 1, N'1-11', 0, N'Walk Every Morning', CAST(N'2020-06-28T16:16:07.0000000' AS
DateTime2))
INSERT [dbo].[prescriptions] ( [medicine_id], [symptom_id], [patient_id],
[medicine_time], [disease_id], [note], [prescription_date]) VALUES ( 1, 1, 1, N'1-11', 12, N'Walk Every Morning', CAST(N'2020-06-28T16:18:30.0000000' AS
DateTime2))
Go
INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description])
VALUES (N'Cancer aaa', N'danger', N'dssskh')
INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description])
VALUES (N'Swine Flu', N'Meduim', N'dd')
INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description])
VALUES (N'Skin Elergy', N'3', N'')
INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description])
VALUES (N'Diabtiease', N'0', N'')
INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description])
VALUES (N'Blood Presure', N'0', N'')
INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description])
VALUES (N'Abdominal Aortic Aneurysm', N'0', N'Abdominal Aortic Aneurysm')
18
INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description])
VALUES (N'Abdominal Cramps (Heat Cramps)', N'0', N'Abdominal Cramps (Heat
Cramps)')
INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description])
VALUES (N'Back Pain (Low Back Pain)', N'0', N'Back Pain (Low Back Pain)')
INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description])
VALUES ( N'Barium Enema', N'0', N'Barium Enema')
INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description])
VALUES ( N'COld', N'0', N'')
INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description])
VALUES ( N'Head Pain', N'0', N'')
INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description])
VALUES ( N'Fever', N'0', N'dfsdf')
INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description])
VALUES ( N'cancer', N'0', N'')
INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description])
VALUES ( N'swine flu h1 n1', N'0', N'')
INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description])
VALUES ( N'Diabities ss', N'0', N'')
INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description])
VALUES ( N'ffffffffff', N'0', N'18')
INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description])
VALUES ( N'fd', N'0', N'18')
INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description])
VALUES ( N'Tyfide', N'Meduim', N'Tyfide')
INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description])
VALUES ( N'Tyfide', N'Meduim', N'Tyfide')
INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description])
VALUES ( N'Tyfide new', N'Common', N'Tyfide new')
INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description])
VALUES ( N'Tyfide new', N'Common', N'Tyfide new')
INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description])
VALUES ( N'asde', N'Common ', N' asde')
INSERT [dbo].[symptoms] ( [symptom_name], [symptom_type], [description])
VALUES ( N'asde', N'Common ', N' asde')
19
Go
INSERT [dbo].[users] ( [firstName], [lastName], [age], [gender], [email], [contact],
[password], [blood_group], [address], [user_type], [lock_user]) VALUES ( N'karim',
N'elhalloumi', 23, N'male', N'[email protected]', N'+8801516782104',
N'admin', N'AB+', N'marrakech', N'patient', NULL)
INSERT [dbo].[users] ( [firstName], [lastName], [age], [gender], [email], [contact],
[password], [blood_group], [address], [user_type], [lock_user]) VALUES ( N'dd',
N'dd', 15, N'male', N'[email protected]', N'9849849884984', N'dd', N'A+', N'sss',
N'patient', NULL)
ALTER TABLE [dbo].[cc_and_oe] ADD CONSTRAINT
[DF__cc_and_oe__statu__36B12243] DEFAULT ((0)) FOR [status]
GO
ALTER TABLE [dbo].[doctor] ADD CONSTRAINT
[DF__doctor__create_a__3C69FB99] DEFAULT (getdate()) FOR [create_at]
GO
ALTER TABLE [dbo].[doctor] ADD CONSTRAINT
[DF__doctor__lock_use__3D5E1FD2] DEFAULT (NULL) FOR [lock_user]
GO
ALTER TABLE [dbo].[prescriptions] ADD CONSTRAINT
[DF__prescript__presc__4316F928] DEFAULT (getdate()) FOR [prescription_date]
GO
ALTER TABLE [dbo].[users] ADD CONSTRAINT
[DF__users__lock_user__46E78A0C] DEFAULT (NULL) FOR [lock_user]
GO
ALTER TABLE [dbo].[disease] WITH CHECK ADD CONSTRAINT
[CK__disease__disease__3A81B327] CHECK (([disease_id]>(0)))
GO
ALTER TABLE [dbo].[disease] CHECK CONSTRAINT
[CK__disease__disease__3A81B327]
GO
ALTER TABLE [dbo].[symptoms] WITH CHECK ADD CONSTRAINT
[CK__symptoms__sympto__44FF419A] CHECK (([symptom_id]>(0)))
20
GO
ALTER TABLE [dbo].[symptoms] CHECK CONSTRAINT
[CK__symptoms__sympto__44FF419A]
GO
2.1.2 Présentation du script de la base de données
-Microsoft SQL Server Management (pour les traitment et generation de script)
2.2 Connexion à une base de données SQL server à partir du PHP
2.2.1 configuration de l’outil utilisé pour assurer la connexion
21
-Microsoft visual sudio :
2.2.2 Présentation du script de connexion
Connection.php
<?php
$serverName = "DESKTOP-SHOHAG";
$connectionInfo = array( "Database"=>"DiagnosticLabDb", "UID"=>"sa",
"PWD"=>"XXXXX");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn
echo
}else{
echo
die(
}
?>
) {
"Connection established.<br />";
"Connection could not be established.<br />";
print_r( sqlsrv_errors(), true));
22
2.3 Exemple d’opérations CRUD SQLServer/PHP
INSERT INTO note (patient_id, note) VALUES (4, 'Their is no critical conditions')
UPDATE note SET note='Updated Notes Here..' WHERE patient_id=4"
"SELECT user_id, firstname, lastname, age FROM users"
"DELETE note WHERE patient_id=4";
2.3.1 Présentation de la table choisie
23
2.3.2 Présentation des scripts CRUD
Insert.php
<?php
$serverName = "DESKTOP-SHOHAG";
$connectionInfo = array( "Database"=>"DiagnosticLabDb", "UID"=>"sa", "PWD"=>"XXXX
X");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
$tsql = "INSERT INTO note (patient_id, note) VALUES (101, 'Nothing to be serious'
)";
/* Execute the query. */
$stmt = sqlsrv_query( $conn, $tsql);
if ($stmt)
{
echo "Inserted Successfully.<br>\n";
}
else
{
echo "Error in statement execution.\n";
die( print_r( sqlsrv_errors(), true));
}
/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>
Select.php
<?php
$serverName = "DESKTOP-SHOHAG";
$connectionInfo = array( "Database"=>"DiagnosticLabDb", "UID"=>"sa", "PWD"=>"XXXX
X");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
$tsql = "SELECT user_id, firstname, lastname, age FROM users";
/* Execute the query. */
$stmt = sqlsrv_query( $conn, $tsql);
24
if ( $stmt )
{
echo "Statement executed.<br>\n";
}
else
{
echo "Error in statement execution.\n";
die( print_r( sqlsrv_errors(), true));
}
/* Iterate through the result set printing a row of data upon each iteration.*/
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_NUMERIC))
{
echo "Col1: ".$row[0]."\n";
echo "Col2: ".$row[1]."\n";
echo "Col3: ".$row[2]."<br>\n";
echo "-----------------<br>\n";
}
/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>
Update.php
<?php
$serverName = "DESKTOP-SHOHAG";
$connectionInfo = array( "Database"=>"DiagnosticLabDb", "UID"=>"sa", "PWD"=>"XXXX
X");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
$tsql = "UPDATE
note SET note='Updated Notes Here..' WHERE patient_id=101";
/* Execute the query. */
$stmt = sqlsrv_query( $conn, $tsql);
25
if ($stmt)
{
echo "Successfully Updated.<br>\n";
}
else
{
echo "Error in statement execution.\n";
die( print_r( sqlsrv_errors(), true));
}
/*connection closed. */
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>
Delete.php
<?php
$serverName = "DESKTOP-SHOHAG";
$connectionInfo = array( "Database"=>"DiagnosticLabDb", "UID"=>"sa", "PWD"=>"XXXX
X");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
$tsql = "DELETE note WHERE patient_id=101";
/* Execute the query. */
$stmt = sqlsrv_query( $conn, $tsql);
26
if ($stmt)
{
echo "Successfully DELETED.<br>\n";
}
else
{
echo "Error in statement execution.\n";
die( print_r( sqlsrv_errors(), true));
}
/*connection closed. */
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>
Références
https://stackoverflow.com/
https://github.com/
https://techcommunity.microsoft.com/t5/sql-server/bd-p/SQL_Server
Annexes
Projet complet encadré par Mr.Issam Qaffou :
https://we.tl/t-ihrwi5MKgo
Partie 2:
La Notion index:
Dans la vie courante, nous sommes entourés d'index : un code postal, le n° d'un immeuble dans une rue, les numéros de téléphone
sont des index. Autrefois, lorsque le téléphone fit son apparition (Clémenceau aurait dit du téléphone « Quoi ? On vous sonne
comme un laquais ??? ») il n'y avait pas de numéro. On agitait une manivelle qui avait pour effet de réveiller l'opératrice du
standard auquel on était physiquement relié. Puis on demandait à cette personne de nous connecter avec Monsieur le Marquis de
Carabas à Toledo. Il s'ensuivait un échange de bons procédés entre les opératrices des différents relais afin d'établir une liaison
physique entre ces deux clients. Puis vint une première numérotation… Elle était faite ville par ville. On vit alors Fernand
Raynaud dans son célèbre sketch demander désespérément le 22 à Asnières et n'obtenir que New York ! Dans les années 50,
l'automatique fit son apparition et l'on pouvait appeler en interurbain une personne en composant directement son numéro. Ainsi
le commissaire Maigret fut-il joignable à PELLEPORT 38 52. Puis l'automatique fut étendu à toutes les régions de France.
Depuis quelques années seulement on peut joindre n'importe qui sur terre avec un simple numéro. Mais les numéros se sont
allongés.Le mien, complet, est 00 33 6 11 86 40 66.
27
Remarquez la structure de ce numéro. Il commence par un double zéro qui indique que l'on va s'intéresser à l'international. Puis
le second groupe de chiffres indique le pays. Enfin le suivant indique la nature du réseau (ici le réseau de téléphones cellulaires).
Chacun des groupes de chiffres précise un peu plus les informations.
Une chose importante des index est leur structuration. On convient à l'évidence que si les numéros des immeubles étaient
distribués au hasard sur chaque bâtiment[1], il serait difficile (mais pas impossible) de trouver le 68 avenue des Champs Élysées.
En fait, il suffirait – au pire – de parcourir toute l'avenue pour trouver le bon bâtiment.
On comprend donc que l'organisation des données dans un index est primordiale pour accélérer les recherches. En fait, les
données d'un index sont systématiquement triées et les données stockées dans une structure particulière favorisant les
recherches : liste ordonnée ou arbre, le plus souvent.
Exemple:
d'index fait que l'information y est vectorisée. En effet, chaque colonne supplémentaire précise la colonne précédente. De ce fait,
la recherche dans un index multicolonne n'est accélérée que si les données cherchées sont un sous-ensemble ordonné du vecteur.
Voyons cela en termes pratiques à l'aide d'un exemple…
Exemple pratique :
Sélectionnez
CLI_NOM CLI_PRENOM CLI_DATE_NAISSANCE
--------- ----------- -----------------DUPONT Alain
21/01/1930
DUPONT Marcel
21/01/1930
DUPONT Marcel
01/06/1971
DUPONT Paul
21/01/1930
MARTIN Alain
11/02/1987
MARTIN Marcel
21/01/1930
...
Dans cet index composé d'un nom d'un prénom et d'une date de naissance, la recherche sera efficace pour les informations
suivantes :
•
•
•
CLI_NOM
CLI_NOM + CLI_PRENOM
CLI_NOM + CLI_PRENOM + CLI_DATE_NAISSANCE
En revanche, la recherche sur une seule colonne CLI_PRENOM ou CLI_DATE_NAISANCE et a fortiori sur ces deux colonnes
n'aura aucune efficacité du fait du tri relatif des colonnes entre elles (vectorisation)… En effet chercher « Paul » revient à parcourir
tout l'index (balayage ou scan) alors que chercher « MARTIN », revient à se placer très rapidement par dichotomie au bon endroit
(recherche ou seek).
28
29
Téléchargement
Explore flashcards