Tutorial of How to Make a Relational Database

This post maybe will be the last article that specifically discuss about relational database and RDBMS. It is about the tutorial of how to make a relational database. Previous article are about the differences between RDBMS and DBMS, the advantages and benefits, and also the disadvantages and weaknesses. I hope all of them will enrich our insight and knowledge.

How to Make a Relational Database

A relational database is a method of storing data in tables containing information grouped by entity or object which the data describes. Tables can be very general, encompassing a large variety of objects or very specific. A general table or entity about animals would have attributes or fields that tell you the type and breed. A more specific design might have a table for dogs, another for cats, etc. The database is called relational because each table can have relationships with other tables based on a shared piece of information. The relationship can be simply a reference or a parent/child association.

1. List business processes. These are transactions that involve adding, updating or using data.
2. Diagram each business process flow. Show each step in the process and what detailed information is passed to and from each step. Record all data elements in the form of what object they describe.
3. Analyze information identified in the process flow diagrams and convert to entities. Entities are objects of importance to the business process, such as a customer or a product that you store information about. List attributes or data elements to be stored about each entity such as a customer address.
4. Identify keys to each entity. Keys are unique identifiers that will isolate an instance of each entity such as a customer number or product code.
5. Identify relationships between entities and how they reference each other. A customer order might be an entity that contains a product code that becomes the link to another entity.
6. Define a database table for each entity. Attributes of the entity become fields and if a field is the key identifier to another entity it becomes a "Foreign Key" or referential constraint.

Tips & Warnings:
Build your design on paper first, then use spreadsheets to simulate the design and then define tables in the database. A prototype allows you to see possible changes before you start coding.

Source : Dennis Bortolus - eHow

The Disadvantages and Weaknesses of Relational Database And RDBMS

Previous about the advantages and benefit, now about disadvantages and weaknesses of relational database and RDBMS.

Summary

Relational databases are widely used in many industries to store financial records, keep track of inventory and to keep records on employees. In a relational database, information is stored in tables (often called relations) which help organize and structure data. Even though they are widely used, relational databases have some drawbacks.

Relational databases allow users to search and retrieve sets of data, called tables, based on relationships between individual pieces of data. Despite advantages such as optimization over large sets of information and access tools such as Structured Query Language (SQL), relational databases also come with their own disadvantages.

1. Cost
One disadvantage of relational databases is the expensive of setting up and maintaining the database system. In order to set up a relational database, you generally need to purchase special software. If you are not a programmer, you can use any number of products to set up a relational database. It does take time to enter in all the information and set up the program. If your company is large and you need a more robust database, you will need to hire a programmer to create a relational database using Structured Query Language (SQL) and a database administrator to maintain the database once it is built. Regardless of what data you use, you will have to either import it from other data like text files or Excel spreadsheets, or have the data entered at the keyboard. No matter the size of your company, if you store legally confidential or protected information in your database such as health information, social security numbers or credit card numbers, you will also have to secure your data against unauthorized access in order to meet regulatory standards.

2. Abundance of Information
Advances in the complexity of information cause another drawback to relational databases. Relational databases are made for organizing data by common characteristics. Complex images, numbers, designs and multimedia products defy easy categorization leading the way for a new type of database called object-relational database management systems. These systems are designed to handle the more complex applications and have the ability to be scalable.

3. Stuctured Limits
Some relational databases have limits on field lengths. When you design the database, you have to specify the amount of data you can fit into a field. Some names or search queries are shorter than the actual, and this can lead to data loss.

4. Isolated Databases
Complex relational database systems can lead to these databases becoming "islands of information" where the information cannot be shared easily from one large system to another. Often, with big firms or institutions, you find relational databases grew in separate divisions differently. For example, maybe the hospital billing department used one database while the hospital personnel department used a different database. Getting those databases to "talk" to each other can be a large, and expensive, undertaking, yet in a complex hospital system, all the databases need to be involved for good patient and employee care.

5. Restrictions on Data Types
Relational databases come with restraints as to what data types they can store. Complex data types such as 3D images or abstract data objects are not available to store under a relational database. Data types available to a database include numbers, characters (letters), dates and times, or Boolean (true/false) values.

6. Complexity
Relational databases can quickly become complex when the amount of data grows and the relations between pieces of data become more complicated. Furthermore, SQL, while a powerful tool for accessing data from a relational database, is limited in how it accesses data with complicated relationships. Also, representing complex forms of data requires breaking the data into simpler data types that the database supports (such as numbers, letters, and Boolean values). Recombining these values to reconstruct the original data adds to the complexity of the system.

Source :
Anni Martin - eHow

G.S. Jackson - eHow

The Advantages and Benefits of Relational Database And RDBMS

Continuation of my previous post about RDBMS, here is a summary of the relational database and RDBMS advantages from various sources.

Summary

A Relational Database Management System (RDBMS) is a software system that provides access to a relational database. The software system is a collection of software applications that can be used to create, maintain, manage and use the database. A "relational database" is a database structured on the "relational" model. Data are stored and presented in a tabular format, organized in rows and columns with one record per row.

A relational database is one of the most popular ways to store large amounts of data. The method in which the data is organized in a relational database makes it much easier for users to understand that data. It allows information to be easily grouped or compared by type, such as year or name.

A relational database acts as a collection of several separate lists of related information from which data can be extracted. According to the online journal Phyloinformatics, relational database design provides better speed, a more flexible size and higher efficiency than other types of databases. The power of a relational database is that you can continue to add and retrieve data for the duration of a project. Relational databases lend themselves to diverse financial, scientific and marketing uses, given their flexibility and robust data structure.

1. Data Structure
The table format is simple and easy for database users to understand and use. RDBMSs provide data access using a natural structure and organization of the data. Database queries can search any column for matching entries.

2. Multi-User Access
RDBMSs allow multiple database users to access a database simultaneously. Built-in locking and transactions management functionality allow users to access data as it is being changed, prevents collisions between two users updating the data, and keeps users from accessing partially updated records.

3. Privileges
Authorization and privilege control features in an RDBMS allow the database administrator to restrict access to authorized users, and grant privileges to individual users based on the types of database tasks they need to perform. Authorization can be defined based on the remote client IP address in combination with user authorization, restricting access to specific external computer systems.

