HS2021 Time Tracking Database System Assignment Help

HS2021 Time Tracking Database System Assignment Help

HS2021 Time Tracking Database System Assignment Help

Entity- relationship model

ER model is composed of entities and their relationship with other entities in specific domain of interest. It model or diagram represents the data model which is useful to perform business operation with their help. However, model does not describe business operations but details the data schema to use in business operations. Entities are solely not defined without attribute assigned to them.

HS2021 Time Tracking Database System Assignment HelpComponents of ER Model

Entity: Entity represents smallest atomic unit in real world which is easily identifiable (Kroenke and Auer, 2010). For instance, class, student, file, number etc. But an entity itself has no meaning unless there is an attribute to define it. Entities are represented with rectangle.

Attribute: The property of entity is called attribute. For example: student is an entity but name of student represent the property of student. In same manner, mobile number, mail address, district etc are attributes. Attributes are represented with oval structure.

Key attributes:attributes those can uniquely identify the record in database are called key attributes. For example, roll number and employee ID are key attributes as they are unique in every raw (Thalheim, 2013). It is shown with underline in oval.

Composite attributes:Attributes those can be divided into more parts are composite attributes. For example, Address is composite attribute which can be divided into street number, city, district and state. Composite attributes are represented with double oval.

Relationship:The association among entities is termed as relationship. Relationship is represented with diamond shape and may be of following types:

One-to-one relationship (1:1): When one entity of table has relationship with single entity in another table, it is one to one relationship. For instance, husband wife relationship is one to one.

One-to-many relationship (1: M):One entity having relationship with more than one entities in another table is termed as one-to-many relationship (Teorey.et.al, 2011). For example, one football team has more than one player.

Many-to-many relationship:relationship in which more than one entity from a table have relationship with more than one entity in another table. For instance, many students may involve in many courses from university.

relationship in ER diagrams

Normalization

Normalization is the process to remove update and insertion anomalies and data redundancy in database. Database tables are split with more atomic attributes and unified values with interconnected entities to perform normalization.

First Normal Form (1NF): For a table to be in first normal form, it must to hold only atomic values, not the composite and separate values or group of information. For instance, below table is not in first normal form as columns contain more than one value (Elmasri and Navathe, 2011).

First Normal Form

Normalised form

Normalised form

Second Normal form (2NF): As according to second normalization form, database tables must need to contain a primary key in column on which all other columns are dependent. There should be no column which is partially or not dependent on primary column. Also table must in fist NF to transform in 2NF. For example: candidate key in table is {student, subject} where has is only depend on the student and has no relation with subject. Table can be split into two tables to make proper use of space as Adam is used twice to match the subjects.

Second Normal form

Third Normal Form (3NF): It states that every non-prime attribute in table must be dependent of primary key. In other words, one non-primary attribute cannot be determined by other non-prime attributes those exist in table. There should not be transitive dependency between attributes (Özsu and Valduriez, 2011). The input table must need to present the characteristics of second normal form.

For example, below table is in second NF but not in 3NF because street, city and state are dependent of zip which is not a primary key. This is transitive dependency.

Stud_id

Stud_name

Date_of_birth

Street

City

State

Zip

121

Adam

07-05-2000

5

Bangor

Wales

67343

122

Alex

05-07-2003

90

Cardiff

Wales

67332

Table in third normal form can be create in following manner where zip in new table is created as primary key for table.

Stud_id

Stud_name

Date_of_birth

Zip

121

Adam

07-05-2000

67343

122

Alex

05-07-2003

67332

Zip

Street

City

State

67343

5

Bangor

Wales

67332

90

Cardiff

Wales

Now the tables are in third normal form and have following advantages for database:

Data integrity achieved with elimination of transitive dependency (Coronel and Morris, 2016).
Data duplication removed for database.

 

Establishing a Crime-Tracking Database System

For the crime tracking database system, the entities will be criminals,criminals_phone,criminal_aliases,crimes,crimes_arresting_officer,sentencing,appeals, police_officer.

Column name: shows each attributes of table
Data type: shows the type of the attributes like size.
Comment: extra information related to attributes so we can understand the role of attributes.

Table 1: Criminals

The table stores the personal record of the criminals; the primary key of the table would be c_id.

Column Name

Data type

Comment

c_id

Number(8)

primary key of criminal

c_name

Varchar2(24)

Stores the name of the criminal

c_address

Varchar2(240)

stores the address of the criminal

c_violent_offender_status

char(1)

stores the violent offender status of criminal(yes or no)

