A Case Study:

You have been given the responsibility of constructing a database grounded on the ERD diagram

$($refer to page $4)$ provided by Provisional Employment. Some reports are also required by

Provisional Employment to make strategic decisions and improve the efficiency of everyday

operations. You must do the following to obtain this:

Requirements:

R$1.$ Notepad will be used to construct the three SQL batch$/$script files outlined below. You

will run these files through the MySql shell to see how they work. It's possible to utilize

comments$/$remarks to explain what queries are written using $[--].$

R$2.$ Create a Notepad text file called GroupX$\_$A$2\_$CreateStructures.sql$.$ The Xs represent your

Group Name. In this file, you will write all your SQL queries to only generate the table

structures as given in the solution ERD $[$Figure $1].$ This task is part of DDL$.$ Note the

following:

Table $1$: Details the requirement for the Notepad text file $[$GroupX$\_$A$2\_$CreateStructures.sql$].$

$*$ X represents your Group Name

No$.$ Requirements

$1.$ When you run your script, all the tables should be created with their constraints as

given in the ERD.

$2.$ Add comments to indicate which SQL statements correspond to which tables using $--.$

$3.$ Attribute$/$entity names should be similar if not the same as in the ERD. You can also

add any additional useful attributes to the given tables and would not necessarily need

to remove any existing ones. Consider any such changes that you introduce carefully

$3|$ P a g e

$4.$ The basic structure in the given ERD must be captured in your implementation as

given $($page $4).$ Any significant changes, such as additions of extra attributes or

entities, must be clearly and very briefly explained with comments in the script where

the relevant SQL statement is$.$

$5.$ You need the SQL statements to create the tables here. Any other types of statements

may not be needed.

$6.$ Test out your script in the MySQL shell. When it runs, it is expected to create all the

tables with all their required constraints without any errors. Avoid syntax errors.

R$3.$ Create another text file in Notepad called GroupX$\_$A$2\_$InsertData.sql$.$ The X represents

your Group Name. In this file, you will write all your SQL statements to populate the tables

created in the first step with data. This script will only be executed after the first script

above. Note the following:

Table $2$: Details the requirement for the Notepad text file $[$GroupX$\_$A$2\_$InsertData.sql$].*$

X represents your Group Name.

No$.$ Requirements

$1.$ The data to be inserted in each table is entirely up to you but should be adding the

realistic data pertinent to the scenario. Add only $10$ records per table and follow any

constraints given in the scenario in ERD. Also, add comments$/$remarks to indicate

which statements correspond to which tables using $--.$

$2.$ Avoid any code that ignores any particular constraint checks when inserting data.

$3.$ Test out your script to see if it works without errors $($syntax or other$)$

R$4.$ To create the queries that will facilitate the extraction of information for reports follow

the instructions given below and create a text file called GroupX$\_$A$2\_$Queries.sql$.$ The Xs

represent your Group Name. Write in this file, that all the queries below are based strictly

on the given ERD.

$4|$ P a g e

Table $3$: Details the requirement for the Notepad text file $[$GroupX$\_$A$2\_$Queries.sql$].*$ X

represents your Group Name.

No$.$ Requirements

$1.$ Add comments to indicate the query you are writing, eg$.$ Query $1,$ Query $2,$ etc. If the

query is ambiguous, you may not get any credit for that query.

$2.$ Make sure your queries have no syntax errors. Avoid any gratuitous elements in your

queries where possible. Test them out.

$3.$ Each query is to generate results based on the requirements given below:

ERD Guide: Provisional Employment

Figure $1$: A ERD Model for Provisional Employment.

$5|$ P a g e

Queries:

Write the following queries to make information extraction easier for report generation:

Q$1.$ The executives at Provisional Employment want a list of all the candidates and their

qualifications. The candidates' qualifications should be sorted in ascending order on the

list.

Q$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.

Q$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 Candidate's details, their qualifications

the company's details, and the qualifications they are looking for.

Q$4.$ 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.

Q$5.$ To help finalize financial record