Relational Databases PENDAHULUAN PENDAHULUAN

T Relational Databases

© 2006 Prentice Hall Business Publishing

Accounting Information Systems, 10/e

Romney/Steinbart

1 of 95

PENDAHULUAN • Perbedaan file & database – – – –

Manfaat database Sistem database Relational database Studi kasus



Romney/Steinbart

2 of 95

PENDAHULUAN • Database relational merupakan dasar dari sebagian besar SIA terpadu yg modern – Merupakan tipe database yang paling banyak digunakan untuk pemprosesan transaksi – Pada bab ini akan menjelaskan konsep dari database



Romney/Steinbart

3 of 95

1

FILE VS. DATABASES • Mula-mula kita harus memahami prinsip-prinsip dasar bagaimana data disimpan dalam sistem komputer – Entitas (entity) is segala sesuatu yang akan disimpan datanya. Misal: universitas  entitas: mahasiswa

MAHASISWA

NIM

Nama Belakang

Nama Depan

Nomor Telepon

Tanggal Lahir

333-33-3333

Simpson

Alice

333-3333

10/11/84

111-11-1111

Sanders

Ned

444-4444

11/24/86

123-45-6789

Moore

Artie

555-5555



04/20/85 Romney/Steinbart

4 of 95

FILE VS. DATABASES – Informasi tentang atribut (attributes) dari suatu entitas (misal: NIM dan tanggal lahir) disimpan dalam suatu fields.

MAHASISWA

NIM

Nama Belakang

Nama Depan

Nomor Telepon

Tanggal Lahir

333-33-3333

Simpson

Alice

333-3333

10/11/84

111-11-1111

Sanders

Ned

444-4444

11/24/86

123-45-6789

Moore

Artie

555-5555




5 of 95

FILE VS. DATABASES – Seluruh field yang berisi data tentang satu entitas (misal: satu mahasiswa) membentuk suatu record. – Contoh di bawah ini menjukkan record untuk Artie Moore.

MAHASISWA

NIM

Nama Belakang

Nama Depan

Nomor Telepon

Tanggal Lahir

333-33-3333

Simpson

Alice

333-3333

10/11/84

111-11-1111

Sanders

Ned

444-4444

11/24/86

123-45-6789

Moore

Artie

555-5555




6 of 95

2

FILE VS. DATABASES – Kumpulan dari seluruh record yang saling berhubungan akan membentuk suatu file (misal: file mahasiswa)

MAHASISWA

NIM

Nama Belakang

Nama Depan

Nomor Telepon

Tanggal Lahir

333-33-3333

Simpson

Alice

333-3333

10/11/84

111-11-1111

Sanders

Ned

444-4444

11/24/86

123-45-6789

Moore

Artie

555-5555




7 of 95

FILE VS. DATABASES – Kumpulan dari file-file yang saling berhubungan dan terkoordinasi akan membentuk suatu database.

File Mahasiswa

File Kelas File Dosen Pembimbing



Romney/Steinbart

8 of 95

FILE VS. DATABASES • Sistem database dibuat untuk mengatasi masalah yang timbul akibat bertambah banyaknya file master – Setiap kali ada informasi baru, perusahaan membuat file dan program baru – Akibatnya: jumlah master file semakin banyak



Romney/Steinbart

9 of 95

3

FILE VS. DATABASES Master File 1 Fact A Fact B Fact C

Master File 2 Fact A Fact D Fact F

Master File 1 Fact A Fact B Fact F

Enrollment Program

• Bertambah banyaknya master file menimbulkan masalah a.l: – Informasi yang sama disimpan dalam file master yang berbeda – Sulit untuk mengintegrasikan data secara efektif – Informasi yang sama bisa jadi tidak konsisten • Mis: jika ada mahasiswa yang nomor HP nya berubah, mungkin hanya ter-update di satu file master saja tapi tidak di file master yang lain

Fin. Aid Program

Grades Program



Romney/Steinbart

10 of 95

FILE VS. DATABASES • Database merupakan kumpulan file yang saling berhubungan dan terkoordinasi secara terpusat

Database Fact A Fact B Fact C Fact D Fact E Fact F

Database Management System

Enrollment Program

Fin. Aid Program


Grades Program


Romney/Steinbart

11 of 95

FILE VS. DATABASES Database Fact A Fact B Fact C Fact D Fact E Fact F


