PHP-databaskonstruktion

Log | Files | Refs

database.md (2793B)

      1 ## Database Project Features
      2 
      3 ### 1. Implementation
      4 - **Agent Relationships**: Defined relationships between agents, operations, and incidents.
      5 - **IE Diagram**: Visual representation of agent relationships in Appendix A.
      6 - **Database Tables**: Custom-designed tables detailed in Appendix B.
      7 
      8 ### 2. Data Types & Constraints
      9 - **Agent Codenames**: Implemented using `CHAR(3)`.
     10 - **Terrain Types**: Managed with `TINYINT UNSIGNED`.
     11 - **Salaries**: Precision storage using `DECIMAL`.
     12 
     13 ### 3. Denormalization Strategies
     14 - **Merge**: Simplified queries by merging region and incident tables.
     15 - **Codes**: Replaced repetitive text with numeric codes in terrain table.
     16 - **Vertical Split**: Separated reports into active and archived categories.
     17 - **Horizontal Split**: Streamlined the agent table by isolating attributes.
     18 
     19 ### 4. Indexing
     20 - **Purpose**: Improved query speed with indexing.
     21 - **Types**: Primary keys, foreign keys, and composite indexes.
     22 - **Algorithm**: Utilized `BTREE` for versatile data management.
     23 
     24 ### 5. Views
     25 - **Report Simplification**: Unified view of all reports.
     26 - **Field Agent Information**: Aggregated view of field agent details.
     27 - **Agent Performance**: Displayed average success rates.
     28 
     29 ### 6. Stored Procedures
     30 - **Functionality**: Automated data fetching, manipulation, and report archiving.
     31 - **Security**: Implemented procedures immune to SQL injection.
     32 
     33 ### 7. Triggers
     34 - **Logging**: Tracked changes in the agent table.
     35 - **Cascade Delete**: Automated cleanup for deleted incidents.
     36 - **Validation**: Ensured operational limits per region.
     37 
     38 ### 8. Rights Management
     39 - **Roles**: Custom roles for Field Agents, Group Leaders, and Managers.
     40 - **Administrative Control**: Comprehensive rights for database administration.
     41 
     42 ### Additional information
     43 - **Paper**: View the accompanying academic paper here: [databaseimplementation_report_sv.pdf](A22willi_databaskonstruktion_databasimplemenetation.pdf)
     44 
     45 ### References
     46 - [GeeksforGeeks](https://www.geeksforgeeks.org/)
     47 - [MySQL Documentation](https://dev.mysql.com/doc/)
     48 - [High Performance MySQL by Zawodny & Balling](https://www.oreilly.com/library/view/high-performance-mysql/0596003064/ch04.html)
     49 - [Gustavsson's Databassystem](https://his.instructure.com/files/885449/download?download_frd=1&verifier=HwCRIQRFThPTy5ltdhXCUlaTSasvCFa2tFJTd979)
     50 
     51 
     52 ### Appendix
     53  - **A**:
     54  ![IE-diagram](IE-diagram.png)
     55  - **B**:
     56  > Agent(Kodnamn, Förnamn, Efternamn, Lön, Är Fält Agent, ÄrGruppLedare, Är Handläggare)
     57  Operation(KodnamnsTyp, Startdatum, Slutdatum, SR, Region, IncidentNamn, IncidentNr, Gruppledare)
     58  OpererarI (Kodnamn, Kodnamnstyp, Startdatum, Incident Namn, IncidentNr)
     59  Region(Namn, Terräng)
     60  Incident(Namn, Nr, Plats, Region)
     61  Rapport(Datum, Titel, Innehåll, For fattare, IncidentNamn, IncidentNr)