TTV_Jabit

Table Creation for sample_school

Mar 25th, 2021
574
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*  The Tables
  2. ------------------------------------------------------
  3.     FACULTY(_ID_, LNAME, OFFICE, BLDG, PHONE, SALARY)
  4.     COURSE(_NUMBER_, _DEPT_, LEVEL, MAXSIZE)
  5.     TEACH(_ID_, _NUMBER_, _DEPT_)
  6.     MEMBEROF(_ID_, _DEPT_)
  7. ------------------------------------------------------
  8. */
  9.  
  10. -- Creates the database
  11. CREATE DATABASE sample_school;
  12.  
  13. -- Sets the sample school db as the active db
  14. USE sample_school;
  15.  
  16. -- Creates the table for Faculty Members
  17. CREATE TABLE IF NOT EXISTS Faculty (
  18.     ID INT PRIMARY KEY AUTO_INCREMENT,
  19.     Lname VARCHAR(20),
  20.     Office INT,
  21.     Bldg VARCHAR(10),
  22.     Phone VARCHAR(25),
  23.     Salary DECIMAL(8, 2) DEFAULT 76000.00 CHECK (Salary <= 200000)
  24. );
  25.  
  26. -- Creates the table containing all of the courses
  27. CREATE TABLE IF NOT EXISTS Course (
  28.     Course_Number INT AUTO_INCREMENT NOT NULL,
  29.     Dept CHAR(4) REFERENCES MemberOf,
  30.     Course_Level INT,
  31.     MaxSize INT,
  32.    
  33.     PRIMARY KEY (Course_Number, Dept)
  34. );
  35.  
  36. -- Creates the table to connect Members with Departments
  37. CREATE TABLE IF NOT EXISTS MemberOf (
  38.     ID INT REFERENCES Faculty,
  39.     Dept CHAR(4),
  40.    
  41.     PRIMARY KEY(ID, Dept)
  42. );
  43.  
  44. -- Creates the teaching table
  45. CREATE TABLE IF NOT EXISTS Teach (
  46.     ID INT REFERENCES Faculty,
  47.     Course_Number INT REFERENCES Course,
  48.     Dept CHAR(4) REFERENCES MemberOf,
  49.  
  50.     PRIMARY KEY (ID, Course_Number, Dept),
  51.     FOREIGN KEY (Course_Number, Dept) REFERENCES Course(Course_Number, Dept)
  52. );
  53.  
  54. -- Inserts some default values into the Faculty table
  55. INSERT INTO
  56.     Faculty(Lname, Office, Bldg, Phone, Salary)
  57. VALUES
  58.     ('Cotts', 103, 'DuPont', '555-555-1234', 48000),
  59.     ('Garth', 423, 'DuPont', '555-555-4321', 137500),
  60.     ('Jones', 211, 'Ewing', '555-555-9876', 132546),
  61.     ('Noone', 421, 'Smith', '555-555-6789', 56872),
  62.     ('Smith', 301, 'Ewing', '555-555-3456', 68791),
  63.     ('Smith', 102, 'Smith', '555-555-8765', 87350),
  64.     ('Xyzyx', 099, 'Smith', '555-555-2468', 73426),
  65.     ('Einstein', 001, 'Sharp', '555-555-1111', 175356);
  66.  
  67. -- Inserts the values into MemberOf table
  68. INSERT INTO
  69.     MemberOf
  70. VALUES
  71.     (2, 'EE'),
  72.     (5, 'MATH'),
  73.     (1, 'EE'),
  74.     (1, 'CIS'),
  75.     (1, 'MATH'),
  76.     (1, 'PHYS'),
  77.     (4, 'CIS'),
  78.     (3, 'MATH'),
  79.     (6, 'CIS'),
  80.     (7, 'CIS'),
  81.     (8, 'PHYS');
  82.  
  83. -- Inserts the values into the course list
  84. INSERT INTO
  85.     Course
  86. VALUES
  87.     (411, 'CIS', 4, 25),
  88.     (180, 'CIS', 1, 60),
  89.     (220, 'CIS', 2, 40),
  90.     (637, 'CIS', 5, 25),
  91.     (652, 'CIS', 5, 25),
  92.     (221, 'MATH', 1, 60),
  93.     (411, 'MATH', 4, 25),
  94.     (230, 'MATH', 2, 60),
  95.     (413, 'EE', 4, 25),
  96.     (323, 'EE', 3, 25),
  97.     (867, 'EE', 5, 25);
  98.  
  99. -- Inserts the values for teach
  100. INSERT INTO
  101.     Teach
  102. VALUES
  103.     (2, 323, 'EE'),
  104.     (2, 413, 'EE'),
  105.     (5, 411, 'CIS'),
  106.     (5, 411, 'MATH'),
  107.     (1, 867, 'EE'),
  108.     (1, 652, 'CIS'),
  109.     (4, 180, 'CIS'),
  110.     (3, 230, 'MATH'),
  111.     (6, 220, 'CIS'),
  112.     (6, 637, 'CIS');
  113.  
  114. -- At the top I display what the relational model for the tables are, I then detail the creation and insertion of data.
RAW Paste Data

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×