Enrollment Program

Fin. Aid Program


Grades Program

• Pendekatan database memperlakuakn data sebagai sumber daya organisasi yang harus dapat digunakan oleh dan dikelola untuk seluruh organisasi, bukan hanya untuk departemen tertentu • Sistem Manajemen Basis Data (Database management system = DBMS) berfungsi sbg penghubung antara database dan programprogram aplikasi


Romney/Steinbart

12 of 95

4

FILE VS. DATABASES • Kombinasi dari database, DBMS, dan program aplikasi yang mengakses database disebut database system.

Database Fact A Fact B Fact C Fact D Fact E Fact F


Enrollment Program

Fin. Aid Program


Grades Program


Romney/Steinbart

13 of 95

FILE VS. DATABASES Database Fact A Fact B Fact C Fact D Fact E Fact F


Enrollment Program

Fin. Aid Program


Grades Program

• Orang yang bertanggung jawab atas database disebut database administrator. • Dengan semakin berkembangnya teknologi, banyak perusahaan yang memangun database dalam skala besar yang disebut data warehouses.


Romney/Steinbart

14 of 95

KEUNTUNGAN SISTEM DATABASE • Teknologi database memberikan manfaat sbb: – Data integration


• Diperoleh dengan menggabungkan master file dalam kelompok data yang lebih besar yang bisa diakses oleh banyak program


Romney/Steinbart

15 of 95

5

KEUNTUNGAN SISTEM DATABASE • Database technology provides the following benefits to organizations: – Data integration – Data sharing •


Mempermudah untuk berbagi data yang sudah terintegrasi


Romney/Steinbart

16 of 95

KEUNTUNGAN SISTEM DATABASE • Database technology provides the following benefits to organizations: – Data integration – Data sharing – Reporting flexibility • Laporan dapat dengan mudah direvisi dan dihasilkan bila diperlukan • Database dapat dengan mudah dibaca untuk memperoleh informasi



Romney/Steinbart

17 of 95

KEUNTUNGAN SISTEM DATABASE • Database technology provides the following benefits to organizations: – Data integration – Data sharing – Reporting flexibility – Minimal data redundancy and inconsistencies • Mengurangi pengulangan dan ketidak konsistenan data



Romney/Steinbart

18 of 95

6

KEUNTUNGAN SISTEM DATABASE • Database technology provides the following benefits to organizations: • Data items berdiri sendiri, terpisah dari program – Data integration yang menggunakannya – Data sharing • Sehingga data item dapat dirubah tanpa harus merubah program, dan juga sebaliknya – Reporting flexibility • Mempermudah pemprograman dan – Minimal data redundancy manajemen and inconsistencies menyederhanakan data – Data independence



Romney/Steinbart

19 of 95

KEUNTUNGAN SISTEM DATABASE • Database technology provides the following benefits to organizations: – Data integration – Data sharing – Reporting flexibility • Manajemen data lebih efisien karena administrator database bertanggung – Minimal data redundancy and inconsistencies jawab untuk mengkoordinasi, mengendalikan, dan mengelola data – Data independence – Central management of data



Romney/Steinbart

20 of 95

KEUNTUNGAN SISTEM DATABASE • Database technology provides the following benefits to organizations: – Data integration – Data sharing – Reporting flexibility • Hubungan dapat digambarkan secara – Minimal dataeksplisit redundancy and inconsistencies dan digunakan untuk membuat laporan manajemen – Data independence • Misal: hubungan antara biaya penjualan dengan kampanye promosi – Central management of data – Cross-functional analysis © 2006 Prentice Hall Business Publishing


Romney/Steinbart

21 of 95

7

DATABASE SYSTEMS • Tampilan Logis dan Fisik dari data (Logical and Physical Views of Data) – Dalam sistem yang berorientasi file, programmer harus mengetahui lokasi fisik dan layout dari record yang digunakan oleh program • Mereka harus memberikan acuan lokasi, panjang, dam format dari setiap field • Ketika data digunakan oleh beberapa file, proses ini akan semakin kompleks © 2006 Prentice Hall Business Publishing


Romney/Steinbart

22 of 95

DATABASE SYSTEMS • Sistem database mengatasi masalah ini dengan memisahkan penyimpanan dan penggunaan dari data elements. – Terdapat 2 tampilan data: • Tampilan Logis (Logical view)  Bagaimana pengguna atau programer secara konseptual mengatur & memahami data.



