First Duty

Assalamualaikum Wr. Wb

I Hope Allah always Bless Us.. before i write my first duty about “ Praktikum Desain Basis Data Subject “. I will Introduce my self. My name is M Medina Almas Ali, I dont Have Basic in Databases programming. But start from this. I hope i will have more spirite to write and learn database.. oke i think enaugh..
Dear Mr. Aziz and the Assist is Bro Charis Fauzan..i hope u always find to teach me Basic Data Design. amiin.

First of All.. Before we start the basic... we need the software Xampp & PostgressSql

Xampp.. here

Mysql..here

  ~ 1^st Duty ~

*~ Background~*

CREATE USER

The -Uoption is used to specify the identity you want to use for creating the new user. It must be a PostgreSQL user with permission to createusers, normally the PostgreSQL user you named when you performed the installation. The -Poption causes createuserto prompt for a password for the new user. Here, we have allowed neilto create new databases, and he is allowed to create new users. Some of the examples in the book use another user, rick, who also has permission to create databases, but does not have permission to create new users. If you would like to exactly replicate these examples, now is a good time to create this user. Once you have created a PostgreSQL user with these rights, you will beable to create the bpsimpledatabase.[1] Neil Matthew, and Richar Stones, Begining Database with PostgreSql page 65

Now let's see how to create a new database and how to remove an existing one. The syntax for the CREATE DATABASE command is

CREATE DATABASE database-name

[ WITH [ OWNER    [=] {username|DEFAULT}]

[ TEMPLATE [=] {template-name|DEFAULT}]

[ ENCODING [=] {encoding|DEFAULT}]

[ TABLESPACE [=] tablespace ]]

As I mentioned earlier, the database-name must follow the PostgreSQL naming rules described earlier and must be unique within the cluster.If you don't include the OWNER=username clause or you specify OWNER=DEFAULT, you become the owner of the database. If you are a PostgreSQL superuser, you can create a database that will be owned by another user using the OWNER=username clause. If you are not a PostgreSQL superuser, you can still create a database if you have the CREATEDB privilege, but you cannot assign ownership to another user.  [2]Korry Douglas, Susan, PostgreSQL, Second Edition.

RDBMSs offer ways to add and update data, of course, but their real power stems from their ability to allow users to ask questions about the data stored, in the form of queries. Unlike many earlier database designs, which were often structured around the type of question that the data  needed to answer, relational databases are much more flexible at answering questions that were not known at the time the database was designed. Codd’s proposals for the relational model use the fact that relations define sets, and sets  can be manipulated mathematically. He suggested that queries might use a branch of theoretical  logic called the predicate calculus, and that query languages would use thisas their base. This  would bring unprecedented power for searching and selecting data sets. Modern database systems, including PostgreSQL, hide all the mathematics behind an expressive and easy-tolearn query language.[3] Neil Matthew, and Richar Stones, Begining Database with PostgreSql page 8

To begin, we will look at more of the operators that PostgreSQL supports within SELECT statements, including advanced matching and mathematical operators that allow us to construct  sophisticated tests in WHEREclauses. Next, we will see how operators are implemented as functions in PostgreSQL and look at a few additional functions that add to the expressive power of our SELECT statements.[4] Neil Matthew, and Richar Stones, Begining Database with PostgreSql page 268

timestamp (datetime) Stores dates and times from 4713 BC to 1465001 AD, with a resolution of 1 microsecond. You may also see timestamptzused sometimes in PostgreSQL, which is a shorthand for timestamp with time zone. interval (interval) Stores an interval of approximately +/– 178,000,000 years, with a resolution of 1 microsecond. date (date) Stores dates from 4713 BC to 32767 AD, with a resolution of 1 day.time (time) Stores a time of day, from 0 to23:59:59.99, with a resolution of 1 microsecond.

Berikut ini beberapa cara pengontrolan, misalkan jika ingin mengetahui waktu beserta tanggal pada hari ini, gunakan perintah select current_timestamp;tanggal beserta jam akan ditampilkan secara bersamaan.

 db_personal=> select current_timestamp;

 timestamp  --------------------------------

 2005-12-16 14:07:16.099+07

 (1 row)

Berikut query untuk menampilkan tanggal.

 db_personal=> select current_date;

 date  ------------

2005-12-16

(1 row)

Query untuk menampilkan waktu.

 db_personal=> select current_time;

 time  ----------

14:07:18

(1 row) [5]. Yuliardi, panduan administrasi database postgremysql versi 1.4 page 27

*~Result and Homework~*

