Facebook
From sdfsd, 9 Months ago, written in Plain Text.
Embed
Download Paste or View Raw
Hits: 418
  1. USE project;
  2.  
  3. CREATE TABLE user (
  4.     user_id VARCHAR(50) PRIMARY KEY,
  5.     fname VARCHAR(50) NOT NULL,
  6.     mname VARCHAR(50),
  7.     lname VARCHAR(50) NOT NULL,
  8.     username VARCHAR(50) UNIQUE NOT NULL,
  9.     phone VARCHAR(20) NOT NULL,
  10.     email VARCHAR(100) NOT NULL,
  11.     school VARCHAR(100) NOT NULL,
  12.     branch VARCHAR(100) NOT NULL,
  13.     type VARCHAR(20) NOT NULL
  14. );
  15.  
  16.  
  17. CREATE TABLE login (
  18.     username VARCHAR(50) PRIMARY KEY,
  19.     password VARCHAR(100) NOT NULL,
  20.     type VARCHAR(20) NOT NULL,
  21.     FOREIGN KEY (username) REFERENCES user(username)
  22. );
  23.  
  24.  
  25. CREATE TABLE courses (
  26.     c_id INT PRIMARY KEY,
  27.     c_name VARCHAR(100) NOT NULL,
  28.     school VARCHAR(100) NOT NULL,
  29.     sem VARCHAR(10) NOT NULL,
  30.     description TEXT
  31. );
  32.  
  33.  
  34. CREATE TABLE faculty (
  35.     t_id VARCHAR(50),
  36.     c_id INT NOT NULL,
  37.     slot VARCHAR(10) NOT NULL,
  38.     seats INT NOT NULL,
  39.     waiting INT NOT NULL,
  40.     PRIMARY KEY (t_id),
  41.     FOREIGN KEY (c_id) REFERENCES courses(c_id)
  42. );
  43.  
  44.  
  45. CREATE TABLE registered (
  46.     user VARCHAR(50) NOT NULL,
  47.     c_id INT NOT NULL,
  48.     t_id VARCHAR(50) NOT NULL,
  49.     slot VARCHAR(10) NOT NULL,
  50.     status VARCHAR(20) NOT NULL,
  51.     PRIMARY KEY (user, c_id, t_id, slot),
  52.     FOREIGN KEY (user) REFERENCES user(username),
  53.     FOREIGN KEY (c_id) REFERENCES courses(c_id),
  54.     FOREIGN KEY (t_id) REFERENCES faculty(t_id)
  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. INSERT INTO user (user_id, fname, mname, lname, username, phone, email, school, branch, type)
  84. VALUES
  85. ('abul01', 'Abul', '', 'Hossain', 'abul01', '+1234567890', '[email protected]', 'RUET', 'Rajshahi', 'Student'),
  86. ('babul02', 'Babul', '', 'Hossain', 'babul02', '+9876543210', '[email protected]', 'RUET', 'Rajshahi', 'Student'),
  87. ('babu03', 'Babu', '', 'Mia', 'babu03', '+1122334455', '[email protected]', 'RUET', 'Rajshahi', 'Student'),
  88. ('ali04', 'Khokon', '', 'Ali', 'ali04', '+4433221100', '[email protected]', 'RUET', 'Rajshahi', 'Student'),
  89. ('gias05', 'Prof. Gias', '', 'Uddin', 'gias05', '+5556667777', '[email protected]', 'RUET', 'Rajshahi', 'Faculty');
  90.  
  91.  
  92. INSERT INTO login (username, password, type)
  93. VALUES
  94. ('abul01', 'hashed_password_1', 'Student'),
  95. ('babul02', 'hashed_password_2', 'Student'),
  96. ('babu03', 'hashed_password_3', 'Student'),
  97. ('ali04', 'hashed_password_4', 'Student'),
  98. ('gias05', 'hashed_password_5', 'Faculty');
  99.  
  100.  
  101. INSERT INTO courses (c_id, c_name, school, sem, description)
  102. VALUES
  103. (101, 'Computer Networks', 'RUET', '2022 Odd', 'Database Systems'),
  104. (102, 'Operating System', 'RUET', '2022 Odd', 'Core concepts of computer operating systems.'),
  105. (103, 'Data Communication', 'RUET', '2022 Odd', 'Core concepts of computer operating systems.'),
  106. (104, 'Numerical Methods', 'RUET', '2022 Odd', 'Computational techniques for solving mathematical problems.'),
  107. (105, 'Database Systems', 'RUET', '2022 Odd', 'Database management, SQL, and data modeling.');
  108.  
  109.  
  110. INSERT INTO faculty (t_id, c_id, slot, seats, waiting)
  111. VALUES
  112. ('gias05', 101, 'A1', 50, 10);
  113.  
  114.  
  115. INSERT INTO registered (user, c_id, t_id, slot, status)
  116. VALUES
  117. ('abul01', 101, 'gias05', 'A1', 'Enrolled'),
  118. ('babu03', 102, 'gias05', 'B2', 'Enrolled'),
  119. ('babul02', 103, 'gias05', 'C3', 'Enrolled'),
  120. ('ali04', 104, 'gias05', 'D1', 'Enrolled'),
  121. ('abul01', 105, 'gias05', 'E2', 'Enrolled');
  122.  
  123.  
  124.  
  125.  
  126.  
  127.  
  128.  
  129.