Page 1—Transactions and Reports: Page 1 is the exact same requirement you were r

Question

Page 1—Transactions and Reports:

Page 1 is the exact same requirement you were required to submit for Assignment 4 and you need not change it unless deficiencies were noted in Assignment 4.

Frequent Transactions: List the ten most frequent business events (or use cases) that you feel your organization will perform on a recurring basis ranked from most frequently to least frequently performed. Just state the event, such as "Make sales to customers," "Record receipt of replenishment inventory," "Record employee time card entries," etc. Use a numbered list format with double spacing. (Don't agonize over the exact sequencing of event frequencies but there should definitely be a clear winner for first place.)

Reports: On the same page as the transactions above list the most frequent report you think your organization will need to develop as well as the most important from a managerial information value report your organization will need to generate. Reports are any output from the system designed to be printed. A customer invoice or monthly statement is a report as are a wide range of managerial outputs. Be sure to label which report is the most frequent and which is the most important.

Page 2 (et seq.)—Database structural Modifications:

Create a Word document itemizing the structural changes you would make to your original database for the purposes of improving performance. List tables in alphabetical order followed by a numbered list itemizing the changes and briefly support (provide the reasons for) the your structural changes you propose. You may put more than one table's change information on one page but do not allow a single table's information to break across pages unless it is too large to fit on a single page.

Here is an example of how the submission for the Orders table from NorthWinds might look:

Orders Table:

Horizontally partitioned by year into 2005Orders, 2006Orders, etc. This table is accessed very frequently for shipping and billing purposes but most transactions only require recent orders which are contained in, at most, two tables. Removing old orders to historical tables vastly reduces table and index sizes for this table while retaining needed historical data.

Added the derived attribute TotalSalesTax to the table. Sales taxes are frequently calculated and reported for invoices, other billing documents, and reports to state tax authorities and require access to the huge OrderDetails table. TotalSalesTax is a smallmoney field which does not lengthen the record significantly and the value is both relatively stable once determined and also easy to update when necessary. This allows us to avoid querying OrderDetails for sales tax reporting.

Denormalized the Orders table by including Customer.CompanyName in the Orders record. Two of our most frequently executed reports require access to this information from the Customers table so replicating this information avoids access to the Customers table when these reports are run. [Note: I don't think this is a particularly good example but I wanted to show what specification of a denormalized attribute might look like.]

Note that all three changes would be applied to a single table and that each description lists both the change to be made and the justification. The justifications are not extensive. Note how these justifications addressed frequency.

You will repeat the format illustrated above for each table for which you propose structural changes. If a table does not require any structural modifications you will list it in the correct alphabetical order by stating the table name followed by "No change". E.g.

Employees Table: No change

Do not assume that every table, or even most tables, require modification. You should start your analysis with your most frequent use case analysis, think about the SQL these use cases would need, how this SQL will use the DB, and then the ideas presented in the Module for when structural modification is appropriate.

NorthWind

Professional CE Provide FEN stName stName Provider LastName LastName submit Gende starte Date Address Main Ph ane Numbe Application Enrollment quellifies lment SSN Enrollment Status ioense Type ID Code Renevu License ID Nur Decision Date SectionID Term Qual SSN ApplicationID License Ty Qual Name Section Section structor Provider ID CECourselD Section Number License Degree Type SSN Term School sue Date Null Room icenseTypeID Graduation Date Expiration Date Decision Date Approve dByID ExpirationDate ApplicationID Document Title QualificationType Submission Date Review ByID ApplicantFirst Name Qual cationName ApplicentLast Name DocumentDescription Received From Received Date QualificationID Docume nt Status DMQAStaff DocumentStatusID Received Date Staff ID Not eviewedD Staff FirstName ApprovedDate ines StaffLast Name DisapprovedDate rtmentID pal License ID Renew allD Hire Date Position ID Staff need to relate to LoginID document or qua cation because they approve. One staff member per document. *Determines status Received, ApprovedDate, DisapprovedDate. If the last two are null, then it has been received nstructorEmployment FEI Employment Division cto Instructor1 SSN Instruct arStartDate CE Course QualificationTypeID ourse Name ourse Hou Doc and Qualification License Type Qual Qual nName particular type of license. For that license type, here are the things you have to do.

Explanation / Answer

The ten most business events that my organisation will do is:-

Giving techical support

Daily interaction with clients

Assign resources under that client

Resiolving issues raised

Sending reports of resolved issues and wok done

Billing reports of resources

Setup quarterly meets to report about status of that quarter

Giving demos and quotations for the projects to takeover the project.

Record Billing details of clients.

The report which my organisation makes which are most important is

Employee Table:-

Single database table contains unnormalised data causes performance degradation. In the same way maintaining many tables is also causes performance degradation. Which means when we are creating a table in the database first we have to check whether is really needed to be created.

In this case the above database also contains some extra tables which those can be removed.

NorthWind:-

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.