1. ) Using your own SalesRep-Worksin-SalesArea database instance from assignment 1, login to the SQL query processor on our cs server, called Oracle Sqlplus to create the three database tables and insert the tuples in your database state with the following sequence of instructions. Note that this exercise is to get you beginning to connect to SQLplus while preparing to learn full SQL language syntax in Chapters 6 and 7. You will be given the instructions to use now. Show the result of this exercise through a script file you will attach. (Total for que 2 is 10 marks)

i. First connect to our cs.uwindsor.ca through either SSH client or NoMachine

ii. Then create a script file to capture your Unix session and connect to Sqlplus with:

(Note that SalesArea here does not have city attribute. Thus, just type in only 4 attributes and values for it).

>script username_assn2que2 >sqlplus >password SQL> CREATE TABLE SalesRep

(SRid NUMBER(4) NOT NULL,

SRname VARCHAR2(15),

SRage NUMBER(2),

Salary NUMBER(8, 2),

PRIMARY KEY(SRid));

SQL> CREATE TABLE SalesArea

(Arid NUMBER(4) NOT NULL,

Aname VARCHAR2(15),

city VARCHAR2(15),

budget NUMBER(10,2),

managerid NUMBER(4),

PRIMARY KEY(Arid),

FOREIGN KEY (managerid) REFERENCES SalesRep (SRid));

SQL> CREATE TABLE WORKSIN

(SRid NUMBER(4) NOT NULL,

Arid NUMBER(4) NOT NULL,

Hours NUMBER(3,1) ,

PRIMARY KEY(SRid, Arid),

FOREIGN KEY (SRid) REFERENCES SalesRep (SRid),

FOREIGN KEY (Arid) REFERENCES SalesArea(Arid));

SQL> INSERT INTO SalesRep

VALUES (1111, John Smith, 22, 20000.60);

SQL> COMMIT;

// Repeat similar INSERT instructions for all the data in your 3 tables

// starting with the entity tables first, eg, SalesRep, SalesArea, before WORKSIN. After each INSERT, issue a COMMIT as done above.

SQL> select * from cat; // to show all the objects in your catalogue

SQL> select * from SalesRep; // to show the contents of this table

SQL> exit //to exit sqlplus

exit // to exit and create script file

** More Hint: While in Sqlplus, if you want to delete data from your tables and drop them for before issuing your instructions for your Unix script file for handing in, you can use the following instructions for each table to first delete the data from the table and then drop the table.

delete from Worksin; delete from SalesArea; delete from SalesRep;

commit;

drop table Worksin cascade constraints;

drop table SalesArea cascade constraints;

drop table SalesRep cascade constraints;

commit; *** Also Note: you can start creating a script file only after you have created your tables correctly and inserted data in the tables. In that case, you cannot re-create existing tables. Then, you can then just run the desc table (eg. Desc Member) commands for each table to show the structure of each table before using (for example), the (select * from Member; ) to show the tuples of each table or delete data and drop the tables as explained above so you can re-create the tables more correctly.

Solution: (10 marks)

An attached script showing execution of CREATE TABLE instructions and INSERT INTO table instructions with the few SELECT instructions to show contents of the catalogue and tables. The results of these queries should be displayed in the script file too.