Romney/Steinbart

23 of 95

DATABASE SYSTEMS • Sistem database mengatasi masalah ini dengan memisahkan penyimpanan dan penggunaan dari data elements. – Terdapat 2 tampilan data: • Tampilan logis (Logical view) • Tampilan Fisik (Physical view)  Bagaimana dan dimana data secara fisik diatur dan disimpan



Romney/Steinbart

24 of 95

8

DATABASE SYSTEMS – Memisahkan tampilan logis dan fisik memungkinkan pengembangan aplikasi baru karena programer dapat fokus untuk memasukkan kode (coding/hal-hal yang akan dilakukan program) ke dalam logika aplikasi dan tidak perlu memusatkan perhatian pada bagaimana & dimana data disimpan atau di akses



Romney/Steinbart

Logical View—User A

Logical View—User B

Enrollment by Class

Scholarship Distribution Sr. 33%

Fr. 5%

25 of 95

Soph. 24%

Jr. 38%

DBMS Operating System

DBMS menterjemahkan logical views pemakai dalam instruksi mengenai data apa yang harus diambil dari database

Database © 2006 Prentice Hall Business Publishing


Romney/Steinbart

Logical View—User A

Logical View—User B

Enrollment by Class

Scholarship Distribution Sr. 33%

Fr. 5%

26 of 95

Soph. 24%

Jr. 38%

DBMS Operating System

Database © 2006 Prentice Hall Business Publishing

Sistem operasi menerjemahkan permintaan DBMS ke dalam instruksi untuk memindahkan data secara fisik dari berbagai disk


Romney/Steinbart

27 of 95

9

DATABASE SYSTEMS • DBMS menangani hubungan (link) antara tampilan fisik dan logis dari data – Memungkinkan pemakai untuk mengakses, membuat permintaan, dan memperbarui data tanpa harus mencari dimana dan bagaimana data disimpan secara fisik



Romney/Steinbart

28 of 95

DATABASE SYSTEMS • Memisahkan tampilan fisik dan logis data juga berarti para pemakai dapat mengubah konsep hubungan antara berbagai data tanpa merubah penyimpanan datanya • Database administrator dapat merubah penyimpanan fisik data untuk meningkatkan kinerja sistem, tanpa menimbulkan pengaruh pada pemakai atau program aplikasinnya



Romney/Steinbart

29 of 95

DATABASE SYSTEMS • Skema (Schemas) – Mendeskripsikan struktur logis dari database – Terdapat 3 tingkatan skema: • Conceptual level • Tampilan seluruh database pada tingkat organisasi • Mendaftar elemen-elemen data hubungan antar mereka



Romney/Steinbart

30 of 95

10

Subschema--User A

Subschema--User B

Subschema--User C Smith . . . A Jones . . . B Arnold . . .D

Pemetaan pandangan tingkat eksternal ke skema tingkat konseptual

Classes

Enroll

Student

Cash Receipt Pemetaan tingkat konseptual ke deskripsi tingkat internal Student Record Student No. --character [9] Student Name --character [26] SAT Score --integer [2], non-null, index=itemx © 2006 Prentice Hall Business Publishing

Class Record Class Name --character [9] Dept No. --integer [4], non-null, index=itemx Course No. --integer [4], non-null, index=itemx


Romney/Steinbart

31 of 95

DATABASE SYSTEMS • Skema (Schemas) – Mendeskripsikan struktur logis dari database – Terdapat 3 tingkatan skema: • Conceptual level • External level • Terdiri dari satu set tampilan individual bagi pemakai dari berbagai bagian database, misal: bagaimana pemakai memandang bagian dari sistem dimana dia berinteraksi • Tampilan individual ini disebut sebagai subskema. © 2006 Prentice Hall Business Publishing

Subschema--User A


Subschema--User B

Romney/Steinbart

32 of 95



Classes

Enroll

Student




Romney/Steinbart

33 of 95

11

DATABASE SYSTEMS • Skema (Schemas) – Mendeskripsikan struktur logis dari database – Terdapat 3 tingkatan skema: • Conceptual level• • External level • • Internal level


Subschema--User A

Tampilan tingkat terendah dari database Mendeskripsikan bagaimana data disimpan dan diakses, termasuk didalamnya: – Layout record – Definisi – Alamat – Indeks


