1 / 20

Enhance Your Data Skills: Mastering Microsoft Access and Referential Integrity

In this CS 101 course, taught by Cody Cutright, you'll gain essential skills in Microsoft Access, focusing on creating and managing databases. Learn how to design tables with various data types, establish relationships, and enforce referential integrity to maintain data accuracy. With practical assignments and upcoming important dates, this course aims to equip you with the knowledge necessary for success in the business world. Join us as we explore database design and data validation, ensuring your data entry meets the correct standards.

raanan
Download Presentation

Enhance Your Data Skills: Mastering Microsoft Access and Referential Integrity

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Tip: Opening a single file in an email, or from the internet, can compromise your entire computer. http://en.wikipedia.org/wiki/Remote_administration_tool

  2. Day 14:Access Chapter 2 Cody Cutright CS 101 February 23rd, 2014

  3. About Computer science • My mission as a CS 101 Instructor is to give you all the tools you need to succeed in the business world in relation to Microsoft Office. That being said, I’m also available before class if you want to discuss anything technology related (even after class if you’re particularly interested some days). • “Knowledge is power, and knowing is half the battle.” - Every GI Joe PSA… ever

  4. Upcoming due dates • 2/28: Midpoint of semester • 3/07: Last day to drop • 3/10-3/14: Spring Break • 3/17: MyITLab Lesson C

  5. Chapter 2 Objectives • creating tables (Design View) • fields • keys • data types • lookup fields • indexing • relationships window • creating relationships • specifying relational integrity settings • importing data and tables • importing from XML files (content in Chapter 17) • importing from Excel spreadsheets (content in Chapter 17) • creating and modifying queries • creating using Query Wizard (brief overview) • using Design View • specifying tables • adding fields • sorting • specifying criteria

  6. Table Views – Datasheet View The Datasheet view of an Access table resembles an Excel spreadsheet and displays data in a grid format—rows represent records, and columns represent fields.

  7. Table Views – Design View Design view is used to create and modify the table structure by adding and editing fields and by setting the field properties.

  8. Referential Integrity Why is referential integrity important? It makes sure that a foreign key in a table exists as a primary key in a related table.

  9. Referential integrity

  10. Input mask • Makes sure the data you enter is in a correct form • For example, (33)324-99 is not a valid phone number! • But a number of the form (999) 000-0000 is!

  11. Creating Tables In Design View, we will create three tables: Customers, Orders, and Items

  12. Customers Table: AccountNumber AccountType LastName FirstName RepName Phone Address City State Zip RepeatCust?

  13. To do: Delete the ID Row Account Number Data Type Account Type Lookup Phone Input Mask ZipCode Input Mask RepeatCust? Yes/No Index by Address What’s missing? Now, save the table

  14. Orders Table: OrderNum AccountNum RepName ItemSKU

  15. To do: Delete the ID Row AccountNum -> What to do? ItemSKU ItemSKU Data Type? Save table changes

  16. Items Table: ItemSKU ItemName Set Key Save Changes

  17. Relationships: Close any open tables. Access simplifies creating relationships, what relationships should our tables have? Design -> Relationships

  18. Creating Relationships: • Show all tables • Link from Orders to Customers with AccountNumber • Enforce referential integrity • Create • Link Orders and Items via ItemSKU • We will assume each order has only one item • Save Relationships

  19. Data Validation • Data Validation is a formula that can be specified to help ensure incorrect data is not entered. • In our example, let’s assume we’ve started our order numbering at 1,000 after switching over from an old system (to preserve records). Let’s create a data validation rule and validation text.

  20. More Practice! • http://cs101.wvu.edu/instructors/cutright/lecture-notes/

More Related