4. Network Access
RDBMSs provide access to the database through a server daemon, a specialized software program that listens for requests on a network, and allows database clients to connect to and use the database. Users do not need to be able to log in to the physical computer system to use the database, providing convenience for the users and a layer of security for the database. Network access allows developers to build desktop tools and Web applications to interact with databases.

5. Speed
The relational database model is not the fastest data structure. RDBMS advantages, such as simplicity, make the slower speed a fair trade-off. Optimizations built into an RDBMS, and the design of the databases, enhance performance, allowing RDBMSs to perform more than fast enough for most applications and data sets. Improvements in technology, increasing processor speeds and decreasing memory and storage costs allow systems administrators to build incredibly fast systems that can overcome any database performance shortcomings.

6. Maintenance
RDBMSs feature maintenance utilities that provide database administrators with tools to easily maintain, test, repair and back up the databases housed in the system. Many of the functions can be automated using built-in automation in the RDBMS, or automation tools available on the operating system.

7. Language
RDBMSs support a generic language called "Structured Query Language" (SQL). The SQL syntax is simple, and the language uses standard English language keywords and phrasing, making it fairly intuitive and easy to learn. Many RDBMSs add non-SQL, database-specific keywords, functions and features to the SQL language.

8. Functions
Relational databases offer a wide array of functions that create advanced data manipulation techniques. They support relational algebra as well as set operations. This includes operations to select, join and divide. Many of these operations are possible only with relational databases. This data manipulation is possible with the use of queries, generally in Structured Query Language (SQL), to manage the data and give users the information they are looking for.

9. Efficiency
Efficiency is one of the benefits to a relational database. Because of the ways in which data can be compared and combined, each piece of data needs to be saved in only one place. This is related to the process of normalizing the database, which eliminates the need for duplicate data. Since each piece of data is saved in only one location, it is much easier and quicker to update. The language used to create the database, SQL, can also validate date entry.

10. Views
Because relational databases are highly customizable, users can present data in any way they wish. One of the most important concepts of the database is known as a view. Although the data is stored in tables, which may never change attributes, users can customize or delete a view easily without affecting the data. Views manipulate the data to present the important pieces that users would like to see, while removing the unnecessary data that is not used. This is similar to copying and pasting the important parts of documents into one file.

11. Scalable
Both in the number of records, or rows, and the number of fields, a database can be enlarged to enhance its usability. Whether a relational database is complex or simple, the size is scalable. Records can be added over time. The flexible structure also allows the addition of new fields not anticipated in the initial database design. By adding records and fields, the size of the database can be increased for a particular project.

12. Minimizes Duplication and Inconsistencies
The relational database inherently lacks duplication. Data entry is streamlined because the user enters and stores a particular bit of information only once. This allows for less data entry, which minimizes data entry errors. The design eliminates inconsistencies in data retrieval and formatting.

13. Generate Metadata
The streamlined design of a relational database allows for generation of metadata, or data about data. For example, the user can generate statistics about the frequency that a value occurs in the table.

Source :

Deborah Lee Soltesz - eHow

Travis Larson - eHow

Janet Roemmel - eHow

The Differences Between RDBMS and DBMS

Back to data and database, I was looking about the differences between RDBMS and DBMS and found an insight from eHow. There are several articles related, and i will share them in this blog.

Major Differences Between a Relational Database Management System & a Database Management System

A database management system (DBMS) is a computer software application that allows users to create, control, and maintain a database. A relational database management system (RDBMS) is a more complex DBMS that stores data in two-dimensional tables that are related to one another via keys. While both database applications have their uses, businesses generally prefer relational database management systems because they can handle larger sets of data.

1. Data Storage
A basic DBMS simply stores data in a persistent form, in that the data remains unaltered even if the application that created it crashes or is deleted. The data is organized into rows and columns. A database management system also maintains a set of rules so that users can access the data. A relational database management system adds a tabular structure with tables. Each table column is a field, and each table row is a record.

2. Relationships
In an RDBMS, interdependent relationships exist between sets of data. Each table has a unique field known as a key. Tables that share a key host a one-to-many, many-to-many or one-to-one relationship. There are no relationships in a DBMS, so the rows and columns are independent of one another. The benefit of maintaining relationships is referential integrity. Every time a record is changed, all related records from other tables are immediately updated.

3. Redundancy
A relational database management system is prone to less redundancy because everything is related. Therefore, duplicate sets of data cannot exist. This ensures that the data stored is accurate and relevant. A DBMS, on the other hand, allows duplicated data, which can cause data redundancy, data inconsistency and poor data integrity.

4. Additional Differences
A database management system is less secure, and it can only be used by a single user. In addition, a DBMS cannot be shared on a network or the Internet. An RDBMS can be shared online, and it supports much larger sets of data. However, a relational database management system requires more complex hardware and software, and it is much more difficult to set up.

Source : Vivek Saxena - eHow

My First Domain : NicoBudiDarmawan.com

Today I just bought my first domain.
I'm very excited right now and have completed the basic setup.
Now, what I have to do is just wait for another 60 minutes until Google verify my domain.
Meanwhile, I also found that i got free professional email address.

So I decided to change my email to me@NicoBudiDarmawan.com

One Hour Later...
Finally... It is done!
Yeeeaaayyy...

Please welcome the new personal website : www.NicoBudiDarmawan.com
:)

Overview About Database Types

This week menu are about data and database. I searched about database from internet to enrich our knowledge about it. This post contained general description about various type of database from eHow. I have only built 2 type of database, hierarchical and relational model. It was at college, and until now I've only implement relational model in my job. Later, i will add several posting related with each specific type of database. 

Different Types Databases

A database consists of various types of data that are organized for a number of uses. An address book is a simple example of a database, and a more elaborately designed database might hold demographic statistics used by governmental organizations or large research corporations. Different types of databases contain different types of data.

1. Hierarchical Model
The hierarchical model database consists of child and parent data elements organized into a tree structure. The only connection permitted between two data sets as that of parent and child. The problem here can be illustrated in a very literal sense; if a company created a hierarchical database listing employees and their families, one employee would be represented by one parent element and if the employee had three children, they would be represented by three parent elements (one each). This means each child is only permitted by the system to have one parent.