1.      How to Create User + Password [ SQL Shell (psql) and cmd (MySql);

a.       Create User with psql;

b.      Create User with mysql;

2.      How to Create Database [ SQL Shell (psql) and cmd (MySql);

a.       Create Database with psql;

b.      Create Database with mysql;

3.      Aritmathic [ SQL Shell (psql) and cmd (MySql);

a.       Aritmathic with psql;

b.      Aritmathic with mysql;

4.       Date Manipulation [ SQL Shell (psql) and cmd (MySql);

a.       Date Manipulation with psql;

a.1. Check Time;

a.2. Count Distance;

b.      Date Manipulation with mysql;

b.1. Check Time;

b.2. Count Distance;

1.      Entity Relationship Diagram (ERD)

Definition: An entity-relationship (ER) diagram is a specialized graphic that illustrates the relationships between entities in a database. ER diagrams often use symbols to represent three different types of information. Boxes are commonly used to represent entities. Diamonds are normally used to represent relationships and ovals are used to represent attributes.

Examples: Consider the example of a database that contains information on the residents of a city. The ER digram shown in the image above contains two entities -- people and cities. There is a single "Lives In" relationship. Each person lives in only one city, but each city can house many people.[7] http://databases.about.com/cs/specificproducts/g/er.htm

1. One-to-One

One instance of an entity (A) is associated with one other instance of another entity (B). For example, in a database of employees, each employee name (A) is associated with only one social security number (B).

2. One-to-Many

One instance of an entity (A) is associated with zero, one or many instances of another entity (B), but for one instance of entity B there is only one instance of entity A. For example, for a company with all employees working in one building, the building name (A) is associated with many different employees (B), but those employees all share the same singular association with entity A.

3. Many-to-Many

One instance of an entity (A) is associated with one, zero or many instances of another entity (B), and one instance of entity B is associated with one, zero or many instances of entity A. For example, for a company in which all of its employees work on multiple projects, each instance of an employee (A) is associated with many instances of a project (B), and at the same time, each instance of a project (B) has multiple employees (A) associated with it.

Example;

*~Evaluation~*

Just Check the Different between psql and mysql.

IT managers and other decision makers should not make the mistake of thinking of all open source RDBMSs in similar terms; there are radical differences between MySQL and PostgreSQL, and when considering which of these two alternatives to use, an informed decision must be made, that considers the rather different set of trade-offs that each system represents.

What are the similarities?

The only reasonable basis of comparison between MySQL and PostgreSQL is that they are both open source database projects. However, even here the similarity is small. MySQL is distributed under a more restrictive "Copyleft" licence that effectively forces proprietary software vendors to either open source their application, or purchase a proprietary licence from a single commercial entity and copyright holder, Oracle. In contrast, PostgreSQL is distributed under the most permissive terms that are practical. There is no single, controlling commercial entity. The project's licensing terms are so liberal that it is permitted to create proprietary commercial derivatives without paying any royalties, as has already happened, for example, in the case of EMC's data warehousing solution Greenplum.

What are the differences?

The projects have radically different technical outlooks in almost all regards, and each is informed by a completely different set of concerns.

PostgreSQL has traditionally focused on reliability, data-integrity and integrated developer-focused features. It has an extremely sophisticated query planner, which is capable of joining relatively large numbers of tables efficiently.

MySQL, on the other hand, has traditionally focused on read-mostly web apps, usually written in PHP, where the principal concern is with optimising simple queries.

While both projects have improved considerably since these impressions were initially formed, and a comparison is more difficult to make then it once was, the technical foundations and developer mindsets that informed these earlier perceptions continue to be a pervasive influence on both projects.

Table A is not an exhaustive list of features, data types, or performance issues relating to these two database systems—it just gives a view of what each has to offer. From the table we see that PostgreSQL offers overall features for traditional database applications, while MySQL focuses on faster performance for Web-based applications. Open source development will bring more features to subsequent releases of both databases.
Table A[7] http://www.techrepublic.com/article/mysql-vs-postgresql/

POSTGRESQL MYSQL ANSI SQL compliance Closer to ANSI SQL standard Follows some of the ANSI SQL standards Performance Slower Faster Sub-selects Yes No Transactions Yes Yes, however InnoDB table type must be used Database replication Yes Yes Foreign key support Yes No Views Yes No Stored procedures Yes No Triggers Yes No Unions Yes No Full joins Yes No Constraints Yes No Windows support Yes Yes Vacuum (cleanup) Yes No ODBC Yes Yes JDBC Yes Yes Different table types No Yes

MySQL and PostgreSQL comparison

*~Conclution~*

As It Student.. we should study them, because we need to compare it to increase our skill. . please command me.. because im still noob in english and make report.. :-( . .  
contoh erd ::

Read More