![Page 1: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/1.jpg)
MS Office & Internet I
Database Concepts Part 01 & 02
![Page 2: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/2.jpg)
The Database Environment
The Database Environment consists of all the parts and pieces that makeup a database system
![Page 3: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/3.jpg)
The Database Environment
DUNSHA
D Data
U Users
N Network
S Software
H Hardware
A Administration
![Page 4: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/4.jpg)
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
![Page 5: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/5.jpg)
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
![Page 6: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/6.jpg)
Relation Rules
No Duplicate rows in a Table
No order of Rows
No order of Columns
![Page 7: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/7.jpg)
Relationships
Defines how Tables are related to each other in the database
![Page 8: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/8.jpg)
Relationships
Relationships are created by using a Foreign Key
![Page 9: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/9.jpg)
Relationships
Primary Key
Must be UNIQUE
Is MANDATORY
Is UNCHANGING
Is CONTROLLED BY IT DEPT
![Page 10: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/10.jpg)
Relationships
Foreign Key (FK)
Is the Primary Key (PK) of the parent table
![Page 11: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/11.jpg)
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)
![Page 12: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/12.jpg)
Relationships
Rules:
A Foreign Key can be NULL
It must be a PK in the related table
(We call this enforcing Referential Integrity)
![Page 13: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/13.jpg)
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
![Page 14: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/14.jpg)
Database Model
A Primary Key and Foreign Key may be Composite Keys
Made up of more than one Column (Attribute)
![Page 15: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/15.jpg)
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)
![Page 16: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/16.jpg)
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
![Page 17: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/17.jpg)
MS Access
MS Access is a Personal or Small Business Relational Database
It is limited in scope but powerful for its intended purpose
![Page 18: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/18.jpg)
Microsoft SQL Server
This is the Enterprise or Large Business Relational Database which is very powerful and
complete in scope
![Page 19: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/19.jpg)
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
![Page 20: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/20.jpg)
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
![Page 21: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/21.jpg)
Questions
End Part 01
![Page 22: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/22.jpg)
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
![Page 23: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/23.jpg)
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
![Page 24: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/24.jpg)
Relational Database Theory
• Entity-Relationship Model: Basic Concepts– Entity
• Thing, Object, Concept of interest to the enterprise• Each occurrence can be uniquely identified
![Page 25: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/25.jpg)
Relational Database Theory
• Entity-Relationship Model: Basic Concepts– Attribute
• Property of an entity• Column
![Page 26: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/26.jpg)
Relational Database Theory
• Entity-Relationship Model: Basic Concepts– Relationship
• Association between two (or more) entities
![Page 27: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/27.jpg)
Relational Database Theory
• Entity-Relationship Model: Basic Concepts– Entity Identifier
• Attribute(s) whose value uniquely identifies an entity• Primary Key
![Page 28: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/28.jpg)
Relational Database Theory
• What is an Entity?– Physical entity types
• Person• Building• Machine• Book• Usually Singular
![Page 29: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/29.jpg)
Relational Database Theory
• What is an Entity?– Conceptual entity types
• Contract• Account• Order• Course
![Page 30: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/30.jpg)
Relational Database Theory
• What is an Entity?– Event entity types
• Transaction• Shipment• Reservation• Phone Call• Seminar Offering
![Page 31: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/31.jpg)
Relational Database Theory
• Entity-Relationship Model: Diagrams– Example:
– Soft Rectangle represents entities• Noun• Singular
– Connecting Line represents relationships• Verb
Member
Animal
Adopts
![Page 32: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/32.jpg)
Relational Database Theory
• Relationships have Characteristics– A relationship has Cardinality (Degree)
One-to-One One-to-Many Many-to-Many
![Page 33: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/33.jpg)
Relational Database Theory
• Each entity’s participation is Mandatory or Optional
• Cardinality & Optionality
are based on business rules
Mandatory
Optional
![Page 34: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/34.jpg)
Relational Database Theory
• One:One Relationship– One Member adopts one animal– One Animal is adopted by one member
Member
Animal
Adopts
![Page 35: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/35.jpg)
Relational Database Theory
• One:Many relationship– One member adopts one animal– One animal is adopted by many members
Member
Animal
Adopts
![Page 36: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/36.jpg)
Relational Database Theory
• Many:Many relationship– One member adopts many animals– One animal is adopted by many members
Member
Animal
Adopts
![Page 37: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/37.jpg)
Relational Database Theory
• Optionality: Participation in a Relationship
Zoo Employee
Animal
Cares for
![Page 38: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/38.jpg)
Relational Database Theory
• Mandatory– Every instance of the entity MUST
participate in the relationship– Example:
• Every animal is cared for by at least one employee
![Page 39: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/39.jpg)
Relational Database Theory
• Optional– An instance of the entity CAN participate
in the relationship– Example:
• Some employees do not take care of animals
![Page 40: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/40.jpg)
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
![Page 41: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/41.jpg)
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
![Page 42: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/42.jpg)
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
![Page 43: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/43.jpg)
E-R Diagrams
• Practice 02– A company has twenty employees– Each employee works for a department– There are two departments in the
company
![Page 44: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/44.jpg)
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
![Page 45: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/45.jpg)
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
![Page 46: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/46.jpg)
Questions
End Part 02
![Page 47: MS Office & Internet I Database Concepts Part 01 & 02](https://reader035.vdocuments.us/reader035/viewer/2022062422/56649f265503460f94c3cce8/html5/thumbnails/47.jpg)
Assignment 04
In-Class Project
Develop a Database in MS ACCESS 2003
Choices Are:
Music Library
Book Library
Club or Organization