Transcript Intro to Databases - Cedarville University

Database Organization and
Design
Chapter 1
Adapted from slides by Raghu Ramakrishnan,
[email protected]
What is a DBMS?
• A database is a (very) large, integrated collection
of data
• A database is based upon a data representation
(data model) of a real-world enterprise
– Objects (or Entities, e.g., students, courses)
– Relationships (e.g., Charlie Brown is in CS3610)
• A Database Management System (DBMS) is a
software package designed to store and manage
databases. E.g., UNIDATA, mysql, or Access
File Storage vs. a DBMS
• A DBMS can “stage” data (for large datasets) between main memory
and secondary sources (disk, CD, tape)
• A DBMS provides efficient (indexed) access
• A DBMS provides a built-in query language to facilitate data retrieval
– Permits free form queries
– Standard language provides standard communication for programs using
embedded queries
• A DBMS protects data from inconsistency by
– Enforcing referential integrity during data modification
– Enforcing data format constraints
– Providing controlled concurrent access
• A DBMS provides crash recovery
• A DBMS provide multi-level security and access control
• A DBMS allows for uniform administration of the data
Data Models
• A data model is the manner in which you describe some
data.
– The Entity Relationship Diagram (ERD) represents a conceptual
data model for describing objects and their relationships. The
ERD is the most common of the conceptual data models. It has
several variants.
• Main features of an ERD is entities, attributes, and relationships.
– The relational data model is a conceptual data model for
implementing a database. The relational model is easily the most
widely used model today.
• Main concept the relation: a table of records and fields
• Every relation has a defining schema
• A schema is the description of a particular collection of
data in a given data model.
Levels of Abstraction
View 1
View 2
View 3
Conceptual Schema
Physical Schema
Example: University Database
• External View:
– View1: course_info ( course_name, enrollment )
– View2: teacher_info ( course_name, teacher )
• Conceptual Schema
–
–
–
–
–
Students(sid, name, age, gpa)
Courses (cid, name, credits)
Teachers (tid, name, rank)
Enrolled (sid, cid, grade)
Teaches (tid, cid)
• Physical Schema
– Store relations as tables
– Index tables by id fields
Road Ahead
• Modeling Data with Entity Relationship Diagrams
• Using the Relational Model (i.e., Schema
Descriptions) to Model Data
• Relational Algebra: A concise language for
describing data operations
• Structured Query Language (SQL): A standard
language for manipulation of data by computers
• Database Apps: Learning how to access databases
from programs
• Database File Organization, Storage, and Indexing
Road Ahead (Continued)
• Hash Indexes
• Query Evaluation: Getting the resultant data for
the (somewhat) least cost
• Transaction Management: Keeping multiple users
from interfering with each other
• Schema Design and Normal Forms: Reducing
redundancy in the database
• Physical Database Design: Tuning your database
for performance