Database Design and E-R Diagrams

• Database facilitates data structuring, efficient organization, access, and analysis.
• Access provides a platform for creating data systems, but does not need programming.
• Entity-Relationship (E-R) Diagram provides a visual of entities and their relationships.
• Entities, as real-world objects, are linked by relationships.
• The E-R diagram guides implementation of the database in Access.
• Database design optimizes information use and ensures data integrity and performance.
• It reflects real-world interactions in a digital representation.
• Microsoft Access helps to create an information management system with intuitive tools.
• Access tools enable database building, query creation, report generation, and data analysis.
• Database design combines logic, structure, and understanding of data context.
• To create a database, you must first analyze the situation, think about the type of information that needs to be stored, and generate an E-R diagram.
• The objective of the Entity Relationship model is to obtain a representation of reality that captures its properties and to reflect them in a database.

Entity-Relationship Diagram

• The E-R diagram serves to identify entities, attributes, and relationships for database structure.
• Entity refers to any person, event, or concept for which data is stored, represented by a rectangle in an E-R model.
• Relationship signifies association between entities, represented by a rhombus with the relationship name inside.
• These elements define a relationship:
  • Name identifies the relationship, usually a verb in singular form
  • Cardinality indicates how many times an entity relates to another.
  • One-to-one: A single set of personal data is tied to one student.
  • One-to-many: Multiple employees in one company, but each employee in only one.
  • Many-to-many: Customers can buy diverse items, and items can be bought by many customers.
• Attribute expresses entity properties, qualities, allowing distinction, represented by labeled ellipses or circles connected with lines.
• Key attribute uniquely identifies instances of the entity.

Microsoft Access Platform

• Microsoft Access is a database management system for efficient data organization and manipulation.
• Databases consist of tables with rows and columns.
• Home screen facilitates quick access to database creation and management tools.
• The Home Screen has options for making blank databases, templates, or opening existing ones and the main features
• To see recent files you must open the Open Other File folder.
• A dialog box appears to confirm the name and location of the database when you chose blank database.
• The program has an organized interface that helps in the creation and management of databases.
• The main screen has the File Menu, a toolbar at the top with quick access, Navigation pane, the work area.
• The interface is designed to manage databases.
• The quick access toolbar is an area that you can customize with the most commonly used command buttons.
• The File Menu displays options for file manipulation, such as New, Open, Save, Print, and Close.
• The Options Ribbon has tabs (File, Home, Create, External Data, Database Tools) for database creation and design tools in Access.
• The Navigation Pane displays database objects: tables, queries, forms, reports, macros, or modules.
• The Document Area shows the database object being worked on, with the name of the active document at the top.
• Record Navigation Buttons is enable movement within the database and viewing of records.
• Status Bar shows information about the object in the document area, located at the bottom of the main screen.
• Object view lets you view the object in different ways: Datasheet and Design View.

Basic Concepts

• A database is a file with integrated information of the same type, in tables, reports, queries, and forms.
• Table structures data in rows and columns.
• Record refers to rows in a table, with related data for an individual or entity.
• Field refers to columns in a table, where each field is assigned a specific type of data.

Types of Views

• Datasheet view allows you to view, edit, and add data to a table.
• Design View enables modification of the internal structure of database objects.
  • Define fields: fields that will contain the information in the table
  • Assign types of data: what type of data will be kept
  • Set a primary key: unique record key
  • Configure field properties: required info
  • Enables control over the database structure, essential elements for data management.
• Form view displays data in a more accessible, personalized format for easier data entry and a focus on one record at a time.
• Report view display data in a structured way for analysis, printing, and grouping.
• Preview lets you see how a report will look before printing.

Types of Objects

• Table stores database data arranged in rows and columns.
• Form is created from a database for customizable display of table records, and is used to enter, view or modify data.
• Report presents data from a database in an organized way.
• Query allows you to search, filter, and analyze data from database tables.

Types of Data

• Text is key for managing information in databases, allowing entry of a combination of alphanumeric data.
• Number data performs arithmetic operations.
• Date and time shows date and time.
• Currency formats data like numbers.
• Hyperlink stores links to web addresses, files, emails, or locations in the same database.
• Attached Data stores files of different types, as documents or images.

Primary, Compound, and Foreign Keys

• Primary Key uniquely identifies each record in a table, ensuring no duplicates.
  • Unique
  • Cannot contain null values
  • Identifies a single record
• Compound Key combines two or more fields to ensure uniqueness.
  • Useful when no single field can guarantee data uniqueness
  • Set of the combined fields must be unique
• Foreign Key relates to the primary key of another table used to structure data.
  • Points to the primary key of another table
  • Establishes a relationship between two tables
  • Does not need to be unique in the table where it is defined

Input Masks

• Templates are ready-to-use databases including predesigned tables, reports, forms, and queries.
• Table design comes from interpretation and transformation of an Entity-Relationship (E-R) Diagram.
• Each digram becomes a table, where the entity attributes re transformed into many fields.
• The relationships determine how tables will be connected through keys

Databases and Templates

• Another way to create a database is to use the File menu, select the New option from the Backstage view, and click on the template.
• Access web database templates can be shared over the web via SharePoint server, templates for students, sales channels, tasks and project management.
• Can delete or modify the already assigned types the templates come with.
• To select a record in the table, You must place the cursor on the left side and then click when the selection symbol appears.

General Points

• Databases can be created from scratch or from pre-built templates
• Templates offer web database options sharable via SharePoint, templates for project management, tasks etc.
• You can create a new database by clicking the database from the blan desktop button.
• The name of the file can be defined
• First setting the names of the fields and data for each field can be done from the Datasheet view.
• Input Mask provides data validation and avoids entry of invalid data.
  • Helps in entering data correctly, for easier data location
• The Input Mask provides a specific format for data entry.
• You can add input masks to table fields, reports, queries and form controls.
• The data in the field must follow the pattern set by the input mask
• Controls data format in the database.

Input Mask Sections

• Mandatory: mask characters with placeholders.
• Optional: mask characters and how they should be stored.
• Third: placeholder used by Access.
  • 0 indicates a required digit.
  • 9 indicates an optional digit.
• The 0 in the second section indicates that the mask characterswill be stored with the data
• If instead of the underscore a hyphen is to be used it must be identified in the third section.
• Add a table field to create an input mask.
• These types of data are available for input masks: text, number, and date or time.

Defining Input Mask Characters

• Format 0 input masks ensures a specific vale into fields (phone numbers, dates, ID, etc)
• Format 9 is used when certain numeric values are optional in data entry.
• Using # in input masks is useful when flexible options are needed for entering digits or signs, it is ideal for fields where there may be variability in the input format, this ensures that users have the freedom to include or omit certain characters as needed.
• Using the L format in Access is useful when you need to ensure that users enter mandatory letters in certain fields, such as initials, country codes, or abbreviations. This insures consistency in data entry

More on Input Masks

• format in access ensures that any letter entered is automatically converted to uppercase.

• < format is useful to standardize data entry in lowercase, the data remains consistent.
• To modify the height of the table records, place the cursor in the middle of two records, the double arrow will appear, click and drag to the appropriate size.
• Home ribbon and the text format group are use in formatting the table.
• There are Record Navigation buttons to see the number of records.
• Use the Search to write data for the entire table.
• To obtain the expected results you will use the Home ribbon and the Records and Clipboard groups.