5.4.0.2 Database Schema Tables
Table Problem
|
Name
|
Date type
|
Null
|
|
Problem ID
|
Integer(12)
|
not null
|
|
Type of problem
|
Varchar(255)
|
not null
|
|
Date of problem
|
Time stamp
|
not null
|
|
problemdesc
|
Varchar(255)
|
Not null
|
|
status
|
Varchar(20)
|
not null
|
Table solution
|
Name
|
Date type
|
Null
|
|
Solution ID
|
Integer(12)
|
not null
|
|
solution
|
Varchar(255)
|
not null
|
|
Date of solution
|
Time stamp
|
not null
|
|
status
|
Varchar(20)
|
not null
|
|
Solved by
|
Varchar(25)
|
not null
|
Table employee
|
Name
|
Date type
|
Null
|
|
Employee ID
|
Integer(12)
|
not null
|
|
Full name
|
Varchar(255)
|
not null
|
|
Department
|
Varchar(25)
|
not null
|
|
Building
|
Varchar(20)
|
not null
|
Table report
|
Name
|
Date type
|
Null
|
|
Report ID
|
Integer(12)
|
not null
|
|
Employee ID
|
Integer(12)
|
not null
|
|
Problem ID
|
Integer(12)
|
not null
|
|
Solution ID
|
Integer(12)
|
not null
|
|
Date of report
|
Timestamp
|
not null
|
Table comment
|
Name
|
Date type
|
Null
|
|
comment ID
|
Integer(12)
|
not null
|
|
Full name
|
Varchar(255)
|
not null
|
|
Date
|
timestamp
|
not null
|
|
message
|
Varchar(20)
|
not null
|
Table user
|
Name
|
Date type
|
Null
|
|
ID
|
Integer(12)
|
not null
|
|
username
|
Varchar(255)
|
not null
|
|
password
|
Varchar(255)
|
not null
|
5.4.0.3 Entity relationship diagram (ERD)
An Entity Relationship Diagram (ERD) is a diagram that shows
how tables in a database are linked together and how they interact with each
other in the database. It can be described as a graphical representation of the
data requirements for a database.
There are 5 major parts to an ERD:
a) Entity
An entity represents a person, place, or thing that you want
to track in a database. E.g. in this system, a customer, admin are entities.
The entity becomes a table in the database.
Each occurrence of the entity is an «Entity
Instance». This is becomes a record or a «row» in the table.
E.g. a single name is an instance of the entity -tbl_problem.
b) Attribute
An attribute describes various characteristics about an
individual entity. They tell us more about an entity. The characteristics
become the «columns» in the table. E.g. each problem has an Id and a
problem type.
c) Primary Key
A primary is an attribute of group of attributes that uniquely
identifies an instance of the entity.
d) Relationship
A relationship describes how one or more entities interact
with each other. A verb is often used to describe the relationship. E.g. a
problem has a unique problem ID.
Relationships can be between two instances of entities or
more. Other times you don't even need an instance for a relationship to occur.
Relationships are explained through cardinality.
e) Cardinality
Cardinality is the count of instances that are allowed or are
necessary between entity relationships E.g. A client can have zero, one or
multiple problem, but each problem can only belong to one solution. One of the
ways to represent cardinality in an ERD is by use of Crow's Foot Notation.
There are four different ways to represent cardinality using
Crow's foot Notation.
· One-Mandatory means that you must have
at least one and only one instance.
· Many-Mandatory means that you must
have at least one instance but you can have several instances.
· One-Optional means that you don't have
to have an instance but if you do, you can only have one.
· Many-Optional means that you don't
have to have an instance but if you do, there isn't a limit as to how many
instances you can have.
Figure 5.21: ERD diagram
| 
