Database Coursework

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!!!!