· 4 years ago · Mar 25, 2021, 05:10 PM
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
11CREATE DATABASE sample_school;
12
13-- Sets the sample school db as the active db
14USE sample_school;
15
16-- Creates the table for Faculty Members
17CREATE 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
27CREATE 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
37CREATE 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
45CREATE 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
55INSERT INTO
56 Faculty(Lname, Office, Bldg, Phone, Salary)
57VALUES
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
68INSERT INTO
69 MemberOf
70VALUES
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
84INSERT INTO
85 Course
86VALUES
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
100INSERT INTO
101 Teach
102VALUES
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.