Antoine Folka

Crime Reporting System ER Diagram

This article will discuss the step by step process on how to prepare the entity relationship diagram or ERD of the project entitled Crime Reporting System.

The objective of the project entitled Crime Reporting System is to provide an avenue for the residents and police to report and respond to crimes reported through this platform. The project was designed and developed in PHP, MySQL and Bootstrap. For more information about the project, you may visit the database design and screen layout of crime reporting system, just search for crime reporting database design and crime reporting user interface.

The first step in the development of the Crime Reporting System is to prepare the ER diagram that will serve as the basis later on in the creation of the actual database.

We will create and explain the process of making the entity relationship diagram of crime reporting system.

Let's start from the symbols used in the ER Diagram.

Entity is represented by the rectangle shape. The entity will be our database table of Crime Reporting System later on.

Attribute is represented by the oval shape. This will be the columns or fields of each table in the Crime Reporting System.

Relationship is represented by diamond shape. This will determine the relationships among entities. This is usually in a form of primary key to foreign key connection.

We will follow the 3 basic rules in creating the ER Diagram.

1. Identify all the entities.
2. Identify the relationship between entities and
3. Add meaningful attributes to our entities.

Step 1. In the Crime Reporting System we have the following entities

• User
• Resident
• Resident Credential
• Crime Information
• Crime Category
• Police Information
• Response Information

We will now draw the entities of the Crime Reporting System specified above and it will be represented by a rectangle shape.

Step 2. After we have specified our entities, it is time now to connect or establish a relationship among the entities.

• User encodes information of residents (1 to many relationship).
• A resident uploads or submits their resident credentials (1 to many relationship).
• A resident reports a crime through the online and mobile application of the project (1 to many relationship).
• User encodes information of police officer (1 to many relationship).
• The police officer responds to the crimes reported by the residents (1 to many relationship).
• The crime belongs to a specific crime category (1 to 1 relationship).

Step 3. The last part of the ERD process is to add attributes to our entities.

User Entity has the following attributes:

• User ID – primary key represented with underline
• Full name
• Address
• Contact
• Username
• Password

Resident Entity has the following attributes:

• Resident ID – primary key represented with underline
• Resident Name
• Resident contact
• Resident address
• Image
• Username
• Password
• Encoded By – foreign key

Resident Credential Entity has the following attributes:

• Credential ID – primary key represented with underline
• Resident ID – foreign key
• Credential Info

Crime Information Entity has the following attributes:

• Crime ID – primary key represented with underline
• Crime Description
• Resident ID – foreign key
• Date Reported
• Time Reported
• Location
• Image Capture
• Status

Crime Category Entity has the following attributes:

• Crime Category ID – primary key represented with underline
• Description

Police Information Entity has the following attributes:

• Police ID – primary key represented with underline
• Name
• Mobile Number
• Address
• Email
• Username
• Password
• Encoded by – foreign key

Response Information Entity has the following attributes:

• Response Info ID – primary key represented with underline
• Crime ID – foreign key
• Status
• Date
• Time
• Police Officer ID – foreign key

Note: all attributes with underline represents the primary key of the entity or table.

The next step is to convert the plan designed on ER Diagram into the actual database, please search for the Crime Reporting System article which was already posted.

Contact us on our facebook page for the softcopy of the Crime Reporting System.

You may visit our facebook page for more information, inquiries and comments.

Hire our team to do the project.

Working with ER Diagrams

ER Diagram is a visual representation of data that describes how data is related to each other. In ER Model, we disintegrate data into entities, attributes and setup relationships between entities, all this can be represented visually using the ER diagram.

For example, in the below diagram, anyone can see and understand what the diagram wants to convey: Developer develops a website, whereas a Visitor visits a website.

---

Components of ER Diagram

Entitiy, Attributes, Relationships etc form the components of ER Diagram and there are defined symbols and shapes to represent each one of them.

Let's see how we can represent these in our ER Diagram.

Entity

Simple rectangular box represents an Entity.

Relationships between Entities - Weak and Strong

Rhombus is used to setup relationships between two or more entities.

Attributes for any Entity

Ellipse is used to represent attributes of any entity. It is connected to the entity.

Weak Entity

A weak Entity is represented using double rectangular boxes. It is generally connected to another entity.

Key Attribute for any Entity

To represent a Key attribute, the attribute name inside the Ellipse is underlined.

Derived Attribute for any Entity

Derived attributes are those which are derived based on other attributes, for example, age can be derived from date of birth.

To represent a derived attribute, another dotted ellipse is created inside the main ellipse.

Multivalued Attribute for any Entity

Double Ellipse, one inside another, represents the attribute which can have multiple values.

Composite Attribute for any Entity

A composite attribute is the attribute, which also has attributes.

---

ER Diagram: Entity

An Entity can be any object, place, person or class. In ER Diagram, an entity is represented using rectangles. Consider an example of an Organisation- Employee, Manager, Department, Product and many more can be taken as entities in an Organisation.

The yellow rhombus in between represents a relationship.

---

ER Diagram: Weak Entity

Weak entity is an entity that depends on another entity. Weak entity doesn't have anay key attribute of its own. Double rectangle is used to represent a weak entity.

---

ER Diagram: Attribute

