CS403 Assignment no 4 Solution


Assignment No. 04 Semester Fall 2011 Database Management Systems-CS403





Total Marks: 20

Due Date:
09 – 01 – 2012


Objective
To understand the SQL Commands to physically create the Database Tables.

Instructions:
Please read the following instructions carefully before solving & submitting assignment:
It should be clear that your assignment will not get any credit if:

o       The assignment is submitted after due date.
o       The submitted assignment does not open or file is corrupt.
o       The assignment is copied (from other student or copy from handouts).
o       The assignment is in the format other than MS Word (doc).
o       Student ID is not mentioned in the assignment File or name of file is other than student ID.

For any query about the assignment, contact at cs403@vu.edu.pk

GOOD LUCK


Write SQL Commands to Create the following tables along with attributes and Primary/Foreign key constraints. Use the appropriate Data Types for each attribute.


Customer (CID, CName, Address, Phone)                                               
Order (Order-ID, Description, CID)                                                           
Product (PID, PName, Price, CAT-ID)                                                       
Product-Order (Order-ID, PID)                                                                  
Invoice (Inv-ID, Description, Total-Amount, Order-ID)                           
Category (CAT-ID, CATName)                                                          
Vendor (VID, Name, Address)                                                                  
Product-Vendor (PID, VID)                      


=================================================================

SOLUTION


Assignment No_4

Data Base Management System
Total Marks = 20
Solution:
CREATE TABLE Customer (
CID char (20) primary key,
CName varchar (50),
Address varchar (100),
Phone char (10)
)
*************************************************************
CREATE TABLE _Order (
Order_ID char (10) primary key,
_Description varchar (100),
CID char (20) references Customer (CID)
)
*************************************************************
CREATE TABLE Product (
PID char (10) primary key,
PName varchar (50),
Price real,
Cat_ID char (10) references Category (Cat_ID)
)
***************************************************************
CREATE TABLE Product_Order (
Order_ID char (10)
PID char (10)
Primary key (Order_ID, PID)
)
***************************************************************
CREATE TABLE Invoice (
Inv_ID char (10) primary key,
_Description varchar (100),
Total_Amount real
Order_ID char (10) references _Order (Order_ID)
)
*****************************************************************
CREATE TABLE Category (
Cat_ID char (10) primary key,
CatName varchar (50)
)

******************************************************************
CREATE TABLE Vendor (
Ven_ID char (10) primary key,
_Name varchar (50),
Address varchar (100)
)
*******************************************************************
                                
CREATE TABLE Product_Vendor (
PID char (10),
Ven_ID char (10),
Primary key (PID, Ven_ID)
)


        

x


No comments:

Post a Comment