A DBMS EXAMPLE SECTION 2 A broad overview

A DBMS EXAMPLE SECTION 2 A broad overview of a simple DBMS

The Database • Main components Design Tools 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 Stedent. Name Student. Phone Advisor. Name Advisor. Phone 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 Stedent. Name Student. Phone Advisor. Name Baker, Rex 232 -8897 Parks Advisor. Name Advisor. Phone Charles, Mary 232 -0099 Parks 236 -0098 Johnson, Beth 232 -4487 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 Table. Name Number of Columns Primary Key Student 4 Student. Number Advisor 3 Advisor. Name Course 3 Course. Number Enrollment 3 {Student. Number, Course. Number} Sys. Tables Table Column Name Table Name Data Type Length Student. Number Student Integer 4 First. Name Student Text 20 Last. Name Student Text 30 Major Student Text 10 Advisor. Name Advisor Text 25 Phone Advisor Text 12 Department Advisor Text 15 Course. Number Course Integer 4 Title Course Text 10 Number. Hours Course Decimal 4 Student. Number Enrollment Integer 4 Enrollment. Number Enrollment Integer 4 Grade Enrollment Text 2 Sys. Columns Table

Indexes • Also called “overhead data” STUDENT DATA TABLE Student. Number First. Name Last. Namne 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 Last. Name Student. Number Abernathy 200 Baker 100 Major Student. Number 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, Captain. Name, Phone, Street, City, State, Zip) – ITEM (Item_Id, Quantity, Description, Date. Out, Date. In • Relationships: – CAPTAIN (Captain_Id, Captain. Name, Phone, Street, City, State, Zip) – ITEM (Item_Id, Quantity, Description, Date. Out, Date. In, 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