Basics of DATABASE

DATABASE

• A systematic collection of data
• Databases support storage and  manipulation of data
• Databases make data management easy

Examples:

• An online telephone directory would definitely use database to store data pertaining to people, phone numbers, other contact details, etc.
• Your electricity service provider is obviously using a database to manage billing , client related issues, to handle fault data, etc.
• Let's also consider the facebook. It needs to store, manipulate and present data related to members, their friends, member activities, messages, advertisements and lot more.

DATABASE MANAGEMENT SYSTEM (DBMS)

• A collection of programs which enables its users to access database, manipulate data, reporting / representation of  data .
•  Also helps to control access to the  database.
•  Had been first implemented in 1960s.
• With time database technologies evolved a lot while usage and expected functionalities of databases have been increased immensely.

TYPES OF DBMS

• Hierarchical - this type of DBMS employs the "parent-child" relationship of storing data. This type of DBMS is rarely used nowadays. Its structure is like a tree with nodes representing records and branches representing fields. The windows registry used in Windows XP is an example of a hierarchical database. Configuration settings are stored as tree structures with nodes.
• Network DBMS - this type of DBMS supports many-to many relations. This usually results in complex database structures.  RDM Server is an example of a database management system that implements the network model.
• Relational DBMS - this type of DBMS defines database relationships in form of tables, also known as relations. Unlike network DBMS, RDBMS does not support many to many relationships.Relational DBMS usually have pre-defined data types that they can support. This is the most popular DBMS type in the market. Examples of relational database management systems include MySQL, Oracle, and Microsoft SQL Server database.
• Object Oriented Relation DBMS - this type supports storage of new data types. The data to be stored is in form of objects. The objects to be stored in the database have attributes (i.e. gender, ager) and methods that define what to do with the data. PostgreSQL is an example of an object oriented relational DBMS.

DATABASE ELEMENTS

Tables

A database table is composed of records and fields that hold data. Tables are also called datasheets. Each table in a database holds data about a different, but related, subject.

Figure 2 Database Table

Records

Data is stored in records. A record is composed of fields and contains all the data about one particular person, company, or item in a database. In this database, a record contains the data for one customer support incident report. Records appear as rows in the database table. A record for Log ID 1201242 is highlighted in Figure 3.

Figure 3 Records appear as rows in a database table.

Fields

A field is part of a record and contains a single piece of data for the subject of the record. In the database table illustrated in Figure 4, each record contains four fields:

Log ID	A number assigned to this customer support incident for identification purposes
Operator	The code for the customer support operator who handled this incident
Resolved	A check box to indicate whether the incident was resolved
Duration	The time in seconds the operator spent on this incident

Fields appear as columns in a database table. Data from the Log ID field for five records is highlighted in the Figure 4.

Figure 4 Fields appear as columns in a database table.

Forms

A database form shows all or selected fields for one record. Forms show field names and data in an attractive and easy-to-read format. Record 1 of the database is shown in Figure 5.

Figure 5 Database Form

You can enter data in fields in a form just as you can in a table. Data has been entered in three fields in the form shown in Figure 6. Navigation buttons for moving from record to record are displayed at the bottom of the form.

Figure 6 Use navigation buttons to move from record to record

Queries

A query finds records in a database according to criteria you specify. Sometimes working with all the data in a large database is not practical. You might wish to work with just a small part or subset of the data. For example, you might want to find records for unresolved customer support incidents. A query can be used to display these records. You could construct the query to display only selected fields, such as Log ID and Resolved. A query displays only the data you request.

The table in Figure 9 shows all data that has been entered in the database. An empty checkbox in the Resolved field indicates the incident has not been resolved.

Figure 9 Table Displaying All Data

Figure 10 shows the results of a query. Only the Log ID and Resolved fields for unresolved incidents are displayed.

Figure 10 Data From a Query

Reports

A report presents data in an attractive format and is especially suitable for printing. Reports can display data from tables or queries. All or selected fields can be included in a report. Data can be grouped or sorted and arranged in a variety of ways.

Figure 11 shows a report containing data from the Complaints table. The data is grouped by resolved and unresolved incidents. The Log ID field is sorted in ascending order. Compare the format of the data in this report to the database table shown in Figure 12.

Figure 11 Database Report

Figure 12 Database Table

PRIMARY KEY 

• A column or group of columns that uniquely identify a row
• Every table should have a primary key. And a table cannot have more than one primary key. The PRIMARY KEY characteristic can be specified as part of a column definition, as in the first column of the Country table, or it can be specified as a separate clause of the CREATE TABLE statement, as in the statement that creates the Job table. The primary key in the Job table is made up of three columns: job_code, job_grade, and job_country. While a value can appear more than once in any of these columns taken individually, the fact that they are collectively a primary key means that the three values taken together cannot occur in more than one row. 

   FOREIGN KEY

       

•    A column or set of columns in one table whose values must have matching values in the primary key of another (or the same) table.
•    A foreign key is said to reference its primary key. Foreign keys are a mechanism for maintaining data integrity. In the Job table, for example, any country listed in the job_country column must also exist in the Country table. By stating that the job_country column of the Job table is a foreign key that references the country column of the Country table, you are guaranteeing this, because InterBase will return an error if a value is entered the job_country column that does not have a matching entry in the country column of the Country table.

• You can declare a constraint such as UNIQUE, FOREIGN KEY, or PRIMARY KEY either as part of a column definition, or as a table constraint following the column definitions. The syntax varies slightly depending on which you choose. See the Language Reference for details.
  You declared the primary key constraint as part of a column definition in the Country and Department tables. For the Job table, you declared the primary key, foreign key, and check constraints at the table level. Functionally, the effect is the same.

       

        REFERENCES :P (tehee...)

•   https://www.guru99.com/introduction-to-database-sql.html
•  https://www.cengage.com/school/corpview/RegularFeatures/DatabaseTutorial/db_elements/db_elements2.htm
•  http://docs.embarcadero.com/products/interbase/IBXE3Update4/QuickStart/GettingStarted/GettingStarted_Part_II/Primary_Keys_and_Foreign_Keys.htm

*All informations are strictly not owned by me. This is for educational purposes only.