Database of Auto Parts Warehouse Design


Project Report, 2011

32 Pages, Grade: A


Excerpt

Content

1. Description of the application and its users
1.1 User (Predefined and Specified in Section 6):
1.2 Process
1.3 Data Flow of Diagram of Database design
1.4 Scope Define:
1.5 A

2. Conceptual and physical design of your database
2.1 Entity Relationship Diagram
2.2 Relationship E

3. Explanation of transactions that impact the database and their SQL

4. Documentation of all integrity rule
4.1 Primary and Foreign key
4.2 Customer, Staff, Part, Vendor name cannot be null
4.3 Email must be unique
4.4 Price and Quantity should be larger than
4.5 When manager stock new part from vendor, the inventory number (Onhand) in Part table and the quantity in the PartSupportInfo table should be updated correspondently
4.6 When manage stock new part, the part id and vendor id should be on the part list and vendor list
4.7 When updating CarPartInfo table Car ID should be on the list of Car table
4.8 Each Customer can only purchase same part less than 10 within same

5. Documentation of all

6. Documentation of users and their role
6.1 Web Customer
6.2 Customer Represent
6.3 Staff Manager
6.4 Database A

7. Documentation of test result
7.1 Data integrity test
7.2 Functional

8. Disaster recovery plan of AutoParts Warehouse
8.1 Create T_SQL statement for backup and restore
8.2 Backup S

9. Documentation of the data warehouse and its purpose
9.1 Inventory statistic
9.2 Geographic Information
9.3 Data Mining S

Appendix I – SQL Script of all

1. Description of the application and its users

The database designed for AutoParts Warehouse is to mainly serve as an ecommerce website. Customer can register online with their shipping address and transaction information. Customer can search the parts they need from company website and place order. According to the order come from customer, company can ship the parts to correspondent customers. In order to make economy efficient, company won’t save too many parts in stock. Oppositely, Company will keep only minimum number of each part. If one of parts runs out of stock, a staff will order the part from vendor and then send it to customer. Our scope will mainly cover the function of customer order, price query, parts summary and company stock. Online transaction with bank and shipment with shipping company are out of our scope

1.1 User (Predefined and Specified in Section 6):

a. Web Customers
b. Customer Represents
c. Customer Managers
d. Database Administrator

1.2 Process

a. Create/Edit Customer
User: C
Data I/O: Customer Information I
b. Query parts by correspondent car type
User: Customer/S
Data I/O: Search criteria input and display
c. Place Order
User: C
Data I/O: Purchase Information I
d. Edit Order
User: Customer/Represents (Staff)
Data I/O: Data change input and display corrected
e. Add/Edit Product
User: S
Data I/O: Product Information Input and display
f. Update Inventory
User: S
Data I/O: Corrected Information I
g. Stock
User: Staff (Manager)
Data I/O: Stock Information I
h. Query parts and vendors Information
User: S
Data I/O: parts and vendors query criteria input and display
i. Query Customer Order Information
User: Customer/S
Data I/O: Customer, parts and data info input and display
j. Query Staff stock information
User: Staff (Manager)
Data I/O: Parts and Staff information input and display

1.3 Data Flow of Diagram of Database design

Abbildung in dieser Leseprobe nicht

1.4 Scope Define:

Our scope will mainly cover the function of customer order, price query, parts summary and parts stock. Online transaction with bank and shipment with shipping company are out of our scope

1.5 Assumptions

—- All type of Car has been listed in Car T
—- Original Price comes from vendor will not change by
—- Each part can only be suitable for one type of

2. Conceptual and physical design of your database

Based on I/O needs I defined in the section 1. In section 2, I create the Entity Relationship Diagram and explain the all relationship. Tables detail will be listed with SQL script in the Appendix I

2.1 Entity Relationship Diagram

Abbildung in dieser Leseprobe nicht enthalten

2.2 Relationship Explanation

Customer-Orders:

A customer can have zero or many orders and each order should belong to one customer

Orders-O

An order can possess at least one details such as one order can purchase two or more parts. Orderdetail should belong to one

Orderdetails-P

An orderdetails can have one or more parts and one part can be belong to zero of more

