35640/15393 Database Systems and Approaches Assignment Sample 2024

Introduction to the Report

The assessment is based on the report to develop SQL queries for a provided data. This is an effective method for implementing the ideas of the system and these can be rationalised with the development of EERM and SQEE systems that are relevant for the implementation of the system issues.

1. EERM

Extended Enterprise Risk Management can be effective in terms of the system diagram and the relevance of the system can also be implied with the developed query of the system.

1.2 Extended Entity Relationship Diagram (EER Diagram)

Producing an Extended entity-relationship Model (EERM)

Enhanced entity-relationship diagrams usually are counted as the extended version of the normal diagrams. EER diagrams are considered to be the most helpful tool available in the market for designing databases with high-level models (Komar et al. 2020, p. 555). EER diagrams are generally equipped with all types of ER diagrams components such as adding attributes or relationship inheritances. In this study, the researcher is going to create a database related to a crime that occurred in England and wales. The entire ER diagram is being created based on accused victims. In this research, the ERD model that is the Entity Relation Model of the system is shown and that has a hugely significant role in the process. As per the view of the researcher, there are various kinds of a class present in the system that is shown in the figure and they have different kinds of roles. An Entity-relationship model mainly describes the structure of database based on provided scenario or case study. It is mainly a blue print which can be applied later as a database. The actual components of those models are Relationship and Entity set. The ER diagram mainly visualizes the relationship among various entities. The entities are group of product which can be used as per the similar entities in DBMS. The entity set is the group of similar types of entities and these contains different types of attributes. It is also known as graphical approach to the database design with high level data modeling which defines different elements along with their relationship using a specific software system. The ER model represents the objects of real world that is distinguishable from the surrounding. There are various characteristics such as it must have a set of properties and it contains values.

Figure 1: Extended Entity-Relationship model

(Source: Created using QSEE Superlite)

Figure 2: Extended Entity Relationship model

(Source: Created using QSEE Superlite)

This is a typical image of an Entity-relationship model, created using QSEE Superlite, the software. The above model is being created on crime-related data of England and wales. In this diagram, the researcher wants to derive one-to-many, one-to-one, and many-to-many relationships. For showing all the small details in an organized way this ERD diagram has been created. BY using this EER diagram the police forces of England and wales would be very pleased (Alexander, 2018, p. 56).

In this model for showing the relationship between different entities, the researcher has developed 8 different tables. The first table is all about showing the details of the Victim who is being accused of some crime. The victim table is filled with so many details such as victim name, victim Id, address, and contact number. One victim could only have one unique victim Id means this would be worked as the primary key in the table. The other table suggests all the crime details of a particular area. Generally, crime resistor is filled with full of crime details and details of every single crime along with the victim. A crime resistor should have the following attributes such as crime id that indicates all the occurred crime details like crime description. On the other hand, the other attributes are case states, the id of the officer who arrests the accused public, and the most important thing police station’s name. In this table, crime id can be used as a primary key (Gupta et al. 2017, p. 56). Only having the primary key would be m, more than enough to get all the crime details. Generally, one crime resistor has so many victims’ names and their details. It establishes one too many relations from crime resistor to victim table. The next table is all about deriving the details of the police station. The police station table has the following attributes – Police station ID, Police station name and its location, contact details of the police station. Generally, a police station has many crime-related resistors that establish one-to-many relationships between the police station and the Crime resistor. On the other hand, the accused table shows all the details about the accused victim such as Accused Id, his/her name and age, gender, and most importantly his/her generated crime id. This crime id attribute would be working as the foreign key of this particular table. There is a relationship between crime resistor and accused victim. One crime resistor many accused victim details means it also shows a one-to-many relationships in the ER diagram. The next table is showing all details of the witness. The following attributes are Witness id and Crime Id. Normally, one witness id is being created per crime however there is a possibility of having many witnesses in one crime. However, this can be differentiated with the crime id the connector with the crime resistor. One crime resistor could have so many witnesses’ details so it indicates a one-to-many relationship with the crime resistor (Kim et al. 2020, p. 379).

