Small set of information becomes data, this set of information helps make decision. Data is always some useful information.
Place where you store the data. Database represents some aspect of the real world called "miniworld". A database is designed, built and populated with data for a specific purpose. It has intended group of users and some preconceived applications in which these users are interested.
In other words, a database has some source from which data is derived, some degree of interaction with events in the real world and an audience that is actively interested in the contents of the database.
Database can also be defined as collection of one or more tables.
Ex: Mobile, human brain etc
Is a program that stores retrieves and modifies data in the database on request.
Study of different techniques of design, development and maintenance of the database
These types are based upon their management of database structures. Types of DBMS are entirely dependent upon how the database is structured by that particular DBMS.
A dBMS is said to be hierarchical if the relationships among the data in the database are established in such a way that one data item present as the subordinate of another one.
Direct relationships exists between any 2 records that are stored consecutively. The data structure tree is followed by DBMS to structure the database. No backward movement is possible or allowed.
Ex: Dbase, Foxpro
A DBMS is said to be in a network DBMS if the relationships among data in the database are the type many to many. Relationships among many to many appears in the form of a network.
Ex: Pascal, Cobol, Fortron
A DBMS is said to be a relational or RDBMS if the database relationships are treated in the form of a table. A table is composed of rows and columns is used to organized the database and its structure and is actually a 2 dimension array in the computer memory.
Ex: Oracle, Sybase, Informix, Sql Server, Microsoft Access
Logical association of any 2 tables created to preserve the data integrity is called as Relationship.
Once the relationship is created, one table becomes master and other becomes child. Whatever we enter in child table has to be there in master table, otherwise the child table record will be rejected.
1. One to One:
One department is headed by one person. Only one person can head a department.
One to Many:
One salesman can serve in only one city but a city can have many sales man.
Many to Many:
A teacher can teach many students and a student can many teachers.
A transaction usually means a sequence of information exchange and its related work.
A transaction is any one execution of a user program in a DBMS. Transaction is either performed entirely or not preformed at all. Transaction should not be interfered by any other transaction. Changes made by a
transaction should be permanently committed. Database should be consistent before and after any transaction.
Two types --> Implicit and Explicit
Implicit --> Insert, Update, Delete
Explicit --> Programmed to commit like create and drop
Table: Collection of rows and columns. Two dimensional structures with rows and columns. A table is also called as an entity.
A column is also called as an attribute or a field.
A row is also called as a record or a tuple.
A cell is the intersection of row and a column.
Constraints: These are the restrictions or conditions that are used on the columns of the
table to preserve the data correctness.
Types: NOT NULL(NOT BLANK), Unique, Primary Key, Foreign Key, Check
NULL --> is neither zero or blank space. It is used to represent empty values.
1. NOT NULL: This ensures that atleast some value should be present for an attribute. Can have more than one not null constraints on a table.
Ex: Name cannot be left blank/NULL in a table.
2. Unique: It checks for duplicate values. A unique column can have multiple null values
Ex: EMPLOYEEID/MOBILE#/MAILID should/could be unique and Not BLANK/NULL.
3. Primary Key: is used for identifying a record uniquely in a table. It is the combination of NOT NULL and UNIQUE constraints. You can have only one Primary Key is allowed per table.
Ex: EmployeeID or Mobile/ MailID can be chosen as Primary key.
The columns that are eligible to become PK are called Candidate Keys. A column
which is eligible to become PK but not chosen as PK is called Alternate key.
CK = PK + AK
AK = CK - PK
If EmployeeId is chosen as primary key then, EmailId and Mobile# are considered as Alternate Keys. Together EmployeeId, EmailId and Mobile# are considered as Candidate Keys.
When PK is created out of more than one column then its called as composite PK.
4. Check: Check is used for enforcing some additional conditions with respect to Business requirement.
Ex: SAL > 3000, AGE > 14
5. Foreign Key: It is a referential integrity constraint which creates the relationship between the tables. To create a FK in a child table, master table should have PK on the common column.
Composite PK in master table is used as composite FK in child table.
Foreign key can take both NULL and duplicate values. There can be more than one FK per table.
Ex: Deptno from DEPT table to Deptno in Employee table.
Here in the above picture,
1 --> Row or a Record or a Tuple
2 --> Column or a Attribute or a Field
This is also a primary key from the examples we have discussed so far
3 --> Its a column with a NOT NULL constraint and Check constraint to be > 0
4 --> Its a column, having referential integrity constraint called as Foreign key
5 --> Its a CELL, intersection between row and a column having NULL value
6 --> Its a CELL, having some value
Here in the above picture,
1 --> Row or a Record or a Tuple
2 --> Column or a Attribute or a Field
This is also a primary key from the examples we have discussed so far
3 --> Its a column with a NOT NULL constraint and Check constraint to be > 0
4 --> Its a column, having referential integrity constraint called as Foreign key
5 --> Its a CELL, intersection between row and a column having NULL value
6 --> Its a CELL, having some value
Comments
im just done with 3 classes of SQL :) .. d way u r communicating n presenting slides is awesome, being a beginner feeling very confident to learn dis subject. :) blog is helping a lot :) :)
what do u mean by candidate key?
and also what is difference between primary key and candidate key?
In simple words any attribute of an entity which is NOT NULL and UNIQUE can be called as CANDIDATE Key.
Out of these Candidate keys you can choose only one attribute of an entity as a Primary Key.
If you were to choose 1 Primary Key out of 3 Candidate Keys then other remaining Candidate Keys are called as Alternate Keys.
This can also be written in the form of a formula,
CandidateKey(CK) = PrimaryKey(PK) + AlternateKey(AK)
Hope this is clear.
please reply soon.....
You should first look at the design and understand what data you want to store in your database. Once this is done then you should next proceed to ER diagram relating these data objects and their attributes.
Please go through my presentations and let me know what that you are not able to relate.
Sorry for the delayed response.
Mithun
This is my second SQL class at Banaswadi. I like the way of explaining the subject with simple examples, so that all can understand the topics easily:)
Also making groups and giving presentations to other groups will help a lot....
Mithun
Why do we classify the things in tables as PK,CK and AK and What is the use of it? Am little bit confused about these things..
It is not mandatory to identify CK,PK and AK. It is considered as best practice to have a PK for an entity and to have an understanding of CK and AK.
Regards,
Mithun
Do we have existing tables in online oracle database, so that we can start from 'SELECT' statement?
Mona
Yes you have the same tables even on APEX(Online Database).
Mithun
The classes you conduct on SQL are simple to understand and even makes the non computer students feel at comfort.
But would like to request / suggest that the blog you post could have been better if it had different pages for separate topics rather than all in one. As it is not so comfortable to scroll all the way down to find the necessary topics.
Thank you Sreenivas
Thanks for you comments and suggestions.
I have already modified the contents so that you can easily find it on the right right below Qspiders link.
Have already seperated questions.
Will surely create articles based on the topics.
Regards,
Mithun
i had created table "emp2" and renamed as "emp2%" and called that "emp2%" table...but my answer getting invalid character...please help me
Create table as follows,
create table "emp2%" (columns);
while selecting use,
select * from "emp2%";
You should use double quotes if you have any special character in your table name.
Regards,
Mithun
I'm Loganathan...I just attend the SQL classes for 3 days.
Your teaching was awesome sir..
I attended Your class,it is very interesting and helpful,thanks for sharing knowledge sir.
I m not able to find the E-R Diagrams slides in this blog,please let me know where it presents otherwise plz upload them sir.
thank u.
I have put the slides for you.
Pls check.
Mithun
what is meant by foreign key? plz explain with example sir
This is my first SQL class. I like the way of explaining with simple examples like car-engine etc. I studied dis topics in Engg, so i gt refresh the SQL.
I'm have joined newly to Quality Spider's.
I have attended 3 classes of yo's till now. It was extraordinary.
I mean your expressing skills are fantastic. Even a non-Tech guy can understand so clearly.
I would also ask you few things that i'm looking for complete SQL Enotes. So that i can get a depth view of it. So can you guide me in getting a best of one sir.
With regards, TEJ G.K
I'm also looking for few notes on DBMS & RDBMS which has complete in depth overview of it.
Thank in advance :-)
With regards,
Tej G.K
I haven't understood ER model. Can you provide me the link which as brief information about it.
Thanks!!
I tried installing the Oracle 10g express edition.
Its downloaded successfully and installed also, But the problem is in connecting to the db.
I get this error: Firefox can't establish a connection to the server at 127.0.0.1:8080.
When i try to launch it web browser.
I need to login so that i can SQL * Plus.. :( Just stuck up here from hours.
Please help me on this as to what have i done wrong.
Thanks in advance!
I HAV MISSED THE STARTING CLASSES SO CAN U PLZ TEL ME WHICH VERSION OF DATABASE SHLD I DOWNLOAD AND MINE IS WINDOWS 7 SOFTWARE SO PLZ TEL ME HOW TO DOWNLOAD FOR THIS
FOREIGN KEY
In the context of relational databases, a foreign key is a referential constraint between two tables.[1]
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.
The foreign key identifies a column or set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. The columns in the referencing table must reference the columns of the primary key or other superkey in the referenced table. The values in one row of the referencing columns must occur in a single row in the referenced table. Thus, a row in the referencing table cannot contain values that don't exist in the referenced table (except potentially NULL). This way references can be made to link information together and it is an essential part of database normalization. Multiple rows in the referencing table may refer to the same row in the referenced table. Most of the time, it reflects the one (parent table or referenced table) to many (child table, or referencing table) relationship.
The referencing and referenced table may be the same table, i.e. the foreign key refers back to the same table. Such a foreign key is known in SQL:2003 as a self-referencing or recursive foreign key.
A table may have multiple foreign keys, and each foreign key can have a different referenced table. Each foreign key is enforced independently by the database system. Therefore, cascading relationships between tables can be established using foreign keys.
Regards,
Mithun
Thanks you.
Mithun
For SQL Notes refer to,
Basics Notes By Mithun Ashok
and
SQL 10g Reference Guide by Oracle
Regards,
Mithun
Follow the link below on more detailed review on DBMS and RDBMS,
References to RDBMS Concepts
Regards,
Mithun
For notes on ER diagram refer to my presentation on Introduction to DBMS.
Go to Control Panel --> Admin Tools --> Services.
Check if all the services for Oracle has been started. If not then start these services and you should be able to login to iSQLPLUS
Regards,
Mithun
You can download Oracle 10g Release2 for windows 32 bit.
Regards,
Mithun
sir i tried downloading database 10g but its showing errors n telling tat i shld download JRE version 1.3.1 first.....so can u plz tel me wher i can get this software from
You are the right person to teach well understandable manner im thanking you to give such confidance .sir one more thing it is not working properly im getting confusion plz suggest me sir in all the way.
sir i hav missed my starting 5 classes so im little confused with the basics so can i get ur notes that u giv in the class of the first 5 days....can u plz mail them to me.......my email id is mahalaxmi.masm@gmail.com
Im from Qspiders. I have few questions:
1. Display all employees who are not clerk or Analyst?
I tried following none of the give me result i want:
select * from emp where job not like 'Clerk' or 'Analyst';
select * from emp where job not in ('Clerk', 'Analyst');
2. display employees whose sal > 2000?
Here again i tried this ...
select ename, sal "sal+comm" from emp where sal > 2000 and comm is not null;
select ename, sal "sal+comm" from emp where sal > 2000;
Please suggest..
I guess you have not downloaded the correct software. Download 10g Release 2 enterprise edition.
Mithun
Thanks for your comments.
Let me know what is not working properly and I will surely help you resolve your problem.
Regards,
Mithun
Please refer to my presentation and notes under Qspiders Page.
Regards,
Mithun
Sure will take care.
:-) Mithun
Am sure you would have got the solution in the class.
Let me know if you still have any questions.
Regards,
Mithun
sir suppose we already had a table called emp10 and we have stored a values for the first row,and then added a new column say 'comments' then how to store the first value for column 'comments'
Thanks for your comments.
You can update the first row of the column comment using update statement. Check the primary key of first row and use that in the condition.
Regards,
Mithun
Let me know what you did not understand in ER diagrams.
Regards,
Mithun
i have been attending from past 3days.Being a student of electronics background its everything new to hear but the way you teach is giving me hope to move further.its a bit confusing with ER diagrams but am working out on it....
i have been attending from past 3days.Being the student of electronics backgroung ite everythng new to hear but the way you teach is giving me hope to move further.Its a bit confusing with ER diagrams but am working out..
Regards,
Mithun
One of my friend suggested me to go through WAMP server but i didnt get how it is useful for database testing. In realtime database testing is nt done the way you teached us? So Plz send ur ans to my mail: arif.sindagikar@gmail.com
thank u.
One of my friend suggested me to go through WAMP server but i didnt get how it is useful for database testing. In realtime database testing is nt done the way you teached us? So Plz send ur ans to my mail: arif.sindagikar@gmail.com
thank u.
can a fk column have more than one null value??
I missed DDL(create,insert...) statement class,how can i get that notes....i searched here,but i couldnt get that...?
dis is NADAF I hd lost my vb script notes
cud u pls tel me wher ican get notes regarding vb instructions, excel automation..........
will you tell the difference between SQL server and Oracle server.
A suggestion:
Being new to the concepts of Database, DBMS, SQL etc., it was quite confusing to go just by definitions and understand them. A graphical representation would add more clarity similar to this link: http://www.fellstrider.com/358infosysarch2.html
Your slides are awesome ...Thanks
I could not attend your yesterday's class in the evening (7-9 pm) (which is the second class) about the constraints and foreign keys due to my work in office and could not make it to the class. Just want to know if I can continue with the class without understanding the second class or not.
Naveen
please consider this Dept Relation
DeptId Deptname DeptHead DeptLocn
10 Testing 1 Bang
10 Testing 1 Hyd
20 HR 2 Bang
30 Sales 3 Bang
20 HR 2 Hyd
My question is
a) In this scenario since none of columns in the above table have uniqueness criteria, do we need to go for composite PK?
what are the possible composite keys?
if we had this senario, is there a need of composite key?
DeptId Deptname DeptHead DeptLocn
10 Testing 1 Bang
20 Testing 1 Hyd
30 HR 2 Bang
40 Sales 3 Bang
50 HR 2 Hyd
THANK YOU in advance...
This is samar one of the evening class student ... i have doubt in ck and pk can you please describe it briefly..
i am bit confused about rowid and rownum.are they just defined for Oracle database or for any database??
thanks so much sir
can we create a table without primary key plz explain??
sir if at'll i want to change or get the last row at the first ,second or 3rd row to 7th ...in the table ...how to do this?interchange of row......example i want 8th row to be in 2nd?
sir if at'll i want to change or get the last row at the first ,second or 3rd row to 7th ...in the table ...how to do this?interchange of row......example i want 8th row to be in 2nd?
pasword " the account is locked" so can give the ans plz..........
pasword " the account is locked" so can give the ans plz..........
wht is the query to find the employees having only one L in ename
very good morning..
sir pls give brief description about LOCKs.
Currently am working and i got offer to migrate IT(Development Team) in our company itself after written test, But In our office they are using MYSQL.
Could you please let me know the difference b/w MySQl and SQL+
I am Currently Working as a trainee in a MNC , Current i am undergoin fundamental training. Our Trainee manager has given us option to choose the stream .I would like to have your suggestion before i take my step forward. I have given two streams in which one i have to choose. 1) Production support in Oracle/PlSQL and 2) Software Testing(manual ) for intial stage. I am intrested in both , I am very much confused what stream i should choose. Being a disciple of your blog i am seeking for your suggestion .
Kindly suggest me which stream is better for future . Waiting for your valuable response
create table emp2 as select * from emp1
here 1=2;
why we used 1=2 ????
create table emp2 as select * from emp1
here 1=2;
why we used 1=2 ????
I am your old student. can i get oracle 10g software in ol?
what is the command to view all the tables in database
I have some problem in emp table. May be this is not a problem, My query is that I changed the constraint of empno column to NUMBER(6) (actually it is NUMBER(4)). I am not able to reduce it to NUMBER(4). If u can change that can you tell me how.
I am from Qspiders BCM 18 batch which recently finished on 24 November.
INDEED IT WAS HAPPY LEARNING WITH U SIR.
I have some problem in emp table. May be this is not a problem, My query is that I changed the constraint of empno column to NUMBER(6) (actually it is NUMBER(4)). I am not able to reduce it to NUMBER(4). If u can change that can you tell me how.
I am from Qspiders BCM 18 batch which recently finished on 24 November.
INDEED IT WAS HAPPY LEARNING WITH U SIR.
ReplyDelete
can you tell me the difference between varchar and varchar2??
can you plz tel me the difference between varchar and varchar2??
I'm confused between GROUP BY & ORDER BY?
Reply
I'm confused between GROUP BY & ORDER BY?
reply
Thank you sir
What is the meaning of master table and child table in FK
i want to create a relationship between two tables.How can i do it can you give me with an example
Regards,
Suhail
I am a student of your SQL class. Wanted to know command to set LINES size and PAGES size permanently in ORACLE.