Subschema--User B

Romney/Steinbart

34 of 95



Classes

Enroll

Student


Subschema--User A



Subschema--User B

Romney/Steinbart

35 of 95



Classes

Enroll

Student

Cash Receipt

Tanda panah dua arah mewakili pemetaan antar skema

Pemetaan tingkat konseptual ke deskripsi tingkat internal Student Record Student No. --character [9] Student Name --character [26] SAT Score --integer [2], non-null, index=itemx © 2006 Prentice Hall Business Publishing



Romney/Steinbart

36 of 95

12

DATABASE SYSTEMS • DBMS menggunakan pemetaan untuk menterjemahkan permintaan data dari pemakai atau program aplikasi (diekspresikan dalam bentuk nama dan hubungan logisnya) kedalam indeks dan alamat yang dibutuhkan untuk mengakses data



Romney/Steinbart

37 of 95

DATABASE SYSTEMS • Akuntan seringkali terlibat dalam pengembangan skema tingkat konseptual dan eksternal sehingga penting untuk mengetahui perbedaan antara keduanya • Akses data oleh pegawai harus dibatasi pada subskema yang relevan dengan pekerjaannya



Romney/Steinbart

38 of 95

DATABASE SYSTEMS • Kamus Data (Data Dictionary) – Merupakan komponen kunci dari DBMS • Berisi informasi mengenai struktur database • Setiap elemen data yang disimpan dalam database, misal: NIM, memiliki catatan di kamus data yang mendeskripsikan elemen tersebut • Contoh: Ref.1 hal. 100, Tabel 4.2



Romney/Steinbart

39 of 95

13

DATABASE SYSTEMS  Akuntan harus berpartisipasi dalam pengembangan kamus data karena mereka memiliki pemahaman yang baik mengenai elemen-elemen data yang ada dalam organisasi perusahaan, yaitu dari mana asalnya dan dimana mereka digunakan.



Romney/Steinbart

40 of 95

DATABASE SYSTEMS • Bahasa DBMS – Setiap DBMS harus menyediakan sarana untuk pelaksanaan tiga fungsi dasar, yaitu: • Menciptakan database (creating) • Mengubah database (updating) • Mempertanyakan database (querying)



Romney/Steinbart

41 of 95




Romney/Steinbart

42 of 95

14

DATABASE SYSTEMS • Menciptakan database: – Rangkaian perintah yang digunakan untuk menciptakan database dikenal sebagai data definition language (DDL). DDL digunakan untuk: • Membangun kamus data • Mengawali atau menciptakan database • Mendeskripsikan pandangan logis untuk setiap pemakai atau programer • Memberikan batasan untuk keamanan field atau catatan pada database © 2006 Prentice Hall Business Publishing


Romney/Steinbart

43 of 95




Romney/Steinbart

44 of 95

DATABASE SYSTEMS • Mengubah database – Rangkaian perintah yang digunakan untuk merubah database dikenal sebagai data manipulation language (DML). DML digunakan untuk mengelola data, seperti: • Memperbatui (updating) data • Penyisipan (inserting) data • Penghapusan (deleting) bagian dari database



Romney/Steinbart

45 of 95

15




Romney/Steinbart

46 of 95

DATABASE SYSTEMS • Querying a database: – Rangkaian perintah yang digunakan untuk menyelidiki database dikenal sebagai data query language (DQL). DQL digunakan intuk: • • • •

Mengambil data Menyortir data Menyusun data Menyajikan suatu bagian dari database



Romney/Steinbart

47 of 95

DATABASE SYSTEMS • Penulis Laporan (Report Writer) – Banyak DBMS yang juga memasukkan report writer, yaitu bahasa yang menyederhanakan pembuatan laporan – Pemakai hanya menspesifikasi: • Elemen apa yang akan dicetak • Bagaimana format laporan

– Report writer kemudian: • Mencari dalam database • Mengekstraksi bagian dari data tersebut • Mencetak sesuai dengan format yang telah ditentukan



Romney/Steinbart

48 of 95

16

RELATIONAL DATABASES • DBMS dikarakteristikan melalui jenis model logis data yang mendasarinya – Data model merupakan perwakilan abstrak dari isi database – Kebanyakan DBMS yang baru disebut sebagai relational databases karena menggunakan model relational data yang dikembangkan E.F. Codd in 1970.



