Database
A database is a place where data are stored in columns, and rows in a table, just like a spreadsheet, a database consist of one or several tables. A table consists of many columns, known as fields, and each field consist of many rows, called records. Data stored in a table, can be retrieved, updated or even deleted through executing a set of instruction to a database. This set of instruction is what we call SQL statement.
When the first database was created, its design was not in perfect form. The model of the design was to store data in a single stream of bytes. This is known as a flat-file database. A flat-file database is inefficient, given the lack of scalability and storage capacity.
Relational Database
A relational database model is designed to contain several tables that can be joined together via the use of common related fields. The link of two or more tables is achieved through the use of primary key and foreign keys, known as a relationship. The advantage of a relational database over a flat-file database is its ability to store data in different tables, with minimal duplication.
Primary Key (PK)
A primary key is an identifier that uniquely identifies a record stored in a table. By assigning a primary key to a particular field in a table, we can uniquely retrieve, update or delete certain records from a table. A primary key, can relate to other primary key created in another table. A primary field cannot be null, means it must be populated with value. A user cannot insert a value in a primary field twice, as a primary field is a unique field, and it cannot contain two rows of records with the same value.
Foreign Key (FK)
A primary key is known as a foreign key, if it links to a primary key of another table. A value entered in a foreign field, should be the same value entered in the primary field of another table. You could not enter a value as a foreign key that are not initially entered or exist in a primary field of another table.
Normalization Concept
Normalization is a guideline that shows the method or way of designing a well-structured database. Under normalization methodology, we can restructure database by simply following the below main three steps:
First Normal Form
Second Normal Form
Third Normal Form
First Normal Form
In the first normal form, a database designer is required to identify the type and group of data that each data item will fall in, and then decide which data should be used as the basis of creating individual table to contain them. Let’s take an example of creating a phone book database. A phone book, generally consist of name, date of birth, address, phone number, place of work, and other details. We know that our main item data is Name and Location. So, we create a table called Name_T to store the name, date of birth, and phone number of each individual. We also create a table called Location_T to hold data on address and place of work. Our next task, under the first normal form, is to eliminate repeating groups of data. We know that, under the Name_T table, it is very likely that two or more person, may share the same name, and as for the address, it is possible that more than one person could be staying in the same place, therefore we could end up typing the same name or address twice in each of the tables. In order to ensure there is no duplication of data in each table, we need to identify a particular field to be a primary key. A primary key is a unique identifier that identifies particular records in a table, and it ensures that a value entered in its field can never be re-entered twice. This enforces data integrity and consistency. In our Name_T table, we assign the field Name to be a primary field, and set it as primary key, and change the fieldname Name to Name_ID. We then, set the field address as a primary key, changing its fieldname address to Address_ID. We then create another table called, Customer Details_T table to store the Name_ID and Address_ID field. By assigning the Name and Address as a primary key, we can now have more than one record that shares the same name and location.
Second Normal Form
No other non-key field is independent of the primary key. We must ensure that all existing fields in a table must depend on the primary key. We know that the Name_T table, contains the date of birth field, and it is possible that more than one person has the same date of birth, thus, we need to create a separate table specifically to hold the date of birth data, and we rename the date of birth to DOB_ID, and set it as a primary key.
Third Normal Form
When we reached the second normal form, we almost complete normalizing our database structure. In the third normal form, it’s basically ensuring that all non-key fields are now fully dependent on the primary key. We identify one more field that brings us to our third normal form. We could have more than one person working in the same place, thus, it is logical to create the field place of work as a separate table. We rename the existing fieldname place of work to POW_ID, giving reference to a primary key created in a new table called Place_of_Work_T table.
What is SQL?
SQL, an abbreviation for Structured Query Language, is a communication language. When you go to an auto-teller machine, to withdraw money, you need to press certain button, to instruct the machine what to do, when you go to the Internet, you use your keyboard or mouse to navigate or search for your favorite website, you are telling your machine what to do. All this are possible with the help of SQL. It is a universal language that receives instruction from a “front-end” object that will then compile and send the instruction back to a “back-end” object. The front-end object is an application tool, such as VB, C++, and the back-end object, is a database system, that helps to store data. The instruction received from a front-end application, generally perform the following task:
Select existing data
Insert new data
Update existing data
Delete existing data
SQL is a language established by the American National Standards Institute (ANSI), a standard committee that consists of database experts from industry and software vendors. Thus, SQL is a universal and open language, meaning that, it is not owned by any industry.
Transact-SQL
DBMS, or database management system, is a software product that holds and store data. A number of famous DBMS worth noting, are, IBM DB2, MySQL, Sybase Adaptive Server, Oracle, Microsoft Access, and Microsoft SQL Server. These various DBMS, would have their own type of SQL version, generally differ in terms of syntax and features, but, they all complied to the American National Standards Institute (ANSI) SQL Standard.