On the other hand, witness details are combined with witness FIR details. It creates one to one relationship between the witness and witness FIR table. The next table is being created for listing all officer details. According to the ER diagram, one police station has so many officers’ records. It establishes one to many relationships between the police station and the official record. All the details about the ERD diagram have been derived thoroughly in this section.

1.3 List your attributes and identifiers here:

Crime_ID	Crime_DES	Victim_name	FIR_NO	Date_of_report	Case_Status	Officer_ID	Accused_ID	Police_Station_name	Address
1	Northwick	Steve, Benny	25	21.01.21	Approved	252	980	Cambria	Cheshire
2	Crewe	John, Mike	24	25.01.21	Pending	253	981	Bridgewater	Avon
3	Parkside	Glenn, Joe	21	14.12.20	Not Approved	254	982	Cleveland	Cleveland
4	Patch way	Phil	42	05.11.20	Pending	255	983	Thames	Thames

In this part, the researcher has described different information about normalization. In order to do this job, the researcher has used the criminal database. Different types of normalization process are also described in this part. Normalization is a database design technique that elements the undesirable characteristics and reduces data redundancy by deleting, updating and inserting anomalies in the database. Normalization divided a huge table into two smaller tables and creates a relationship between them (goyal et al. 2017, p. 5847). In a database management system, the main aim of normalization is to reduce duplicate data and store the data in a logical manner. Different types of normalization are available in the database management system, such as 1NF, 2NF, 3NF, 4NF, BCNF and so on. In this case, the researcher has done 1NF, 2NF and 3NF with the database. 1NF or first normalization form is the resources of relation in a relational database (gates-rector and blanton, 2019, p. 359). It is the most basic normalization form of a database. In this form, every cell of a table contains a single piece of data and it does not allow any types of duplicate data in any row. In the below section the researcher has visualized the main table structure and the table after doing 1NF. The tables that are used to explain the process are mentioned below.

1.4 Include a list of the Validation Questions of your model here:

In this research, the researcher implements the crime record tables that are used for the 1NF tables. The table shows that the entire process is separated for the victim name to execute the methods. This table contains the crime_id, crime_des, victim_name, FIR_no, Date_of_report, case_status, officer_id, accused_id, Police_station_name and Address. There are some records of different victim name that are based on the same Crime_id (Pizzol et al. 2017, p. 854). The values that are stored in the column should be of the same containing

1.5 A definition for each entity…..

Crime_ID	Crime_DES	FIR_NO	Date_of_report	Case_Status	Officer_ID	Accused_ID	Police_Station_name	Address
1	Northwick	25	21.01.21	Approved	252	980	Cambria	Cheshire
2	Crewe	24	25.01.21	Pending	253	981	Bridgewater	Avon
3	Parkside	21	14.12.20	Not Approved	254	982	Cleveland	Cleveland
4	Patch way	42	05.11.20	Pending	255	983	Thames	Thames

1.6 Occurrences for each entity…

Crime_ID	Victim_name
1	Steve
1	Benny
2	John
2	Mike
3	Glenn
3	Joe
4	Phil
Victim_ID		Name	Contact	Address
1		Smith Jones	6255845809	Cheshire
2		John Thomas	6588625105	Avon
3		Glenn Smith	6254478954	Cleveland
4		Phil Pattinson	9874589652	Thames

PS_ID	PS_NAME	Location	Phone	Office_ID
1	Cambria Police	Cheshire	8796854852	895
2	Bridgewater Police Station	Avon	6352418796	584
3	Cleveland Police	Cleveland	8547898563	589
4	Thames Police Station	Thames Valley	2541236589	785

2. Normalisation

Form 1

2NF is almost the same as 1nf and it is created based on the primary key of the table. Main aim of the second normalization form is to delete partial dependency on the primary key (franklin et al. 2017, p. 1963). In any case, a table doesn’t match the condition of 1nf them nobody can apply 2NF on this table. In order to create do the 2NF, the researcher has used the data of the 1NF table. In this part, the researcher has used primary key patient_id from the patient table in order to do the 2NF in a proper manner.

