SAK 3405 - Database Systemup

 

Home | Course Taught | Research Interests Distance Education

 

 

RINGKASAN SETIAP TOPIK

Berikut adalah ringkasan kandungan setiap topik:

Topik 1. Bab ini mengandungi pengenalan kepada Pangkalan Data. Terminologi yang digunakan dan konsep-konsep mestilah Difahami dengan baik. Perkara seperti mengapa diperlukan pangkalan data, apakah
komponen persekitaran database, database languages, fungsi-fungsi DBMS, perlulah difahami.
I want to ask you, what is database and what is DBMS?

Topik 2. Of course, you should know the definition of data model. The main focus of the chapter is how to develop E-R diagram from the given problem. Before you draw an E-R diagram, you must identify the main components of E-R which are:
entity sets,
attrbutes, and relationships.
You can skip model data hirarki dan rangkaian.

Topik 3. You should understand the concept of relational model and know how to manipulate relation using relational algebra operators:
union, set difference, projection, selection, join, etc.
You should also know how to express query in relational algebra.
For example, given a relation EMPLOYEE(Emp_ID, Ename, Address)
Query: List all employees’ name.
The above query is expressed in relational algebra as follows:
PEname (EMPLOYEE)

Another type of theoretical query languages is relational calculus.

Topik 4. SQL. This is very important chapter. You must be able to express
the query in SQL. SQL consists of 4 basic command
SELECT
UPDATE
DELETE
INSERT.

Topik 5. Normalisation
(Rujuk: Modul: Bahagian 5.2
Elmasri Chapter 14 )

Normal Form yang dikaji ialah 1NF, 2NF, 3NF, BCNF dan 4NF sahaja.

In normalization, the key concepts are:

Data redundancy (update anomalies)
Functional dependency
Full functional dependency
Transitive Dependency
Primary key.

What must you know about normalization? Given a relation, you should be able to identify the normal form of the relation and if necessary, convert it into the higher normal form.

Nota: Anda boleh skip Section 5.8 (5NF) daripada modul.


Topik 6. Database Design
(Elmasri: Chapter 16: Practical Database Design and Tuning)

This topic covers database application life cycle in which database design phase is very crucial. Database design phase consists of:

1. Requirement collection and analysis (Pengumpulan dan analisis keperluan)
2. Conceptual database design
3. Choice of DBMS
4. Data model mapping
5. Physical Database design
6. Database system implementation.

Anda mestilah memahami fungsi dan hasil setiap langkah di atas. Data Model mapping dapat difahami dengan baik jika anda membuat latihan. Section 5.1 modul mengandungi maklumat yang berkaitan tetapi tidak mencukupi.


Topik 7. Transaction Management
(Elmasri: Chapter 19 dan Chapter 20)

Anda mesti dapat memberi pengertian transaksi dengan jelas. Secara ringkasnya, transaksi ialah satu unit kerja yang terdiri daripada tindakan pangkalan data (delete, update, retrieve), samaada semua tindakan itu dilaksanakan atau tiada satu pun tindakan itu dilaksanakan. Ini dipanggil sifat atomicity.

A transaction has 4 properties:
Atomicity, consistency, isolation and durability (ACID)

Two issues related to transactions, i.e concurrency control and recovery.

If concurrency control mechanism is not available, three problems arise:
a. The lost update problem
b. Uncommitted dependency problem
c. Inconsistent data analysis.

You should be able to analysis each of these problems. How could we solve the above problems? A locking technique is one of the solutions. Study this technique and understand the application of the technique to the above problems.

Recovery concern with restoring the database to the correct state after failure. You must know the recovery facilities provided by DBMS. These include:

- back up facilities
- log (journal) facilities
- check point facility
- recovery manager

What is log file (journal)?

Topik 8. Data integrity and security.

Data integrity deals with the validity of data. Integrity constraints are imposed to the database to ensure the validity of data. What is integrity constraint? It is a database rule.
The major integrity constraints:
- Domain constraint
- Entity integrity constraint
- Referential integrity constraint

