Understanding DBMS and MySQL (UDM)

1. Database

Database is a collection of data / files that are interrelated / related. The relationship is indicated by the key of each data / file. Meanwhile, Data is a collection of facts and figures that can be processed to produce information.

Other opinions (Optional)

According to Whitten, Bentley, and Dittman (2004, p27), Data is raw facts about people, places, events and things that are important in an organization. While Information is data that has been processed or reorganized into a meaningful form. Information is formed from a combination of data that is expected to have meaning to the recipient.

According to Ramakrishnan and Gehrke (2005, p4), a database is a collection of data, specifically describing the activities of one or more related organizations.

According to Connolly and Begg (2005, p15), a database is a collection of logically related data, and a description of this data designed to meet the information needs of an organization.

Example:

In the scope of a company, or agency. The database can be related to a particular object, topic or purpose. For example, telephone books, language dictionaries, library book catalogs, music and video collections, customer data, supplier data, student data, employee data, etc.

Most data represents / represents facts, while "Record" is a term often used for details / specifications of data stored in the database. For example, employees (data) have names (records), ID numbers (records), dates (records), gender (records), etc.

Okay, hold the question for now, because I'm sure that beginners may still not understand the definition above. I'll try to explain with a picture:

Please pay attention to the picture above!

1. You can use a database as an analogy to storage media, whether it's a flash disk, cupboard, shelf, display case or something else.
2. Database consists of several tables, while the table is a framework of data / facts / objects / classes / instances. Above are examples of products, customers, and orders.
3. Entity is a table/data whose existence is very unique, and can be distinguished from other objects.
4. Tables consist of several rows and columns, rows represent attributes or details of data / facts / objects / classes / instances.
5. Columns, also called Attributes, represent standard settings that are provided by MySQL.
6. Cell is part of a table, row, or column. And the term "field" refers to a cell.

Database Characteristics

• An organized collection of data
• There is a relationship between data
• Objectivity.

Benefits of Database

It is easy to obtain certain information, for example answers to questions such as:

• How many students are taking the "Introduction to Database" course?
• Who graduated in August this year?
• What percentage of students did not register last semester?
• How many credits are obtained by students with NIM 12345?

Database Purpose

• Speed ​​and ease of obtaining information (speed)
• Storage space efficiency
• Accuracy
• Availability
• Completeness
• Security
• Shared use (sharability).

Database Abstraction

Database Levels

Data abstraction is the level at which users view how data in a database is actually processed, to suit the conditions faced by the user.

A DBMS often hides the details of how data is stored and managed in a database, in order to make it easier for users to use the DBMS. The levels consist of:

1. Physical Level
2. Conceptual Level (Conceptual Level)
3. View Level.

1. Physical Level (Physical Level)

It is the lowest level of data abstraction, which describes how data is stored in real conditions. This level is very complex because the data structure is described in detail.

2. Conceptual Level (Conceptual Level)

This level describes what data is stored in the database and explains how the data is related to each other as a whole. A user at this level can see that student data is stored in the student table, the krs table, the transcript table and so on. This level is usually used by a Database Administrator (DBA).

3. View Level

It is the highest level of data abstraction, which describes or displays part of the entire database, tailored to user needs.

Example:

The finance department/staff only needs financial data, so what is depicted/displayed is only a view of the financial data. Likewise, the accounting department only needs the data needed for accounting. So not all database users need all the data/information in the database.

Database Development

Database Implementation Examples

• ATM
• e-Mobile Banking and Internet Banking
• Tracking Package Goods
• Distance learning
• Flight Ticket Reservation
• Library System
• Dynamic Web

Database Pioneers

2. DBMS

DBMS (Data Base Management System) is a database management system that uses computers to store data in such a way that it becomes easier to retrieve, manipulate, and produce information for users.

Other opinions (Optional)

According to Ramakrishnan and Gehrke (2005, p4), Database Management System or DBMS is software designed to help maintain and use large collections of data.

Basic Components of DBMS

• Hardware
• Software
• User (Database Administrator (DBA), Programmer, End User)

DBMS Architecture

3. MySQL

MySQL (pronounced /maɪˌɛskjuːɛl/ My SQL, or "My sequel" /maɪsiːkwəl/) stands for "My Structured Query Language". It is the world's most popular open source database software package. With proven reliable performance and ease of use.

MySQL has become the most favorite database and trusted choice for leading web-based applications such as Facebook, Twitter, YouTube, Yahoo, and many more.

Oracle drives MySQL innovation, delivering new capabilities to power next-generation web, cloud, mobile, and embedded applications.

MySQL is a client-server and multi-user DBMS. This means that there is MySQL software that runs as a client, and there is also one that runs as a server. The server functions to provide services for requests made by the Client. There are many users who can access the server database simultaneously through the client program.

Benefits of DBMS for Managing Data

According to an expert named Ramakrishnan and Gehrke (2005, p9), using DBMS to manage data has several advantages, including:

1. Data Independence

Application programs are ideally able to provide detailed data representation and storage, while the DBMS hides this and provides an abstract view of the data.

2. Data access becomes efficient

