Languages:
- Sql server: TSQL
- Oracle: SQL, PL/SQL
TOOLS:
- SQL server: SSMS, SSBIDS
- Oracle : SQL*PLUS, TOAD
Different Types of Keys in RDBMS:- [components of RDBMS]
The different keys are
- Primary key
- Composite key
- Candidate key
- Super key
- Foreign key.
PRIMARY KEY in SQL Server with example :-
What is Primary Key in SQL Server?
- A column that uniquely identifies each record in table is called “Primary key”.
- Example is Student-ID.
STUDENT TABLE:
|
SID
|
SNAME | GROUP |
Where SID is PRIMERY KEY
COMPOSITE KEY in SQL Server :-
What is Composite Key in SQL Server?
Sid Cid Sname Cname Doc
1 10 a java 10
1 11 a .net 15
2 10 b java 10
- A combination of columns uniquely identifying the record then it is called “composite key”. In the above set of rows Sid and Cid are the Composite keys
CANDIDATE KEY in SQL Server :-
What is Candidate Key in SQL Server?
- Vehicle Table :
Vehicle no eng. No Vname Price Model
- Attributes which are eligible for Primary Keys are called “Candidate key”.
- Candidate keys : vehno,eng.no
- Primery key :vehno
- Alternate key :engno.
- A primery key is always Candidate key but
A candidate key may or may not be a Primary key.
SUPER KEY in SQL Server :–
What is Super Key in SQL Server?
- The combination of columns uniquely identifiers the record is called “Super key”.
Super Key Example:-
Dep.table
Dno dname loc – s.k.1
Dno+dname – s.k.2
Dno+loc – s.k.3
Dname+loc – s.k.4
Primary key = min (super key)
So, hence primary key is dno & dname.
Primary key dno & alternate key dname.
FOREIGN KEY in SQL Sever:-
What is Foreign Key in SQL Server?
- It is used to establish relationship b/w two tables is called “Foreign key”.
- A Foreign key is a primary key in some other table.
Foreign Key Example :-
Emp Dep
Empno ename sal d.no D.no Dname Emp.no
1 A 5 10 10 sales 12 n’t automic
2 B 6 20 20 alcs 25
3 C 7 30 30 stones 367
- If the relationship is one to many always the F.K is should be added to the table participating on “many-side of the relationship”.
Example :-
Customer Order
Cid cname order.no ord.date price
- 1 to many ,so primary key can be added to order table only.
- If relationship is one to one , the foreign key can be added to any table.
Dept mgr
D.no Dname mgrno Name D.no
It is one to one.
Many –to-Many Relationship in sql with example :-
Example :
- Supp. Cust
Eid Ename Cid Cname
11 A 1 X
12 B 2 Y
13 C 3 Z
SUPP-CUST
Sid Cid
10 1
10 2
11 1
- RDBMS Doesn’t support Many-to-Many relationship –
In RDBMS, Many-to-Many relationship will be splitting to two-one-two many Relationship.
DATABASE DESIGN in SQL Server:-
Steps followed in database development:
- Analysis
- Design
- Development
- Implementation
- Maintenance.
- Db designed by “DB Designer(or) DB Architect”
- The DB designer designed the db by using two techniques,
(i) ER model: Top-down Approach
(ii) Normalization: Bottom up Approach
- DB developed by “DB Developer”
- The developer the will develop the database by using “RDBMS TOOL” i:e sql server (or) oracle.
ER MODEL in SQL Server :–
- ER stands for “entity relationship” model
- It is a “Graphical representation of data”.
- The diff. element of ER model
- Entities
- Entity sets
- Attributes
- Relationships
ENTITY in SQL Server with Example:–
- It is an object.
Ex: Employee,customer,bank A/c,course etc.
ENTITY SETS in ER Diagram with Example:–
- Collection of entities that share common properties is called “Entity Sets”.
Example —
Symbol: rectangle represents the entity set.
Example: Employee
Examples of ATTRIBUTES in Database:–
- Attributes one the properties of entity.
Example:–
Empid ename job sal
These are all properties of employee.
- Attributes are indicated by symbol is “Oval or Ellipse”.
RELATIONSHIPS in SQL Server :–
- A relationship is a association b/w entities of entity sets.
- The relationship is indicated by symbol is “dimension”
Types of Relationships in SQl Server :–
- 1 to 1
- 1 to many
- Many to Many
- ER model can be used to at “Design part”
Difference Between ER model and Relational Model
ER Model Relational Model
Entity sets Here,entity sets are converted to “Tables”
Attributes “Fields”
Relationships “Foreign keys”