How are these implemented in databases?

What is data security?
DBMS gives what we called privilege (operation facilities – SELECT, UPDATE, INSERT, DELETE) to the users, usually done DBA. The privilege are assigned to users through SQL GRANT command.

The REVOKE command, on the other hand, will withdraw the privilege from users.

So, you should understand the format of the grant and revoke command.


Nota:

Berikut adalah bahagian-bahagian daripada modul yang boleh dilangkau:

a. Bahagian 3.8 hingga 3.13 (berkenaan dengan relational calculus)
b. Bahagian 5.7 hingga 5.8 (berkenaan 5NF)
d. Bab 7 (organisasi fizikal pangkalan data).


Berikut pula adalah bahan yang perlu dibaca daripada buku Elmasri:
Fundamentals of Database Systems, Third Edition.

Chapter 1 (Databases and Database users)
Chapter 2 (Database System Concepts and Architecture)
Chapter 3 (E-R data model)
Chapter 4 (Section 4.1 & 4.2- EER).
Chapter 7 (Relational Data Model)
Chapter 8 SQL
Chapter 9 (section 9.1- converting ER to relations)
Chapter 10 (Examples of DBMS)
Chapter 11 (Object-oriented databases)
Chapter 14 (Normalisation)
Chapter 16 (Database Design)
Chapter 19 (Transaction concepts)
Chapter 20 (Concurrency control)
Chapter 22 (Database Security)
 

 

Tugasan I


Prepare an E-R diagram for a real estate firm that lists property for sale. The following describes this organization:

. The firm has a number of sales offices in several states. Attributes of sale office include Office_Number (identifier) and Location.

. Each sales office is assigned one or more employees. Attributes of employee include Employee_ID (identifier) and Employee_Name. An employee must be assigned to only one sales office.

. For each sales office, there is always one employee assigned to manage that office. An employee may manage only the sale office to which she is assigned.

. The firm lists property for sale. Attributes of property include Property_ID (identifier) and Location. Components of Location include Address, City, State, and Zip_Code.

. Each unit of property must be listed with one (and only one) of the sales offices. A sales office may have any number of properties listed, or may have no properties listed.

. Each unit of property has one or more owners. Attributes of owners are Owner_ID (identifier) and Owner_Name. An owner may own one or more units of property. An attribute of the relationship between property and owner is Percent_Owned.



Tugasan 2

Tugasan ini adalah sambungan kepada tugasan 1. Anda dikehendaki menukarkan gambarajah E-R yang terhasil daripada tugasan 1 kepada set hubungan (set of relations) berdasarkan langkah-langkah penukaran E-R kepada relation.


Kerja Amali

Tugasan selanjutnya adalah berdasarkan buku berikut:


Projects For MS Access 2000
By Philip A. Koneman
Addison-Wesley's SELECT Lab Series

Project 1: On Your Own Exercises: No1 & 2

Project 2: On Your Own Exercises: No 3

Project 4: On Your Own Exercises: No 1 & No. 3

Project 5: On Your Own Exercises: No 1 & No. 2


Nota:

Tugasan boleh dihantar secara beransur-ansur atau secara serentak. Tugasan hendaklah sampai kepada saya sebelum minggu peperiksaan akhir.

(Completed assignment can be handed in one by one or the whole assignments may be submitted at once. All assignments must reach me before the final exam of the course. )
 

 

CONTOH KERTAS PEPERIKSAAN PERTENGAHAN SEMESTER

Soalan 1 (Question 1)

a. Bincangkan dua kelemahan sistem pemprosesan fail yang menggerakkan seseorang untuk mengambil pendekatan pangkalan data dalam pemprosesan data.
(Discuss two weaknesses of file processing systems that motivates ones to adopt the database approach in data processing.) (2 marks)

b. Terangkan tiga fungsi Sistem Pengurusan Pangkalan Data.
(Briefly explain three (3) functions of a Database Management System.)
(3 marks)

