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

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

DATABASE & FEATURES OF DBMS

Posted on Updated on

WHAT IS DATABASE?

Database is collection of data which is related by some aspect. Data is collection of facts and figures which can be processed to produce information. Name of a student, age, class and her subjects can be counted as data for recording purposes.

WHAT ARE THE FEATURES OF DBMS?

 

  • Real-world entity: Modern DBMS are more realistic and uses real world entities to design its architecture. It uses the behavior and attributes too. For example, a school database may use student as entity and their age as their attribute.
  • Relation-based tables: DBMS allows entities and relations among them to form as tables. This eases the concept of data saving. A user can understand the architecture of database just by looking at table names etc.
  • Isolation of data and application: A database system is entirely different than its data. DBMS stores metadata which is data about data, to ease its own process.
  • Less redundancy: DBMS follows rules of normalization, which splits a relation when any of its attributes is having redundancy in values. Following normalization, which itself is a mathematically rich and scientific process, make the entire database to contain as less redundancy as possible.
  • Consistency: DBMS always enjoy the state on consistency where the previous form of data storing applications like file processing does not guarantee this. Consistency is a state where every relation in database remains consistent.
  • Query Language: DBMS is equipped with query language, which makes it more efficient to retrieve and manipulate data. A user can apply as many and different filtering options, as he or she wants. Traditionally it was not possible where file-processing system was used.
  • ACID Properties: DBMS follows the concepts for ACID properties, which stands for Atomicity, Consistency, Isolation and Durability. These concepts are applied on transactions, which manipulate data in database.
  • Multiuser and Concurrent Access: DBMS support multi-user environment and allows them to access and manipulate data in parallel. Though there are restrictions on transactions when they attempt to handle same data item, but users are always unaware of them.
  • Multiple views: DBMS offers multiples views for different users. A user who is in sales department will have a different view of database than a person working in production department. This enables user to have a concentrate view of database according to their requirements.

Security: Features like multiple views offers security at some extent where users are unable to access data of other users and departments

SQL Commands

Posted on Updated on

Qu.) What are SQL Commands?

Ans.)The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their nature:

 

1.)             DDL – Data Definition Language:

Command Description
CREATE Creates a new table, a view of a table, or other object in database
ALTER Modifies an existing database object, such as a table.
DROP Deletes an entire table, a view of a table or other object in the database.

 

2.)             DML – Data Manipulation Language:

Command Description
INSERT Creates a record
UPDATE Modifies records
DELETE Deletes records

 

3.)             DCL – Data Control Language:

Command Description
GRANT Gives a privilege to user
REVOKE Takes back privileges granted from user

4.)             DQL – Data Query Language:

Command Description
SELECT Retrieves certain records from one or more tables

Posted by Vissicomp Technology Pvt Ltd

http://www.vissicomp.com

Components of the Database System Environment

Posted on Updated on

Components of the Database System Environment

There are five major components in the database system environment and their interrelationship  are –

  • Hardware
  • Software

    DBMS Application Users
    DBMS Application Users
  • Data
  • Users
  • Procedures

1. Hardware: The hardware is the actual computer system used for keeping and accessing the database.

2. Software: The software is the actual DBMS. Between the physical databases itself (i.e. the data as actually stored) and the users of the system is a layer of software, usually called the Database Management System or DBMS. All requests from users for access to the database are handled by the DBMS. One general function provided by the DBMS is thus the shielding of database users from complex hardware-level detail.

omponents of the Database System
omponents of the Database System

3. Data: It is the most important component of DBMS environment from the end users point of view. As shown in figure that data acts as a bridge between the machine components and the user components. The database contains the operational data and the meta-data, the ‘data about data’.

4. Users: There are a number of users who can access or retrieve data on demand using the applications and interfaces provided by the DBMS. Each type of user needs different software capabilities. The users of a database system can be classified in the following groups, depending on their degrees of expertise or the mode of their interactions with the DBMS. The users can be:

• Naive Users

• Online Users

• Application Programmers

• Sophisticated Users

• Data Base Administrator (DBA)

5. Procedures: Procedures refer to the instructions and rules that govern the design and use of the database. The users of the system and the staff that manage the database require documented procedures on how to use or run the system.

These may consist of instructions on how to:

• Log on to the DBMS.

• Use a particular DBMS facility or application program.

• Start and stop the DBMS.

• Make backup copies of the database.

• Handle hardware or software failures.

Posted by VissiComp Technology Pvt Ltd http://www.vissicomp.com