Romney/Steinbart

49 of 95

RELATIONAL DATABASES • Relational data model mewakili semua yang disimpan dalam database, dalam bentuk tabel. • Tabel ini dinamakan hubungan (relational)


Student ID 333-33-3333 111-11-1111 123-45-6789


STUDENTS Last First Name Name Simpson Alice Sanders Ned Moore Artie

Course ID Course 1234 ACCT-3603 1235 ACCT-3603 1236 MGMT-2103 STUDENT x COURSE

SCID 333333333-1234 333333333-1236 111111111-1235 111111111-1236 © 2006 Prentice Hall Business Publishing

Romney/Steinbart

50 of 95

Phone No. 333-3333 444-4444 555-5555

COURSES Section 1 2 1

Day MWF TR MW

Time 8:30 9:30 8:30

Primary key adalah atribut, atau kombinasi dari beberapa atribut, yang secara unik mengidentifikasi baris tertentu dalam sebuah tabel Accounting Information Systems, 10/e

Romney/Steinbart

51 of 95

17


Student ID 333-33-3333 111-11-1111 123-45-6789

Course ID Course 1234 ACCT-3603 1235 ACCT-3603 1236 MGMT-2103

Phone No. 333-3333 444-4444 555-5555


Day MWF TR MW

Time 8:30 9:30 8:30

STUDENT x COURSE

SCID 333333333-1234 333333333-1236 111111111-1235 111111111-1236

Dalam beberapa tabel, dua atau lebih atribut dapat bersama-sama membentuk PK



Romney/Steinbart

52 of 95

STUDENTS Student ID

Last Name

First Name

Phone No.

Advisor No.

333-33-3333

Simpson

Alice

333-3333

1418

111-11-1111

Sanders

Ned

444-4444

1418

123-45-6789

Moore

Artie

555-5555

1503

ADVISORS Advisor No.

Last Name

First Name

Office No.

1418

Howard

Glen

420

1419

Melton

Amy

316

1503

Zhang

Xi

202

1506

Radowski

J.D.

203

Foreign key atribut yang muncul dalam suatu tabel, yang merupakan PK dalam tabel lainnya © 2006 Prentice Hall Business Publishing


Romney/Steinbart

53 of 95

STUDENTS Student ID

Last Name

First Name

Phone No.

Advisor No.

333-33-3333

Simpson

Alice

333-3333

1418

111-11-1111

Sanders

Ned

444-4444

1418

123-45-6789

Moore

Artie

555-5555

1503


Last Name

First Name

Office No.

1418

Howard

Glen

420

1419

Melton

Amy

316

1503

Zhang

Xi

202

1506

Radowski

J.D.

203

Foreign key digunakan untuk menghubungkan tabel-tabel



Romney/Steinbart

54 of 95

18

STUDENTS Student ID

First Name

Last Name

Advisor No.

Phone No.

333-33-3333

Simpson

Alice

333-3333

1418

111-11-1111

Sanders

Ned

444-4444

1418

123-45-6789

Moore

Artie

555-5555

1503


Last Name

First Name

Office No.

1418

Howard

Glen

420

1419

Melton

Amy

316

1503

Zhang

Xi

202

1506

Radowski

J.D.

203

Atribut lainnya yang bukan merupakan atribut kunci dalam setiap tabel, menyimpan informasi penting mengenai entitasnya © 2006 Prentice Hall Business Publishing


Romney/Steinbart

55 of 95

RELATIONAL DATABASES • Alternatives for Storing Data – One possible alternate approach would be to store all data in one uniform table. – For example, instead of separate tables for students and classes, we could store all data in one table and have a separate line for each student x class combination.


Student ID

Last Name

333-33-3333


Romney/Steinbart

First Name

Phone No.

Simpson

Alice

333-3333

ACCT-3603

1 M

9:00 AM

333-33-3333

Simpson

Alice

333-3333

FIN-3213

3 Th

11:00 AM

333-33-3333

Simpson

Alice

333-3333

MGMT-3021

11 TH

12:00 PM

111-11-1111

Sanders

Ned

444-4444

ACCT-3433

2 T

10:00 AM

111-11-1111

Sanders

Ned

444-4444

MGMT-3021

5 W

8:00 AM

111-11-1111

Sanders

Ned

444-4444

ANSI-1422

7 F

9:00 AM

