GS/2018/0318 On 17th Jan 2019

IT Y2 A

DATABASE ASSIGNMENT

I. DATA DICTIONARY

The data dictionary is represented as follows:

Table Fields Type Siz Key Nul Defaul Extra Description

e l t
Product ProductId Int 11 Primar No No Auto_incremen Product
s y key t identificatio
n
productNam varcha 23 No No Name of
e r product
Price Int 26 No No Price per
product
SupplierID Int 12 Foreign No No Foreign key
key of supplier
table
Quantity Int 26 No No Quantity of
available
items

Table Fields Type Size Key Null Default Extra Description

customer CustomerId Int 11 Primary No No Auto_increment customer
key identification
firstName varchar 23 No No Name of
customer
Phone Int 26 unique No No Customer
phone
number
Paymethod Int 20 No No Payment
method
country varchar 26 No No Location of
customer
Table Fields Type Size Key Null Default Extra Description
supplier SupplierID Int 11 Primary No No Auto_increment supplier
key identification
firstName varchar 23 No No Name of
supplier
street Int 26 No No Location of
supplier
Supplierdate Int 12 No No Date
supplier
supplied
product
contact Int 26 unique No No Reachable
line of
supplier

Table Fields Type Size Key Null Default Extra Description

Orders OrderId Int 11 Primary No No Auto_increment order
key identification
ProductId varchar 23 Foreign No No Foreign key
key of product
table
customerId Int 26 Foreign No No Foreign key
key of customer
table
SupplierID Int 12 Foreign No No Foreign key
key of supplier
table
Orderdate No No Date of
order

Table Fields Type Size Key Null Default Extra Description

Orderdetails ID Int 11 Primary No No Auto_increment Identification
key of
Orderdetails
OrderId int 20 Foreign No No Foreign key
key of orders
table
Table Fields Type Size Key Null Default Extra Description
shippers ShipperID Int 11 Primary No No Auto_increment shipper
key identification
firstName varchar 23 No No Name of
shipper
Comp_name Int 26 No No Price per
product
Phone Int 12 unique No No Shipper
phone
number
Photo varchar 45 No No Photo of
shipper

Table Fields Type Siz Key Nul Defaul Extra Description

e l t
comment CommentId Int 11 Primar No No Auto_incremen Comment
s y key t identificatio
n
customerID int 23 Foreign No No Foreign key
key of customer
table
comment varcha 255 No No Price per
r product
messageDat No No Received
e date of msg

II. ENTITY RELATIONSHIP DIAGRAM(ERD)

 CustomerId ID

Orders Order 1
N descri Orderdetails
N ption
N
OrderId

CustomerId
Receive
Carry M
his/her Customers
ordered order
M M
Country
ShipperID Phone
M
Shippers View
products

ProductName
N

Products Send
opinion

SupplierID CommentID CustomerID

fisrtName
N
Supply
Comments
Supplier M product

SupplyDate
Comment MessageDate
Contact street