1. There is the code for two tables given below. You must copy this to Notepad++
ID: 3705488 • Letter: 1
Question
1. There is the code for two tables given below. You must copy this to Notepad++ (copy it exactly!!, though it has been provided). Once you have copied it to Notepad++, you ought to execute (create them) in SQL Server. Once you successfully do this, you may proceed to the rest of the steps. DROP TABLE student DROP TABLE faculty CREATE TABLE student (student id INT PRIMARY KEY s first VARCHAR (30), s mi CHAR (1), s last VARCHAR (30), f class CHAR (2), s dob DATETIME, s major VARCHAR (22), s gpa NUMERIC (3, 2)) CREATE TABLE faculty (f id INT PRIMARY KEY f first VARCHAR (30), f last VARCHAR (30), f salary MONEY, year hired INT phd BIT); 2. Now that the tables are created, you must make the relationship between the two. Business rules: A faculty member advises zero to many students; a student has one and only one advisor]. Using SQL, make the appropriate relationship. If you want (and you should), you may add the relationship by redoing the CREATE command above (but this is the only time you can change the CREATE commands). 3. After some discussion, you discover you do not want or need the field that gives aExplanation / Answer
ALTER TABLE student
ADD CONSTRAINT fk_advisor
FOREIGN KEY (s_advisor)
REFERENCES faculty (f_id)
CONSTRAINT fk_advisor
FOREIGN KEY (s_advisor)
REFERENCES faculty (f_id)
Faculty Rows:
INSERT INTO faculty (f_id, f_first, f_last, phd, f_salary, year_hired) VALUES
(14, 'Ralph A.', 'Brooks', 1, 78000, 1999),
(22, 'Jane E.', 'Seymour', 0, NULL, 2002),
(29, 'James', 'Brody', 1, 56000, 1996),
(71, 'Mary', 'Tessman', 1, 97000, 2002),
(44, 'Bobby', 'Bell', 0, 69500, 1999);
(5 rows affected)
Student Rows:
INSERT INTO student (student_id, s_first, s_last, s_class, s_advisor, s_dob) VALUES
(111, 'Maybelle', 'Easton', 'JR', 29, '1984-07-01');
INSERT INTO student (student_id, s_first, s_mi, s_last, s_class, s_advisor, s_gpa, s_dob) VALUES
(151, 'Peter', 'A', 'Billings', 'SR', 44, 3.30, '1986-11-03'),
(171, 'John', 'C', 'Briggs', 'FR', 71, NULL, '1987-05-18');
INSERT INTO student (student_id, s_first, s_last, s_class, s_advisor, s_gpa) VALUES
(144, 'Peggy', 'Ming', 'JR', 29, 3.62);
INSERT INTO student (student_id, s_first, s_mi, s_last, s_class, s_advisor, s_gpa, s_dob) VALUES
(166, 'Abby', 'M', 'Lehmann', 'JR', 22, 4.0, '1984-02-08');
(1 row affected)
(2 rows affected)
(1 row affected)
(1 row affected)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.