c. Terangkan istilah bahasa definisi data dan bahasa pengolahan data.
(Explain the terms data definition language and data manipulation language.)
(2 marks)

d. Apakah model data dan namakan dua contoh model data.
(What is data model and name two examples of data models.) (1 mark)

e. Terangkan cara perwakilan data dalam model data relational.
(Explain the way of representing data in relational data models.) (2 marks)


Soalan 2 (Question 2)

Lukiskan gambarajah E-R untuk situasi berikut: (6 markah)

Suatu syarikat mempunyai sebilangan pekerja. Atribut PEKERJA ialah ID-Pekerja, Nama, Alamat, dan Tarikh-Lahir. Syarikat juga mempunyai beberapa projek. Atribut PROJEK ialah ID-Projek, Nama-Projek dan Tarikh-Mula. Setiap pekerja diberikan satu atau lebih projek atau mungkin tidak diberikan projek. Satu projek mesti mempunyai sekurang-kurangnya satu pekerja, atau mungkin mempunyai beberapa pekerja yang ditugaskan. Kadar bil satu pekerja mungkin berubah mengikut projek, dan syarikat ingin merekod kadar bil berkaitan (Kadar-Bil) untuk setiap pekerja apbila diberikan suatu projek.

Draw an E-R diagram for the following situation: (6 marks)

A company has a number of employees. The attributes of EMPLOYEE include Employee_ID (unique), Name, Address, and Birth_Date. The company also has several projects. Attributes of PROJECT include Project_ID (unique), Project_Name, and Start_Date. Each employee may be assigned to one or more projects, or may not be assigned to a project. A project must have at least one employee assigned, and may have any number of employees assigned. An employee’s billing rate may vary by project, and the company wishes to record the applicable billing rate (Billing_Rate) for each employee when assigned to a particular project.


Soalan 3 (Question 3)

Perhatikan pangkalan data di bawah yang mengandungi tiga relation iaitu PELAJAR, DAFTAR dan KULIAH dan kemudian jawab soalan-soalan berikutnya:
(Consider the database below that consists of three relations, namely PELAJAR, DAFTAR, and KULIAH, and then answer the following questions:)


PELAJAR
Matrik Nama Major Umur
10000 Jamal Ekonomi 21
15000 Putih Komunikasi 19
20000 Bakar Ekonomi 25
25000 Gibson Pengurusan 24
35000 Rohaya Komputer 20
40000 Remi Matematik 19

DAFTAR
Matrik Kod Taraf
10000 ECO4405 W
15000 KOM2000 W
20000 ECO4405 ELF
20000 SAK2500 W
40000 KOM2000 ELF
40000 MTK4100 W
40000 SAK2500 ELF

KULIAH
Kod Masa Bilik
KOM2000 IJ9 DK5
ECO4405 IRJ3 DK4
MTK4100 IRJ8 DK4
SAK2500 ISK11 DK2

a. Ungkapkan pertanyaan-pertanyaan berikut dalam relational algebra.
(Express the following queries in relational algebra.) (6 marks)

i. Senaraikan kod dan taraf semua kursus yang diambil oleh pelajar bernama Bakar.
(List down the code and status of all courses enrolled by student named Bakar.)

ii. Senaraikan waktu kuliah bagi semua kursus yang diambil oleh Bakar.
(List lecture hours of all courses enrolled by Bakar)

iii. Senaraikan no. matrik dan nama semua pelajar yang tidak mendaftar mana-mana kursus.
(List matric numbers and names of all students who do not enroll any course.)


b. Ungkapkan pula semua pertanyaan dalam bahagian (a) di atas menggunakan SQL.

(Express also all the queries of section (a) above in SQL.) (6 marks)

c. Senaraikan bilangan pelajar dalam setiap major.
(List the number of students in each major.) (2 marks)


***** End of Question *****

CONTOH KERTAS PEPERIKSAAN AKHIR

