Unit 2;
Relational structure:
- EF Codd first proposed the concept of relational data model hence
he is known as the father of the relational model.
- A relational database is a collection of 2 dimensional tables
which consists of rows and columns
- These tables are known as relations, columns as attributes and
rows are known as tuples. Each table represents some real world
thing or event about which information is collected.
Tables: in relational data model, relations are saved in the format of
tables. This format stores relations among entities. A table has rows
and columns, where rows represent records and columns represent
attributes.
Rows(tuples): is a set of data or a record in a table(relation).
Column: it is a vertical entity in a table which contains all the
information associated with a specific field in the table.
Attribute: Attributes can be defined as a named column of a
table(relation), it is also a property of an entity. There are 4 types
of attributes:
1. Simple attribute: attributes which do not possess any complex
data.
2. Composite attribute: These are generally set of sub attributes
3. Single valued and multivalued attributes; a) single valued:
should have only one value, b) multivalued: should have one or
more values.
4. Derived attribute: attributes derived from other attributes.
Entity: Entities are real world objects that are used in creation of
databases.
Domain: Domains are a few predetermined sets of values that can only
be added into columns, i.e the values are allowed to be entered in a
column.
Keys
- A column that can uniquely identifies a single records in a table
are called keys of the table
- Types of keys are as followed
- Primary key: A primary key is a column or a group of columns
in a table that uniquely identifies a tuple or a record(row)
in a relational table, a primary key cannot be NULL and it
must be unique for each tuple.
- Foreign key: A foreign key is a column or a group of columns
in a table that provide a connection between data of 2
tables, a foreign key refers to the primary key of another
table to create a connection.
- Super key: An attribute or a set of attributes that uniquely
identifies a single row in an entity.
- Candidate key: A super key with minimum number of attributes
is called a candidate key.
- Composite key: A key with more than one attribute that
uniquely identifies a tuple(row) from an entity.
Data constraints:
- Data constraints are the restrictions on content of the database
or on database operations
- It is a condition specified on a database schema that restrict
the data to be inserted in an instance of the database
- They are the data integrity rules/restriction which allows only
valid data into tables.
Domain constraints:
- Domain constraints are constraints that define the domain or set
of values for an attribute, which can be entered into them.
- It specifies that the value taken by the attributes must be the
atomic values from its domain.
- The datatypes of domain include string, characters, integer etc
- The value of attribute must be available in the corresponding
domain
Referral integrity constraints:
- Referential integrity constraint is enforced when a foreign key
references the primary key of a relation.
- The referential integrity constraint is specified between 2
tables to maintain the consistency among tuples in the two
tables.
- A tuple in one relation refers only an existing tuple in another
table
Entity relationship model:
- Entity relationship model is a high level database model useful
in developing a conceptual design of a database.
- The entity relationship model is shown diagrammatically using the
entity relationship diagram which represent the elements of
conceptual model that shows the meanings and the relationships
between those element
- These ER diagrams are created based on the three basic concepts
namely, entities, attributes, and relationships
Entity set:
- Weak entity set:
1. An entity set that does not have any ke attribute of its own
is called a weak entity set.
2. The weak entity is also called a dependent entity as it
depends on another entity for its identification.
3. A member of weak entity set is called as subordinate entity
4. It is represented by a double rectangle in the Entity
relationship diagram.
Diagram:
- Strong entity set:
- 1. An entity set that has a key attribute is called a strong
entity set.
- 2. The strong entity is called an independent entity, as it does
not rely on other entities for its identification.
- 3. A member of strong entity set is called a dominant entity
- 4. It is represented by a reactant in entity relationship diagram
Diagram:
Functional dependency:
- Functional dependency in DBMS is a relationship between
attributes of a table dependent on each other.
- It helps in preventing data redundancy
Normalization
Normalization is a process of organizing the data in a database to
avoid data redundancy, insertion anomaly, update anomaly, and deletion
anomaly. It ensures the database is well-structured and free of
unnecessary duplication.
Types of Normalization
1. First Normal Form (1NF)
2. Second Normal Form (2NF)
3. Third Normal Form (3NF)
4. Boyce & Codd Normal Form (BCNF)
5. Fourth Normal Form (4NF)
First Normal Form (1NF):
- A relation R is said to be in the first normal form (1NF) if the
domain of all attributes of RR are atomic. Alternatively, a table
is in the first normal form if it contains no repeating element
groups.
- Example:
Relation: Supplier(sno, sname, location, pno, qty)
SNO SNAME LOCATION PNO QTY
S1 Abc Mumbai P1 200
S2 Pqr Pune P2 300
S3 Lmn Delhi P1 400
The above relation is in 1NF as all the domains have atomic values.
However, it is not in 2NF.
Second Normal Form (2NF):
- A relation is in the second normal form if:
1. It is in its first normal form.
2. All the non-key attributes are fully functionally dependent on
the primary key.
- In the example relation:
- NAME, LOCATION depends upon S_no
- quantity depends on (S_no, P_no)
To convert this relation into 2NF, the table is split into two tables
as:
Supplier Table
(S_no, SUPPLIER_NAME, LOCATION)
S_no SUPPLIER_NAME LOCATION
S1 Abc Mumbai
S2 Pqr Pune
S3 Lmn Delhi
Supplier_Product Table
(S_no, P_no, quantity )
S_no P_NO quantity
S1 P1 200
S2 P2 300
S3 P1 400
Now, both tables are in 2NF.
Third Normal Form (3NF):
A relation is in the third normal form if:
1. It satisfies the criteria to be in second normal form.
2. There exists no transitive functional dependency.
Transitive functional dependency means if A is functionally dependent
on B, and B is functionally dependent on C, then C is transitively
dependent on A.
Example Schema:
(S_no, SupplierName, Supplier_city, O_no, quantity, amount, P_code,
P_name, rate)
Step 1: To convert it into 2NF, the table is decomposed into two
tables:
- Supplier Details:
(S_no, Supplier_name, Supplier_city)
S_no Supplier_name Supplier_city
S1 ABC Mumbai
S2 PQR Pune
S3 LMN Delhi
- Order Details:
(O_no, quantity, amount, S_no, P_code, P_name, rate)
O_no quantity amount S_no P_code P_name Rate
01 10 5000 S1 P1 Widget a 500
02 20 6000 S2 P2 Widget b 300
03 15 7000 S3 P3 Widget b 500
Now, the above tables are in 2NF.
Step 2: To convert these into 3NF, the tables are further decomposed:
- Supplier Details:
(S_no, Supplier_name, Supplier_city)
S_no Supplier_name Supplier_city
S1 ABC Mumbai
S2 PQR Pune
S3 LMN Delhi
- Product Details:
(P_code, P_name, rate)
P_code P_name rate
P1 Widget A 500
P2 Widget B 300
- Order Details (or Transaction Details):
(O_no, S_no, P_code, quantity, amount)
O_no S_no P_code quantity amount
01 S1 P1 10 5000
02 S2 P2 20 6000
03 S3 P3 15 7500
Now, the above three tables satisfy transitive dependencies and are in
3NF.