DBMS uses a variety of techniques to store and retrieve data efficiently. This feature becomes important if the data is stored on external storage.

3. Data Integrity and Security

If data is always accessed through a DBMS, then constraints on data integrity can be enforced.

Example:

Before entering data for employee salaries, the DBMS can check whether the department's budget is sufficient. In addition, the DBMS can also run authority controls for data access for certain users.

4. Data Administration

When multiple users share data by centralizing it with an administrator, it allows for significant benefits. Such as Professionals who understand the nature of the data being managed and understand how different user groups share/access data.

These professionals can also be given the responsibility to manage data representation, to minimize redundancy, and also to fine-tune data storage.

5. Concurrent Access and Crash Recovery

You can also interpret it as "Collective Access" and "Recovery After Collision"

Mass data access has been scheduled by the DBMS in such a way that users only need to think about the data they are accessing. In addition, the DBMS also protects users from the effects of system failures.

6. Save Application Development Time

DBMS supports many important functions commonly used by DBMS data access applications. These are closely related to high-level interfaces, facilitating rapid application development.

Applications become more reliable, because many important application tasks are handled/supported by the DBMS.

Stages of Creating a Database

Maybe other lecturers also have their own techniques, while my techniques include the following:

Database Creation Techniques

1. Determine Candidate Database and Table Names

To determine which ones will become tables or fields, please just take out/write down everything that is on your mind, after everything is written down and there is nothing else you can think of, then the next step is to please determine which ones are suitable to become tables and primary keys (just choose according to your beliefs, later if it is wrong we will just correct it)    >> at the evaluation stage).

2. Creating Standardization

Create a standardization of writing table names and field names, because considering that this is case sensitive, actually you are free to create your own standardization, but if I may recommend, it is better to use all lowercase letters, and if there are more than 2 words, it is better to separate them by using capital letters and do not use underscores because it is to distinguish between standard field names and functions, because when we enter the coding level later, it will be easier for us and we don't need to bother going back and forth to look at the database.

3. Avoid Assumptions

Avoid assumptions, because if someone has made a game, of course this is very disturbing and troublesome, especially in teamwork, because the assumption will later enter the analysis stage, so I'll just give an example, like "gender" most programmers use 0/1 logic? yes if this is certain, and it turns out to be true, like a gender, if not female then male, so we can determine which one represents 0/1, well if it's like this, it's easy, try if we assume .... it's very troublesome !!, for example "gender" >> can be male, female, male, female, M, P, what else ???? heeheh.. if it's like this, it will be troublesome at the coding level because there are too many "if, else if" (this is example 1), then for example if "status" even though this is multi-perception, we should just determine it and not assume, then, "status" = married, unmarried, widower, widow, well if that's the case, it's easy, Try assuming, later it can be more than that, for example "status" = married, married, unmarried, single, joko, dating, widower, widow, etc.

A Little Back In Time To Stimulate The Brain

Okay to remind you all, about the Database material in the last semester, just a little bit.. maybe we can start from creating a database, okay let's determine what kind of database we need to create? library, export-import, transportation, or academic? okay, sorry, I'd better determine it, how about Academic, which is closer to us, besides that later we can also know more about the processes in academics. I'll just determine it, please try to make it as best as possible and as memorable as possible, just relax, >>> 15 minutes later (15 minutes later) ... Notepad++ is blank..   really clean and in the end I just wrote this insert "sorry sir, the memory is experiencing a hard reset  " the following is an assignment made to stimulate my memory, et al.

Home Work

• DB names = academic
• Table 1: students = student ID number, name, address, photo, family type, cellphone number, status.
• Table2: value = value id, subject id, value, lecturer.
• Table 3: course = course ID, name, credits, semester.
• Table 4: lecturer = lecturer ID, name, address

Finally I brought this homework home hehee   and it's time to go back to the past... I'll try using CMD, see the full tutorial  HERE .

Understanding Entity Relationship Diagrams (ERD)

The ER model is usually depicted in the form of a diagram called an Entity-Relationship Diagram (ERD). ERD is a graphical notation in conceptual data modeling that is used to model data structures and relationships between data. With ERD we can test the model by ignoring the process that must be done. And with ERD we try to answer questions such as; what data do we need? how is one data related to another? ERD uses a number of notations and symbols to describe the structure and relationships between data, basically there are 3 types of symbols used, namely:

1. Entity

Entities are depicted in rectangular shapes.

Figure 10.14. Entity notation in ER-Diagram

An entity is an individual that represents something real (its existence) and can be distinguished from something else. It can be an element of an environment, a resource or a transaction that has significance for a model to be built.

Example of Entity set:

• All Teachers or Teachers Only (This association has members: Mr. Fahri, Mrs. Fitri, Mr. Joko and other teachers)
• All Students or Students only (This association has members: Joni, Ridho, Fanny, Donny and other students)
• All Cars or Cars only (This group has members: Daihatsu cars, Toyota cars, Suzuki cars, and other cars).

Identifying the presence or absence of an entity in a problem is indeed not easy. But usually if we find a noun in the problem then the word is usually a candidate entity. For example, if we are going to build a school library database, then we will find books, students, teachers, librarians as strong candidate entities.

