MODUL 5

MODULE 5 

IMPLEMENTATION OF DATABASE TABLE-TABLE (1) 

(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 3 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 (with the SQL command 'CREATE TABLE')
2. In the semi-manually with the help of GUI-based client (MySQL Front, PgAccess, phpPgAdmin, etc..)
3. Automatically with 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

Preparation Phase Table

1 . Making the most of all the main table ( which does not have FK ) .
2 . Make 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 FK to the ang ssedikit most.
3 . Repeat step 2 until all the tables are completed.
· Implementation Manual
Example to determine a primary key ( PK ) :
· CREATE TABLE faculty ( nip INTEGER PRIMARY KEY , nama_dosen VARCHAR ( 45 ) , alamat_dosen VARCHAR ( 255 ) ) ;
Example to specify Foreign Key ( FK ) :
· CREATE TABLE student ( nim INTEGER PRIMARY KEY , INTEGER REFERENCES lecturer nip ( nip ) , nama_mhs VARCHAR ( 45 ) , alamat_mhs VARCHAR ( 255 ) ) ;
· Referential Integrity
1 . Integrity refers to the relationship between the database tables via the Foreign Key is concerned .
2 . In the insert , the record should be included in the main table first , then new in the second table .
3 . On delete , the record should be removed in a second table first , then a new main table .
4 . By default , PostgreSQL will refuse to insert or delete that violate database integrity .
· Insert Table
1 . 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. Phase 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. Phase 2: Perform inserts on all tables 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. Step 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 if the column does not exist in the data for kolo-insert it:
- CREATE TABLE student (nim integer PRIMARY KEY, nama_mhs VARCHAR (45), faculty VARCHAR (5) DEFAULT 'IAF':
Example insert:
- INSERT INTO student (nim, nam_mhs) VALUES (1, 'Ali Typhoon');
· Not Null
To limit that must not be NULL column values​​:
- CREATE TABLE room (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. Can use DEFAULT column values ​​that 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 (code INTEGER PRIMARY KEY, name VARCHAR (20));
- INSERT INTO test (code) VALUES (1);
- INSERT INTO test VALUES (2​​, '');
Consider the following results:
- SELECT * FROM test WHERE name IS NULL;
- SELECT * FROM test WHERE name = '';

 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 faculty (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 products (kode_produk INTEGER PRIMARY KEY, nama_produk VARCHAR (45), price INTEGER, CHECK (price <= 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 supplier ( kode_pemasok INTEGER PRIMARY KEY , nama_pemasok VARCHAR ( 45 ) , kode_produk product INTEGER REFERENCES 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 a 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 customer ( 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 nam_nasabah only :
- INSERT INTO customer ( nama_nasabah ) VALUES ( ' Ali Typhoon ' ) ;
Serial only be from 1 to 232 . If not enough , you can use bigserial from 1 to 264 . Record deletion will not affect the order of the serial and bigserial . Value for a column that uses a serial / bigserial will increase by 1 , will never again retreat . For example :
- DELETE FROM customers WHERE id_nasabah = 1 ;
- INSERT INTO customer ( nama_nasabah ) VALUES ( ' Ali Typhoon ' ) ;
- Note id_nasabah : SELECT * FROM customer;
C. Equipment and Materials
1. The computer with the Windows 7 operating system.
2. PostgreSQL 9.3 application program
3. Practical Module System Files and Databases.
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 (1)> 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 "mandiri", and then click OK.

3. On the properties tab, click on mandiri, then select the icon jigsaw yellow and green) and select psql console.
4.      Create table nasabah, cabang_bank, rekening, dan transaksi:

5.      Create  table nasabah_has_rekening:

 6. To check the results of creating a table using the command \ dt:

7. Subsequently, insert records into a table that has been created with insert into command.
In the nasabah table:

In the cabang_bank table:

In the rekening table;

In the nasabah_has_rekening table:

 In the transaksi table:

8. Seeing the results on each table.
In the nasabah table:

 In the cabang_bank table:

 In the rekening table:

In the nasabah_has_rekening table:

 In the transaksi table:

E. Analysis
From the experiments above, I can conclude that making use PostgreSQL 9.3 database with psql console, entirely text-based. So to create, insert, and delete data using certain commands that have been standardized by the SQL. In this experiment, it takes a good ability to memorize, but in addition we also have to understand memorize each command typed.
F. Duties
Implement the results of a database design that handles the data lectures on assignment module 3 into pgAdmin III program. Insert some records into each table in the database that has been created. Print out the results implemenatasi the design and analysis of the results.
The steps to create the database course:

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 (1)> 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 "UMS", and then click OK.

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

4.      Create table mahasiswa, dosen, mata_kuliah, ruang_kelas:

Create tabel mahasiswa_has_mata_kuliah:

5. To check the results of creating a table using the command \ dt:

6. Subsequently, insert records into a table that has been created with insert into command.
In the mahasiswa table:

 In the dosen table:

 In the mata_kuliah table:

 In the ruang_kelas table:

 In the mahasiswa_has_mata_kuliah table:

7. Seeing the results on each table.
In the mahasiswa table:

 In the dosen table:

 In the mata_kuliah table:

 In the ruang_kelas table:

 In the mahasiswa_has_mata_kuliah table :

From the implementation of the database data for each relation lecture from one to many (1: n) will be the primary key attribute of the table (with relation one) is added to the table with a lot of relationships. For example, for the relation of lecturers to mata_kuliah (1: n), the table will be added mata_kuliah primary key of the lecturer, ie nik.

Then to the relation of many to many (m: n), we have to create a new table that links the two tables that relate to each other earlier. For example, of the relation of students to mata_kuliah (m: n), created a new table with the name mahasiswa_has_mata_kuliah attribute containing the primary key of both tables (students and mata_kuliah) is nim and kode_mk.