Question

2.   A college needs a database that supports its placement office to help college students finding out potential employers. The primary purpose of the database is to schedule interviews and facilitate searches by students and companies. The requirements are the followings:

•   Student data include a unique identifier, a name, phone number, an email address, a web address, a major, a minor, and a GPA.
•   The placement office maintains a standard list of positions based on the Labor Department's list of occupations. Position data include a unique position identifier and a position description.
•   Company data include a unique company identifier, a company name, and a list of positions and interviewers. Each company must map its positions into the position list maintained by the placement office. For each available position, the company lists the cities in which positions are available.
•   Interviewer data include a unique interviewer identifier, a name, a phone, an email address, and a web address. Each interviewer works for one company.
•   An interview includes a unique interview identifier, a date, a time, a location (building and room), an interviewer, and a student.
•   An interview may be setup without knowing the interviewer
•   An interview is setup with the student

Answer 1

Let us first draw the Er diagram using following steps

• Draw a Student Entity with attributes S_Id, S_NAME, S_phone , S_email ,S_webaddress, major, minor, and GPA. making S_Id as primary key

• Make an Entity set Position with attributes pos_identifier and a pos_description.
• Entity set Company with attributes com_id, com_name, the relation ship between company and position will have attributes location
• Entity set Interviewer interviewer_id,name, phone, email , webaddress. A relation ship between interviwer and company will be many to one

•   An interview includes a unique interview identifier, a date, a time, location (wiil be composite attribute including

building and room), an interviewer, and a student.will be shown as binary relationship between student and interviewer
•   An interview may be setup without knowing the interviewer
•   An interview is setup with the student

the Er diagram will be as below

Sid (S-name- Student Interview) (S-phone R Interviewer Interviewer name phone femail Lweb addre 2019-9-30 11:32 s-webadd, (Semail M - Interniew-ia date time (minor major (GPA) location com_id COMPANY Hc-name (vacancy (City, and Pos-id | ER-Diagram I Positions description

Converting the ER- diagrem into relational mochel Step 1 - Convert all strong entity gets into seletion and include the simple attributes and simple components of componte athubutes as below 0 student & sid, £name, s.phone, so emart, so webodd, major, minor, GPA} ② Interviewer & intervier_id, name, phone, email, web addhan, compid} ② Positions { pos_id, description] Сомраля ( Сом јИ , Скан. Step 2 : Convert all binary relations in geletsonal tables as below. a 1:1 relation the com works for between Interviewer and Company take Primary key of 1 file as foreign key in many nde lie come id will be gig in interviewer So add com- id in interviewer as faeigu key. b) many to many relations: create the following relations and include simple attributes and simple components of composte alte butts and include primary key of participatiny ③ Vacancy & comp.id, pas id, Rity} ③ Interview & Interviewjd, int date, time, room, building, S-id, interviewe 2019-9-30 11:32 relation
The final detailed database would be as below

NOTE: THE DATA TYPES AND SIZE OF EACH ATTRIBUTES CAN BE CHOSEN AS REQUIREMENT

Table: STUDENT

ATTRIBUTE NAME	DESCRIPTION
SID	PRIMARY KEY
S_NAME
S_PHONE
S_EMAIL
S_WEBADRESS
MAJOR
MINOR
GPA

Table: INTERVIWER

ATTRIBUTE NAME	DESCRIPTION
INTERVIEWER_ID	PRIMARY KEY
NAME
PHONE
EMAIL
WEBADRESS
COMP_ID	FOREIGN KEY REFERENCES COM_ID OF COMPANY TABLE

Table: POSITIONS

ATTRIBUTE NAME	DESCRIPTION
POS_ID	PRIMARY KEY
DESCRIPTION

Table: COMPANY

ATTRIBUTE NAME	DESCRIPTION
COM_ID	PRIMARY KEY
C_NAME

Table: VACANCY

ATTRIBUTE NAME	DESCRIPTION
COMP_ID	PRIMARY KEY{COMP_ID,POS_ID}, COMP_ID FOREIGN KEY RFERENCING COM_ID OF COMPANY AND POS_ID FOREIGN KEY REFERENCING POS_ID OF POSITION
POS_ID
CITY

Table: INTERVIEW

ATTRIBUTE NAME	DESCRIPTION
INTERVIWER_ID	PRIMARY KEY
DATE
TIME
ROOM
BUILDING
S_ID	FOREIGN KEY REFERENCING S_ID OF STUDENT
INTERVIEWR_ID	FOREIGN KEY REFERENCING INTERVIEWER_ID OF INTERVIEWR