In this portion, the researcher implements two processes that can get through different situations to get the exact result for the steps (Bullock, 2018, p. 246). In order to implement the process, two tables show the entire evaluation of the methods. The first table contains the Crime_id, crime destination, FIR_no, date_of_report, Case_status, officer_id, Accused_id, Police_station_name, Address. Since it is the second form of database normalization, it gets fulfilled with the two requirements. It also carries with the non-prime attribute which can check the functional dependence of the proper subset. This can go with the relation of the same process that can match with the candidate key. The first table contains four records that show the entire crime records of the table (Grange et al. 2018, p. 6224). The second table shows the records of the crime_id and victim_name that can help to search the result in the entire situation procedures. The victim name can be easily checked with the criminal_id that can take out the proper result of the process. The non-attribute can be dependent on the proper type which can check the proper subset of the candidate key. Multiple records are shown in the second table to show that the overall process is implemented after using the 1NF process.

Form 2

3NF is another normalization form and it is created based on the 2NF of a database. Users used 3nf in order to remove duplication from a huge amount of database. Users also can active data integrity by using the 3NF. In case there has no transitive dependency for non-prime attributes in the database then the relationship must be available in the third normalization form. 3NF is happened based on the primary key and foreign key of a table. Key is a value that is used to define a record in a table in a unique manner (khamis et al. 2018, p. 9). In a database, a key can be presented by a single column or multiple columns. The primary key is the main key of a database, without a primary key a database is incomplete. The primary key is a single column value that is used to define a database record in a unique manner. The primary key does not contain any kind of duplicate data or null value and a table can contain only one primary key at a time. Another important key of a database table is the foreign key. A primary key is changed into a foreign key in another table. It is used to create a relationship between two or more than two tables (chowdhury et al. 2018, p. 1351). A table can contain more than one foreign key at a time, but does not allow any duplicate value in it.

In this portion, the researcher also implements the criminal records that can help in getting the result of the third normalization form (Tronconi, 2018, p. 164). There are three tables that are created to get detailed information for the records of using it specifically. The first table also explains the criminal records which only mention four records that have occurred in the various locations. This table also includes the police station name that can specify the records through which the cases were placed. This also reduces the duplication of data and avoids various anomalies that can resist the integrity of the data management. This is guaranteed by every column that is dependent on the primary key. The second table also explains the same records of the criminal_id and victim_name that can show the crime recordings. The last one shows the records of the police_id and station name that can go through in selecting the records for the incidents (Vafaei et al. 2018, p. 20).

3. COMPOSITE EERD (based on top-down and bottom up approaches) – using QSEE.

3.1 Composite EERD

Figure 3: Composite Model

Discuss any key changes to the model

A specific model that collects data from more than one Directquery Source is known as the Composite model. Through a composite model, a developer can able to create relationships between two or more tables ever after the tables came from different sources. Composite models have the ability to establish data connections from different source groups such as one direct query connection and an import connection. More than two data connections can be formed from different source groups through this composite model. This type of composite model does have a mixed storage capability that allows the user to import different data sources in the same model (Frimpong et al. 2018, p. 56). Composite is very much capable of storing individual tables and provides greater flexibility. In this way, an individual direct query table can be imported. Many-to-many relationships are being easily supported by the composite model.

4. FINAL LIST OF tables and attributes, defining all Primary Keys and any Foreign Keys(s) derived from the Composite EERD

In this part, the researcher is going to derive the composite model by producing the final list of relations for each table. Based on the composite model all the existing attributes are victim, crime resistors, Police station, Victim fir, accused, accused fir, officer record, witness, and witness fir. In this segment, the researcher is going to define both foreign key and primary key. For deriving the primary as well as foreign key first needs to know their definitions (Karahasanovic et al. 2017, p. 1).

Primary key

The commonly primary key is considered to be part of an existing table or a table purposely created by the database as per the defined sequence. The primary key is being used to identify all the unique identities present in a table within a database table. The primary key should always contain some unique values. A table full of null values cannot be primary. Whenever more than one field is being used as a primary they are known as a composite key (Chugh et al. 2017, p. 117).

