-
Notifications
You must be signed in to change notification settings - Fork 0
/
sqltable.php
executable file
·136 lines (95 loc) · 2.26 KB
/
sqltable.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
<!DOCTYPE html>
<html>
<head>
<title>Create tables</title>
</head>
<body>
<?php
require_once('DBClass.php');
echo "Creating SQL Database";
/* Assuming existence of a database- SystemDB
$SystemDB= <<<HERE
CREATE DATABASE CounsSystem
HERE; */
$Administrator= <<<HERE
CREATE TABLE Administrator
(fname VARCHAR(15) NOT NULL,
lname VARCHAR(15) NOT NULL,
uname VARCHAR(15) NOT NULL,
mailid VARCHAR(15),
password VARCHAR(15) NOT NULL,
PRIMARY KEY(mailid));
HERE;
$Counselor= <<<HERE
CREATE TABLE Counselor
(fname VARCHAR(15) NOT NULL,
lname VARCHAR(15) NOT NULL,
mailid VARCHAR(15),
password VARCHAR(15) NOT NULL,
PRIMARY KEY(mailid));
HERE;
$Applicant= <<<HERE
CREATE TABLE Applicant
(fname VARCHAR(15) NOT NULL,
lname VARCHAR(15) NOT NULL,
mailid VARCHAR(15),
password VARCHAR(15) NOT NULL,
firstVisit BOOLEAN NOT NULL DEFAULT TRUE,
mailidCouns VARCHAR(15),
PRIMARY KEY(mailid),
FOREIGN KEY(mailidCouns) REFERENCES Counselor(mailid));
HERE;
// BLOB-upto 64KB; MEDIUMBLOB-upto 16 MB
$Note= <<<HERE
CREATE TABLE Note
(note MEDIUMBLOB,
mailidAppl VARCHAR(15) NOT NULL,
PRIMARY KEY(mailidAppl),
FOREIGN KEY(mailidAppl) REFERENCES Applicant(mailid));
HERE;
$Docs= <<<HERE
CREATE TABLE Docs
(docName VARCHAR(40) NOT NULL,
content MEDIUMBLOB,
mailidAppl VARCHAR(15) NOT NULL,
PRIMARY KEY(mailidAppl),
FOREIGN KEY(mailidAppl) REFERENCES Applicant(mailid));
HERE;
//DATE format: YYYY:MM:DD; YEAR: YYYY. GRE score, TOEFL score-integer/float?
$Profile= <<<HERE
CREATE TABLE Profile
(dob DATE,
college VARCHAR(40),
branch VARCHAR(40),
yearofComp YEAR,
gpa float,
greScore float,
toeflScore float,
mailidAppl VARCHAR(15) NOT NULL,
PRIMARY KEY(mailidAppl),
FOREIGN KEY(mailidAppl) REFERENCES Applicant(mailid));
HERE;
//TIME format: HH:MM:SS
$Message= <<<HERE
CREATE TABLE Message
(sender CHAR(1) NOT NULL,
content BLOB,
date DATE NOT NULL,
time TIME NOT NULL,
mailidAppl VARCHAR(15) NOT NULL,
PRIMARY KEY(sender,time,date,mailidAppl),
FOREIGN KEY(mailidAppl) REFERENCES Applicant(mailid));
HERE;
$db=new DBClass();
//db->select("$SystemDB");
$db->query($Administrator);
$db->query($Counselor);
$db->query($Applicant);
$db->query($Note);
$db->query($Docs);
$db->query($Profile);
$db->query($Message);
echo "SQL Database created successfully";
?>
</body>
</html>