ms office & internet i database concepts part 01 & 02

MS Office & Internet I

Database Concepts Part 01 & 02

The Database Environment

The Database Environment consists of all the parts and pieces that makeup a database system

The Database Environment

DUNSHA

D Data

U Users

N Network

S Software

H Hardware

A Administration

What is a Database?

A Database is a:

Collection of Related Data for

A Known Group of Users that meet

Specific Requirements and

Models or Represents the Real World

Database Structures

Relational Type Object Type MS Access Type

Relation Table Table

Tuple Row Record

Attribute Column Field

Primary Key Primary Key Primary Key

Foreign Key Foreign Key Foreign Key

Relation Rules

No Duplicate rows in a Table

No order of Rows

No order of Columns

Relationships

Defines how Tables are related to each other in the database

Relationships

Relationships are created by using a Foreign Key

Relationships

Primary Key

Must be UNIQUE

Is MANDATORY

Is UNCHANGING

Is CONTROLLED BY IT DEPT

Relationships

Foreign Key (FK)

Is the Primary Key (PK) of the parent table

Relationships

Rules:

No Part of the Primary Key can be NULL

(We call this enforcing Entity Integrity)

NULL = Nothing

(It is not Zero (0) it is nothing)

Relationships

Rules:

A Foreign Key can be NULL

It must be a PK in the related table

(We call this enforcing Referential Integrity)

Database Model

Represents Reality

Has Relations (Tables) that:

Equals one Entity Type per Table

Each Row represents only one occurrence of the Entity

Each occurrence (Row) is Unique

Database Model

A Primary Key and Foreign Key may be Composite Keys

Made up of more than one Column (Attribute)

Example Table (w/Data)

ANAME AFAMILY WEIGHT

Candice Camel 1800

Zona Zebra 900

Sam Snake 5

Elmer Elephant 5000

Leonard Lion 1200

Primary Key

(Underlined)

Relation (Table)

Attributes (Columns)

Tuples (Rows)

Example Relationship

MID MNAME MADDR ANAME

171 N. Harrison 1400 Blush Rd

Zona

144 J. Montagano

1108 5th Ave Leonard

194 J. Spence 1244 Lark Ln Candice

303 E. Wingate 5222 Gains Dr Candice

101 H. Yarchun 177 Beach Rd

270 K. Steeg 140 Crystal Dr Zona

291 S. Ackerman 1172 Park Dr Sam

301 K. Snyder 196 279th Ave

ANAME AFAMILY WEIGHT

Candice Camel 1800

Zona Zebra 900

Sam Snake 5

Elmer Elephant 5000

Leonard Lion 1200

AnimalForeign Key

(Circled)Zoo-Member

MS Access

MS Access is a Personal or Small Business Relational Database

It is limited in scope but powerful for its intended purpose

Microsoft SQL Server

This is the Enterprise or Large Business Relational Database which is very powerful and

complete in scope

ORACLE Database

This is the Enterprise or Large Business Relational Database which is very powerful and

complete in scope

Taught at Rio in courses:

IT 20303 DBMS Concepts

IT 20803 Database Communications

IT 21003 Database Administration

Can lead to Oracle Certification (OCA) or the URG Database Technology Certificate

MS Access

We will build a database in Class using MS Access utilizing all of the parts and pieces:

Tables

Rows

Columns

PK’s

FK’s

Questions

End Part 01

Entity-Relationship Diagrams

Logical Database Design

What is a Data Model?A way to represent reality

A schematic of data items and relationshipsA “blueprint” for the database

Entity-Relationship Diagrams

The Entity-Relationship Approach

Represents reality using well-defined graphics and rules

Basic building blocks are:

“things” (entities) and relationships

Member

Animal

M

1 Adopts

Relational Database Theory

• Entity-Relationship Model: Basic Concepts– Entity

• Thing, Object, Concept of interest to the enterprise• Each occurrence can be uniquely identified


• Entity-Relationship Model: Basic Concepts– Attribute

• Property of an entity• Column


• Entity-Relationship Model: Basic Concepts– Relationship

• Association between two (or more) entities


• Entity-Relationship Model: Basic Concepts– Entity Identifier

• Attribute(s) whose value uniquely identifies an entity• Primary Key


• What is an Entity?– Physical entity types

• Person• Building• Machine• Book• Usually Singular


• What is an Entity?– Conceptual entity types

• Contract• Account• Order• Course


• What is an Entity?– Event entity types

• Transaction• Shipment• Reservation• Phone Call• Seminar Offering


• Entity-Relationship Model: Diagrams– Example:

– Soft Rectangle represents entities• Noun• Singular

– Connecting Line represents relationships• Verb

Member

Animal

Adopts


• Relationships have Characteristics– A relationship has Cardinality (Degree)

One-to-One One-to-Many Many-to-Many


• Each entity’s participation is Mandatory or Optional

• Cardinality & Optionality

are based on business rules

Mandatory

Optional


• One:One Relationship– One Member adopts one animal– One Animal is adopted by one member

Member

Animal

Adopts


• One:Many relationship– One member adopts one animal– One animal is adopted by many members

Member

Animal

Adopts


• Many:Many relationship– One member adopts many animals– One animal is adopted by many members

Member

Animal

Adopts


• Optionality: Participation in a Relationship

Zoo Employee

Animal

Cares for


• Mandatory– Every instance of the entity MUST

participate in the relationship– Example:

• Every animal is cared for by at least one employee


• Optional– An instance of the entity CAN participate

in the relationship– Example:

• Some employees do not take care of animals

E-R Diagrams

• Guidelines to Develop an E-R Diagram– Identify the Major Entities– Identify the Attributes for each entity– Determine the Unique Identifier(s)– Identify the Relationships– Assign Cardinality– Determine Optionality– Resolve M:N Relationships

E-R Diagrams

• Mapping the E-R Diagram to the Relational Database– Each entity becomes a Table– Each attribute becomes a Column– Unique Identifier becomes the PK– Each 1:M becomes a FK on the Many

Side

E-R Diagrams

• Practice 01– A company has ten departments– A company has five divisions– A company has one hundred employees– Each employee must work for one

department– Each division has two departments

E-R Diagrams

• Practice 02– A company has twenty employees– Each employee works for a department– There are two departments in the

company

E-R Diagrams

• Practice 03– A company has three divisions– A company has one manager per division– Each manager is in charge of one

committee

E-R Diagrams

• Practice 04– A company has a sales department with

fifteen salespersons– Each salesperson works for the sales

department– Each salesperson is supervised by one

manager– The managers may not have an

salesperson to supervise

Questions

End Part 02

Assignment 04

In-Class Project

Develop a Database in MS ACCESS 2003

Choices Are:

Music Library

Book Library

Club or Organization

ms office & internet i database concepts part 01 & 02

Documents