c_probation_status

char(1)

stores the probation status of criminal(yes/no)

Table 2: Criminals_phone

Column Name

Data type

Comment

c_id

Number(8)

unique key of criminal

c_phone

Number(12)

store phone number of criminal,it may be two contact no of any criminal

A criminal may have two contact numbers. For making the table to 3 NF, we have to follow the rule of third normal form it should be atomic, non transitive dependency.

c_id will be the foreign key to criminals table.

Table 3: Criminals_aliases

Column Name

Data type

Comment

c_id

Number(8)

unique key of criminal

c_aliases

varchar2(24)

criminal aliases name

c_aliases shows the nick name of the criminal like(franky,fatty,blacky etc), a criminal may habe two or more than two name. So it should be atomic value in table and remove the transitive dependency.

Table 4: Crimes

Column Name

Data type

Comment

c_id

Number(8)

Candidate_Key

cr_id

Number(8)

Primary Key

classification

Varchar2(24)

store crimes classification (felony, misdemeanour, other)

date_charged

Char(1)

date charged of crime (yes or no)

appeal_status

Varchar2(24)

status of appeal (closed, can appeal, in appeal),

hearing_date

Date

hearing date of criminal crime

appeal_cut_off_date

Date(

appeal date from criminal(after 60 days of previous hearing)

crime_codes

varchar2(240)

100 of codes of crimes ( burglary, forgery, assault etc)

amount

number(12)

total amount of file

court_fee

number(12)

court fees

payment_status

Char(1)

payment status (yes or no)

payment_due_date

Date

last date of payment

charge_ status

varchar2(12)

status of charge(pending, guilty, not guilty)

In this table we have the records of crimes committed by a criminal. The primary key of the table would be cr_id

Table 5: Crime_arresting_officer

Column Name

Data type

Comment

c_id

Number(8)

candidate key of criminal table

cr_id

Number(8)

candidate key of crime table

po_id

Number(8)

unique key of police officer table

Table 6: Sentencing

Column Name

Data type

Comment

c_id

Number(8)

Part of primary key

cr_id

Number(8)

Part of primary key

start_date

Date(dd/mm/yyyy)

start date of sentences

end_date

Date(dd/mm/yyyy)

end date of sentences

no_violation

Varchar2(12)

which is not reporting to probation officer

type_sentence

Varchar2(12)

different type of sentences(jail period, house arrest, probation)

In this table we save the record of sentencing, type of sentences like as criminal on probation or house arrest or in jail, start date, end date, number of violations who they are not reporting to probation officer. c_id, cr_id make a primary key of sentencing table.

Table 7; Appeals

Column Name

Data type

Comment

c_id

Number(8)

unique key of criminal table

cr_id

Number(8)

unique number of crimes

a_id

Number(8)

primary key

appeal_filling_date

Date(dd/mm/yyyy)

appeal filling date of criminal

appeal_hearing date

Date(dd/mm/yyyy)

appeal hearing date of criminal

appeal_status

varchar2(12)

status (pending, approved and disapproved)

Table 8: Police_officer

Column Name

Data type

Comment

po_id

number(8)

primary key

po_name

varchar2(12)

name of police officer

po_precinct

varchar2(12)

precint of police officer

po_badge no

varchar2(12)

badge no of police officer

po_phone_no

number(12)

contact no of police officer

po_status

char(1)

status of police officer(active/inactive)

In the police officer table save the personnel record of police officer,which po_id,po_name,po_precinct,po_badge no.,po_phone no,po_status but in this table one police officer may contain two contact number then we have to make one more table.

Table 9: Police_officer_phone

Column Name

Data type

Comment

po_id

number(8)

unique identification no. of police officer

po_phone_no

number(12)

contact no of police officer

We make this table because we have to follow third normal form. it should atomic, on-functional dependency.

ER Diagram

ER Diagram

References

Books and Journals

Kroenke, D. and Auer, D.J., 2010. Database processing (p. 126). Prentice Hall.

Thalheim, B., 2013. Entity-relationship modeling: foundations of database technology. Springer Science & Business Media.

Teorey, T.J., Lightstone, S.S., Nadeau, T. and Jagadish, H.V., 2011.Database modeling and design: logical design. Elsevier.

Elmasri, R. and Navathe, S., 2011. Database systems (Vol. 9). Pearson Education.

Özsu, M.T. and Valduriez, P., 2011. Principles of distributed database systems. Springer Science & Business Media.

Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management. Cengage Learning.