Question

research the following:

• Data modeling
• Benefits of SQL for application processing

Based on your research and understanding, complete the following discussions:

• Discuss the benefits of views. Examine how views can affect database usability by considering both positive and negative aspects. Provide examples from business cases to support your response.
• Evaluate and discuss the given comment: "The database is slow and does not provide the data I need. Using this system makes my job harder."
  • Based on your evaluation, answer the following questions:
    • What could be the possible reasons behind the problems the user has described?
    • What would be your recommendations for addressing these problems? Why?
• Consider how business processes can change over time. Discuss the effects of these changes on database design and ongoing development in the area. Research and include at least two examples to support your claims.
• Consider the given statement: "When I provided the business requirements to the database development team, the team confirmed that it understood what I needed. However, the entity-relationship (E-R) model does not support the business requirements, and I now have to redesign the system." How would you resolve this issue? What are some possible solutions to the stated problem?

Answer 1

solution;

: Data modeling- It is basically a process of creating a data model for an information system or we can say that it is a process of learning about data and data model is the end result of data modeling process.

A database model is a type of data model that determines the logical structure of a database and fundamentally determines in which manner data can be stored, organized, and manipulated. The most popular example of a database model is the relational model,which uses a table-based format.

Example- RDBMS- Relational database management system - is a database management system based on the relational model. This is also called SQL model.

SQL- it is language used by relational databases to query, update and manage data.

Views:- A view is a virtual table based on the result set of an SQL statement. A view contains rows and columns , just like a real table.

or generally speaking , a table has a set of definition and it physically stores the data. A view also has a set of definitions , which is build on top of table(s) or other view(s) and it does not physically stores the data.

Benefits of views:-

1. View can represent a subset of the data contained in a table. Consequently a view can limit the degree of exposure of the underlying tables to the outer world, a given user may have permission to query the view, while denied access to the rest of the base table.

2. Join two or more tables and show it as one object to user.

3. Views can hide the complexity of data. For example, a view could appear as Student1 or College15, transparently partitioning the actual underlying table.

4. View needs very less space. Only view definition needs the database space where it does not store any data in it.

5. Depending on the SQL engine used, views can provide extra security.

Views also has some disadvantages , which are as follows:-

A query fired on a view will run slower than a query fired on a base table ,this is because the view definition has to be retrieved from the oracle's system catalog,the base table has to identified and opened in memory and then view has to be constructed on the top of the base table,suitably masking table columns.

View when called always contacts the base table, on which it is built to get the data .so it always goes to the server thats why it degrades the performance of the server.

When Table gets deleted then view remains with no data.
When table gets dropped the view still remains but cannot be accessed (Could not use view or function View_Name because of binding errors).
Later when we recreate the table which was dropped earlier can be accessed using the view which we created without recreating it.

"The database is slow and does not provide the data I need. Using this system makes my job harder."

1.There are two things that impact query performance are data volume and user/transaction concurrency.

So what exactly causes SQL statements to run slow and how do you make them run faster?

2. (a) Bad SQL:- Despite many databases having query optimizers, human inefficiency can’t be ignored. As an example, anyone who writes SELECT * should really be given a public flogging these days. For developers, it’s easy to focus on getting the right result from a query, rather than thinking how long the query might take in production with real data. Performing several table joins on a dev database with small sets of data will always run quickly; executing the same query in production with millions of rows in tables could grind an application to a halt. Cursors are another abused function, which again can be an expensive operation. They help developers iterate through result sets but can waste system resource if they are not properly implemented.

(b). Bad Database Design:- Sounds obvious, but a badly designed database schema will often contribute to bad database performance. Too little or too much normalization can mean the database and your queries have to work harder to get the data you need. A bad database design can increase the complexity of SQL queries and the cost of change down the road. Joins
[1:01 AM, 7/25/2019] mounikasondemanju: can be expensive operations on big data sets, so it’s best to fully understand the needs of the application(s) and its data before jumping into building schemas.

(c). Not Using Database Features:- Stored procedures require a bit more effort but make applications less complex and costly to maintain. Databases are good at processing, managing, and querying data–way more efficient than any developer embedding SQL queries in application code, which can cause result sets to be dumped in application server memory so they can be iterated through and processed. Less is more when it comes to accessing the database–so keep your hits low, let the database do the work for you, and only bring back the data you need.

Over the last few years we've developed a number of techniques that allow a database design to evolve as an application develops. This is a very important capability for AGILE methodologies. The techniques rely on applying continuous integration and automated refactorig to database development , together with a close collaboration between DBAs and application developers. The techniques work in both pre-production and released systems.

In the last few years, we've seen the rise of a new breed of software methodologies, the agile methodologies. These make some new and significant demands on database design. One of the most central of these demands is the idea of evolutionary design. On an agile project you assume that you cannot fix the requirements of the system up-front. As a result having a detailed design phase at the beginning of a project becomes impractical. The design of the system has to evolve through the various iterations of the software. Agile methods, in particular extreme programming (XP), have a number of practices that make this evolutionary design practical.

Dealing with change:-

One of the primary features of agile methods is their attitude towards change. Most of the thinking about software process is about understanding requirements early, signing off on these requirements, using the requirements as a basis for design, signing off on that, and then proceeding with construction. This is a plan-driven cycle, often referred to (usually with derision) as the waterfall approach.

gile processes approach change differently. They seek to embrace change, allowing changes to occur even late in a development project. Changes are controlled, but the attitude of the process is to enable change as much as possible. In order to make this work, you need a different attitude to design. Instead of thinking of design as a phase, which is mostly completed before you begin construction, you look at design as an on-going process that is interleaved with construction, testing, and even delivery. This is the contrast between planned and evolutionary design.

The Practices:-

(a). DBAs collaborate closely with developers

(b). Everybody gets their own database instance

(c). Developers frequently integrate into a shared master

(d). A database consists of schema and test data

(e). All changes are database refactorings

(f). Automate the refactorings

(g). Automatically Update all Database Developers

(h). Clearly separate all database access code