Scribd
Upload
74 views3 pages

Lab01: Advance SQL 1: TIS3351 Advance Database Oct2014

This document provides instructions for a lab assignment to create and manipulate tables in a database for a furniture store. The student is asked to: 1) Create a database called PVF and connect to it. Discuss suitable data types and attributes. 2) Create tables for CUSTOMER, PRODUCT, ORDER, ORDERDETAIL with the necessary columns, constraints, and sample data provided. 3) Modify the PRODUCT table to add constraints for price and default quantity. 4) Update an order quantity and delete a row from ORDERDETAIL. 5) Attempt to delete a customer, to demonstrate a constraint preventing deletion due to existing order records.

Uploaded by

Peter Kong
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
74 views3 pages

Lab01: Advance SQL 1: TIS3351 Advance Database Oct2014

This document provides instructions for a lab assignment to create and manipulate tables in a database for a furniture store. The student is asked to: 1) Create a database called PVF and connect to it. Discuss suitable data types and attributes. 2) Create tables for CUSTOMER, PRODUCT, ORDER, ORDERDETAIL with the necessary columns, constraints, and sample data provided. 3) Modify the PRODUCT table to add constraints for price and default quantity. 4) Update an order quantity and delete a row from ORDERDETAIL. 5) Attempt to delete a customer, to demonstrate a constraint preventing deletion due to existing order records.

Uploaded by

Peter Kong
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

TIS3351

Advance Database

Oct2014

Lab01: Advance SQL 1


Instruction: You are required to create a database on DB2 for the following
Entity Relationship Diagram. Then, write SQL data definition command and
data manipulation commands for all questions below. Show your executable
solutions to tutor.

Figure 1. Entity Relationship Diagram for Pine Valley Furniture (version 1).

1. Create a database named PVF. Then connect to the database.


Before you proceed:
a) Discuss suitable data types for all the attributes. Look at the
values in tables below.
b) Discuss suitable attributes to be automatically generated.
c) Are there any attributes could have default values? Are there any
attributes with specific constraints?

TIS3351

Advance Database

Oct2014

2. Create table CUSTOMER as below:


CREATE TABLE CUSTOMER
(
CustID
int
not null
with 1,

generated always as identity (start


increment by 1),

CustFname varchar(20),
CustLname varchar(20),
PRIMARY KEY(CustID)
);
To see the changes, you may display the table structure you have just
defined:
SELECT * FROM customer;

3. Continue to create table PRODUCT, ORDER, and ORDERDETAILS. Make


sure you include all the necessary entity integrity constraint, and
referential integrity constraint.
Note: Use ON DELETE RESTRICT for FOREIGN KEYS.
4. Modify table PRODUCT to add constraint check ProdPrice must always
be more than 200.00
5. Modify table PRODUCT to add a new column named QtyOnHand. Set the
default quantity on hand as 10 items.
6. Insert the following values into the tables:
CUSTOMER
CustID CustFna
me
1
Linda
2
Gary
ORDER
OrderI OrderDate
D
11
05-232014

CustLna
me
Green
Bold

PRODUCT
ProdID
ProdName
C01
D05

Chair
Desk

ProdPri
ce
250.00
360.00

CustID

ORDERDETAIL
OrderID ProdID

Quantity

11

C01

QtyOnHa
nd
default
35

TIS3351

12

Advance Database

05-242014

Oct2014

11

D05

12
12

D05
C01

1
1

7. Gary (OrderID 12) actually buys 2 desks. Update the desk (D05) Quantity
bought by Gary.
8. Delete from ORDERDETAIL where the OrderID is 12 and ProdID is CO1.
9. Now, can you delete a customer named Linda? Why can or can not?

You might also like