POLITEKNIK POS INDONESIAUjian Akhir Semester Ganjil 2013/2014

JURUSAN : TEKNIK INFORMATIKAMATA KULIAH : BASISDATA 2

HARI TANGGAL : 5 Pebruari 2014

WAKTU : 120 Menit

SIFAT : Tutup BUku

1. Kerjakan soal dari yang termudah2. Kegiatan yang tercela (menyontek dan kerjasama) akan mengakibatkan lembar jawaban tidak dinilai

secara keseluruhan (Nilai UAS = 0)3. Berdo’a sebelum mengerjakan soal !

____________________________________________________________________________

1. Which of the following types of statements can use a temporary tablespace?A. An index creationB. SQL statements with a GROUP BY clauseC. A hash join operationD. All of the above

2. Which of the following system privileges can allow the grantee to masquerade as another userand therefore should be granted judiciously?A. CREATE ANY JOBB. ALTER USERC. CREATE ANY PROCEDURED. All of the above

3. Which of the following is not an object privilege on a table?A. SELECTB. DEBUGC. REFERENCESD. READ

4. Where can the database write audit_trail records?A. In a database tableB. In a file outside the databaseC. Both in the database and in an operating system fileD. Either in the database or in an operating system file

5. Which of the following activities can be audited?A. Unsuccessful deletions from the audit_trail tableB. Unsuccessful selects from the employee_salary tableC. All GRANT and REVOKE statements on procedures executed by user systemD. All of the above

6. Which of the following conditions prevents you from being able to insert into a view?

A. A TO_NUMBER function on one of the base table columnsB. A CONNECT BY clause in the view definitionC. A column of type RAWD. All of the above

7. Changes made with an UPDATE statement are made permanent in the database after what occurs?

A. DBWR flushes the changes to disk.B. You issue a SAVEPOINT statement.C. You issue a COMMIT statement.D. A checkpoint occurs.

8. Which of the following is not a PL/SQL program?A. LibraryB. TriggerC. FunctionD. Procedure

9. Why would you execute a CREATE OR REPLACE PROCEDURE statement instead of a DROPPROCEDURE and a CREATE PROCEDURE?A. It is less typing.B. There is no difference between the two.C. CREATE OR REPLACE PROCEDURE does not invalidate dependent objects.D. DROP PROCEDURE and CREATE PROCEDURE require regranting of privileges.

10. Which of the following is not a trigger event?A. UPDATEB. SELECTC. NOAUDITD. SERVERERROR

11. Which of the following statements can be used to enable triggers?A. ALTER SYSTEMB. ALTER TABLEC. ALTER USERD. ALTER PACKAGE

12. Which of the following is not a mode for Data Pump export?A. DatabaseB. TablespaceC. TableD. Metadata

13. Which type of PL/SQL program can be called in the select list of a SQL statement?A. TriggerB. ProcedureC. FunctionD. None of the above

14. Which of the following INSERT statements raises an exception?A. INSERT INTO ORDERS SELECT * FROM STANDING_ORDERSB. INSERT FIRST WHEN ORDER_TYPE IN (2,5,12) THEN INSERT INTO ORDERS SELECT *FROM STANDING_ORDERSC. INSERT FIRST WHEN ORDER_TYPE IN (2,5,12) THEN INTO ORDERS SELECT * FROMSTANDING_ORDERSD. INSERT INTO ALL WHEN ORDER_TYPE IN (2,5,12) THEN INTO ORDERS SELECT * FROMSTANDING_ORDERS

15. Which of the following commands returns an error if the transaction starts with SET TRANSACTIONREAD ONLY?A. ALTER SYSTEMB. SELECTC. ALTER USERD. SET ROLE

16. The highest level at which a user can request a lock is the ________ level.A. SchemaB. TableC. RowD. Block

17. Which of the following commands returns control to the user immediately if a table is alreadylocked by another user?A. LOCK TABLE HR.EMPLOYEES IN EXCLUSIVE MODE WAIT DEFERRED;B. LOCK TABLE HR.EMPLOYEES IN SHARE MODE NOWAIT;C. LOCK TABLE HR.EMPLOYEES IN SHARE MODE WAIT DISABLED;D. LOCK TABLE HR.EMPLOYEES IN EXCLUSIVE MODE NOWAIT DEFERRED;

