research the following: Data modeling Benefits of SQL for application processing
ID: 3661407 • Letter: R
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?
Explanation / Answer
View: A view is a virtual table in the database with a name. As the name suggests, it lets a user view a database from a view of his own. It lets users to see specific rows and columns based on their permissions. From the perspective of a user a view is a regular column in a database. However, view in reality is just a result of a query. Every view has source tables from which it gets its data. CREATE VIEW statement: CREATE VIEW statement is used for creating a view. For creating a view you must have access to the source tables. While creating a view, each column can be named. Length, data type, and other features of each column are derived from the definition of the columns in the source tables. For Example: CREATE VIEW AVGSAL AS SELECT EMPL_NUM, SALARY FROM EMPLOYEES WHERE SALARY >(SELECT AVG(SALARY) FROM EMPLOYEES) Advantages of views: 1. View the data without storing the data into the object. 2. Restict the view of a table i.e. can hide some of columns in the tables. 3. Join two or more tables and show it as one object to user. 4. Restict the access of a table so that nobody can insert the rows into the table. Disadvatages: 1. Can not use DML operations on this. 2. When table is dropped view becomes inactive.. it depends on the table objects. 3. It is an object, so it occupies space. Pls. add , if I miss any of them. Thanks, Mohan Database views are virtual tables which are built up using a SELECT query. The query may contain one or more tables. To help explain database views, here is a quick script created using SQL Server to create some tables and data. (If you want to create the tables using MySQL, then substitute the part “IDENTITY(1,1)” with “AUTO_INCREMENT”) CREATE TABLE Department ( ID TINYINT PRIMARY KEY IDENTITY(1,1), Department VARCHAR(50) NOT NULL ) GO CREATE TABLE Staff ( ID INT PRIMARY KEY auto_increment, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, Salary INT NOT NULL, DepartmentID TINYINT NOT NULL REFERENCES Department(ID) ) GO INSERT INTO Department(Department) VALUES('Sales'),('Finance'),('Technology'); INSERT INTO Staff(FirstName, LastName, Salary, DepartmentID) VALUES ('John','Smith',50000,1) ,('Raymond','Jones',50000,2) ,('Tracey','Carter',50000,3); CREATE VIEW vwStaff AS SELECT S.ID AS StaffID , S.FirstName , S.LastName , D.Department FROM Staff S JOIN Department D ON S.DepartmentID = D.ID GO GRANT SELECT ON vwStaff TO application_developer; Instead of selecting from the tables, the application developer would select from the view “vwStaff” to retrieve the data they needed. SELECT * FROM vwStaff StaffID FirstName LastName Department ----------- -------------------- -------------------- -------------------- 1 John Smith Sales 2 Raymond Jones Finance 3 Tracey Carter Technology (3 row(s) affected) There is no performance difference between running the SQL or running database views. If you look at both SQL Server and MySQL, the execution plans are identical between the SQL and database views. MySQL…. 1 mysql> 2 explain SELECT S.ID AS StaffID 3 , S.FirstName 4 , S.LastName 5 , D.Department 6 FROM Staff S 7 JOIN Department D ON S.DepartmentID = D.IDG; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: S type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: D type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 1 ref: dbadiaries.S.DepartmentID rows: 1 Extra: 2 rows in set (0.00 sec) 1 mysql> explain SELECT * FROM vwStaffG; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: S type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: D type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 1 ref: dbadiaries.S.DepartmentID rows: 1 Extra: 2 rows in set (0.00 sec) SQL Server…. Advantages of Database Views • Data protection – create views to protect sensitive data. For example the salary column was hidden in my example above. • Code re-use – simplifies application development. If some business logic changes for presenting data, change it once in the view and not many times in different places. • Simplifies access to data for untrained users. Lets say you have a department of data analysts who are experts with Excel but can’t write SQL. Create them a view of the data and have them load that into Excel. They can’t write SQL but they can choose a view from a list of available datasets – everyone is happy. • Performance – lets say your data analysts can write SQL but you don’t want them writing run-away queries which bring your database server down. Create them a view instead of allowing them direct access to the data. • If you are using BCP to export data, you can format the data through views as BCP data formatting is rather limited. • Working around non-deterministic function limitations. Pitfalls of using Database Views • Ease of use – you’re probably thinking, what’s he talking about? Well it is very easy to think “I’ll join to that view I created in the database to satisfy this new query I am writing”. You have to remember that database views are SELECT queries. Every time you run one or join to one, the query is run on the fly to produce the results of the view. As an author of a query and using the view above, if you only needed access to the data in the “Staff” table to form part of your query, why join to a view which pulls in data from the “Department” table? That is extra IO overhead which when dealing with large volumes of data or frequent batch requests can have negative performance implications. It is better to have access to either the table (if you are allowed to) or another view of the “Staff” table which allows you to view the fields which you require and use that. • Nested database views – don’t create views based on other views as this has a negative performance impact. Better to create views from the base tables.
Diagnosing Problems with Database Links If you think a database link is not functioning properly, then you can drop and re-create it using the Oracle Enterprise Manager Console, SQL*Plus, or another tool. • Make sure that the database link name is the same as the global name of the target database. • Make sure that the scheduled interval is what you want. • Make sure that the scheduled interval is not shorter than the required execution time. If you used a connection qualifier in a database link to a given site, then the other sites that link to that site must have the same connection qualifier. For example, suppose you create a database link as follows: CREATE DATABASE LINK dbs1.world@myethernet CONNECT TO repadmin IDENTIFIED BY secret USING 'connect_string_myethernet' All the sites, whether masters or materialized views, associated with dbs1.world@myethernet must include myethernet as the connection qualifier. See Also: Oracle Database Administrator's Guide for more information database links and connection qualifiers Diagnosing Problems with Master Sites Problems can arise in a multimaster replication system. The following sections discuss some problems and ways to solve them: • Replicated Objects Not Created at New Master Site • DDL Changes Not Propagated to Master Site • DML Changes Not Asynchronously Propagated to Other Sites • DML Cannot be Applied to Replicated Table • Bulk Updates and Constraint Violations • Re-creating a Replicated Object • Unable to Generate Replication Support for a Table • Problems with Replicated Procedures or Triggers Replicated Objects Not Created at New Master Site If you add a new master site to a master group, and the appropriate objects are not created at the new site, then try the following: • Ensure that the necessary private database links exist between the new master site and the existing master sites. If you used the Replication Management tool's Setup Wizard to setup your sites, then you should not have any problems. You must have links both to the new site from each existing site, and from the new site to each existing site. • Make sure that the administrative requests at all sites have completed successfully. If requests have not been executed yet, then you can manually execute pending administrative requests to complete the operation immediately. DDL Changes Not Propagated to Master Site If you create a new master group object or alter the definition of a master group object at the master definition site and the modification is not propagated to a master site, then first ensure that the administrative requests at all sites have completed successfully. If requests are pending execution, then you can manually execute them to complete the operation immediately. When you execute DDL statements through the replication API, Oracle executes the statements on behalf of the user who submits the DDL. When a DDL statement applies to an object in a schema other than the submitter's schema, the submitter needs appropriate privileges to execute the statement. In addition, the statement must explicitly name the schema. For example, assume that you supply the following as the ddl_text parameter to the DBMS_REPCAT.CREATE_MASTER_ REPOBJECT procedure: CREATE TABLE oe.new_employees AS SELECT * FROM hr.employees WHERE ...; Because each table name contains a schema name, this statement works whether the replication administrator is oe, hr, or another user, as long as the administrator has the required privileges. ________________________________________ Note: Qualify the name of every schema object with the appropriate schema. ________________________________________ DML Changes Not Asynchronously Propagated to Other Sites If you make an update to your data at a master site, and that change is not asynchronously propagated to the other sites in your replication environment, then try the following: • Use the Replication Management tool in the Oracle Enterprise Manager Console to check whether the corresponding deferred transaction has been pushed to the destination. If not, then you can also check to see how much longer it will be before the scheduled link pushes the queue to the destination site. If you do not want to wait for the next scheduled push across a link, then you can execute deferred transaction manually. • If a scheduled link's interval has passed and corresponding deferred transactions have not been pushed, then check the corresponding job for the link. • Even after propagating a deferred transaction to a destination, it may not execute because of an error. Check the DEFERROR data dictionary view at the destination site for errors. See Also: Oracle Database Advanced Replication Management API Reference for information about modifying tables without replicating the modifications, which may be necessary when you need to manually synchronize the data in replicated tables DML Cannot be Applied to Replicated Table If you receive the deferred_rpc_quiesce exception when you attempt to modify a replicated table, then the master group to which your replicated object belongs is quiescing or quiesced. To proceed, your replication administrator must resume replication activity for the master group. Bulk Updates and Constraint Violations A single update statement applied to a replicated table can update zero or more rows. The update statement causes zero or more update requests to be queued for deferred execution, one for each row updated. This distinction is important when constraints are involved, because Oracle effectively performs constraint checking at the end of each statement. While a bulk update may not violate a uniqueness constraint, for example, some equivalent sequence of individual updates may violate uniqueness.
If the ordering of updates is important, then update one row at a time in an appropriate order. This lets you define the order of update requests in the deferred transactions queue. Re-creating a Replicated Object If you add an object such as a package, procedure, or view to a master group, then the status of the object must be valid. If the status of an object is invalid, then recompile the object or drop and re-create the object before adding it to a master group. Check the DBA_REPOBJECT data dictionary view for the status of replication objects. Unable to Generate Replication Support for a Table When you generate replication support for a table, Oracle activates an internal trigger at the local site. EXECUTE privileges for most of the packages involved with replication, such as DBMS_REPCAT and DBMS_DEFER, need to be granted to replication administrators and users that own replicated objects. The Replication Management tool's Setup Wizard and the DBMS_REPCAT_ADMIN package both perform the grants needed by the replication administrators for many typical replication scenarios. When the owner of a replicated object is not a replication administrator, however, you must explicitly grant EXECUTE privilege on DBMS_DEFER to the object owner. Problems with Replicated Procedures or Triggers If you discover an unexpected unresolved conflict, and you were mixing procedural and row-level replication on a table, then carefully review the procedure to ensure that the replicated procedure did not cause the conflict. Complete the following checks: • Ensure that ordering conflicts between procedural and row-level updates are not possible. • Check if the replicated procedure locks the table in EXCLUSIVE mode before performing updates or uses some other mechanism of avoiding conflicts with row-level updates. • Check that row-level replication is disabled at the start of the replicated procedure and re-enabled at the end. • Ensure that row-level replication is re-enabled even if exceptions occur when the procedure executes. • Check to be sure that the replicated procedure executed at all master sites. You should perform similar checks on any replicated triggers that you have defined on replicated tables. Diagnosing Problems with the Deferred Transaction Queue If deferred transactions at a site are not being pushed to their destinations, then the following sections explain some possible causes for the problem: • Check Jobs for Scheduled Links • Distributed Transaction Problems with Synchronous Replication • Incomplete Database Link Specifications • Incorrect Replication Catalog Views Check Jobs for Scheduled Links When you create a scheduled link, Oracle adds a corresponding job to the site's job queue. If you have scheduled a link to push deferred transactions at a periodic interval, and you encounter a problem, then you should first be certain that you are not experiencing a problem with the job queue. Distributed Transaction Problems with Synchronous Replication When you use synchronous replication, Oracle uses a distributed transaction to ensure that the transaction has been properly committed at the remote site. Distributed transactions use two-phase commit. Asynchronous replication does not use two-phase commit. See Also: Oracle Database Administrator's Guide for information on diagnosing problems with distributed transactions Incomplete Database Link Specifications If you notice that transactions are not being pushed to a given remote site, then you may have a problem with how you have specified the destination for the transaction. When you create a scheduled link, you must provide the full database link name. Incorrect Replication Catalog Views Having the wrong view definitions can lead to erroneous deferred transaction behavior. The DEFCALLDEST and DEFTRANDEST views are defined differently in catdefer.sql and catrepc.sql. The definitions in catrepc.sql should be used whenever replication is used. If catdefer.sql is ever (re)loaded, then ensure that the view definitions in catrepc.sql are subsequently loaded. Diagnosing Problems with Materialized Views There are a number of problems that might happen with materialized view sites in a replication system. The following sections discuss some problems and ways to troubleshoot them: • Problems Creating Replicated Objects at Materialized View Site • Refresh Problems • Advanced Troubleshooting of Refresh Problems Problems Creating Replicated Objects at Materialized View Site If you unsuccessfully attempt to create a new object at a materialized view site, then try the following: • For an updatable materialized view, check that the associated master table or master materialized view has a materialized view log. • Make sure that you have the necessary privileges to create the object. For a materialized view, you need SELECT privilege on the master table or master materialized view and its materialized view log. See "Assign Privileges" for more information. • If you are trying to add an existing materialized view to a materialized view group, then try re-creating the materialized view when you add it to the group. • If you are trying to create a fast refresh primary key or subquery materialized view, then make sure that the materialized view log on the master table or master materialized view logs primary keys. • If you are trying to create a fast refresh rowid materialized view, then make sure that the materialized view log on the master table logs rowids. • Check if the materialized view log has the required columns added for subquery materialized views. See "Logging Columns in the Materialized View Log" for information. • Check if the materialized view log exists for all tables that are involved in a fast refresh materialized view. If the materialized view contains a subquery, then each table referenced in the subquery should have a materialized view log. Problems Performing Offline Instantiation of a Deployment Template If you receive and error stating that Oracle is unable to initialize the extent in the temporary tablespace when you try to instantiate a deployment template offline, then you may need to adjust the datafile for the temporary database so that it auto extends. For example, issue the following statement to adjust the datafile: ALTER DATABASE TEMPFILE '/u02/oracle/rbdb1/temp.dbf' AUTOEXTEND ON NEXT 10M; After you have made this adjustment, instantiate the deployment template offline at the materialized view site. Refresh Problems The following sections explain several common materialized view refresh problems. Common Problems Several common factors can prevent the automatic refresh of a group of materialized views: • The lack of a job queue process at the materialized view database • An intervening network or server failure • An intervening server shutdown When a materialized view refresh group is experiencing problems, ensure that none of the preceding situations is preventing Oracle from completing group refreshes. Automatic Refresh Retries When Oracle fails to refresh a group automatically, the group remains due for its refresh to complete. Oracle will retry an automatic refresh of a group with the following behavior: • Oracle retries the group refresh first one minute later, then two minutes later, four minutes later, and so on, with the retry interval doubling with each failed attempt to refresh the group. • Oracle does not allow the retry interval to exceed the refresh interval itself. • Oracle retries the automatic refresh up to sixteen times. If after 16 attempts to refresh a refresh group Oracle continues to encounter errors, then Oracle considers the group broken. The General page of the Refresh Group property sheet in Schema Manager indicates when a refresh group is broken. You can also query the BROKEN column of the USER_REFRESH and USER_REFRESH_CHILDREN data dictionary views to see the current status of a refresh group. The errors causing Oracle to consider a materialized view refresh group broken are recorded in a trace file. After you correct the problems preventing a refresh group from refreshing successfully, you must refresh the group manually. Oracle then resets the broken flag so that automatic refreshes can happen again. The entity-relationship model (or ER model) is a way of graphically representing the logical relationships of entities (or objects) in order to create a database. The ER model was first proposed by Peter Pin-Shan Chen of Massachusetts Institute of Technology (MIT) in the 1970s
Enhanced Entity-Relationship model features Although most properties of entities and relationships can be expressed using the basic modeling constructs, some of them are costly and di_cult to express (and to understand). That's why there are some extensions to the ER model. Subclasses, Superclasses, and Inheritance _ In some cases, an entity type has numerous subgroupings of its entities that are meaningful and need to be represented explicitly because of their signi_cance to the DB application. ISA PERSON GPA STUDENT Major SSN Name _ Relationships and attributes of superclass are inherited to subclass (in particular primary key attribute(s)); subclass can have additional attributes and relationships _ An entity cannot exist merely by being a member of only a subclass. Specialization _ Process of de_ning a set of subclasses of an entity type (top-down) EMPS is a subclass of EMPLOYEES and thus inherits its attributes and relationships (same for CONTRACT EMPS). Generalization: _ Reverse process of specialization (bottom-up); identify common features of entity types and generalize them into single superclass (including primary key!) Constraints on Specialization _ disjointness and totality constraints disjoint, total/partial: each entity in the superclass must/can be in exactly one subclass The disjointness constraint is indicated by the word disjoint" right next to the ISA triangle The totality constraint is indicated by double lines leading from the superclass to the ISA triangle _ overlapping constraints overlapping, total (. . . must be in at least one subclass) In this case, only double lines leading to the ISA triangle are used. overlapping, partial (. . . can be in at least one subclass) _ Note: special rules are required to propagate deletions from superclass/subclass (implemented later). Note that for generalization, each entity in the superclass must belong to exactly one subclass • ER models assume information content that can readily be represented in a relational database. They describe only a relational structure for this information. • They are inadequate for systems in which the information cannot readily be represented in relational form, such as with semi-structured data. • For many systems, possible changes to information contained are nontrivial and important enough to warrant explicit specification. • Some[who?] authors have extended ER modeling with constructs to represent change, an approach supported by the original author;[19] an example is Anchor Modeling. An alternative is to model change separately, using a process modeling technique. Additional techniques can be used for other aspects of systems. For instance, ER models roughly correspond to just 1 of the 14 different modeling techniques offered by UML. • ER modeling is aimed at specifying information from scratch. This suits the design of new, standalone information systems, but is of less help in integrating pre-existing information sources that already define their own data representations in detail. • Even where it is suitable in principle, ER modeling is rarely used as a separate activity. One reason for this is today's abundance of tools to support diagramming and other design support directly on relational database management systems. These tools can readily extract database diagrams that are very close to ER diagrams from existing databases, and they provide alternative views on the information contained in such diagrams. • In a survey, Brodie and Liu[20] could not find a single instance of entity–relationship modeling inside a sample of ten Fortune 100 companies. Badia and Lemire[21] blame this lack of use on the lack of guidance but also on the lack of benefits, such as lack of support for data integration. • The enhanced entity–relationship model (EER modeling) introduces several concepts not in ER modeling, but are closely related to object-oriented design, like is-a relationships. • For modelling temporal databases, numerous ER extensions have been considered.[22] Similarly, the ER model was found unsuitable for multidimensional databases (used in OLAP applications); no dominant conceptual model has emerged in this field yet, although they generally revolve around the concept of OLAP cube (also known as data cube within the field)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.