Problem 3: Convert the following ER diagram into a set of tables 1). Be sure to

Question

Problem 3: Convert the following ER diagram into a set of tables 1). Be sure to underline the primarv kev for each table vou write. 2). State the foreign keys in each table after conversion, if any 3). When you convert a relationship, be sure to use a different font for all attributes added to a table including the foreign key added. If hand-writing, circle all attributes added as a result of relationship conversion. 4). List all new tables added at the end (Hint: Remember two fields in the same table can't have the same name.) nam Objective Committee tartDate office phone specialty ervesOn tartDate sname facname Phone addr year Faculty dvises GradStudent fid 1 StartDate studies StartDate orksFor Chairs urrentStandin name name Department offers Program Location Desc

Explanation / Answer

Table 1: grad_student

CREATE TABLE grad_student (

sid int NOT NULL,

   sname varchar(255),

   fid_fk int,

   phone int,

   address varchar(255),

   year int,

   start_date_fac_advice date,

   PRIMARY KEY (sid),

   FOREIGN KEY (fid_fk) REFERENCES faculty(fid)

);

Table 2: program

CREATE TABLE program (

pname varchar(255) NOT NULL,

dname_fk varchar(255),

   desc varchar(255),

   PRIMARY KEY (pname),

   FOREIGN KEY (dname_fk) REFERENCES department(dname)

);

Table 3: department

CREATE TABLE department (

dname varchar(255) NOT NULL,

   location varchar(255),

   PRIMARY KEY (dname)

);

Table 4: faculty

CREATE TABLE faculty (

fid int NOT NULL,

   facname varchar(255),

   specialty varchar(255),

   dname_fk varchar(255),

   phone int,

   office varchar(255),

   start_date date,

   PRIMARY KEY (fid),

   FOREIGN KEY (dname_fk) REFERENCES department(dname)

);

Table 5: committee

CREATE TABLE committee (

cname varchar(255) NOT NULL,

   objective varchar(255),

   PRIMARY KEY (cname)

);

Table 6: student_program_map

CREATE TABLE student_program_map (

spid int NOT NULL,

   pname_fk varchar(255),

   sid_fk int,

   current_standing int,

   PRIMARY KEY (spid),

   FOREIGN KEY (pname_fk) REFERENCES program(pname),

   FOREIGN KEY (sid_fk) REFERENCES grad_student(sid)

);

Table 7: department_head

CREATE TABLE department_chair (

dcid int NOT NULL,

   dname_fk varchar(255),

   fid_fk int,

   start_date date,

   PRIMARY KEY (dcid),

   FOREIGN KEY (dname_fk) REFERENCES department(dname),

   FOREIGN KEY (fid_fk) REFERENCES faculty(fid)

);

Table 8: faculty_committee_map

CREATE TABLE faculty_committee_map (

fcid int NOT NULL,

   cname_fk varchar(255),

   fid_fk int,

   start_date date,

   PRIMARY KEY (fcid),

   FOREIGN KEY (cname_fk) REFERENCES committee(cname),

   FOREIGN KEY (fid_fk) REFERENCES faculty(fid)

);

sid int sname varchar(255) fid_fk int phone int address varchar(255) year int start_date_advice date

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.