DBMS Business case study
DownloadTélécharger
Actions
Vote :
ScreenshotAperçu
Informations
Catégorie :Category: nCreator TI-Nspire
Auteur Author: SPITZER2001
Type : Classeur 3.0.1
Page(s) : 1
Taille Size: 4.26 Ko KB
Mis en ligne Uploaded: 20/11/2024 - 18:32:56
Uploadeur Uploader: SPITZER2001 (Profil)
Téléchargements Downloads: 15
Visibilité Visibility: Archive publique
Shortlink : https://tipla.net/a4329963
Type : Classeur 3.0.1
Page(s) : 1
Taille Size: 4.26 Ko KB
Mis en ligne Uploaded: 20/11/2024 - 18:32:56
Uploadeur Uploader: SPITZER2001 (Profil)
Téléchargements Downloads: 15
Visibilité Visibility: Archive publique
Shortlink : https://tipla.net/a4329963
Description
Fichier Nspire généré sur TI-Planet.org.
Compatible OS 3.0 et ultérieurs.
<<
Our project revolves around the development of a comprehensive and scalable database system for "ShopMaster," an e-commerce platform designed to support customers, products, orders, payments, and shipping. The aim was to create a system that is both efficient and capable of meeting the companys current and future needs. The first step was identifying the core business entities: Customers, Products, Orders, Transactions, and Shipping. Each entity was carefully designed to store critical data efficiently: Customers include personal details, multiple addresses, contact information, and purchase history; Products are categorized with attributes like price, stock levels, supplier information, and descriptions; Orders detail order dates, payment statuses, quantities, and delivery information; Transactions track payment history, refunds, and failed payments; and Shipping associates sales activity with delivery progress and personnel. Database Design and Normalization The database was designed using Entity-Relationship (ER) modeling to illustrate the relationships between entities. Key relationships include: 1:1 Relationship: Between customers and their primary payment method. 1 Relationship: Between customers and their orders, products and suppliers, and orders and transactions. Many Relationship: Between orders and products, which was resolved through a junction table linking order IDs and product IDs to capture quantities and prices. To ensure data integrity and efficiency, we applied Normalization up to the Third Normal Form (3NF): First Normal Form (1NF): Eliminated duplicate rows and ensured each column held atomic data. Second Normal Form (2NF): Removed partial dependencies by ensuring that all non-key attributes were fully dependent on the primary key. Third Normal Form (3NF): Eliminated transitive dependencies, ensuring no attribute was dependent on non-primary key columns. This systematic approach minimized data redundancy, reduced the risk of anomalies during data insertions, deletions, or updates, and improved data consistency. Strategic denormalization was applied in areas like product inventory for faster retrieval in high-traffic scenarios, balancing performance needs with database integrity. DBMS Strategies We deployed the database on Azure SQL, a cloud-based platform chosen for its scalability, high availability, and advanced security features. Key DBMS strategies included: Backup and Recovery: Regular automated backups were configured to ensure data recovery in case of failures. Transaction log backups were implemented for point-in-time recovery. Performance Optimization: Indexing strategies, including clustered and non-clustered indexes, were used to enhance query performance. Partitioning was considered for large tables like Orders to improve read/write operations. Role-Based Access Control (RBAC): Access permissions were segmented by roles to enhance security. Admins had full control over the database, salespersons could only access sales and product data, and customers had limited access to their profiles and orders. This ensured data confidentiality and compliance with security protocols. Database Implementation The implementation process involved creating tables with properly defined primary and foreign keys to enforce referential integrity. Constraints like UNIQUE, NOT NULL, and CHECK were used to ensure data quality. Stored procedures were used for common operations like adding orders or processing returns, encapsulating business logic within the database layer. Triggers monitored specific events, such as automatically updating inventory levels when orders were placed. Challenges and Solutions A major challenge was achieving a balance between normalization and performance. Fully normalized databases can lead to slower queries due to the need for multiple joins. We addressed this by selectively denormalizing tables used in frequent queries, such as those related to product availability, ensuring optimal query speed without sacrificing data integrity. Another challenge was ensuring the system's scalability to support future growth. This was managed through modular schema design and Azures scalability features. Management and Security To safeguard the database, robust management practices were implemented: Data Backup: Automated daily backups, with transaction log backups every 15 minutes for critical tables. Data Recovery: A well-documented recovery strategy to restore data in case of system failure. Monitoring: Performance metrics like query execution times and storage usage were actively monitored to preemptively address bottlenecks. Access Control: Multi-tiered RBAC restricted users to only the data they required, protecting sensitive information and reducing the risk of accidental data modifications. Presentation Visuals The presentation includes a detailed ER diagram showcasing the relationships and justifications for design choices. Screenshots of SQL code demonstrate the creation of
[...]
>>
Compatible OS 3.0 et ultérieurs.
<<
Our project revolves around the development of a comprehensive and scalable database system for "ShopMaster," an e-commerce platform designed to support customers, products, orders, payments, and shipping. The aim was to create a system that is both efficient and capable of meeting the companys current and future needs. The first step was identifying the core business entities: Customers, Products, Orders, Transactions, and Shipping. Each entity was carefully designed to store critical data efficiently: Customers include personal details, multiple addresses, contact information, and purchase history; Products are categorized with attributes like price, stock levels, supplier information, and descriptions; Orders detail order dates, payment statuses, quantities, and delivery information; Transactions track payment history, refunds, and failed payments; and Shipping associates sales activity with delivery progress and personnel. Database Design and Normalization The database was designed using Entity-Relationship (ER) modeling to illustrate the relationships between entities. Key relationships include: 1:1 Relationship: Between customers and their primary payment method. 1 Relationship: Between customers and their orders, products and suppliers, and orders and transactions. Many Relationship: Between orders and products, which was resolved through a junction table linking order IDs and product IDs to capture quantities and prices. To ensure data integrity and efficiency, we applied Normalization up to the Third Normal Form (3NF): First Normal Form (1NF): Eliminated duplicate rows and ensured each column held atomic data. Second Normal Form (2NF): Removed partial dependencies by ensuring that all non-key attributes were fully dependent on the primary key. Third Normal Form (3NF): Eliminated transitive dependencies, ensuring no attribute was dependent on non-primary key columns. This systematic approach minimized data redundancy, reduced the risk of anomalies during data insertions, deletions, or updates, and improved data consistency. Strategic denormalization was applied in areas like product inventory for faster retrieval in high-traffic scenarios, balancing performance needs with database integrity. DBMS Strategies We deployed the database on Azure SQL, a cloud-based platform chosen for its scalability, high availability, and advanced security features. Key DBMS strategies included: Backup and Recovery: Regular automated backups were configured to ensure data recovery in case of failures. Transaction log backups were implemented for point-in-time recovery. Performance Optimization: Indexing strategies, including clustered and non-clustered indexes, were used to enhance query performance. Partitioning was considered for large tables like Orders to improve read/write operations. Role-Based Access Control (RBAC): Access permissions were segmented by roles to enhance security. Admins had full control over the database, salespersons could only access sales and product data, and customers had limited access to their profiles and orders. This ensured data confidentiality and compliance with security protocols. Database Implementation The implementation process involved creating tables with properly defined primary and foreign keys to enforce referential integrity. Constraints like UNIQUE, NOT NULL, and CHECK were used to ensure data quality. Stored procedures were used for common operations like adding orders or processing returns, encapsulating business logic within the database layer. Triggers monitored specific events, such as automatically updating inventory levels when orders were placed. Challenges and Solutions A major challenge was achieving a balance between normalization and performance. Fully normalized databases can lead to slower queries due to the need for multiple joins. We addressed this by selectively denormalizing tables used in frequent queries, such as those related to product availability, ensuring optimal query speed without sacrificing data integrity. Another challenge was ensuring the system's scalability to support future growth. This was managed through modular schema design and Azures scalability features. Management and Security To safeguard the database, robust management practices were implemented: Data Backup: Automated daily backups, with transaction log backups every 15 minutes for critical tables. Data Recovery: A well-documented recovery strategy to restore data in case of system failure. Monitoring: Performance metrics like query execution times and storage usage were actively monitored to preemptively address bottlenecks. Access Control: Multi-tiered RBAC restricted users to only the data they required, protecting sensitive information and reducing the risk of accidental data modifications. Presentation Visuals The presentation includes a detailed ER diagram showcasing the relationships and justifications for design choices. Screenshots of SQL code demonstrate the creation of
[...]
>>