2. Attributes

Attributes are depicted in the form of ellipses and are associated with the entity in which the attribute resides.

Figure 10.15. Use of attribute notation in ER-Diagram

Every entity has attributes. Attributes are characteristics or traits that distinguish one entity from another.

Attribute Examples:

Student Entity

Has attributes including: nis (student registration number), name, address, telephone number, place of birth, date of birth and others.

Figure 10.17. Student entity and its attributes

Teacher Entity

Has attributes including: NIP, name, address, rank, telephone number, place of birth, date of birth, field of expertise, etc.

Figure 10.18. Teacher entity and its attributes

Car Entity

Has attributes including: Engine number, chassis number, color, year of manufacture, engine type, fuel, etc.

Figure 10.19. Car entity and its attributes

Not all characteristics of an entity are important for a problem scope. For example, in a library database problem, the characteristic of a student's shoe number is not an important characteristic that can be used as an attribute. But if the scope of the problem is a student shoe uniform ordering database, then the shoe number is an important attribute.

Apart from choosing which ones are really important for an entity, we also have to determine which ones will be the key attributes (Primary Key).

In the example above (attribute example) we can easily determine the primary key of an existing entity.

Example of Key Attribute (Primary key):

Student Entity

For example, the attributes owned are: NIS (student registration number), name, address, telephone number, place of birth, and date of birth. Of these six attributes, the most suitable to be a primary key is NIS because this attribute is the most unique. No student has the same NIS. The name may still be the same, but the NIS is not.

Teacher Entity

Suppose the attributes owned are: NIP, name, address, rank, telephone number, place of birth, date of birth, and field of expertise. The most suitable attribute to be a primary key is NIP because this attribute is the most unique. No teacher has the same NIP. So NIP can be used as a teacher entity identifier.

Car Entity

Suppose the attributes owned are: Engine number, chassis number, color, year of manufacture, engine type, and fuel. Here there are two unique attributes, namely engine number and chassis number. In this case we can choose one of the two attributes as the primary key.

As a guide, the following are the characteristics of attributes that can be considered as identifiers (candidate keys):

• The value does not change
• It is impossible to contain a null value (empty is not zero)
• Does not contain name or location data that may change.

3. Relationship

Relationships are described in the form of diamonds.

Figure 10.16. Use of relationship notation in ER-Diagram

Relationship is a relationship that occurs between a number of entities. For example, from the student entity there is a student who has NIS = "GHI007" and student_name = "Donny" has a relationship with the expertise program entity with program_code = "RPL" and program_name = "Software Engineering". The relationship between the two entities means that the student is taking the expertise program at a particular school.

Figure 10.20. Relationship

Ramakrishnan and Gehrke (2000) stated that the concept of relationship in the ER model is different from the concept of relation in the relational data model. Relationship is a mechanism that connects between entities. In the implementation into DBMS, both entities and relationships will be represented in the form of tables (relation).

Every relationship always has cardinality. Cardinality or Degree of Relationship indicates the maximum number of entities that can relate to another entity in another entity set.

In Figure 10.20 we can actually see a cardinality between the student entity set and the expertise program entity set. Students can relate to only one entity in the expertise program entity set. Conversely, one entity in the expertise program can relate to many students. In the figure, Donny can only relate to Software Engineering, while Software Engineering can relate to both Donny and Joni.

There are several types of relationship levels (cardinality) between one entity and another. Cardinality indicates the maximum number of entities in an entity set that can relate to entities in another entity set. In general, there are three forms of cardinality between entity sets, namely:

One to One

Relationship with one-to-one cardinality means that one member of an entity set can only be related to one member of another entity set. The relationship between the husband and wife entity sets can be grouped into a one-to-one relationship.

Figure 10.21. One-to-one relationship between husband and wife

One to Many / many to one (one to many / many to one)

One-to-many and many-to-one cardinality can be considered the same because the review of cardinality is always seen from two sides. For example, in a school there is a rule that one class consists of many students but not vice versa, namely one student cannot study in a different class.

Figure 10.22. One-to-many relationship between class and students

Many to Many

This cardinality is quite complicated to explain but we often encounter it. For example, the relationship between students and subjects has a many-to-many cardinality. Students have the right to take (study) more than one subject and each subject can be taken (studied) by more than one student.

Introduction to Relational Databases

Hi dev, tonight I want to discuss relational databases, let's start from,

1. Relational Model

One of the most well-known database models is the relational model, where data is arranged in the form of tables and there can be a relationship between two tables.

Example;

We can obtain information from related tables, for example;

2. Basic Terms

Okay, now that we understand the general concept of relational, let's discuss the general terms that are often used in the world of relational databases.

A database can consist of a number of tables. Meanwhile, tables are often also called relations. Each table stores a set of data or rows.

The image below will help you understand the sentence above.

Data in a table follows a hierarchical standard as follows.

3. Characteristics in Relationships

• No twin lines
• The order of the rows is not important
• Each attribute has a unique name
• Free attribute placement
• Each attribute has a single value and its type is the same for all rows.

