Tag: key

DBMS Basics: Getting Started Guide

Getting started with Database Management System.

In this video tutorial we’re trying our best to keep everything to the minimum, and make sure not to make it look complicated for beginners.

The tables and the normalization shown in the video are just for the purpose of demonstration.

dbms-basics

DBMS is a software system which helps in managing incoming data, organizes it and provides certain ways for the data to be modified and/or extracted by the users or other programs.

Some examples of Database Management System Software include MySQL, PostgreSQL, Microsoft Access, SQL Server, FileMaker, Oracle, RDBMS, dBASE, Clipper, and FoxPro.
Also: Some Native database systems that can be connected with PHP ?

Tables Relational databases are made up of relations, commonly known as TABLES.
Relationships exists between the tables and the data are inter-related by making use of Primary and Foreign keys.

Table Columns
It has unique names.
Each column has an associated datatype.
Columns are sometimes called as fields or attributes.

Table Rows
Each row in the table represents individual data.
Rows are also called as records or tuples.

Values
Every value must have the same data type, as specified by it’s column.

Key
To identify each row uniquely.
The identifying column in a table is called as key or primary key.

Schema
Complete set of table design for a database.
It’s like a blueprint for the database.

A Schema should show the tables along with their columns, and the primary and foreign keys. Usually primary key’s are underlines and foreign keys are italicized.

Database Management System: Basics


