Praktikum 6 bab6

Praktikum 6 Basis Data

TIPE DATA

Praktikum Sistem Basis Data I PostgreSQL 8.X Pertemuan 6 Oleh: Saepudin Nirwan, S.Kom., M.Kom. Purpose of Data Types It is tempting to think that databases would be easier to use if only one data type existed -- a type that could hold any type of information, such as numbers, character strings, or dates. Although a single data type would certainly make table creation simpler, having different data types offers definite advantages: Consistent Results Columns of a uniform type produce consistent results. Displaying, sorting, aggregates, and joins deliver consistent results. No conict arises over how different types are compared or displayed. For example, selecting from an INTEGER column always yields INTEGER values. Data Validation Columns of a uniform type accept only properly formated data; invalid data are rejected. For example, a column of type INTEGER will reject a DATE value. Compact Storage Columns of a uniform type are stored more compactly. Performance Columns of a uniform type are processed more quickly.

POSTGRESQL data types Installed Types

Character String

Character string types are the most commonly used data types. They can hold any sequence of letters, digits, punctuation, and other valid characters. Typical character strings are names, descriptions, and mailing addresses. You can store any value in a character string. Nevertheless, this type should be used only when other data types are inappropriate, as other types provide better data validation, more compact storage, and better performance. Three character string data types exist: TEXT, ARCHAR(length), and CHAR(length). TEXT does not limit the number of characters stored. VARCHAR(length) limits the length of the eld to length characters. Both TEXT and VARCHAR() store only the number of characters in the string. CHAR(length) is similar to VARCHAR(), except it always stores exactly length characters. This type pads the value with trailing spaces to achieve the specied length, and provides slightly faster access than TEXT or VARCHAR().

Number

Number types allow the storage of numbers. The number types are INTEGER, INT2, INT8, OID, NUMERIC(), FLOAT, and FLOAT4. INTEGER, INT2, and INT8 store whole numbers of various ranges. Larger ranges require more storage. For example, INT8 requires twice the storage of INTEGER and is slower that INTEGER. OID is used to store POSTGRESQL object identiers. Although you could use INTEGER for this purpose, OID better documents the meaning of the value stored in the column. NUMERIC(precision, decimal) allows user-dened digits of precision, rounded to decimal places. This type is slower than the other number types.

 Geometric Type: Temporal

Temporal types allow storage of date, time, and time interval information. Although these data can be stored in character strings, it is better to use temporal types. The four temporal types are DATE, TIME, TIMESTAMP, and INTERVAL. DATE allows storage of a single date consisting of a year, month, and day. The format used to input and display dates is controlled by the DATESTYLE setting TIME allows storage of an hour, minute, and second, separated by colons. TIMESTAMP stores both the date and the time.for example, 2000-7-12 17:34:29. INTERVAL represents an interval of time, like 5 hours or 7 days. INTERVAL values are often generated by subtracting two TIMESTAMP values to nd the elapsed time. For example, 1996.12.15 19:00:40 minus 1996.12.8 14:00:10 results in an INTERVAL value of 7 05:00:30, which is 7 days, 5 hours, and 30 seconds. Temporal types can also handle time zone designations. Logical The only logical type is BOOLEAN. A BOOLEAN eld can store only true or false, and of course NULL. You can input true as true, t, yes, y, or 1. False can be input as false, f, no, n, or 0. Although true and false can be input in a variety of ways, true is always output as t and false as f.

Geometric

The geometric types support storage of geometric primitives. They include POINT, LSEG, PATH, BOX, CIRCLE, and POLYGON. Network The network types are INET, CIDR, and MACADDR. INET allows storage of an IP address, with or without a netmask. A typical INET value with a netmask is 172.20.90.150 255.255.255.0. CIDR stores IP network addresses. It allows a subnet mask to specify the size of the network segment. A typical CIDR value is 172.20.90.150/24. MACADDR stores MAC (Media Access Control) addresses, which are assigned to Ethernet network cards at the time of their manufacture. A typical MACADDR value is 0:50:4:1d:f6:db. Internal A variety of types are used internally. Psql's \dT command shows all data types.

Type Conversion Using CAST

In most cases, values of one type are converted to another type automatically. In those rare circumstances where you need to explicitly convert one type to another, you can use CAST to perform the conversion. To convert val to an INTEGER, use CAST(val AS INTEGER). To convert a column date_col of type DATE to type TEXT, use CAST(date_col AS TEXT). You can also perform type casting using double colons.that is, date_col::text or num_val::numeric(10,2).