The meaning of no twin rows is like this;

• The line has an identity that makes two lines not identical.
• This identity is called the "primary key"

The rows highlighted in yellow are examples of twin rows.

The implication that the row order is unimportant is because we are referring to unique identities, not sequential numbers.

Each attribute has a unique name, and can be located anywhere.

Each attribute has a single value.

Each attribute has the same data type.

Source

Database System Lecture Material, by Wahyu Widodo.

The relational database model was first introduced by EF Codd in 1970. This data model is based on a simple and natural mathematical structure, namely relations (tables). Data manipulation operations are all rooted in mathematical logic. This makes expressions in tables can be analyzed and optimized (Lewis et al., 2002).

The main formant in the relational data model is the relation (table). Relation consists of two important things, namely schema and instance. Relation instance is nothing more than a two-dimensional table with rows and columns. Rows are commonly referred to as tuples, which have the same meaning as records in a file. But unlike file records, all tuples have the same number of columns and there are no tuples in the same relation instance. Columns in relation instances are also known as attributes or columns (Ramakrishnan and Gehrke, 2000; Lewis et al., 2002). Figure 10.23. shows how the relationship between table/file/relation, row/record/tuple and column/field/attribute. This figure also shows the structure of the relationship.

Figure 10.23. Table/file/relation, row/record/tuple and column/field/attribute relationships

Relation schema consists of the name of the relation, the names of the attributes in a relation along with their domain names, and integrity constraints. The name of the relation must be unique in a database, or there must be no identical relation names. The attribute name is the column name of the relation and there must be no identical attribute names in a relation. The domain name of an attribute is related to the data type used by the attribute. Integrity constraints are restrictions on relational instances in a schema (Ramakrishnan and Gehrke, 2000; Lewis et al., 2002).

1. Relational Database Structure

As explained above, a table consists of rows and columns.

Row/Baris/Tuple

Row/Baris/Tuple is a collection of related attributes in one row (see Figure 10.23). This row will always repeat with the same structure but with different data content. For example, in Figure 10.23, the first row has the same structure as the 2nd and 3rd rows. However, the data in each row is different. We can identify that for buyers with id_beli = 1 must have the name = Cristiano Ronaldo and for id_beli = 2 must have the name = Ryan Giggs, and so on.

Field/Column/Attributes

Field/Column/Attributes shows the structure of data from repeated rows. In Figure 10.23, there are 4 columns, namely buyer_id, name, address, and telephone. Data in the buyer_id column, for example, will have the same structure, namely in the form of numbers and is a sequence of buyer ids. Likewise, the name column only contains the buyer's name, not mixed with other data.

A column must have a column name and data type for the data contained in the column (Figure 10.24). In addition, sometimes there are also constraints and domains for the data included in the column. The data type will depend heavily on the attributes used and the operations to be performed on this database. The data domain has many similarities in meaning with the function of the data type used. However, the data type refers more to the data storage capability that is possible for an attribute physically, without considering whether or not the data is feasible when viewed from real-world usage. While the data domain is more emphasized on the limits of the values ​​allowed for an attribute, seen from the reality of its use.

Figure 10.24. Columns, constraints and data types (Powell, 2006)

In Figure 10.24, the names of the columns in a table are complete with their data types and constraints. The ISBN column, for example, has an integer data type, meaning that this column can only be filled with integer numbers. In addition, this column has a no null constraint, which means that when filling in data, this column must always be filled and cannot be left blank. In the Pages column, the data type is date, meaning that it can only contain data in the form of dates. The Pages column does not have a null constraint, meaning that this column can be filled or not filled.

The types of data types depend on the DBMS used. For example, MySQL provides a wider range of data types than Microsoft Access. In MySQL, for example, we will find data types set, enum which are not found in Microsoft Access. Choosing the right data type is very important because it affects data consistency and database performance.

An example of a domain is, if we are dealing with attributes/columns about classes in Elementary Schools (SD). This class column can only be filled with numeric data from 1 to 6, because there are no classes 7 or 4.5 in SD. This means that the domain of the class column is an integer number (whole and no fractions) between 1 and 6 only. Another example is the exam score column, this column's domain is a natural number (real and may be a fraction) between 0 and 100.

There are several types of attributes, namely:

Simple Attribute, namely an atomic attribute that cannot be broken down any further.

Figure 10.25. Example of a simple attribute.

Composite attribute, namely an attribute that can be further broken down into sub-attributes, each of which has meaning.

Figure 10.26. Example of a composite attribute

• Single-valued attribute, namely an attribute that has at most one value for each row of data. 
• Multi-Valued Attribute, namely an attribute that can contain more than one value but of the same type.

Figure 10.27. Examples of single-valued attributes and multi-valued attributes

Derived Attribute, namely an attribute whose value is obtained from processing results or can be derived from other related attributes or tables.

2. Relationships Between Tables

The main advantage of relational databases compared to other database models is the ease of building relationships between tables in a form that makes sense and can be understood. Relationships between tables can be derived directly or indirectly from the ER-Diagram that has been discussed previously. Entities in the ER-Diagram are usually candidates for a table in a relational database. Relationships between tables can usually be identified from the relationships between entities in the ER-Diagram.