123-45-6789

Moore

Artie

555-5555

ACCT-3433

2 T

10:00 AM

123-45-6789

Moore

Artie

555-5555

FIN-3213

3 Th

11:00 AM

Course No.

Sectio n

56 of 95

Day

Time

• Using the suggested approach, a student taking three classes would need three rows in the table. • In the above, simplified example, a number of problems arise.



Romney/Steinbart

57 of 95

19

Student ID

Last Name

First Name

333-33-3333

Phone No.

Simpson

Alice

333-3333

ACCT-3603

1 M

9:00 AM

333-33-3333

Simpson

Alice

333-3333

FIN-3213

3 Th

11:00 AM

333-33-3333

Simpson

Alice

333-3333

MGMT-3021

11 TH

12:00 PM

111-11-1111

Sanders

Ned

444-4444

ACCT-3433

2 T

10:00 AM

111-11-1111

Sanders

Ned

444-4444

MGMT-3021

5 W

8:00 AM

111-11-1111

Sanders

Ned

444-4444

ANSI-1422

7 F

9:00 AM

123-45-6789

Moore

Artie

555-5555

ACCT-3433

2 T

10:00 AM

123-45-6789

Moore

Artie

555-5555

FIN-3213

3 Th

11:00 AM

Course No.

Sect.

Day

Time

• Suppose Alice Simpson changes her phone number. You need to make the change in three places. If you fail to change it in all three places or change it incorrectly in one place, then the records for Alice will be inconsistent. • This problem is referred to as an update anomaly. © 2006 Prentice Hall Business Publishing

Student ID

Last Name

333-33-3333


Romney/Steinbart

58 of 95

First Name

Phone No.

Simpson

Alice

333-3333

ACCT-3603

1 M

9:00 AM

333-33-3333

Simpson

Alice

333-3333

FIN-3213

3 Th

11:00 AM

333-33-3333

Simpson

Alice

333-3333

MGMT-3021

11 TH

12:00 PM

111-11-1111

Sanders

Ned

444-4444

ACCT-3433

2 T

10:00 AM

111-11-1111

Sanders

Ned

444-4444

MGMT-3021

5 W

8:00 AM

111-11-1111

Sanders

Ned

444-4444

ANSI-1422

7 F

9:00 AM

123-45-6789

Moore

Artie

555-5555

ACCT-3433

2 T

10:00 AM

123-45-6789

Moore

Artie

555-5555

FIN-3213

3 Th

11:00 AM

Course No.

Sect.

Day

Time

• What happens if you have a new student to add, but he hasn’t signed up for any courses yet? • Or what if there is a new class to add, but there are no students enrolled in it yet? In either case, the record will be partially blank. • This problem is referred to as an insert anomaly. © 2006 Prentice Hall Business Publishing

Student ID

Last Name

333-33-3333


Romney/Steinbart

59 of 95

First Name

Phone No.

Simpson

Alice

333-3333

ACCT-3603

1 M

9:00 AM

333-33-3333

Simpson

Alice

333-3333

FIN-3213

3 Th

11:00 AM

333-33-3333

Simpson

Alice

333-3333

MGMT-3021

11 TH

12:00 PM

111-11-1111

Sanders

Ned

444-4444

ACCT-3433

2 T

10:00 AM

111-11-1111

Sanders

Ned

444-4444

MGMT-3021

5 W

8:00 AM

111-11-1111

Sanders

Ned

444-4444

ANSI-1422

7 F

9:00 AM

123-45-6789

Moore

Artie

555-5555

ACCT-3433

2 T

10:00 AM

123-45-6789

Moore

Artie

555-5555

FIN-3213

3 Th

11:00 AM

Course No.

Sect.

Day

Time

• If Ned withdraws from all his classes and you eliminate all three of his rows from the table, then you will no longer have a record of Ned. If Ned is planning to take classes next semester, then you probably didn’t really want to delete all records of him. • This problem is referred to as a delete anomaly. © 2006 Prentice Hall Business Publishing


Romney/Steinbart

60 of 95

20

RELATIONAL DATABASES • Alternatives for Storing Data – Another possible approach would be to store each student in one row of the table and create multiple columns to accommodate each class that he is taking.


First Name

Romney/Steinbart

Phone No.

Class 1

333-33-3333

Simpson

Alice

333-3333

ACCT-3603

FIN-3213