18. Which of the following is not one of the three primary sources of performance metric informationin the EM Database Control Performance screen?A. HostB. SessionC. InstanceD. Network

19. Your users have called to complain that system performance has suddenly decreased markedly.Where would be the most likely place to look for the cause of the problem in the EMDatabase Control?A. Main screenB. Performance screenC. Administration screenD. Maintenance screen

20. Using EM Database Control, you’ve identified that the following SQL statement is the source ofa high amount of disk I/O:SELECT NAME, LOCATION, CREDIT_LIMIT FROM CUSTOMERSWhat might you do first to try to improve performance?A. Run the SQL Tuning Advisor.B. Run the SQL Access Advisor.C. Check the EM Database Control main screen for alerts.D. Click the Alert Log Content link in the EM Database Control main screen.

21. The option on the EM Database Control backup scheduling options screen that allows you torefresh an image copy on disk with an incremental backup is known as which new RMAN feature?A. Incrementally updated backupsB. Incremental level 0 backupsC. Compressed image copy refreshD. Compressed incremental backups

22. In the Disk Settings section of the EM Database Control Configure Backup Settings page, whichof the following backup settings is not configurable?A. Disk Backup TypeB. Control File Autobackup FormatC. Disk Backup LocationD. Parallelism

23. Which of the following pieces of information is not available in the control file?A. Instance nameB. Database nameC. Tablespace namesD. Log sequence number

24. What is the biggest advantage of having the control files on different disks?A. Database performance.B. Guards against failure.C. Faster archiving.D. Writes are concurrent, so having control files on different disks speeds up control file writes.

25. Which of the following can help prevent database network failures? (Choose all that apply.)A. Configure a backup listener process on the server.B. Open more than one session when updating the database.C. Configure multiple network cards on the server.D. Create a standby database.

SOAL Essay

TABEL BUKU

KODE_BUKU JUDUL STATUS_PINJAM

DB001 Database system Y

AK001 Arsitektur computer T

AL001 Algoritma Pemograman T

MU001 Multimedia dan grafik T

MT001 Matematika Diskrit T

ST001 Sistem Telekomunikasi Y

TABEL PEMINJAM

Kode_Peminjam Nama_peminjam No_Anggota Alamat Telp

P0001 Asep A090001 Jl. Cijerah 30 0226005976

P0002 Dian A090002 Jl. Pajajaran 50 0224736358

P0003 Sahrini A090003 Jl. Sukaraja 12 0226734377

P0004 Yanti A090004 Jl. Sarijadi 23 0227436436

TABEL PEMINJAMAN

Kode_pinjam Tgl_pinjam Kode_peminjam Kode_buku Lama_pinjam Tgl_kembali Denda

Pinj-001 21/3/2008 P0001 DB001 5 27/3/2008 500

Pinj-002 21/3/2008 P0002 AK001 5 26/3/2008

Pinj-003 21/3/2008 P0003 AL001 5 28/3/2008 1000

Pinj-004 21/3/2008 P0004 MU001 5 26/3/2008

Pinj-005 22/3/2008 P0001 MT001 5 30/3/2008 1500

Pinj-006 23/3/2008 P0003 ST001 5 28/3/2008

1. Gambarkan ER Diagram dari skema database di atas

2. Buat DML untuk :

a. Tampilkan daftar seluruh buku

a. Tampilkan daftar peminjaman buku yang di kenakan denda

b. Tampilkan daftar buku yang tidak di pinjam

c. Tampilkan daftar seluruh buku yang melebihi lama pinjam

d. Tampilkan data peminjam yang meminjam buku sebanyak 1 kali

e. Tampilkan data peminjam yang meminjam buku lebih dari satu buku

3. Apa beda database schema dan dan database state

4. Apa perbedaan Two-tier architecture dengan three-tier architecture

5. Sebuah data di sebut “self describing” sebab database tsb dapat mengurangi duplikasi data

pernyataan tsb benar atau salah berikan penjelasannya

6. Pada “two-tier architecture” agar pemrosesan lebih cepat maka digunakan “application Server”

di samping Database Server benar atau salah berikan penjelasannya.

7. Data Model merupakan representasi phisik (physical representation) dari struktur basis data .

pernyataan tsb benar atau salah berikan penjelasan

8. Apa bedanya multi user database dengan single user database?