DataBase Management System

Normalization

Posted on

NORMALIZATION

Fourth Normal Form (4NF)

When attributes in a relation have multi-valued dependency, further Normalization to 4NF and 5NF are required. Let us first find out what multi-valued dependency is.

multi-valued dependency is a typical kind of dependency in which each and every attribute within a relation depends upon the other, yet none of them is a unique primary key.

Consider a vendor supplying many items to many projects in an organization. The following are the assumptions:

  1. A vendor is capable of supplying many items.
  2. A project uses many items.
  3. A vendor supplies to many projects.
  4. An item may be supplied by many vendors.

A multi valued dependency exists here because all the attributes depend upon the other and yet none of them is a primary key having unique value.

Vendor Code Item Code Project No.
V1 I1 P1
V1 I2 P1
V1 I1 P3
V1 I2 P3
V2 I2 P1
V2 I3 P1
V3 I1 P2
V3 I1 P3

The given relation has a number of problems. For example:

  1. If vendor V1 has to supply to project P2, but the item is not yet decided, then a row with a blank for item code has to be introduced.
  2. The information about item I1 is stored twice for vendor V3.

Observe that the relation given is in 3NF and also in BCNF. It still has the problem mentioned above. The problem is reduced by expressing this relation as two relations in the Fourth Normal Form (4NF). A relation is in 4NF if it has no more than one independent multi valued dependency or one independent multi valued dependency with a functional dependency.

The table can be expressed as the two 4NF relations given as following. The fact that vendors are capable of supplying certain items and that they are assigned to supply for some projects in independently specified in the 4NF relation.

Vendor-Supply

Vendor Code Item Code
V1 I1
V1 I2
V2 I2
V2 I3
V3 I1

Vendor-Project

Vendor Code Project No.
V1 P1
V1 P3
V2 P1
V3 P2

Posted By-: Vissicomp Technology Pvt. Ltd.

Website -: http://www.vissicomp.com

Advertisements

Normalization

Posted on Updated on

Boyce-Code Normal Form (BCNF)

  • A relationship is said to be in BCNF if it is already in 3NF and the left hand side of every dependency is a candidate key.
  • A relation which is in 3NF is almost always in BCNF. These could be same situation when a 3NF relation may not be in BCNF the following conditions are found true.
  1. The candidate keys are composite.
  2. There are more than one candidate keys in the relation.
  3. There are some common attributes in the relation
Professor Code Department Head of Dept. Percent Time
P1 Physics Ghosh 50
P1 Mathematics Krishnan 50
P2 Chemistry Rao 25
P2 Physics Ghosh 75
P3 Mathematics Krishnan 100

Consider, as an example, the above relation. It is assumed that:

  1. A professor can work in more than one department
  2. The percentage of the time he spends in each department is given.
  3. Each department has only one Head of Department.
  4. The relation diagram for the above relation is given as the following:

 

Untitled

The given relation is in 3NF. Observe, however, that the names of Dept. and Head of Dept. are duplicated. Further, if Professor P2 resigns, rows 3 and 4 are deleted. We lose the information that Rao is the Head of Department of Chemistry.

The normalization of the relation is done by creating a new relation for Dept. and Head of Dept. and deleting Head of Dept. form the given relation. The normalized relations are shown in the following.

Professor Code Department Percent Time
P1 Physics 50
P1 Mathematics 50
P2 Chemistry 25
P2 Physics 75
P3 Mathematics 100

 

Department Head of Dept.
Physics Ghosh
Mathematics Krishnan
Chemistry Rao

See the dependency diagrams for these new relations.

y1

 

 

 

 

 

Posted By-: Vissicomp Technology Pvt. Ltd.

Website -: http://www.vissicomp.com

DBMS

Posted on

  1. What are the advantages of DBMS over the traditional file system of storing data?
  2. Reducing data redundancy and inconsistency
  3. Allowing easy access to data
  4. Isolating data by providing a common format for data storage
  5. Solving the data integrity problems by providing constraints
  6. Providing atomicity, specifically for secure online transactions.

 

  1. What are the disadvantage in File Processing System?
  2.   Data redundancy and inconsistency.
  3.     Difficult in accessing data.
  4.     Data isolation.
  5.     Data integrity.
  6.     Concurrent access is not possible.
  7.     Security Problems.

 

  1. Describe the three levels of data abstraction?

 

The are three levels of abstraction:

  1.     Physical level: The lowest level of abstraction describes how data are stored.
  2.    Logical level: The next higher level of abstraction, describes what data are stored in         database and what relationship among those data.
  3.    View level: The highest level of abstraction describes only part of entire database.

 

Posted By-: Vissicomp Technology Pvt. Ltd.

Website -: http://www.vissicomp.com

DBMS

Posted on

USEFULL FOR FYBSC (IT) & TYBSC (CS) STUDENTS

 

1. Software that defines a database, stores the data, supports a query language, produces reports and creates data entry screens is a:

A) Data dictionary

B) Database management system (DBMS)

C) Decision support system

D) Relational database

 

2. The separation of the data definition from the program is known as:

A) Data dictionary

B) Data independence

C) Data integrity

D) Referential integrity

 

3. In the client / server model, the database:

A) is downloaded to the client upon request

B) is shared by both the client and server

C) Resides on the client side

D) Resides on the server side

 

4. The database design that consists of multiple tables that are linked together through matching data stored in each table is called a:

A) Hierarchical database

B) Network database

C) Object oriented database

D) Relational database

 

5. Which of the following items is not the advantage of a DBMS?

A) Improved ability to enforce standards

B) Improved data consistency

C) Local control over the data

D) Minimal data redundancy

 

6. Which of the following statements is not correct?

