Saturday, April 29, 2023
Thursday, April 27, 2023
Tuesday, April 25, 2023
E. F. Codd’s 12 rules in DBMS
E. F. Codd’s 12 rules in DBMS
Codd's
twelve rules are a set of thirteen rules (numbered zero to twelve) proposed by
Edgar F. Codd, a pioneer of the relational model for databases, designed to
define what is required from a database management system in order for it to be
considered relational, i.e., a relational database management system (RDBMS).
Rule
0: Foundation Rule
Any
relational database management system that is propounded to be RDBMS or
advocated to be a RDBMS should be able to manage the stored data in its
entirety through its relational capabilities.
Rule
1: Rule of Information
The
information can be represented in one and only one way i.e., tables (also known
as Relations). This rule emphasizes the fact that the information can be stored
in rows and columns, whenever the data has to be stored or represented, it has
to be in tabular form only.
Rule
2: Rule of Guaranteed Access
Every
data entity which is atomic in nature should be accessed logically by using a
right combination of the name of table, primary key represented by a specific
row value and column name represented by attribute value.
Rule
3: Rule of Systematic Null Value Support
Null
values are completely supported in relational databases. They should be
uniformly considered as ‘missing information’. Null values are independent of
any data type. They should not be mistaken for blanks or zeroes or empty
strings. Null values can also be interpreted as ‘inapplicable data’ or ‘unknown
information.’.
Rule
4: Rule of Active and online relational Catalog
RDBMS
should maintain data dictionary tables to keep track of current state of the
database. These are special tables which keep track of the current state of the
database. These tables contain information about table definitions, views,
column definitions and other types of database object updated automatically
when changes are made to database.
Rule
5: Rule of Comprehensive Data Sub-language
RDBMS
should have comprehensive transactional data definition language, data
manipulation language & data control language. All the operations on
database should be supported by the data language which is a part of the
package.
Rule
6: Rule of Updating Views
One
can feel that any view can be updated, but in real practice, one cannot update
all the views as some views are based on aggregating and virtual columns.
Rule
7: Rule of Set level insertion, update and deletion
A
single operation should be sufficient to retrieve, insert, update and delete
the data.
Rule
8: Rule of Physical Data Independence
A
change in the storage strategy should not affect the performance of data.
Rule
9: Rule of Logical Data Independence
The
data should be independent of logic involved in programming, in case the
database design is changed then the programs should be independent of these
changes.
Rule
10: Rule of Integrity Independence
Integrity
constraints should be available and stored as metadata in data dictionary.
Rule
11: Rule of Distribution Independence
A
database should work properly regardless of its distribution across a network.
Even if a database is geographically distributed, with data stored in pieces,
the end user should get an impression that it is stored at the same place. This
lays the foundation of distributed database.
Rule
12: Rule of Non Subversion
Any
row should obey the security and integrity constraints imposed. No special
privileges are applicable.
DBMS Keys
DBMS Keys
What
are the Keys in DBMS?
A
key in DBMS is an attribute or a set of attributes that help to uniquely
identify a tuple (or row) in a relation (or table). Keys are also used to
establish relationships between the different tables and columns of a
relational database. Individual values in a key are called key values.
A
key is used in the definitions of various kinds of integrity constraints. A
table in a database represents a collection of records or events for a
particular relation. Now there can be thousands and thousands of such records,
some of which may be duplicated. There should be a way to identify each record
separately and uniquely, i.e. no duplicates. Keys allow us to be free from this
hassle. A key could either be a combination of more than one attribute (or
columns) or just a single attribute. The main motive of this is to give each
record a unique identity.
There
are broadly nine types of keys in DBMS:
1. Primary Key
2. Candidate (Secondary) Key
3. Alternate Key
4. Super Key
5. Composite Key
6. Unique Key
7. Surrogate (Artificial) Key
8. Foreign Key
9. Partial Key
1.
Primary Key (PK)
A
Primary Key is a column of a table or a set of columns that helps to identify
every record present in that table uniquely. There can be only one Primary Key
in a table. Also, the Primary Key cannot have the same values repeating for any
row. Every value of the Primary Key has to be different with no repetitions. The
Primary Key constraint put on a column or set of columns will not allow them to
have any null values or any duplicates.
Candidate
Keys are those attributes that uniquely identify rows of a table. The PK of a
table is selected from one of the Candidate Keys. So, Candidate Keys have the
same properties as the PK. There can be more than one Candidate Keys in a
table.
A
table can have multiple choices for a PK; however, it can choose only one. So,
all the keys which did not become the Primary Key are called Alternate Keys.
Super
Key is the set of all the keys which help to identify rows in a table uniquely.
This means that all those columns of a table than capable of identifying the
other columns of that table uniquely will all be considered super keys.
Super Key is the superset of a Candidate Key. The Primary Key of a table is picked from the Super Key set to be made the table’s identity attribute.
Composite
Key is a set of two or more attributes that help identify each tuple in a table
uniquely. The attributes in the set may not be unique when considered
separately. However, when taken all together, they will ensure uniqueness.
Unique
Key is a column or set of columns that uniquely identify each record in a
table. All values will have to be unique in this key. A Unique Key differs from
a PK because it can have only one null value, whereas a PK cannot have any null
values.
Surrogate
Keys is an Artificial Key which aims to uniquely identify each record is called
a Surrogate Key. This kind of key in DBMS is unique because it is created when
we don’t have any natural PK. They do not lend any meaning to the data in the
table. Surrogate Key in DBMS is usually an integer. Surrogate keys in SQL are
allowed when:
·
No
property has the parameter of the Primary Key.
·
In
the table when the Primary Key is too big or complicated.
Foreign
Key is used to establish relationships between two tables. A Foreign Key will
require each value in a column or set of columns to match the Primary Key of
the referential table. Foreign keys help to maintain data and referential
integrity.
person-id | last_nm | first_nm | age |
1 | Sharma | Hemant | 30 |
2 | Kumar | Anil | 32 |
3 | Singh | Suraj | 31 |
ORDERS
order-id | order_number | personId |
1 | 77895 | 3 |
2 | 44678 | 3 |
3 | 22456 | 2 |
4 | 24568 | 1 |
Here, personId of Orders table can be declared as Foreign Key.
9. Partial Key
The set of attributes that are used to uniquely identify a weak entity set is called the Partial key. Only a bunch of the tuples can be identified using the partial keys. The Partial Key of the weak entity set is also known as a discriminator.
Here
we have an apartment as a weak entity and building as a strong entity type
connected via ‘belongs to’ relationship set. Apartment number is not globally
unique i.e. more than one apartment may have same number globally but it is
unique for a particular building since a building may not have same apartment
number. Thus apartment number cannot be primary key of entity Apartment but it
is a partial key shown with a dashed line.
Extended / Enhanced ER (EER) Diagram
Extended
/ Enhanced ER (EER) Diagram
•
EER
diagram stands for Enhanced (or Extended) Entity Relationship Diagram.
•
EER
is a high-level data model that incorporates the extensions to the original ER
model.
•
Enhanced
or Extended ER Diagrams are high level models that represent the requirements
and complexities of complex database.
•
In
addition to ER model concepts EER includes Specialization, Generalization and
Aggregation.
Generalization
& Specialization
Generalization:
Generalization is a process of
generalizing an entity which contains generalized attributes or properties of
generalized entities. It is a Bottom up process i.e. consider we have 3 sub
entities Car, Truck and Motorcycle. Now these three entities can be generalized
into one super class named as Vehicle.
Specialization:
Specialization is a process of
identifying subsets of an entity that share some different characteristic. It
is a top down approach in which one entity is broken down into low level
entity.
Another approaches to depict EER diagrams
Example 1
Aggregation
In aggregation, the relation between two entities is treated as a single entity. In aggregation, relationship with its corresponding entities is aggregated into a higher level entity.
Center
entity offers the Course entity act as a single entity in the relationship
which is in a relationship with another entity visitor. In the real world, if a
visitor visits a coaching center then he will never enquiry about the Course
only or just about the Center instead he will ask the enquiry about both.
Below is the ER diagram:
This is conversion of the above ER diagram into tables:
Notable points:
1. As Age is derived attribute, so it will not be mentioned in the table conversion.
2. As Hobby is multivalued attribute, it is converted into separate table associate with the STUDENT table.
Below is the EER diagram:
Tables will be:
1.
CUSTOMER (pname, mobileno, address, cust_type)
2.
FULLTIME (pname, mobileno, address, dept, desig)
3.
PARTTIME (pname, mobileno, address, dept, #days, job)
4.
ADHOC (pname, mobileno, address, dept, hours)
1. The entities which are further classified into sub - entities; will not be converted into table.
2. However their attributes will be a part of their sub-entities.
Monday, April 24, 2023
Entity Relationship (E-R) Diagram
Entity
Relationship Diagram
•
E–R
diagram stands for Entity Relationship Diagram.
•
Entity–relationship
modeling was developed for database and design by Peter Chen in 1976.
•
An
E–R diagram shows the relationship among entity sets.
•
An
entity set is a group of similar entities and these entities can have
attributes.
•
In
terms of DBMS, an entity is a table.
• By
showing relationship among tables and their attributes, E–R diagram shows the
complete logical structure of a database.
Introduction to ER Model
ER
Model is used to model the logical view of the system from data perspective
which consists of these components:
• An
Entity may be an object with a physical existence – a particular person, car,
house, or employee – or it may be an object with a conceptual existence – a
company, a job, or a university course.
•
An
Entity is an object of Entity Type and set of all entities is called as entity
set. e.g.; E1 is an entity having Entity Type Student and set of all students
is called Entity Set. In ER diagram, Entity Type is represented as:
E-R Diagram Notations
Attribute:
•
Attributes
are the properties which define the entity type. For example, Roll_No, Name,
DOB, Age, Address, Mobile_No are the attributes which defines entity type
Student. In ER diagram, attribute is represented by an oval.
1.
Key Attribute –
The
attribute which uniquely identifies each entity in the entity set is called key
attribute. For example, Roll_No will be unique for each student. In ER diagram,
key attribute is represented by an oval with underlying lines.
2.
Composite Attribute –
An
attribute composed of many other attribute is called as composite attribute.
For example, Address attribute of student Entity type consists of Street, City,
State, and Country. In ER diagram, composite attribute is represented by an
oval comprising of ovals.
3.
Multivalued Attribute –
An
attribute consisting more than one value for a given entity. For example,
Phone_No (can be more than one for a given student). In ER diagram, multivalued
attribute is represented by double oval.
4.
Derived Attribute –
An
attribute which can be derived from other attributes of the entity type is
known as derived attribute. e.g.; Age (can be derived from DOB). In ER diagram,
derived attribute is represented by dashed oval.
The
complete entity type Student with its attributes can be represented as:
Relationship Type and Relationship Set
A relationship type represents the association between entity types. For example, ‘Enrolled in’ is a relationship type that exists between entity type Student and Course. In ER diagram, relationship type is represented by a diamond and connecting the entities with lines.
A
set of relationships of same type is known as relationship set. The following
relationship set depicts S1 is enrolled in C2, S2 is enrolled in C1 and S3 is
enrolled in C3.
Degree of a relationship set
The number of different entity sets participating in a relationship set is called as degree of a relationship set.
1. Unary Relationship –
When
there is only ONE entity set participating in a relation, the relationship is
called as unary relationship. For example, one person is married to only one
person.
2.
Binary Relationship –
When
there are TWO entities set participating in a relation, the relationship is
called as binary relationship. For example, Student is enrolled in Course.
3.
n-ary Relationship –
When
there are n entities set participating in a relation, the relationship is
called as n-ary relationship.
Cardinality
The number of times an entity of an entity set participates in a relationship set is known as cardinality. Cardinality can be of different types as:
A.
One to One Relationship,
B.
One to Many Relationship,
C.
Many to One Relationship, &
D.
Many to Many Relationship.
A. One to One Relationship –
When a single instance of an entity is associated with a single instance of another entity then it is called one to one relationship. For example, a person has only one passport and a passport is given to one person.
B. One to Many Relationship –
When
a single instance of an entity is associated with more than one instances of
another entity then it is called one to many relationships. For example – a
customer can place many orders but a order cannot be placed by many customers.
C. Many to One Relationship –
When
more than one instances of an entity is associated with a single instance of
another entity then it is called many to one relationship. For example – many
students can study in a single college but a student cannot study in many
colleges at the same time.
D. Many to Many Relationship –
When
more than one instances of an entity is associated with more than one instances
of another entity then it is called many to many relationship. For example, a
student can be assigned to many projects and a project can be assigned to many
students.
Participation
Constraint
Participation Constraint is applied on the entity participating in the relationship set.
Total Participation –
Each entity in the entity set must participate in the relationship. If each student must enroll in a course, the participation of student will be total. Total participation is shown by double line in ER diagram.
Partial Participation –
The entity in the entity set may or may NOT participate in the relationship. If some courses are not enrolled by any of the student, the participation of course will be partial.
The
diagram depicts the ‘Enrolled in’ relationship set with Student Entity set
having total participation and Course Entity set having partial participation.
Weak Entity Type and Identifying Relationship
As discussed before, an entity type has a key attribute which uniquely identifies each entity in the entity set. But there exists some entity type for which key attribute can’t be defined. These are called Weak Entity type.
For example: A company may store the information of dependents (Parents, Children, Spouse) of an Employee. But the dependents don’t have existence without the employee. So Dependent will be weak entity type and Employee will be Identifying Entity type for Dependent.
A
weak entity type is represented by a double rectangle. The participation of
weak entity type is always total. The relationship between weak entity type and
its identifying strong entity type is called identifying relationship and it is
represented by double diamond.
E–R Diagram for Online Shopping System
Step 1: Identify basic entities (approx. 5-8)
Step 2: Draw entities on a plain sheet
Step 3: Establish relationship between entities through diamond boxes.
Note:
Your E-R diagram must be readable either from left to right or top to bottom.
Step 4: Add appropriate attributes to each and every entity.
Step 5: Mention cardinality among entities
Subscribe to:
Posts (Atom)
