Question

ADVANCED DATABASE(ORACLE DATABASE 11g Masterkill)

Question 5 (Marks: 10) Mike is a database administrator who has recently joined an online car rental company who captured and stored data in Microsoft Excel spreadsheets. He is tasked with creating a database and migrating all the data into a relational database management system. Mike wants to implement an Oracle database, but is unsure of the Oracle 11g edition to use. Explain to Mike the various program categories that will enable him to operate a database in Oracle. Discuss at least three of these program categories. Question 6 (Marks: 5) Differentiate between Sequences and Synonyms and how they can be used to ensure better data management.

Answer 1

Answer-5)

Various Program categories to operate database in oracle are:-

1)Creating Programs:-You create programs by using the CREATE_PROGRAM procedure or Enterprise Manager. By default, programs are created in the schema of the creator. To create a program in another user's schema, you need to qualify the program name with the schema name..

Example:-

BEGIN DBMS_SCHEDULER.CREATE_PROGRAM ( program_name program_action program_type comments ); END;

2)Altering Programs:-You can use Enterprise Manager or the DBMS_SCHEDULER.SET_ATTRIBUTE and DBMS_SCHEDULER.SET_ATTRIBUTE_NULL  package procedures to alter programs...

Example:-

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE('program1,program2,program3');

END;

3)Dropping Programs:-You drop one or more programs using the DROP_PROGRAM procedure or Enterprise Manager...

Example:-

BEGIN DBMS_SCHEDULER.DROP_PROGRAM('program1, program2, program3'); END;

4)Disabling Programs:-You disable one or more programs using the DISABLE procedure or Enterprise Manager. When a program is disabled, the status is changed to disabled. A disabled program implies that, although the metadata is still there, jobs that point to this program cannot run..

Example:-

BEGIN DBMS_SCHEDULER.DISABLE('program1, program2, program3'); END;

5)Enabling Programs:-You enable one or more programs using the ENABLE procedure or Enterprise Manager. When a program is enabled, the enabled flag is set to TRUE...

Example:-

BEGIN DBMS_SCHEDULER.ENABLE('program1, program2, program3'); END;

Answer-6)

Sequences:- Sequences are database objects from which multiple users can generate unique integers. The sequence generator generates sequential numbers, which can help to generate unique primary keys automatically, and to coordinate keys across multiple rows or tables..

To use a sequence, your schema must contain the sequence or you must have been granted the SELECT object privilege for another user's sequence. Once a sequence is defined, it can be accessed and incremented by multiple users....

CREATE SEQUENCE Order_seq START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 20;

Synonyms:- A synonym is an alias for a schema object. Synonyms can provide a level of security by masking the name and owner of an object and by providing location transparency for remote objects of a distributed database..

Using synonym:

INSERT INTO emp (empno, ename, job) VALUES (emp_sequence.NEXTVAL, 'MICHAEL', 'KEVIN');

Droppig synonym:

 DROP SYNONYM emp;