Database Design Importance and Microsoft Access

Introduction to Database Design in Access

• In today's digital age, managing vast amounts of data is crucial for organizations of all sizes
• Database design enables efficient data structuring and organization, facilitating easy access and analysis
• Microsoft Access is a user-friendly platform for creating data systems without advanced programming knowledge
• Key topics include database design, information analysis, entity relationship representation (E-R), and functional system construction using tables, forms, queries, and reports

The Entity-Relationship (E-R) Diagram

• The Entity-Relationship Diagram (E-R) is a fundamental tool in database design
• It provides a visual representation of system entities and their relationships
• Entities represent real-world objects (e.g., customers, products)
• Relationships define how entities connect (e.g., a customer places multiple orders)
• E-R diagrams aid in visualizing the system structure, and guiding the implementation phase in Access
• E-R diagrams enable clear and efficient database organization

Database Design

• Database design goes beyond simple data storage
• It structural organization process information to optimize its utilization
• The design ensures data integrity, redundancy prevention, and enhanced system performance
• Every database represents a digital model of a real-world system, reflecting entity interactions
• A well-planned design prevents data loss, duplication, and confusion, also enables sales analysis, trend identification, and informed decision-making
• Essential to understand the nature of the data, identify system entities, define attributes, and clarify entity relationships

Creating a Data Manager in Access

• Design, analysis, and modeling are combined for creating an information management system in Microsoft Access
• Access provides tools for constructing comprehensive databases, facilitating data storage, and simplifying data entry
• It also builds queries and reports for extracting and analyzing data
• Database design blends logic, structure, and data context understanding
• A complete database creation process will be guided, from conceptual design to Access implementation
• Provides the skills to build functional and efficient databases for diverse real-world needs
• Other Database Management Systems (DBMS) include MySQL, Microsoft SQL Server or Oracle Database
• These systems have varied features and capabilities to address different user or business needs

The Entity-Relationship (E-R) Diagram

• The first step for database creation is:
  • Situational analysis
  • Determining information storage needs
  • Generating an E-R Diagram
• Conceptual Entity-Relationship (E-R) modeling interprets the real world through specific symbols and expressions
• The model's purpose is to represent and capture reality within a database
• The E-R Diagram is a graphical tool that models the logical structure of a database
  • It shows system entities and their attributes

E-R Diagram Symbology

• **
• Entity - Represented with a rectangle
• Relation - Represented with a diamond
• Attribute - Represented with an eclipse

Steps to Develop an E-R Diagram

• Identify entities involved in the situation
• Identify the relationships between entities
• Identify the attributes of these entities and relationships
• Identify the primary key for each entity or relationship

Entities

• Represent a person, event, or concept about which information is stored
• In an E-R model, an entity is represented by a rectangle labeled with the entity's name

Relations

• Represent an association or correspondence between entities
• Relations are represented by a diamond containing the name of the relationship
• For example, a "works" relationship between an employee and a bank branch

Relation Elements

• Name of Relationship
  • Distinguishes each relation with a descriptive name
  • Usually a verb in singular form
• Relation Cardinality
  • Indicates how many times an entity may relate to another
  • Indicates possible connections entity such as students, can have with another entity
    • One to one
    • One to many
    • Many to many

Attributes

• Used to express the properties, qualities, identifiers, or aspects of an entity or relationship
• Attributes provide more detail about an entity, helps distinguish it from others
• Attributes in an entity are represented graphically by labeled ellipses or circles
  • These are connected to the corresponding entity or relationship with a straight line
  • Attributes must have a unique name that clearly describes content

Developing an E-R Diagram

• A preparatory school needs a system that allows for manageing grades for students in each learning unit of the semester
• The system should register and consult grades Information such as the teachers that impart each learning unit should be included
• Have identified four fundamental elements:
  • Students
  • Learning units
  • Grades
  • Professors

Steps

• Identify entities to form a system design (these entities will be converted into tables)
  • Key entities: Student, Grade, Profesor, and Learning Unit
• Identify the relationship between each entity
• "Student" connects with "Grade"
  • Each student receives a grade for each Learning Unit
• "Learning Unit" relates to "Grade"
  • Each unit generates multiple grades for students
• "Professor" relates to "Learning Unit"
  • A professor may teach several Units
• These relationships should allow the information to be structured in an organized way

