Computer Assignment 2: Company Database Guide Customer Table

Computer Assignment 2 Company Database GuideCustomer Table Data Dic

Develop a comprehensive guide to creating and managing a company database in MS Access 2007, including designing customer, product, and transaction tables, establishing relationships with referential integrity, entering sample data, creating forms for data entry, developing queries with appropriate criteria, and generating reports with grouping and summaries.

Paper For Above instruction

The development of a well-structured company database in Microsoft Access 2007 involves meticulous planning and execution across several stages, including table creation, establishing relationships, data entry, form design, querying, and reporting. This comprehensive approach ensures data integrity, ease of use, and meaningful insights, facilitating efficient management of customer, product, and transaction data within the organization.

Designing Tables and Data Dictionary

The foundational step in constructing the database involves designing three core tables: Customer, Product, and Transaction. Each table is carefully structured with specific fields, data types, and properties to suit the nature of data stored. The Customer table includes CustomerID (AutoNumber primary key), CustomerName (Text, 20 characters), PhoneNo (Text, 13 characters), and City (Text, 15 characters). The Product table comprises ProductID (AutoNumber primary key), ProductName (Text, 15 characters), Price (Currency), and ExpiryDate (Date/Time, Medium Date format). The Transaction table integrates TransactionID (AutoNumber primary key), CustomerID (Lookup Wizard referencing Customer), ProductID (Lookup Wizard referencing Product), Quantity (Number, Integer), and TransactionDate (Date/Time, Medium Date).

Creating Tables in Design View

Creating tables in Design View is crucial to accurately define data properties and enforce data integrity. After initiating a new table in Design View, assign appropriate data types for each field according to the data dictionary guidelines. For example, set CustomerID as an AutoNumber primary key, and specify FieldSize properties for text fields. Save each table with appropriate names and close after setup to avoid editing issues during further design steps.

Establishing Relationships with Referential Integrity

Next, relationships are established between tables to reflect real-world associations. CustomerID in the Transaction table is linked as a lookup wizard to the Customer table, displaying CustomerName, ensuring that only valid customer entries are linked. Similarly, ProductID links to the Product table through lookup wizard, displaying ProductName. Enforcing Referential Integrity during relationship setup guarantees data consistency; it prevents orphan records (transactions without a valid customer or product) and maintains database integrity.

Entering Sample Data Through Data Entry Forms

Once tables and relationships are configured, data entry is facilitated using Forms for user-friendly data input. Forms for each table allow for entering multiple records—five customers, five products, and fifteen transactions—with randomization and chronological order for transactions. The use of lookup fields simplifies data entry by allowing selection from predefined lists, ensuring accuracy and consistency. Sample data should include varied entries reflecting realistic scenarios, such as multiple customers from different cities or products with expiry dates where applicable.

Creating Queries with Criteria

Queries are essential to retrieve specific data subsets. In Design View, queries combine data from the three related tables, selecting relevant fields such as ProductName, CustomerName, Price, Quantity, and TransactionDate. Criteria filter records—for example, purchasing a specific product like 'Siu Mai' or transactions between specific dates. Ensuring correct syntax, such as using '#' symbols for date criteria or quotation marks for text fields, is critical. Additional queries might filter by transaction amount or date ranges, providing targeted insights into sales performance.

Building Summarized Reports

The final stage involves creating reports for print-ready summaries grouped by fields such as CustomerName or ProductName. Using the Report Wizard, data is grouped, sorted, and subtotaled—for instance, calculating total quantities per customer. Custom titles and formatting enhance readability. These reports provide valuable summaries for management, highlighting sales trends, customer activity, and product performance, aiding strategic decision-making.

Conclusion

Constructing a robust company database in MS Access 2007 requires attention to detail at each stage—from designing normalized tables, setting appropriate data types and properties, to establishing relationships with referential integrity. Incorporating forms streamlines data entry, while queries enable precise data retrieval based on specified criteria. Ultimately, well-designed reports offer clear, organized insights that support managerial decisions. Mastery of these components ensures an efficient, reliable, and scalable database system that supports ongoing organizational needs.

References

• Hochstein, J. (2011). Access 2010 VBA Programming Inside Out. Pearson Education.
• Rob, P., & Coronel, C. (2007). Database Systems: Design, Implementation, & Management. Cengage Learning.
• Simon, R. (2006). Microsoft Access 2007 Step by Step. Microsoft Press.
• Gaskin, J. (2010). Mastering Microsoft Access 2010. Pearson Education.
• Antoniou, G., & Manolopoulos, Y. (2010). Fundamentals of Data Management. Springer.
• Murach, J. (2015). Murach's Access 2016. MikeMurach.com.
• O'Neil, P. (2010). Access 2010 VBA Programming. Cengage Learning.
• Deitel, H., & Deitel, P. (2012). Microsoft VBA Programming. Pearson.
• Collett, P., & Collier, H. (2009). Microsoft Access 2007: The Complete Reference. McGraw-Hill Education.
• Sullins, K. (2008). Intro to Microsoft Access. Barron's Educational Series.