MIS303 Access Assignment 1 50 Pts Fall 2020

1mis303 Access Assignment I 50 Pts Fall 2020in This Assignment S

Create a 3-table database for a textbook publishing company, including the authors, authorship log, and textbooks tables. Use Access 2016 to build the database named "ABCPublish-FirstnameLastname," incorporating your own first and last name in the filename. The database design must follow the provided data model and entity relationship diagram, which define the table structures, fields, data types, and relationships.

Construct the "Authors" table with specified fields such as A_ID (primary key), First_Name, Last_Name, Middle_Name, Email, Phone, Mailing_Address, City, State, Zip, and SSN, ensuring data types, formats, and rules (e.g., required fields, formats) are strictly followed. Populate this table with the provided author data, maintaining data integrity and adherent to the specified formats.

Build the "Textbooks" table with fields like ISBN (primary key), Subject, Title, Edition Number, Pub_Year, Versions, Print_QTY, List_Price, and Unit_Cost, following the data types, formats, and rules outlined. Insert the provided textbook data accurately into this table.

Import the "AuthorshipLog" table from the provided spreadsheet file "Authorship.xlsx," verifying imported data matches the specified fields, data types, and rules, especially for fields such as Log_No (primary key), ISBN, A_ID, Auth_Order, and Compensation_PCT.

Create relationships between all three tables according to the data model, establishing the appropriate referential integrity and relationship symbols as depicted in the ER diagram.

Before submission, ensure the database is properly saved, closed, and uploaded via the Blackboard submission link. No collaboration or external help is permitted, and violations will be penalized academically and administratively.

Paper For Above instruction

The development of a relational database for a textbook publishing company such as ABCPublish requires deliberate attention to data organization, consistency, and integrity. This paper discusses the systematic process of creating a normalized database with three interrelated tables: Authors, Textbooks, and AuthorshipLog. It emphasizes the importance of precise data modeling, appropriate data type selection, referential integrity, and compliance with the specified data formats and rules to ensure the database functions effectively and supports business operations such as author and book management.

The initial step involves designing and creating the "Authors" table from scratch in Microsoft Access 2016. This table captures vital author information, crucial for managing author identities and contact details. The primary key, A_ID, must adhere to a rigid format: a 4-character code starting with a letter followed by three digits, such as A003. The remaining fields—First_Name, Last_Name, Middle_Name, Email, Phone, Mailing_Address, City, State, Zip, and SSN—must be populated with data that conform to their specified formats, e.g., Email requiring standard email formatting, Phone in the format (xxx) xxx-xxxx, and SSN as xxx-xx-xxxx. Enforcing these rules in Access involves setting data validation rules, input masks, and field properties to prevent invalid data entry.

The "Textbooks" table is constructed similarly, with ISBN designated as the primary key, requiring a 13-digit number format. Other fields include Subject, Title, Edition Number, Pub Year, Versions, Print Quantity, List Price, and Unit Cost. Data types are chosen to fit their respective fields, such as Short Text for ISBN and Year, Integer for Edition Number and Print Quantity, and Currency for List Price and Unit Cost. Proper validation—like ensuring Year is a 4-digit value—is implemented through field properties. The data entered into this table must be accurate and adhere strictly to these formats to facilitate seamless data retrieval and reporting.

The "AuthorshipLog" table, imported from an external Excel spreadsheet, records the relationships between authors and textbooks, including their order of authorship and compensation percentage. Critical to this table are the fields Log_No (primary key), ISBN, A_ID, Auth_Order, and Compensation_PCT. Post-import, verification ensures data consistency and conformity to field rules. Importing data must be handled carefully to prevent errors, and any inconsistencies can be rectified directly within Access.

Creating the relationships involves establishing referential integrity between the three tables: Authors and AuthorshipLog via A_ID, and Textbooks and AuthorshipLog via ISBN. These relationships should be visually represented on the relationship diagram, ensuring proper one-to-many linkages, and enforcing referential constraints to maintain data integrity during updates or deletions.

Finally, before submitting, the database must be thoroughly checked for completeness, correct relationships, and adherence to design specifications. Proper naming conventions should be maintained, and the file should be closed before uploading to the Blackboard platform. This structured approach guarantees a reliable data management system aligned with the company's operational needs, supports analytical queries, and upholds academic honesty standards.

References

• Batini, C., & Scannapieco, M. (2006). Data Quality: Concepts, Methodologies, and Techniques. Springer.
• Harrington, J. L. (2016). Relational Database Design and Implementation. Morgan Kaufmann.