To clarify how relational databases handle relationships between tables we will use the following tables. The first table is Author. This table consists of 3 columns, namely author_id, author and birth_year (Figure 10.28). In this table, author_id is the primary key.

Figure 10.28. Author Table

The second table is Publisher. This table has 6 columns, namely, publisher_id, name, company_name, address, city and telephone (Figure 10.29). The primary key in this table is publisher_id.

Figure 10.29. Publisher Table

The third table is Books. This table has 6 columns, namely, title, year_published, ISBN, publisher_id, description, and group (Figure 10.30). The primary key in this table is ISBN. There is something a little strange in this table, namely the publisher_id column which is one of the columns in the Publisher table, is included in this table. Actually this is not strange or an error, but this is how relational databases handle relationships between tables.

Figure 10.30 Book table

In the real world, we will find that one publisher does not only produce one book title, but hundreds or even millions of book titles. So formally the relationship between publisher and book can be expressed as a relationship with one-to-many cardinality. If depicted in the form of an ER-Diagram it will look like Figure 10.31. One publisher can publish many book titles and one book title is only published by one publisher. To make it clearer, look at Figure 10.31. A publisher with publisher_id = 13 (McGraw Hill) publishes 6 books. On the other hand, a book with ISBN = 0-0702063-1-7 (Guide To Oracle) is only published by a publisher with publisher_id = 13 (McGraw Hill). So the placement of the publisher_id column in the book table is intended to represent the relationship between Publisher and Book. The publisher_id column in the book table is commonly referred to as a foreign key.

Figure 10.31. ER-Diagram for Publishers and Books

Figure 10.32. Relationship between Publisher and Book tables

In the tables that have been created above, we have not seen a table that shows a particular author who wrote what book. To create a table containing authors and their books, we can use the relationship between the author table and the book table. But before that, we have to see how the relationship between authors and books in the real world. An author may only write one book title, but maybe more. While one book may be written by only one author, but it may also be written by two, three or more authors. So we can say that the cardinality of the relationship between authors and books is many-to-many. We can describe the ER-Diagram for this case as in Figure 10.33.

Figure 10.33. ER-Diagram for Authors -- Books

In the case of many-to-many cardinality, we cannot directly insert a foreign key into another table. We must create a new table so that the cardinality between the tables involved can be changed to one-to-many. The Pengautor_Book table is a table that is formed to handle the relationship between the Book table and the Author table. This table only contains two attributes (columns), namely ISBN which comes from the Book table and id_pengautor which comes from the Author table. In Figure 10.34, it can be seen in the Pengautor_Book table that there are several books written by more than one author.

Figure 10.34. Relationship between Author and Book tables

Figure 10.35. Relationships between tables

3. Summary

• A database is a collection of data that is interconnected with each other, stored on computer hardware and used by software to manipulate it. 
• Basic database operations include creating a new database, deleting a database, creating a new table, deleting a table, filling or adding new data, retrieving data, changing data, deleting data.   
• Database Management System is software that works specifically to handle databases. 
• Entity-Relationship Diagram is a graphical notation in conceptual data modeling that is used to model data structures and relationships between data. The elements of ERD are entities, attributes, relationships and cardinalities. 
• The main element in the relational data model is the relation (table). A relation instance is a two-dimensional table with rows (row/record/tuple) and columns (column/field/attribute). 
• In relational databases, proper identification of the relationships between attributes within a table and the relationships between tables is the key to creating a good database.

4. Practice Questions

1. Visit the school library, then make a brief observation. Take notes to determine who and what is involved in the school library activities. Observe which can be classified as entities, attributes. And how the relationships between entities. 
2. From the results of activity no. 1 then create tables that show entities and their relationships. Also determine the attributes of each table. 
3. Also pay attention to the types of attributes you have determined.

The picture on the side is a picture of a mobile phone or better known as a handphone that is running one of the applications, namely the Contact List. Maybe the application as in this picture is familiar to those of you who have used a cellphone. This application stores the names of friends, parents or others complete with their names and telephone numbers. Sometimes even equipped with email addresses, office addresses, fax numbers and other data.

Figure 10.1. Contact list facility on a mobile phone

Pay attention to this Contact List application. When you enter someone's name and phone number, the name and phone number will not be confused with someone else's name or phone number. Everything is neatly arranged. You can also search for a particular person's name by simply typing a few letters that are related to the name. Also note that the names stored are always in alphabetical order, even if you enter them out of order.

Name data, phone numbers and other data in the Contact List application are arranged based on the concept of data management better known as a database. In this chapter we will learn the basic concepts of databases. Basic competencies in database management systems (DBMS) are part of the competency standards for creating Microsoft Access-based applications which will be discussed in detail in Chapter 11. The final part of this chapter will be closed with a summary and practice questions.

Objective

After studying this chapter, it is expected that readers will be able to:

• Explaining the concept of data, database and database management system (DBMS)
• Explaining Entity-Relationship Diagrams
• Explain relational databases.

1. Database

