Y3wBsjei

1-- INITIALIZING DATABASE
2USE master
3-- DROP DATABASE IF EXISTS EarlyInterventionC1;
4-- CREATE DATABASE EarlyInterventionC1;
5GO
6USE EarlyInterventionsC1;
7
8-- CREATING BASIC TABLES
9CREATE TABLE Impairment(
10    ImpairmentID INT NOT NULL IDENTITY PRIMARY KEY,
11    Impairment NVARCHAR(255) NOT NULL
12);
13
14CREATE TABLE Roles(
15    RoleID INT NOT NULL IDENTITY PRIMARY KEY,
16    Role NVARCHAR(30) NOT NULL,
17);
18
19CREATE TABLE DevelopmentDomain(
20    DevelopmentDomainID INT NOT NULL IDENTITY PRIMARY KEY,
21    DevelopmentDomain NVARCHAR(255) NOT NULL
22);
23
24CREATE TABLE Practitioner(
25    PractitionerID INT NOT NULL IDENTITY PRIMARY KEY,
26    Practitioner NVARCHAR(255) NOT NULL
27);
28
29CREATE TABLE Situation(
30    SituationID INT NOT NULL IDENTITY PRIMARY KEY,
31    Situation VARCHAR(255) NOT NULL
32);
33
34CREATE TABLE ProgramUser(
35    UserID INT NOT NULL IDENTITY PRIMARY KEY,
36    Email NVARCHAR(20) NOT NULL,
37    Password NVARCHAR(15) NOT NULL,
38    Role NVARCHAR(15) NOT NULL
39);
40
41CREATE TABLE Languages(
42    languageID INT NOT NULL IDENTITY PRIMARY KEY,
43    Language NVARCHAR(255) NOT NULL
44);
45
46CREATE TABLE TypeOfProgram (
47    TypeOfProgramID INT NOT NULL IDENTITY PRIMARY KEY,
48    TypeOfProgram NVARCHAR(255)
49);
50
51CREATE TABLE Age (
52    ageRangeID INT NOT NULL IDENTITY PRIMARY KEY,
53    ageRangeNumber INT
54);
55
56CREATE TABLE Program(
57    ProgramID INT NOT NULL IDENTITY PRIMARY KEY,
58    HiddenStatusID INT NOT NULL DEFAULT 0,
59    Title NVARCHAR(500) NOT NULL,
60    Author NVARCHAR(500) NOT NULL,
61    ISBN NVARCHAR(25),
62    Publisher NVARCHAR(75),
63    YearOfPublication INT,
64    PlaceOfPublication NVARCHAR(75),
65    AddressPublisher NVARCHAR(100),
66    PhoneNumberOfPublisher NVARCHAR(255),
67    CostOfProgram NVARCHAR(255),
68    WebsitePublisher NVARCHAR(500),
69    AgeRange NVARCHAR(255) NOT NULL,
70    InclusionCriteria NVARCHAR(255),
71    ExclusionCriteria NVARCHAR(255),
72    AlternativeTargetGroups NVARCHAR(255),
73    ScientificBasis NVARCHAR(3000),
74    UnderlyingTheory NVARCHAR(3000),
75    EmpiricallyTested NVARCHAR(1000),
76    Efficiency NVARCHAR(255),
77    TrainingProgram NVARCHAR(255),
78    ViewOnImpairment NVARCHAR(255),
79    ViewOnCare NVARCHAR(255),
80    ViewOnQualityOfCare NVARCHAR(255),
81    PortrayalOfMankind NVARCHAR(255),
82    BuiltInAssessment NVARCHAR(255),
83    ExternalAssessment NVARCHAR (255),
84    ConcreteExercise NVARCHAR(255),
85    GlobalDetailed NVARCHAR(255),
86    Comments NVARCHAR (2000),
87    Intensity NVARCHAR(255),
88    SuggestionStatus NVARCHAR(255),
89    UserID INT NOT NULL CONSTRAINT USER_FK REFERENCES ProgramUser(UserID)
90);
91
92-- CREATING COUPLETABLES
93
94
95
96CREATE TABLE ProgramSituation(
97    ProgramID INT,
98    SituationID INT,
99    CONSTRAINT ProgramSituation_PK PRIMARY KEY (ProgramID, SituationID),
100    CONSTRAINT Program_FKCS FOREIGN KEY (ProgramID) REFERENCES Program(ProgramID) ON DELETE CASCADE,
101    CONSTRAINT Situation_FKC FOREIGN KEY (SituationID) REFERENCES Situation(SituationID)ON DELETE CASCADE,
102);
103
104CREATE TABLE ProgramLanguage(
105    ProgramID INT,
106    LanguageID INT,
107    CONSTRAINT ProgramLanguage_PK PRIMARY KEY (ProgramID, LanguageID),
108    CONSTRAINT Program_FKCL FOREIGN KEY (ProgramID) REFERENCES Program(ProgramID)ON DELETE CASCADE,
109    CONSTRAINT Language_FKC FOREIGN KEY (LanguageID) REFERENCES Languages(LanguageID) ON DELETE CASCADE,
110);
111
112
113CREATE TABLE ProgramDevelopmentDomain(
114    ProgramID INT,
115    DevelopmentDomainID INT,
116    CONSTRAINT ProgramDevelopmentDomain_PK PRIMARY KEY (ProgramID, DevelopmentDomainID),
117    CONSTRAINT Program_FKCD FOREIGN KEY (ProgramID) REFERENCES Program(ProgramID) ON DELETE CASCADE,
118    CONSTRAINT DevelopmentDomain_FKC FOREIGN KEY (DevelopmentDomainID) REFERENCES DevelopmentDomain(DevelopmentDomainID) ON DELETE CASCADE,
119);
120
121CREATE TABLE ProgramPractitioner(
122    ProgramID INT,
123    PractitionerID INT,
124    CONSTRAINT ProgramPractitioner_PK PRIMARY KEY (ProgramID, PractitionerID),
125    CONSTRAINT Program_FKCP FOREIGN KEY (ProgramID) REFERENCES Program(ProgramID)ON DELETE CASCADE,
126    CONSTRAINT Practitioner_FKC FOREIGN KEY (PractitionerID) REFERENCES Practitioner(PractitionerID)ON DELETE CASCADE,
127);
128
129CREATE TABLE ProgramImpairment(
130    ProgramID INT,
131    ImpairmentID INT,
132    CONSTRAINT ProgramDisability_PK PRIMARY KEY (ProgramID, ImpairmentID),
133    CONSTRAINT Program_FKCDI FOREIGN KEY (ProgramID) REFERENCES Program(ProgramID) ON DELETE CASCADE,
134    CONSTRAINT Impairment_FKC FOREIGN KEY (ImpairmentID) REFERENCES Impairment(ImpairmentID) ON DELETE CASCADE,
135);
136
137CREATE TABLE ProgramTypeOfProgram (
138    ProgramID INT,
139    TypeOfProgramID INT,
140    CONSTRAINT ProgramTypeOfProgram_PK PRIMARY KEY (ProgramID, TypeOfProgramID),
141    CONSTRAINT Program_FK FOREIGN KEY (ProgramID) REFERENCES Program(ProgramID) ON DELETE CASCADE,
142    CONSTRAINT TypeOfProgram_FK FOREIGN KEY (TypeOfProgramID) REFERENCES TypeOfProgram(TypeOfProgramID) ON DELETE CASCADE,
143)