Search for notes by fellow students, in your own course and all over the country.
Browse our notes for titles which look like what you need, you can preview any of the notes via a sample of the contents. After you're happy these are the notes you're after simply pop them into your shopping cart.
Document Preview
Extracts from the notes are below, to see the PDF you'll receive please use the links above
ER Model to Relational Model
The ER Model offers a great overview of entity-relationship that
is easier to understand when it is represented in diagrams
...
Although we are able to
build an approximation schema, not all ER constraints can be
imported into the relational model
...
While some of them are
automated, others are manual
...
ER schematics often include −
Entity and its attributes
• Relationship, which is association among entities
...
Mapping Process (Algorithm)
• Create table for each entity
...
Declare primary key
...
Mapping Process
• Create table for a relationship
...
• If relationship has any attribute, add each attribute as
field of table
...
• Declare all foreign key constraints
...
Mapping Process
• Create table for weak entity set
...
• Add the primary key of identifying entity set
...
Mapping Hierarchical Entities
ER specialization or generalization comes in the form of
hierarchical entity sets
...
• Create tables for lower-level entities
...
• In lower-level tables, add all other attributes of lowerlevel entities
...
• Declare foreign key constraints
...
It is based
on relational algebra and tuple relational calculus
...
SQL includes both data definition and data manipulation
languages
...
Data Definition Language
SQL uses the following set of commands to define database
schema −
CREATE
Creates new databases, tables and views from RDBMS
...
For example−
Drop object_type object_name;
Drop database tutorialspoint;
Drop table article;
Drop view for_students;
ALTER
Modifies database schema
...
Data Manipulation Language
SQL is equipped with data manipulation language (DML)
...
DML is responsible for all forms data
modification in a database
...
SELECT/FROM/WHERE
• SELECT − This is one of the fundamental query
command of SQL
...
It selects the attributes
based on the condition described by WHERE clause
...
In case more than one relation
names are given, this clause corresponds to Cartesian
product
...
For example −
Select author_name
From book_author
Where age > 50;
This command will yield the names of authors from the
relation book_author whose age is greater than 50
...
Syntax−
INSERT INTO table (column1 [, column2, column3
...
])
Or
INSERT INTO table VALUES (value1, [value2,
...
Syntax −
UPDATE table_name SET column_name = value [,
column_name = value
...
Syntax −
DELETE FROM table_name [WHERE condition];
For example −
DELETE FROM tutorialspoints
WHERE Author="unknown";
DBMS - Normalization
Functional Dependency
Functional dependency (FD) is a set of constraints between two
attributes in a relation
...
, An, then those
two tuples must have to have same values for attributes B1, B2,
...
Functional dependency is represented by an arrow sign (→) that
is, X→Y, where X functionally determines Y
...
Armstrong's Axioms
If F is a set of functional dependencies then the closure of F,
denoted as F+, is the set of all functional dependencies logically
implied by F
...
Reflexive rule − If alpha is a set of attributes and beta
is_subset_of alpha, then alpha holds beta
...
That is adding attributes
in dependencies, does not change the basic
dependencies
...
a
→ b is called as a functionally that determines b
...
Trivial FDs always hold
...
• Completely non-trivial − If an FD X → Y holds, where x
intersect Y = Φ, it is said to be a completely non-trivial
FD
...
Managing a database with anomalies is next to impossible
...
For example, when we try to update
one data item having its copies scattered over several
•
•
places, a few instances get updated properly while a
few others are left with old values
...
Deletion anomalies − We tried to delete a record, but
parts of it was left undeleted because of unawareness,
the data is also saved somewhere else
...
Normalization is a method to remove all these anomalies and
bring the database to a consistent state
...
This rule defines that all the attributes in a relation must
have atomic domains
...
We re-arrange the relation (table) as below, to convert it to First
Normal Form
...
Second Normal Form
Before we learn about the second normal form, we need to
understand the following −
•
•
Prime attribute − An attribute, which is a part of the
candidate-key, is known as a prime attribute
...
If we follow second normal form, then every non-prime attribute
should be fully functionally dependent on prime key attribute
...
We see here in Student_Project relation that the prime key
attributes are Stu_ID and Proj_ID
...
e
...
But we find that Stu_Name can be identified by Stu_ID and
Proj_Name can be identified by Proj_ID independently
...
We broke the relation in two as depicted in the above picture
...
Third Normal Form
For a relation to be in Third Normal Form, it must be in Second
Normal form and the following must satisfy −
•
•
No non-prime attribute is transitively dependent on
prime key attribute
...
We find that in the above Student_detail relation, Stu_ID is the
key and only prime key attribute
...
Neither Zip is a superkey
nor is City a prime attribute
...
To bring this relation into third normal form, we break the
relation into two relations as follows −
Boyce-Codd Normal Form
Boyce-Codd Normal Form (BCNF) is an extension of Third Normal
Form on strict terms
...
In the above image, Stu_ID is the super-key in the relation
Student_Detail and Zip is the super-key in the relation ZipCodes
...
DBMS - Joins
We understand the benefits of taking a Cartesian product of two
relations, which gives us all the possible tuples that are paired
together
...
Join is a combination of a Cartesian product followed by a
selection process
...
We will briefly describe various join types in the following
sections
...
The join condition is denoted by
the symbol θ
...
, An) and (B1,
B2,
...
Theta join can use all kinds of comparison operators
...
Std = Subject
...
The above example corresponds to equijoin
...
It does not
concatenate the way a Cartesian product does
...
In addition, the attributes must
have the same name and domain
...
Courses
CID
Course
Dept
CS01
Database
CS
ME01
Mechanics
ME
EE01
Electronics
EE
HoD
Dept
Head
CS
Alex
ME
Maya
EE
Mira
Courses ⋈ HoD
Dept
CID
Course
Head
CS
CS01
Database
Alex
ME
ME01
Mechanics
Maya
EE
EE01
Electronics
Mira
Outer Joins
Theta Join, Equijoin, and Natural Join are called inner joins
...
Therefore,
we need to use outer joins to include all the tuples from the
participating relations in the resulting relation
...
Left Outer Join(R
S)
All the tuples from the Left relation, R, are included in the
resulting relation
...
Left
A
B
100
Database
101
Mechanics
102
Electronics
Right
A
B
100
Alex
102
Maya
104
Mira
Courses
A
HoD
B
C
D
100
Database
100
Alex
101
Mechanics
---
---
102
Electronics
102
Maya
Right Outer Join: ( R
S)
All the tuples from the Right relation, S, are included in the
resulting relation
...
Courses HoD
A
B
C
D
100
Database
100
Alex
102
Electronics
102
Maya
---
---
104
Mira
Full Outer Join: ( R
S)
All the tuples from both participating relations are included in
the resulting relation
...
Courses HoD
A
B
C
D
100
Database
100
Alex
101
Mechanics
---
---
102
Electronics
102
Maya
---
---
104
Mira