Database Design and Microsoft Access

• Database: A process for efficient data structuring and organization to streamline access and analysis.
• Access: A user-friendly platform designed for creating data systems, accessible without extensive programming knowledge.

Entity-Relationship (E-R) Diagram

• Graphical representation of entities within a system, illustrating their interconnections.
• Serves as a guide during implementation in Access.
• Entities: Real-world objects connected by relationships.
• Relationships: How real world objects connect with each other.
• Database design: Structured information organization to optimize use, ensure data integrity, prevent redundancy, and improve system performance.
• Databases should mirror real-world systems and interactions between entities.
• Microsoft Access helps create information management systems, offering intuitive tools for building databases, extracting, and analyzing information via queries and reports.
• Database design integrates logic, structure, and contextual understanding.
• Initial steps for creating a database involve analyzing the situation, identifying required information, and generating an E-R diagram.
• The Entity Relationship (E-R) model captures reality, reflecting properties in a database where the structure comprises system relationships and attribute descriptions.

E-R Diagram Creation Steps

• Identify the entities in the situation.
• Identify the relationships between those entities.
• Identify the attributes of those entities and the relationship.
• Identify the key attribute of each entity or relationship.
• Entity: Any person, event, or concept about which the information is recorded, represented by a rectangle in an E-R model.
• Relationship: An association between entities, depicted by a rhombus containing the relationship name.

Elements taken into account to define a relationship

• Name of the Relationship: The identifier of the relationship.
• Cardinality of the Relationship
  • Indicates how many times an entity can relate to another.
  • One-to-one: Unique personal data associated with a single student.
  • One-to-many: Many employees in a specific company, but each employee works for only one company.
  • Many-to-many: A customer can buy different items, and an item can be bought by different customers.
• Attribute: Expresses properties, qualities, or identifiers of an entity or relationship, enhancing understanding and distinguishing from others.
• Entities attribute: Represented graphically by labeled ellipses or circles, linked to the entity or relationship with a straight line.
• Key attribute: Ensures unique identification of each entity instance.
• The identification of entities, attributes, and relationships is key to structure databases correctly.
• Microsoft Access: This is a database management system that allows users to create, organize, and manipulate data efficiently.
• Databases: Tables consisting of rows and columns, much like spreadsheets.

Home Screen

• Facilitates quick access to manage and create databases.
• Options: creating a blank database, use a predefined template, or open recent databases.
• The home screen serves as a starting point for accessing Access' primary features.
• The main screen has an organized interface for database creation and management, featuring the File Menu, Quick Access toolbar, Navigation Pane and the work area.
• Quick access toolbar: Customizable with frequently used commands.
• File Menu: Contains backstage view options for file manipulation, including New, Open, Save, Print, and Account settings.
• Options Ribbon: File, Home, Create, External Data, and Database Tools, each containing groups and command buttons.
• Navigation Pane: Displays database objects like tables, queries, forms, reports, macros, or modules.
• Document Area: This is area where the database object is displayed that you are working with, showing the active document's name.

Basic Concepts

• Database: A file that integrates a collection of tables, reports, queries, and forms.
• Table: A structured arrangement of rows and columns for organizing data, similar to Excel.
• Record: Rows in a table where related data is grouped for each entity; each row contains specific information divided into fields.
• Field: Columns in the table, each with a name and assigned data type (text, numbers, dates).

Types of View

• Datasheet view: This allows direct viewing, editing, and addition of data to a table or query in a row-and-column format.
• Design View: Modify database structure by defining fields, data types, and relationships which is crucial for correct database construction.
  • You can add, delete, or modify the names of the fields that will contain the information in the table.
  • Assign types of data: Type of data that will be kept for each field
  • Set a primary key: Choose a field to uniquely identify each record.
  • Configure field properties: Access additional details such as whether it is a required field.
• Form view: Presents data in a more user-friendly way, simplifying data entry by focusing on one record at a time.
• Report view: Displays organized data for analysis or printing.
• Preview: Used to see how the report will look before printing reviewing the layout and details before generating a printed version of the document

Types of objects

• Table: Stores database data, organized in rows and columns.
• Form: Created from the database to display table records one at a time, enhanced with customizable controls and designs for easier data interaction.
• Report: Presents database data in a visually appealing organization, created from one or more tables for summaries, analyses, and clear information sharing.
• Query: Allows for searching, filtering, and analyzing data across tables, enabling specific selections, criteria application, and calculations for efficient information retrieval.

Types of data

• Text: Manages alphanumeric information, utilizing a Field Size property to set character limits.
• Number: Used for arithmetic operations, controlled by the Field Size property with Integer, Long Integer, and Double options.
• Date and time: Displays date and time with various formats like General Date, Long Date, and Short Time, managed by the Format property.
• Currency: Numeric data for financial functions, using the Format property for options like General Number, Currency, and Euro.
• Hyperlink: Stores links to web addresses, files, or emails, facilitating integration of external resources without common field property options.
• Attached Data: Enables direct storage of various file types in a database.
• In Datasheet view: Double-click on the cell where the attachment is captured, a dialog box will appear where you will select the file you want to add and then click on the Add button, in the next dialog box, locate the location and the file, click on the Open button.
• You can view it, double-click on the cell.

Keys in Databases

