KL Sims Development Inc Database Group One Donald
K&L Sims Development, Inc. Database Group One: Donald Luna Alison Lee Brooke Sims
Problem Statement K&L Sims Development, Inc. is a fast growing development company based here in Rolla, MO. As this development company grows, they must keep everything well organized in order to make sure they continue to please everyone associated with the company. Our intent is to help make the following portions of K&L Sims Development, Inc. completely paperless. The database we will design will help keep K&L Sims Development, Inc. organized as it grows. The database will assist the company by keeping track of the following items: § Who their suppliers are § What houses are currently in the building process § What lots/building sites they currently own § The homeowners that they have sold to § Who are their available contractors
System Requirements What it does do: This database system will allow K&L Sims Development, Inc. to input all of their current information that they have collaborated over time into the above listed categories (entities) in the database. The user of the database will be the construction manager, who is also the office/business manager. This individual will also be able to print out reports on any of the information that is in the database, such as a list of all the suppliers and the account number associated with each supplier.
System Requirements Cont’d What it does not do: This database system will not keep track of any financial information for K&L Sims Development, Inc. This would involve a whole new accounting aspect of the company. It will also not include the advertisement portion of the company, or any realtor contacts or information. **Revision: After talking with the customer at the test stages of the database, it was decided that due to the small size of the database and the need to keep record of all houses built and lots owned and sold not to have delete commands for the Homeowners, Houses, and Lots tables. The only thing needed to keep these tables properly maintained is the update commands to change the information in each table as needed.
ER Diagram (Original)
ER Diagram (**Revised)
ER Diagram (**Revised)
ER Diagram (**Revised)
ER Diagram (**Revised)
ER Diagram (**Revised)
ER Diagram (**Revised)
ER Diagram (**Revised)
Functional Requirements § Add Supplier § Add Houses § Add Lots § Add Homeowners § Add Contractors § Update Attributes §**Revision: Delete Attributes for Contractors, Stockholders, and Suppliers tables
Functional Requirements § Add Supplier § Add Houses § Add Lots § Add Homeowners § Add Contractors § Update Attributes §**Revision: Delete Attributes for Contractors, Stockholders, and Suppliers tables
User Manual
Example#1 @lotq 1 This command will list all lots and their information from the Lots table. OUTPUT: L_N ACREAGE STA H_N --- ----- --L 01 5 N/A H 03 L 02 4. 75 N/A H 02 L 03 6 N/A H 01 L 04 5. 5 N/A H 05 L 05 5. 25 N/A H 07 L 06 5 AVL 000 L 07 5. 5 AVL 000 L 08 5. 75 N/A H 04 L 09 6 N/A H 06 L 10 5 AVL 000 10 rows selected.
Example #2 @lotin This command allows you to insert information about a new lot into the Lots table. After implementing this command, @lotq 1 was implemented to verify the insertion. The inserted lot is bold in the example. *Important note: When entering a lot into the database, if there is no corresponding house number as of yet, please enter ‘ 000’ as shown in this example. When a house number becomes available, use @lotupd 2 to update the house number for this new lot.
Output Enter value for status: N/A Enter value for house_number: 000 old 1: INSERT INTO LOTS VALUES ('&LOT_NUMBER', '&ACREAGE', '&STATUS', '&HOUSE_NUMBER') new 1: INSERT INTO LOTS VALUES('L 11', '6. 99‘ 'N/A', '000') 1 row created. SQL> @lotq 1 Enter value for lot_number: L 11 Enter value for acreage: 6. 99
Final Output for Ex. #2 L_N ACREAGE STA H_N ----- --- --L 01 5 N/A H 03 L 02 4. 75 N/A H 02 L 03 6 N/A H 01 L 04 5. 5 N/A H 05 L 05 5. 25 N/A H 07 L 06 5 AVL 000 L 07 5. 5 AVL 000 L 08 5. 75 N/A H 04 L 09 6 N/A H 06 L 10 5 AVL 000 L 11 6. 99 N/A 000 11 rows selected.
Example #3 @supdel This command will delete a selected supplier from the Suppliers table. @supq 3 was implemented both before and after the delete to verify that the supplier in the example was deleted. @supq 3 shows the name and account number for all suppliers in the Suppliers table.
Output SQL> @supq 3 NAME ACCOUNT --------------- -----METROPOLIS BUILDING SUPPLY 00 KALS DOOLEY WINDOW SERVICE 00 KALS MCCALLS CARPET-MART 00 KALS SHERWIN PAINT STORE 669116717 RIBBAL SUPPLY 009333 LOWES 0016345 MIDWAY BLOCK AND BRICK 00 KALS ALSIZE SUPPLY CENTER 16942113 BURTONSUPPLY 23657 STRAY SIGN COMPANY INC. 00 KALS BCT CORPORATION 336
Output cont’d NAME ACCOUNT ---------------ELECTRIC COOPERATIVE 3124 ROLLA RENTALS 00 KALS MISSOURI SIDING WHOLESALE KALD 000 SCOTT POWER EQUIPMENT C 0042 OZARKS TESTING CO 00 KALS 16 rows selected.
Using @supdel SQL> @supdel Enter value for name_of_supplier_to_delete: LOWES Enter value for account_of_supplier_to_delete: 0016345 OLD 1: DELETE FROM SUPPLIERS WHERE NAME = '&Name_of_supplier_to_delete' AND ACCOUNT = '&Account_of_supplier_to_delete'. NEW 1: DELETE FROM SUPPLIERS WHERE NAME = 'LOWES' AND ‘ACCOUNT’ 1 row deleted.
Final Ouput for Ex. #3 SQL> @supq 3 NAME ACCOUNT --------------- -----METROPOLIS BUILDING SUPPLY 00 KALS DOOLEY WINDOW SERVICE 00 KALS MCCALLS CARPET-MART 00 KALS SHERWIN PAINT STORE 669116717 RIBBAL SUPPLY 009333 MIDWAY BLOCK AND BRICK 00 KALS ALSIZE SUPPLY CENTER 16942113 BURTON SUPPLY 23657 STRAY SIGN COMPANY INC. 00 KALS
Final Output cont’d NAME ACCOUNT --------------- -----BCT CORPORATION 336 ELECTRIC COOPERATIVE 3124 ROLLA RENTALS 00 KALS MISSOURI SIDING WHOLESALE KALD 000 SCOTT POWER EQUIPMENT C 0042 OZARKS TESTING CO 00 KALS 15 rows selected.
Estimate of Effort This is an estimate in total hours for each phase of the project: · Phase 1: 15 hours (data collection, requirements, & documentation) · Phase 2: 100 hours · Phase 3: 95 hours · Total: 210 hours
Questions? ?
- Slides: 27