An Attribute describes a property or characterstic of an entity. For example, Name, Age, Address etc can be attributes of a Student. An attribute is represented using eclipse.

---

ER Diagram: Key Attribute

Key attribute represents the main characterstic of an Entity. It is used to represent a Primary key. Ellipse with the text underlined, represents Key Attribute.

---

ER Diagram: Composite Attribute

An attribute can also have their own attributes. These attributes are known as Composite attributes.

---

ER Diagram: Relationship

A Relationship describes relation between entities. Relationship is represented using diamonds or rhombus.

There are three types of relationship that exist between Entities.

1. Binary Relationship
2. Recursive Relationship
3. Ternary Relationship

---

ER Diagram: Binary Relationship

Binary Relationship means relation between two Entities. This is further divided into three types.

One to One Relationship

This type of relationship is rarely seen in real world.

The above example describes that one student can enroll only for one course and a course will also have only one Student. This is not what you will usually see in real-world relationships.

One to Many Relationship

The below example showcases this relationship, which means that 1 student can opt for many courses, but a course can only have 1 student. Sounds weird! This is how it is.

Many to One Relationship

It reflects business rule that many entities can be associated with just one entity. For example, Student enrolls for only one Course but a Course can have many Students.

Many to Many Relationship

The above diagram represents that one student can enroll for more than one courses. And a course can have more than 1 student enrolled in it.

---

ER Diagram: Recursive Relationship

When an Entity is related with itself it is known as Recursive Relationship.

---

ER Diagram: Ternary Relationship

Relationship of degree three is called Ternary relationship.

A Ternary relationship involves three entities. In such relationships we always consider two entites together and then look upon the third.

For example, in the diagram above, we have three related entities, Company, Product and Sector. To understand the relationship better or to define rules around the model, we should relate two entities and then derive the third one.

A Company produces many Products/ each product is produced by exactly one company.

A Company operates in only one Sector / each sector has many companies operating in it.

Considering the above two rules or relationships, we see that although the complete relationship involves three entities, but we are looking at two entities at a time.

---

---

We are well into the second month in the great blank-slate world of Nowhere, and Domain Game players are busy taming—or being tamed by—this brave new world. While cyclopean ruins, mysterious golden barges, vast metal domes, white-furred apes, and other oddities have beguiled the expeditions from Elsewhere, they are making a go at the more prosaic job of settling into their new dominions.

Everywhere is the sound of hammers, saws, and the rest of the general din of construction. Ziggurats, longhouses, towers, stockades, mud huts are slowly and surely being raised in this howling wilderness.

And as goes the flurry activity so go the gnomes behind the scenes working to ensure the smooth flow of the natural laws of the new world—that is the in-game mechanics for the larger rules supplement project.

For the rest of you we give you this today: construction rules for your classical D&D game. For the full version including road construction, underground excavation, and all the lovely modifiers that give you that fine crunch you can find it here as part of the revised rules guidelines (including some other rules for would-be overlords).

Construction

To build a structure:

1. the type of structure(s) to be built

2. The number of ten-foot square cubes to be built of that structure

3. Number of workers assigned

4. Where the materials are to come from etc.

Example: Margrave von Schadenfreude wants to have 50 followers start working on five small one-story wooden cottages that are 10 feet by 30 feet (3 cubes).

Since the Margrave's project is in a wilderness area, he will direct another group of his followers to harvest the needed materials directly. Thatch from the grasslands will be used and timber cut from the adjacent light woods.

Total labor time for the project is 480 worker/days (15 cubes x 32, the base-rate for an average wooden structure). Since he has 50 workers on the project it will take them 9.6 days (480 divided by 50) to complete building the cottages–barring any delays.

Baseline Cost and Time

All work based on 10'x10' "cubes". All prices applicable if structure is in a borderlands or civilized hex. Substitute gp equivalent amount of materials for wilderness area.

Any building over two cubes must have at least one semi-skilled worker for eight cubes. Any building over 24 cubes, must have an engineer or architect assigned for every 48 cubes.

Material	Price per Cube	Construction Time
Primitive	10 gp	25 worker/days
Mudbrick	20 gp	30 worker/days
Wooden	30 gp	32 worker/days
Brick	110 gp	90 worker/days
Stone	140 gp	160 worker/days

Quality level

Building is assumed to come with basic furnishings, roofing, and flooring appropriate to the quality level. Additional or unusual furnishings must be purchased separately.

Cheap	75% price/time
Average	100% price/time
Comfortable	150% price/time
Semi-Fortified	200% price/time
Luxurious	250% price/time
Fortified	300% price/time

Vertical building

Second-story building is free. Add 25% to the cost for each additional story.

Misc. Structures

	Amount	Cost	Construction Time
Wood fence	10' length, 6" wide	20 gp	5 worker/days
Wooden palisade	10' length, 2' wide	80 gp	25 worker/days
Ditch	10' length, 10' wide	60 gp	20 worker/days
Abatis	10' length, 3' wide	40 gp	10 worker/days
Earthworks	10' length, 5' wide	60 gp	15 worker/days
Stone curtain wall	10' length, 5' thick	250 gp	100 worker/days
Reinforced door	1 panel	40	2 worker/days
Portcullis	1 panel	600	15 worker/days
Secret door	1 panel	400	20 worker/days