2. Network Model
The network model database defined in the 1971 Conference in Data Systems Languages extends upon the hierarchical database in that data sets are permitted a many-to-many relationship. This is important because in some circumstances, child elements of data may need two parent elements, such as in the previous example. The CODASYL network model database is based on mathematical set theory, and each parent element of data can be a child element in one or more sets in the database's set construct.

3. Relational Model
In a relational model database, all data and the relationships between data are organized into tables. Each table collates data and separates each unique entry into a new row. Every row contains the same associated data fields. Each data field column has it's own unique name and corresponding data type. For example in an iTunes play list, each song is placed on its own row. Data fields each occupy their own column, and all data formats are unified. The order of rows or columns is not significant.
   
4. Object/Relational Model
Databases constructed with the object/relation model add new capabilities to standard tabular database systems. In addition to conventional fielded data, more intricate binary media can be stored, such as images, audio, video and applets. The user can perform complex analytical and manipulative operations to convert multimedia and other composite objects encapsulated within the data structure.

Source : eHow -Jezen Thomas

Sifat Basis Data / Database

Berikut adalah kutipan dari Blogger lain mengenai beberapa sifat database. Kutipan artikel tersebut saya edit, atas dasar pengalaman saya pribadi dan beberapa teori yang mungkin tidak sejalan atau sepaham dengan penulis aslinya. Selamat membaca. :)

1. Berbagi Data
Data yang disimpan didalam basis data tidak secara umum dipegang semata-mata untuk hanya digunakan oleh seseorang. Suatu basis data secara normal diharapkan bisa diakses oleh lebih dari satu orang, dan mungkin pada waktu yang bersamaan.

Contoh :

• Supermarket. Data penjualan bisa diakses oleh sistem pengontrol persediaan dan sistem informasi manajemen.
• Rumah Sakit. Dalam melakukan pengelolaan data historikal penyakit / pengobatan pasien, menangani pembayaran, perawatan, bisa diakses oleh dokter, suster dan bagian apotek, staff admin, dsb.
• Institusi Pendidikan / Sekolah. Dalam melakukan pengelolaan data siswa, penjadwalan kegiatan, perkuliahan, nilai, data bisa di akses oleh guru, kepala sekolah, dan staff TU, serta murid yang berkepentingan.

2. Integrasi Data
Integrasi data adalah suatu proses menggabungkan atau menyatukan data yang berasal dari sumber yang berbeda dan mendukung pengguna untuk melihat kesatuan data. Integrasi data dibutuhkan seiring dengan perkembangan organisasi dan meningkatnya bisnis proses pada institusi tersebut yang saling membutuhkan data-data dan informasi dari divisi atau unit-unit yang berada pada organisasi tersebut.

Tahap-tahap pengembangan dari integrasi bisnis proses adalah : 

• Meninjau dan menganalisis setiap SOP (Standard Operating Procedure) setiap unit.
• Analisis keterkaitan proses berdasarkan SOP antar unit.
• Gunakan tools work flow diagram untuk memudahkan proses integrasi.
• Buat kesepakatan dan kesepahaman antar pihak manajemen disetiap unit.

Contoh : 

• Data Warehouse. Sebuah bank memerlukan integrasi data antara deposit sistem, loan sistem, client database, dll.
• Puskesmas. Diperlukan integrasi antara data dari poli umum , poli gigi , poli mata , poli anak untuk membuat aplikasi database-nya.

3. Integritas Data
Integritas data adalah berhubungan dengan penjagaan dari perubahan data secara tidak sah. Untuk menjaga integritas data, sistem harus memiliki kemampuan untuk mendeteksi manipulasi data oleh pihak-pihak yang tidak berhak, antara lain penyisipan, penghapusan, dan pensubsitusian data lain kedalam data yang sebenarnya.

Contoh :

• Kantin / Restoran. Bila ada promo free makanan, terkadang kasir bisa saja tidak memberikan hak free pada konsumen, olehkarena itu diperlukan integritas agar tidak terjadi kecurangan dan manipulasi data lainnya.
• Operator / Provider Internet. Pihak penjual jasa terkait mengeluarkan tagihan sesuai pemakaian konsumen dan tanggap terhadap keluhan-keluhan konsumen, tidak menerima uang upah disetiap service nya, yang tentunya disesuaikan dengan peraturan yang ada.

4. Keamanan Data
Keamanan merupakan suatu proteksi terhadap pengrusakan data dan pemakaian data oleh pihak yang tidak punya kewenangan.

#Bentuk Penyalahgunaan Database : 

A. Tidak disengaja, jenisnya :

• Kerusakan selama proses transaksi.
• Anomali yang disebabkan oleh akses database yang konkuren.
• Anomali yang disebabkan oleh pendistribuasian data pada beberapa komputer.
• Logika error yang mengancam kemampuan transaksi untuk mempertahankan konsistensi database.

B. Disengaja, jenisnya : 

• Pengambilan data / pembacaan data oleh pihak yang tidak berwenang.
• Pengubahan data oleh pihak yang tidak berwenang.
• Penghapusan data oleh pihak yang tidak berwenang.

#Tingkatan Keamanan Database :

A Fisikal : 
Lokasi-lokasi dimana terdapat sistem komputer haruslah aman secara fisik terhadap serangan perusak.
B. Manusia : 
Wewenang / hak pemakai harus dilakukan dengan berhati-hati untuk mengurangi kemungkinan adanya manipulasi oleh pihak terkait.
C. Sistem Operasi & Jaringan : 
Kelemahan pada sistem operasi dan jaringan memungkinkan pengaksesan data oleh pihak tidak berwenang, karena hampir seluruh jaringan sistem database menggunakan akses jarak jauh / remote.

#Keamanan Data :

A. Otorisasi :

• Pemberian wewenang atau hak istimewa (privilege) untuk mengakses sistem atau obyek database.
• Kendali otorisasi (kontrol akses) dapat dibangun pada perangkat lunak dengan 2 fungsi :
• Mengendalikan sistem atau obyek yang dapat diakses.
• Mengendalikan bagaimana pengguna menggunakannya.
• Sistem administrasi yang bertanggung jawab untuk memberikan hak akses dengan membuat account pengguna.

B. Tabel View :
 
Merupakan metode pembatasan bagi pengguna untuk mendapatkan model database yang sesuai dengan kebutuhan perorangan. Metode ini dapat menyembunyikan data yang tidak digunakan atau tidak perlu dilihat oleh pengguna.