[youtube https://www.youtube.com/watch?v=aR44FbeeFH8]

YouTube Link: https://www.youtube.com/watch?v=aR44FbeeFH8 [Watch the Video In Full Screen.]



Anomalies
Assume that we’re running an online store.
We’ve a order table.

If a person called Satish orders Apple iPad, Mac Book and a iPhone from our site.
We store his name and address and quantity of his order.

Next, Satish moves to a different place before we process the order, now we will need to update his address at 3 places!

Doing 3 times as much work. We may miss updating Satish’s address in some place, making the data inconsistent. This is called modification anomaly.

If we design our database table in this way, we’ll need to take the address of Satish each time he orders something from our online store. This way, we need to always make sure the address(data) is consistent across all the rows. If we do not take care, we may end up with conflicting data.
Ex: One row may indicate Satish to be living in Bangalore and another row may indicated Satish to be living in New York!
This scenario is called Insertion anomaly.

Once all the orders of Satish has been processed, we delete the records. This way, we no longer have Satish’s address. So we can’t send any promotional offers etc to Satish in the future. If he want’s to order something again from our online store, he need to enter his address again. This scenario is called deletion anomaly.

We could solve these anomaly problems by making use of Primary and Foreign key’s and by developing the skill/art of normalization.

Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database using the defined relationships.

Primary Key
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only ONE primary key.

Foreign Key
A foreign key is a field in a relational table that matches a candidate key of another table. The foreign key can be used to cross-reference tables.

Relationships
We’ll cover
one-one
one-many
many-many
relationships in coming video tutorials, with some real time examples.

Fetch JSON Data Using jQuery AJAX Method: getJSON

Video tutorial illustrates fetching of JSON data using jQuery AJAX method, getJSON
Since its an AJAX method, the data is fetched without the need for refresh of the browser.

HTML code
index.html

1
2
3
4
5
6
7
8
9
10
11
12

<html>
<head><title>JSON jQuery AJAX</title></head>
<body>
 
<ul></ul>
 
<button>Users</button>
 
<script src="script/jquery-1.8.1.min.js" type="text/javascript"></script>
<script src="script/my_script.js" type="text/javascript"></script>
</body>
</html>

Here we have a unordered list, without any list items in it. We’ll be fetching the list items from the JSON file using jQuery.
A button, clicking upon which we would fetch the Data inside jSON file.

JSON File
json_data.json

1
2
3
4
5
6
7
8
9
10
11
12
13
14

{
   "p1": { 
           "name": "Satish",
          "age":   25,
          "company": "Techntoip"
         },
 
 
   "p2": {
           "name": "Kiran",
          "age":   28,
          "company": "Oracle"   
         }   
}

Starts with a opening brace and ends with a opening brace.
In our example, we have taken two objects.
p1 and p2 are keys. It’s values are present inside opening and closing brace.
Each object has name, age and company info.

Also watch: Objects, Arrays: JSON Using jQuery.

jQuery Code
my_script.js

1
2
3
4
5
6
7

$("button").click( function() {
 $.getJSON( "json_data.json", function(obj) { 
  $.each(obj, function(key, value) { 
         $("ul").append("<li>"+value.name+"</li>");
  });
 });
});

Once the user clicks on the button, we invoke $.getJSON method.
First parameter we are passing is the URL of the json file. Next the call back function.

The call back function receives the jSON data which we have called as obj in our example.
Using $.each() method we iterate through all the objects present in our json file and then split them into key => value pair.

Now using the value we fetch the name present inside each objects value.

Video Tutorial: Fetch JSON Data Using jQuery AJAX Method: getJSON


[youtube https://www.youtube.com/watch?v=pe6keTE9LbE]

YouTube Link: https://www.youtube.com/watch?v=pe6keTE9LbE [Watch the Video In Full Screen.]




jSON & Database
We can generate jSON data out of a database and then fetch it using jQuery and display it on our web browser without the need for the modification of any code.
Whenever the json file or the database table data gets updated/modified, it instantly reflects on our web application.

Note:
You must open the file from a server. localhost works file too.
If you access the files directly from your computer folder, this application doesn’t work. So the files MUST be on a server.

Objects, Arrays: JSON Using jQuery

Video tutorial illustrates the basics of JSON: Create Object, Arrays, Access Elements, Modify/Update the value etc.

JSON full form: JavaScript Object Notation.

JSON is a lightweight data-interchange format.
It’s easy for humans to read and write.
It’s easy for machines to parse and generate.
So it’s more efficient at transferring data than XML.

HTML code
index.html

1
2
3
4
5
6
7
8
9
10

<html>
<head><title>JSON and jQuery!</title></head>
<body>
 
<ul></ul>
 
<script src="script/jquery-1.8.1.min.js" type="text/javascript"></script>
<script src="script/my_script.js" type="text/javascript"></script>
</body>
</html>

We have a simple HTML structure, with a unorder list.
We’ll fill the list items from the JSON data using jQuery.

JSON and jQuery
my_script.js

1
2
3
4
5
6
7
8
9
10
11
12

$(document).ready( function() {
 
 var v1 = { "name": "Satish", "age": 25, "company": "Technotip IT Solutions" };
 var v2 = { "name": "Kiran", "age": 28, "company": "Oracle" };
 
 var obj = { "obj1": v1, "obj2": v2};
 
 var v3 = { "companies": [ "Microsoft", "Apple", "Google", "Oracle" ] };
 
  $("ul").append("<li>"+obj["obj1"]["name"]+"</li>");
  $("ul").append("<li>"+v3.companies[0]+"</li>");
});

Object
An object is a set of name/value pairs. An object begins with { and ends with }. Each name is followed by : (colon) and the name/value pairs are separated by , (comma).

Array
An array is an ordered collection of values. An array begins with [ and ends with ]. Values are separated by , (comma).
Values in JSON can be string, number, array, objects, Boolean values or even null.

Fetch JSon Data From Object:
Object_name.Key;

Fetch JSon Data From Array:
Object_name.array_name[index_number];

Update JSON Data:

JSON and jQuery
my_script.js

1
2
3
4
5

 var v2 = { "name": "Kiran", "age": 28, "company": "Oracle" };
  $("ul").append("<li>"+v2.company+"</li>");
 
 v2.company = "Apple";
  $("ul").append("<li>"+v2.company+"</li>");

Would output
Oracle
Apple

Similarly, we could change the value of any key of an object or an array element.

Video Tutorial: Objects, Arrays: JSON Using jQuery


[youtube https://www.youtube.com/watch?v=MK39DQabaW8]

YouTube Link: https://www.youtube.com/watch?v=MK39DQabaW8 [Watch the Video In Full Screen.]



We could nest objects inside objects or arrays inside objects or objects inside an array etc, upto any level of complexities.
This is advantages for web applications like building menus with nested items, which are fetched from database.

Since, writing and fetching and parsing of JSON data is easy, most developers have stared using JSON over XML.