A) All many-to-many relationships must be converted to a set of one-to-many relationships by adding a new entity

B) In a one-to-one relationship between two classes, the two classes are generally described by one table in relational database model

C) Encapsulation provides some security and control features

D) Properties and functions can be protected from other areas of the applications

 

Posted By-: Vissicomp Technology Pvt. Ltd.

Website -: http://www.vissicomp.com

 

 

SQL JOIN TYPES

Posted on

SQL JOIN TYPES:

  • INNER JOIN: returns rows when there is a match in both tables.

The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.

SELECT table1.column1, table2.column2...FROM table1
INNER JOIN table2 ON table1.common_field = table2.common_field;

 

  • LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table. The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in right table, the join will still return a row in the result, but with NULL in each column from right table.

This means that a left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.

SELECT table1.column1, table2.column2...FROM table1 
LEFT JOIN table2 ON table1.common_field = table2.common_field;

 

  • RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table. The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table. This means that if the ON clause matches 0 (zero) records in left table, the join will still return a row in the result, but with NULL in each column from left table. This means that a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.
SELECT table1.column1, table2.column2...FROM table1
RIGHT JOIN table2 ON table1.common_field = table2.common_field;

 

  • FULL JOIN: returns rows when there is a match in one of the tables. The SQL FULL JOIN combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.
SELECT table1.column1, table2.column2...FROM table1
FULL JOIN table2 ON table1.common_field = table2.common_field;

 

  • SELF JOIN: is used to join a table to it as if the table were two tables, temporarily renaming at least one table in the SQL statement. The SQL SELF JOIN is used to join a table to it as if the table were two tables, temporarily renaming at least one table in the SQL statement.
SELECT a.column_name, b.column_name... FROM table1 a, table1 b
WHERE a.common_field = b.common_field;

 

  • CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables. The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records from the two or more joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement.
SELECT table1.column1, table2.column2...
FROM  table1, table2 [, table3 ]

Posted by Vissicomp Technology Pvt Ltd
www.vissicomp.com

Posted on

SQL – NULL Values

  •  The SQL NULL is the term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank.
  •   A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces.

The basic syntax of NULL while creating a table:

 CREATE TABLE CUSTOMERSINFO(

ID   INT NOT NULL,   NAME VARCHAR (20)     NOT NULL,   AGE  INT    NOT NULL,   ADDRESS  CHAR (25) ,  SALARY   DECIMAL (18, 2),       PRIMARY KEY (ID));

  •   Here, NOT NULL signifies that column should always accept an explicit value of the given data type. There are two columns where we did not use NOT NULL, which means these columns could be NULL.
  •     A field with a NULL value is one that has been left blank during record creation.

Example:

  •   The NULL value can cause problems when selecting data, however, because when comparing an unknown value to any other value, the result is always unknown and not included in the final results.
  •    You must use the IS NULL or IS NOT NULL operators in order to check for a NULL value.
  •    Consider the following table, CUSTOMERS having the following records:

+—-+———-+—–+———–+———-+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+—-+———-+—–+———–+———-+

|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |

|  2 | Khilan   |  25 | Delhi     |  1500.00 |

|  3 | kaushik  |  23 | Kota      |  2000.00 |

|  4 | Chaitali |  25 | Mumbai    |  6500.00 |

|  5 | Hardik   |  27 | Bhopal    |  8500.00 |

|  6 | Komal    |  22 | MP        |          |

|  7 | Muffy    |  24 | Indore    |          |

+—-+———-+—–+———–+———-+

Now, following is the usage of IS NOT NULL operator:

SELECT  ID, NAME, AGE, ADDRESS, SALARY

FROM CUSTOMERS WHERE SALARY IS NOT NULL;

This would produce the following result:

+—-+———-+—–+———–+———-+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+—-+———-+—–+———–+———-+

|  1 | RamK    |  32 | Ahmedabad |  2000.00 |

|  2 | Kushi   |  23 | Delhi     |  1500.00 |

|  3 | kamma   |  21 | Kota      |  2000.00 |

+—-+———-+—–+———–+———-+

Now, following is the usage of IS NULL operator:

SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE SALARY IS NULL;

This would produce the following result:

+—-+———-+—–+———–+———-+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+—-+———-+—–+———–+———-+

|  6 | Koma     |  22 | MP        |          |

|  7 | buffy    |  24 | Indore    |          |

+—-+———-+—–+———–+———-

Posted by Vissicomp Technology Pvt Ltd

http://www.vissicomp.com

SQL Constraints:

Posted on Updated on

SQL Constraints:

Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.

Constraints could be column level or table level. Column level constraints are applied only to one column where as table level constraints are applied to the whole table.

Following are commonly used constraints available in SQL:

  • NOT NULL Constraint: Ensures that a column cannot have NULL value.
  • DEFAULT Constraint: Provides a default value for a column when none is specified.
  • UNIQUE Constraint: Ensures that all values in a column are different.
  • PRIMARY Key: Uniquely identified each rows/records in a database table.
  • FOREIGN Key: Uniquely identified a rows/records in any another database table.
  • CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions.
  • INDEX: Use to create and retrieve data from the database very quickly.

Data Integrity:

The following categories of the data integrity exist with each RDBMS:

  • Entity Integrity: There are no duplicate rows in a table.
  • Domain Integrity: Enforces valid entries for a given column by restricting the type, the format, or the range of values.
  • Referential integrity: Rows cannot be deleted, which are used by other records.
  • User-Defined Integrity: Enforces some specific business rules that do not fall into entity, domain or referential integrity.

Posted by Vissicomp Technology Pvt Ltd

http://www.vissicomp.com