Project Deliverable 3: Database And Programming Design

Project Deliverable 3 Database And Programming Design

This assignment consists of two sections: a design document and a revised Gantt chart or project plan. You must submit both sections as separate files, clearly labeled for each part. You may make necessary assumptions to complete the assignment. The main goal is to design a robust database schema that supports the company's business processes and workflow, enabling seamless data flow.

Relational databases are essential for handling business transactions efficiently, and a well-designed database schema forms the foundation of this process. The schema should define tables, fields, relationships, views, indexes, and other relevant elements, all tailored to reflect the organization’s operations.

Section 1: Design Document

Develop a comprehensive 5-10 page design document that addresses the following:

  • Create a database schema supporting the company's business models and workflows.
  • Provide a rationale for your schema design, justifying choices related to structure, relationships, views, and indexes.
  • Design database tables with appropriate naming conventions; identify primary keys and foreign keys; explain how referential integrity will be maintained.
  • Normalize all database tables to third normal form (3NF) to ensure data consistency and reduce redundancy.
  • Produce an Entity-Relationship (E-R) Diagram using graphical tools such as Microsoft Visio or Dia, which will be included in the appendix.
  • Explain your rationale behind the E-R Diagram design choices, particularly how entities relate and support business needs.
  • Create a Data Flow Diagram (DFD) using similar graphical tools, illustrating how data moves within the system, with the diagram included in the appendix.
  • Provide an explanation of your design rationale for the DFD, emphasizing its role in representing data processes and flow.
  • Develop at least two sample queries designed to meet organizational reporting requirements.
  • Create at least two screen layouts demonstrating the user interface that organizational staff will utilize to interact with the system.

The document should follow formatting guidelines: double-spaced, Times New Roman font size 12, with one-inch margins, including a cover page and a references page formatted in APA style. Charts or diagrams created in MS Visio or Dia should be included as an appendix, with all references to diagrams clearly indicated within the main text.

Section 2: Revised Gantt Chart / Project Plan

Update the Gantt chart or project plan created in Deliverable 2 using project management software such as Microsoft Project or an open-source alternative like OpenProject. The updated plan should include all tasks necessary to complete the project, reflecting any changes or additional steps identified during the design process.

Paper For Above instruction

Developing an effective database and visualizing its design through diagrams are pivotal steps in aligning technical architecture with organizational needs. The core objective is to produce a relational database schema that encapsulates the company's business processes efficiently while ensuring data integrity, normalization, and usability for reporting and user interaction.

Database Schema Design

The foundational step in this project was to understand and analyze the business processes to inform the database schema. The schema comprises several interconnected tables representing core business entities such as Customers, Orders, Products, Suppliers, and Employees. Each table is designed with clear naming conventions to facilitate clarity and maintainability.

For example, the Customer table includes fields such as CustomerID, Name, Address, Email, and PhoneNumber. CustomerID serves as the primary key, uniquely identifying each customer. Similarly, the Orders table contains OrderID, CustomerID, OrderDate, and TotalAmount. OrderID is the primary key, while CustomerID acts as a foreign key referencing the Customer table, ensuring referential integrity.

This structure supports the business process where customers place orders for products supplied by various suppliers. The relationships are established through foreign keys, enabling data consistency across the database. The schema's design supports complex queries such as retrieving all orders for a particular customer or identifying products supplied by a specific vendor.

Normalization and Integrity

Normalization to third normal form (3NF) was applied to eliminate redundancy and dependency anomalies. Each table contains atomic data, and foreign keys link tables logically, supporting data consistency and integrity. Referential integrity is maintained through foreign key constraints, which prevent orphan records and ensure consistent relationships.

Entity-Relationship Diagram

The E-R diagram visually represents entities, attributes, and relationships. Entities such as Customer, Order, Product, and Supplier are depicted with their respective attributes and relational links. For instance, the Customer-Order relationship illustrates that a customer can place multiple orders, but each order is associated with one customer.

The diagram's design rationale emphasizes clarity, ease of understanding, and alignment with business workflows. It facilitates communication among stakeholders and guides the physical database implementation.

Data Flow Diagram

The DFD illustrates how data moves through the system, from data entry at user interfaces to processing and storage. It delineates processes like order entry, inventory management, and reporting. The diagram supports understanding system interactions and helps identify potential bottlenecks or process improvements.

Sample Queries

  1. Retrieve all orders placed by a specific customer:
  2. SELECT OrderID, OrderDate, TotalAmount

    FROM Orders

    WHERE CustomerID = ?;

  3. List all products supplied by a particular vendor:
  4. SELECT ProductName, Price

    FROM Products

    WHERE SupplierID = ?;

User Interface Layouts

The first mockup presents a customer order entry screen, featuring fields for customer information, product selection, and order details. The second layout illustrates an organizational report dashboard, including filters and data visualization components to support managerial decisions.

Conclusion

This comprehensive design promotes efficient data management aligning with organizational workflows. By integrating normalized tables, clear relationships, and visual diagrams, the system enhances data integrity, usability, and reporting capabilities essential for organizational success.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
  • Hoffer, J. A., Venkataraman, R., & Topi, H. (2016). Modern Database Management (12th ed.). Pearson.
  • Radin, R. (2014). Principles of Data Management. Information Systems Education Journal, 12(4), 45-54.
  • Date, C. J. (2012). An Introduction to Database Systems (8th ed.). Pearson.
  • Connolly, T., & Begg, C. (2015). Database Systems: A Practical Approach to Design, Implementation, and Management (6th ed.). Pearson.
  • Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management (11th ed.). Cengage Learning.
  • Panko, R. R. (2014). Business Data Communications and Networking. Pearson.
  • Zapanta, E. (2017). Data Flow Diagrams: An Essential Guide. Information Systems Journal, 23(3), 150-169.
  • Microsoft Docs. (2022). Create Entity Relationship Diagrams in Visio. https://docs.microsoft.com/en-us/visio
  • Dia Diagram Editor. (n.d.). Open Source Diagramming Tool. https://wiki.gnome.org/Apps/Dia

Related Assignments