Relational Model:- Define Domain: A domain is a set of all possible (or permissible) values in an attribute. Tuple: A row or a record is called as tuple in relational database management system. Relation: A relation is nothing but a table which can store data in rows and columns form in relational database management system.
Advantages of relational database.
1) Controlled redundancy 2) Sharing of data 3) Improved data security 4) Consistency 5) Higher integrity Primary Key: A primary key is an attribute in Relation that uniquely identifies the rows in relation. A Primary key does not hold NULL values and duplicate values. OR A key which is selected by the designer to uniquely identify the entity is called as Primary key. Example: In a Student table(Rollno , Name, Percentage) , Rollno is the primary key
Candidate key In a relation there may be a key or combination of keys which
uniquely identify the record. Such a key is called as Candidate key. Example : Consider a Student table (Rollno,Name,Percentage), if (Rollno) and( Name)both are unique then both are identified as candidate keys. OR Consider a Student table (Rollno,Name,Percentage), if (Rollno ,Name) is unique , then (Rollno, Name) can be a candidate key if and only if Name and Rollno individually are not unique.
Foreign Key:- A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.
Types of Relational constraints.
Relational Constraints Relational constraints are a set of rules. It is used to maintain the quality of information. Integrity constraints ensure that the data insertion, updating, and other processes have to be performed in such a way that data integrity is not affected. Thus, integrity constraint is used to guard against accidental damage to the database. Types of Relational integrity Constraints are as follows 1. Domain constraints 2. Entity integrity constraints 3. Referential Integrity Constraints
Domain Constraint – It is used to maintain value according to user specification For example: Not null, check constraint. 1) Not Null: By default, all columns in tables allows null values. When a NOT NULL Constraint is enforced on column or set of columns it will not allow null values. Example SQL> CREATE TABLE STUDENT (ROLL_NO NUMBER (5), NAME VARCHAR2 (20) NOT NULL);
2) Check Constraint: The constraint defines a condition that each row must satisfy. A single Column can have multiple check condition. Example SQL> CREATE TABLE EMP (ID NUMBER (5), NAME VARCHAR2 (10), SAL NUMBER (10) CHECK (SAL>15000));
3) The DEFAULT constraint provides a default value to a column when there is no
value provided while inserting a record into a table. Example SQL> CREATE TABLE STUDENT (ROLL_NO NUMBER (5), NAME VARCHAR2 (20) NOT NULL, COURSE VARCHAR(20)DEFAULT ‘DMS’);
Entity integrity constraints –
It provides a way of ensuring that changes made to the database by authorized users do not result in a loss of data consistency. For example: Primary key, unique constraints 1) Primary Key constraint: It is used to avoid redundant/duplicate value entry within the row of specified column in table. It restricts null values too. Example SQL> CREATE TABLE EMP (ID NUMBER (5)PRIMARY KEY, NAME VARCHAR2 (10), SAL NUMBER (10));
2) Unique Constraint: The UNIQUE constraint uniquely identifies each record in
a database table. The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness of a column or set of columns. It allows null value. Example CREATE TABLE PERSONS (P_ID NUMBER(5)UNIQUE, FIRSTNAME VARCHAR2(20), CITY VARCHAR2(20));
Referential Integrity Constraint:
It establishes parent child relationship between two tables. For example :Foreign key constraints It is a relational database concept in which multiple tables share a relationship based on the data stored in the tables, and that relationship must remain consistent. A value of foreign key is derived from primary key which is defined in parent table. Example CREATE TABLE DEPARTMENT (EMP_ID NUMBER(5) REFERENCES EMP(EMP_ID), DNO NUMBER(3));
Entity:- • An entity may be any object, class, person or place. In the ER diagram, an entity can be represented as rectangles. • Consider an organization as an example- manager, product, employee, department etc. can be taken as an entity. Entity set:- • An entity set is a group of similar entities and these entities can have attributes. • In terms of DBMS, an entity is a table or attribute of a table in database, so by showing relationship among tables and their attributes, ER diagram shows the complete logical structure of a database. Strong Entity:- • The strong entity has a primary key or Unique key. • Weak entities are dependent on strong entity. Its existence is not dependent on any other entity. • Strong Entity is represented by a single rectangle − • example, Professor is a strong entity here, and the primary key is Professor_ID. Weak Entity:- • The weak entity in DBMS do not have a primary key and are dependent on the parent entity. It mainly depends on other entities. • Weak Entity is represented by double rectangle − • Continuing our previous example, Professor is a strong entity, and the primary key is Professor_ID. However, another entity is Professor_Dependents, which is our Weak Entity. Attribute:- • The attribute is used to describe the property of an entity. Eclipse is used to represent an attribute. • For example, id, age, contact number, name, etc. can be attributes of a student.
Types Of Attribute:- • Key Attribute • The key attribute is used to represent the main characteristics of an entity. It represents a primary key. The key attribute is represented by an ellipse with the text underlined. •
Composite attribute:- • An attribute that composed of many other attributes is known as a composite attribute. The composite attribute is represented by an ellipse, and those ellipses are connected with an ellipse.
Multivalued Attribute:- • An attribute can have more than one value. These attributes are known as a multivalued attribute. The double oval is used to represent multivalued attribute. • For example, a student can have more than one phone number.
Derived Attribute:- • An attribute that can be calculated from other attribute is known as a derived attribute. It can be represented by a dashed ellipse. • For example, A person's age changes over time and can be derived from another attribute like Date of birth. Different symbols used in ER-Model are the following: Relationship:- • A relationship is used to describe the relation between entities. Diamond is used to represent the relationship.
One-to-One Relationship:- • When only one instance of an entity is associated with the relationship, then it is known as one to one relationship. • For example, one Department have one Manager
One-to-many relationship:- • When only one instance of the entity on the left, and more than one instance of an entity on the right associates with the relationship then this is known as a one-to-many relationship. • For example, Scientist can invent many inventions, but the invention is done by the only specific scientist.
Many-to-one relationship:- • When more than one instance of the entity on the left, and only one instance of an entity on the right associates with the relationship then it is known as a many-to-one relationship. • For example, Student enrolls for only one course, but a course can have many students. Many-to-many relationship:- • When more than one instance of the entity on the left, and more than one instance of an entity on the right associates with the relationship then it is known as a many-to-many relationship. • For example, Employee can assign by many projects and project can have many employees.
Functional dependency A functional dependency occurs when one attribute in a relation uniquely determine another attribute. (OR) A relation say R attribute X is functionally dependent on attribute Y if every value in X in the relation has exactly one value of Y in the given relation.
The functional dependency is represented as X Y, which specifies X is functionally
dependent on Y or Y attribute functionally determine the attribute X. Example: Consider table : Employee( Emp_Id, Emp_Name, Emp_Address) Here Emp_Id attribute can uniquely identify the Emp_Name attribute of employee table because if we know the Emp_Id, we can tell that employee name associated with it. Functional dependency can be written as: Emp_Id Emp_Name First Normal Form (1NF) A relation is said to be 1NF if and only if every entry of the relation has at most a single (atomic) value. OR A relation R is said to be in first normal form (1NF) if the domain of all attributes of R are atomic. It does not allow multivalued attributes and composite attributes.
Example Supplier (sno, sname, location, pno, qty)
The above relation is in 1NF as all the domains are having atomic value. But it is not in 2NF.
Benefits of denormalization Reduce number of relations : It reduce the number of relations because it combines two relations into one new relation. Reduce number of foreign keys-It reduce number of foreign keys because number of relations are reduced . Minimizes need for joins-It minimizes need for joins because it combines many relations into one. Increase Performance - It increase performance of database by adding redundant data or by grouping data.
Drawbacks of demoralization. Slow Data Updates-It may speed up the retrieval but can slow down database updates Increase size of relations -It can increase size of the relations due to combining multiple relations into one single relation. Complex implementation -It may simplify implementation in some cases but may make it more complex in other. Application Specific -It is always application-specific and needs to be re- evaluated if the application changes. List of Advantages of Normalization are the following: 1. More efficient data structure. 2. Avoid redundant fields or columns. 3. More flexible data structure i.e. we should be able to add new rows and data values easily 4. Better understanding of data. 5. Ensures that distinct tables exist when necessary. 6. Easier to maintain data structure i.e. it is easy to perform operations and complex queries can be easily handled. 7. Minimizes data duplication. 8. Close modeling of real world entities, processes and their relationships.
