ITECH1006 Database Management System Assignment Solution

ITECH1006 Database Management System Assignment Solution

ITECH1006 Database Management System Assignment Solution

This is a solution related Database Management System in which discuss identification of database, database management system and type of database management system and difference different partitioning. 

Ans 2

Client/Server object DBMS primarily use data shipping architecture as they provide support for fine grained and close level interaction between data and the application. The server is also relieved by several of its duties and various DBMS functions which allow the server to perform other functions more effectively. In this approach the data objects from the server are cached at client nodes. Client/Server model is very much performance intensive and hence the use of data shipping architecture ensure that resource utilization at the client end is maximised and server performance is also booted up. In this the client processes the query while from the server the data is simply bought on demand. In this case application needs to provide associative access to data and this could pose to be a problem. This architecture is also used by Client/Server object DBMS because it provides improved network latency which basically refers to the time which a server takes to respond to request. This improved latency is the result of the operation in which data objects are pre-fetched by the client. The data is taken from the server cache and stored in the client cache where it is operated upon. This approach ensures that any request is processed quickly and this is one of the primary requirements of a client/server model. No matter the kind of workload on the system data shipping architecture ensures that robust performance is provided. There are several algorithms which are being considered for ensuring optimal performance of the client server architecture. But there is still a need to come to a consensus about which model or algorithm should be best suited for this purpose. The choice largely depends upon what is more important for a particular system. Some choose robustness over quality and all this largely depends upon the IT business requirement. A normal adaptive data shipping architecture includes cache consistency, adaptive data transfer and recovery data management service. Out of these three adaptive data transfer has proved to be a better option. The adaptive data shipping object fairs the best out of all the data shipping architecture when one talks about robustness. It has the ability to transfer both may it be a page or an object from server to the client. It can dynamically switch between transferring of object groups or pages depending upon the requirement at that time. When it comes to adaptive cache consistency then in that case most of the time there is a trade off between good performance and low abort rate. The chosen algorithm provides one service much better than the other. The one with good performance will always be prone to optimistic in nature and hence will also abort early and on the other hand if one is working in an interactive user environment higher abort rates can prove to be very dangerous. But an algorithm that provides lower abort rate will lead to massive overhead on its part which would reduce the performance of the system. Thus, there is still a constant lookout for a cache consistency algorithm that provides for both. But, one thing that’s certain is that data shipping architecture suits a client/server object DBM’s because it focuses on improving the network latency.

Database Management System Assignment Sample

On the other hand relational DBMS make use of function shipping architecture. This is also known as query shipping. In query shipping client ships a query to the server. Server receives the query, executes it, processes the result and sends the result to the client. It has better parallelization property. In query shipping the query is executed at the place where the data is actually residing. Function shipping is suitable for systems when the server resources are at plentiful and hence can be exploited without much concern. Also if the queries are highly selective which might be the case in RDBMS, the performance of query shipping architecture tends to be better. Also, function shipping directly provides associative access to data. This is because it reduces the communication cost in this case. Also, at times the client machines are low cost and poorly performing and query processing allows one to make use of such client machines. Hence, in the case of RDBMS where the client machines can be not so powerful function shipping architecture is very useful. It helps when the database information at client is not so good and provides significant performance benefits. Also, this approach is limited to single server machines and has not been tested in the scenarios which have multiple servers involved. In query shipping technique if there is producer of the inner relation or producer of the outer relation, one can easily apply the join operator to either. The primary copy or the main copies of the various relations that are placed at the server and one can apply scan operators at that point. Barring the display operators all the others can be applied when one talks about the site of the producer.

Both data shipping architecture and function shipping architecture work for providing the final processed query to the end user but the approach taken by them is different. One processes the query on the client while the other processes the query on the server. The DBMS model which can either be client/server or relational DBMS but the performance requirement out of both is different. One requires faster results while the other has certain limitations when it comes to hardware availability and hence on the basis of requirement or availability one decides that which shipping architecture needs to be used. In a system that requires faster response to requests one should go for data shipping which involves processing at client end and hence quicker results while in cases when the client machine is not strong and cannot take that level stress one should go for query shipping or function shipping where the responsibility of processing the query lies on the server and server receives the query, executes it and returns the result set to the client.

