Transcript Slides
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-A14 IBM-153 HP-A919 ........ 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 Id ASTB03 CSCA02 CSCA48 MTGA04 ...... ENROLMENT TABLE Name Astronomy The Why and How... Programming Management ........ Student Course 001 CSCA02 001 MTGA04 003 CSCA02 004 CSCA48 ...... ........ 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 1st Solution: Flat 2nd 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 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 Using Table Wizard Designing a table yourself (using Design view) 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 Data Types Text Memo Number Date/Time Currency AutoNumber Yes/No OLE Object Hyperlink Properties 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 ToolsRelationships… Create a 1-to-many relationship between the CustomerID fields Relationships One-To-Many relationship (Join) a CustomerID can appear once in Customers and many times in Invoices a CustomerID in Invoices must be in Customers Relationships Enforcing Referential Integrity causes an error message if a CustomerID 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