DBMS/Database Design
- entity
- nothing but it represents field of the database e.g.,
employee
- entity set
- collection of different fields
- relationship
- relationship set
- domain
- The set of possible values for an attribute is called the
domain of the attribute e.g.: The domain of attribute marital status is having four values: single, married, divorced or widowed.
The domain of the attribute month is having twelve values ranging from January to December.
- Key attribute
- The attribute (or combination of attributes) that is
unique for every entity instance e.g.: the account number of an account, the employee id of an employee etc.
- attribute
- e.g., gender
- composite attribute
- degree of relationship
- the number of entity types involved in a
database
- cardinality of relationship
- The minimum and maximum values of this
connectivity is called the cardinality of the relationship e.g., one to one, one to many, many to many
- weak entity
- Entity that depends on other entity for its existence and
doesn’t have key attribute (s) of its own e.g. : spouse of employee
- identifying relationship
- non-identifying relationship
- discriminator
ER Diagram
[edit | edit source]- entity relationship data model
- Introduction to Data Modeling at University of Texas, Austin
Modelling concepts
- Specialisation
- Generalisation
- Categorisation
- Aggregation
Normalization
[edit | edit source]Normalization is the formal process for deciding which attributes should
be grouped together in a relation.The process of normalization was
first developed by E.F.CODD. Normalization is the process of evaluating and correcting the relation schemes to minimize data redundancies and thereby reducing anomalies.
First Normal Form
[edit | edit source]As per the rule of the 1NF an attributes of a table cannot hold multiple value. It should hold only atomic value.
Second Normal Form
[edit | edit source]Second Normal form is characterized by the property of functional dependency. a relation is in second normal form if it is in 1st normal form and every non key attribute is fully and functionally dependent on the primary key.
Third Normal Form
[edit | edit source]transitive dependency : A relation is in third normal form , if it is in second normal form and
no transitive dependencies exist.
Suppose A,B and C are the three attributes of a relation(R) then if A->B(B depends on A) B->C(C depends on B) then we can say that "C" depends transitively on "A".
Boyce Codd Normal Form (BCNF)
[edit | edit source]BCNF is based on functional dependencies that take into account all candidate keys in a relation. For a relation with only one candidate key, 3rd normal form and BCNF are equivalent. A relation is in BCNF if and only if every determinant is a candidate key. now what is a determinant? consider the following functional dependency: A→B where A and B are attributes in relation R. it says that B is functionally dependent on A. here A is referred to as determinant and B is the dependent.
BCNF is slightly stronger than 3nF.
Fourth Normal Form
[edit | edit source]- multivalued dependencies are removed
Fifth Normal Form
[edit | edit source]Any remaining anomalies are removed.in this normal form we isolate semantically related multiple relationships.
Anomalies can be : Insertion Anomaly,Deletion Anomaly or Modification/update Anomaly. Any remaining anomalies are removed.in this normal form we isolate semantically related multiple relationships.