Part-CarPartI

This is one on one mandatory

Car-CarPartI

Car table contain the information of all car in the market. Car can possess zero or more part in our store. However, one part must be only suitable for one type of

Staff-M

A staff should be supervised by only one manager and one manager can supervise zero or more

Staff-S

One staff can create zero or more stock and one stock order should belong to one

Stock-S

One stock order can have many stockdetails, while one stockdetails can only belong to one

Stockdetails-V

One stockdetials can be related to one or more vendors, because one part can be offered by different vendors. A vendor can have zero or more stockdetails

Vendor-PartSupportI

A Vendor can offer zero of more parts. And a part should be offered at least one Vendor

Part-PartSupportI

PartSupportInfo should belong to only one part. And, Part have own at least one of many PartSupportI

3. Explanation of transactions that impact the database and their SQL implementation

Process1 a): Create Customer

Transaction Name: CreateC

In this transaction, database will create new customer and automatically assign unique ID to the customer

SQL Implementation:

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

INSERT INTO Customer Values ('ID','firstname','lastname','Address','teleNo','Email','City','State','Zip')

TRANSACTION

Process1 b): Edit Customer

Transaction Name: EditC

In this transaction, database will execute update statement to update date of correspondent customer. Only Customer have the right to change its account

SQL Implementation:

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

UPDATE Customer SET First_Name='firstname' where Cid='12'

TRANSACTION

Process 2: Place order

Transaction Name : PlaceO

In this transaction, database will insert a new recode in ‘Order’ table and ‘Orderdetails’ tables, and update the data of Quantity in the ‘Part’ table. If there is any error happens, transaction will rollback

SQL Implementation:

CREATE PROC PlaceO

@Oid as int,

@Pid as int,

@Cid as int,

@Quantity as int,

@date as date,

@total as

set nocount

select @total= SUM(Quantity) from Orderdetail od,Orders o where od.Oid=o.Oid and Cid=@Cid and Pid=@Pid and date=@

IF ((@total+@Quantity)<10)

B

insert into Orders Values (@Oid,@Cid,@date)

insert into dbo.Orderdetail Values (@Oid,@Pid,@Quantity,Null)

update Part set Onhand=Onhand-@Q

End

End

print 'Over days limit'

Process 3: Edit order

Transaction Name: EditO

In this transaction, database will update the data in ‘Order’,’Part’ and ‘Orderdetails’ table, if there is any error happens, transaction will rollback

SQL STATEMENT:

Reference to Process 2

Process 4 a): Add part

Transaction Name: AddP

In this transaction, database will insert new recode to the ‘Part’ table and Car

SQL STATEMENT:

CREATE PROC AddP

@Pid as int,

@Part_Name as nvarchar(30),

@Onhand as int,

@Price as real,

@Manufacture as nvarchar(50),

@Car_Id as

set nocount

Begin TRANSACTION

insert into Part values (@Pid,@Part_Name,Null,@Onhand,@Price,@Manufacture)

insert into CarPartInfo values (@Pid,@Car_Id)

if (@@ERROR<>0) ROLLBACK TRANSACTION

Process 4 b): Edit Part

Transaction Name: EditP

In this transaction, database will update the information of ‘Part’ table

SQL STATEMENT:

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

UPDATE Part SET Description='Description...'

TRANSACTION

Process 6: Stock

Transaction Name: S

In this transaction, database will insert new record of ‘Stock’ table and ‘Stockdetails’ table, and update Part

SQL STATEMENT:

CREATE PROC AddNewS

@Staff_Id as int,

@Stock_Id as int,

@Data as date,

@Pid as int,

@Vid as int,

@Quantity as int,

@Origin_Price as

set nocount

Begin

insert into Stock values (@Stock_Id,@Staff_Id,@Data)

- into Stockdetail values(@Stock_Id,@Pid,@Vid,@Quantity,@Origin_Price)

Process 7: Query Parts and Vendors information

Transaction Name: QueryPrt&VensI

In this transaction, database will display the parts and related vendors and can compare the different price come from different vendors for a given part

SQL STATEMENT:

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

