BCIS 338 Business Information Systems I
Summer I 2020
DATABASE PROJECT
Instructions:
Please develop an MS Access database, which addresses the business requirements defined in the business case below. Please upload your completed MS Access database file to the proper Canvas assignment after you rename the file by adding your last name and first name.
Business Case:
This mini-project is a (very) simplified version of a real-life business problem, where a small-size real estate company’s owner can no longer control the flow of information within the company due to significant growth in business. As a result, the owner hired a consultant to understand the options to address this growing issue. After a series of discussions with the owner, along with the independent observation of the business processes, the consultant decided that a database solution can relieve the increasing complexity of managing information within the company. The consultant conducted incremental interviews with the owner and other subject matter experts in the organization, reviewed the process documentation and observed the processes first-hand. As a result, the consultant captured the following observations for the database solution:
The business wants to keep track of; 1) properties 2) customers, and 3) real-estate purchasing transactions.
For each property, the system shall keep track of
A unique property id
Property Street Address
Property City
Property State
Property Zip Code
Square_Footage
Number of Rooms
For each customer, the system shall keep track of
A unique customer id
Customer First Name
Customer Last Name
For each real-estate purchasing transaction, the system shall keep track of
A unique transaction id
Purchase date
Sales Value
Property Sold (referential integrity constraint: unique property id from property table)
Buyer (referential integrity constraint: a unique customer id from customer table)
A property can be sold many times, hence can show up many times in the real-estate purchasing transaction table.
A customer may purchase multiple properties, hence can show up many times in the real-estate purchasing transaction table.
When you build the tables in MS Access, you can use the text data files, which contain data for property, customer and transaction tables. (Hint: When you import these text data files to MS Access, you must make sure that the column names in the text file match the column names in the MS Access table. Please recall our demonstration in the class on this matter).
Source Data Files are listed below:
property.txtPreview the document
transaction.txtPreview the document
customer.txtPreview the document
Deliverables:
Please develop an MS Access database to fulfill the requirements above.
Please import the text data files: property, customer and transaction to their respective tables.
Please develop a query to return all real-estate purchase transactions, where the sales value is greater than $150,000.
Please develop a query to return all real-estate purchase transactions, where the sales value is between $200,000 and $250,000.
Grading:
We will use the grading schema below to score this database project. This schema can help you as you are working on this project.
DatabaseProjectGradingSchema.xlsxPreview the document