Ans. 4

Difference between horizontal and vertical partitioning

The process of dividing a database into its constituents is known as partitioning. The resulting constituent elements are independent parts which can exist on their own. At times it is done to improve the database performance or to make the database manageable or for any other business need.  It makes the database information more efficient and easy to manage and also simpler to analyze. Hence, it is considered to be good in the case of both data warehouses and online transaction processing systems. Storing a large amount of database is a very expensive process as amount has to be spent on maintaining the data. Therefore it is better to store the data into smaller partitioned tables. With the help of partitioning one can access data at finer and granular level. Table is partitioned if it has more than 2GB of data or if it contains large amount of historical data or if it has to be distributed across multiple storage devices. It finds benefits at many levels and hence is used frequently in RDBMS (Relational database management systems) as well as ODBMS (Object database management system).

On a very basic level partitioning can be done horizontally or vertically. In simple terms if the database is partitioned on the basis of rows then it is called horizontal partitioning. And if the table is divided into multiple tables on the basis of columns then it is known as vertical partitioning. Horizontally partitioned tables that are generated from a base table have less number of rows than the main table but in the case of vertically partitioned table it is the number of columns that are limited in the new generated tables. This is the major differentiating point in the case of horizontal and vertical partitioning.  

When we start analyzing both the type of partitioning in detail we find out that in the case of horizontal partitioning a single table is divided into multiple tables but the number of rows in each table is different than the base table. In every new table the number of columns is same but the rows are less. For e.g. there can be a table with 10,000 rows. If it is horizontally divided into 10 different tables then each table might have different rows depending upon the requirement. Horizontal partitioning results in groups of physical row based databases and we can individually or collectively access these databases. Horizontally partitioned set of tables will have all the columns of the base table and in this case none of the table attributes are missing. If there is table that has data of a hotel for past 10 years then one can horizontally divide the tables for each year. The new tables will be having lesser number of rows than the original table but the number of columns in this case remains constant.

On the other hand when one goes for vertical partitioning of a table which has thousand of columns than the resulting child tables will have different number columns which have been decided on the basis of what the business requires it to be like. This partitioning method reduces the width of the table as the resulting datasets have only few columns, but the number of rows in each resulting dataset is the same. For example, there can be table with employee data that has been arranged across different columns. If one wants the family details only then the partition should be done vertically. Different table can be created. Some with family details, other with experience details while another one can have the health records. Hence, the number of rows remains the same but the columns are different in each resulting table.

Other points of differentiation are also there between horizontal and vertical partitioning. The difficulty level of the partitioning process is different for each. When it comes to that, vertical partitioning is considered to be slightly more difficult than horizontal partitioning because it has a comparatively large solution space. Both minimize the irrelevant IO, linear programming does this by exploiting the parallelism that exists between transactions. And the different parts of the objects are accessed by these transactions. On the other hand horizontal partitioning minimises IO by putting a check on the number of non relevant objects that are being accessed by the transactions. In the case of horizontal partitioning the methods are easier to handle because in each transaction these methods can be replicated, but in the case of vertical partitioning this is difficult as each object has to be further fragmented.  

Scalability is another factor by which we can differentiate vertical and horizontal partitioning. Horizontal partitioning is more scalable than vertical partitioning as in the case of vertical partitioning the number of partitions are bound by the number of attributes in the class but in the case of horizontal partitioning the number of objects in a class determines the number of partitions that will occur. Horizontal partitioning are more suitable for OLTP or online transaction processing systems as compared to vertical partitioning which finds more usage in the case of decision support systems. When it comes to ease of implementation then it is found that horizontal partitioning is an easier process than vertical partitioning. Vertical partitioning faces a lot of problems because of the fact that we need to make partitions depending upon the attributes in the class which at times can be a complicated process.

Thus, we saw what is partitioning and why database administrators go for it. The benefits of it and the different IT technology to perform partitioning of a database based on business needs and requirements.  But majorly we talked about the different types of partitioning (horizontal and vertical) and what are the major point of differentiation in both the cases which is on the basis of difficulty, scalability, ease of implementation and others aspects too. And finally we saw that in which case we need to choose the horizontal and technique and when the vertical partitioning technique can be more beneficial.