Systems and Database Design CSG1207/CSI5135 Assignment 2 Solution

This document presents a solution to a database design and implementation assignment (CSG1207/CSI5135), detailing the design process through assumptions, logical and physical ER diagrams, and a comprehensive data dictionary. The logical ER diagram outlines nine entities with defined primary keys, designed according to 3NF normalization to minimize redundancy and inconsistency. The physical ER diagram provides a visual representation of the database structure, illustrating relationships and constraints. The data dictionary further elaborates on each table's columns, data types, constraints, and relationships, offering a complete blueprint for database implementation. The assignment covers customer details, addresses, item categories, order processing, and customer preferences, ensuring all details are accurately recorded and managed within the database system. Desklib provides this document as a resource for students.

CSG1207/CSI5135 Systems and Database Design
Assignment 2
Database Design & Implementation
Student Name:
Student ID:

P a g e | 1
Table of contents
Assumptions...............................................................................................................................2
Logical ER diagram.....................................................................................................................3
Physical ER diagram...................................................................................................................4
Data Dictionary...........................................................................................................................5
1

P a g e | 2
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.
2

P a g e | 3
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.
3

P a g e | 4
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.
4

P a g e | 5
Data Dictionary
Customer
Column Type Nul
l
Defaul
t 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 Nul
l
Defaul
t Links to Comments
id (primary key) int(50) No
referral_number (foreign
key) int(50) No
customer
-> cust_number
Indexes
5

P a g e | 6
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 Nul
l
Defaul
t Links to Comment
s
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 Nul
l
Defaul
t Links to Comments
item_number
(Primary) int(50) No
Item_name Varchar(100
) no
6

P a g e | 7
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 Nul
l
Defaul
t Links to Comment
s
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 Nul
l
Defaul
t Links to Comment
s
7

P a g e | 8
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 Nul
l
Defaul
t Links to Comment
s
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
Keynam
e Type Uniqu
e
Packe
d Column Cardinalit
y
Collatio
n
Nul
l
Commen
t
Primary
key
BTRE
E yes No Invoice_no 100 A No
foreign
key
BTRE
E No No Cust_number 80 A No
8

P a g e | 9
Keynam
e Type Uniqu
e
Packe
d Column Cardinalit
y
Collatio
n
Nul
l
Commen
t
foreign
key
BTRE
E No No Billing_address_id 80 A No
Foreign
key
BTRE
E No No Delivery_address_i
d 80 A No
Order_details
Column Type Nul
l
Defaul
t 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
9

P a g e | 10
10