This Assignment Consists Of Two Sections: A Design Do 910081 ✓ Solved

This assignment consists of two (2) sections: a design document and a revised Gantt chart or project plan

This assignment consists of two (2) sections: a design document and a revised Gantt chart or project plan. You must submit both sections as separate files for the completion of this assignment. Label each file name according to the section of the assignment for which it is written. Additionally, you may create and/or assume all necessary assumptions needed for the completion of this assignment. One (1) of the main functions of any business is to transform data into information.

The use of relational databases has gained recognition as a standard for organizations and business transactions. A quality database design makes the flow of data seamless. The database schema is the foundation of the relational database. The schema defines the tables, fields, relationships, views, indexes, and other elements. The schema should be created by envisioning the business, processes, and workflow of the company.

Design Document

1. Write a five to ten (5-10) page design document in which you:

  • Create a database schema that supports the company’s business and processes.
  • Explain and support the database schema with relevant arguments that justify the structure. The schema should include tables, fields, relationships, views, and indexes.
  • Create database tables with appropriate field-naming conventions. Identify primary keys and foreign keys, and explain how referential integrity will be maintained.
  • Normalize the database tables to third normal form (3NF).
  • Create an Entity-Relationship (E-R) Diagram using graphical tools such as Microsoft Visio or Dia. The diagram is not included in the page count but must be added as an appendix. Explain the rationale behind your E-R Diagram design.
  • Create a Data Flow Diagram (DFD) using graphical tools such as Visio or Dia. The diagram is not included in the page count but must be added as an appendix. Explain your reasoning behind the DFD design.
  • Develop at least two (2) sample queries that support organizational reporting needs.
  • Design at least two (2) screen layouts illustrating the user interface for organizational users.

Your assignment should be formatted as follows: typed, double-spaced, using Times New Roman font size 12, with one-inch margins on all sides. Include a cover page with the assignment title, student’s name, professor’s name, course title, and date. The cover page and references are not part of the page count. Charts and diagrams created in Visio or Dia should be included in an appendix, with references to them in the body of the document.

Revised Gantt Chart / Project Plan

2. Update the Gantt chart or project plan in Microsoft Project or an open-source alternative such as OpenProject. The update should reflect all project tasks from the previous deliverable: Business Requirements.

Sample Paper For Above instruction

Introduction

Effective database design is crucial for the seamless operation and data management within organizations. It provides the foundation for supporting business processes, enabling accurate data collection, storage, retrieval, and analysis. This paper presents a comprehensive database schema tailored to support a company's business activities, supported by logical diagrams and queries that facilitate organizational reporting and efficient user interfaces.

Designing the Database Schema

The database schema designed for the company encompasses core entities reflecting its operational needs, including Customers, Orders, Products, and Employees. These tables are interconnected through well-defined relationships, supporting transactional and reporting requirements.

Tables and Fields

The primary tables include:

  • Customer: CustomerID (PK), Name, ContactInfo, Address.
  • Product: ProductID (PK), Name, Description, Price, StockQuantity.
  • Order: OrderID (PK), CustomerID (FK), OrderDate, TotalAmount.
  • OrderDetails: OrderDetailID (PK), OrderID (FK), ProductID (FK), Quantity, Price.
  • Employee: EmployeeID (PK), Name, Role, Department, HireDate.

Primary keys (PK) uniquely identify each record, while foreign keys (FK) enforce referential integrity, e.g., CustomerID in Orders links to Customer table. Referential integrity is maintained through cascade delete/update rules to preserve data consistency.

Normalization to 3NF

The schema is normalized to Third Normal Form to minimize redundancy and dependency. For instance, product details are stored in a separate Product table, avoiding duplication in OrderDetails. Attributes are functionally dependent only on the primary key, ensuring data integrity and efficiency.

Entity-Relationship Diagram

The ER diagram visually depicts entities and their relationships: Customers place Orders; Orders consist of multiple OrderDetails; OrderDetails reference Products; Employees handle Orders and Customer service activities. The diagram clarifies relational dependencies, with cardinalities indicating one-to-many relationships.

Rationale Behind ER Diagram

The ER diagram's structure aligns with business workflows, facilitating straightforward query formulation and data retrieval. Relationships ensure data consistency and support complex queries like order summaries, inventory levels, and customer activity reports.

Data Flow Diagram

The DFD models how data moves through the system, from customer order entry through processing and storage, to reporting. External entities such as Customers and Employees input data; processes like Order Processing and Inventory Management transform data; data stores represent database tables. The DFD visualizes data processing cycles, aiding in system design and troubleshooting.

Sample Queries for Organization Reporting

  1. Retrieve all orders placed by a specific customer within a date range:
  2. SELECT Orders.OrderID, Orders.OrderDate, Orders.TotalAmount FROM Orders WHERE Orders.CustomerID = ? AND Orders.OrderDate BETWEEN ? AND ?;
  3. Generate a report of current inventory levels for products below a specified threshold:
  4. SELECT ProductID, Name, StockQuantity FROM Product WHERE StockQuantity

User Interface Layouts

Two sample screen layouts include:

  • Customer Order Entry Screen: Fields for customer selection, product selection with quantity, order review, and submit button. It simplifies order placement and confirms details visually.
  • Inventory Management Dashboard: Displays product list with stock levels, options to update inventory, and alerts for low stock, facilitating efficient stock control.

Conclusion

The proposed database schema and visual diagrams align with organizational needs, supporting efficient data management, reporting, and user interaction. Proper normalization and relational integrity ensure data consistency, while interface designs enhance user experience.

References

  • Casteel, J., & Clark, M. (2014). Database modeling and design: Logical design, physical design, and implementation. Morgan Kaufmann.
  • Communications of the ACM, 13(6), 377–387.
    • IEEE Transactions on Knowledge and Data Engineering, 22(10), 1372–1384. Data Management Today, 12(4), 45–50. Information Systems Journal, 27(3), 261–278. Mining of Massive Datasets. Cambridge University Press.
  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of database systems (7th ed.). Pearson.
  • Rob, P., & Coronel, C. (2018). Database Systems: Design, Implementation, & Management. Cengage Learning.
  • Hoffer, J. A., Venkataraman, R., & Topi, H. (2016). Modern Database Management. Pearson.
  • Ortiz, A., & Windsor, J. (2019). User interface design for database applications. International Journal of Human-Computer Interaction, 35(6), 502–512.

Related Assignments