-
Notifications
You must be signed in to change notification settings - Fork 1
/
db.sql
51 lines (48 loc) · 1.35 KB
/
db.sql
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
CREATE TABLE student (
id INT NOT NULL,
first_name VARCHAR(50),
middle_name VARCHAR(50),
last_name VARCHAR(50),
date_of_birth VARCHAR(10),
email VARCHAR(50),
category VARCHAR(15),
gender VARCHAR(15),
belongs_to_EWS varchar(5) check(belongs_to_EWS in ('True', 'False')),
is_disabled varchar(5) check(is_disabled in ('True', 'False')),
mother_tongue VARCHAR(30),
caste VARCHAR(30),
religion VARCHAR(30),
phone VARCHAR(10),
aadhar VARCHAR(15),
PRIMARY KEY (id)
);
CREATE TABLE academic_detail(
student_id INT NOT NULL,
CET_percentile NUMBER(15, 7),
JEE_percentile NUMBER(15, 7),
HSC_or_equivalent_percentage NUMBER(15, 7),
SSC_or_equivalent_percentage NUMBER(15, 7),
SSC_passing_year INT,
HSC_passing_year INT,
FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE CASCADE,
PRIMARY KEY (student_id)
);
CREATE TABLE parent(
id INT NOT NULL,
relationship_type VARCHAR (20),
first_name VARCHAR(50),
middle_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(50),
occupation_type VARCHAR(50),
occupation_name VARCHAR(50),
annual_income VARCHAR(50),
phone VARCHAR (10),
PRIMARY KEY (id)
);
CREATE TABLE student_parents(
student_id INT NOT NULL,
parent_id INT NOT NULL,
FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE CASCADE,
FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
);