K

1
KL Sims Development, Inc. Database
Group OneDonald Luna Alison Lee Brooke Sims
2
Problem Statement

• KL 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 KL Sims Development, Inc.
  completely paperless. The database we will design
  will help keep KL 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

3
System Requirements
What it does do This database system will
allow KL 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.

4
System Requirements Contd
What it does not do This database system
will not keep track of any financial information
for KL 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.
5
ER Diagram (Original)
6
ER Diagram (Revised)
7
ER Diagram (Revised)
8
ER Diagram (Revised)
9
ER Diagram (Revised)
10
ER Diagram (Revised)
11
ER Diagram (Revised)
12
ER Diagram (Revised)
13
Functional Requirements

• Add Supplier
• Add Houses
• Add Lots
• Add Homeowners
• Add Contractors
• Update Attributes
• Revision Delete Attributes for Contractors,
  Stockholders, and Suppliers tables

14
Functional Requirements

• Add Supplier
• Add Houses
• Add Lots
• Add Homeowners
• Add Contractors
• Update Attributes
• Revision Delete Attributes for Contractors,
  Stockholders, and Suppliers tables

15
User Manual
16
Example1
_at_lotq1 ? This command will list all lots and
their information from the Lots
table. OUTPUT L_N ACREAGE STA
H_N --- ---------- ---
--- L01 5 N/A
H03 L02 4.75 N/A
H02 L03 6 N/A
H01 L04 5.5 N/A
H05 L05 5.25 N/A
H07 L06 5 AVL
000 L07 5.5 AVL
000 L08 5.75 N/A
H04 L09 6 N/A
H06 L10 5 AVL
000 10 rows selected.
17
Example 2


_at_lotin
This command
allows you to insert information about
a new lot into the Lots
table. After implementing this
command, _at_lotq1was 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 _at_lotupd2 to
update the house number for this new lot.

18
Output

Enter 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('L11', '6.99 'N/A',
'000') 1 row created. SQLgt _at_lotq1 Enter value for
lot_number L11 Enter value for acreage 6.99
19
Final Output for Ex. 2
L_N ACREAGE STA H_N ---
---------- --- --- L01 5
N/A H03 L02 4.75
N/A H02 L03 6
N/A H01 L04 5.5 N/A
H05 L05 5.25 N/A
H07 L06 5 AVL
000 L07 5.5 AVL 000 L08
5.75 N/A H04 L09
6 N/A H06 L10 5
AVL 000 L11 6.99
N/A 000 11 rows selected.

20
Example 3



_at_supdel This command will
delete a selected supplier from

the Suppliers table. _at_supq3 was
implemented both before and

after the delete to verify that the supplier in
the example was
deleted.
_at_supq3 shows the name and account number for all

suppliers in the
Suppliers table.

21
Output
SQLgt _at_supq3 NAME
ACCOUNT -------------------
-----------
---------- METROPOLIS BUILDING SUPPLY
00KALS DOOLEY WINDOW SERVICE
00KALS MCCALLS CARPET-MART
00KALS SHERWIN PAINT STORE
669116717 RIBBAL SUPPLY
009333 LOWES
0016345 MIDWAY BLOCK AND
BRICK 00KALS ALSIZE SUPPLY CENTER
16942113 BURTONSUPPLY
23657 STRAY SIGN COMPANY INC.
00KALS BCT CORPORATION
336


22
Output contd


NAME ACCOUNT -----------
------------------- ---------- ELECTRIC
COOPERATIVE 3124 ROLLA RENTALS
00KALS MISSOURI
SIDING WHOLESALE KALD000 SCOTT POWER
EQUIPMENT C0042 OZARKS TESTING CO
00KALS 16 rows selected.

23
Using _at_supdel
SQLgt _at_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.


.
24
Final Ouput for Ex.3


SQLgt _at_supq3 NAME
ACCOUNT --------------------------
---- ---------- METROPOLIS
BUILDING SUPPLY 00KALS DOOLEY WINDOW SERVICE
00KALS MCCALLS CARPET-MART
00KALS SHERWIN PAINT STORE
669116717 RIBBAL SUPPLY
009333 MIDWAY BLOCK AND BRICK
00KALS ALSIZE SUPPLY CENTER
16942113 BURTON SUPPLY
23657 STRAY SIGN COMPANY INC.
00KALS
25
Final Output contd


NAME
ACCOUNT ------------------------------
---------- BCT CORPORATION
336 ELECTRIC COOPERATIVE
3124 ROLLA RENTALS
00KALS MISSOURI SIDING WHOLESALE
KALD000 SCOTT POWER EQUIPMENT
C0042 OZARKS TESTING CO
00KALS 15 rows selected.
26
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
•  

27
Questions??