Class Diagram Design | Case Study

Contents
Chapter 1: Introduction....................................................................................................................3
Class diagram...................................................................................................................................4
Constraints and Assumptions.......................................................................................................5
Chapter 3: Design Decisions...........................................................................................................5
Chapter 4: Relational model............................................................................................................6
Chapter 5: Implementation..............................................................................................................8
Chapter 6: Queries.........................................................................................................................16
Chapter 7: Conclusion...................................................................................................................19
References......................................................................................................................................19

Chapter 1: Introduction
Online Sweet Shop is an online shop that specializes in selling of sweets to its customers. The
shop operates online and does not have any retail centers. The shop is in need of a database that
will be used to support its e-commerce thus this report discusses the design process of the
database. The aims and objectives of this project are;
 To design a class diagram based on the case study and provide a list of constraints and
assumptions used to design the class diagram.
 To discuss the design decisions made while constructing the class diagram and the
resulting design decisions needed to convert the class diagram to a relational model.
 To present a relational model that can be used for the implementation of the database
using a relational database management system.
 To show an implementation of the database. This will include showing the actual SQL
code for creating tables and implementing relationships between the tables as well as data
population codes that insert data into the tables making up the database
 To show an implementation of queries that demonstrate the implemented database meets
all the initial requirements of the database.
 To provide a critical evaluation of the design and implementation process of the database

Class diagram
Figure 1: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . Class
diagram

Constraints and Assumptions
 Each region has one warehouse. A region has many distribution centers and each
distribution center is associated with only one warehouse which operates in the region.
The distribution centers get their stock from the warehouse in the region that they fall in.
 There are two types of orders that can be prepared by a distribution center. The first type
of order is a standing order and the second is a special order. Both orders differ on the
basis of the items that are currently in stock.
 Each distribution center has its own stock. When a customer makes an order, the order is
processed based on the items that are currently available in stock in the distribution
center.
 A customer maintains a list of items that he or she would like to purchase. The list is
maintained as a wish list. When the customer makes an order and the item is transferred
from the wish list to the shopping list. Once an item is added to the shopping list, the item
is removed after 90 days if the customer does not buy it. The shopping list only maintains
one type of item at a time.
 Each order has one or more items that are currently available in stock. An order cannot
have items that are not in stock.
 An order results to one and only one payment which is done using a credit card.
Chapter 3: Design Decisions
The following design decisions were made while constructing the class diagram;
 A warehouse has one or more centers.
 A distribution center can receive none or many standing orders.
 A distribution center can receive none or many special orders.
 A standing order has one or more products.
 A special order has one or more products.
 A distribution center has a stock of one or more items where each item has a quantity that
is available in stock.
 A customer can have none or one shopping list.
 A shopping list has one or more items.
 A customer can make or more orders and an order can have one or more items.
 An order results to and only one payment.
Database schema
Schema is the design representation of tables, it contains the attributes of data and how they will
be arranged in the table. Below is an example of table schema
Table customer
customerID username password address.
Table shoppinglist
customerID sweetEntry date

Pitfalls We Wish to Avoid
Redundancy and loose of data
When designing our schema, we want to do so in such a way that we minimize redundancy of
data without losing any data. By redundancy, I mean data that is repeated in different rows of a
table or in different tables in the database.
Anomalies
These are problems caused on the data because of the flaws in the database. Anomalies can occur
in three ways. We will discuss how they occur using the table schema used above.
Insertion Anomalies
This anomalies occur when we try to insert data from a referenced table which does not exist in
that table. For example, it is not possible to insert data to the shopping list using a customer Id
that does not exist in the customer table. The customer Id in the shopping list table reference the
customer id in the customer table. To avoid this anomalies we need to add data to customer table
before inserting to shopping list table.
Deletion anomalies
Imagine if we could have a table called departmentEmployee table that’s hold the employee and
the department details.
Employee ID Employee Name Department Id Department Id
Imagine if we have a deparment with the an ID 200 if we decide to delete the department using
its departmentID we will delete all employees on that department causing loss of data. To avoid
this anomalies we can create two tables department Table and Employee table.
Update anomalies
Occurs when we try to delete columns in the database that has design flaws. Using the above
schema of employeedepartment table. If we want to update the name of employee with employee
Id 23 and belongs to department id 200. The employees from the department 200 data will be
updated causing the loss of data.
Null values
When designing database its good to avoid using attributes with a null value. Imagine if we want
to add a new attribute with a null value in the customer table. If the table has 20 columns it mean
that row will have null values for all the columns.
Functional Dependencies
We need to understand the functional dependency before we understand about normalization
which we will discuss next. Afunctional dependency occurs between two columns in a given
table. If functional dependency occurs between two columns that is column X and column Y
written as X  Y the values of col X will determine the value of col Y. For example in the