Queen maharany: MODUL 6

module 6

MODULE 6

IMPLEMENTATION OF DATABASE TABLE (2)

(USE INSTRUCTIONS CREATE TABLE & HIS OPTIONS

AND ENTERING INTO TABLE RECORD-TABLE DATABASE)

A. Purpose

Students are able to make the structure of the database tables based on module design and implement four of the tables in the database.

B. Basic Theory

The database can be implemented by the ER diagram that has been made. Implementation of the database can be:

1. Manually (using SQL 'CREATE TABLE')

2. Generally semi-manually with the help of GUI-based client (MySQL Front, PgAccess, phpPgAdmin, etc..)

3. Automatically by CASE Tools (DBDesigner)

· Introduction to PostgreSQL

PostgreSQL is an object-relational database management system (ORDBMS), is open source, supports SQL92 and SQL99 standards and supports the programming languages C, C + +, Java, Tcl, Perl, Python, PHP, and so on.

The important feature of PostgreSQL: Inheritance, Data types, Functions, Constraints, Triggers, Rules, and Transactional Integrity.

Architecture-based PostgreSQL Client-Server. Software for database backend server (server-side) was Postmaster.

Frontend software (client-side):

· Psql (PostgreSQL is provided in the package)

· Client-based GUI (pgAdmin, PgAccess, ApplixWare)

· Create your own applications (C, C + +, Java, PHP, etc..)

Some URLs for PostgreSQL:

· www.postgresql.org

· Www.postgresql.org / docs

· techdocs.postgresql.org

Table Creation Phase

1. Makes all the main tables (which does not have FK).

2. Makes all the tables that relate directly (or have a relationship) with a table created in the previous step. Begin in the order of the table with the most number of FK ang ssedikit to most.

3. Repeat step 2 until all the tables are completed.
Implementasi Manual

Example to determine Primary Key (PK):

· CREATE TABLE dosen (nip INTEGER PRIMARY KEY, nama_dosen VARCHAR(45), alamat_dosen VARCHAR(255));

Example to determine Foreign Key (FK):

· CREATE TABLE mahasiswa (nim INTEGER PRIMARY KEY, nip INTEGER REFERENCES dosen(nip), nama_mhs VARCHAR(45), alamat_mhs VARCHAR(255));
Referential Integrity

1. Integrity of the database refers to the relationships between table via Foreign Key is concerned.

2. On the insert, the record should be included in the main table first, then the new second table.

3. On delete, the record should be removed in a second table first, then new in the main table.

4. By default, PostgreSQL will refuse to insert or delete that violate database integrity.

Insert Table

1. The SQL command to insert the data in the table for all columns:

INSERT INTO

VALUES (...);

2. To enter data in certain columns:

INSERT INTO (...)

VALUES (...);

Phase Insert Table

1. Stage insert table following the table creation stage

2. Stage 1: Perform the insert on all the most important table (which does not have FK).

3. Stage 2: Perform the insert on all the tables are directly related to the in-insert tables in the previous stage, in the order of the table with the least amount of FK to the most.

4. Stage 3: Repeat step 2 until all the insert is completed.

· Stage Delete Table

1. To maintain the integrity of the database, then the stage to perform the delete table is the opposite of a stage insert table.

2. By default, PostgerSQL will reject delete that violate database integrity. In other words, the record in the main table will not be deleted if there are records in the second table related to the primary record.

Create Advanced Table Options:

· Default

To specify a default value of a column if no data in column-inserts for it:

