Week 10
This presentation is the property of its rightful owner.
Sponsored Links
1 / 40

Week 10 PowerPoint PPT Presentation


  • 75 Views
  • Uploaded on
  • Presentation posted in: General

Database Design . Week 10. Table Instance Charts. Create Tables. For the tables for the sample in the Wk10_TableInstanceChart.xlsx . Types of Data. Common Data Types in Access. Autonumber Text Memo Number Currency Date/Time Yes/No. Autonumber. Use for unique sequential numbering

Download Presentation

Week 10

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Week 10

Database Design

Week 10


Table instance charts

Table Instance Charts


Create tables

Create Tables

  • For the tables for the sample in the Wk10_TableInstanceChart.xlsx


Types of data

Types of Data


Common data types in access

Common Data Types in Access

  • Autonumber

  • Text

  • Memo

  • Number

  • Currency

  • Date/Time

  • Yes/No


Autonumber

Autonumber

  • Use for unique sequentialnumbering

  • Commonly used for PK (Primary Key)

  • Increments by 1

  • Automatically inserted when a recordisadded.


Week 10

Text

  • Used for text or combinations of text and numbers, such as addresses, or for numbers that do not require calculations, such as phone numbers, part numbers, or postal codes.

  • Stores up to 255 characters.


Week 10

Memo

  • Use for lengthy text and numbers, such as notes or descriptions.

  • Should be used “sparingly”, because it takes up too much room in database

  • Stores up to 65,536characters (if not more)


Number

Number

  • Use for data to be included in mathematical calculations, except calculations involving money (use “Currency”type).

  • When you are creating a RELATIONSHIP with an “AUTOnumber” field with the “PARENT” table


Yes no

Yes/No

  • Use for data that can be only 1of 2 possible values, such as

    • Yes/No,

    • True/False,

    • On/Off.

  • Nullvalues are NOTallowed


Week 10

Rule

  • One chart completed for each table

    • Wk10_TableInstanceChart.xlsx


Review relationship essentials

Review Relationship Essentials

  • Need a primary key

    • EVERY TABLE NEEDS A PRIMARY KEY

    • Should be 1stfield

  • Need a foreign key

    • a Foreign Key is a column in a table used to reference a Primary Key in anothertable.

    • A table does NOT necessarily have to have a Foreign Key

    • Whenever you see: “MANY”—this will be the Foreign Key

  • Primary key and foreign keys are essential when you create relationships that join together multiple tables in a database


Build time

Build Time

  • For each of the following scenarios:

    • Create and write out a business narrative

    • Create list of ENTITIES/tables

    • Create the attributes for the table

    • Create the ERD and the relationship rules

    • Create a table instance chart


Human resources 2

HUMAN RESOURCES 2


Human resources 3

HUMAN RESOURCES 3


Create tables in ms access

Create Tables in ms access


Table design view

Table Design View

  • Exercise:

    • Create table for gender


Datasheet view

Datasheet View

  • Exercise:

    • Add data to the gender table


Table design view1

Table Design View

  • Exercise:

    • Create Ribbon > Table Design in the Tables grouping

    • Identify your own PK

    • Moving the order of the rows


Create table with lookups

Create Table with Lookups

  • Exercise:

    • Add data to the city entity using a look up wizard as the data type for the province


Add data to an entity

Add Data to an Entity

  • Based on another entity

    • Exercise:

      • Create the students table (studentID, fname, lname and gender fields only)


Edit an entity

Edit an Entity

  • Exercise:

    • Complete the Students table

    • Go back and look at your default value (note it was not added to previous records entered)


Edit data in datasheet view

Edit Data in Datasheet View

  • Exercise:

    • Go back and add the age group and city for each of the students previously entered

  • Datasheet view

    • Record count

    • Add a new record

    • Search for a record

    • Sort the records


Compacting repairing a database

COMPACTING & REPAIRING A DATABASE

  • DATABASE TOOLS> COMPACT & REPAIR button

    • •As you add, edit, & delete objects, a database changes in size

    • •To minimize the size of a database & improve performance should be done on a regular basis

    • •Rearranges how a fragmented database is stored on disk


Want more information

Want More Information

  • Visit the following site.

    http://www.functionx.com/access/index.htm


Queries

Queries


Week 10

QUERIES: DEFINITION

Is a QUESTIONto the database-asking for a “set of records”from 1or more Tables/QueriesMS Access responds by displaying the requested datais a storedquestion, rather than a stored responseresultsautomatically update,IFtable is edited, it allows you to view & operate on “selected subsets” of your data


Northwind database

Northwind Database


Single table queries

Single Table Queries

  • Select fields to display

  • Sort by?

  • Simple Conditions

    • When you enter text into the criteria cell your text should be enclosed in quotes ("") to distinguish it from other expressions and operators that you may need to add.


Working with text

Working with Text


Matching text

Matching Text


Or statement

OR Statement


In statement

IN Statement


Not statement

NOT Statement


Not in statement

NOT IN Statement


Like text statement

LIKE “Text*” Statement


Like text statement1

LIKE “*Text” Statement


Hybrid homework

Hybrid / Homework

  • Read Calculating in Access Queries

  • Read Calculating Totals in Access

  • Read Working out Someone’s Age

  • Watch Multi-Table Queries Part 1 & 2

  • Database Assignment Part 2


Hybrid myitlab hybrid myitlab

HYBRID: MYITLABHYBRID: MYITLAB

  • MYITLAB HOMEWORK:


Quiz time

Quiz Time


  • Login