Crime record management system project.pptx

Crime Records

1. Crime record management system A project for Database management system 65-NIHARIKA THAKUR 66-DIYA PANDEY 67-GANESH SHINDE 68-TARUN SINGH 69-SHUBHAM CHAUHAN 70-IBRAHIM BUKHARI

2. Table Of Contents: Introduction E-R diagram Description Attributes Pl sql and it’s operations Create table Describe command Insert command Update Implicit cursor and loop Date and time Exception handling view Delete Alter Group by clause Having clause Order by clause Copy table Greater than operator joins: inner,outer and full join Index Cursor and loop Trigger conclusion

3. Introduction The proposed system specifically looks into the subject of Crime Records Management. User can register their complaints online. The system at any point of time can provide the details of existing charge sheets and their statuses. People can check missing persons details online using this system. The system at any point of time can provide the details of the police station and the employees. This system is also show most wanted person details online on the police website.

4. E R diagram

5. Attributes Table Name Attributes Data type CrimeRecord Crime_id Criminal_id Crime_PLACE Crime_type Crime_description Int Not null Int Not null Varchar(50)Not null Varchar(50)Not null Varchar(200)Not null Complaints Complaint_id Complaint_PLACE Complaint_type Complains_description Int Not null Varchar(50)Not null Varchar(100)Not null Varchar(200)Not null FIR FIR_id FIR_PLACE FIR_type FIR_description Int Not null Varchar(50) not null Varchar(200)not null Varchar(200)not null Chargesheet Entity Charge_sheet_id Charge_sheet_fine Charge_sheet_type Charge_sheet_description Int Not null Int Not null Varchar(50)not null Varchar(200)not null Prisoner Entity Prisoner_id Prisoner_Crime_id Prisoner name Prisoner mobile Prisoner email Prisoner username Prisoner password Prisoner address Int Not null Int Not null Varchar(50)not null Int Not null Varchar(50)not null Varchar(50)not null Varchar(10)not null Varchar(100)not null Criminal Entity Criminal id Criminal Crime Criminal name Criminal mobile Criminal email Criminal username Criminal password Criminal address Int Not null Int Not null Varchar(50)not null Long Not null Varchar(50)not null Varchar(50)not null Varchar(10)not null Varchar(200)not null

6. Description

7. Create table The CREATE TABLE statement is used to create a new table in a database. Syntax is as follows: CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, ....)

8. Create table

9. Create table

10. Create table

11. DESCRIBE COMMAND The  DESCRIBE command  enables you to  describe  objects recursively to the depth level set in the SET  DESCRIBE command . ... the type of PL/SQL object (function or procedure) the name of the function or procedure. the type of value returned (for functions) Syntax is as follows: DESCRIBE { table-Name | view-Name }

12. DESCRIBE COMMAND

13. DESCRIBE COMMAND

14. DESCRIBE COMMAND

15. DESCRIBE COMMAND

16. DESCRIBE COMMAND

17. DESCRIBE COMMAND

18. INSERT COMMAND The  INSERT  INTO  statement  is used to add new data to a database.  INSERT  INTO adds a new record to a table.  INSERT  INTO can contain values for some or all of its columns.  INSERT  INTO can be combined with a SELECT to  insert  records. Syntax is as follows: INSERT   INTO   table_name  ( column1 ,  column2 ,  column3 , ...) VALUES  ( value1 ,  value2 ,  value3 , ...);

19. INSERT COMMAND

20. INSERT COMMAND

21. INSERT COMMAND

22. INSERT COMMAND

23. INSERT COMMAND

24. INSERT COMMAND

25. INSERT COMMAND

26. INSERT COMMAND

27. INSERT COMMAND

28. UPDATE COMMAND UPDATE command  is used to  update  any record of data in a table. Following is its general syntax,  UPDATE   table_name SET column_name = new_value WHERE some_condition ; ... In the above statement, if we do not use the WHERE clause, then our  update  query will  update  age for all the columns of the table to 18. Syntax is as follows: UPDATE   table_name SET   column1  =  value1 ,  column2  =  value2 , ... WHERE   condition ;

29. UPDATE COMMAND

30. DELETE COMMAND In the database structured  query  language ( SQL ), the  DELETE statement  removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed. Syntax is as follows: UPDATE   table_name SET   column1  =  value1 ,  column2  =  value2 , ... WHERE   condition ;

31. DELETE COMMAND

32. GREATER THAN OPERATOR(>) Greater than  ( > ) — returns true if the value on the left is  greater than  the value on the right, otherwise it returns false . Less  than or equal  to ( <= ) — returns true if the value on the left is less  than or equal  to the value on the right, otherwise it returns false . Syntax is as follows SELECT * FROM table_name WHERE condition;

33. GREATER THAN OPERATOR(>)

34. JOINS INNER JOIN The  INNER JOIN  selects all rows from both participating tables as long as there is a match between the columns. An SQL  INNER JOIN  is same as  JOIN  clause, combining rows from two or more tables. ... For  example , retrieving all rows where the student identification number is the same for both the students and courses tables Syntax is as follows SELECT   column_name (s) FROM   table1 INNER   JOIN   table2 ON   table1.column_name  =  table2.column_name ;

35. INNER JOIN

36. INNER JOIN

37. INNER JOIN

