Your final project consists of two parts: a database and an e-commerce, both rel

Question

Your final project consists of two parts: a database and an e-commerce, both related to the business initiative you presented for your midterm (Voice assistance device)

1)Database.

For this part of your project, your team must design an Access database for a business process necessary in your company. Select one business process from unit 1, for example, processing sales, placing an order, or handling reservation, among others.

This database must have at least five tables and their relationships. Add at least ten records to each table, and at least 20 transactions (sales, orders, reservations, or whatever business process you are focusing on). Create a report of the transactions.

Explanation / Answer

create table order_details{

orderid number,

date DATE NOTNULL,

amount number,

custid1 number,

pay_id number,

prodid1 number,

primarykey(orderid)

foreignkey(custid1) references cust_details(cust_id)

foreignkey(pay_id) references paymenttype(payment_id)

foreignkey(prodid) references product(prod_id)

};

create table cust_details{

cust_id number,

custname VARCHAR(20),

phno VARCHAR(20),

oid number,

primarykey(cust_id),

foreignkey(oid) references order_details(orderid)

};

create table reservations{

cid number,

pid number,

avail TINYINT NOTNULL,

foreignkey(cid) references product(prod_id)

};

create table paymenttype{

payment_id number,

or_id number,

cus_id number,

amount number,

primarykey(payment_id),

foreignkey(or_id) references orderdetails(orderid),

foreignkey(cus_id)references cust_details(cust_id)

};

create table product{

prod_id number,

prodname VARCHAR(20),

price number,

custid1 number,

primarykey(prod_id),

foreignkey(custid1)references cust_details(cust_id)

};

INSERTING 10 RECORDS TO EACH TABLE

insert into order_details values(1,10-12-2017,2000);

After records are been inserted into all the tables by using ApexSQLLog reports can be generated

i have selected insert,update and delete operations inorder to generate the reports ,which will be in the form of a graph.

Following is the query through which reports can be generated using sqlserver management tool

select cust_details(cust_id,custname,phno,oid) AS customer_id,name,phno,oid,order(orderid,date,amount)

AS payment_id from paymenttype INNERJOIN customer ON cust_id=order.custid1 INNERJOIN order ON cust_details.cust_id=product.custid1INNERJOIN paymentmode ON paymenttype.paymentid=orderdetails.payment_id INNERJOIN product ON product.prod_id=orderdetails.pid1 where orderdetails.date IS NULL AND cust_details.cust_id in(

select cust_details.cust_id from orderdetails INNERJOIN customer ON orderdetails.orderid=cust_details.cust_id where orderdetails.date IS NULL GROUPBY customer,customer_id ORDER BY customer,order,paymentmode DESC);

)

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.