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
[...]