Reference no: EM132681207
COMP2350 Database Systems - Macquarie University
1. Problem Domain
The problem domain is a simplified portion of what you came across earlier in Assignment 1.
Advertising agency Shine manages its client companies' advertising campaigns. It has a number of staff members with specialized skills. A record is kept of each client company, and of every campaign. Each campaign is based on a unique theme. A Shine staff may work on zero, or one or more campaigns at a time.
When a campaign starts, an estimate of the cost needs to be set and agreed upon. The actual cost of a campaign is calculated from staff time devoted to it. Staff members are paid (and clients charged) based on the hourly rate associated with their pay grade.
When the campaign is completed, an actual completion date and the actual cost are recorded and a single invoice for the whole campaign is sent to the client. The issue date and payment status of the invoice are also recorded. When the client pays, the date paid is recorded.
A relational data model for this domain is provided below to facilitate your understanding of the domain. A DDL script (create_DB.sql) for creating the corresponding database, and a DML script (populate_DB.sql) for populating this database with some sample data are being provided in the Assignment 2 folder. Note that the DDL script will also create another table (not included in the diagram), called alerts, which is meant to contain messages as to which invoices are overdue.
2. Task Specifications
Task One -
Create the tables listed in the relational data model as shown in Figure 1 (attached), as well as the alerts table, by running the provided DDL script provided in the 'Assignment 2' folder. Then insert sample records to the tables by running the provided DML script provided. Verify that the tables are created and populated as intended.
Task 4(Testing) -
This task involves testing the code developed in Task Two and Task Three.
- First you are required to test the programs you wrote against the sample data provided. The tests to be carried out, in the order given, is provided in the provided file: test_script.sql. These tests constitute a minimal test against a very small number of records. Look at this file carefully to understand what is being tested and what should be the expected result. Note the roles of setting autocommit off and the rollback statements.
- Next you carry out a more extensive test by testing the programs against a larger set of records that are designed to easily expose any flaws in your programs. You do that by deleting records, adding records, or modifying the records in other ways, and then calling different procedures. You accordingly modify the file test_script.sql, and save it as <yourLastname_yourFirstname>_test_script.sql, and submit among other files.
Report Specification
Your report should have the following sections:
1. Initial State of the database. Paste to the word file the screen shots showing the provided sample data in the tables.
2. Stored Programs. Paste into this section the programs you wrote (the sql file <yourLastname_yourFirstname>_programs.sql that you prepared).
3. Required Testing against Sample Database. Paste into this section the initial tests you ran (one by one) and then the corresponding results as screenshots. The provided script file lists what tests to run, how, and in which order.
4. More Extensive Testing. Explain what sort of changes you are going to make to which tables, what tests you are going to run, and why. Copy and paste from your file <yourLastname_yourFirstname>_test_script.sql the DML statements you used to modify the tables followed by the screenshots of the records in those tables; then copy and paste the procedure calls you made, and the screenshots of the records in the relevant tables (or the error messages).
5. Notes (optional). In this optional section, you might wish to note anything, such as whether you faced any particular difficulty in completing any of these tasks, the nature and extent of any help you received from anyone, and why.
Attachment:- Database Systems.rar
Darwin gathered information from geology
: Darwin gathered information from geology, paleontology, taxonomy, demography, and evolutionary biology to develop his theory of evolution,
|
What is ending inventory
: ?Assuming that a periodic inventory system is used, what is ENDING INVENTORY (not Cost of Goods Sold) under the weighted average cost method
|
Emerging threats and counter measures
: Web server auditing can go a long way in enforcing tighter security and ensuring business continuity.
|
What is the estimated cost of ending inventory
: The retail selling price for these same goods was $150,000. What is the estimated cost of ending inventory using the retail method
|
Test the programs you wrote against the sample data
: First you are required to test the programs you wrote against the sample data provided. The tests to be carried out, in the order given
|
Calculate the estimated cost of ending inventory
: If the company's products have an average gross profit of 25 percent, calculate the estimated cost of ending inventory, using the gross profit method
|
Determine the balances that appear in the accounts payable
: Determine the balances that appear in the accounts payable subsidiary ledger. What Accounts Payable balance appears in the general ledger at the end of January
|
What is the present value of the machine to her
: After five years she could sell the machine for $4,000. What is the present value of the machine to her
|
How to calculate the non-cash gifts
: How to calculate the non-cash gifts regarding to taxable employee income - Lauren received two non-cash gifts from WEL, a season ski pass worth $400
|