Transcript Database Components - Dr. Stephen C. Hayne

Database System Components
The Database and the DBMS
Stephen C. Hayne
1
A Model of the Database
Processing System
Bridge
Machine
Hardware
Programs
Human
Data
Procedures
People
Instructions
Actors
Stephen C. Hayne
2
Database System Components
Stephen C. Hayne
3
User Data

Data about
– Entities or objects
» employees, departments, products....
» students, courses, professors
– Attributes
» SS#, name, address, salary, department
– Relationships (bi-directional) between entities or
objects
»
»
»
»
Stephen C. Hayne
Employees assigned to Departments.
Departments are assigned Employees.
Students take Courses.
Courses are taken by Students.
4
Users’ Data
For relational databases, tables with rows
and columns representing unique records
and attributes
 Not all tables are equally desirable: What is
the problem with this?

Movie
Stephen C. Hayne
Rating
Star
Saving Private Ryan
G
Tom Hanks
Saving Private Ryan
G
Matt Damon
Titanic
R
Kate Winslet
Titanic
R
Leonardo Di Caprio
5
Relationships Between Entities

Cardinality
– the number of entities of each type that may
participate in the relationship
– may be
» 1:1 (one-to-one)
» 1:M (one-to-many)
» M:N (many-to-many)
– to determine cardinality, ask:
Stephen C. Hayne
» for each A, how many B’s may be related?
» for each B, how many A’s may be related?
6
Minimum & Maximum Cardinality



For each A, what is the minimum and maximum
number of related B’s.
For each B, what is the minimum and maximum
number of related A’s.
If the minimum is zero
– The relationship is “optional.”
– The entity may or may not (sometimes) participates

One or more
– The relationship is “mandatory.
The entity must (always) participate.
Stephen C. Hayne
7
Relationship Examples




Business Rules
1 : 1 (one-to-one)
– Each faculty member is always assigned to one office.
– Each office is sometimes assigned to one faculty member.
1 : M (one-to-many)
– Each employee is always assigned to one department.
– Each department is always assigned many employees.
M : N (many-to-many)
– Each student always takes many courses.
– Each course is sometimes taken by many students
Stephen C. Hayne
8
What is the cardinality between...

Cities and zip codes?
– Each city ? has ? zip codes.
– Each zip code ? corresponds to only ? city.
===> 1:M

People and phone numbers?
– Each person ? has ? phone numbers.
– Each phone number ? belongs to ? persons.
===> M:N
Stephen C. Hayne
9
Four Data “Models”
Hierarchical
 Network
 Relational
 Object Oriented

Stephen C. Hayne
10
Hierarchical
Support 1:1 and 1:M Relationships
 Parent - Child Viewpoint
 Parent (Owner) record has one or more
children (Members)

– Customer has one checking account
– Checking account has many transactions

Each child (member) has only one parent
(owner)
Stephen C. Hayne
11
Example Hierarchy: Banking Database
Customer# | SS# | Address| ...
Checking Acct. # | Current Balance | Statement Date
Check# | Date | Payee | Amount
Deposit# | Date | Amount
Savings Acct. # | Current Balance | Statement Date
Stephen C. Hayne
12
Simple Network
Children may have more than one parent
 Example: Joint checking account

John Dough
Jayne Dough
Checking Acct. # 999
Stephen C. Hayne
13
Complex Network

Supports M:N relationships
Stephen C. Hayne
Students
Portfolios
Courses
Securities
14
Relational Model
Data organized into tables (relations)
 Tables have columns (attributes)
 Data stored in rows (tuples)
 Each row has a unique primary key
 Relationships included in the data

Stephen C. Hayne
15
Relational Tables and Data
EMPLOYEE(SS#, EmployeeName, EmployeeAddress, Department_ID)
DEPARTMENT(DEPARTMENT_ID, DepartmentName, Manager)
EMPLOYEE
SS#
EmployeeName
1223
John Dough
3214
Jayne Dough
7653
Jill Outley
Employee Address
Wall Street
Wall Street
Wellborn
DEPARTMENT
DEPARTMENT_ID
Acct
Fin
DepartmentName
Accounting
Finance
Stephen C. Hayne
Department_ID
Acct
Fin
Acct
Manager
Beane Kounter
Cash McCall
16
Metadata
“Data about the data.”
 Data dictionary - Data Definition Language

– For each table or record
» Table or record name
» Number of columns or Fields
» Primary key
– For each data item
» Date item name
» Data type (Numeric, date, text...)
» Size
Stephen C. Hayne
17
Metadata
Use DDL
 Description
of database
structure
 System
tables (e.g.,
tables and
columns)

Stephen C. Hayne
18
Indexes

Usually “inverted lists” for accessing data.
Department
Acct
Fin
Stephen C. Hayne
Employees
1223, 7653
3214
19
Indexes and other data structures
Improve performance for sorting and
searching
 But: overhead of updating
 Who has Info Systems as major?

Stephen C. Hayne
20
Application Metadata

Structure & format of application
components
–
–
–
–
Stephen C. Hayne
Forms
Reports
Queries
Etc.
21
Forms
Data entry
 Hiding IDs

Stephen C. Hayne
22
Queries
By example (QBE)
 By form
 By DML (Data Manipulation Language)

Stephen C. Hayne
23
Reports

Data display
Stephen C. Hayne
24
Menus

Organize application components
Stephen C. Hayne
25
Application programs
DMLs
 Standard
languages +
DBMS
interface
 Eg: trapping
events in
Visual Basic
+ Access

Stephen C. Hayne
26
DB
System
Architecture
Stephen C. Hayne
27
The Database Schema



Tables
Relationships
Domains
– constraints on values that a column may have.
– e.g. date, integer number, text of length N...
– ID’s (primary keys) must be unique

Business rules
– Rules or policies that must be enforced in the database.
» Each employee is always assigned to one department.
» Each department is always assigned one or more employees.
Stephen C. Hayne
28
Summary

Database Systems include
– the database consisting of
» Data, Metadata, Indexes, Application metadata
– the database management system (DBMS)
» Design Tools & Run Time Tools
Stephen C. Hayne
29