DB-Project/All_Relations.sql at master · SOUMYASHUKLA/DB-Project · 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


CREATE DATABSE UGMS_DB;

USE UGMS_DB;

CREATE TABLE Instructor (
    IID INT(9),
    Fname VARCHAR(20) NOT NULL,
    Lname VARCHAR(20) NOT NULL,
    Contact INT(11) NOT NULL,
    Faculty VARCHAR(20) NOT NULL,
    PRIMARY KEY (IID)
);


CREATE TABLE Course (
    CID VARCHAR(6),
    Cname VARCHAR(50) NOT NULL,
    Faculty VARCHAR(50) NOT NULL,
    PRIMARY KEY (CID)
);


CREATE TABLE Instructor_Enrolment (
    IID INT(9),
    CID VARCHAR(6),
    PRIMARY KEY (IID, CID),
    FOREIGN KEY (IID) REFERENCES Instructor(IID),
    FOREIGN KEY (CID) REFERENCES Course(CID)
);

CREATE TABLE Assignment_Mark (
    Task_Type VARCHAR(30),
    SID INT(9),
    CID VARCHAR(6),
    Marks INT(3),
    PRIMARY KEY (SID, CID),
    FOREIGN KEY (SID) REFERENCES Student(SID),
    FOREIGN KEY (CID) REFERENCES Course(CID)
);


CREATE TABLE Student
(
SID INT(9),
Fname VARCHAR(20) NOT NULL,
Lname VARCHAR(20) NOT NULL,
Faculty VARCHAR(20) NOT NULL,
Address VARCHAR(50) NOT NULL,
Contact INTEGER (11) NOT NULL,
DOB DATE NOT NULL,
PRIMARY KEY (SID)
);


CREATE TABLE LoginID
(
LogID INTEGER(3) auto_increment,
UserID VARCHAR(15),
Role VARCHAR(15),
PRIMARY KEY(LogID)
);


CREATE TABLE Student_Auth (
UserID VARCHAR(15),
Pwd VARCHAR(20),
SID INT(9),
primary key (UserID),
FOREIGN KEY (SID) REFERENCES Student (SID)
);


CREATE TABLE Instructor_Auth (
UserID VARCHAR(15),
Pwd VARCHAR(20),
IID INT(9),
PRIMARY KEY (UserID),
FOREIGN KEY (IID) REFERENCES Instructor (IID)
);


CREATE TABLE Admin
(
	AID INT(9),
    Fname VARCHAR(20) NOT NULL,
    Lname VARCHAR(20) NOT NULL,
    PRIMARY KEY (AID)
);

CREATE TABLE Admin_Auth
(
	UserID VARCHAR(15),
    Pwd VARCHAR(20),
    AID INT(9),
    PRIMARY KEY (UserID),
    FOREIGN KEY (AID) REFERENCES Admin(AID)
);


CREATE TABLE Student_Enrolment
(
 SID INT(9),
 CID VARCHAR(6),
 Role VARCHAR(20),
 PRIMARY KEY(SID,CID),
 FOREIGN KEY (SID) REFERENCES Student(SID),
 FOREIGN KEY (CID) REFERENCES Course(CID)
);

CREATE TABLE Grade_Sheet
(
	CID VARCHAR(6),
    SID INT(9),
	Final_Grade INT(3),
    PRIMARY KEY (CID,SID),
    FOREIGN KEY (CID) REFERENCES Course(CID),
    FOREIGN KEY (SID) REFERENCES Student(SID)
);