Foreign key

A foreign key is a set of columns that are being used to refer to the primary keys columns present in another table. A table that contains the foreign keys is known as the child table on the other hand a table that contains the candidate key is referred to as the parent table. Foreign keys enhance the integrity of data level as well as also improve the performance as they are normally indexed. With the help of foreign keys, they develop no need to write long codes.

According to the composite model, Victim ID is going to be the primary key of this part means with this key Victim Id any victim can be found from anywhere in ENGLAND and wales. Victim IDs should be completely different from each other means it can differentiate all the victims from each other. On the other hand, the next table is all about victim fir, which is filled with two entities such as victim id and crime id. Here crime Id is being used as the sole primary key for this particular table. On the other hand, the victim id is being worked as the foreign key for the Victim table (Mensah et al. 2019, p. 56). The next table is being created for showing all the existing details about crimes that occurred in England and Wales surrounded areas. The crime resistor is filled with so many entities such as crime id, crime description, and victim name, date of report, case status, accused id, police station name, and address. In this crime resistor table, Crime id is considered to be the sole primary key. Through the crime Id, any information regarding crime can be leaked or pursued. Here Victim’s name can be considered as the foreign key for the victim table. With the help of the victim id, police officers can find the location of the crime. The officer id entity is being added to the crime resistor for finding the officer in charge on that particular case. The police station name has also been added to the table just to store more compact details about crime stories. With the help of this crime resistor, any police officer can be able to find out any criminal’s record. All criminal record has been stored in that crime resistor. The next table is formed for giving information about different police stations situated in England and Wales. Police id is considered to be the sole primary key of this particular table. One police id would be more than enough to recognize under which police a crime has been committed. The police station should have all the working officers’ IDs. In case of any trouble through officer’s ID, they can be traced. In this case, the police station name is being considered as the foreign key for crime resistors. There is a relationship between crime resistor and accused victim. One crime resistor many accused victim details means it also shows a one to many relationships. The main motive of creating this accused table is to separate them from the victim table. In order to separate both the tables, the researcher uses uniques IDs. In this way, they can be separated from each other. Accused is subsequently connected with the accused fir table where it indicates one-to-one relationships. The next table is demonstrating all details of the witness. The following attributes of the witness Fir table are Witness id and Crime Id. Generally, one witness id is being created per crime however there is a possibility of having many witnesses in one crime. In that case, witness id plays a crucial role without having a proper witness id all witnesses cannot be differentiated from each other. In this part, the researcher has firmly derived the concept of primary as well as a foreign key along with its working principles. In this section, all primary keys and foreign keys are indicated through the composite model.

5. Physical Design Discussion

By developing this composite, the researcher wants to derive the relation between these above-mentioned tables. The composite model is being combined with 8 different tables. Every attribute has its entities to work with (Sun et al. 2017, p. 454).

In the victim section, the researcher includes four different sections in order to establish the relationships between one-to-many tables. In that Victim for section, it represents entities like victim Id and crime id. That crime resistor section indicates so many entities related to the crime data. The following entities are crime IDs used as the primary key for this particular table, the other entities of this table are crime description, victim name, caser status, accused id, and name, police station name along with the address. On the other hand, the police station table is equally associated with the crime resistor just like the victim. The police station table contains the Police station id along with its name and location. For finding any victim of the accused public there needs to be an ID number that helps to recognize them in any situation (Ramos Corchado et al. 2021, p. 1803).

Victims should be arrested after committing the crime in that case having a police station is a primary need. For running a police station there must be some officers who could handle this kind of situation. For this context, an officer table has been created. Whenever a crime is being committed by victims a witness must be there. For this reason, the witness table has been created by the researcher. The witness table is firmly connected to the crime resistor.

In this particular segment, the researcher has firmly derived the composite model. The entire composite model is related to the criminal records that occurred in England and the Wales area.

Figure 4: Accused table creation