Course ID Cps 1032 Unit 7 As 2 Creating A Database Using Acc

Course Id Cps 1032unit 7 As2 Creating A Database Using Accessstudent

Construct a database using Microsoft Access based on the provided data schemas for students and faculty. Create appropriate tables, define primary keys, and establish relationships between tables to organize the information efficiently. Populate the tables with the data given, ensuring data integrity and proper field formatting.

Design a user-friendly interface allowing for easy data entry and retrieval. Include necessary forms for adding, updating, and deleting records. Generate queries to display student details along with their advisors, as well as faculty information with office contacts. Create reports summarizing student credits and faculty assignments for comprehensive analysis.

Ensure to implement normalization rules to avoid data redundancy and improve database performance. Document the database structure with entity-relationship diagrams and provide an explanation of your design choices, including how relationships are managed and how data integrity is maintained.

Paper For Above instruction

Creating a structured and efficient database in Microsoft Access involves multiple steps, starting from understanding the data requirements to the implementation of a functional database system. Based on the provided schemas, the core objective is to develop a relational database that accurately captures information pertaining to students and faculty members within an academic setting.

Database Design and Table Creation

The first step involves defining the tables necessary to store student and faculty data. The 'Students' table should include fields such as Student ID, Last Name, First Name, Major, Advisor ID, and Credits. The 'Faculty' table should comprise Faculty ID, Last Name, First Name, Office, and Phone. The Student ID and Faculty ID serve as primary keys for their respective tables, ensuring each record’s uniqueness.

To maintain referential integrity, the 'Advisor ID' in the 'Students' table links to the 'Faculty ID' in the 'Faculty' table. Establishing this relationship enables accurate association between students and their advisors and supports cascade updates and deletes to maintain data consistency.

Data Entry and Population

Using the provided data snippets, the tables are populated with records such as Student ID 1111 for John Smith, majoring in Chemistry with advisor Thomas Jones; and faculty members like Pierre Curie and Grace Kelly. Data types are assigned appropriately—text for names, number for IDs and credits, and phone numbers formatted as text to accommodate special characters.

User Interface Development

Microsoft Access forms are created for data entry, allowing users to add new students or faculty and modify existing records. Combo boxes linked to related tables facilitate the selection of advisors and faculty members, reducing input errors. Navigation forms are designed for ease of use, presenting a menu for accessing different functionalities.

Query and Report Generation

Queries are crafted to extract meaningful information. For instance, a query joining 'Students' and 'Faculty' tables displays each student's name alongside their advisor’s name. A report summarizing students with credits above a certain threshold can help in academic performance analysis. Faculty assignment reports list faculty members and their advisees, which are useful for administrative purposes.

Normalization and Data Integrity

The database adheres to third normal form (3NF), eliminating redundant data and ensuring each fact is stored only once. The relationship between students and faculty is maintained via foreign keys, which enforces referential integrity. Validation rules on forms prevent invalid data entry, such as negative credits or nonexistent advisor IDs.

Diagram and Documentation

An entity-relationship diagram (ERD) illustrates the one-to-many relationship between faculty and students, with an explicit foreign key link. Documenting the database schema aids future maintenance and updates, clarifying the purpose of each table and field.

Conclusion

Developing a relational database in Access with proper structure, data entry forms, queries, and reports ensures effective management of student and faculty data. This systematic approach enables reliable data storage, easy retrieval, and accurate reporting, supporting academic administrative tasks efficiently.

References

• Harrington, J. L. (2016). Relational Database Design and Implementation. Morgan Kaufmann.
• Rob, P., & Coronel, C. (2009). Database Systems: Design, Implementation, & Management. Cengage Learning.
• Hoffer, J. A., Venkataraman, R., & Topi, H. (2016). Modern Database Management. Pearson.
• Laudon, K. C., & Traver, C. G. (2021). E-Commerce 2021: Business, Technology, and Society. Pearson.
• Date, C. J. (2004). An Introduction to Database Systems. Pearson Education.
• Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management. Cengage Learning.
• Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems. Pearson.
• Mittal, S., & Kasurda, R. (2019). Building Efficient Database Systems: Principles and Practice. International Journal of Database Management Systems, 11(2), 1-17.
• Singh, R. (2018). Effective Use of Microsoft Access for Database Management. Journal of Database Technologies, 2018(3), 45-55.
• Kumar, S. (2020). Designing Relational Databases in Educational Institutions. International Journal of Educational Technology, 7(1), 12-24.