Relationships Between Entities

• Student and Grade
  • A student can have several grades
  • Each grade is associated with one student
  • One-to-many relationship
• Learning Unit and Grade
  • A Learning Unit involves multiple grades
  • Each grade pertains to one Learning Unit
  • One-to-many relationship
• Professor and Learning Unit
  • A professor can teach several Units
  • Each Unit is assigned to one professor
  • One-to-many relationship

Attributes

• Each entity should have attributes that describe characteristics
• In the student entity, the attributes include:
  • Registration number
  • Name
  • Email
• Each entity with the cardinality "many" should ensure an attribute is included
• Grade (related to Student and Learning Unit)
  • Matricula
  • Learning Unit code
• Learning Unit related to Profesor
  • Profesor Code is necessary other than a unique identifier

Attributes for the system

• Student: Matricula Name Date of birth Email CURP
• Learning Unit Learning Unit Code Learning Unit Name Description Semester, Frequency Profesor Number
• Profesor Profesor Number Name, Last name Email Telephone, Speciality
• Grade: Matrícula, Learning Unit Code Evaluation Date Evaluation

Primary Keys

• Each instance of an entity has to have a primary key for identification
• All entities with all necessary attributes and relations form an E-R diagram

Microsoft Access Interface

• Microsoft Access creates, organizes and manipulates data in user-friendly way
• Accesss has evolved with version updates
• Access allows both novice and experienced people to manage the amount of infomation
• Databses are made up of table rows and collumns similar to a spreadsheet Numeric and alpha numeric data can be entered through information data gathering forms
• Reports on base information can be generated
• Consultations that faciliate data manipulations can be achieved

Log In

• The Microsoft Access icon displays the program where the data base can be created

Panel Displays

• The Access Panel has all the quick access creation tools designed for creation and data base maintnence
• Access openes a Panel with the tools
  • Create data based
  • Create data templates
  • Access earlier data bases
• A page with the last data bases can be displayed
• The opening panel is the point to orginize and access the functionaries of Access

Main Access Screen

• The main screen of Microsoft Access provides a organized interface
• This interface faciltates the creation and management of databases
• Opening Access will display the Archive Menu and a upper part tool bar
• Work section
• Navigation Page
• An easy and fast access to all tools

Main Access Screen tools

• Quick Access Toolbar
  • It customizes command buttons inside access
• File Menu
  • It displays all the file options:
    • New
    • Open
    • Guard as
    • Print
    • Close
• Ribbons Chart
  • Archive tools
  • Open Tools
  • Creates
• Navigation Panel:
  • Table, questions, forms, reports, macros

Document tools

• The area where data base object that can be edited, the main page where it can have various open windows
• Record Navigation Buttons: Moves in the data base, can move foreward and backwards, go to first or last
• State tool bar: Main section in part inferior display, presents information on the objects in documents
• Object Watch: Displays the objects you an watch in files, both design and data display

Structure

• File is the integration info that contains type in table form, reports, questioniaries and forms
• Data base with Excel based data tools

Fundamental Concepts

• A. Table: Rowand columns structure to integrat data
• B. Record Rows of the table where the info is selated that contains fields
• Campo (fields) Type of data: text, number, date

How to Use These Views

• Microsoft Access has views that allow the use of data in diverse forms
• Objects: table, form, inquiries, reports
• All views have spcific functions:
  • design
  • introduce
  • visualize data

Visual Forms

• Similar to a calculation chart allow to watch, edit, and add directly in a chart or question
• Data shows in both rows and collumns: Access Icon
• The Icon for microsoft Access.
• Main Screen in form of access main window.

File Type

• A file in which information is integrated in organized form.

1.4.4 Types of data

• The data which contains is compose of the fields of the table
• The data are usually used in excel
• Certain props permit manipulation of files, reports and questionarires
• Data types are shows in next image
• Ab text
• 12 number
• Money
• Date and time.

Primary key

• The primary key is composed of a few fields that allows for the uniques identification
• A register inside a table.
• Each entity within should follow below
• Should contain a unique entity name
• Should not repeat any entity inside a file
• An example is the "student" file, the field is Matricula

Compose key

• The key is composed of 2 or more tables or files to be guaranteed unicity of certain registers
• Es combination:
• 2 o mas campos, Todos campos de la clave compuestas en conjunto deben unicidad.