Support Functions

 Functions enable you to access specialized routines from SQL. They take one or more arguments and return a result. Suppose you want to uppercase a value or column. No command will perform this operation, but a function can handle it. POSTGRESQL has a function called upper that takes a single string argument and returns the argument in uppercase. The function call upper(col) calls the function upper with col as its argument and returns it in uppercase.

Support Operators Operators

are similar to functions Psql's \do command shows all dened operators and their arguments. Example of a function call

Membuat  tabel functest

create  table functest (name varchar (20), date_part date);

Memasukan data  ke dalam tabel functest

Insert into functest values (‘judy’,’02/05/2011’);

Menampilkan semua data yang ada di tabel functest

select * from functest;

Read more »

Posted in: Tugas Basdat

Praktikum 5

BAB 5

Example of Aggregates

Aggregates can be combined with a WHERE clause to produce more complex results. For example, the query SELECT AVG(age) FROM friend WHERE age >= 21 computes the average age of people age 21 or older. This prevents Dick Gleason from being included in the average computation because he is younger than 21. NULL values are not processed by most aggregates, such as MAX(), SUM(), and AVG(); they are simply ignored. However, if a column contains only NULL values, the result is NULL, not zero. COUNT(*) is different in this respect.

Aggregate	Function
COUNT(*) SUM(colname) MAX(colname) MIN(colname) AVG(colname)	Count of rows total maximum minimum average

Contoh Aggregates

Read more »

Posted in: Tugas Basdat

CDM PDM perpustakaan

                                                                               CDM perpustakaan

 

PDM perpustakaan

Read more »

Posted in: Tugas Basdat

Penjelasan PostgreSQL

PostgreSQL adalah sebuah perangkat lunak bebas object-relational database server ( database sistem manajemen ), dirilis di bawah fleksibel lisensi BSD-style . It offers an alternative to other open-source database systems (such as MySQL and Firebird ), as well as to proprietary systems such as Oracle , Sybase , IBM's DB2 and Microsoft SQL Server . Ia menawarkan alternatif sumber database terbuka sistem lain (seperti MySQL dan Firebird ), serta kepemilikan sistem seperti Oracle , Sybase , IBM DB2 dan Microsoft SQL Server .
PostgreSQL's unusual-looking name gives some readers pause in trying to pronounce it, especially those who pronounce "SQL" as "sequel". PostgreSQL nama tidak biasa tampak memberikan beberapa pembaca jeda dalam mencoba mengucapkannya, terutama mereka yang mengucapkan "SQL" sebagai "sekuel". PostgreSQL's developers pronounce it "post-gress-QL". pengembang PostgreSQL's mengucapkannya "pasca-gress-QL". ( Audio sample ( http://postgresql.rmplc.co.uk/postgresql.mp3 ) , 5.6k MP3). ( Audio sampel (http://postgresql.rmplc.co.uk/postgresql.mp3), MP3 5.6k).

