Q. 1. One of the drawbacks of using file systems to store data is “data redundan
ID: 3602428 • Letter: Q
Question
Q. 1. One of the drawbacks of using file systems to store data is “data redundancy” which leads to the “inconsistency” problem.
Explain in your own words, why this issue was considered as a drawback? Support your answer with an example and how they solved this problem later.
Q. 2. What are the principles of relational database model? What is the difference between RDBMS and DBMS? Which one is more suitable in modern database applications? Justify with your answer.
Q. 3. Discuss the differences between plsql and mysql?
Explain in your own words, please provide some examples.
Explanation / Answer
Que1: One of the drawbacks of using file systems to store data is “data redundancy” which leads to the “inconsistency” problem.
Explain in your own words, why this issue was considered as a drawback? Support your answer with an example and how they solved this problem later.
Ans: Data Redundancy : Redundancy means having multiple copies of the same data. In computer file-based processing system, each application program has its own data files. The same data may be duplicated in more than one file. The duplication of data may create many problems such as:
1. To update a specific data/record, the same data must be updated in all files, otherwise different file may have different information about a specific item.
2. A valuable storage space is wasted.
3. There are no methods to validate the insertion of duplicate data in file system. Any user can enter any data. File system does not validate for the kind of data being entered nor does it validate for previous existence of the same data in the same file. Duplicate data in the system is not appreciated as it is a waste of space, and always lead to confusion and mishandling of data. When there are duplicate data in the file, and if we need to update or delete the record, we might end up in updating/deleting one of the record, leaving the other record in the file. Again the file system does not validate this process. Hence the purpose of storing the data is lost.
Though the file name says Student file, there is a chance of entering staff information or his report information in the file. File system allows any information to be entered into any file. It does not isolate the data being entered from the group it belongs to.
Disadvantages of data redundancy
1. Increases the size of the database unnecessarily.
2. Causes data inconsistency.
3. Decreases efficiency of database.
4. May cause data corruption.
Data is stored more than once in different files, that means duplicate data may occur in all these files. Since all the files are independent on each other so it is very difficult to overcome this error and if anyone finds this error then it will take time and effort to solve this issue.
For Example: A student is having record in college library and in Examination department. Then his name, roll number, fathers name and class will be same in both the departments. Also these departments are not dependent on each other. So it create lots of duplicates value about that student and when he needs any change for his name or class then he has to go to both the departments to make these changes happen otherwise it will create problem for him.
Then RDBMS system was implemented in college to maintain the student’s record, in which all the tables were dependent upon each other. Because of Primary Key concept, redundant values were removed. This solved the student’s problem.
Que 2: What are the principles of relational database model? What is the difference between RDBMS and DBMS? Which one is more suitable in modern database applications? Justify with your answer
Ans:
Principles of relational database model:
Codd’s criteria provide the benchmarks for defining RDBs. Knowing and understanding these principles will help you develop and design RDBs: Following are E.F. Codd’s Twelve Principles of Relational Databases
1. Information is represented logically in tables.
2. Data must be logically accessible by table, primary key, and column.
3. Null values must be uniformly treated as “missing information,” not as empty strings, blanks, or zeros.
4. Metadata (data about the database) must be stored in the database just as regular data is.
5. A single language must be able to define data, views, integrity constraints, authorization, transactions, and data manipulation.
6. Views must show the updates of their base tables and vice versa.
7. A single operation must be able to retrieve, insert, update, or delete data.
8. Batch and end-user operations are logically separate from physical storage and access methods.
9. Batch and end-user operations can change the database schema without having to recreate it or the applications built upon it.
10. Integrity constraints must be available and stored in the RDB metadata, not in an application program.
11. The data manipulation language of the relational system should not care where or how the physical data is distributed and should not require alteration if the physical data is centralized or distributed.
12. Any row processing done in the system must obey the same integrity rules and constraints that set-processing operations do
Difference between DBMS and RDBMS:
DBMS is extension of RDBMS. Key differences are:
DBMS
RDBMS
1
DBMS applications store data as file.
DBMS applications store data in tabular form.
2
In DBMS, data is stored in either a hierarchical form or a navigational form.
In RDBMS, table has an identifier called Primary Key and the data values are stored in form of tables.
3
Normalization is not present in DBMS.
Normalization is present in RDBMS.
4
DBMS doesn’t provide any security with regards to data manipulation.
RDBMS defines the data integrity constraints for the purpose of ACID(Atomicity, Consistency, Isolation and Durability) property.
5
DBMS uses file system to store the data. SO, there will be no relation between the tables.
In RDBMS, data values are stored in form of tables, so a relationship between these data values will be stored in form of a table as well.
6
DBMS has to provide some uniform methods to access the stored information.
RDBMS supports a tabular structure of a data and a relationship between them to access the stored information.
7
DBMS doesn’t support distributed database.
RDBMS supports distributed database.
8
DBMS is meant for small organizations and to deal with small data. It supports single user.
RDBMS is meant for large organizations and to deal with large data. It supports multiple user.
9
E.g. File System, XML etc.
E.g. MYSQL, Postage, sql server, Oracle etc
Which one is more suitable in modern database applications? Justify with your answer
RDBMS is more widely used database.
Every RDBMS is a DBMS, but the opposite is not true: RDBMS is a DBMS which is based on the relational model, but not every DBMS must be relational.
You can say that a RDBMS is an extension of a DBMS, even if there are many differences between the two. Most software products in the market today are both DBMS and RDBMS compliant. Essentially, they can maintain databases in a (relational) tabular form as well as a file form, or both. This means that today a RDBMS application is a DBMS application, and vice versa. However, there are still major differences between a relational database system for storing data and a plain database system.
RDBMS is a database management system based on relational model defined by E.F.Codd. Data is stored in the form of rows and columns. The relations among tables are also stored in the form of the table.
Features of RDBMS
· SQL databases are table based databases
· Data store in rows and columns
· Each row contains a unique instance of data for the categories defined by the columns.
· Provide facility primary key, to uniquely identify the rows
· Provides data to be stored in tables
· Persists data in the form of rows and columns
· Provides facility primary key, to uniquely identify the rows
· Creates indexes for quicker data retrieval
· Provides a virtual table creation in which sensitive data can be stored and simplified query can be applied.(views)- Sharing a common column in two or more tables(primary key and foreign key)
· Provides multi user accessibility that can be controlled by individual users
Que3: . Discuss the differences between plsql and mysql? Explain in your own words, please provide some examples.
Ans:
PL/SQL is a database language used with Oracle Database.
PL/SQL:
[1]PL/SQL, Oracle's procedural extension of SQL, is an advanced fourth-generation programming language (4GL).
[2]It offers software-engineering features such as data encapsulation, overloading, collection types, exceptions, and information hiding.
[3]PL/SQL also supports rapid prototyping and development through tight integration with SQL and the Oracle database.
[4]PL/SQL is a proprietary procedural language used by Oracle
[5]PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation
MySQL:
[1]MySQL is a also a Database tool itself that uses SQL language. it is open source.
[2]MySQL is weaker in the areas of inserting and deleting data. But it is an excellent choice, for data storage and referencing data.
[3]MySQL is a relational database management system. You can submit SQL queries to the MySQL database to store, retrieve, modify or delete data.
[4]Basically, MySQL is one of many books holding everything, SQL is how you go about reading that book.
Understanding the Main Features of PL/SQL:
A good way to get acquainted with PL/SQL is to look at a sample program. The program below processes an order for a tennis racket. First, it declares a variable of type NUMBER to store the quantity of tennis rackets on hand. Then, it retrieves the quantity on hand from a database table named inventory. If the quantity is greater than zero, the program updates the table and inserts a purchase record into another table named purchase_record. Otherwise, the program inserts an out-of-stock record into the purchase_record table.
-- available online in file 'examp1'
DECLARE
qty_on_hand NUMBER(5);
BEGIN
SELECT quantity INTO qty_on_hand FROM inventory
WHERE product = 'TENNIS RACKET'
FOR UPDATE OF quantity;
IF qty_on_hand > 0 THEN -- check quantity
UPDATE inventory SET quantity = quantity - 1
WHERE product = 'TENNIS RACKET';
INSERT INTO purchase_record
VALUES ('Tennis racket purchased', SYSDATE);
ELSE
INSERT INTO purchase_record
VALUES ('Out of tennis rackets', SYSDATE);
END IF;
COMMIT;
END;
With PL/SQL, you can use SQL statements to manipulate Oracle data and flow-of-control statements to process the data. You can also declare constants and variables, define procedures and functions, and trap runtime errors. Thus, PL/SQL combines the data manipulating power of SQL with the data processing power of procedural languages.
Example 1:
The following example uses a simple FOR loop to insert ten rows into a database table. The values of a loop index, counter variable, and either of two character strings are inserted. Which string is inserted depends on the value of the loop index.
PL/SQL Block
Output Table
Example 2:
The following example uses a cursor to select the five highest paid employees from the emp table.
Input Table
PL/SQL Block
Output Table
Example 3:
The following example demonstrates Declaring, Defining, and Invoking a Simple PL/SQL Function that computes and returns the maximum of two values.
DECLARE
a number;
b number;
c number;
FUNCTION findMax(x IN number, y IN number)
RETURN number
IS
z number;
BEGIN
IF x > y THEN
z:= x;
ELSE
Z:= y;
END IF;
RETURN z;
END;
BEGIN
a:= 23;
b:= 45;
c := findMax(a, b);
dbms_output.put_line(' Maximum of (23,45): ' || c);
END;
/
When the above code is executed at the SQL prompt, it produces the following result
Maximum of (23,45): 45
PL/SQL procedure successfully completed.
MY SQL Example 1:
Consider below table:
Database: southwind
Table: products
productID
INT
productCode
CHAR(3)
name
VARCHAR(30)
quantity
INT
price
DECIMAL(10,2)
1001
PEN
Pen Red
5000
1.23
1002
PEN
Pen Blue
8000
1.25
1003
PEN
Pen Black
2000
1.25
1004
PEC
Pencil 2B
10000
0.48
1005
PEC
Pencil 2H
8000
0.49
Creating and Deleting a Database - CREATE DATABASE and DROP DATABASE:
mysql> CREATE DATABASE southwind;
Query OK, 1 row affected (0.03 sec)
mysql> DROP DATABASE southwind;
Query OK, 0 rows affected (0.11 sec)
Creating the table:
CREATE TABLE IF NOT EXISTS products (
productID INT UNSIGNED NOT NULL AUTO_INCREMENT,
productCode CHAR(3) NOT NULL DEFAULT '',
name VARCHAR(30) NOT NULL DEFAULT '',
quantity INT UNSIGNED NOT NULL DEFAULT 0,
price DECIMAL(7,2) NOT NULL DEFAULT 99999.99,
PRIMARY KEY (productID)
);
Query OK, 0 rows affected (0.08 sec)
Describing the table:
mysql> DESCRIBE products;
+-------------+------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+------------+----------------+
| productID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| productCode | char(3) | NO | | | |
| name | varchar(30) | NO | | | |
| quantity | int(10) unsigned | NO | | 0 | |
| price | decimal(7,2) | NO | | 99999.99 | |
Inserting Rows - INSERT INTO
mysql> INSERT INTO products VALUES (1001, 'PEN', 'Pen Red', 5000, 1.23);
Query OK, 1 row affected (0.04 sec)
DBMS
RDBMS
1
DBMS applications store data as file.
DBMS applications store data in tabular form.
2
In DBMS, data is stored in either a hierarchical form or a navigational form.
In RDBMS, table has an identifier called Primary Key and the data values are stored in form of tables.
3
Normalization is not present in DBMS.
Normalization is present in RDBMS.
4
DBMS doesn’t provide any security with regards to data manipulation.
RDBMS defines the data integrity constraints for the purpose of ACID(Atomicity, Consistency, Isolation and Durability) property.
5
DBMS uses file system to store the data. SO, there will be no relation between the tables.
In RDBMS, data values are stored in form of tables, so a relationship between these data values will be stored in form of a table as well.
6
DBMS has to provide some uniform methods to access the stored information.
RDBMS supports a tabular structure of a data and a relationship between them to access the stored information.
7
DBMS doesn’t support distributed database.
RDBMS supports distributed database.
8
DBMS is meant for small organizations and to deal with small data. It supports single user.
RDBMS is meant for large organizations and to deal with large data. It supports multiple user.
9
E.g. File System, XML etc.
E.g. MYSQL, Postage, sql server, Oracle etc
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.