Scenario _______________________________________________________________________

Question

Scenario

___________________________________________________________________________________

Based on Temporary Employment’s(TE) ERD diagram you have been tasked with creating a database that will capture the basic structure given in the ERD. TE also require some reports for strategic decision making and improving efficiency of daily operations. To achieve this you are required to:

Use DDL to create the table structures and populate them.

Use DML to create queries that will facilitate extraction and presentation of required information.

       

Figure 1: Temporary Employment ERD

Queries

__________________________________________________________________________________

To facilitate information extraction for report generation, write the following queries:   

Query 0: Using DDL, create TE’s database and insert at least 10 records into each table. For each table column you will need to use appropriate data types and values. Your tables should enforce entity and referential constraints.

Query 1: TE’s management would like a list of all the candidates and their qualifications. The list should be sorted in ascending order of the candidate’s qualifications.

Query 2: Management would like to know the companies that have employed any of their candidates. The list should be sorted by company name in ascending order.

Query 3: To help management determine if the candidates’ qualifications are in line with those that the companies a looking for a list is required of the Candidates details, their qualifications the companies details and the qualifications they are looking for.  

Query 4: The candidates contact details haven’t been recorded in the database. Candidate’s table will need to include 2 new columns to facilitate recording of email address and phone number. Implement the required columns and add these contact details for all the candidates.

Query 5: Management needs to know the candidates who haven’t been successfully placed into employment with any of the companies. These identified candidates will then be contacted and encouraged to enroll into courses that will help provide the required qualifications and improve the candidates’ chances of a successful placement. List these candidates and the recommended courses that they should be encouraged to enroll in.

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.

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.

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.

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.

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.

Query 11: 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

DDL Commands:-

The above Create DDL commands will create the tables in your database.

The below is the insert command which inserts data into your tables. Below is the syntax based on that insert values into your database.

INSERT INTO <TABLE_NAME> VALUES(COLUMN1, COLUMN2,COLUMN3,..........)

Example:-- Insert into comapny values(12345,'chegg');

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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