PostgreSQL telah memiliki evolusi panjang, dimulai dengan Ingres proyek di UC Berkeley . The project lead, Michael Stonebraker had left Berkeley to commercialize Ingres in 1982 , but eventually returned to academia. Memimpin proyek, Michael Stonebraker telah meninggalkan Berkeley untuk mengusahakan Ingres di 1982 , tetapi akhirnya kembali ke akademisi. After returning to Berkeley in 1985 , Stonebraker started a post-Ingres project to address the problems with contemporary database systems that had become increasingly clear during the early 1980s. Setelah kembali ke Berkeley pada 1985 , Stonebraker memulai proyek-Ingres posting untuk mengatasi masalah dengan sistem database kontemporer yang telah menjadi semakin jelas pada awal 1980-an. The code bases of Postgres and Ingres started (and remain) completely separated. Kode basis dari Postgres dan Ingres mulai (dan tetap) benar-benar terpisah.
The resulting project, named Postgres , aimed at introducing the minimum number of features needed to add complete support for types. Proyek yang dihasilkan, bernama Postgres, yang ditujukan untuk memperkenalkan fitur jumlah minimum yang dibutuhkan untuk menambahkan dukungan lengkap untuk tipe. These features included the ability to define types, but also the ability to fully describe relationships – up until this time widely used but maintained entirely by the user. Fitur-fitur ini termasuk kemampuan untuk menentukan jenis, tetapi juga kemampuan untuk sepenuhnya menggambarkan hubungan - sampai saat ini banyak digunakan tetapi dikelola sepenuhnya oleh pengguna. In Postgres the database "understood" relationships, and could retrieve information in related tables in a natural way using rules . Dalam Postgres database "memahami" hubungan, dan bisa mengambil informasi dalam tabel terkait dengan cara alami menggunakan aturan.
Starting in 1986 the team released a number of papers describing the basis of the system, and by 1988 the project had a prototype version up and running. Dimulai pada 1986 tim ini merilis sejumlah makalah yang menjelaskan dasar sistem, dan dengan 1988 proyek ini memiliki versi prototipe dan berjalan. The team released version 1 to a small number of users in June 1989 , followed by Version 2 with a re-written rules system in June 1990 . 1991 's Version 3 re-wrote the rules system again, but also added support for multiple storage managers and for an improved query engine. Tim merilis versi 1 ke sejumlah kecil pengguna pada bulan Juni 1989 , diikuti oleh Versi 2 dengan sistem peraturan tertulis yang kembali pada bulan Juni 1990 . 1991 's Versi 3 menulis ulang sistem aturan lagi, tapi juga menambahkan dukungan untuk penyimpanan beberapa manajer dan untuk mesin query diperbaiki. By 1993 a huge number of users existed and began to overwhelm the project with requests for support and features. Dengan 1993 sejumlah besar pengguna ada dan mulai kewalahan proyek dengan permintaan dukungan dan fitur. After releasing a Version 4 -- primarily as a cleanup -- the project ended. Setelah merilis 4 Versi - terutama sebagai suatu pembersihan - proyek berakhir.
Although the Postgres project had officially ended, the BSD license (under which Berkeley had released Postgres) enabled Open Source developers to obtain copies and to develop the system further. Meskipun proyek Postgres telah resmi berakhir, lisensi BSD (di mana Berkeley telah merilis Postgres) yang aktif Open Source pengembang untuk memperoleh salinan dan untuk mengembangkan sistem lebih lanjut. In 1994 two UC Berkeley graduate students, Andrew Yu and Jolly Chen, added a SQL language interpreter to replace the earlier Ingres-based QUEL system, creating Postgres95. Pada tahun 1994 dua UC Berkeley mahasiswa pascasarjana, Andrew Yu dan Jolly Chen, menambahkan SQL juru bahasa untuk menggantikan berdasarkan sebelumnya Ingres- QUEL sistem, menciptakan Postgres95. The code was subsequently released to the web to find its own way in the world. 1996 saw a re-naming of the project: in order to reflect the database's new SQL query language, Postgres95 became PostgreSQL . Kode ini kemudian dialihkan ke web untuk menemukan jalan sendiri di dunia. 1996 melihat kembali penamaan proyek: dalam rangka merefleksikan baru SQL database bahasa query, Postgres95 menjadi PostgreSQL.
The first PostgreSQL release formed version 6.0. Rilis pertama kali dibentuk PostgreSQL versi 6.0. Subsequently a group of database developers and volunteers from around the world, coordinated via the Internet , have maintained the software. Selanjutnya sekelompok pengembang database dan sukarelawan dari seluruh dunia, terkoordinasi melalui Internet , telah mempertahankan perangkat lunak. Since version 6.0, many subsequent releases have appeared, and many improvements have occurred in the system; on January 19, 2005 version 8.0 became the current release. Sejak versi 6.0, rilis berikutnya banyak muncul, dan banyak perbaikan terjadi dalam sistem; pada 19 Januari 2005 versi 8.0 menjadi rilis saat ini.
Although the license allowed for the commercialization of Postgres, the Postgres code did not develop commercially with the same rapidity as Ingres -- somewhat surprisingly considering the advantages Postgres offered. Meskipun lisensi diperbolehkan untuk komersialisasi Postgres, kode Postgres tidak mengembangkan secara komersial dengan kecepatan yang sama seperti Ingres - agak mengherankan mengingat Postgres keuntungan yang ditawarkan. The main offshoot originated when Paula Hawthorn (an original Ingres team member who moved from Ingres) and Michael Stonebraker formed Illustra Information Technologies to commercialize Postgres. Para cabang utama berasal ketika Paula Hawthorn (sebuah Ingres asli anggota tim yang pindah dari Ingres) dan Michael Stonebraker membentuk ilustrasi Teknologi Informasi mengkomersialkan Postgres.

Description Deskripsi