Contoh pada database relasional, untuk pengamanan dilakukan beberapa level :

• Relasi = pengguna diperbolehkan atau tidak diperbolehkan mengakses langsung suatu relasi.
• View = pengguna diperbolehkan atau tidak diperbolehkan mengakses data yang terdapat pada view.
• Read Authorization = pengguna diperbolehkan membaca data, tetapi tidak dapat memodifikasi.
• Insert Authorization = pengguna diperbolehkan menambah data baru, tetapi tidak dapat memodifikasi data yang sudah ada.
• Update Authorization = pengguna diperbolehkan memodifikasi data, tetapi tidak dapat menghapus data.
• Delete Authorization = pengguna diperbolehkan menghapus data.

Untuk modifikasi data terdapat otorisasi tambahan :

• Index Authorization = pengguna diperbolehkan membuat dan menghapus index data.
• Resource Authorization = pengguna diperbolehkan membuat relasi-relasi baru.
• Alteration Authorization = pengguna diperbolehkan menambah/menghapus atribut suatu relasi.
• Drop Authorization = pengguna diperbolehkan menghapus relasi yang sudah ada. 

C. Backup Data & Recovery :
 
Backup : proses secara periodik untuk mebuat duplikat dari database dan melakukan logging file (atau program) ke media penyimpanan eksternal.

Jurnaling : proses menyimpan dan mengatur log file dari semua perubahan yang dibuat di database untuk proses recovery yang efektif jika terjadi kesalahan.

• Isi Jurnal : 

• Record Transaksi :

1. Identifikasi dari record
2. Tipe record jurnal (transaksi start, insert, update, delete, abort, commit)
3. Item data sebelum perubahan (operasi update dan delete)
4. Item data setelah perubahan (operasi insert dan update)
5. Informasi manajemen jurnal (misal : pointer sebelum dan record jurnal selanjutnya untuk semua transaksi

• Record Checkpoint : Suatu informasi pada jurnal untuk memulihkan database dari kegagalan. Apabila sekedar redo, akan sulit mengetahui sejauh mana penyimpanan jurnal dapat mencarinya kembali, maka untuk membatasi pencarian dapat menggunakan teknik ini.

Recovery / Pemulihan : merupakan upaya uantuk mengembalikan basis data ke keadaan yang dianggap benar setelah terjadinya suatu kegagalan. 

• 3 Jenis Pemulihan / Recovery :
• Pemulihan terhadap kegagalan transaksi : Kesatuan prosedur dalam program yang dapat mengubah / memperbaharui data pada sejumlah tabel.
• Pemulihan terhadap kegagalan media : Pemulihan karena kegagalan media dengan cara mengambil atau memuat kembali salinan basis data (backup)
• Pemulihan terhadap kegagalan sistem : Karena gangguan sistem, hang, listrik terputus alirannya.

• Fasilitas Pemulihan / Recovery pada DBMS :
• Mekanisme backup secara periodik.
• Fasilitas logging dengan membuat track pada tempatnya saat transaksi berlangsung dan pada saat database berubah.
• Fasilitas checkpoint, melakukan update database yang terbaru.
• Manager pemulihan, memperbolehkan sistem untuk menyimpan ulang database menjadi lebih konsisten setelah terjadinya kesalahan.

• Teknik Pemulihan :
• Deferred Update / Perubahan Tunda : perubahan pada DB tidak akan berlangsung sampai transaksi ada pada poin disetujui (COMMIT). Jika terjadi kegagalan maka tidak akan terjadi perubahan, tetapi diperlukan operasi REDO untuk mencegah akibat dari kegagalan tersebut.
• Immediate Update / Perubahan Langsung : perubahan pada DB akan segera tanpa harus menunggu sebuah transaksi tersebut disetujui. Jika terjadi kegagalan diperlukan operasi UNDO untuk melihat apakah ada transaksi yang telah disetujui sebelum terjadi kegagalan.
• Shadow Paging : Menggunakan page bayangan dimana pada prosesnya terdiri dari 2 tabel yang sama, yang satu menjadi tabel transaksi dan yang lain digunakan sebagai cadangan. Ketika transaksi mulai berlangsung, kedua tabel ini adalah sama. Selama berlangsungnya proses, tabel transaksi akan menyimpan semua perubahan ke database, tabel bayangan akan digunakan jika terjadi kesalahan. Keuntungannya adalah tidak membutuhkan REDO atau UNDO, kelemahannya membuat terjadinya fragmentasi.

D. Kesatuan Data & Enkripsi :

Enkripsi : keamanan data.

Integritas : metode pemeriksaan dan validasi data (metode integrity constrain), yaitu berisi aturan-aturan atau batasan-batasan untuk tujuan terlaksananya integritas data.

Konkuren : mekanisme untuk menjamin bahwa transaksi yang konkuren pada database multi user tidak saling menganggu operasinya masing-masing. Adanya penjadwalan proses yang akurat (Time Stamping).

Contoh : Dalam PEMILU , semua hasil coblosan dari TPS se-indonesia disalin ke DBMS , lalu dilakukan perhitungan yang sah

5. Abstraksi Data
Abstraksi data merupakan tingkatan-tingkatan pengguna dalam memandang bagaimana sebenarnya data diolah dalam sebuah sistem database sehingga menyerupai kondisi yang sebenarnya dihadapi oleh pengguna sehari-hari. Sebuah DBMS seringkali menyembunyikan detail tentang bagaimana sebuah data disimpan dan dipelihara (diolah) dalam sebuah sistem database, dengan tujuan untuk memudahkan pengguna dalam menggunakan DBMS tersebut. Karena itu seringkali data yang terlihat oleh pemakai sebelumnya berbeda dengan yang tersimpan secara fisik.

Terdapat 3 Level abstraksi data:

A. Level Fisik (Physical Level)

• Level terendah dalam abstraksi data, yang menunjukkan bagaimana sesungguhnya suatu data disimpan. User melihat data sebagai gabungan dari struktur dan datanya sendiri. Tingkatan ini berurusan dengan:

• Alokasi ruang penyimpanan untuk data dan indeks.
• Deskripsi record untuk penyimpanan.
• Penempatan record data.
• Teknik kompresi dan enkripsi data.

B. Level Konseptual (Conceptual Level)

• Menggambarkan data apa yang sebenarnya disimpan dalam database, serta hubungannya (relationship) dengan data lainnya. Berisi struktur logika database yang hanya dapat dilihat oleh DBA. Tingkat konsepsual ini menyatakan:

• Entitas, atribut dan relasinya.
• Konstrain-konstrain terhadap data.
• Informasi semantiks data.
• Informasi keamanan dan integritas data.

C. Level Pandangan (View Level)

• Level tertinggi dari abstraksi data, hanya menampilkan sebagian data dari database. Tidak semua user membutuhkan semua data dalam database. Sebagai contoh:
• Karyawan= RECORD
• Nama : STRING;
• Alamat : STRING;
• Bagian : STRING;
• Gaji : LONGINT;

• Pada contoh ini record pegawai berisi 4 buah field (nama, alamat, bagian, gaji ). Setiap field memiliki nama, dan setiap nama memiliki tipe data.

Pada level fisik, pegawai dapat dijabarkan sebagai blok data yang terletak pada lokasi berurutan (satuan byte). Pada level konseptual masing-masing record dijabarkan dengan definisi tipe data. Pada level pandangan, user tertentu hanya boleh mengakses data tertentu, contohnya: seorang yang menangani penggajian berhak mengetahui gaji seseorang bahkan mengubahnya, tetapi orang yang bekerja di bagian lain tentu tidak boleh melihatnya.

6. Independansi Data
Adalah kemampuaan untuk membuat perubahan dalam struktur data tanpa membuat perubahan pada program yang memproses data. Struktur data pada database terpisah dengan program Perubahan struktur data tidak membuat program harus dimodifikasi. Perubahan struktur database dimungkinkan terjadi tanpa harus mengubah aplikasi yang mengaksesnya sehingga pembuatan antarmuka ke dalam data akan lebih mudah dengan penggunaan DBMS.

Dua Jenis Independensi Data :

• Independensi Fisik: Skema logis tetap tidak berubah meskipun ruang penyimpanan atau jenis beberapa data yang berubah untuk alasan pengoptimalan atau reorganisasi. Perubahan skema internal mungkin diperlukan karena beberapa skema fisik ditata ulang kembali. Independensi data secara fisik yaitu teknik atau cara penyimpanan dan pengaksesan data dalam fisik media penyimpan dapat mengalami perubahan tanpa harus mengubah deskripsi logis basis data (Global Logical Data/ Conceptual View) yang digunakan dalam schema basis data.

• Independensi Logis: Skema eksternal mungkin tetap tidak berubah untuk perubahan dari skema logis. Hal ini sangat diinginkan agar perangkat lunak aplikasi tidak perlu dimodifikasi atau diterjemahkan ulang. Independensi data secara logis, dimaksudkan bahwa kebutuhan-kebutuhan data para pemakai dapat mengalami perubahan tanpa harus mengubah pandangan logis pemakai terhadap basis data atau deskripsi logis basis data yang digunakan dalam schema basis data.

Terima kasih...
Sumber / Source : 
Blogger 1  Blogger 2 
Blog 1 Source A Blog 2 Source A 
Blog 1 Source B Blog 2 Source B
Blog 1 Source C Blog 2 Source C

20 Database Design Best Practices

I found this article quite useful and also gave me more knowledge about how to design a good database. Please take a look. :)

