· 8 years ago · Jul 01, 2018, 12:38 PM
1/*
2Howto:
3Just change the path of the two files pat.csv and pri.csv at lines 55 and 57.
4After that start MySQL and create a new database called what ever you like. And then move into the database and type source CreateDatabase.sql and your done!
5
6Example syntax:
7CREATE DATABASE 'WhatEver';
8USE WhatEver;
9SOURCE CreateDatabase.sql
10*/
11
12
13/* Drop tables */
14DROP TABLE IF EXISTS pat;
15DROP TABLE IF EXISTS pri;
16DROP TABLE IF EXISTS patient;
17DROP TABLE IF EXISTS primer;
18DROP TABLE IF EXISTS URL;
19DROP TABLE IF EXISTS virus;
20DROP TABLE IF EXISTS test;
21DROP TABLE IF EXISTS need;
22DROP TABLE IF EXISTS visit;
23DROP TABLE IF EXISTS users;
24DROP TABLE IF EXISTS security_question;
25DROP TABLE IF EXISTS result;
26
27/*Import excel sheets. Will create corresponding table and then load both .csv files.*/
28CREATE TABLE pat (
29 PatientNo int,
30 PatientFirstName VARCHAR(9),
31 SerialNo BIGINT(11),
32 DateofRecieved VARCHAR(10),
33 Sex VARCHAR(1),
34 AGE INT(2),
35 TelNo VARCHAR(14),
36 DoctorName VARCHAR(15),
37 SampleType VARCHAR(5),
38 TestRequired VARCHAR(11),
39 Country VARCHAR(6),
40 Result VARCHAR(21)
41);
42
43CREATE TABLE pri (
44 Primernumber int(3),
45 PrimerName VARCHAR(13),
46 Sequence VARCHAR(34),
47 Virus VARCHAR(6),
48 GenomicRegion VARCHAR(17),
49 ProductSize VARCHAR(5),
50 Link VARCHAR(99),
51 Location VARCHAR(13),
52 Comments VARCHAR(29)
53);
54
55LOAD DATA INFILE '/home/souliz/pat.csv' INTO TABLE pat
56 FIELDS TERMINATED BY ',' ENCLOSED BY '"';
57LOAD DATA INFILE '/home/souliz/pri.csv' INTO TABLE pri
58 FIELDS TERMINATED BY ',' ENCLOSED BY '"';
59
60/* Create and insert into tables */
61CREATE TABLE virus (
62 name VARCHAR(75) NOT NULL,
63 id INT AUTO_INCREMENT NOT NULL UNIQUE,
64 PRIMARY KEY (name)
65);
66INSERT INTO virus (name)
67 select virus from pri group by virus;
68
69CREATE TABLE patient (
70 name VARCHAR(75) NOT NULL,
71 gender VARCHAR(1) NOT NULL,
72 virus VARCHAR(75) NOT NULL,
73 sampleType varchar(75),
74 patientNo INT NOT NULL,
75 serialNo BIGINT NOT NULL,
76 PRIMARY KEY (serialNo),
77 FOREIGN KEY (virus) REFERENCES virus(name)
78);
79INSERT INTO patient
80 select patientfirstname, sex, testrequired,sampleType,patientNo, serialno from pat where NOT testrequired = 'Genotyping' AND NOT testrequired= ' Genotyping';
81
82CREATE TABLE primer (
83 name VARCHAR(75) NOT NULL,
84 sequence VARCHAR(75) NOT NULL,
85 number INT NOT NULL,
86 id INT NOT NULL AUTO_INCREMENT,
87 virus_id INT NOT NULL,
88 PRIMARY KEY (id),
89 FOREIGN KEY(virus_id) REFERENCES virus(id)
90);
91INSERT INTO primer(name,sequence,number,virus_id)
92 select p.primername, p.sequence, p.primernumber,v.id FROM pri as p, virus as v where v.name = p.virus group by p.virus;
93
94CREATE TABLE URL (
95 id INT NOT NULL AUTO_INCREMENT,
96 url VARCHAR(200) NOT NULL,
97 virus_id INT NOT NULL,
98 PRIMARY KEY (id),
99 FOREIGN KEY(virus_id) REFERENCES virus(id)
100);
101INSERT INTO URL (url, virus_id)
102 select p.link, v.id from pri as p, virus as v where v.name=p.virus group by p.virus;
103
104CREATE TABLE need (
105 patient_id BIGINT NOT NULL,
106 test_id INT NOT NULL,
107 PRIMARY KEY (patient_id, test_id)
108);
109
110CREATE TABLE test (
111 name VARCHAR(75) NOT NULL,
112 test_id INT NOT NULL,
113 patient_id BIGINT NOT NULL,
114 PRIMARY KEY (test_id)
115);
116
117
118CREATE TABLE IF NOT EXISTS users (
119 user_id BIGINT AUTO_INCREMENT NOT NULL,
120 username VARCHAR(35) NOT NULL,
121 email VARCHAR(55) NOT NULL,
122 password VARCHAR(32) NOT NULL,
123 name VARCHAR(30),
124 picture VARCHAR(255) DEFAULT 'profile.jpg',
125 activation_code VARCHAR(20) NOT NULL,
126 activated TINYINT DEFAULT 0 NOT NULL,
127 user_type INT DEFAULT 1 NOT NULL,
128 PRIMARY KEY(user_id),
129 UNIQUE(username),
130 UNIQUE(email)
131);
132
133CREATE TABLE result (
134 result_id INT NOT NULL AUTO_INCREMENT,
135 test_id INT NOT NULL AUTO_INCREMENT,
136
137
138
139CREATE TABLE security_question (
140 question_id tinyint(4) NOT NULL AUTO_INCREMENT,
141 question varchar(200) NOT NULL,
142 PRIMARY KEY (question_id),
143 UNIQUE KEY question (question))
144 ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6
145;
146INSERT INTO `security_question` VALUES(3, 'In what city did you meet your spouse/significant other?');
147INSERT INTO `security_question` VALUES(5, 'In what city or town did your mother and father meet?');
148INSERT INTO `security_question` VALUES(1, 'What is your favorite food?');
149INSERT INTO `security_question` VALUES(4, 'What school did you attend for sixth grade?');
150INSERT INTO `security_question` VALUES(2, 'Who was your best friend in childhood?');
151
152
153/*
154DROP TABLE IF EXISTS pat;
155DROP TABLE IF EXISTS pri;
156*/
157
158/*
159Useful queries
160
161select pa.sampleType, v.name, pr.sequence from patient as pa, virus as v, primer as pr where pa.virus=v.name and v.id=pr.virus_id and pa.name = 'Erik';
162*/