1. a. Terangkan kelainan (ralat) yang mungkin terhasil akibat keberulangan data dalam hubungan. (3 markah)

b. Apakah kebergantungan transitif? Manakah normal form yang dikaitkan dengan kebergantungan ini? (2 markah)

c. Bilakah sesuatu jadual (table) berada dalam 3NF? (2 markah)

d. Untuk memelihara kelengkapan pejabat, komputer, pencetak dan sebagainya, Syarikat AMAL menggunakan struktur jadual berikut:

atribut nilai sampel

Item-ID C99-50
Item-Desc HP LaserJet 4L printer
Bldg-Room 135
Bldg-Code A02
Bldg-Name Sri Mawar
Bldg-Manager R. Mansoor

Diberi maklumat ini, tuliskan semua kebergantungan fungsian jadual. Apakah normal form jadual itu? Tukarkan jadual itu kepada jadual 3NF. (Petunjuk: Kedua-dua Item-ID dan Bldg-Code mempunyai nilai yang unik.) (6 markah)


2. Nyatakan langkah-langkah reka bentuk pangkalan data dan terangkan dengan ringkas setiap satu dari mereka. Nyatakan juga hasil dalam setiap langkah. (10 markah)


3. a. Terangkan kenyataan berikut:
Transaksi adalah satu unit logikal kerja. (2 markah)

b. Apakah keadaan pangkalan data konsisten? (2 markah)

c. Tuliskan kenyataan SQL yang mungkin digunakan dalam pengurusan transaksi, dan jelaskan bagaimana mereka bekerja. (2 markah)

d. Terangkan satu masalah pelaksanaan transaksi serentak yang paling biasa ditemui.
(2 markah)


e. Pertimbangkan pelaksanaan transaksi serentak berikut:

transaksi A masa transaksi B

Retrieve R (andaikan t1
Nilai awal R ialah 35)
t2 Retrieve R


R = R + 100 t3

t4 R = R - 20

i. Apakah nilai R selepas dikemaskini pada t4? Adakah ianya nilai yang betul bagi R? Jelaskan jawapan anda. (2 markah)

ii. Tunjukkan bagaimana teknik penguncian menghalang masalah dalam transaksi serentak di atas daripada berlaku. (3 markah)

f. Apakah yang dimaksudkan dengan integriti data? Jelaskan makna kekangan entity integrity dan referential integrity (4 markah)


***** akhir soalan *****




1. a. Describe the anomalies (errors) that are likely to be the result of data redundancy in relations. (3 marks)

a. What is a transitive dependency? With what normal form is it associated?
(2 marks)

c. When is a table in 3NF? (2 marks)

d. To keep track of office furniture, computers, printers, and so on, the AMAL company uses the following table structure:

Attribute Sample value
Item-ID C99-50
Item-Desc HP LaserJet 4L printer
Bldg-Room 135
Bldg-Code A02
Bldg-Name Sri Mawar
Bldg-Manager R. Mansoor

Given this information, write all functional dependencies of the table. What normal form is the table in? Convert the table into 3NF tables. (Hint: Both Item-ID and Bldg-Code have unique value.) (6 marks)


2. State the database design steps and briefly explain each one of them. State also the result in each step. (10 marks)


3. a. Explain the following statement:
A transaction is a logical unit of work. (2 marks)

b. What is a consistent database state? (2 marks)

c. Write the SQL statements that might be used in transaction management, and explain how they work. (2 marks)

d. Describe one most common concurrent transaction execution problem.
(2 marks)
e. Consider the following concurrent transaction execution:

transaction A time transaction B

Retrieve R (assume t1
an initial value of R is 35)
t2 Retrieve R

R = R + 100 t3

t4 R = R - 20

i. What is the value of R after it is updated at t4? Is it the correct value of R? Explain your answer. (2 mark)

ii. Show how a locking technique prevents the problem found in the concurrent transaction above from occurring. (3 marks)

f. What is meant by data integrity? Explain the term entity integrity and referential integrity constraints. (4 marks)

***** end of questions *****