A cursory examination of PostgreSQL might suggest that the system resembles other database systems. Pemeriksaan sepintas PostgreSQL mungkin mengatakan bahwa sistem menyerupai sistem database lainnya. PostgreSQL uses the SQL language to run queries on data. PostgreSQL menggunakan bahasa SQL untuk menjalankan query pada data. That data exists as a series of tables with foreign keys linking related data together. Data tersebut ada sebagai rangkaian tabel dengan kunci asing menghubungkan data yang terkait bersama-sama. One might characterise the primary advantage of PostgreSQL over some of its competitors as programmability : PostgreSQL makes it much easier to build real-world applications using data taken from the database. Orang mungkin mencirikan keunggulan utama dari PostgreSQL selama beberapa pesaing sebagai programabilitas: PostgreSQL membuat lebih mudah untuk membangun aplikasi dunia nyata menggunakan data yang diambil dari database.
The SQL data stores simple data types in "flat tables", requiring the user to gather together related information using queries. Menyimpan data SQL tipe data sederhana dalam "meja datar", mengharuskan pengguna untuk mengumpulkan informasi terkait menggunakan query. This contrasts with the way applications and users utilise the data itself: typically in a high-level language with rich data types where all of the related data operates as a complete unit of its own, typically referred to as a record or object (depending on the language). Hal ini kontras dengan cara aplikasi dan pengguna memanfaatkan data itu sendiri: biasanya dalam Bahasa tingkat tinggi dengan tipe data yang kaya di mana semua data terkait beroperasi sebagai unit lengkap sendiri, biasanya disebut sebagai catatan atau objek (tergantung pada bahasa).
Converting information from the SQL world into the object-oriented programming world presents difficulties because the two have very different models of data organization. Mengubah informasi dari dunia SQL ke dunia pemrograman berorientasi objek menyajikan kesulitan karena kedua memiliki model yang sangat berbeda dari organisasi data. The industry knows this problem as impedance mismatch : mapping from one model to the other may take up to 40% of a project's development time. Industri tahu masalah ini sebagai ketidakcocokan impedansi : pemetaan dari satu model ke model yang lain mungkin diperlukan waktu hingga 40% dari pembangunan di waktu proyek. A number of mapping solutions, typically referred to as object-relational mapping , address the issue, but they tend to cost a lot and have problems of their own, causing poor performance or forcing all data access to take place through the one language that the mapping supports. Sejumlah solusi pemetaan, biasanya disebut sebagai objek-relasional pemetaan , alamat masalah ini, namun mereka cenderung biaya banyak dan memiliki masalah sendiri, menyebabkan kinerja yang buruk atau memaksa semua akses data dilakukan melalui bahasa satu bahwa pemetaan mendukung.
PostgreSQL can solve many of these issues directly in the database. PostgreSQL dapat memecahkan banyak masalah ini secara langsung dalam database. PostgreSQL allows the user to define new types based on the normal SQL types, allowing the database itself to understand complex data. PostgreSQL memungkinkan pengguna untuk menentukan jenis baru berdasarkan jenis SQL normal, yang memungkinkan database itu sendiri untuk memahami data yang kompleks. For instance, you can define an address to consist of several strings for things like street number, city and country. Misalnya, Anda dapat menentukan address terdiri dari beberapa string untuk hal-hal seperti jalan kota nomor, dan negara. From that point on one can easily create tables containing all the fields needed to hold an address with a single line. Sejak saat itu, kita dengan mudah dapat membuat tabel yang berisi semua bidang yang diperlukan untuk menampung alamat dengan garis tunggal.
PostgreSQL also allows types to include inheritance, one of the major concepts in object-oriented programming . PostgreSQL juga memungkinkan untuk memasukkan jenis warisan, salah satu konsep utama dalam pemrograman berorientasi obyek . For instance, one could define a post_code type, and then create us_zip_code and canadian_postal_code based on it. Misalnya, orang bisa menentukan post_code jenis, dan kemudian membuat us_zip_code dan canadian_postal_code didasarkan pada itu. Addresses in the database could then take either us_address or canadian_address form, and specialized rules could validate the data in each case. Alamat dalam database kemudian dapat mengambil baik us_address atau canadian_address bentuk, dan aturan khusus dapat memvalidasi data dalam setiap kasus. In early versions of PostgreSQL, implementing new types required writing C extensions and compiling them into the database server; in version 7.4, it has become much easier to create and use custom types via CREATE DOMAIN . Pada versi awal PostgreSQL, menerapkan jenis baru yang dibutuhkan menulis extension C dan kompilasi mereka ke dalam server database, dalam versi 7.4, telah menjadi jauh lebih mudah untuk membuat dan menggunakan jenis kustom melalui CREATE DOMAIN .
The programming of the database itself can profit greatly from the use of functions . Pemrograman dari database itu sendiri dapat keuntungan besar dari penggunaan fungsi. Most SQL systems allow users to write a stored procedure , a block of SQL code that other SQL statements can call. Kebanyakan SQL sistem memungkinkan pengguna untuk menulis prosedur yang tersimpan, sebuah blok kode SQL yang lain pernyataan SQL dapat memanggil. However SQL itself remains unsuitable as a programming language, and SQL users can experience great difficulty in constructing complex logic. Namun SQL itu sendiri masih tidak cocok sebagai bahasa pemrograman, dan SQL pengguna dapat mengalami kesulitan besar dalam membangun logika kompleks. Worse, SQL itself does not support many of the most basic operations in a programming language, like branching and looping . Lebih buruk lagi, SQL itu sendiri tidak mendukung banyak operasi yang paling dasar dalam bahasa pemrograman, seperti percabangan dan perulangan . Instead each vendor has written their own extensions to the SQL language to add these features, and such extensions do not necessarily operate across database platforms . Sebaliknya setiap vendor telah menulis ekstensi sendiri untuk bahasa SQL untuk menambahkan fitur ini, dan ekstensi tersebut tidak perlu beroperasi di database platform .
In PostgreSQL programmers can write such logic in any one of a considerable set of supported languages. Dalam PostgreSQL programmer dapat menulis logika tersebut dalam salah satu dari satu set besar bahasa yang didukung.

