Task 1: Use the following Create Table commands to create below tables. Show the content of each table. (no marks)

Use the data to load the data into the database.

create table salesperson

(sno number not null, name varchar2(20) not null, zip number, primary key(sno));

create table customers

( cno number (3) not null, companyname varchar2(30) not null, primary key(cno));

create table orders

(orderno number(3) not null,

cno number (3) not null,

sno number (3) not null,

received date, shipped date,

type char(1), check (type in ('C','S','E','R')

primary key (orderno),

foreign key (cno) references customers,

foreign key (sno) references salesperson);

create table items

(orderno number (5) not null,

itemno number (2),

quantity number (3),

prince number (9,2),

foreign key (orderno) references orders);

create table transactions

( tid number(5) not null,

orderno number(3) not null,

cno number(3) not null,

sno number(3) not null,

received date, shipped date,

itemno number(2),

quantity number (3),

prince number (9,2),

type char(1),

primary key(tid, orderno, cno, sno)

);

Task 2: create a set of insert commands and fill the above tables as below: (5 marks)

• 5 sales person
• 5 customers

Task 3: write a stored function called init_func to read a text file containing the transactions and then insert them into the transaction table.

Write another stored function called order_func which reads through transaction table and fills the order and item tables.

The function should return true if it finishes without a problem otherwise it should print appropriate message by capturing relevant exceptions. You need to think about what exceptions could happen and take care of them in exception handling. The transaction id is a generated sequence. For this task you need to create a comma separated text file that each row in it corresponds to a row in transactions table. This text file contains:

• 30 transactions (which includes 15 orders, each with 2 items, 3 orders for each customer)

(25 marks)