-
Notifications
You must be signed in to change notification settings - Fork 2
/
Project.sql
126 lines (102 loc) · 3.48 KB
/
Project.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
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
use a123
CREATE TABLE Roles
(
Roles_Id VARCHAR(10) CONSTRAINT rolesPK PRIMARY KEY,
Role_Description VARCHAR(100),
Role_Name VARCHAR(50)
);
CREATE TABLE Student
(
Student_id VARCHAR (11) CONSTRAINT studentPK PRIMARY KEY,
Password VARCHAR (50),
Section VARCHAR(3),
Contact varchar(10) NOT NULL,
Name VARCHAR (50),
Class_Name VARCHAR(10),
Role_Id VARCHAR (10) FOREIGN KEY REFERENCES roles(roles_id)
);
CREATE TABLE Course
(
Course_ID VARCHAR(6) CONSTRAINT coursePK PRIMARY KEY,
Course_Name VARCHAR(60),
Student_Id VARCHAR (11) FOREIGN KEY REFERENCES student(Student_Id)
);
CREATE TABLE Teacher
(
Teacher_Id VARCHAR(11) CONSTRAINT teacherPK PRIMARY KEY,
Email VARCHAR(60),
Name VARCHAR(60),
Password VARCHAR(60),
Course_Id VARCHAR (6) FOREIGN KEY REFERENCES course(Course_Id)
);
CREATE TABLE Assignment
(
Assignment_Id VARCHAR(10) CONSTRAINT assignmentPK PRIMARY KEY,
Deadline DATE,
Question VARCHAR(250),
Course_Id VARCHAR (6) FOREIGN KEY REFERENCES course(Course_Id),
Teacher_Id VARCHAR (11) FOREIGN KEY REFERENCES teacher(Teacher_Id)
);
CREATE TABLE Team
(
Team_No VARCHAR(10) PRIMARY KEY,
Student_Leader_Name VARCHAR(50),
Team_Name VARCHAR(30),
Student_Id VARCHAR (11) FOREIGN KEY REFERENCES student(Student_Id),
Assignment_Id VARCHAR (10) FOREIGN KEY REFERENCES assignment(Assignment_Id),
Teacher_Id VARCHAR (11) FOREIGN KEY REFERENCES teacher(Teacher_Id)
);
CREATE TABLE BuddyMentor
(
Mentor_student_id VARCHAR(11) CONSTRAINT mentorPK PRIMARY KEY,
Name CHAR,
Contact INT,
Student_id VARCHAR (11) FOREIGN KEY REFERENCES student(Student_id)
);
CREATE TABLE Remarks
(
Remarks VARCHAR(100) ,
Student_Id VARCHAR (11) FOREIGN KEY REFERENCES student(Student_Id),
Mentor_Student_Id VARCHAR (11) FOREIGN KEY REFERENCES buddymentor(Mentor_Student_Id),
Assignment_Id VARCHAR (10) FOREIGN KEY REFERENCES assignment(Assignment_Id),
CONSTRAINT PK_studid PRIMARY KEY (Student_Id)
);
CREATE TABLE Submission
(
Answer VARCHAR(500) ,
Assignment_Id VARCHAR (10) FOREIGN KEY REFERENCES assignment(Assignment_Id),
Team_No VARCHAR (10) FOREIGN KEY REFERENCES team(Team_No),
Teacher_Id VARCHAR (11) FOREIGN KEY REFERENCES teacher(Teacher_Id),
Course_Id VARCHAR (6) FOREIGN KEY REFERENCES course(Course_Id),
CONSTRAINT PK_AssignmentId PRIMARY KEY (Assignment_Id)
);
CREATE TABLE Grades
(
Feedback VARCHAR(500),
Grades VARCHAR(2),
Student_Id VARCHAR (11) FOREIGN KEY REFERENCES student(Student_Id),
Assignment_Id VARCHAR (10) FOREIGN KEY REFERENCES assignment(Assignment_Id),
CONSTRAINT PK_Students1 PRIMARY KEY (Student_Id)
);
CREATE TABLE Allots
(
Teacher_Id VARCHAR (11) FOREIGN KEY REFERENCES teacher(Teacher_Id),
Team_No VARCHAR (10) FOREIGN KEY REFERENCES team(Team_No),
Assignment_Id VARCHAR (10) FOREIGN KEY REFERENCES assignment(Assignment_Id),
CONSTRAINT PK_Team PRIMARY KEY (Team_No)
);
CREATE TABLE Gives
(
Teacher_Id VARCHAR (11) FOREIGN KEY REFERENCES teacher(Teacher_Id),
Feedback VARCHAR (100) ,
Student_Id VARCHAR (11) FOREIGN KEY REFERENCES student(Student_Id),
CONSTRAINT PK_Students2 PRIMARY KEY (Student_Id,Teacher_id),
);
ALTER TABLE Student
ADD Team_No VARCHAR (10) CONSTRAINT teamnoFK FOREIGN KEY(Team_no) REFERENCES team(Team_No);
ALTER TABLE Student
ADD Mentor_Student_Id VARCHAR (11) CONSTRAINT mentorFK FOREIGN KEY REFERENCES buddymentor(Mentor_Student_Id);
/*ALTER TABLE Student
ADD Role_Id VARCHAR (10) CONSTRAINT roleFK FOREIGN KEY (Role_Id) REFERENCES roles(Role_Id);*/
ALTER TABLE Assignment
ADD Team_No VARCHAR (10) CONSTRAINT assignTeamFK FOREIGN KEY (Team_No) REFERENCES team(Team_No)