_2025-05-13 _{#data-visualization #data-management #sql #hmp669} ^{[[maps-of-content|🌐 Maps of Content — All Notes]]} ^{Series: [[hmp669|Data Management and Visualization]]} ## Introduction to Microsoft Access for Database Implementation > [!abstract]- Quick Review > > Microsoft Access is a user-friendly relational database management system (RDBMS) that allows translation of business rules into interconnected tables with defined relationships and data integrity rules. > > **Key Concepts**: > > - Tables (entities) with fields (attributes) connected through relationships > - Primary keys uniquely identify records; foreign keys create relationships > - Referential integrity ensures data consistency between related tables > > **Must Remember**: > > - Every table needs a primary key (AutoNumber is convenient) > - One-to-many relationships require foreign keys in the "many" table > - Lookup Wizards display user-friendly values but store numeric IDs internally > > **Critical Relationships**: > > - Tables are connected via primary key/foreign key relationships > - Business rules translate to entity relationships in database design > - User interface elements (forms, lookup wizards) improve data entry but don't change underlying structure > [!code]- Code Reference > > > |Command/Syntax|Purpose|Example| > |---|---|---| > |**Microsoft Access Interface Elements**||| > |Design View|Create and modify table structure|Right-click table > Design View| > |Datasheet View|Enter and view data in spreadsheet format|Double-click table name| > |Relationships Tool|Create and manage table relationships|Database Tools tab > Relationships| > |Form Wizard|Create data entry forms|Create tab > Forms > Form Wizard| > |**Data Types**||| > |Short Text|Store text up to 255 characters|Names, descriptions, codes| > |Number|Store numeric values|Quantities, measurements, IDs| > |AutoNumber|Generate unique sequential values|Primary keys (ID fields)| > |Date/Time|Store dates and times|Birth dates, appointment times| > |Yes/No|Store Boolean values|True/false, yes/no options| > |Hyperlink|Store web addresses|Website URLs, email addresses| > |**Field Properties**||| > |Input Mask|Format data entry|Phone: (\_\_\_) \_\_\_**-**\_\_\_\_| > |Required|Make field mandatory|Yes/No setting| > |Default Value|Set automatic initial value|Today() for current date| ## Fundamentals of Microsoft Access ### What is Microsoft Access? Microsoft Access is a relational database management system (RDBMS) that stands out through its: - Graphical user interface - Forms and drag-and-drop creation options - Visual relationship management tools - Integrated UI elements for data entry > [!note] Technical Context > While Microsoft Access is more accessible than alternatives like Microsoft SQL Server, it offers less power for advanced database management. Access databases created in modern versions (2007 and later) use the `.accdb` file extension. ### Translating Business Rules to Database Structure The database design process begins with business rules that define how entities relate. **Example Business Rule**: "A faculty member can advise many students, and one student can be advised by only one faculty member." From this rule: - **Nouns** become entities (tables): Faculty, Student - **Verbs** indicate relationships: "advise" shows how Faculty relates to Student - **Quantifiers** define relationship types: "many students" and "only one faculty" indicate a one-to-many relationship > [!tip] Design Approach > Always start by analyzing the business rules before creating database objects. The structure of your database should reflect the real-world relationships between entities. ![[ms-access-for-database-implementation-1747272949189.webp]] ## Creating Database Objects in Microsoft Access ### Tables and Views Access provides two main views for working with tables: - **Datasheet View**: Spreadsheet-like interface for data entry - **Design View**: Where table structure is defined (fields and data types) ### Defining Fields and Data Types In Design View, you define the structure of each entity by specifying: 1. **Field Names**: The attributes of your entity (e.g., student_ID, first_name, last_name) 2. **Data Types**: The kind of data each field will store Common data types in Access include: - **Short Text**: Names, codes, short descriptions (up to 255 characters) - **Long Text**: Notes, lengthy descriptions - **Number**: Numeric values (various formats) - **Currency**: Monetary values - **Date/Time**: Dates and times - **Yes/No**: Boolean values (true/false) - **Hyperlink**: Web addresses, email addresses - **Attachment**: Files embedded in the database > [!tip] Field Properties > Access offers additional properties like "Input Mask" to format data entry for phone numbers, dates, or social security numbers, ensuring consistent data format. ### Primary Keys and Identity Integrity The **identity integrity rule** requires that every table must have a primary key—a field or combination of fields that uniquely identifies each record. - Primary keys **must be unique** (no duplicates) - Primary keys **cannot be null** (must have a value) - **AutoNumber** data type automatically generates unique values for primary keys > [!note] AutoNumber Advantage > Using AutoNumber "takes away the responsibility from you as the designer who arbitrarily specify unique numbers" and ensures each record has a guaranteed unique identifier. ## Establishing Relationships Between Tables ### Understanding Relationship Types The example demonstrates a **one-to-many relationship**: - One faculty member can advise many students - Each student has only one faculty advisor ### Implementing Relationships with Foreign Keys To establish this relationship: 1. The "many" side (Student table) contains a **foreign key** 2. The foreign key references the primary key of the "one" side (Faculty table) 3. In our example, "advisor" in the Student table is the foreign key referencing "faculty_ID" ![[ms-access-for-database-implementation-1747272949189.webp]] ### Enforcing Referential Integrity **Referential integrity** ensures that relationships between tables remain consistent by: - Preventing orphaned records (records in the "many" table that refer to non-existent records in the "one" table) - Requiring that any value in a foreign key field must exist in the referenced primary key > [!warning] Important Rule > Always enable the "Enforce Referential Integrity" option when creating relationships. Without it, you could create students with advisor IDs that don't exist in the faculty table, corrupting your data. ### Using the Relationships Tool Access provides a visual tool for managing relationships: 1. Navigate to "Database Tools" tab 2. Click "Relationships" 3. Add tables to the relationship diagram 4. Drag the primary key field from the "one" table to the foreign key field in the "many" table 5. In the dialog box, check "Enforce Referential Integrity" ## Enhancing User Experience ### Forms for Data Entry Forms provide a more structured interface for data entry compared to the spreadsheet-like Datasheet View. The Form Wizard helps create forms based on: 1. Fields selected from one or more tables 2. Layout options for the form 3. Style preferences for visual appearance ### Lookup Wizard for Foreign Key Data Entry The Lookup Wizard improves user experience by: 1. Displaying meaningful data (e.g., faculty names) instead of numeric IDs 2. Creating drop-down menus for selecting related records 3. Making data entry more intuitive > [!important] Internal vs. External Representation > While users see meaningful text (like faculty names) in the interface, "internally in the student table, it's still saved as a number." The database stores the ID value (foreign key) but displays the corresponding descriptive field. ## Case Application: Faculty-Student Advising Database > [!example]- Building a Faculty-Student Database > > **Business Scenario**: A university department needs to track faculty advisors and their assigned students. > > **Step 1**: Create the Faculty table > > - Define fields: faculty_ID (AutoNumber, Primary Key), first_name, last_name, department > - Enter sample faculty data > > **Step 2**: Create the Student table > > - Define fields: student_ID (AutoNumber, Primary Key), first_name, last_name, date_of_birth, phone, LinkedInPage, advisor (Number) > - Note that "advisor" will be the foreign key > > **Step 3**: Establish the relationship > > - Open Relationships tool > - Create a one-to-many relationship from Faculty to Student > - Connect faculty_ID to advisor > - Enable "Enforce Referential Integrity" > > **Step 4**: Enhance with Lookup Wizard > > - In Student table Design View, change the advisor field to use Lookup Wizard > - Select Faculty table as the lookup source > - Choose faculty_ID as the stored value > - Select first_name and last_name as display fields > > **Result**: When entering student data, users see faculty names in a dropdown menu, but the system stores the faculty_ID internally, maintaining proper relationships. ## Summary and Key Takeaways Microsoft Access provides an accessible platform for implementing relational databases by: 1. Translating business rules into database structures 2. Creating tables with appropriate fields and data types 3. Establishing relationships between tables using primary and foreign keys 4. Enforcing data integrity through constraints 5. Enhancing usability with forms and lookup wizards The design process flows from understanding real-world relationships to implementing them in the database structure, always ensuring that data integrity is maintained. ### The Single Most Important Takeaway Database design is properly identifying entities and their relationships, then implementing them with appropriate keys and integrity constraints to ensure data consistency while providing user-friendly interfaces for data entry. -- Reference: