Ibs 520 introduction to internet technology
Download
1 / 26

IBS 520 Introduction to Internet Technology - PowerPoint PPT Presentation


  • 104 Views
  • Uploaded on

IBS 520 Introduction to Internet Technology. Database Fundamentals Week 4. Database Fundamentals. A database is simply a structured collection of similar data. Databases display data in an imaginary two dimensional grid. . An Important Tutorial.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'IBS 520 Introduction to Internet Technology' - alva


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
Ibs 520 introduction to internet technology

IBS 520Introduction to Internet Technology

Database Fundamentals

Week 4


Database fundamentals
Database Fundamentals

  • A database is simply a structured collection of similar data.

  • Databases display data in an imaginary two dimensional grid.


An important tutorial
An Important Tutorial

  • Prof Holowczak is one of the most valuable CIS Professors at Baruch College. All the Database instructors at Baruch College use his tutorials for their classes.

  • Read the tutorial very carefully. The content of the tutorial will be sufficient for creating a basic database driven website.

  • http://cisnet.baruch.cuny.edu/holowczak/classes/2200/access/accessall.html


Relational database management systems
Relational Database Management Systems

  • RDBMS is a software program that lets you create databases and then manipulate data in the databases.

  • Data is organized in collection of tables.

    • Allows you to create database structures

    • It lets you easily add new records, change field values in existing records and delete records.

    • Contains built in query language

    • Contains built in report generator

    • Provides protection of databases.


Collection of related tables
Collection of Related Tables

  • A collection of related tables is called a database or relational database.

  • Sometimes you may want the information about Customers and orders they placed. To obtain this information you must have a way to connect records in the customer table to records in the order table.

  • You connect the records in the separate tables through a common field that appears in both tables.


Primary key
Primary Key

  • Each customer number in Customer table must be unique, so that you can distinguish one customer from one another.

  • The customer number field is the Primary Key of the Customer table.

  • A Primary Key is a field or collection of fields whose values uniquely identify each record in a table.

  • In the order table order number is the primary key.


Entity integrity
Entity Integrity

  • Access forces you to enter a value for the primary key field in every record in the table. This is known as entity integrity.

  • If you do not enter a value for a field, you have actually given the field a null value. You cannot give a null value to the primary key field.


Foreign key
Foreign Key

  • When you include the primary key from one table as a field in a second table to form a relationship between the two tables it is called a foreign key in the second table.


Guidelines for designing a database
Guidelines for Designing a Database

  • It is a useful tool but you need to carefully design the database.

  • In database design you determine fields,tables, and relationships needed to satisfy the requirements.


1 identify all the fields needed to produce the required information

Customer name

Order Number

Street

City

State

Zip Code

Customer Number

Billing date

Owner Name

Invoice amount

Placed by

Phone

First contact

1- Identify all the fields needed to produce the required information


2 group related fields into tables

CUSTOMER TABLE

Customer Number

Customer Name

Street

City

State

Zip Code

Owner name

Phone

First Contact

ORDER TABLE

Order Number

Billing date

Placed by

Invoice Amount

2- Group related fields into tables


3 determine each tables primary key
3- Determine each tables primary key

  • Customer Number can be the primary key for the Customer Table

  • Order Number can be the primary key for the Order Table


4 include a common field in related tables foreign key
4- Include a common field in related tables (Foreign Key)

  • With this common field Barbara can find all the orders placed by a certain customers


Avoid data redundancy
Avoid Data Redundancy

  • Data redundancy occurs when you store the same data in more than one place.

  • With the exception of common fields to connect tables you should avoid redundancy

    • It wastes storage space

    • Can cause inconsistencies


5 determine the properties of each field
5-Determine the Properties of Each Field

  • These properties include

    • fieldname,

    • fields maximum number of characters,

    • fields description,

    • fields valid values

    • and other field characteristics.


Guidelines for designing access tables
Guidelines for Designing Access Tables

  • A name can be up to 64 characters long

  • A name cannot start with a space.

  • A table or query name must be unique within a database.

  • A name can contain letters, numbers, spaces and special characters except a period(.) exclamation mark(!) and square brackets []


Tips

  • Capitalize the first letter of each word in the name

    • CustomerNum

  • Avoid extremely long names

  • Use standard abbreviations Num for Number Amt for Amount and Qty for Quantity

  • Do not use spaces in field names


Assigning field data types
Assigning Field Data Types

  • You must assign a data type for each field.

  • The data type determines what field values you can enter for the field and what other properties the field will have.

  • Billing date will store Date values so you assign Date/Time field value.


Creating a table
Creating A Table

  • Creating a table consists of :

    • Naming the fields and defining the properties for the fields.

    • Specifying a primary key (and a foreign key if applicable) for the table

    • Saving the table structure


Referential integrity
Referential Integrity

  • When you add a record to a related table, a matching record must already exist in the primary table.


Cascade update
Cascade Update

  • If you attempt to change the value of the PK (Primary Key) in the primary table, Access prevents this change if matching records exist in the related table. However if you choose “Cascade Updates” option, Access permits the change in value to the PK and changes the appropriate FK values in the related table.


Cascade delete option
Cascade Delete Option

  • When you delete a record in the Primary table, access prevents the deletion if matching records exist in a related table. However if you choose the cascade delete option, Access deletes the record in Primary table and all records in related tables that have matching FK values.


Introduction to queries
Introduction To Queries

  • Querying a database

    • A query is a question that you ask about data stored in a database

      • Display selected fields and records from a table

      • Sort records

      • Perform calculations

      • Update data in the tables in a database

      • Find and display data from two or more tables


Questions
Questions

  • Which customers have unpaid bills?

  • Which type of coffee sells in Ohio?

    The queries that ask for this information are called select queries.


Forms and reports
Forms And Reports

  • Refer to the tutorial.


ad