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 distance between the two fire extinguishers
Fire extinguisher
Yes
No
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 1's volume
Zone
Yes
No
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
image.png
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.