select v.Name,p.Part_Name,ps.Origin_P

from Vendor v,Part p,PartSupportInfo

where p.Pid=ps.Pid and v.Vid=ps.V

Order by v.N

TRANSACTION

Process 8: Query Customer Order information

Transaction Name: QueryCustOrdI

In this transaction, database will display a given customer and all the orders has been placed

SQL STATEMENT:

CREATE PROC findCustO

@Cid as int,

@datein as date,

@dateout as

set nocount

select Last_Name, Part_Name,od.Quantity,DATE from Customer c,Orders o,Orderdetail od,Part

where c.Cid=O.Cid and o.Oid=od.Oid and od.Pid=p.Pid and o.Date between @datein and @

G

Process 9: Query Staff stock information

Transaction Name: QueryStafStocI

In this transaction, database will query the stock information according to staff

SQL STATEMENT:

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

select Last_Name, Part_Name, p.Pid, sd.Q

from Stock st, Stockdetail sd, Staff s, Part

where st.Staff_Id=s.Staff_Id and sd.Stock_id=st.Stock_Id and sd.Pid=p.P

Order by Last_N

TRANSACTION

4. Documentation of all integrity rule

4.1 Primary and Foreign key

4.2 Customer, Staff, Part, Vendor name cannot be null

Set not null constraint of all above attribute or use SQL:

ALTER TABLE Customer ADD CONSTRAINT Not Null (First_Name, Last_Name)

4.3 Email must be unique

Set unique constraint of attribute or use SQL:

ALTER TABLE Customer ADD CONSTRAINT emailUnique Unique(Email)

4.4 Price and Quantity should be larger than 0

Set constraint price should be larger than 0 and Quantity should be equal or larger than 0

4.5 When manager stock new part from vendor, the inventory number (Onhand) in Part table and the quantity in the PartSupportInfo table should be updated correspondently

Use trigger 1:

create trigger InventoryBalCheck on Stockdetail for

declare @Pid as

declare @Quantity as

declare @Vid as

declare @OriginPrice as

declare @Result as nvarchar(260)

select @Pid=pid,@Quantity=Quantity,@Vid=Vid from

update Part set Onhand=Onhand+@Quantity where Pid=@P

select @Result from PartSupportInfo where Pid=@Pid and @Vid=V

IF (@Result IS NOT Null)

BEGIN

update PartSupportInfo set Quantity=Quantity+@Quantity where Vid=@Vid and Pid=@P

END

End

BEGIN

Insert into PartSupportInfo VALUES (@Pid,@Vid,@Quantity,@OriginPrice)

END

4.6 When manage stock new part, the part id and vendor id should be on the part list and vendor list

Use trigger 2:

create trigger PartVendorChek on Stockdetail for update,I

declare @Pid as

declare @Quantity as

declare @Vid as

declare @OriginPrice as

declare @PResult as nvarchar(260)

declare @VResult as nvarchar(260)

select@Pid=pid,@Quantity=Quantity,@Vid=Vid,@OriginPrice=Origin_Price from

select @PResult from Part where Pid=@P

select @VResult from Vendor where Vid=@V

IF ((@PResult is Null) OR(@VResult is Null))

B

ROLLBACK TRANSACTION

Print 'Vendor id or Part id is not on the list'

End

Else Print 'Transaction OK'

END

[...]

Excerpt out of 32 pages

Details

Title
Database of Auto Parts Warehouse Design
College
University of Florida
Grade
A
Author
Year
2011
Pages
32
Catalog Number
V187359
ISBN (eBook)
9783656111375
ISBN (Book)
9783656111535
File size
748 KB
Language
English
Keywords
database, auto, parts, warehouse, design
Quote paper
Jiyi Tian (Author), 2011, Database of Auto Parts Warehouse Design, Munich, GRIN Verlag, https://www.grin.com/document/187359

Comments

  • No comments yet.
Read the ebook
Title: Database of Auto Parts Warehouse Design



Upload papers

Your term paper / thesis:

- Publication as eBook and book
- High royalties for the sales
- Completely free - with ISBN
- It only takes five minutes
- Every paper finds readers

Publish now - it's free