CSCI235 Assignment 2

The tasks of this assignment cover the implementation of SQL, database reverse engineering, modifications of database structure, data entry, simple data manipulations, and implementation of simple queries.

To complete this task, you need to download oracle database server in order to execute SQL command.

You are given with 2 SQL files (a2create.sql) and (a2drop.sql). The first file contains CREATE TABLE statement of SQL and the second file contains DROP TABLE statement that delete all tables created by the first SQL file when they are no longer needed.

First Task

It is about database reverse engineering. Based on the SQL file given, you have to analyze the SQL and discover a conceptual schema (E-R) diagram of the sample database. You are required to determine the keys (identifiers) of entity sets, names of relationships, key constraints, types of relationships (1:1, 1:M, M:N), weak entity sets, identification relationships, and class (ISA) hierarchies (if any). The solution should be similar to the diagram below.

Second Task

This task is about structural modifications of relational tables. For example:

-- Implement SQL script addsalary.sql that adds a column SALARY NUMBER(7,2) to relational table EMPLOYEE.
SQL> ALTER TABLE employee ADD SALARY NUMBER(7,2);

This above SQL statement will alter the table EMPLOYEE with additional column for salary with numerical data type that can accept 2 decimal numbers.

Third Task

This task is about creating relational tables and data entry operations. For example,

--we would like to store information about administration employees. Each admin employee is described by employee number (E#), name (NAME), date of birth (DOB), address (ADDRESS), and hired date (hiredate).

SQL>CREATE TABLE admin_employee(
E# NUMBER(12,0) NOT NULL,
NAME VARCHAR(50) NOT NULL,
DOB DATE,
ADDRESS VARCHAR(200),
HIREDATE DATE NOT NULL);

The above statement will create a table called for employee information.


Fourth Task

This task is for data manipulation operations. Since after we have constructed the database structure, it is now time to manipulate the data. For example,

-- Implement a parameterised SQL addtruck.sql script that prompts about full information describing a truck and inserts a new row into table TRUCK. Execute script addtruck.sql

SQL>INSERT INTO truck VALUES('&reg',&capacity,&weight,'&status');
Enter value for reg: ABC123
Enter value for capacity: 45000
Enter value for weight: 3000
Enter value for status: USED
old1: INSERT INTO truck VALUES('&reg',&capacity,&weight,'&status')
new1: INSERT INTO truck VALUES('ABC123',45000,3000,'USED')


Fifth Task

The last task of the assignment is about data retrieval operations using SELECT statement of SQL. For example

-- Find the names of all employees (NAME) born between 1950 and 1960

SQL>SELECT NAME, DOB FROM employee WHERE EXTRACT (YEAR FROM DOB)>1950 AND EXTRACT (YEAR FROM DOB)<1960;

All the above tasks have to submit in hard copy for assessment.