MGMT-3021

111-11-1111

Sanders

Ned

444-4444

ACCT-3433

MGMT-3021

ANSI-1422

123-45-6789

Moore

Artie

555-5555

ACCT-3433

FIN-3213

Student ID0

Last Name


Class 2

Class 3

61 of 95

Class 4

• This approach is also fraught with problems: – How many classes should you allow for in building the table? – The above table is quite simplified. In reality, you might need to allow for 20 or more classes (assuming a student could take many 1-hour classes). Also, more information than just the course number would be stored for each class. There would be a great deal of wasted space for all the students taking fewer than the maximum possible number of classes. – Also, if you wanted a list of every student taking MGMT-3021, notice that you would have to search multiple attributes.


Student ID 333-33-3333 111-11-1111 123-45-6789





Romney/Steinbart

62 of 95

Phone No. 333-3333 444-4444 555-5555


Day MWF TR MW

Time 8:30 9:30 8:30

• The solution to the preceding problems is to use a set of tables in a relational database. • Each entity is stored in a separate table, and separate tables or foreign keys can be used to link the entities together. Accounting Information Systems, 10/e

Romney/Steinbart

63 of 95

21

RELATIONAL DATABASES • Basic Requirements of a Relational Database – Every column in a row must be single valued. • In other words, every cell can have one and only one value. • In the student table, you couldn’t have an attribute named “Phone Number” if a student could have multiple phone numbers. • There might be an attribute named “local phone number” and an attribute named “permanent phone number.” • You could not have an attribute named “Class” in the student table, because a student could take multiple classes. © 2006 Prentice Hall Business Publishing


Romney/Steinbart

64 of 95

RELATIONAL DATABASES • Basic Requirements of a Relational Database – The primary key cannot be null. • The primary key uniquely identifies a specific row in the table, so it cannot be null, and it must be unique for every record. • This rule is referred to as the entity integrity rule.


Student ID 333-33-3333 111-11-1111 123-45-6789



Course ID Course 1234 ACCT-3603 1235 ACCT-3603 1236 MGMT-2103

Romney/Steinbart

65 of 95

Phone No. 333-3333 444-4444 555-5555


Day MWF TR MW

Time 8:30 9:30 8:30

STUDENT x COURSE


• Note that within each table, there are no duplicate primary keys and no null primary keys. • Consistent with the entity integrity rule. Accounting Information Systems, 10/e

Romney/Steinbart

66 of 95

22

RELATIONAL DATABASES • Basic Requirements of a Relational Database – A foreign key must either be null or correspond to the value of a primary key in another table. • This rule is referred to as the referential integrity rule. • The rule is necessary because foreign keys are used to link rows in one table to rows in another table. © 2006 Prentice Hall Business Publishing


Romney/Steinbart

67 of 95

STUDENTS Student ID

Last Name

First Name

Phone No.

Advisor No.

333-33-3333

Simpson

Alice

333-3333

1418

111-11-1111

Sanders

Ned

444-4444

1418

123-45-6789

Moore

Artie

555-5555

1503


Last Name

First Name

Office No.

1418

Howard

Glen

420

1419

Melton

Amy

316

1503

Zhang

Xi

202

1506

Radowski

J.D.

203

Advisor No. is a foreign key in the STUDENTS table. Every incident of Advisor No. in the STUDENTS table either matches an instance of the primary key in the ADVISORS table or is null. © 2006 Prentice Hall Business Publishing


Romney/Steinbart

68 of 95

RELATIONAL DATABASES • Basic Requirements of a Relational Database – All non-key attributes in a table should describe a characteristic of the object identified by the primary key. • Could nationality be a non-key attribute in the student table? • Could advisor’s nationality be a non-key attribute in the student table?



Romney/Steinbart

69 of 95

23

RELATIONAL DATABASES • The preceding four constraints produce a wellstructured (normalized) database in which: – Data are consistent. – Redundancy is minimized and controlled.

• In a normalized database, attributes appear multiple times only when they function as foreign keys. • The referential integrity rule ensures there will be no update anomaly problem with foreign keys. © 2006 Prentice Hall Business Publishing


Romney/Steinbart

70 of 95

RELATIONAL DATABASES • An important feature is that data about various things of interest (entities) are stored in separate tables. – Makes it easier to add new data to the system. • You add a new student by adding a row to the student table. • You add a new course by adding a row to the course table. • Means you can add a student even if he hasn’t signed up for any courses. • And you can add a class even if no students are yet enrolled in it. – Makes it easy to avoid the insert anomaly.