A database is a collection of data that are interconnected with each other, stored in computer hardware and used by software to manipulate it. A database is one of the main components in an information system, because it is the basis for providing information to users (Fathansyah, 1999; Post, 1999).

If you imagine, a database is similar to a cabinet in a school administration room that stores various archives. Each type of archive is grouped, arranged and stored in a designated place. So there will be groups of student archives, teacher archives, subject archives, financial archives, and so on. The only difference is in the storage media. If an archive cabinet uses a cabinet made of wood, iron or plastic, while a database uses electronic storage media such as disks (hard discs, CDs, or tapes). Figure 10.2 provides an illustration of the similarities between an archive cabinet and a database.

One important thing to note, a database is not just an electronic data storage. Not all electronic data storage can be called a database. If the storage does not use the principle of arrangement, separation or organization then we cannot call the data storage a database. Figure 10.2 shows the application of the principle of arrangement, organization or separation, both in filing cabinets and in databases.

Figure 10.2. Filing cabinet and database

The main principle in database is the concept of data independence, namely the separation of data from its application program (Lewis et al., 2002; Post, 1999). While the main purpose of the database is to help users in abstracting a system. There are three levels of abstraction that are usually used, namely physical level, conceptual level and view level (Figure 10.3). The physical level shows how data will be stored. The conceptual level relates to what data will be stored and how the data is related. The view level is the highest level that explains parts of the database to a particular user (Ramakrishnan and Gehrke, 2000).

Figure 10.3. Levels in data abstraction (Lewis et al., 2002)

The database has several important criteria, namely:

1. It is data oriented and not program oriented.
2. Can be used by multiple application programs without the need to change the database. 
3. Can be easily developed, both in volume and structure. 
4. Can meet the needs of new systems easily 
5. Can be used in different ways.

In stages, basic database operations can be described in the scheme in Figure 10.4. These operations include:

• Creating a new database (create database). This operation is the same as creating or purchasing a new filing cabinet.
• Drop database. This operation is the same as destroying or destroying a filing cabinet. 
• Creating a new table (create table). This operation is the same as adding a new archive group. This operation can only be run if the database has been created. 
• Drop table. This operation is the same as destroying the old archive group. This operation can only be run if the table already exists in a database.

Figure 10.4. Basic operations on a database

• Filling or adding new data (insert data) to a table. This operation is similar to adding a new archive sheet to an archive group. This operation can only be run if the table has been created. 
• Retrieve data from a table. This operation is similar to searching for archive sheets stored in an archive group. 
• Changing data from a table (update data). This operation is similar to correcting the contents of an archive sheet from a group of archives. 
• Deleting data from a table (delete). This operation is similar to deleting a file sheet from a file group.

Databases are built to fulfill the objectives of organizing data, which include the following:

• Efficiency includes speed, storage space and accuracy.
• Handle large amounts of data. 
• Shareability. 
• Eliminate data duplication and inconsistency.

2. Database Management System 

Physical database management is not done directly by the user, but is handled by a special/specific software. This software is called DBMS which will determine how data is organized, stored, changed and retrieved. It also implements data security mechanisms, shared data usage, data accuracy/consistency enforcement, and so on. Briefly, the structure of a DBMS can be seen in Figure 10.5.

Figure 10.5. General structure of DBMS

Additional applications are optional (can be there or not) and are usually found in DBMS as additional functions. For example, report making applications, form designing applications, diagram or chart making applications, system monitoring applications, and other applications.

There are dozens, maybe even hundreds of DBMS software available. Each with its own specifications. Starting from the very simple to the most complex. In this section we will discuss 5 DBMS that are quite familiar among DBMS users, namely Microsoft Access, MySQL, Microsoft SQL Server, PosgreSQL, and Oracle.

Microsoft Access

Microsoft Access or sometimes also called Microsoft Office Access is a relational DBMS output from Microsoft which is included in the Microsoft Office package. Microsoft Access combines the Microsoft Jet Database relational engine, Graphical User Interface (GUI) and software development tools. Microsoft Access can use data stored in Microsoft Jet Database, Microsoft SQL Server, Oracle or other types as long as they are compatible with ODBC (Open Database Connectivity).

Rapid Application Development, especially for building prototypes and stand-alone applications. Microsoft Access can also be used as a database for simple web-based applications. However, in more complex applications, whether web-based or not, Microsoft Access is not a good choice. Especially because of its shortcomings in handling use by many users (multi-user). This is because Microsoft Access is actually a personal database that is more intended for single-users. Microsoft Access is also not equipped with database triggers and stored procedures.

Figure 10.7. Microsoft Access view

One of the advantages of Microsoft Access for programmers is its compatibility with SQL (structured query language) is relatively high. In Microsoft Access we can create text-based or GUI-based queries and then can be executed directly and easily to get results.

MyQSL