- CREATE TABLE mahasiswa (nim integer PRIMARY KEY, nama_mhs VARCHAR(45), fakultas VARCHAR(5) DEFAULT ‘FKI’:

Example insert:

- INSERT INTO mahasiswa (nim, nam_mhs) VALUES (1, ‘Ali Topan’);

· Not Null

To limit that must not be NULL column values:

- CREATE TABLE ruang (kode_ruang VARCHAR(20) PRIMARY KEY, lokasi_ruang VARCHAR(255) NOT NULL, kapasitas_ruang INTEGER NOT NULL);

If the specified column NOT NULL, then insert must enter a value for that column. DEFAULT can use that column values are added automatically.

Definition of NULL

Especially for string type (varchar or char), NULL is not the same as empty. If the column value is NULL, meaning that the value is unknown or no value at all. If nilaikolom is empty, meaning that the column value is unknown (no value), which is the value of a blank (empty string).

Example:

- CREATE TABLE test (kode INTEGER PRIMARY KEY, nama VARCHAR(20));

- INSERT INTO test (kode) VALUES (1);

- INSERT INTO test VALUES (2, ‘ ’);

Consider the following results:

- SELECT* FROM test WHERE nama IS NULL;

- SELECT* FROM test WHERE nama = ‘ ‘;

· Unique

To ensure that the unique column values:

- CREATE TABLE mata_kulaih (kode_mk INTEGER PRIMARY KEY, nama_mk VARCHAR(45) UNIQUE);

For multikolom unique:

- CREATE TABLE dosen (nip INTEGER PRIMARY KEY, nama_dosen VARCHAR(45), alamat_dosen VARCHAR(255), UNIQUE (nama_dosen, alamat_dosen));

· Check

To limit the value of the column, for example:

- CREATE TABLE produk (kode_produk INTEGER PRIMARY KEY, nama_produk VARCHAR (45), harga INTEGER, CHECK (harga <= 100000 AND kode_produk> 100));

Check on top of that price should be the maximum limit of Rp 100000, and kode_produk should be above 100.

Determination of Referential Integrity

example:

- CREATE TABLE pemasok (kode_pemasok INTEGER PRIMARY KEY, nama_pemasok VARCHAR(45), kode_produk INTEGER REFERENCES produk ON DELETE CASCADE ON UPDATE CASCADE);

For the example above, if there is an update or delete in the main table, the second table is automatically adjusted.

Kinds of action:

o NO ACTION or restrict: do not update or delete. This is the default option.

o CASCADE: the value of the second column in the table adjusted to the value of the column in the main table.

o SET NULL: the value of a column in the second table made NULL.

o SET DEFAULT: the value of a column in the second table to be used as the value DEFAULT (DEFAULT value must be determined at the time of table creation).

· Autoincrement

For autoincrement feature, use the "serial":

- CREATE TABLE nasabah (id_nasabah SERIAL PRIMARY KEY, nama_nasabah VARCHAR(45));

For the example above, id_nasabah do not need to insert, because the database will automatically add them in sequence. We quite simply enter nama_nasabah only:

- INSERT INTO nasabah (nama_nasabah) VALUES (‘Ali Topan’);

Serial only be from 1 to 232. If not enough, you can use bigserial from 1 to 264. Elimination record will not affect the order of the series and bigserial. Value for a column that uses a serial / bigserial will increase by 1, will never again retreat. For example:

- DELETE FROM nasabah WHERE id_nasabah=1;

- INSERT INTO nasabah (nama_nasabah) VALUES (‘Ali Topan’);

- Perhatikan id_nasabah: SELECT* FROM nasabah;

C. Equipment and Materials

1. Computer with Windows 7 operating system.

2. Program application PostgreSQL 9.3

3. Modules Practical Database Systems.

D. Step Work

1. Run pgAdmin III to PostgreSQL 9.3, the way by going to Start> All Programs> PostgreSQL 9.3> pgAdmin III.

2. In the object browser tab (to the left), double-click on PostgreSQL 9.3 (localhost: 5432), then right-click on Databases> New Database, give it a name in the name field, because the bank wants to create a database, then give the name of the bank, eg "Perbankan" , then click OK

3. On the properties tab, click on Perbankan, then select the icon jigsaw yellow and green) and select psql console.

4. Creating nasabah table, cabang_bank, rekening, transaksi, and nasabah_has_rekening the create table command.

5. Checking the results of creating a table by using the command \ dt.

6. Inserting rows into a table that has been created with insert into command.

7. Judging from records that have been entered with the command select * from.

a. Table nasabah:

b. Table cabang_bank:

c. Table rekening:

e. Table nasabah_has_rekening:

d. Table transaksi:

E. Task

Implement a database design results in module 4 tudas into pgAdmin III program. Insert some records into each table in the database that you have created. Print out the results of the implementation of the design and analysis of the results.

Implementation steps:

University Database:

1. PgAdmin III Running on PostgreSQL 9.3, the way by going to Start> All Programs> PostgreSQL 9.3> pgAdmin III.

2. On the object browser tab (to the left), double-click on PostgreSQL 9.3 (localhost: 5432), then right-click on Databases> New Database, give it a name in the name field, because the university wanted to create a database, then give it a name eg "Universitas" , then click OK.

3. On the properties tab, click on Universitas, then select the icon jigsaw yellow and green) and select psql console.

4. Creating a table of mahasiswa, dosen, mata_kuliah, ruang_kelas, and mhs_has_mk using the create table command.

5. Checking the results of creating a table by using the command \ dt.

6. Inserting rows into a table that has been created with insert into command.

7. Judging from records that have been entered with the command select * from.

a. Table mahasiswa:

b. Table dosen:

c. Table mata_kuliah:

d. Table ruang_kelas:

e. Table mhs_has_mk:

Mirai database:

1. PgAdmin III Running on PostgreSQL 9.3, the way by going to Start> All Programs> PostgreSQL 9.3> pgAdmin III.

2. Pada tab object browser (sebelah kiri), double klik pada PostgreSQL 9.3 (localhost:5432), kemudian klik kanan pada Databases > New Database, beri nama pada kolom nama, karena ingin membuat database tempat kursus Mirai, maka beri nama “Mirai”, kemudian klik OK.