Spatial Databases and Data Management Assignment 1
PART A
System Specification
Warehouse facilities management limited is newly contracted to manage a
warehouse of a sports company as well as all the assets in the warehouse. They
have identified the need of a new database management system to meets all the
requirements of their facility tasks. The main tasks are listed below.
1. Maintenance- to make sure all assets like CCTV camera fire alarm and fire
extinguisher running well and replace them in time.
2. Monitoring- making sure all the goods and assets stored safety avoiding
accidents e.g. being stolen and fire accident.
3 Making sure the process of storing goods is running properly.
The warehouse have been divided into two zones, zone 1 is for menswear zone 2
is for womenswear. There are there main tasks of the warehouse, first, receive
the import goods, second, storing the goods and distribute them into each zones,
third, export goods to customer. Under the terms of their contract, there are two
zones in the warehouse, zone 1 is for manswear and zone 2 is for womanswear.
The number and the use of zones could be changed. Warehouse and zones are
modeled in 3D. There is a aisle between two zones with 10 meters length and
same width as the zones aiming to avoide fire accident. Cameras work 24/7, the
main jobs is to prevent losing assets and checking around to make sure all the
potential risk of fire can be avoid. Cameras need to be replaced every 2 years.
Fire extinguishers need to be replaced every 5 years. There are several Forklift
trucks with different colors in the warehouse to help moving large heavy goods
to improve the efficiency. The forklift car can carry 2000 pounds weight and
average weight of cloth is 2.5 pounds. Besides, the average surface area of a cloth
is 0.05 meter square.
Entity Table
Entity# Entity Name Spatial 3D
1 Company Yes No
2 Warehouse Yes Yes
3 Zone Yes Yes
4 Forklift Truck No No
5 Goods Yes No
6 Fire Alarm Yes Yes
7 Fire Extinguisher Yes No
8 CCTV Camera Yes Yes
Totals 8 7 4
Requirements Table
Requirement# Requirement
Entity or
Entities
Required
Spatial
Query
Join
1
How many fire
alarms in zone 1
Fire Alarm and
Zone
Yes Yes
2
Count the
number of CCTV
camera in zone 2
Camera and
Zone
Yes Yes
3
Find the yellow
color forklift car
Forklift Car No No
4 What is the Fire Yes No
distance
between the two
fire
extinguishers
extinguisher
5
Calculate
maximum
amount of
goods in zone 1
based on the
surface area of
zone1
Goods and
Zone
Yes Yes
6
Determine which
Camera need to
be replaced and
which zone they
are in
Camera and
Zone
Yes No
7
Determine a
safety perimeter
of 10 m from
each zone in
case of fire.
Zone Yes No
8 Calculate zone Zone Yes No
1's volume
9
Determine
maximum
number of
goods the
forklift truck can
carry
Goods and
Forklift truck
No Yes
10
Calculate the
surface area of
the warehouse
Warehouse No No
Totals
10
N/A 7 4
PART B
E-R diagram
i. Entities and Relationships:
Entities: The entities including the company, which is the owner of the
warehouse; a warehouse is the 3D volume for the spatial database; a zone means
a spatial part to store goods within a warehouse; a forklift truck refers to the
vehicle working in the warehouse to help carrying the goods; a piece of goods
means a piece of collection of the product that produced by the company and
being stored in the warehouse; the fire alarm is a set of equipment installed on
the celling of the warehouse to avoid having a fire; a fire extinguisher is an active
fire protection device used to extinguish or control small fires spreading in
different locations of the warehouse; the CCTV camera is Closed-circuit television
cameras that can produce images or recordings for surveillance purposes and
also spread out in the whole warehouse.
Cardinality of Relationships:
The company owns the warehouse and the warehouse belongs to the
company, and the relationship is from one to many because a company can
have many warehouses and one warehouse can only belongs to a company.
The warehouse contains different zones and zones are inside the warehouse,
and the relationship is from one to many as one warehouse can have many
zones and one zone can only inside one warehouse.
The forklift car passes through zones, and the relationship is many to many
as one forklift car can pass through different zones and one zone can have
many forklift cars. This relationship will be eliminated in the logic model.
The fire alarm avoids fire accidents in zones, and the relationship is one to
many because one zone can have many fire alarms and one fire alarm can
only exists in one zone.
The fire extinguishers are installed in zones, and the relationship is one to
many as one fire extinguisher can only in one zone and one zone can have
not only one fire extinguisher.
The goods are stored in zones, and the relationship is one to many as a good
can only stay in one zone and one zone may have many goods.
Each zone is monitored by CCTV cameras, and the relationship is one to
many as one camera can be only placed in one zone and one zone may have
more than one camera.
Cardinality of Attributes Identifiers:
- Company name and location are the attributes for the company entity. Both of
the attributes are mandatory with minimum value of 1 and also both of them
are identifiers.
- Location and Name are the attributes for the Warehouse entity. Both of them
are mandatory with minimum value of 1 and also both of them are identifiers.
- Location, number and zone use are three attributes of the zone entity. All of
them are mandatory with minimum value of 1, and location is identifier. Zone
use and number are not identifiers. Because there could be same zone use in
different zones and the number of zone can change.
- Color, brand and purchase date are the attributes of the forklift car entity. All
of them are mandatory with minimum value of 1, while only location is
identifier. Because the brand of cars can be the same and they can also be
purchased at the same time.
- Location and install date are the attributes of the fire alarm entity. Both of
them are mandatory with minimum value of 1. However, only location is
identifier, because the fire alarms can be installed at the same time.
- Location, purchase date and last replaced date are the attributes of the fire
extinguisher entity. The location and purchase date are mandatory, while last
replaced time is optional because the site of fire extinguisher can be newly
established and has never replaced. At the same time, only location can be the
identifier because the purchase date and last replaced time can be same for
different fire extinguishers.
- Location install date and last replaced date are the attributes of CCTV camera
entity. The location and install date are mandatory, while last replaced time is
optional because the site of CCTV camera can be newly established and has
never replaced. At the same time, only location can be the identifier because
the purchase date and last replaced time can be same for different cameras.
- Location, import time and export time are the three attributes of goods entity.
The location and import time is mandatory with minimum value of 1, while
export time is optional with minimum value of 0, because the goods may
haven’t decide the export time. As for identifiers, only location can be
identifier, because the different goods can be imported or exported at the
same time.
ii. Business rules
Entity definition
- Company is defined as an organisation that sells good or service in order to
do make profit. This company’s main business is selling sports wear to make
money.
- A warehouse is a commercial building for storing goods. They are usually
used by manufactures and they are often plain large buildings located in town
and village. Normally goods will be sent to the warehouse after manufactured
and warehouse will distribute them to the customers.
- A zone is an area or stretch of land having a particular characteristic, purpose,
or use, or subject to particular restrictions. There are two zone in the
warehouse. And the two zones are locating separately with distance between
each edge.
- A forklift truck is a vehicle with a pronged device in front for lifting and
carrying heavy loads. During the working time all tucks are working in the
warehouse, it can move where it is needed with no limits of the zones.
- A fire alarm is a device making a loud noise that gives warning of a fire. Each
zone will have at least one fire alarm to make sure the safety of the assets of
the warehouse. And all the fire alarms are installed on the top of the ceiling.
- A fire extinguisher is a portable device that discharges a jet of water, foam,
gas, or other material to extinguish a fire. There are two extinguisher in the
warehouse and each zone will have one to extinguishing fire in time and
minimize the lose due to fire.
- CCTV cameras are defined as video-recording devices in a TV system in which
signals are not publicly distributed but are monitored, primarily for
surveillance and security purposes. The cameras exists in all the zones to
make sure all each corner in the warehouse can be monitored.
- Goods is the sportswear that the company selling and storing in the
warehouse. Every cloth in this warehouse has an import time and export time
that indicates the time they are in and out.
Integrity constraints
- Company: Company name and location provides the unique identifier for the
company entity.
- Warehouse: Warehouse location and name provides the unique identifier for
the warehouse entity.
- Zone: Zone location provides unique identifier for the zone entity.
- Forklift Car: The car’s color provides unique identifier for the forklift car
entity.
- Fire Alarm: The location of the fire alarm provides the unique identifier for
fire alarm entity.
- Fire Extinguisher: The location provides the unique identifier for fire
extinguisher entity.
- CCTV Camera: The location provides the unique identifier for the CCTV
camera entity.
- Goods: The location provides the unique identifier for the good entity.
iii. Derivations:
- To calculate the maximum amount of goods that a forklift car can carry
requires the formulae: maximum carrying weight divided by average weigh of
good.
- To calculate the surface area of warehouse requires the formulae: width of
the warehouse times length of the warehouse.
- To calculate the volume of zone 1 requires the formulae: width of zone 1
times length of zone1 times height of zone1.
- To calculate the maximum amount of goods can be stored in zone 1 requires
the formulae: surface area of zone1(length of zone1 times width of zone1)
divided by the average surface area of a cloth.
- To calculate the distance of two extinguishers requires the formulae: square
root of (the length of the warehouse’s square times width of the warehouse’s
square).
iv. Assumptions
The warehouse is a cube with is 60 meters length, 30 meters width, and 10
meters height.
Zone 1 has 30 meters length, 30 meters width, and 10 meters height. Zone 2
has 20 meters length, 30 meters width, and 10 meters height.
The distance between two closest edges of two zones (aisle) is 10 meters for
the safety reason.
A warehouse can only belongs to one company while a company can own
many warehouses to store goods.
The forklift cars are distinguished by different colors each truck will only
has one color. And they can work where it is required no matter zones.
All the goods are put horizontally in a zone with no overlap and no space
between clothes so location of goods is unique.
The export time attribute can be null in goods entity, when the goods are
still in the warehouse.
Each zone will only have one fire extinguisher. Two fire extinguishers are
located at the opposite corner of each zone. The distance can be calculate by
their coordinate.
Every zone will have at least one camera and fire alarm.
Fire alarms and CCTV caremas located on the top of the ceiling with the
same height as the warehouse. Besides they will not overlap with others.
Each of the fire alarm and caremas has its unique location.
Knowing the height of Fire alarms and caremas are important so they are
modled in 3D.