Database collection of related information stored in an

Database collection of related information stored in an organized form Database program software tool for storage & retrieval of that information.

Database Terminology Data is held in tables Record: one row in a a table Field: columns that each record is divided into Filing cabinet analogy Database (ABC Inc. ) Table (Customers) Record (cust#78) Field (last name)

Relational Database one-to-one relationship table EMPLOYEE TABLE Id 001 002 003 004. . . Last Name Smith Jones Doe Black. . First Name Joan Paul John Jason. . WORK STATION TABLE Work Station 6 -1942 -A 9 -0865 -A 8 -3649 -B 8 -3775 -A. . . . Work Station 6 -1942 -A 8 -3649 -B 8 -3775 -A 9 -0865 -A. . . . Make and Model IBM-153 DELL-A 14 IBM-153 HP-A 919. . . . key EMPLOYEE 1 Operating System MS Linux MS MS. . . record 1 WORK STATION each employee has one workstation

Relational Database EMPLOYEE TABLE Id 001 002 003 004. . . Last Name Smith Jones Doe Black. . First Name Joan Paul John Jason. . one-to-many relationship DEPARTMENT TABLE Department IT MF IT HR. . . . Id AR IT HR MF. . . Name Accounts Receivable Information Technology Human Resources Manufacturing. . . . key EMPLOYEE * 1 DEPARTMENT a department has many employees an employee works in one department

Relational Database many-to-many relationship STUDENT TABLE Id 001 002 003 004. . . Last Name Green Black Taylor Trane. . COURSE TABLE First Name Jack Millie Peter Oscar. . key ENROLMENT TABLE Id ASTB 03 CSCA 02 CSCA 48 MTGA 04. . . Name Astronomy The Why and How. . . Programming Management. . . . Student Course 001 CSCA 02 001 MTGA 04 003 CSCA 02 004 CSCA 48. . . STUDENT COURSE 1 1 a student takes many courses * ENROLMENT * a course has many students

Flat vs Relational Flat database system that performs operations on a single file Relational database system that has a number of different tables, which may be linked together by common fields

Flat Suppose you want to store information about books and their authors 1 st Solution:

Flat 2 nd Solution: both solutions: replication & data accuracy problems, wastes space, data retrieval problems

Relational Correct solution uses 2 linked tables author’s name common to both tables one-to-many relationship ∞ 1

Primary Key Every table should have a primary key – must be unique – prevents duplicates – maintains sort order (table index) – used to define relationships – may be a combination of several fields Good Bad SIN Last Name Employee ID Address Account # Dept ID User ID Job Title Email any item that could be duplicated

Database Terminology this is an Access Table: Record Field value

primary key (every table needs one) is used for • sorting the table • accessing individual records • linking tables

Form for entering records into a Table

Form for entering records into a Table

example of a Query on one Table

similar Query but showing only failing marks

similar Query, now modified to include name and address from another Table

example of a Report based on a Table

example of a Report based on a Query

MS Access Environment Menu Bar Toolbar Database Window wizards Object Buttons tables Status Bar

Creating a Table n Using Table Wizard n Designing a table yourself (using Design view) n ¨ field names ¨ data types ¨ primary key ¨ properties Views: Datasheet or Design ¨ enter data using the Datasheet view

Design View Primary Key Field Names Data Type Description Field Properties

Data Types Text Memo Number Date/Time Currency Auto. Number Yes/No OLE Object Hyperlink

Properties Field size Format Input Mask Caption Default Value Validation Rule Validation Text Required Allow Zero Length Indexed

Datasheet View • Use to add, edit, or delete records Currently selected record: editing saved End Number of current record Total records

Relationships n Ø Tools Relationships… Create a 1 -to-many relationship between the Customer. ID fields

Relationships n One-To-Many relationship (Join) a Customer. ID can appear once in Customers and many times in Invoices ¨ a Customer. ID in Invoices must be in Customers ¨

Relationships n Enforcing Referential Integrity causes an error message if a Customer. ID not in the Customers table is entered

Indexes • Examine indexes set up for database – make it faster to search a table – should be set up if searching on a field is anticipated Note: indexing refers to how data is stored, whereas sorting changes how the table is displayed.

Create a Form: wizard • Form view

Form: Design view sizing handles drag Toolbox

Form: complex example Each page shows one record Label Text Box (bound to field) Command Button