commit a3a5f0a6822ac9531281a3530e60e70374d1669f
parent f5b25bbc31b6a6e0d06a43a5ada621124d7ea97f
Author: William Lindholm <a22willi@student.his.se>
Date: Wed, 13 Dec 2023 10:25:29 +0100
Added readme
Diffstat:
13 files changed, 125 insertions(+), 0 deletions(-)
diff --git a/readme.md b/readme.md
@@ -0,0 +1,62 @@
+# Database Project Overview
+
+This repository features a database project developed for an academic assignment. It showcases a web interface designed to facilitate various database operations.
+
+## Project Features
+
+### Login System
+- **Session Management**: Checks session variables to verify user authentication and redirect appropriately.
+- **Prepared Statements**: Secure handling of user credentials, leveraging MySQL users for password security.
+
+
+
+### Data Entry Forms
+- **ModalBuilder**: Streamlines the creation of data entry forms.
+- **Foreign Key Handling**: Generates dropdowns for selecting values from foreign tables with composite primary keys.
+- **Input Types**: Supports various data types, including text and datetime, with validation for required fields.
+
+
+
+### Data Insertion Logic
+- **Safe Data Handling**: Employs prepared statements for SQL injection prevention.
+- **Error Management**: Uses exceptions to provide feedback on data insertion issues.
+
+### Listboxes for Foreign Key Selection
+- **Automated Listbox Generation**: Facilitates the selection of valid foreign key values from the database.
+
+### Search Functionality
+- **SQL Query Construction**: Builds search queries for efficient database entry retrieval.
+
+### Content Modification Forms
+- **Update Capability**: Allows for the modification of existing entries.
+- **User Interaction Design**: Intuitively uses hidden fields to retain the context of user selections.
+
+### Table Rendering
+- **Interactive Tables**: Creates responsive HTML tables with `TableFactory` and `TableBuilder`.
+- **Customization Options**: Adds custom buttons via callback functions for each row.
+
+
+
+### Dynamic Table Headers
+- **Automated Header Creation**: Dynamically generates table headers based on the database schema.
+
+### Stored Procedure Execution
+- **Procedure Handling**: Facilitates the execution of backend procedures through the frontend.
+
+### Dynamic Data Manipulation Links
+- **Context-Sensitive Deletion**: Enables record deletion from the UI with context-aware URLs.
+
+### Hidden Field Utilization in Forms
+- **Streamlined Data Entry**: Implements hidden fields for a simplified user experience.
+
+### Database Connection Management
+- **Singleton Pattern**: Ensures a single instance of the database connection is used application-wide.
+- **Explicit Connectivity Feedback**: Clearly communicates database connection status.
+
+_Detail_: [Database Connection Class](db/dbconnection.php "View the database connection class")
+
+## Additional Information
+
+- **Database Design**: Explore the database design and implementation [Database Design](src/database.md).
+- **ASP.NET MVC Rewrite**: A similar project version using ASP.NET MVC is available at [MVC-database-construction](https://github.com/LindholmLabs/MVC-databaskonstruktion).
+- **Paper**: The accompanying paper can be read here: [PHP_Application_Report_sv.pdf](src/PHP_Applikation_rapport.pdf).
+\ No newline at end of file
diff --git a/src/A22willi_databaskonstruktion_databasimplemenetation.pdf b/src/A22willi_databaskonstruktion_databasimplemenetation.pdf
Binary files differ.
diff --git a/src/IE-diagram.png b/src/IE-diagram.png
Binary files differ.
diff --git a/src/PHP_Applikation_rapport.pdf b/src/PHP_Applikation_rapport.pdf
Binary files differ.
diff --git a/src/agents_page.png b/src/agents_page.png
Binary files differ.
diff --git a/src/database.md b/src/database.md
@@ -0,0 +1,61 @@
+## Database Project Features
+
+### 1. Implementation
+- **Agent Relationships**: Defined relationships between agents, operations, and incidents.
+- **IE Diagram**: Visual representation of agent relationships in Appendix A.
+- **Database Tables**: Custom-designed tables detailed in Appendix B.
+
+### 2. Data Types & Constraints
+- **Agent Codenames**: Implemented using `CHAR(3)`.
+- **Terrain Types**: Managed with `TINYINT UNSIGNED`.
+- **Salaries**: Precision storage using `DECIMAL`.
+
+### 3. Denormalization Strategies
+- **Merge**: Simplified queries by merging region and incident tables.
+- **Codes**: Replaced repetitive text with numeric codes in terrain table.
+- **Vertical Split**: Separated reports into active and archived categories.
+- **Horizontal Split**: Streamlined the agent table by isolating attributes.
+
+### 4. Indexing
+- **Purpose**: Improved query speed with indexing.
+- **Types**: Primary keys, foreign keys, and composite indexes.
+- **Algorithm**: Utilized `BTREE` for versatile data management.
+
+### 5. Views
+- **Report Simplification**: Unified view of all reports.
+- **Field Agent Information**: Aggregated view of field agent details.
+- **Agent Performance**: Displayed average success rates.
+
+### 6. Stored Procedures
+- **Functionality**: Automated data fetching, manipulation, and report archiving.
+- **Security**: Implemented procedures immune to SQL injection.
+
+### 7. Triggers
+- **Logging**: Tracked changes in the agent table.
+- **Cascade Delete**: Automated cleanup for deleted incidents.
+- **Validation**: Ensured operational limits per region.
+
+### 8. Rights Management
+- **Roles**: Custom roles for Field Agents, Group Leaders, and Managers.
+- **Administrative Control**: Comprehensive rights for database administration.
+
+### Additional information
+- **Paper**: View the accompanying academic paper here: [databaseimplementation_report_sv.pdf](A22willi_databaskonstruktion_databasimplemenetation.pdf)
+
+### References
+- [GeeksforGeeks](https://www.geeksforgeeks.org/)
+- [MySQL Documentation](https://dev.mysql.com/doc/)
+- [High Performance MySQL by Zawodny & Balling](https://www.oreilly.com/library/view/high-performance-mysql/0596003064/ch04.html)
+- [Gustavsson's Databassystem](https://his.instructure.com/files/885449/download?download_frd=1&verifier=HwCRIQRFThPTy5ltdhXCUlaTSasvCFa2tFJTd979)
+
+
+### Appendix
+ - **A**:
+ 
+ - **B**:
+ > Agent(Kodnamn, Förnamn, Efternamn, Lön, Är Fält Agent, ÄrGruppLedare, Är Handläggare)
+ Operation(KodnamnsTyp, Startdatum, Slutdatum, SR, Region, IncidentNamn, IncidentNr, Gruppledare)
+ OpererarI (Kodnamn, Kodnamnstyp, Startdatum, Incident Namn, IncidentNr)
+ Region(Namn, Terräng)
+ Incident(Namn, Nr, Plats, Region)
+ Rapport(Datum, Titel, Innehåll, For fattare, IncidentNamn, IncidentNr)
+\ No newline at end of file
diff --git a/src/incidents_page.png b/src/incidents_page.png
Binary files differ.
diff --git a/src/login_page.png b/src/login_page.png
Binary files differ.
diff --git a/src/modalbuilder.png b/src/modalbuilder.png
Binary files differ.
diff --git a/src/operations_page.png b/src/operations_page.png
Binary files differ.
diff --git a/src/report_page.png b/src/report_page.png
Binary files differ.
diff --git a/src/table.png b/src/table.png
Binary files differ.
diff --git a/src/terrain_page.png b/src/terrain_page.png
Binary files differ.
