Assignment 2 Database Design & Implementation Student Name: Student ID:   Table of contents Assumptions 2 Logical ER diagram 3 Physical ER diagram 4 Data Dictionary 5   Assumptions • The customers who are registered, only their details are recorded. • Each customer has their addresses. Customers can have multiple addresses. • All the customer details are mandatory. • The customer referral number will be from the customer table. • Except for address name, all the address tables are mandatory. • The items which are registered, only their details are recorded in the database. • It is not mandatory to the description of each item. • Each item belongs at least one category. • Each order has a particular invoice number. • It is possible the customer has never ordered anything. • It is possible that particular item has never been ordered. • It is possible that particular category has never been ordered. • It is possible that customer has not been interested in any of the categories.   Logical ER diagram Figure 1: logical diagram The logical diagram has been well designed according to the scenario given. The scenario results in designing of 9 entities with a primary key defined in each table. This diagram has been designed taking 3 NF normalization forms, to remove any inconsistency and redundancy. All the tables are precisely observed and designed while taking some few assumptions. This complete ER-diagram has been converted into Physical ER diagram.   Physical ER diagram Figure 2: Physical ER diagram This physical ER diagram is an actual representation of the database. All the constraints have been perfectly mentioned in the diagram. This physical ER diagram shows all sort of relationships which will exist in the actual database.   Data Dictionary Customer Column Type Null Default Links to Comments cust_number(primary key) int(50) No first_name Varchar(100) No last_name Varchar(100) No email_address Varchar(100) No Y/N int(50) No Indexes Keyname Type Unique Packed Column Cardinality Collation Null Comment primary BTREE yes No cust_number 180 A No unique BTREE yes No email_address 180 A No Referral Column Type Null Default Links to Comments id (primary key) int(50) No referral_number (foreign key) int(50) No customer -> cust_number Indexes Keyname Type Unique Packed Column Cardinality Collation Null Comment primary BTREE yes No id 180 A No Foreign key BTREE no No referral_number 180 A No Cust_address Column Type Null Default Links to Comments Addres_id (primary key) int(50) No Cust_number (foreign key) int(50) No Customer->cust_number Address_name Varchar(100) yes Indexes Keyname Type Unique Packed Column Cardinality Collation Null Comment PRIMARY BTREE Yes No Addres_id 180 A No Foreign key BTREE no No Cust_number 180 A No Item_details Column Type Null Default Links to Comments item_number (Primary) int(50) No Item_name Varchar(100) no description Varchar(100) Yes NULL price int(10) no Indexes Keyname Type Unique Packed Column Cardinality Collation Null Comment PRIMARY BTREE Yes No item_number 57 A No Item_category Column Type Null Default Links to Comments Category_number (Primary) int(50) No Category_name Varchar(100) No Item_number (foreign key) int(50) No Item_details -> item_number Indexes Keyname Type Unique Packed Column Cardinality Collation Null Comment PRIMARY BTREE Yes No Category_number 95 A No foreign key BTREE no No Item_number 100 A No Category_newsletter Column Type Null Default Links to Comments Cust_number(foreign key) int(50) No customer -> cust_number Category_number(foreign key) int(50) No Item_category->category_number Indexes Keyname Type Unique Packed Column Cardinality Collation Null Comment Foreign key BTREE yes No Cust_number 100 A No Foreign key BTREE No No Category_number 100 A No Order Column Type Null Default Links to Comments Invoice_no (primary key) int(50) No Cust_number (foreign key) int(50) No Customer->cust_number Date_order date No Billing_address_id (foreign key) int(50) No Address->address_id Delivery_address_id (foreign key) int(50) No Address->address_id Indexes Keyname Type Unique Packed Column Cardinality Collation Null Comment Primary key BTREE yes No Invoice_no 100 A No foreign key BTREE No No Cust_number 80 A No foreign key BTREE No No Billing_address_id 80 A No Foreign key BTREE No No Delivery_address_id 80 A No Order_details Column Type Null Default Links to Comments order_id (primary key) int(50) No Invoice_no (foreign key) int(50) No Order->invoice_no Item_number (foreign key) int(50) No Item_details->item_number quantity int(50) No Indexes Keyname Type Unique Packed Column Cardinality Collation Null Comment primary key BTREE yes No order_id 100 A No Foreign key BTREE No No Invoice_no 80 A No Foreign key BTREE No No Item_number 80 A No

Assignment 2
Database Design & Implementation
Student Name:
Student ID:


Table of contents
Assumptions. 2
Logical ER diagram.. 3
Physical ER diagram.. 4
Data Dictionary. 5

Assumptions

·         The customers who are registered, only their details are recorded.
·         Each customer has their addresses. Customers can have multiple addresses.
·         All the customer details are mandatory.
·         The customer referral number will be from the customer table.
·         Except for address name, all the address tables are mandatory.
·         The items which are registered, only their details are recorded in the database.
·         It is not mandatory to the description of each item.
·         Each item belongs at least one category.
·         Each order has a particular invoice number.
·         It is possible the customer has never ordered anything.
·         It is possible that particular item has never been ordered.
·         It is possible that particular category has never been ordered.
·         It is possible that customer has not been interested in any of the categories.