MySQL is a multi-user and multi-threaded SQL-DBMS. MySQL runs as a server that serves many users to access a number of databases. This DBMS is very popular in the world of web-based applications as a database component. In addition to being available in a free version, MySQL's popularity is also greatly influenced by the popularity of the Apache web server and the PHP programming language. Terms such as LAMP (Linux-Apache-MySQL-PHP/Perl/Python), MAMP (Mac-Apache-MySQL-PHP/Perl/Python and WAMP (Windows-Apache-MySQL-PHP/Perl/Python have become very popular. Many web-based applications are built using a combination of these software. WordPress, Drupal, Mambo, Wikipedia, PHP-Nuke, are some examples of web-based applications that use this combination.

Unlike Microsoft Access, the default MySQL installation does not provide a GUI for users to interact with the database. Users can interact with the client using text-based commands. However, many GUIs have been developed to facilitate interaction with databases, both in the form of stand-alone applications (e.g. MySQL-Front, MySQL-GUI, etc.) or web-based (e.g. phpMyAdmin). Even by using the MyODBC component, MySQL can be accessed with the GUI from Microsoft Access like other ODBC-compatible databases.

Figure 10.9. Initial view of phpMyAdmin

Microsoft SQL Server

Microsoft SQL Server is a relational DBMS from Microsoft, like Microsoft Access. The main query language used is a variant of ANSI SQL called T-SQL (Transact-SQL). This language allows users to create stored procedures, thereby increasing the efficiency of access to the database. This DBMS is also equipped with clustering and mirroring facilities. A cluster is a collection of servers with identical configurations, allowing for the division of work between servers. While the Software Engineering 261 mirroring facility allows a DBMS to create a complete replica of the database contents for use on another server.

Microsoft SQL Server is available in several distribution versions. In Microsoft SQL Server distributed with Microsoft Office or Microsoft Visual Studio, commonly called MSDE (Microsoft SQL Server Database Engine), it is not equipped with a GUI device. Users can interact using a text-based client such as MySQL. While in higher versions such as the personal or professional version, GUI facilities are available (Figure 10.11).

Figure 10.11. GUI on Microsoft SQL Server

PostgreSQL

PostgreSQL or often called Postgres is included in the category of Object-Relational Database Management System (ORDBMS). ORDBMS is a DBMS that in addition to using relational database principles also uses an object-oriented approach in its database model. Postgres was developed as free software and is open-source so it is not controlled by one or two companies.

The advantages of Postgres compared to other DBMS 262 Software Engineering are its free and open-source nature, its excellent documentation support, its flexibility and its features that are not inferior to commercial DBMS. In addition to supporting the object-relational data model, Postgres also supports the use of spatial databases (usually for Geographic Information System use). Postgres also supports multi-user and multi-threaded operations, perhaps even better than MySQL in terms of security.

Like MySQL and Microsoft SQL Server, we can interact with the Postgres database using client-side commands with a tool called psql. GUI interfaces have also been widely developed, including phpPgAdmin, PgAdmin, and others.

Oracle Database

Figure 10.13. Oracle Logo

The name Oracle Database or Oracle RDBMS is a name that is highly regarded in the DBMS world. Oracle was developed by Oracle Corporation.

Oracle stores data logically in the form of tablespaces and physically in the form of data files. Tablespaces can contain various memory sections, such as data sections, index sections and so on. These sections contain one or more areas. These areas contain contiguous blocks of data. Oracle can store and store procedures and functions independently.

Database Management System Application

Some of the most well-known DBMS products today are Microsoft SQL Server, MySQL, ORACLE and PostgreeSQL. All of these DBMS use the same command, namely SQL (Structure Query Language). The approach used in this dictation uses the Transact SQL command typed in Query Analyzer. SQL Server 2000 is a DBMS product made by Microsoft.

SQL Server 2000 offers several features in managing databases. There are two commonly used features, namely:

1. Using Enterprise Manager

This feature is relatively easy to use because the database management mode uses the GUI (Graphical User Interface) feature. So just by using the click and drag mouse method, you can manage the database easily.

2. Using SQL Query Analyzer

This feature uses Transact SQL to manage databases. Transact SQL commands are an extension of standard SQL commands that are tailored to SQL Server database management.

In Figure 2, the left panel displays all database lists and objects in the database. Such as tables, views, triggers, stored procedures. The SQL Editor window is used to type Transact SQL commands to manage the database.

3. SQL Server 2000 Objects

1. Diagram

It is a diagram used to design a relationship between tables in a database.

2. Tables

Storing data records. Tables are the core of a database that are grouped into rows and columns.

3. Views

A virtual table used to access specific data in a table. Important data that is not displayed publicly can be hidden by using views.

4. Store Procedure

A set of SQL commands stored in the database server and can be executed via the execute command or the F5 key.

5. Users

Users who are given the right to access the database on the server.

6. Function

A set of SQL commands that can return values.

4. Query Analyzer

Before using the Query Analyzer feature you must connect to SQL Server, by selecting Start >> All Programs >> Microsoft SQL Server >> Query Analyzer. Please select Windows Authentication mode >> OK. If you choose SQL Server Authentication then fill in the login and password at the time of the first SQL Server 2000 installation.

The Query Analyzer work window is divided into two panels. The top panel is the SQL Editor panel, while the bottom panel is the results panel. If only the top panel is visible, press the Hide Result button next to the Database Tools. To try typing the first SQL command in the SQL Editor panel, type the following command:

