Interview Database
Details are kept of the applicants who have been short-listed for interviews for jobs within a company.
It is necessary to be able to record information about the interview (date and time, the result of the interview, the panel members, the applicant), the job details, applicant and panel member details (they are all employees of the company).
A panel member can sit on many panels.
An employee can apply for more than one job, and hence have more than one interview.
Each person has only one grade and only one department.
Panel Members for job number J10 interviews are employee numbers 603, 602 and 605.
Panel Members for job number J11 interviews are employee numbers 604, 605 and 612.
Tables other than the 3 given below (EMP, INT and JOB) will be needed. It is up to each student to decide what the other tables are and how all the tables are related to each other.
Once you have decided what the other tables are, you will need to specify their attributes (column or field names) and add some sensible example data.
For the purpose of this coursework, ‘emp_add’ will be just a postcode.
Data for the Job, Interview and Applicant tables (this can be entered as .csv files which is why the data is not lined up under the column headings):
EMP
emp_no, emp_name, dept_no, grade_no, emp_add, emp_phone
602,James,9,MG2,LU4 5NH,09487398204
603,Patel,9,MG2,N7 8DB,93782084973
604,Charles,10,MG1,W7 8RT,9860839876
605,Lee,9,MG1,SW19 9AT,87964793201
606,McDonald,10,MG3,N20 8JH,73928374657
607,Jones,10,MG4,W6 8HD,07917635627
608,Ioannides,10,MG3,E4 9BG,07192837465
609,Tang,9,MG4,NW2 6FG,07726549876
610,Wilson,9,MG3,SE4 3GL,07816549165
611,Baker,9,MG4,E14 6KY,07626719837
612,Abraham,8,MG1,NW5 9CL,02876340982
613,Ismail,8,MG2,W8 3NL,02783651084
614,Ali,8,MG4,NE3 4BY,07823715692
615,Ali,8,MG3,SE3 1BU,0782764239
616,Demesse,8,MG3,W1 2ST,07298239810
INT
int_no, emp_no, int_date, int_time, job_no, decision
I12,609,12/04/2014,14:00,J11,?
I13,614,12/04/2014,15:00,J11,?
I14,615,12/04/2014,16:00,J11,?
I15,615,15/01/2014,14:00,J10,Y
I16,616,15/01/2014,15:00,J10,N
I17,606,15/01/2014,16:00,J10,N
JOB
job_no, job_title, grade_no, dept_no
J10,Office Manager,MG3,9
J11,Deputy Dept Manager,MG2,8
your task
Your Tasks
(1) Examine the case history given in a separate file and look for nouns that seem important to the design, to try to identify the entities. (6 marks)
(2) List each pair of entities that you think have a relationship between them. (6 marks)
(3) Draw the relationships you have identified in (2) above, showing cardinality and optionality if/where it exists. (10)
(4) Use them to draw an Entity Relationship Diagram, showing cardinality and optionality if/where it exists, as identified in (3) above. (6 marks)
(5) State any assumptions that you have made. (4 marks)
(6) List the likely attributes for each entity (you should also list the entities and attributes of the tables that you have been given (Interview, Job, Employee). (8 marks)
(7) Indicate the primary key (by underlining it) and any foreign keys (using an asterisk) for each entity. (10 marks)
(8) Implement your work using Access (50 marks)
DEADLINE TODAY!!!!