Keys in DBMS
The key is defined as the column or attribute of the database table. For example if a table has id,name and address as the column names then each one is known as the key for that table. We can also say that the table has 3 keys as id, name and address. The keys are also used to identify each record in the database table. The following are the various types of keys available in the DBMS system.
1. A simple key contains a single attribute.
2. A composite key is a key that contains more than one attribute.
3. A candidate key is an attribute (or set of attributes) that uniquely identifies a row. A candidate key must possess the following properties:
o Unique identification - For every row the value of the key must uniquely identify that row.
o Non redundancy - No attribute in the key can be discarded without destroying the property of unique identification.
4. A primary key is the candidate key which is selected as the principal unique identifier. Every relation must contain a primary key. The primary key is usually the key selected to identify a row when the database is physically implemented. For example, a part number is selected instead of a part description.
5. A super key is any set of attributes that uniquely identifies a row. A superkey differs from a candidate key in that it does not require the non redundancy property.
6. A foreign key is an attribute (or set of attributes) that appears (usually) as a non key attribute in one relation and as a primary key attribute in another relation. I say usually because it is possible for a foreign key to also be the whole or part of a primary key:
o A many-to-many relationship can only be implemented by introducing an intersection or link table which then becomes the child in two one-to-many relationships. The intersection table therefore has a foreign key for each of its parents, and its primary key is a composite of both foreign keys.
o A one-to-one relationship requires that the child table has no more than one occurrence for each parent, which can only be enforced by letting the foreign key also, serve as the primary key.
7. A semantic or natural key is a key for which the possible values have an obvious meaning to the user or the data. For example, a semantic primary key for a COUNTRY entity might contain the value 'USA' for the occurrence describing the United States of America. The value 'USA' has meaning to the user.
8. A technical or surrogate or artificial key is a key for which the possible values have no obvious meaning to the user or the data. These are used instead of semantic keys for any of the following reasons:
o When the value in a semantic key is likely to be changed by the user, or can have duplicates. For example, on a PERSON table it is unwise to use PERSON_NAME as the key as it is possible to have more than one person with the same name, or the name may change such as through marriage.
o When none of the existing attributes can be used to guarantee uniqueness. In this case adding an attribute whose value is generated by the system, e.g. from a sequence of numbers, is the only way to provide a unique value. Typical examples would be ORDER_ID and INVOICE_ID. The value '12345' has no meaning to the user as it conveys nothing about the entity to which it relates.
Tuesday, June 15, 2010
ORACLE
RDBMS--------
DATA---DATA IS A RAW FACTS.
INFORMATION----A ABOUT THE DATA IS CALLED INFORMATION.
DATABASE---
DATA + INFORMATION=DB
DBMS-----
DB+SOFTWARE=DBMS
DATA + INFORMATION+SOFTWARE
RDBMS-
RELATIONAL MODEL+DBMS
(TABLE SPACES)+DBMS
OORDBMS----
OOPS+R+DBMS
DDBMS--- DISTRIBUTED+DBMS
DATA BASE--- IT IS A COLLECTION OF DATA AND INFORMATION IN A MEANINGFULL WAY SO THAT THE RETRIVAL, PROCESSING, FETCHING OF DATA IS NOW MORE MEANINGFULL AND EFFICIENT.
DBMS--- IT IS A SOFTWARE FOR MANAGE THE DATABASE SO THAT
THE RETRIVAL, PROCESSING, FETCHING OF DATA IS NOW MORE MEANINGFULL AND EFFICIENT.
RDBMS---HERE WE MERGE RELATIONAL MODEL (TABLE SPACES) WITH DBMS.
SQL----
STANDERED QUERY LANGUAGE
STRUCTURED QUERY LANGUAGE
PRONOUNCED “SEQUEL”
IT IS DEVELOPED BY “IBM”
NOW IT IS CONTROLLED BY ANSI AND ISO (AMERICAN NATIONAL STANDERED INSTITUTION AND INTERNATIONAL STANDERED ORGANIJATION )
SQL IS COMMON FOR ALL RDBMS SOFTWARES.
LIKE---ORACLE—8P,9I,10G ETC
MS-ACCESS
SQL SERVER
DB2 ETC.
FOUR COMPONENTS OF SQL----
1. DDL(DATA DEFINATION LANGUAGE)
2. DML(DATA MANUPULATION LANGUAGE)
3. DQL—DATA QUERY LANGUAGE
4. DCL(DATA CONTROL LANGUAGE)
1. DDL---
CREATE, ALTER DROP
2. DML--- INSERT , DELETE , UPDATE
3. DQL--- SELECT
4. DCL--- GRANT , REVOKE
INITIALLY SQL HAVE ONLY FOUR COMPONENTS AND NINE COMMANDS
AFTER THAT ONE MORE COMPONENT ARE ALSO ADDED IT IS CALLED ----
5. DTL(DATA TRANJACTION LANGUAGE)
-------
COMMIT, ROLLBACK , SAVE POINT
NOTE--- SQL IS CASE INSENSITIVE LANGUAGE. SO UPPER CASE LETTERS
AND LOWER CASE LETTERS ARE TREATED AS SAME.
NOTE—SQL IS CASE INSENSITIVE AT COMMAND LEVEL . IT IS CASE SENSITIVE
AT DATA LEVEL.
DATA TYPES---
CHAR----IT IS USED FOR FIXED LENGTH
(ONE CHARACTER TAKE ONE BYTE OF MEMORY)
VARCHAR2----IT IS USED FOR VARIABLE
LENGTH
NUMBER----ALL NUMBERS. INTEGERS
AND FLOATING POINT ALSO
NAME CHAR(20);
EX---KAPIL
HERE 5 BYTES—USED SPACE
, 15 BYTES PADDING SPACE
TOTAL SIZE== 20 BYTE ARE OCCUPY
NAME VARCHAR2(20);
EX—DHARAMENDRA
11 BYTE USED SPACE , 9 BYTES ARE FREE
FOR FURTHER USE.
SO TOTAL SIZE USE = 11 BYTES ARE OCCUPY
DDL COMMANDS---
1. CREATE COMMAND---IT IS USED FOR CREATE A TABLE.
SYNTAX
CREATE TABLE TABLENAME
(
COL1 DATATYPE(SIZE),
COL2 DATATYPE(SIZE),
COL3 DATATYPE(SIZE),
COLN DATATYPE(SIZE)
);
TABLE ---ENTITY SET
COLUMN—ATTRIBUTES
ROW---TUPPLE,ENTITY
CREATE TABLE STUDENT
(
NAME VARCHAR2(20),
ROLLNO NUMBER(3),
SEM NUMBER(1),
FEES NUMBER(7,2)
);
HERE FEES NUMBER(7,2)----
MAX---99999.99
DML COMMAND—
INSERT COMMAND—
SYNTAX---
INSERT INTO TABLENAME
(COL1,COL2,…..)
VALUES
(VAL1,VAL2,….);
INSERT INTO STUDENT
(NAME,ROLLNO,SEM,FEES)
VALUES
(‘BHANU’,27,1,60000);
INSERT INTO STUDENT
(SEM,FEES,ROLLNO,NAME)
VALUES
(1,24000,19,’ANSHUL’);
INSERT INTO STUDENT
VALUES
(‘KOUSHEL’,22,1,888);
DQL COMMAND---
SELECT COMMAND
SYNTAX---
SELECT COL1,COL2,…. FROM
TABLENAME
WHERE CONDITION
SELECT * FROM TABLENAME ;
HERE * - -- METACHARACTER(ALL DATA ALL ROW + ALL COLUMN)
DML OPERATION----
DELETE COMMAND—
SYNTAX---
DELETE TABLENAME ;
DELETE TABLENAME WHERE CONDITION;
DETETE STUDENT;
ALL THE DATA OF STUDENT TABLE ARE DELETED .
BUT TABLE STRUCTURE ARE PRESENT INSIDE DATABASE.
DELETE STUDENT WHERE NAME=’BHANU’;
ONLY ‘BHANU’ DATA ARE DELETED.
DML COMMAND----
UPDATE COMMAND---IT IS USED FOR UPDATE THE DATA PRESENT INSIDE TABLE.
SYNTAX---
UPDATE TABLENAME SET ……
WHERE CONDITION…….
UPDATE STUDENT SET FEES= FEES+10000;
UPDATE STUDENT SET
FEES= FEES-10000;
UPDATE STUDENT SET SEM= SEM+1;
UPDATE STUDENT SET NAME = ‘NEHA’ WHERE NAME = ‘BHANU’;
DDL COMMAND---
DROP COMMAND===
SYNTAX---
DROP TABLE TABLENAME;
TABLE AS WELL AS DATA ALL ARE DROPPED.
DDL COMMAND---
ALTER COMMAND—WE CAN MODIFY THE STRUCTURE OF THE TABLE.
SYNTAX---
ALTER TABLE TABLENAME
ADD
NEWCOLNAME DATATYPE(SIZE);
ALTER TABLE STUDENT ADD
YEAR NUMBER(1);
CONSTRAINTS----
IT IS THE LIMITATION ON THE TABLE
WE CAN RISTRICT THE DATA INSIDE THE TABLE FOR UNPROPER HANDLLING.
TYPES---
PRIMARY KEY---DUPLICATES ARE NOT ALLOWED,NULL ARE NOT ALLOWED
UNIQUE + NOT NULL = PRIMARY KEY
NOT NULL – NULL ARE NOT ALLOWED
DUPLICATED CAN BE ALLOWED
UNIQUE -- DUPLICATES ARE NOT ALLOWED BUT NULL CAN BE ALLOWED.
CHECK--- CHECK THE RANGE OF COLUMN.
CREATE TABLE CS_STUDENT
(
NAME VARCHAR2(20) NOT NULL,
F_NAME VARCHAR2(20) NOT NULL,
SEM NUMBER(1) NOT NULL,
YEAR NUMBER(1) NOT NULL,
ROLLNO NUMBER(3) PRIMARY KEY,
FEES NUMBER(7,2) CHECK (FEES >=20000 AND FEES <=80000),
PASSPORTNO NUMBER(6) UNIQUE
);
DATA---DATA IS A RAW FACTS.
INFORMATION----A ABOUT THE DATA IS CALLED INFORMATION.
DATABASE---
DATA + INFORMATION=DB
DBMS-----
DB+SOFTWARE=DBMS
DATA + INFORMATION+SOFTWARE
RDBMS-
RELATIONAL MODEL+DBMS
(TABLE SPACES)+DBMS
OORDBMS----
OOPS+R+DBMS
DDBMS--- DISTRIBUTED+DBMS
DATA BASE--- IT IS A COLLECTION OF DATA AND INFORMATION IN A MEANINGFULL WAY SO THAT THE RETRIVAL, PROCESSING, FETCHING OF DATA IS NOW MORE MEANINGFULL AND EFFICIENT.
DBMS--- IT IS A SOFTWARE FOR MANAGE THE DATABASE SO THAT
THE RETRIVAL, PROCESSING, FETCHING OF DATA IS NOW MORE MEANINGFULL AND EFFICIENT.
RDBMS---HERE WE MERGE RELATIONAL MODEL (TABLE SPACES) WITH DBMS.
SQL----
STANDERED QUERY LANGUAGE
STRUCTURED QUERY LANGUAGE
PRONOUNCED “SEQUEL”
IT IS DEVELOPED BY “IBM”
NOW IT IS CONTROLLED BY ANSI AND ISO (AMERICAN NATIONAL STANDERED INSTITUTION AND INTERNATIONAL STANDERED ORGANIJATION )
SQL IS COMMON FOR ALL RDBMS SOFTWARES.
LIKE---ORACLE—8P,9I,10G ETC
MS-ACCESS
SQL SERVER
DB2 ETC.
FOUR COMPONENTS OF SQL----
1. DDL(DATA DEFINATION LANGUAGE)
2. DML(DATA MANUPULATION LANGUAGE)
3. DQL—DATA QUERY LANGUAGE
4. DCL(DATA CONTROL LANGUAGE)
1. DDL---
CREATE, ALTER DROP
2. DML--- INSERT , DELETE , UPDATE
3. DQL--- SELECT
4. DCL--- GRANT , REVOKE
INITIALLY SQL HAVE ONLY FOUR COMPONENTS AND NINE COMMANDS
AFTER THAT ONE MORE COMPONENT ARE ALSO ADDED IT IS CALLED ----
5. DTL(DATA TRANJACTION LANGUAGE)
-------
COMMIT, ROLLBACK , SAVE POINT
NOTE--- SQL IS CASE INSENSITIVE LANGUAGE. SO UPPER CASE LETTERS
AND LOWER CASE LETTERS ARE TREATED AS SAME.
NOTE—SQL IS CASE INSENSITIVE AT COMMAND LEVEL . IT IS CASE SENSITIVE
AT DATA LEVEL.
DATA TYPES---
CHAR----IT IS USED FOR FIXED LENGTH
(ONE CHARACTER TAKE ONE BYTE OF MEMORY)
VARCHAR2----IT IS USED FOR VARIABLE
LENGTH
NUMBER----ALL NUMBERS. INTEGERS
AND FLOATING POINT ALSO
NAME CHAR(20);
EX---KAPIL
HERE 5 BYTES—USED SPACE
, 15 BYTES PADDING SPACE
TOTAL SIZE== 20 BYTE ARE OCCUPY
NAME VARCHAR2(20);
EX—DHARAMENDRA
11 BYTE USED SPACE , 9 BYTES ARE FREE
FOR FURTHER USE.
SO TOTAL SIZE USE = 11 BYTES ARE OCCUPY
DDL COMMANDS---
1. CREATE COMMAND---IT IS USED FOR CREATE A TABLE.
SYNTAX
CREATE TABLE TABLENAME
(
COL1 DATATYPE(SIZE),
COL2 DATATYPE(SIZE),
COL3 DATATYPE(SIZE),
COLN DATATYPE(SIZE)
);
TABLE ---ENTITY SET
COLUMN—ATTRIBUTES
ROW---TUPPLE,ENTITY
CREATE TABLE STUDENT
(
NAME VARCHAR2(20),
ROLLNO NUMBER(3),
SEM NUMBER(1),
FEES NUMBER(7,2)
);
HERE FEES NUMBER(7,2)----
MAX---99999.99
DML COMMAND—
INSERT COMMAND—
SYNTAX---
INSERT INTO TABLENAME
(COL1,COL2,…..)
VALUES
(VAL1,VAL2,….);
INSERT INTO STUDENT
(NAME,ROLLNO,SEM,FEES)
VALUES
(‘BHANU’,27,1,60000);
INSERT INTO STUDENT
(SEM,FEES,ROLLNO,NAME)
VALUES
(1,24000,19,’ANSHUL’);
INSERT INTO STUDENT
VALUES
(‘KOUSHEL’,22,1,888);
DQL COMMAND---
SELECT COMMAND
SYNTAX---
SELECT COL1,COL2,…. FROM
TABLENAME
WHERE CONDITION
SELECT * FROM TABLENAME ;
HERE * - -- METACHARACTER(ALL DATA ALL ROW + ALL COLUMN)
DML OPERATION----
DELETE COMMAND—
SYNTAX---
DELETE TABLENAME ;
DELETE TABLENAME WHERE CONDITION;
DETETE STUDENT;
ALL THE DATA OF STUDENT TABLE ARE DELETED .
BUT TABLE STRUCTURE ARE PRESENT INSIDE DATABASE.
DELETE STUDENT WHERE NAME=’BHANU’;
ONLY ‘BHANU’ DATA ARE DELETED.
DML COMMAND----
UPDATE COMMAND---IT IS USED FOR UPDATE THE DATA PRESENT INSIDE TABLE.
SYNTAX---
UPDATE TABLENAME SET ……
WHERE CONDITION…….
UPDATE STUDENT SET FEES= FEES+10000;
UPDATE STUDENT SET
FEES= FEES-10000;
UPDATE STUDENT SET SEM= SEM+1;
UPDATE STUDENT SET NAME = ‘NEHA’ WHERE NAME = ‘BHANU’;
DDL COMMAND---
DROP COMMAND===
SYNTAX---
DROP TABLE TABLENAME;
TABLE AS WELL AS DATA ALL ARE DROPPED.
DDL COMMAND---
ALTER COMMAND—WE CAN MODIFY THE STRUCTURE OF THE TABLE.
SYNTAX---
ALTER TABLE TABLENAME
ADD
NEWCOLNAME DATATYPE(SIZE);
ALTER TABLE STUDENT ADD
YEAR NUMBER(1);
CONSTRAINTS----
IT IS THE LIMITATION ON THE TABLE
WE CAN RISTRICT THE DATA INSIDE THE TABLE FOR UNPROPER HANDLLING.
TYPES---
PRIMARY KEY---DUPLICATES ARE NOT ALLOWED,NULL ARE NOT ALLOWED
UNIQUE + NOT NULL = PRIMARY KEY
NOT NULL – NULL ARE NOT ALLOWED
DUPLICATED CAN BE ALLOWED
UNIQUE -- DUPLICATES ARE NOT ALLOWED BUT NULL CAN BE ALLOWED.
CHECK--- CHECK THE RANGE OF COLUMN.
CREATE TABLE CS_STUDENT
(
NAME VARCHAR2(20) NOT NULL,
F_NAME VARCHAR2(20) NOT NULL,
SEM NUMBER(1) NOT NULL,
YEAR NUMBER(1) NOT NULL,
ROLLNO NUMBER(3) PRIMARY KEY,
FEES NUMBER(7,2) CHECK (FEES >=20000 AND FEES <=80000),
PASSPORTNO NUMBER(6) UNIQUE
);
DBMS
UNIT -1 http://www.ziddu.com/download/128723...-DBMS.ppt.html
UNIT -2http://www.ziddu.com/download/128723...-DBMS.ppt.html
UNIT -3http://www.ziddu.com/download/128723...-DBMS.ppt.html
UNIT -4 http://www.ziddu.com/download/128723...-DBMS.ppt.html
UNIT -5http://www.ziddu.com/download/128723...-DBMS.ppt.html
UNIT -6http://www.ziddu.com/download/128723...-DBMS.ppt.html
UNIT -7http://www.ziddu.com/download/128723...-DBMS.ppt.html
UNIT -8http://www.ziddu.com/download/128723...-DBMS.ppt.html
Subscribe to:
Posts (Atom)