IS222 Assignment 2 Semester 1, 2017 Designing a Website for a client Due: 11:55

Question

IS222 Assignment 2 Semester 1, 2017 Designing a Website for a client Due: 11:55 PM, Friday 19th May Weight: 15% _____________________________________________________________________________________ Aim: To implement a Database based on a client’s requirements. Objectives _____________________________________________________________________________________ The assignment will allow you to demonstrate and exhibit that you: Can analyze requirements and design and implement a database that caters for those requirements Can conduct independent research as part of your requirements analysis Have understood the basic principles of database design and implementation and have applied them to your design Scenario

VII. Query 6: For each of the identified candidates that require further education, management will need to inform each student of the courses, when each course will start, the respective cost of each course and the total cost of the required courses. The list is to be ordered by the candidate’s last name in ascending order.

VIII. Query 7: Management needs a list of all the candidates who are currently pursuing studies and the respective qualifications that they are studying. For qualifications that require multiple courses, management needs to know how many courses each student has completed/enrolled in.

IX. Query 8: To help finalise financial records for the 1st quarter of the year, management needs to know candidates who have not paid their fees in order to contact them.

X. Query 9: For each opening, management will need to determine the most suitable candidate/s. Each potential candidate will then be further evaluated to ensure maximum suitability. List all the companies that have current openings and the details, qualifications and number of successful placements of candidates that could be shortlisted for these openings.

XI. Query 10: Marketing department needs three of the most successful candidates and three of the most successful companies to feature in the latest edition of TE’s newsletter and website.

XII. Query11: To help determine the average pay rate based on qualifications for the respective companies, management needs a list of all the companies, the qualifications they are requesting for the openings and the average pay for openings by each company.

PREREQUISITE COURSE PK,FK1 COURSE NUM PK COURSE NUM uires PK,FK2 QUAL CODE >O COURSE DESCRIPTION COURSE FEE FK1 QUAL CODE is required as QUALIFICATION generates COMPANY s taught in PK coMP CODE PK QUAL CODE QUAL DESCRIPTION COMP NAME S Wr en in SESSION PK SESSION NUM EDUCATION OPENING SESSION STARTDATE PK, FK1 QUAL CODE. SESSION FEE PK PK,FK2 CAND NUM FK 1 COURSE NUM OPENING DESCRIPTION EDUC DATE OPENING AVAILABLE FK1 COMP CODE FK2 QUAL CODE ns is fi CANDIDATE PK CAND NUM is placed in PLACEMENT CAND LNAME PK cretes combetes ENROLL PLACEMENT DATE OPENING NUM FK1 PK,FK1 SESSION NUM FK2 CAND NUM PK,FK2 CAND NUM PLACEMENT TOTALHOURS JOBHISTORY ENROLL DATE ENROLL FEE PAID PK JHLID. produces FK1 CAND NUM PLACEMENT NUM FK2

Explanation / Answer

Hi,

Please find below the answers. Due to lack of time, I could only write the SQL queries answers as per the ERD diagram. Please find the queries below-

Ans 1- select cand_num,cand_lname from candidate where cand_num not in(select cand_num from placement);

Ans 2- select cand_num,cand_lname,s.course_num,cou.course_description,cou.course_fee,session_fee
from candidate c join enroll e
on c.cand_num=e.cand_num
and c.cand_num not in(select cand_num from placement)
join session s
on e.session_num=s.session_num
join course cou
on s.course_num=cou.course_num
order by cand_lname;

Ans 3 - select c.cand_num,cand_lname,q.qual_code,q.qual_description,count(course_num) as Number_of_couses
from candidate c join education e
on c.cand_num=e.cand_num
join qualification q
on e.qual_code=q.qual_code
join course cou
on q.qual_code=cou.qual_code
group by c.cand_num,cand_lname,q.qual_code,q.qual_description

Ans 4- select c.cand_name,c.cand_lname from candidate c
join enroll e
on c.cand_num=e.cand_num
and e.enroll_feepaid='No'

Ans 5 - select c.comp_code,comp_name, opening_num,opening_Description,q.qual_code,q.qual_description ,count(e.cand_num) as No_of_potential_candidates from
company c join opening o
on c.comp_code=o.comp_code
join qualification q
on o.qual_code=q.qual_code
join education e
on q.qual_code=e.qual_code
group by c.comp_code,comp_name, opening_num,opening_Description,q.qual_code,q.qual_description;

Ans 6-
Top 3 candidates --
SELECT cand_num,
COUNT(jh_id) AS value_occurrence
FROM jobhistory
GROUP BY cand_num
ORDER BY value_occurrence DESC
LIMIT 3;

Top 3 companies--
select comp_code from placement where placement_num in(SELECT placement_num,
COUNT(jh_id) AS value_occurrence
FROM jobhistory
GROUP BY placement_num
ORDER BY value_occurrence DESC
LIMIT 3);

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

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