USE contoh

Then press F5 or the Execute Query button. The results will appear on the bottom panel as shown in Figure 5. If you have never created the database, an error message will appear on the panel. SQL commands in the SQL Editor can be saved in a folder by selecting the File >> Save menu or pressing the Save Query/Result button. There are three formats to save files: .sql, .tql and .txt. The file can be opened with Ms. Word or Notepad.

5. Using Comments

In SQL Server 2000 there is a comment feature that can be inserted into SQL commands. Comments are declared in two ways in Query Analyzer.

1. Give a single line comment. The way to do this is by giving a -- (double dash) sign in front of the SQL command line.

Example:

-- mengaktifkan databse contoh
use contoh

2. Giving comments on more than two lines in SQL. The way to do this is by giving a sign  /* and ending it with a sign  */.

/*
perintah ini akan menampilkan
seluruh data pegawai dari tabel
biodata tanpa kriteria
*/
select * from biodata

How to Register a Database to SQL Server Enterprise Manager

The image above shows that the database server has not been registered. So to register the database server through Enterprise Manager do the following steps:

• Right mouse click on the SQL Server Group node >> New SQL Server Registration.
• On the Register SQL Server Wizard menu, select Next, fill in the computer name or IP Address on Available Servers. Click Add >> Next.

Make the selection as shown in the image above and then press Next. On the next menu, select Add the SQL Server(s) to an .... If you want to register the database server to an existing group or select Create a new ... to define your own server group. Then select Next.

Press Finish which indicates the database registration process is complete, then select Close.

The successfully registered SQL Database Server will be immediately displayed in the Enterprise Manager main window, as shown in the image above.

How to Create a Database in SQL Server

The following is an example of a basic command to create a database using the SQL Editor command,

create database praktek
on primary (name=dbdat,
filename='c:\program files\microsoft sql server\mssql\data\dbdat.mdf',
size=10,
maxsize=50,
filegrowth=2)
log on
(name=dblog,
filename='c:\program files\microsoft sql server\mssql\data\dblog.ldf',
size=3,
maxsize=20,
filegrowth=1)

To make the above command run Query Analyzer >> Window Authentication >> Ok. To run the query press F5 or the Execute Query button.

To see whether the newly created practice database has been registered in the database list, do the following steps: select the Query >> Change Database menu.

You can also create a database without any conditions, with the following command:

Create database praktek

To see the full format you can see in SQL Server Books Online. To see the list of databases that exist in SQL Server 2000 type the command:

Exec sp_helpdb

If a database is to be used, don't forget to give the command:

/*Use <namadatabase> contoh:*/

Use praktek

The command to delete a database is:

drop database <namadatabase_yang_dihapus>

If more than one database is deleted, use a comma ( , ). Example:

Drop database praktek, latihan, tugas

The SQL commands you give to the SQL Editor can be saved by going to File >> Save, as shown in Figure 4.2. The file can be saved in .sql, .tql or .txt format. The saved file can be opened directly in Notepad or Ms. Word.

You can use the following exercises to study at home:

Exercise

Create two databases in SQL Editor, one with settings and one without settings. Then from the database you have created can be observed in the Query Analyzer window or Windows Explorer. The next step is to delete the database you created.

How to Create a Table in SQL Server

In SQL Server 2000 there are two types of tables, permanent and temporary. Temporary tables are tables placed in RAM while permanent tables are tables placed on disk. The number of columns/fields that can be created by SQL Server 2000 reaches 1024 fields. To see the complete format of how to create a table you can see in SQL Server Books Online.

In general, the command to create a table is:

Create table biodata (
Nim int identity (1,1),
Nama varchar(20) not null,
Jk char(1),
Tgllahir datetime,
Status bit default 1,
Ag varchar(1) constraint agama check (ag in('I','K','P','B','H')),
Primary key (nim))

Explanation

1. Not Null, states that a field's value is unknown. Not Null should not be used on fields that have identity or primary key properties.
2. Identity, states that the field will be automatically filled with values ​​in increments of 1 starting from the number 1 (see 1.1). Identity is the equivalent of auto_increment in MySQL.
3. Default value, meaning the default value of student status is 1, can be interpreted as 1 = not married, and value 0 = married.
4. Constraint, enforces data integrity and maintains data quality. In the ag field only values ​​('I','K','P','B','H') may be input. Constraint is done when creating a table.
5. Rules, its function is the same as constraints but rules are executed if the table has been formed.

To see if a table has been registered in the database, type the command:

Select * from information_schema.tables

Meanwhile, to see the structure of each table, use the command:

Select * from information_schema.columns

Reference:

• http://www.tutorialspoint.com/dbms/dbms_overview.htm
• https://answers.yahoo.com/question/index?qid=20100504162948AAjBCvm
• http://opensource.telkomspeedy.com/wiki/index.php/MySQL
• http://www.mysql.com/about/
• http://whatis.techtarget.com/definition/entity
• https://id.wikipedia.org/wiki/Entity
• Database System Module, By Wahyu Widodo, S.Kom, M.Cs