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.