Understanding with a Scenario!

Now today il try explaining you the various terms with a help of a mini scenario the various terms in DB.
Consider a case where users can login to a system with a unique username and password.Once they login they can add a url feed in to their account.This feeD can be either categorized as public or private.If it is a private fiels only you can access it,or view it.All other people will never know that you ever included that news feed.If it is public all can view.
   In a user's home page we will have both the private and the public option.In the public option you can view the feed urls that all the other users have included and have put it in the public access mode.
  A feed can belong to one category and has multiple posts.

We will create the design to implement the above functionality.
1)Consider what has to be created as the Master Table.A master table is the table which is important in performing the functionality.In our case  USER is a table with various attributes..

CREATE TABLE USER(
u_id int,
u_name varchar(20),
u_pwd varchar(20),
primary key(u_id)
);

Here u_id is unique in the table,no 2 users can have d same u_id,so it is given under the primary key constraint.
Next master table is FEED.

CREATE TABLE FEED(
f_id int(20),
f_url varchar(20),
cat_id int(4),
access varchar(20),
u_id int(4)
primary key(f_id)
NOT NULL(f_url)
);


NOT NULL(f_url) specifies that f_url should never be left null,must always contain a value.

Now u_id from USER table is the foreign key set it as


ALTER TABLE FEED
ADD FOREIGN KEY (U_Id)
REFERENCES USER(U_Id)


In each stage the datatype is to be correctly chosen ,based on the usage.
One FEED will have multiple tags,so it is a MANY-MANY relation .So introduce a intermediate table called TAG_FEED..


CREATE TABLE TAG_FEED
(t_id int(4),
f_id int(4),
);


If you put t_id in the FEED table it will lead to redundancy as the feed name and everything will be repeated.So this is done.Now create a TAG table.

CREATE  TABLE TAG
(
t_id int(4),
t_name varchar(20)
primary key(t_id)
);

CREATE  TABLE CATEGORY

(

c_id int(4),

c_name varchar(20)

primary key(c_id)

);


Now you may ask,why can't you create a relationship table for category and feed.
Answer is we do it only when many-many relationship exists ,else it is not required.There is only 1 category for a feed,so you don't include a relationship table.


Once you create the schematic structure you have to populate the tables and use the queries to efficiently retrieve them.


Naming convention for Columns:
There are different standards for naming the column names.
I will discuss with you the oracle standard here.


Column names should be spelled out whenever possible.
If a column name should exceed 30 characters, reduce the size of the column name in this order:

• From the left of the column name, remove vowels from each word in the table name except for the first vowel of each word.
•  If the column name is still greater than 30 characters, use standardized shorthand indicators. Record this standard for consistent use.

Creating Default values for fields:

While creating a table if you want to set a default value for your table ypu can do it as 

Create table tab1

(

id int(3)

value int(4),

number int(2) DEFAULT 4

);

Now 4 is the default value for number.


MODIFY TABLE and MODIFY EXISTING COLUMN::
ALTER TABLE table_name MODIFY column_name datatype;


ADD NEW COLUMN:

ALTER TABLE table_name
ADD column_name datatype

Delete 
ALTER TABLE table_name 
DROP COLUMN column_name


RENAME COLUMN NAME OR CHANGE COLUMN TYPE:
ALTER TABLE tab_name
 change old_col new_col 
data_type


UPDATE/DELETE/TRUNCATE TABLE:
Deleting a table:
drop table tab_name;
eg) drop table emp;

Update a table:
   update table_name set field1=value1,field2=value2 where condition;

e.g.)update employee set e_name ='ttt' where e_no=6;

INSERT DATA INTO TABLE:

insert into tab_name values(val1,val2,val3);


eg)insert into emp values(1,'ron','cm');


BULK INSERT

insert into your_table (field1, field2, field3)
values 
  (value1_1, value1_2, value1_3), 
  (value2_1, value2_2, value2_3), 
  (value3_1, value3_2, value3_3)

insert into table1 (no, name, desg)

values 
  (1, 'qqq', 'cm'), 
  (2, 'www', 'acm'), 
  (3, 'ttt', 'dce');

- Sourcebits University

Cloud Computing

www.sourcebits.com