• Space is also used more efficiently than in the other schemes. There should be no blank rows or attributes. © 2006 Prentice Hall Business Publishing

Student ID 333-33-3333 111-11-1111 123-45-6789





Romney/Steinbart

• Add a student here. • Leaves no blank spaces.

Phone No. 333-3333 444-4444 555-5555


71 of 95

Day MWF TR MW

Time 8:30 9:30 8:30

• Add a course here. • Leaves no blank spaces. • When a particular student enrolls for a particular course, add that info here. Accounting Information Systems, 10/e

Romney/Steinbart

72 of 95

24

RELATIONAL DATABASES • Deletion of a class for a student would cause the elimination of one record in the student x class table. – The student still exists in the student table. – The class still exists in the class table. – Avoids the delete anomaly.


Student ID 333-33-3333 111-11-1111 123-45-6789





Romney/Steinbart

• Ned still exists in the student table.

Phone No. 333-3333 444-4444 555-5555


73 of 95

Day MWF TR MW

Time 8:30 9:30 8:30

• Even if Ned was the only student in the class, ACCT-3603 still exists in the course table. • If Ned Sanders drops ACCT-3603, remove Ned’s class from this table. Accounting Information Systems, 10/e

Romney/Steinbart

74 of 95

RELATIONAL DATABASES • There are two basic ways to design wellstructured relational databases. – Normalization – Semantic data modeling



Romney/Steinbart

75 of 95

25




Romney/Steinbart

76 of 95

RELATIONAL DATABASES • Normalization – Starts with the assumption that everything is initially stored in one large table. – A set of rules is followed to decompose that initial table into a set of normalized tables. – Objective is to produce a set of tables in thirdnormal form (3NF) because such tables are free of update, insert, and delete anomalies. – Approach is beyond the scope of this book but can be found in any database textbook. © 2006 Prentice Hall Business Publishing


Romney/Steinbart

77 of 95




Romney/Steinbart

78 of 95

26

RELATIONAL DATABASES • Semantic data modeling (covered in detail in Chapter 15) – Database designer uses knowledge about how business processes typically work and the information needs associated with transaction processing to draw a graphical picture of what should be included in the database. – The resulting graphic is used to create a set of relational tables that are in 3NF. © 2006 Prentice Hall Business Publishing


Romney/Steinbart

79 of 95

RELATIONAL DATABASES • Advantages over simply following normalization rules: – Semantic data modeling uses the designer’s knowledge about business processes and practices; it therefore facilitates efficient design of transaction processing databases. – The resulting graphical model explicitly represents information about the organization’s business processes and policies and facilitates communication with intended users. © 2006 Prentice Hall Business Publishing


Romney/Steinbart

80 of 95

RELATIONAL DATABASES • Creating Relational Database Queries – Databases store data for people and organizations. – To retrieve the data, you query the database and its tables. – Chapter 4 of your textbooks provides some samples of database queries in Microsoft Access. – Try these on your own and/or with your instructor in class. © 2006 Prentice Hall Business Publishing


Romney/Steinbart

81 of 95

27

DATABASE SYSTEMS AND THE FUTURE OF ACCOUNTING • Database systems may profoundly affect the fundamental nature of accounting: – May lead to abandonment of double-entry accounting, because the redundancy of the double entry is not necessary in computer data processing. – May also alter the nature of external reporting. • EXAMPLE: External users could have access to the company’s database and manipulate the data to meet their own reporting needs.



Romney/Steinbart

82 of 95

DATABASE SYSTEMS AND THE FUTURE OF ACCOUNTING • The use of accounting information in decision making will be enhanced by: – Powerful querying capabilities that accompany database packages. – The ability to accommodate multiple views of the same underlying phenomenon. – The ability to integrate financial and operational data.



Romney/Steinbart

83 of 95

DATABASE SYSTEMS AND THE FUTURE OF ACCOUNTING • Accountants must become knowledgeable about databases so they can participate in developing the AIS of the future. • They must help ensure that adequate controls are included to safeguard the data and assure its reliability.



Romney/Steinbart

84 of 95

28

Relational Databases PENDAHULUAN PENDAHULUAN

Recommend Documents