2440: 105 Intro to Computers & Application Software Enoch E. Damson Dept of Business TechnologyStudy Guide #4 (MS Access) The University of Akron Computer Information SystemsSummit CollegeMicrosoft AccessDatabase Concepts- Field – Description of an entity. Other names include attribute or column- Record – a collection of fields. Other names include tuple or row- Entity – collection of records. Other names include relation, table or file- Database – collection of data and metadata about related entities- Metadata – data about other data- Database management system (DBMS) – software used to manipulate databases- Relationships – associations between tables using common fields (with same data in more than one table)- Integrity constraints – rules imposed on a database management system to perform some functionso Primary key – the field that uniquely identifies a record in a table. Helps eliminate redundant recordso Foreign key – the field in one table required to match the primary key of another table to establishrelationships between the two tables and to ensure referential integrity constraintso Data types – define the type of data that can be entered in a fieldTable Elements- Table name – e.g. Student (student_id: text, student_name: text, age: number, date_enrolled: date/time)- Field names – e.g. Student (student_id: text, student_name: text, age: number, date_enrolled: date/time)- Field data types – e.g. Student (student_id: text, student_name: text, age: number, date_enrolled: date/time)Relationships- One-to-one (1:1) – when a record in one table is related on a single record in a second table. - One-to-many (1:M) – when a record in one table is related to multiple records in a second table- Many-to-many (M:N) – when multiple records in one table are related to multiple records in a second tableMicrosoft Access Data Types- AutoNumber – a unique sequential or random number assigned by Access as each record is entered- Text – text, or combinations of text and numbers, up to 255 characters- Memo – lengthy text, or combinations of text and numbers, up to 65536 characters- Yes/No – fields that contain only one of two values (Yes/No, True/False, or On/Off)- Number – numerical values used in calculations (Byte, Integer, Long Integer, Single, Double, Decimal)- Currency – monetary values- Date/Time – date and time values for the years 100 through 9999Microsoft Access Objects- Tables – contain the data about entities- Forms – display data, usually one record at a time, and are used to enter data- Reports – display data in a formatted manner for printing and publication- Queries – retrieve data that matches specific conditions (criteria)- Macros –Database Manipulation- Sorting – the process of arranging data in a specific order based on the value in each field. Data can be sorted in:o Ascending order – sorts text alphabetically (A-Z) and sorts numbers from lowest to highesto Descending order – sorts text in reverse alphabetic order (Z-A) and sorts numbers from highest tolowest- Filtering – the process of displaying only a portion (subset) of the total records based on matching specific values. Only used to provide quick answers without generally saving the results for future use- Query – question asked about databases to retrieve data based on specified conditions (criteria)Page 1 of 22440: 105 Intro to Computers & Application Software Enoch E. Damson Dept of Business TechnologyStudy Guide #4 (MS Access) The University of Akron Computer Information SystemsSummit College- Criteria – condition used to identify specific records- Comparison operators – symbols used to evaluate each field value to determine equality (=), greater than (>), less than (<), or in between a range (between, <=, >=) of values as specified by the criteria- Compound criteria – creates specific queries using the logical operators AND and OR- Logical operators – allow the use of compound (multiple) criteria for the same field or different fields- Wildcards – used in queries to serve as a placeholder for one or more unknown characters in criteria.o Asterisk (*) - used to represent a group of characterso Question mark (?) – used to represent a single character- Calculated fields - field values returned from a query through calculations- Aggregate functions – calculations that are performed on a group of records. Include SUM, AVG, MIN, MAX, COUNTComparison Operators in ConditionsOperator Definition= Equal to> Greater than>= Greater than or equal to< Less than<= Less than or equal to< > Not equal toBetweenLikeInLogical Operators in ConditionsOperator DefinitionAND Both parts of a specified criteria must be met OR Either part of a specified criteria must be metNOT Negates a criteriaArithmetic Operators for Calculated FieldsOperator Definition+ Addition- Subtraction* Multiplication/ DivisionAggregate FunctionsFunction DefinitionSUM Totals the values in a fieldAVG Averages the values in a fieldMIN Locates the smallest value in a fieldMAX Locates the largest value in a fieldCOUNT Counts the number of records in a fieldPage 2 of