1 / 19

Database Applications

Database Applications. Cynthia Marsh Gwen Marlor Byron McCluney. TenMan Systems, Inc. TenMan operates a larger real estate investment trust that owns thousands of commercial properties. TenMan’s computer system needs include: The calculated monthly lease payments Billing the tenants

adsila
Download Presentation

Database Applications

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Applications Cynthia Marsh Gwen Marlor Byron McCluney

  2. TenMan Systems, Inc. • TenMan operates a larger real estate investment trust that owns thousands of commercial properties. • TenMan’s computer system needs include: • The calculated monthly lease payments • Billing the tenants • Posting the cash received • Managing overdue accounts

  3. Lease Payments • The information needed from a tenant includes the following: • Tenant Name, Address, Phone Number, Contact Information, Lease Start Date, Lease End Date. • A lease always starts at the beginning of a month and ends at the end of the month.

  4. Type Costs • Along with the monthly rent the tenant will be charged for the following: • (R, Rent) • (I, Insurance) • (T, Property Tax) • (P, Percentage Rent)-Calculated Annually • Charged Monthly • ((AnnualSales – BaseSales)*PercentageRate)/12

  5. DBDL for Ten-Man Systems Tenant(TenantNum, Name, Street, City, State, Zip, Phone, ContactPerson, LeaseStart, LeaseEnd, BaseSales, PercentRate)

  6. DBDL for Ten-Man Systems CostType(Type, Description) RentCosts(RentCostNum, TenantNum, Type, Amount, StartDate, EndDate) FK TenantNum -> Tenant FK Type -> CostType

  7. DBDL for Ten-Man Systems AR(ARNum, InvoiceNum, Type, TenantNum, Date, Amount) FK TenantNum -> Tenant FK Type -> CostType

  8. DBDL for Ten-Man Systems ARHistory(ARNum, InvoiceNum, Type, TenantNum, Date, Amount) FK TenantNum -> Tenant FK Type -> CostType TenantSales(TenantNum, Date, Amount) FK TenantNum -> Tenant

  9. ER Diagram for Ten-Man Systems

  10. Monthly Events (Queries) A query that shows lease that will expire within the next 12 months: • SELECT * • FROM Tenant • WHERE LeaseEnd < '2005-03-31' + INTERVAL 12 MONTH; or • SELECT * • FROM Tenant • WHERE LeaseEnd < DATE_SUB(CURDATE(),INTERVAL 12 MONTH);

  11. Monthly Events (Queries) A query that shows the current percentage rent charges, with the largest charges first: • SELECT * • FROM AccountsReceivable • WHERE Type = 'P' • AND MONTH(ARDate) = 02 • AND YEAR(ARDate) = 2005 • ORDER BY Amount DESC; or • SELECT * • FROM AccountsReceivable • WHERE Type = 'P' • AND YEAR(ARDate) = YEAR(CURDATE()) • AND MONTH(ARDate) = MONTH(CURDATE()) • ORDER BY Amount DESC;

  12. Monthly Events (Queries) A query that shows all charges unpaid during the current month: • SELECT AccountsReceivable.TenantNum, TName, SUM(Amount) • FROM AccountsReceivable, Tenant • WHERE AccountsReceivable.TenantNum = Tenant.TenantNum • AND YEAR(ARDate) = 2004 • AND MONTH(ARDate) = 12 • GROUP BY AccountsReceivable.TenantNum • HAVING SUM(Amount) > 0;

  13. Monthly Events (Queries) A query that shows all charges unpaid during the current month: Or you can: • SELECT AccountsReceivable.TenantNum, TName, SUM(Amount) • FROM AccountsReceivable, Tenant • WHERE AccountsReceivable.TenantNum = Tenant.TenantNum • AND YEAR(ARDate) = YEAR(CURDATE()) • AND MONTH(ARDate) = MONTH(CURDATE()) • GROUP BY AccountsReceivable.TenantNum • HAVING SUM(Amount) > 0;

  14. Monthly Events (Queries) A query that shows all charges unpaid during all previous months. The oldest will appear first: • SELECT AccountsReceivable.TenantNum, TName, SUM(Amount) • FROM AccountsReceivable, Tenant • WHERE AccountsReceivable.TenantNum = Tenant.TenantNum • AND ARDate < '2005-03-31' • GROUP BY AccountsReceivable.TenantNum • HAVING SUM(Amount) > 0 • ORDER BY Amount DESC;

  15. Monthly Events (Queries) A query that shows all charges unpaid during all previous months. The oldest will appear first: or • SELECT AccountsReceivable.TenantNum, TName, SUM(Amount) • FROM AccountsReceivable, Tenant • WHERE AccountsReceivable.TenantNum = Tenant.TenantNum • AND ARDate < CURDATE() • GROUP BY AccountsReceivable.TenantNum • HAVING SUM(Amount) > 0 • ORDER BY Amount DESC;

  16. Monthly Events (Queries) A query that displays the accounts receivable history showing the number of days taken to pay an individual charge. This query will be ordered with the greatest number of days first: CREATE TEMPORARY TABLE tempDays1 SELECT TenantNum, Min(ARDate) AS minDate FROM ARHistory GROUP BY TenantNum, InvoiceNum; CREATE TEMPORARY TABLE tempDays2 SELECT TenantNum, MAX(ARDate) AS maxDate FROM ARHistory GROUP BY TenantNum, InvoiceNum; SELECT tempDays1.TenantNum, DATEDIFF(tempDays2.maxDate, tempDays1.minDate) AS NumberOfDays FROM tempDays1, tempDays2 WHERE tempDays1.TenantNum = tempDays2.TenantNum GROUP BY tempDays1.TenantNum;

  17. Summary • What Did We Learn? • Filling a database with data can be a complex difficult process • Developing a good plan is important • Find Tables • DBDL and ER Diagrams • Make a Script to Create and to Drop Tables

  18. Summary Continued • What Did We Learn? • Prepare yourself for hours of debugging • Small things are easy to miss at first • Test a piece at a time • Queries can be more challenging than they appear at first • Work it out on paper or white board • Know what the results should look like • Test until query produces desired output

  19. The End…

More Related