UOW CSIT115/CSIT815 Autumn 2018: Data Management - MySQL DDL and DML

This document presents a solution to a MySQL Data Definition Language (DDL) and Data Manipulation Language (DML) assignment, likely completed for the CSIT115 or CSIT815 Data Management and Security course at the University of Wollongong (UOW) during the Autumn 2018 session. The assignment involves tasks such as importing a database, creating and modifying tables, adding constraints, inserting, updating, and deleting data using SQL queries. Specific operations include adding a 'TotalApplications' column to the 'applicant' table, creating a constraint to limit the number of applications, modifying the 'specification' column in the 'positions' table, and establishing a foreign key relationship between 'applicant' and 'skill' tables. The solution also covers inserting new skills, updating applicant information, and deleting position records. The document includes the SQL commands used to perform these tasks, demonstrating practical application of DDL and DML concepts in MySQL.

Running head: MYSQL DDL
MySQL DDL
Name of the Student:
Name of the University:
Author Note

1
MYSQL DDL
Task 1
For the first task the database is to be imported in model
Command: use cseit115;
After the database is use the dbdrop.sql is to be run to check if any existing tables are left in the
system.

2
MYSQL DDL
No tables are present
The dbcreate.sql file is executed in the system.
Now the solution1.sql file is run on the system

3
MYSQL DDL
The sql querries included in the solution1.sql are provided below:
ALTER TABLE `applicant` ADD `TotalApplications` INT NOT NULL AFTER `email`;
ALTER TABLE applicant ADD CONSTRAINT app_const CHECK(TotalApplications <= 3);
ALTER TABLE `positions` CHANGE `specification` `specification` VARCHAR(1500)
CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
ALTER TABLE `positions` CHANGE `bonus` `bonus` DECIMAL(10,2) NULL DEFAULT
NULL;
ALTER TABLE `applicant` ADD `Is-an-expert-in` VARCHAR(30) NOT NULL AFTER
`TotalApplications`;
ALTER TABLE `applicant` ADD CONSTRAINT `fk_12` FOREIGN KEY (`Is-an-expert-in`)
REFERENCES `skill`(`sname`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `applicant` DROP `fax`;

4
MYSQL DDL
After the file is run successfully the task is completed.
Task 2
For the second task the following command is to be run at first:
use cseit115;
After Successful execution of the command the database is use;
The dbdrop.sql file is to be executed in the system.
The tables are dropped. Now the tables are to be recreated.

5
MYSQL DDL
The tables are created in the system. Hence the data is required to be loaded in the system.
After the data have been inserted into the system the solution2.sql file is to run on the system to
obtain the results.

6
MYSQL DDL
The sql commands used in the solution2.sql file are:
INSERT INTO `skill` (`sname`) VALUES ('climbing');
INSERT INTO `srequired` (`sname`, `requires`, `slevel`) VALUES ('climbing', 'thinking', '3');
INSERT INTO `spossessed` (`anumber`, `sname`, `slevel`) VALUES ('7', 'climbing', '3');
DELETE FROM `positions` WHERE (`positions`.`pnumber`) = '1';
UPDATE `applicant` SET `city` = 'Sydney', `email` = NULL, `fax` = NULL WHERE
(`applicant`.`city`) = 'Melbourne';
INSERT INTO `skill` (`sname`) VALUES ('Deep thinking');
UPDATE `spossessed` SET `sname` = 'Deep thinking' WHERE `spossessed`.`sname` =
'thinking';

7
MYSQL DDL
UPDATE `srequired` SET `requires` = 'Deep thinking' WHERE `srequired`.`requires` =
'thinking';
DELETE FROM `skill` WHERE `skill`.`sname` = 'thinking';

8
MYSQL DDL
Bibliography
1 Dhanadhya AS, Chatur PN. Enhancing Information Management System performance by
specializing the concept of Indexing. International Journal Of Engineering And Computer
Science. 2015 Jan 1;4(01).
2 Geßner S, Neuhaus P, Varghese J, Bruland P, Meidt A, Soto-Rey I, Storck M, Doods J, Dugas
M. The Portal of Medical Data Models: Where Have We Been and Where Are We Going?.
Studies in health technology and informatics. 2017;245:858-62.
3 Letkowski J. Doing database design with MySQL. Journal of Technology Research. 2015 Jan
1;6:1.
4 Lilani J, Duggal S, Zanje S, Gite BB. Data Integration, Visualization and Analysis: Combined
in a Single Tool.
5 Ocampo AK, Ku CS, Basch CH, Kecojevic A. Data Analytics for YRBS (Youth Risk Behavior
Survey) Data using Machine Learning and Data Mining Techniques.
6 Raza SM, Rajamanickam L. A Proposed Solution for Crime Reporting and Crime Updates on
Maps in Android Mobile Application. International Journal of Computer Applications. 2015 Jan
1;124(1).
7 Yang L, Cao L. The Effect of MySQL Workbench in Teaching Entity-Relationship Diagram
(ERD) to Relational Schema Mapping. International Journal of Modern Education and Computer
Science. 2016 Jul 1;8(7):1.