1. There is the code for two tables given below. You must copy this to Notepad++
ID: 3705489 • 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), E 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
BEGIN TRANSACTION;
1.
DROP TABLE student;
DROP TABLE faculty;
CREATE TABLE student(
student_id INT PRIMARY KEY,
s_first VARCHAR(30),
s_mid CHAR(1),
s_last VARCHAR(30),
s_class VARCHAR(2),
s_dob DATETIME,
s_major VARCHAR(22),
s_gpa NUMERIC(3,2));
CREATE TABLE faculty (
f_id INT PRIMARY KEY,
s_first VARCHAR(30),
s_last VARCHAR(30),
f_salary MONEY,
year_hired INT,
phd BIT);
2. The relation can be create using a foreign key, there are two ways to create foreign key. one is on the time of creation of table, or using the alter command. I am explaining using the ALTER command for you.
ALTER TABLE student ADD advisor_id INT;
ALTER TABLE student ADD FOREIGN KEY (advisor_id) REFERENCES faculty(f_id);
3. To remove a field from table we have to use alter table command along with drop key word
ALTER TABLE student DROP COLUMN s_major;
4. All should be inserted to the created data base
Faculty table to be inserted
INSERT INTO faculty (f_id,f_first,f_last,f_salary,year_hired,phd) VALUES (14,'RALPH','BROOKS',78000,1999,TRUE);
INSERT INTO faculty (f_id,f_first,f_last,year_hired) VALUES (22,'JOHN','SEYMOUR',2002);
INSERT INTO faculty (f_id,f_first,f_last,f_salary,year_hired,phd) VALUES (29,'JAMES’,’BRODY',56000,1996,TRUE);
INSERT INTO faculty (f_id,f_first,f_last,f_salary,year_hired,phd) VALUES (71,'MARY','TESSMAN',97000,2002,TRUE);
INSERT INTO faculty (f_id,f_first,f_last,f_salary,year_hired) VALUES (44,'BOBBY','BELL',69000,1999);
Student table to insert
INSERT INTO student (student_id,s_first,s_last,s_class,s_dob) VALUES (111,’MAYBELLE','EASTON’,’JR’,’01-07-1984 00-00-00’);
INSERT INTO student (student_id,s_first,s_mid,s_last,s_class,s_dob,s_gpa,advisor_id) VALUES (151,’PETERE',’A’,'BILLINGS’,’SR’,’03-11-1986 00-00-00’,3.30,29);
INSERT INTO student (student_id,s_first,s_mid,s_last,s_class,s_dob,s_gpa,advisor_id) VALUES (171,’JOHN',’D’,'BRIGGS’,’FR’,’18-05-1987 00-00-00’,3.30,44);
INSERT INTO student (student_id,s_first,s_last,s_class,s_gpa,advisor_id) VALUES (144,’PEGGY','MING’,’JR’,3.62,29);
INSERT INTO student (student_id,s_first,s_mid,s_last,s_class,s_dob,s_gpa,advisor_id) VALUES (166,’ABBYY',’M’,'LEHMANN’,’JR’,’08-02-1984 00-00-00’,4.00,22);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.