• A built-in language called PL/PgSQL resembles Oracle's procedural language PL/SQL , and offers particular advantages when dealing with query-intensive procedures. Built-dalam bahasa yang disebut PL / pgsql menyerupai's prosedural bahasa Oracle PL / SQL , dan menawarkan keuntungan tertentu ketika berhadapan dengan-intensif prosedur query.
• Wrappers for popular scripting languages such as Perl , Python , Tcl , and Ruby allow harnessing their strengths in string processing and in linking to extensive libraries of outside functions. Wrapper untuk bahasa scripting populer seperti Perl , Python , Tcl , dan Ruby memungkinkan memanfaatkan kekuatan mereka dalam pengolahan string dan menghubungkan ke perpustakaan pilihan fungsi luar.
• Procedures requiring the high performance provided by compiling complex logic into machine code can utilise C or C++ . Prosedur yang membutuhkan kinerja tinggi yang diberikan dengan menyusun logika yang kompleks ke dalam kode mesin dapat menggunakan C atau C + + .
• On the more esoteric side, a handler for the R statistical language allows database queries to take advantage of its rich set of statistical functions. Pada lebih esoteris sisi, penangan untuk R bahasa statistik memungkinkan query database untuk mengambil keuntungan dari yang kaya fungsi statistik.

The programmer can insert the code into the server as a function , a small wrapper that makes the code resemble a stored procedure. Programmer dapat menyisipkan kode ke server sebagai fungsi, bungkus kecil yang membuat kode menyerupai prosedur yang tersimpan. In this way SQL code can call (for instance) C code and vice-versa. Dengan cara ini kode SQL dapat menelpon (misalnya) kode C dan sebaliknya.

• Performance improves because the database engine invokes all the logic at one time in one place, reducing the number of round trips between client and server. Meningkatkan kinerja karena mesin database memanggil semua logika pada satu waktu di satu tempat, mengurangi jumlah perjalanan pulang pergi antara klien dan server.
• Reliability improves due to the centralisation of data verification code in one place, on the server, without relying on synchronising logic in multiple client applications, perhaps even in multiple programming languages. Meningkatkan keandalan karena sentralisasi kode verifikasi data di satu tempat, di server, tanpa bergantung pada logika dalam aplikasi sinkronisasi beberapa klien, bahkan mungkin dalam beberapa bahasa pemrograman.
• By adding useful abstractions to the server, client code can become much shorter and simpler. Dengan menambahkan abstraksi berguna untuk server, kode klien dapat menjadi jauh lebih singkat dan sederhana.

These advantages add up to making PostgreSQL arguably the most advanced database system from a programming perspective, which helps to explain the success of Illustra. Keuntungan ini menambahkan sampai membuat PostgreSQL arguably sistem database yang paling canggih dari sudut pandang pemrograman, yang membantu untuk menjelaskan keberhasilan ilustrasi. Using PostgreSQL can dramatically reduce overall programming time on many projects, with its advantages growing with project complexity. Menggunakan PostgreSQL dapat secara dramatis mengurangi waktu pemrograman keseluruhan pada banyak proyek, dengan kelebihan tumbuh dengan kompleksitas proyek.

Selengkapnya Klik Disini

Read more »

Posted in: Tugas Basdat