Assignment king hired auditor and will earn month

FIT9132 Database Technology Semester 1, 2015

• use appropriate Data Definition Language (DDL) constructs to define table structure, primary key, foreign key and additional integrity constraints as suggested by the business process.

• use appropriate SQL data manipulation statements (insert, update, delete) to maintain data in the database to reflect changes that occur in an organisation or business.

You are to complete the following tasks, related to data definition and data manipulation:

a) Identify the relation for which CREATE TABLE statement is missing in the script file.

b) Provide the missing CREATE TABLE statement at an appropriate location in FIT9132A1_create.sql so that the database can be implemented fully. You will also have to include constraints that you think are relevant and, for the same,

Task 2: Data Manipulation (20 + 30 + 30 = 80 marks)

Source File(s): FIT9132A1_insert.sql that includes INSERT INTO statements for all the tables that you have implemented in Task 1. Execute this insert script before attempting task a-c.

b) For the following scenario, write down the steps, in correct order, that should be performed to achieve the following requirements and then these steps should be translated appropriately into SQL statements:

There are two new offerings of the “Introduction to SQL” course. The first training course will start on 01-April-2015. The training will be provided by MG Ford and will be conducted in Chicago. The second training course will start on 15-April-2015, will be conducted by MG Ford and will be located in New York.

The company has also just opened a new department called MARKETING in Chicago. One new employee, Thomas Paine (DOB: 09-Feb-1973), has been hired to work in this department. He has been appointed as the head of this new department. His job title is manager. He is reporting directly to the Director. He earns 3000/month.

c) For the following scenario, write down the steps, in correct order, that should be performed to achieve the following requirements and then these steps should be translated appropriately into SQL statements:

Mr. Abraham Lincoln (DOB: 12-Feb-1980) successfully completed the “Introduction to SQL” course that was offered on 01-Apr-2015 and received the highest rating.

You should include all the steps (as comments) and SQL statements to complete this part in the FIT9132A1_delete.sql file provided to you.

After executing FIT9132A1_delete.sql, show that the changes made to the database have been done correctly by displaying the contents of the modified tables using the SELECT statements. Save the output of the SELECT statements and name the output file FIT9132A1_delete_OUT.txt

Following files are to be submitted as part of assignment 1:

• Completed FIT9132A1_create.sql
• Completed FIT9132A1_sequence.sql
• Completed FIT9132A1_insert_new.sql
• Completed FIT9132A1_delete.sql
• The output files produced after the execution of the SQL scripts in task a, b and c.

Appendix A

The descriptions of the attributes in different entities are given below.

OFFERING
offbegindate : Begin date for offering
crscode : Course Code
offlocation : Location for offering
empno : Employee number for employee running offering

REGISTRATION
offbegindate : Begin date for offering (references OFFERING.offbegindate)
crscode : Course Code
regevaluation : Grade for course completed
empno : Employee number of employee completing course