· 7 years ago · Sep 20, 2018, 06:24 PM
1CREATE DATABASE interviewdb;
2
3USE interviewdb;
4
5CREATE TABLE IF NOT EXISTS Account_Status (
6 `idStatus` VARCHAR(45) NOT NULL,
7 `Status_Name` VARCHAR(45) NOT NULL,
8 PRIMARY KEY (`idStatus`),
9 UNIQUE INDEX `idStatus_UNIQUE` (`idStatus` ASC) ,
10 UNIQUE INDEX `Status_Name_UNIQUE` (`Status_Name` ASC)
11 );
12
13 CREATE TABLE IF NOT EXISTS Account_Type (
14 `idTypes` VARCHAR(45) NOT NULL,
15 `Type_Name` VARCHAR(45) NOT NULL,
16 PRIMARY KEY (`idTypes`),
17 UNIQUE INDEX `idType_UNIQUE` (`idTypes` ASC)
18 );
19
20
21 CREATE TABLE IF NOT EXISTS Client_Account (
22 `idAccount` VARCHAR(45) NOT NULL,
23 `idStatus` VARCHAR(45) NOT NULL,
24 `Account_Name` VARCHAR(45) NOT NULL,
25 `idType` VARCHAR(45) NOT NULL,
26 PRIMARY KEY (`idAccount`),
27 UNIQUE INDEX `idAccounts_UNIQUE` (`idAccount` ASC) ,
28 INDEX `fk_Accounts_Status_idx` (`idStatus` ASC) ,
29 INDEX `fk_Accounts_AccountType1_idx` (`idType` ASC) ,
30 CONSTRAINT `fk_Accounts_Status`
31 FOREIGN KEY (`idStatus`)
32 REFERENCES Account_Status (`idStatus`),
33 CONSTRAINT `fk_Accounts_AccountType1`
34 FOREIGN KEY (`idType`)
35 REFERENCES Account_Type (`idTypes`)
36 );
37
38
39 CREATE TABLE IF NOT EXISTS Project (
40 `idProject` VARCHAR(45) NOT NULL,
41 `idAccount` VARCHAR(45) NOT NULL,
42 `Project_Name` VARCHAR(255),
43 PRIMARY KEY (`idProject`),
44 UNIQUE INDEX `idProjects_UNIQUE` (`idProject` ASC) ,
45 CONSTRAINT `fk_Projects_Accounts1`
46 FOREIGN KEY (`idAccount`)
47 REFERENCES Client_Account (`idAccount`)
48 );
49
50 CREATE TABLE IF NOT EXISTS Project_Team (
51 `Iteration` INT NOT NULL AUTO_INCREMENT,
52 `idProjects` VARCHAR(45) NOT NULL,
53 `Teams` INT NOT NULL,
54 PRIMARY KEY (`Iteration`),
55 INDEX `fk_Project_Team_Project1_idx` (`idProjects` ASC) ,
56 UNIQUE INDEX `Iteration_UNIQUE` (`Iteration` ASC) ,
57 CONSTRAINT `fk_Project_Team_Project1`
58 FOREIGN KEY (`idProjects`)
59 REFERENCES Project (`idProject`)
60 );
61
62CREATE TABLE IF NOT EXISTS Interview (
63 `idInterview` VARCHAR(45) NOT NULL,
64 `idProjects` VARCHAR(45) NOT NULL,
65 `Date_Created` DATETIME DEFAULT CURRENT_TIMESTAMP,
66 PRIMARY KEY (`idInterview`),
67 UNIQUE INDEX `idInterview_UNIQUE` (`idInterview` ASC) ,
68 INDEX `fk_Interview_Projects1_idx` (`idProjects` ASC) ,
69 CONSTRAINT `fk_Interview_Projects1`
70 FOREIGN KEY (`idProjects`)
71 REFERENCES Project (`idProject`)
72 );
73
74 CREATE TABLE IF NOT EXISTS Question (
75 `idQuestion` VARCHAR(45) NOT NULL ,
76 `Questions` VARCHAR(255) NOT NULL,
77 `Max_Value` INT NULL,
78 `idSection` INT NOT NULL,
79 PRIMARY KEY (`idQuestion`),
80 UNIQUE INDEX `idQuestions_UNIQUE` (`idQuestion` ASC)
81 );
82
83CREATE TABLE IF NOT EXISTS Response (
84 `idQuestions` VARCHAR(45) NOT NULL,
85 `Num_Response` INT NULL,
86 `Written_Response` VARCHAR(255) NULL,
87 `idInterview` VARCHAR(45) NOT NULL,
88 `Iteration` INT NOT NULL AUTO_INCREMENT,
89 INDEX `fk_Responses_Interview1_idx` (`idInterview` ASC) ,
90 INDEX `fk_Responses_Questions1_idx` (`idQuestions` ASC) ,
91 PRIMARY KEY (`Iteration`),
92 UNIQUE INDEX `Iteration_UNIQUE` (`Iteration` ASC) ,
93 CONSTRAINT `fk_Responses_Interview1`
94 FOREIGN KEY (`idInterview`)
95 REFERENCES Interview (`idInterview`),
96 CONSTRAINT `fk_Responses_Questions1`
97 FOREIGN KEY (`idQuestions`)
98 REFERENCES Question (`idQuestion`)
99 );
100
101Insert Into Account_Status (idStatus, Status_Name)
102 Values
103('AR123', 'Gold'),
104('KL645', 'Silver'),
105('JD987','Bronze');
106
107Insert Into Account_Type (idTypes, Type_Name)
108Values
109('MP23', 'Defense and Intelligence'),
110('DS75', 'Taxes'),
111('HH89', 'Relief');
112
113Insert Into Client_Account(idAccount , idStatus , Account_Name, idType)
114Values
115('A54','AR123','Army','MP23'),
116('T56','KL645','Navy','MP23'),
117('L78','JD987','FEMA','HH89'),
118('M46','JD987','IRS','DS75');
119
120Insert Into Project (idProject, idAccount)
121Values
122('SDM5','T56'),
123('LMN9','A54'),
124('RYQ6','L78'),
125('MLH0','T56');
126
127Insert Into Project_Team (Iteration,idProjects,Teams)
128Values
129('1','SDM5','3'),
130('2','SDM5','4'),
131('3','LMN9','2'),
132('4','LMN9','1'),
133('5','RYQ6','8'),
134('6','RYQ6','6'),
135('7','MLH0','5'),
136('8','MLH0','7');
137
138Insert Into Interview(idInterview, idProjects)
139Values
140('1','SDM5'),
141('2','SDM5'),
142('3','LMN9'),
143('4','MLH0'),
144('5','RYQ6'),
145('6','RYQ6');
146
147Insert Into Question(idQuestion, Questions,Max_Value,idSection)
148Values
149(1.1,'What is your overall impression of IBM?',5,1),
150(1.2,'Do you view IBM as a trusted delivery partner?',5,1),
151(1.3,'How well has IBM intergrated with your team?',5,1),
152(2.1,'How well does IBM understand your risks, problems, and issues?',5,2),
153(2.2,'Is IBM meeting your project delivery expectations?',5,2),
154(3.1,'How would you rate the Governance Process?',5,3),
155(3.2,'How would you rate the performance of IBMs key personnel?',5,3);
156
157
158Insert Into Response(idQuestions,Num_Response,Written_Response,idInterview)
159Values
160(1.1,5,'Very upstanding company.',5),
161(1.2,2,'',5),
162(1.3,3,'Fairly Well.',5),
163(2.1,1,'They have not been understanding at all',5),
164(2.2,3,'Fairly quickly there have been a few road blocks along the way.',5),
165(3.1,4,'',5),
166(3.2,4,'They have been very knowledgeable.',5),
167(1.1,1,'Horrible',2),
168(1.2,5,'They are a very honest',2),
169(1.3,5,'They have integrated seemlessly with my team.',2),
170(2.1,4,'They have been understanding of what we are looking for.',2),
171(2.2,4,'',2),
172(3.1,1,'I do not understand it',2),
173(3.2,3,'',2),
174(1.1,2,'Very upstanding company.',6),
175(1.2,4,'',6),
176(1.3,1,'Fairly Well.',6),
177(2.1,2,'They have not been understanding at all',6),
178(2.2,5,'Fairly quickly there have been a few road blocks along the way.',6),
179(3.1,2,'',6),
180(3.2,1,'They have been very knowledgeable.',6);