Automated-Course-Monitoring-System/SQL TABLES.sql at main · sameerbhavar/Automated-Course-Monitoring-System · 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
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
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148


Admin Table
=============

create table admin
(
username varchar(20) primary key,
password varchar(15) not null
);


Faculty
============

create table faculty
(
facultyid int primary key auto_increment,
facultyname varchar(20) not null,
facultyaddress varchar(25),
mobile varchar(15),
email varchar(15) not null,
username varchar(20) not null,
password varchar(15)not null
);


Course
=========

create table course
(
courseid int primary key auto_increment,
coursename varchar(20) not null,
fee int  not null,
courseDescription varchar(50)
);

Batch
=======

create table batch
(
batchid int primary key auto_increment,
courseid int ,
facultyid int,
numberofStudents int,
batchatcstartdate date,
duration varchar(20),
foreign key(courseid) references course(courseid),
foreign key(facultyid) references faculty(facultyid)
);

CoursePlan
============

create table coursePlan
(
planid int primary key auto_increment,
batchid int,
daynumber int,
topic varchar(20),
status varchar(8),
foreign key(batchid) references batch(batchid)
);

Faculty
=========


insert into faculty(facultyname,facultyaddress,mobile,email,username,password) values('chander','kolkata','12346','chander@gmail.com','chander','123');
insert into faculty(facultyname,facultyaddress,mobile,email,username,password) values('swanand','nagpur','654123','swanand@gmail.com','swanand','123');
insert into faculty(facultyname,facultyaddress,mobile,email,username,password) values('Ankush','delhi','987456','ankush@gmail.com','ankush','123');
insert into faculty(facultyname,facultyaddress,mobile,email,username,password) values('venu','tamil','369852','venu@gmail.com','venu','123');
insert into faculty(facultyname,facultyaddress,mobile,email,username,password) values('ratan','M.P','852147','ratan@gmail.com','ratan','123');

+-----------+-------------+----------------+--------+-------------------+----------+----------+
| facultyid | facultyname | facultyaddress | mobile | email             | username | password |
+-----------+-------------+----------------+--------+-------------------+----------+----------+
|         1 | venu        | tamil          | 369852 | venu@gmail.com    | venu     | 123      |
|         2 | ratan       | M.P            | 852147 | ratan@gmail.com   | ratan    | 123      |
|         3 | chander     | kolkata        | 12346  | chander@gmail.com | chander  | 123      |
|         4 | swanand     | nagpur         | 654123 | swanand@gmail.com | swanand  | 123      |
|         5 | Ankush      | delhi          | 987456 | ankush@gmail.com  | ankush   | 123      |
+-----------+-------------+----------------+--------+-------------------+----------+----------+

Course
========

insert into course(coursename,fee,courseDescription) values('Java',50000,'full time course ');
insert into course(coursename,fee,courseDescription) values('Mern',30000,'full time course ');
insert into course(coursename,fee,courseDescription) values('Spring',20000,'full time course ');
insert into course(coursename,fee,courseDescription) values('DSA',30000,'full time course ');
insert into course(coursename,fee,courseDescription) values('React',10000,'full time course ');

+----------+------------+-------+-------------------+
| courseid | coursename | fee   | courseDescription |
+----------+------------+-------+-------------------+
|        1 | Java       | 50000 | full time course  |
|        2 | Mern       | 30000 | full time course  |
|        3 | Spring     | 20000 | full time course  |
|        4 | DSA        | 30000 | full time course  |
|        5 | React      | 10000 | full time course  |
+----------+------------+-------+-------------------+


Batch
=======

insert into batch(courseid,facultyid,numberofStudents,batchstartdate,duration) values(1,2,10,'2022/08/01','50-days');
insert into batch(courseid,facultyid,numberofStudents,batchstartdate,duration) values(2,1,20,'2022/07/01','50-days');
insert into batch(courseid,facultyid,numberofStudents,batchstartdate,duration) values(3,1,30,'2022/06/05','50-days');
insert into batch(courseid,facultyid,numberofStudents,batchstartdate,duration) values(4,3,25,'2022/07/15','50-days');
insert into batch(courseid,facultyid,numberofStudents,batchstartdate,duration) values(3,4,18,'2022/06/12','50-days');

+---------+----------+-----------+------------------+----------------+----------+
| batchid | courseid | facultyid | numberofStudents | batchstartdate | duration |
+---------+----------+-----------+------------------+----------------+----------+
|       1 |        1 |         2 |               10 | 2022-08-01     | 50-days  |
|       2 |        2 |         1 |               20 | 2022-07-01     | 50-days  |
|      10 |        3 |         1 |               30 | 2022-06-05     | 50-days  |
|      12 |        4 |         3 |               25 | 2022-07-15     | 50-days  |
|      13 |        3 |         4 |               18 | 2022-06-12     | 50-days  |
+---------+----------+-----------+------------------+----------------+----------+


CoursePlan
============

insert into coursePlan(batchid,daynumber,topic,status) values(2,1,'introduction to Mern','complete');
insert into coursePlan(batchid,daynumber,topic,status) values(10,3,'work on  spring','incomplete');
insert into coursePlan(batchid,daynumber,topic,status) values(2,2,'es6','complete');
insert into coursePlan(batchid,daynumber,topic,status) values(12,6,'jdbc','incomplete');
insert into coursePlan(batchid,daynumber,topic,status) values(13,2,'recursion','incomplete');

+--------+---------+-----------+----------------------+------------+
| planid | batchid | daynumber | topic                | status     |
+--------+---------+-----------+----------------------+------------+
|      1 |       2 |         1 | introduction to Mern | complete   |
|      6 |       1 |         6 | jdbc                 | incomplete |
|      8 |      10 |         3 | work on  spring      | incomplete |
|      9 |       2 |         2 | es6                  | complete   |
|     10 |      12 |         6 | jdbc                 | incomplete |
|     11 |      13 |         2 | recursion            | incomplete |
+--------+---------+-----------+----------------------+------------+