How to INSERT INTO A DATABASE an information with SQL procedure ?

Question

Can someone please help me how to do this?
Just give me the basic layout how to implement the code would also help me a lot..
Here is the question :

Implement a stored PL/SQL procedure that insert into a database full information about an employee i.e., the values of the following attributes E#, NAME, DOB, ADDRESS, HIREDATE, L#, STATUS, EXPERIENCE (only for mechanics).

Your procedure should enforce a logical consistency constraint saying that "the sets of drivers and mechanics must be disjoint". It means that it is not allowed to have in the tables MECHANIC and DRIVER the rows with the same employee number (E#) and the same driving license number (L#).

Execute your procedure twice. The first execution should insert full information about a new employee. The second execution should fail due to the violation of logical consistency constraint given above.

CREATE TABLE TRKEMPLOYEE(
E# NUMBER(12) NOT NULL,
NAME VARCHAR(50) NOT NULL,
DOB DATE ,
ADDRESS VARCHAR(300) NOT NULL,
HIREDATE DATE NOT NULL,
CONSTRAINT TRKEMPLOYEE_PKEY PRIMARY KEY(E#) );

CREATE TABLE TRKDRIVER(
E# NUMBER(12) NOT NULL,
L# NUMBER(8) NOT NULL,
STATUS VARCHAR(10) NOT NULL,
CONSTRAINT TRKDRIVER_PKEY PRIMARY KEY(E#),
CONSTRAINT TRKDRIVER_UNIQUE UNIQUE(L#),
CONSTRAINT TRKDRIVER_FKEY FOREIGN KEY(E#) REFERENCES TRKEMPLOYEE(E#),
CONSTRAINT TRKDRIVER_STATUS CHECK ( STATUS IN
('AVAILABLE', 'BUSY', 'ON LEAVE')) );

CREATE TABLE TRKMECHANIC(
E# NUMBER(12) NOT NULL,
L# NUMBER(8) NOT NULL,
STATUS VARCHAR(10) NOT NULL,
EXPERIENCE VARCHAR(10) NOT NULL,
CONSTRAINT TRKMECHANIC_PKEY PRIMARY KEY(E#),
CONSTRAINT TRKMECHANIC_UNIQUE UNIQUE(L#),
CONSTRAINT TRKMECHANIC_FKEY FOREIGN KEY(E#) REFERENCES TRKEMPLOYEE(E#),
CONSTRAINT TRKMECHANIC_STATUS CHECK ( STATUS IN
('AVAILABLE', 'BUSY', 'ON_LEAVE')),
CONSTRAINT TRKMECHANIC_EXPERIENCE CHECK ( EXPERIENCE IN
('BEGINNER', 'STANDARD', 'EXPERT')) );

Truly appreciate any of your help
Thank you

Answer 1

The procedure would be passed the listed attributes...if EXPERIENCE is null, that indicates it's a driver. First, check that the E#/L# combination you intend to insert in TRKDRIVER/TRKMECHANIC does not already exist in TRKMECHANIC/TRKDRIVER (the disjoint business rule) - if it does, reject the insert. Otherwise, insert into TRKEMPLOYEE and whichever of the other two tables is appropriate.



Note: another way this could have been done is via INSERT triggers on the two related table. There's also the 'assertion' construct in the latest SQL standard (basically a multiple-table constraint) but unaware of any DBMS that supports it yet.

Answer 2

SQL is quite an easy language, but there is one prerequisite which is to have good database concepts, i would suggest you to first understand what is database and learn about its structure and how it works, then move to SQL language. use yahoo or google to search the database and sql tutorial you will find many.

Answer 3

This website should help, it helps me alot

http://www.php-mysql-tutorial.com/wikis/mysql-tutorials/default.aspx