SAMS/db.sql at main · mausooq/SAMS · GitHub

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
CREATE TABLE STUDENT (
    id BIGINT PRIMARY KEY,
    USN VARCHAR(20) NOT NULL,
    NAME VARCHAR(30) NOT NULL,
    PHONE VARCHAR(12) NOT NULL,
    EMAIL VARCHAR(100),
    PASSWORD VARCHAR(250)
);
CREATE TABLE PROJECT (
    pid BIGINT PRIMARY KEY,
    id BIGINT,
    FOREIGN KEY (id) REFERENCES STUDENT(id),
    image_path VARCHAR(255),
    title VARCHAR(50) NOT NULL,
    date VARCHAR(20) NOT NULL,
    location VARCHAR(100) NOT NULL,
    description varchar(10000) NOT NULL
);
CREATE TABLE ACHIEVEMENT (
    aid BIGINT PRIMARY KEY,
    id BIGINT,
    FOREIGN KEY (id) REFERENCES STUDENT(id),
    image_path VARCHAR(255),
    title VARCHAR(50) NOT NULL,
    date VARCHAR(20) NOT NULL,
    location VARCHAR(100) NOT NULL,
    description varchar(10000) NOT NULL
);
CREATE TABLE INTERNSHIP (
    iid BIGINT PRIMARY KEY,
    id BIGINT,
    FOREIGN KEY (id) REFERENCES STUDENT(id),
    image_path VARCHAR(255),
    title VARCHAR(50) NOT NULL,
    date VARCHAR(20) NOT NULL,
    location VARCHAR(100) NOT NULL,
    description varchar(10000) NOT NULL
);
CREATE TABLE AWARD (
    aid BIGINT PRIMARY KEY,
    id BIGINT,
    FOREIGN KEY (id) REFERENCES STUDENT(id),
    image_path VARCHAR(255),
    title VARCHAR(50) NOT NULL,
    date VARCHAR(20) NOT NULL,
    location VARCHAR(100) NOT NULL,
    description varchar(10000) NOT NULL
);

CREATE TABLE ECA (
    eid BIGINT PRIMARY KEY,
    id BIGINT,
    FOREIGN KEY (id) REFERENCES STUDENT(id),
    image_path VARCHAR(255),
    title VARCHAR(50) NOT NULL,
    date VARCHAR(20) NOT NULL,
    location VARCHAR(100) NOT NULL,
    description varchar(10000) NOT NULL
);

CREATE TABLE Semester (
	id bigint,
    foreign key (id) references student(id),
    semId INT PRIMARY KEY,
    semesterName varchar(20)
);

CREATE TABLE MARKS(
	id bigint,
    semId int,
    subId int primary key,
    subName varchar(100),
    maxMark int,
    subMark int,
    grade char(2),
    foreign key (id) references student(id),
    foreign key (semId) references semester(semId)
);

CREATE TABLE admin (
    admin_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
     email VARCHAR(100) NOT NULL,
    password VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO admin (username, password, email)
VALUES ('ADMIN', 'admin123', 'admin@gmail.com');