• Primary Key: This is a field (or set of fields) that uniquely identifies each record in a table ensuring no duplicates are present.
  • Must contain unique values, must not contain null values, and identify a single record uniquely.
• Compound Key: Combination of two or more fields guaranteeing uniqueness where a single field is insufficient.
  • The combination of two or more fields ensures guarantee the uniqueness of each record in a table.
• Foreign Key: Relates to the primary key of another table, allowing the tables and structure information.
  • Establishes relationships between tables, does not need to be unique in the defined table and points to the primary key of another table.
• Templates: Examples of ready-to-use databases that include predesigned tables, forms, reports, and queries, easily modified to fit specific needs.

Stage 2: Table Structure of a Database

• Table design: Based on transforming an Entity-Relationship (E-R) Diagram into a database structure.
• The E-R Diagram graphically represents entities, their attributes, and relationships.
• Entities from the diagram become tables, with entity attributes becoming fields.
• Relationships, like one-to-one or many-to-many, dictate how tables connect via keys.

Creating a new database

• Start by creating a new database, by clicking the database from the blan desktop button
• The next step is to define the name of your file. Access automatically gives the name Database1.accdb, it will also define the place where the file will be saved, or you can change it to the place of your choice.
• Entering data in a table: This can be done from the Datasheet view, by first setting the names of the fields and then the data for each field
• Field name: Click in the Area of Documents to Add and click again on the drop-down list to select the type of data by pressing Enter. Then type the name you want to for the new field and press Enter.
• Field data: Place the cursor under each of the field names, and begin entering all the records that will form the database.
• Type of Data: To modify the type of data of each field, the user must change from Datasheet view to Design view.
• Primary Key: Inserting a primary key can create relationships between tables.
• Open the database table: Change Datasheet view to Design view, the Design ribbon will be activated. Select the appropriate field and click.

Saving a database

• When starting a session: Provide a name and location for the new database file.

• When changing the file name: First, select the Save Database option from the File menu, choose a storage location, and assign a new name.

• When changing the type of view: Change it from the Datasheet view to the Design view.

• Naming Objects: Access will prompt for a database name when transitioning to Design view if it has not been saved.

  • When using the right button: Click, choose to Save.
  • From the file menu: Click the mouse pointer on the File menu, select the Save As option, and then Save object as.

Opening a database

• From Quick Access: When opened, the backstage view appears with recent databases and the "open other files" option.
• From the file menu: You can open a file by clicking on the File menu. The objects that make up the database appear in the navigation panel.

Closing a database

• To close a database: Use the File menu and choose the Close option.
• Save changes: Save the changes before closing losing any edits.

Adding a New Table to an Existing Database

• Use new table on an existing database to create different objects like forms, reports, queries, etc.

• To achieve this, use the create ribbon from the table group and with the Table common button.

• You can create other tables: Using the same methodology to create queries, reports and form relationships.

Database from a template:

• Go to the File menu and select the New option.
• Option to create a database is by selecting a template from the File menu, offering web database templates (SharePoint server), templates for students, sales, and project management.
• Templates are chosen through double-clicking, with web databases providing instructional videos.
• Selected templates display all components in the navigation panel, allowing modification of pre-assigned table fields and data types.

Working in a Database

• Selecting data in a table: Place the cursor on the left side until the selection symbol appears, and then click. If you have to select several records-drag the cursor to the desired record
• Select a field: Place the cursor at the top of the table until the selection symbol appears. If you need to select several fields, drag the cursor to the desired field.
• To select the entire table: Place the cursor at the top left of the table and click on it

Presentation of a database:

• This can be done be formatting the table: Through format group.
• Modify the height/width of columns can be by through the double arrow.

Input Mask

• Provides data validation, ensuring correct user input and easier data maintenance.
• The input mask: This provides a set format for data entry in a field using characters and symbols.
• Input masks: These can be added to table fields, queries, report and form controls.
• Input masks are defined to control the format in which data are entered into the database, but you can apply another format to the same data to change the way they are displayed

Input Mask: Sections

• First: Enforces mandatory characters, placeholders, and literal data.
• Second: Controls storage, either embedding characters or using display-only, saving space if set to display only.
• Third: Designates a placeholder, defaulting to the underscore character but customizable.
• Input characters: "0" requires a digit between 0 and 9, while "9" makes it optional.
• The # symbol in an input mask allows the user to type a digit, a plus sign (+), a minus sign (-), or a space. If nothing is entered, Access inserts a blank space.
• "L" forces a letter while "?" makes it optional.
• "A" makes letters or numbers mandatory, whereas "a" makes them optional.
• ">" and "<" convert text to uppercase and lowercase, respectively.

Input Mask: Formats

• Create an input Mask: Add to a table field whose type of data is set to Text, Number, Currency and Date and Time in input masks. -Use Format 0 to specify precise value entries in fields like phone numbers and dates, enhancing consistency. -Format 9: Provides users with flexibility in entering numeric values depending on the context of the field

  • Masks using "#": Flexible in format, supporting both digits and signs,.
  • Using the L format: Users must enter mandatory letters in certain fields, such as initials, country codes, or abbreviations.
  • ? format in Access: Provides with data entry where letters are optional.
  • If you want some to automatically have uppercase, the format to use is: The format > - If you want the standarize to show lowercase, use: The format <

• Navigation in a table can be through to different buttons to show what table contains.