1. Use well defined and consistent names for tables and columns (e.g. School, StudentCourse, CourseID ...).
   
2. Use singular for table names (i.e. use StudentCourse instead of StudentCourses). Table represents a collection of entities, there is no need for plural names.
   
3. Don’t use spaces for table names. Otherwise you will have to use ‘{‘, ‘[‘, ‘“’ etc. characters to define tables (i.e. for accesing table Student Course you'll write “Student Course”. StudentCourse is much better).
   
4. Don’t use unnecessary prefixes or suffixes for table names (i.e. use School instead of TblSchool, SchoolTable etc.).
   
5. Keep passwords as encrypted for security. Decrypt them in application when required.
   
6. Use integer id fields for all tables. If id is not required for the time being, it may be required in the future (for association tables, indexing ...).
   
7. Choose columns with the integer data type (or its variants) for indexing. varchar column indexing will cause performance problems.
   
8. Use bit fields for boolean values. Using integer or varchar is unnecessarily storage consuming. Also start those column names with “Is”.
   
9. Provide authentication for database access. Don’t give admin role to each user.
   
10. Avoid “select *” queries until it is really needed. Use "select [required_columns_list]" for better performance.
   
11. Use an ORM (object relational mapping) framework (i.e. hibernate, iBatis ...) if application code is big enough. Performance issues of ORM frameworks can be handled by detailed configuration parameters.
   
12. Partition big and unused/rarely used tables/table parts to different physical storages for better query performance.
   
13. For big, sensitive and mission critic database systems, use disaster recovery and security services like failover clustering, auto backups, replication etc.
   
14. Use constraints (foreign key, check, not null ...) for data integrity. Don’t give whole control to application code.
   
15. Lack of database documentation is evil. Document your database design with ER schemas and instructions. Also write comment lines for your triggers, stored procedures and other scripts.
   
16. Use indexes for frequently used queries on big tables. Analyser tools can be used to determine where indexes will be defined. For queries retrieving a range of rows, clustered indexes are usually better. For point queries, non-clustered indexes are usually better.
   
17. Database server and the web server must be placed in different machines. This will provide more security (attackers can’t access data directly) and server CPU and memory performance will be better because of reduced request number and process usage.
   
18. Image and blob data columns must not be defined in frequently queried tables because of performance issues. These data must be placed in separate tables and their pointer can be used in queried tables.
   
19. Normalization must be used as required, to optimize the performance. Under-normalization will cause excessive repetition of data, over-normalization will cause excessive joins across too many tables. Both of them will get worse performance.
   
20. Spend time for database modeling and design as much as required. Otherwise saved(!) design time will cause (saved(!) design time) * 10/100/1000 maintenance and re-design time.

Source : Cagdas Basaraner - DZone

Sekilas Pengertian dan Penjelasan Tentang Database atau DBMS

Tentang Database :

1. Wikipedia
Database Defitinion : A database is an organized collection of data. The data is typically organized to model relevant aspects of reality (for example, the availability of rooms in hotels), in a way that supports processes requiring this information (for example, finding a hotel with vacancies).

2. About.Com
Database Definition: A database is a collection of information organized into interrelated tables of data and specifications of data objects.

3. Sri Kusumadewi & Hari Purnomo
 Basis data (database) merupakan kumpulan dari data – data yang saling berhubungan antara satu dengan yang lainnya dan disimpan dalam perangkat keras komputer dan dimanipulasi dengan perangkat lunak.

4. Gordon C. Everest
Database adalah koleksi atau kumpulan data yang mekanis, terbagi/shared, terdefinisi secara formal dan dikontrol terpusat pada organisasi.

5. C.J. Date
Database adalah koleksi “data operasional” yang tersimpan dan dipakai oleh sistem aplikasi dari suatu organisasi.

• Data input adalah data yang masuk dari luar sistem.
• Data output adalah data yang dihasilkan sistem.
• Data operasional adalah data yang tersimpan pada sistem.

6. Toni Fabbri
Database adalah sebuah sistem file-file yang terintegrasi yang mempunyai minimal primary key untuk pengulangan data.

7. S. Attre
Database adalah koleksi data-data yang saling berhubungan mengenai suatu organisasi / enterprise dengan macam-macam pemakaiannya.

8. Radiant Victor Imbar & Bernard Renaldy Suteja
Definisi Basis Data (Database) didefinisikan dalam sejumlah sudut pandang seperti :

• Sistem terkomputerisasi yang tujuan utamanya adalah memelihara informasi dan membuat informasi tersebut tersedia saat dibutuhkan.
• Kumpulan data yang saling berhubungan yang disimpan ditempat penyimpanan dan dapat dimanfaatkan kembali dengan cepat dan mudah.

9. PCMag.Com
A set of related files that is created and managed by a database management system (DBMS).

10. Nico Budi Darmawan Tan
Basis data adalah kumpulan data yang saling berhubungan dan dikelola oleh sistem dengan tujuan untuk menghasilkan informasi.

Database is a collection of related data and managed by the system in order to generate information.

=================================

Tentang DBMS :

1. About.Com
A database management system (DBMS) is the software that allows a computer to perform database functions of storing, retrieving, adding, deleting and modifying data. Relational database management systems (RDBMS) implement the relational model of tables and relationships.

2. Radiant Victor Imbar & Bernard Renaldy Suteja
DBMS adalah sistem untuk memasukan, mengubah, menghapus, memanipulasi dan dapat memperoleh data/informasi dengan praktis dan efisien. DBMS mengatur proses pengambilan dan penyimpanan data seperti sistem operasi yang mengatur sumber daya didalam komputer. DBMS akan menentukan bagaimana data diorganisasi, disimpan, dibuah dan diambil kembali. DBMS juga menerapkan mekanisme pengamanan data, pemakaian data secara bersama-sama oleh beberapa aplikasi dan menjaga kehandalan data.

3. English Wikipedia
Database management systems (DBMSs) are specially designed applications that interact with the user, other applications, and the database itself to capture and analyze data. A general-purpose database management system (DBMS) is a software system designed to allow the definition, creation, querying, update, and administration of databases.

4. PCMag.Com
DataBase Management System Software that controls the organization, storage, retrieval, security and integrity of data in a database. It accepts requests from the application and instructs the operating system to transfer the appropriate data.

5. Nico Budi Darmatan Tan
DBMS (Data  Base Management System) adalah sistem yang digunakan untuk proses pengolahan data seperti memasukan, mengubah, menghapus, mengambil, menyimpan, dsb. Setiap DBMS juga dilengkapi fitur keamanan, integritas, kehandalan dan interaktivitas lainnya yang tentunya disesuaikan dengan harga dari sistem tersebut.

DBMS is a system used for data processing such as insert, modify, delete, retrieve, store, etc.. Each DBMS also features security, integrity, reliability and other custom interactivity that depends on the brand of the system.

Pengertian dan Perbedaan Data dengan Informasi

#KBBI (Kamus Besar Bahasa Indonesia)

• Data = Keterangan atau bahan nyata yg dapat dijadikan dasar kajian analisis atau kesimpulan.

• Informasi = Penerangan; Pemberitahuan; Kabar atau berita tentang sesuatu.

#Oxford

• Data = Facts and statistics collected together for reference or analysis.

• Information = Facts provided or learned about something or someone.

#Wikipedia Indonesia

• Data = Adalah catatan atas kumpulan fakta. Data merupakan bentuk jamak dari datum, berasal dari bahasa Latin yang berarti "sesuatu yang diberikan".

• Informasi = Adalah pesan (ucapan atau ekspresi) atau kumpulan pesan yang terdiri dari order sekuens dari simbol, atau makna yang dapat ditafsirkan dari pesan atau kumpulan pesan.

#English Wikipedia

• Data = Are values of qualitative or quantitative variables, belonging to a set of items.

• Information = In its general sense, is "Knowledge communicated or received concerning a particular fact or circumstance".

#Nico Budi Darmawan Tan

• Data = Adalah suatu fakta yang bentuknya bisa tulisan, suara, gambar, angka, simbol, dan sebagainya,  ataupun informasi yang disimpan kembali sebagai suatu fakta yang mana belum memiliki arti yang jelas.

• Data = Is a fact, that its shape can be text, sound, images, numbers, symbols, and so forth, or the information stored back as a fact, which has not had a clear meaning.

• Informasi = Adalah data yang telah diproses dan telah memiliki arti yang dapat digunakan untuk tujuan tertentu ataupun pengambilan keputusan.

• Information = Is data that has been processed and has a meaning that can be used for a particular purpose or decision-making. 

 ==================

Pembagian Data dalam Ilmu Eksak Sains Statistik / Statistika

A. Jenis Data Menurut Cara Memperolehnya

• Data Primer adalah secara langsung diambil dari objek / obyek penelitian oleh peneliti perorangan maupun organisasi. Contoh : Mewawancarai langsung penonton bioskop 21 untuk meneliti preferensi konsumen bioskop.

• Data Sekunder adalah data yang didapat tidak secara langsung dari objek penelitian. Peneliti mendapatkan data yang sudah jadi yang dikumpulkan oleh pihak lain dengan berbagai cara atau metode baik secara komersial maupun non komersial. Contohnya adalah pada peneliti yang menggunakan data statistik hasil riset dari surat kabar atau majalah.

B. Macam-Macam Data Berdasarkan Sumber Data

• Data Internal adalah data yang menggambarkan situasi dan kondisi pada suatu organisasi secara internal. Misal : data keuangan, data pegawai, data produksi, dsb.

• Data Eksternal adalah data yang menggambarkan situasi serta kondisi yang ada di luar organisasi. Contohnya adalah data jumlah penggunaan suatu produk pada konsumen, tingkat preferensi pelanggan, persebaran penduduk, dan lain sebagainya.

C. Klasifikasi Dara Berdasarkan Jenis Datanya

• Data Kuantitatif adalah data yang dipaparkan dalam bentuk angka-angka. Misalnya adalah jumlah pembeli saat hari raya idul adha, tinggi badan siswa kelas 3 ips 2, dan lain-lain.

• Data Kualitatif adalah data yang disajikan dalam bentuk kata-kata yang mengandung makna. Contohnya seperti persepsi konsumen terhadap botol air minum dalam kemasan, anggapan para ahli terhadap psikopat dan lain-lain.

D. Pembagian Jenis Data Berdasarkan Sifat Data

• Data Diskrit adalah data yang nilainya adalah bilangan asli. Contohnya adalah berat badan ibu-ibu pkk sumber ayu, nilai rupiah dari waktu ke waktu, dan lain-sebagainya.

• Data Kontinyu adalah data yang nilainya ada pada suatu interval tertentu atau berada pada nilai yang satu ke nilai yang lainnya. Contohnya penggunaan kata sekitar, kurang lebih, kira-kira, dan sebagainya. Dinas pertanian daerah mengimpor bahan baku pabrik pupuk kurang lebih 850 ton.

E. Jenis-jenis Data Menurut Waktu Pengumpulannya

• Data Cross-Section adalah data yang menunjukkan titik waktu tertentu. Contohnya laporan keuangan per 31 desember 2006, data pelanggan PT. angin ribut bulan mei 2004, dan lain sebagainya.

• Data Time Series / Berkala adalah data yang datanya menggambarkan sesuatu dari waktu ke waktu atau periode secara historis. Contoh data time series adalah data perkembangan nilai tukar dollar amerika terhadap euro eropa dari tahun 2004 sampai 2006, jumlah pengikut jamaah nurdin m. top dan doktor azahari dari bulan ke bulan, dll.

Sumber : Organisasi.Org

Biblical Confessions for Financial Prosperity

Proverbs 18:21 says that death and life are in the power of the tongue; therefore, I speak the Word of God concerning my finances:

1. The favor of God surrounds me and precedes me.
Psalm 5:12, Proverbs 11:27, Proverbs 12:2

2. The blessings of God are chasing me and overtaking me.
Deuteronomy 28:1-2, Luke 6:38

3. Whatever I set my hand to shall prosper.
Joshua 1:8, Psalm 1:3, Psalm 35:27, III John 2

4. My Heavenly Father gives me power to get wealth.
Deuteronomy 8:18, Proverbs 8:17-21, II Corinthians 8:9

5. God is liberally supplying all my needs according to His riches in glory by Christ Jesus.
Philippians 4:19, Proverbs 3:9-10, Matthew 6:8 & 33

6. I no longer live under the curse of poverty. The windows of Heaven are open to me, and God rebukes the devourer for my sake.
Malachi 3:8-12, Galatians 3:13-14

7. The Lord has commanded me to be blessed; therefore, I am blessed and cannot be cursed. I am the head and not the tail. I am above only and not beneath. I will lend and not borrow.
Deuteronomy 28:3-14, Proverbs 10:6

Source : Lakewood Church

Realize

See many things happened, I realize that I must try harder to reach you and your love..
I realize that I must keep fighting for you, your attention, and your love..

I realize that I must keep my spirit of love bigger and stronger..

Although day by day passed..

Week by week passed..

I am still here for you..

I still in love with you..

Hope you will come back for me..

Business Intelligence Implementation Guiding Principles

Adding previous post about BI Agile Implementation, this post related about the guidance and principles about BI Implementation.

11 Guiding Principles for a Successful Business Intelligence Implementation 

Business intelligence is not a software solution. It’s a methodology and process that uses technology as a way to implement change. Here are 11 steps, or guiding principles, for a successful business intelligence implementation from Booz & Co.

1. Drive Change From The Top Down and The Bottom Up
Like any new solution, business intelligence is only effective if people use it. For it to be completely adopted, it needs to be used not only by line managers but also by executives.
 
2. Create a Comprehensive Definition of Business Intelligence
Business intelligence puts emphasis on measuring performance against goals and establishing accountability for reaching those goals. Make sure you have the supporting processes, systems and change management protocols in place to support this new way of running the business.

3. Use an Agile, Modular Approach
You can achieve more flexible and more effective implementations of business intelligence faster with agile development and by focusing on specific areas that are guided by an integrated, overall strategy.

4. Focus On The Right Metrics
Metrics must be aligned with the company’s strategy and capabilities, including both internal and external inputs, and consisting of both leading and lagging indicators.

5. Keep It Simple
Even though technology might let you drill down 16 levels into the data or slice and dice it 100 different ways, that kind of analysis may be irrelevant and distracting. Be selective by including a few key metrics that are the most important and drill down to the top three or four levels.

6. Build a Unified BI System
Some of the most important insight from a BI system can come from discovering how interdependencies impact outcomes across an organization. Integrate data and data analytics across the organization to allow for custom analytics that can identify root causes of issues.

7. Launch Early
To gain acceptance and support you may need some early wins with a BI project. Start with high-priority areas that have high-quality metrics. Demonstrate the value of a BI solution to help build momentum for the project.

8. Create Detailed System Requirements and Select The Right Partners
Successful BI implementations require a partnership between IT and the lines of business. They also require strong project management skills, systems integration know-how and software tools. Make sure you’ve included and selected the right teams across all these areas.

9. Leverage Existing Infrastructure
Business Intelligence implementations should align with a company’s IT strategy and vision for how IT will support the business. BI should complement existing IT capability which can often be achieved by leveraging current IT infrastructure to provide the back end, and using business intelligence solutions to provide the front end.

10. Establish a Centralized Governance Structure
A business intelligence implementation can touch every  area of an enterprise. It requires cooperation and shared ownership from the business and IT, new data management protocols, strong project management and ongoing analysis of the metrics used. For maximum success, you should create an overall governance structure led by the business and supported by IT.

11. Proactively Manage Change
Introducing business intelligence requires extensive change management. When evaluating performance against metrics, there should be clear accountability, consequences for not meeting goals and incentives for exceeding them. As with any initiative that requires change management, a successful BI implementation requires senior leadership support, training and communication throughout the enterprise.

Source : Steve McDonnell - Spotfire Tibco

Business Intelligence Agile Implementation

It has been a while since my last post about the Business Intelligence. Today's post is about agile BI implementation. This article come from Tibco (My Competitor) that give me some insight and knowledge. If you are interested with the agile implementation, maybe this post will give you some hint. :)

6 Steps To an Agile Business Intelligence Implementation

Interest in using the Agile Methodology for business intelligence development and implementation is increasing. Agile methods emphasize a strong partnership between end-users and developers that can increase user adoption and help limit scope to the most critical functionality. It also helps users benefit from BI solutions faster, in smaller pieces, rather than all at once when a single solution is delivered. Ken Collier, a senior consulting with Cutter Consortium, describes six steps to achieving a successful agile business intelligence implementation.

1. Involve Users Early and Often

End users should be involved in the process from the very beginning and should stay engaged on a daily or weekly basis. This will help ensure that end-users get the functionality they need the way they need it.

2. Features First, Not Data First

Some BI implementations may begin with collecting, merging and cleaning the different data sources available within an organization. However, with Agile development, the features and functionality required by users should be defined first, followed by a strategy of how to gather the right data to deliver that functionality.

3. Prioritize Based On Value

When working with end-users to identify the functionality they need, the Agile methodology also requires you to divide that functionality into smaller deliverables over short periods of time. This encourages end-users to prioritize the features they really need that will be delivered first.

4. Test Automation

Using automated testing tools is critical to being able to deliver functionality in short iterations. Without these tools, manual testing will consume too much time in the process.

5. Encourage Collaboration Among Developers

Collaboration and cooperation among developers with different roles in a project, such as data modelers, database administrators and BI developers, are paramount. The greatest success will result when everyone on the development team works together as a single unit, regardless of their individual roles.

6. Start Quickly Out of the Gate

Everybody on the team, including end-users and developers, need to start working on their portions of the project immediately. While some of the pressure of fast deliverables can be intense, it makes for reduced stress in the long-run.

Source : Steve McDonnell - Spotfire Tibco

My First Holiday Experience In Hospital - Part 2

Thanks God, I've been out from the hospital last night. This post is a continuation of my last story about my first holiday experience in hospital. I'm glad for not having serious injury in my internal organ. Just like my first doctor at the beginning, my last doctor also told me that i got infection at my stomach. But he said not to worry about that injury, but focus on how you treat your body next. And then, the hospital also gave me several 'gifts'. Something that every person hate, "Drugs". Damn, i have to finish my treatment at home, eat correctly, and take the medicine on time.

Before leave, i was shocked about the payment. Well, just 2 days of vacation here, but it cost a lot. I won't mention the exact number here, but i give a clue : several millions rupiahs not dollars. Expensive huh?! The most thing i hate, there was a administration fee 7.5%. I think it's similar with the tax. People must pay tax because of their sickness. Dumb. Maybe i shouldn't worry about that, because i think my company will pay it for me. But still, it is not the true meaning. We should manage and treat our body well. We should keep our balance between life, jobs and health. Stay healthy. :)

Pengalaman Pertama "Liburan" di Rumah Sakit

Tanggal 30 Juni 2013 kemarin, saya diharuskan untuk liburan di salah satu rumah sakit di Jakarta. Hal tersebut disebabkan oleh kelalaian saya dalam mengatur diri saya sendiri. Alasan tepatnya saya tidak publikasikan disini yaaa.

Ini adalah pengalaman pertama saya masuk rumah sakit. Rasanya seperti berada di tempat yang gak dikenal. Terbangun dengan infusan di tangan kiri dan nuansa "kesakitan" di sekeliling saya. Aneh dan takut juga menemani saya melewati saat-saat tersebut.

Beberapa teman yang mengantar dan menemani, saya larang memberi tahu siapapun. Orang tua saya pun tak saya kabari. Saya tak ingin membuat mereka cemas. Tidak lupa saya memberi tahu atasan saya di proyek dan rekan saya untuk mengambil alih posisi dan tanggung jawab. Selebihnya, saya tidak memberi tahu saya dirawat dimana. Saya sama sekali tidak ingin dijenguk. Maaf, saya tidak bermaksud arogan, saya hanya tidak mau menyusahkan orang lain lagi. Toh, saya kan masih dalam tahap observasi.

Diagnosa sementara dokter, menyatakan saya kemungkinan terinfeksi penyakit dalam. Ketika di test darah ada sesuatu yang abnormal, yakni karena sel darah putih saya 2x normal. Waduh?! Itulah alasan utama saya untuk menerima liburan paksa disini.

Bosan adalah satu kata yang menggambarkan apa yang saya rasakan ketika menulis postingan ini. Berada disini selama 2 hari membuat jenuh. Saya bergaya bagaikan orang sehat agar bisa rawat jalan, namun jauh dipikiran saya, saya tahu bahwa saya tidak sehat sepenuhnya. Saya merasakan ada sesuatu yang salah dalam diri saya. Meskipun ada juga sisi positif selama hal ini terjadi, yakni saya bisa bayar lunas semua waktu tidur yang terbuang. Namun, terlepas dari segalanya, saya berhutang sesuatu kepada teman - teman saya, baik itu waktu, uang maupun pekerjaan.

Akhir kata ketika saya menuliskan akhir paragraf ini, saya ingin mengucapkan terima kasih kepada semua yang telah mendoakan saya dan tentunya bersyukur kepada Tuhan yang Maha Kuasa sehingga saya masih diberi kekuatan untuk menghadapi kejadian ini. Semoga hasil diagnosa dan observasi dokter yang keluar hari ini dapat membebaskan saya dari "liburan" ini. Amin. :)

Part 2 - Click Here