CS403 Assignment 2 Solution and Discussion

[zaasmi]

GDB Topic:

The Virtual University has a multi-disciplinary approach and offers extensive degree programs in numerous academic disciplines. Although undergraduate and post graduate degrees are awarded in many disciplines, the most sought and highly popular degrees offered by the University are in the fields of Computer and IT. BS, MS and PhD program offered under the department of CS and IT. Students get enrolled and study the relevant courses in a particular semester. VU manages the information about students, degree programs, student’s achievements, course credentials and many more.

Let’s suppose, you are the database designer and asked to design the database of above given scenario.

In order to design the database, you have to choose a data model from the following data models:

Hierarchical data model
Network data model
Relational data model
As a database designer which data model will you choose for the database designing? Justify your answer in either case with only very solid points.

Note: Answer should not exceed 200 words.

GDB Instructions:

You need to provide precise and to the point answer, avoid irrelevant details.
Copied from the internet or another student will get zero marks.
You cannot participate in the discussion after the due date via email.
The GDB will open and close on specified date and time. Please note that no grace day or extra time will be given for posting comments on GDB.

[Khuram Shahzad]

Relational data model
As a database designer, I’ll choose relational data model for the database designing because the relational model represents the database as a collection of relations. A relation is nothing but a table of values. Every row in the table represents a collection of related data values. These rows in the table denote a real-world entity or relationship.
The table name and column names are helpful to interpret the meaning of values in each row. The data are represented as a set of relations. In the relational model, data are stored as tables. However, the physical storage of the data is independent of the way the data are logically organized.
Relational Integrity constraints are referred to conditions which must be present for a valid relation. These integrity constraints are derived from the rules in the mini-world that the database represents.
Four basic update operations performed on relational database model are
Insert, update, delete and select. The Relational database model represents the database as a collection of relations (tables)Attribute, Tables, Tuple, Relation Schema, Degree, Cardinality, Column, Relation instance, are some important components of Relational Model Relational Integrity constraints are referred to conditions which must be present for a valid relation. The relational database is only concerned with data and not with a structure which can improve the performance of the model

[Khuram Shahzad]

https://youtu.be/w723_QQd2vk

[moaaz]

https://youtu.be/vL4Myo1g9v8

[zareen]

Suppose, you are going to develop an application that will be mainly used for data analysis. The database for this application contains huge amount of data which is mainly loaded in batches. This application does not require insertion/updation/deletion of individual records. Our main objective is to improve the performance of application to quickly perform desired analysis.

Now, as a student of the Database Management System, you are going to design the database for above application. In the database design, your main concern will be the “efficiency” in term of searching. Now would you normalize the database or keep your database in de-normalized form.

Justify your answer in either case with proper reasons.

%(red)[Note: Answer should not exceed 200 words.]

[zareen]

@zareen said in CS403 GDB1 Solution and discussion:

Now would you normalize the database or keep your database in de-normalized form.

Although, denormalized schema can greatly improve performance under extreme read-loads but the updates and inserts become complex as the data is duplicate and hence has to be updated/inserted in more than one places.

One clean way to go about solving this problem is through the use of triggers. For example in our case where the %(red)[orders] table has the %(red)[product_name] column as well, when the value of %(red)[product_name] has to be updated, then it can simply be done in the following way:

• Have a trigger setup on the %(red)[products] table that updates the %(red)[product_name] on any update to the %(red)[products] table.
• Execute the update query on the %(red)[products] table. The data would automatically be updated in the %(red)[orders] table because of the trigger.

However, when de-normalizing the schema, do take into consideration, the number of times you would be updating records compared to the number of times you would be executing SELECTs. When mixing normalization and de-normalization, focus on de-normalizing tables that are read intensive, while tables that are write intensive keep them normalized.

link text