PropertySale is for PropertyForSale.Owner owns provides (rent to) PropertyForRent or owns PropertyForSaleOLTP schemaDATA WAREHOUSING AND DATA MINING MARK VROOMAN AND AMINAT ONIPROJECT REPORT KENNESAW STATE UNIVERSITYDEPARTMENT OF COMPUTER SCIENCECS8630- DATABASE ADMINISTRATION 1Step 1: Reason for the data warehouse Beazerhomes is a builder that has properties all over the United States. They have been building homes and apartments since 1995 and they feel the need to have a data warehouse to help record and store what the selling and renting trends and patterns for their buildings are and have been. This will help top management make decisions such as where to build more properties, which properties to sell and which to rent out, what incentives make their buildings high sellers, and what prices to put on the buildings to maximize profit and at the same time, encourage buyers. The data warehouse will have top management access that will include cover all sections,middle management access that will cover areas they manage, and staff access for their individual sections of work. It will allow for simple queries on past and present data. 2Step 2: Description of the E-R diagram (Also see attachment)Promotion uses Advert displayed in the Newspaper, which recommends PropertyForRent and promotes PropertyForSale. Advert describes PropertyForSale. ClientRenter holds Lease and seeks PropertyViewing.Branch registers ClientRenter and offers PropertyForRent, sells PropertyForSale, has Staff, and contacts ClientBuyer.ClientBuyer requests PropertyViewing and agrees to PropertySale. PropertySale is for PropertyForSale.PropertyForRent is placed in Advert and takes PropertyViewing. It is also leased via Lease and requires PropertyMaintenance.3Staff oversees PropertyForRent, manages PropertyForSale, and attends to PropertyMaintenance.Owner owns provides (rent to) PropertyForRent or owns PropertyForSalePropertyViewing views PropertyForSale. 4Step 3: Relational Schema Data warehouse5OLTP schema6Step 4: Data Dictionary (See attachment)Step 5: QueriesSELECT COUNT (timeID)FROM PropertySaleWhere timeID = (SELECT (timeID) FROM Time WHERE year BETWEEN ‘2001’ AND ‘2003’);This query will provide a count of the number of properties that were sold between the beginning of 2001 and the end of 2003. The BETWEEN command includes the endpointsof the range.SELECT SUM (sellingPrice) AS TotalRevenueFROM PropertySaleWhere timeID = (SELECT (timeID) FROM Time WHERE year BETWEEN ‘2001’ AND ‘2003’);This query will give the total of sales (TotalRevenue) for the dates from the beginning of 2001 to the end of
View Full Document
Unlocking...