38. INNER JOIN

39. INNER JOIN

40. INNER JOIN

41. INNER JOIN

42. INNER JOIN

43. INNER JOIN

44. INNER JOIN

45. INNER JOIN

46. INNER JOIN

47. INDEX: An  index  is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. ... These keys are stored in a structure (B-tree) that enables  SQL  Server to find the row or rows associated with the key values quickly and efficiently. Syntax is as follows CREATE   INDEX   index_name ON   table_name  ( column1 ,  column2 , ...);

48. INDEX:

49. LEFT JOIN The  LEFT JOIN  command returns all rows from the  left  table, and the matching rows from the right table. The result is NULL from the right side, if there is no match. Syntax is as follows SELECT   column_name (s) FROM   table1 LEFT   JOIN   table2 ON   table1.column_name  =  table2.column_name ;

50. LEFT JOIN

51. LEFT JOIN

52. LEFT JOIN

53. LEFT JOIN

54. LEFT JOIN

55. LEFT JOIN

56. LEFT JOIN

57. LEFT JOIN

58. LEFT JOIN

59. outer join When performing an inner  join , rows from either table that are unmatched in the other table are not returned. In an  outer join , unmatched rows in one or both tables can be returned. There are a few types of  outer joins : LEFT  JOIN  returns only unmatched rows from the left table. Syntax is as follows SELECT   column_name (s) FROM   table1 FULL   OUTER   JOIN   table2 ON   table1.column_name  =  table2.column_name WHERE   condition ;

60. outer join

61. outer join

62. outer join

63. outer join

64. outer join

65. outer join

66. outer join

67. outer join

68. outer join

69. outer join

70. outer join

71. CURSOR and LOOP   IMPLICIT CURSOR Implicit cursors  are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit  cursor  for the statement. ... Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an  implicit cursor  is associated with this statement. Syntax is as follows DECLARE TOTAL_Complaints INT; BEGIN UPDATE FIR SET FIR_ID=&FIR_ID WHERE FIR_ID=1; TOTAL_Complaints :=SQL%ROWCOUNT; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE('NUMBER OF ROWS UPDATED' || TOTAL_Complaints ); ELSE DBMS_OUTPUT.PUT_LINE('FAIL'); END IF; END; /

72. CURSOR and LOOP   IMPLICIT CURSOR

73. EXPLICIT CURSOR AND LOOP An  explicit cursor  is a named  pointer  to a private SQL area that stores information for processing a specific query or DML statement—typically, one that returns or affects multiple rows. You can use an  explicit cursor  to retrieve the rows of a result set one at a time. Syntax is as follows DECLARE variables; records; create a cursor; BEGIN OPEN cursor; FETCH cursor; process the records; CLOSE cursor; END;

74. EXPLICIT CURSOR AND LOOP

75. Date and time : In  SQL ,  datetime  date data type is used for values that contain both date and time. Microsoft defines it as a date combined with a time of day with fractional seconds that is based on a 24-hour clock. ...  SQL  retrieves and displays  DATETIME  values in 'YYYY-MM-DD hh : mm: ss' format. Syntax is as follows SELECT  *  FROM  Orders  WHERE   OrderDate = '2008-11-11'

76. Date and time :

77. EXCEPTION HANDLING Exception handling  ensures that the flow of the program doesn't break when an  exception  occurs. For  example , if a program has bunch of statements and an  exception  occurs mid way after executing certain statements then the statements after the  exception  will not execute and the program will terminate abruptly. Syntax is as follows DECLARE <declarations section> BEGIN <executable command(s)> EXCEPTION <exception handling goes here > WHEN exception1 THEN exception1-handling-statements

78. EXCEPTION HANDLING

79. View Views can join and simplify multiple tables into a single virtual table. Views can act as aggregated tables, where the database engine aggregates data (sum, average, etc.) and presents the calculated results as part of the data. Views can hide the complexity of data. Syntax is as follows CREATE   VIEW   view_name   AS SELECT   column1 ,  column2 , ... FROM   table_name WHERE   condition ;

80. View

81. Trigger A  trigger  is a special type of stored procedure that automatically runs when an event occurs in the database server. DML  triggers  run when a user tries to modify data through a data manipulation language (DML) event. ...  SQL  Server lets you create multiple  triggers  for any specific statement. Syntax is as follows CREATE [OR REPLACE ] TRIGGER trigger_name .{BEFORE | AFTER | INSTEAD OF }{INSERT [OR] | UPDATE [OR] | DELETE}[OF col_name ]ON table_name .

82. Trigger

83. FUTURE SCOPE: The scope of the project includes that what all future enhancement scan be done in this system to make it more feasible to us: Databases for different products range and storage can be provided. Multilingual support can be provided so that it can be understandable by the person of any language. More graphics can be added to make it more user friendly and understandable. Manage & backup versions of documents online.

84. CONCLUSION: Now-a-days everything is getting computerized. Manual work usually consumes a lot of time and is error prone. To make complaining easy and manage crime records this application is very helpful. The “Crime Management System” is a web based website for online complaining and computerized management of crime records.  Here in this website a person who wishes to file a complaint or report an incident must register before log in and once the admin authenticates the user that he or she can login into the website and file a complaint. This complaint will be received by police and police can send a message regarding status of the complaint to the user who filed.

85. Thank you