- USE project;
- CREATE TABLE user (
- user_id VARCHAR(50) PRIMARY KEY,
- fname VARCHAR(50) NOT NULL,
- mname VARCHAR(50),
- lname VARCHAR(50) NOT NULL,
- username VARCHAR(50) UNIQUE NOT NULL,
- phone VARCHAR(20) NOT NULL,
- email VARCHAR(100) NOT NULL,
- school VARCHAR(100) NOT NULL,
- branch VARCHAR(100) NOT NULL,
- type VARCHAR(20) NOT NULL
- );
- CREATE TABLE login (
- username VARCHAR(50) PRIMARY KEY,
- password VARCHAR(100) NOT NULL,
- type VARCHAR(20) NOT NULL,
- FOREIGN KEY (username) REFERENCES user(username)
- );
- CREATE TABLE courses (
- c_id INT PRIMARY KEY,
- c_name VARCHAR(100) NOT NULL,
- school VARCHAR(100) NOT NULL,
- sem VARCHAR(10) NOT NULL,
- description TEXT
- );
- CREATE TABLE faculty (
- t_id VARCHAR(50),
- c_id INT NOT NULL,
- slot VARCHAR(10) NOT NULL,
- seats INT NOT NULL,
- waiting INT NOT NULL,
- PRIMARY KEY (t_id),
- FOREIGN KEY (c_id) REFERENCES courses(c_id)
- );
- CREATE TABLE registered (
- user VARCHAR(50) NOT NULL,
- c_id INT NOT NULL,
- t_id VARCHAR(50) NOT NULL,
- slot VARCHAR(10) NOT NULL,
- status VARCHAR(20) NOT NULL,
- PRIMARY KEY (user, c_id, t_id, slot),
- FOREIGN KEY (user) REFERENCES user(username),
- FOREIGN KEY (c_id) REFERENCES courses(c_id),
- FOREIGN KEY (t_id) REFERENCES faculty(t_id)
- );
- INSERT INTO user (user_id, fname, mname, lname, username, phone, email, school, branch, type)
- VALUES
- ('abul01', 'Abul', '', 'Hossain', 'abul01', '+1234567890', '[email protected]', 'RUET', 'Rajshahi', 'Student'),
- ('babul02', 'Babul', '', 'Hossain', 'babul02', '+9876543210', '[email protected]', 'RUET', 'Rajshahi', 'Student'),
- ('babu03', 'Babu', '', 'Mia', 'babu03', '+1122334455', '[email protected]', 'RUET', 'Rajshahi', 'Student'),
- ('ali04', 'Khokon', '', 'Ali', 'ali04', '+4433221100', '[email protected]', 'RUET', 'Rajshahi', 'Student'),
- ('gias05', 'Prof. Gias', '', 'Uddin', 'gias05', '+5556667777', '[email protected]', 'RUET', 'Rajshahi', 'Faculty');
- INSERT INTO login (username, password, type)
- VALUES
- ('abul01', 'hashed_password_1', 'Student'),
- ('babul02', 'hashed_password_2', 'Student'),
- ('babu03', 'hashed_password_3', 'Student'),
- ('ali04', 'hashed_password_4', 'Student'),
- ('gias05', 'hashed_password_5', 'Faculty');
- INSERT INTO courses (c_id, c_name, school, sem, description)
- VALUES
- (101, 'Computer Networks', 'RUET', '2022 Odd', 'Database Systems'),
- (102, 'Operating System', 'RUET', '2022 Odd', 'Core concepts of computer operating systems.'),
- (103, 'Data Communication', 'RUET', '2022 Odd', 'Core concepts of computer operating systems.'),
- (104, 'Numerical Methods', 'RUET', '2022 Odd', 'Computational techniques for solving mathematical problems.'),
- (105, 'Database Systems', 'RUET', '2022 Odd', 'Database management, SQL, and data modeling.');
- INSERT INTO faculty (t_id, c_id, slot, seats, waiting)
- VALUES
- ('gias05', 101, 'A1', 50, 10);
- INSERT INTO registered (user, c_id, t_id, slot, status)
- VALUES
- ('abul01', 101, 'gias05', 'A1', 'Enrolled'),
- ('babu03', 102, 'gias05', 'B2', 'Enrolled'),
- ('babul02', 103, 'gias05', 'C3', 'Enrolled'),
- ('ali04', 104, 'gias05', 'D1', 'Enrolled'),
- ('abul01', 105, 'gias05', 'E2', 'Enrolled');