Logical ER diagram

Figure 1: logical diagram
The logical diagram has been well designed according to the scenario given. The scenario results in designing of 9 entities with a primary key defined in each table. This diagram has been designed taking 3 NF normalization forms, to remove any inconsistency and redundancy. All the tables are precisely observed and designed while taking some few assumptions. This complete ER-diagram has been converted into Physical ER diagram.



Physical ER diagram

Figure 2: Physical ER diagram
This physical ER diagram is an actual representation of the database. All the constraints have been perfectly mentioned in the diagram. This physical ER diagram shows all sort of relationships which will exist in the actual database.

 

 

 

Data Dictionary

Customer
Column
Type
Null
Default
Links to
Comments
cust_number(primary key)
int(50)
No
first_name
Varchar(100)
No
last_name
Varchar(100)
No
email_address
Varchar(100)
No
Y/N
int(50)
No
Indexes
Keyname
Type
Unique
Packed
Column
Cardinality
Collation
Null
Comment
primary
BTREE
yes
No
cust_number
180
A
No
unique
BTREE
yes
No
email_address
180
A
No
Referral
Column
Type
Null
Default
Links to
Comments
id (primary key)
int(50)
No
referral_number (foreign key)
int(50)
No
customer
-> cust_number
Indexes
Keyname
Type
Unique
Packed
Column
Cardinality
Collation
Null
Comment
primary
BTREE
yes
No
id
180
A
No
Foreign key
BTREE
no
No
referral_number
180
A
No



Cust_address
Column
Type
Null
Default
Links to
Comments
Addres_id (primary key)
int(50)
No
Cust_number (foreign key)
int(50)
No
Customer->cust_number
Address_name
Varchar(100)
yes
Indexes
Keyname
Type
Unique
Packed
Column
Cardinality
Collation
Null
Comment
PRIMARY
BTREE
Yes
No
Addres_id
180
A
No
Foreign key
BTREE
no
No
Cust_number
180
A
No
Item_details
Column
Type
Null
Default
Links to
Comments
item_number (Primary)
int(50)
No
Item_name
Varchar(100)
no
description
Varchar(100)
Yes
NULL
price
int(10)
no
Indexes
Keyname
Type
Unique
Packed
Column
Cardinality
Collation
Null
Comment
PRIMARY
BTREE
Yes
No
item_number
57
A
No


Item_category
Column
Type
Null
Default
Links to
Comments
Category_number (Primary)
int(50)
No
Category_name
Varchar(100)
No
Item_number (foreign key)
int(50)
No
Item_details -> item_number
Indexes
Keyname
Type
Unique
Packed
Column
Cardinality
Collation
Null
Comment
PRIMARY
BTREE
Yes
No
Category_number 
95
A
No
foreign key
BTREE
no
No
Item_number
100
A
No
Category_newsletter
Column
Type
Null
Default
Links to
Comments
Cust_number(foreign key)
int(50)
No
customer -> cust_number
Category_number(foreign key)
int(50)
No
Item_category->category_number
Indexes
Keyname
Type
Unique
Packed
Column
Cardinality
Collation
Null
Comment
Foreign key
BTREE
yes
No
Cust_number
100
A
No
Foreign key
BTREE
No
No
Category_number
100
A
No
Order
Column
Type
Null
Default
Links to
Comments
Invoice_no (primary key)
int(50)
No
Cust_number (foreign key)
int(50)
No
Customer->cust_number
Date_order
date
No
Billing_address_id (foreign key)
int(50)
No
Address->address_id
Delivery_address_id (foreign key)
int(50)
No
Address->address_id
Indexes
Keyname
Type
Unique
Packed
Column
Cardinality
Collation
Null
Comment
Primary key
BTREE
yes
No
Invoice_no
100
A
No
foreign key
BTREE
No
No
Cust_number
80
A
No
foreign key
BTREE
No
No
Billing_address_id
80
A
No
Foreign key
BTREE
No
No
Delivery_address_id
80
A
No
Order_details
Column
Type
Null
Default
Links to
Comments
order_id (primary key)
int(50)
No
Invoice_no (foreign key)
int(50)
No
Order->invoice_no
Item_number (foreign key)
int(50)
No
Item_details->item_number
quantity
int(50)
No

Indexes
Keyname
Type
Unique
Packed
Column
Cardinality
Collation
Null
Comment
primary key
BTREE
yes
No
order_id
100
A
No
Foreign key
BTREE
No
No
Invoice_no
80
A
No
Foreign key
BTREE
No
No
Item_number
80
A
No













Comments

Post a Comment

Popular posts from this blog

CSG1207/CSI5135 Systems and Database Design

Assignment 2(TASK 2) Database Design & Implementation Student Name: Student ID: 1 Table of Contents Create.sql............................................................................................................................................2 Views.sql...........................................................................................................................................11 Queries.sql........................................................................................................................................11 List of figures Figure 1: customer table.........................................................................................................................2 Figure 2: address table............................................................................................................................4 Figure3: Referral...............................................................................................................
Read more