Transcript DBMS Example - Brock University

A DBMS EXAMPLE
SECTION 2
A broad overview of a simple DBMS
The Database
• Main components
Design Tools
Database
Database Contains
User’s Data
Metadata
Indexes
Application Metadata
D
B
M
S
E
n
g
i
n
e
Table Creation Tool
Developer
Form Creation Tool
Query Creation Tool
Report Creation Tool
Procedural Language
Compiler
Application
Programs
Run Time
Users
Form Processor
Query Processor
Report Writer
Procedural Language
Run Time
Application
Programs
User Data
• Today most databases represent user data as relations
StedentName
StudentPhone
AdvisorName
AdvisorPhone
Baker, Rex
232-8897
Parks
236-0098
Charles, Mary
232-0099
Parks
236-0098
Johnson, Beth
232-4487
Jones
236-0110
Scot, Glen
232-4444
Parks
236-0098
Zylog, Frita
232-5588
Jones
236-0110
StedentName
StudentPhone
AdvisorName
Baker, Rex
232-8897
Parks
AdvisorName
AdvisorPhone
Charles, Mary
232-0099
Parks
Parks
236-0098
Johnson, Beth
232-4487
Jones
Jones
236-0110
Scot, Glen
232-4444
Parks
Zylog, Frita
232-5588
Jones
Metadata
• A database is self-describing, which means that it
contains a description of its structure as part of itself
TableName
Number of Columns
Primary Key
Student
4
StudentNumber
Advisor
3
AdvisorName
Course
3
CourseNumber
Enrollment
3
{StudentNumber,CourseNumber}
SysTables Table
Column Name
Table Name
Data Type
Length
StudentNumber
Student
Integer
4
FirstName
Student
Text
20
LastName
Student
Text
30
Major
Student
Text
10
AdvisorName
Advisor
Text
25
Phone
Advisor
Text
12
Department
Advisor
Text
15
CourseNumber
Course
Integer
4
Title
Course
Text
10
NumberHours
Course
Decimal
4
StudentNumber
Enrollment
Integer
4
EnrollmentNumber
Enrollment
Integer
4
Grade
Enrollment
Text
2
SysColumns Table
Indexes
• Also called “overhead data”
STUDENT DATA TABLE
StudentNumber
FirstName
LastNamne
Major
100
James
Baker
Accounting
200
Mary
Abernathy
Information Systems
300
Beth
Jackson
Accounting
400
Eldridge
Johnson
Marketing
500
Chris
Tifte
Accounting
600
John
Smathers
Information Systems
700
Michael
Johnson
Accounting
LASTNAME INDEX
LastName
StudentNumber
Abernathy
200
Baker
100
Major
StudentNumber
Jackson
300
Accounting
100, 300, 500, 700
Johnson
400, 700
Information Systems
200, 600
Smathers
600
Marketing
400
Tufte
500
MAJOR INDEX
The DBMS
• DBMS products vary greatly
• First products in late 1960’s
• Continually enhanced
• Microsoft Access
Design Tools Subsystem
• Facilitates design and creation of the DB
and its applications
• Also provides programming languages
Run-Time Subsystem
• Processes the applications
• Also provides application program interface
The DBMS Engine
• Intermediary between the design tools , the
run-time subsystems, and the data
• Also involved with transaction management
Creating the Database
• Database schema
• The foundation
A Schema Example
• An example of a University sports facility
• Tables:
– CAPTAIN (Captain_Id, CaptainName, Phone, Street, City, State, Zip)
– ITEM (Item_Id, Quantity, Description, DateOut, DateIn
• Relationships:
– CAPTAIN (Captain_Id, CaptainName, Phone, Street, City, State, Zip)
– ITEM (Item_Id, Quantity, Description, DateOut, DateIn, Captain_Id)
Schema: domains
• A set of values that a column (attribute)
may have
– Length
– Type of field
Schema: rules
• Restrictions on activities that need to be
reflected in the data base and its
applications
– To checkout equipment a captain must have
– No captain may have more than
Creating Tables
• Once the schema is defined
• Use the database creation tool
Defining Relationships
• Relationship between CAPTAIN and
ITEM?
• Foreign key
• Key advantage of a relationship
Components of Applications
• Consists of :
– Forms
– Queries
– Reports
– Menus
– Application Programs
Forms
• Forms allow data entry
– data sheet view
– data entry form
Data Sheet Entry
Captain Registration Form
Data Entry Form – with form-in-form feature
Creating a Form
Queries
• Query data to identify problems or answer
questions
• SQL
• Query by example
Query By Example
Result of the Query
Reports
• A formatted display of database data
• Banded report writer
Application Programs
• Can be written in a language specific to the
DBMS or a standard language that
interfaces with the DBMS
Visual Basic Code to Enforce a Rule