Database

Fundamentals DesignSystems
of Database
Lecture 1: Introduction

١
 Reference Books
Main Reference
Book

٢
 Other References

٣
 Database Design

Course Material:
Lectures summary,
handouts,
presentations,
home works,
references and
announcements
can be found at
the web site
specified below

٤ https://sites.google.com/site/akahmedsiu/
 Database Design

Course Topics:
1. ERD
2. Relational Data Model
3. SQL
4. Functional Dependency
5. Normalization

٥
 Database Design

Students Assessment:

Continuous assessment 20 %
(home works, quizzes, assignments)
Mid-semester Exam 30 %
End of semester exam 50 %
Total 100 %
٦
 What Is a Database System?

• Database:
a very large, integrated collection of data.
• Models a real-world enterprise
– Entities (e.g., teams, games)
– Relationships
(e.g., The Forty-Niners are playing in The Superbowl)
– More recently, also includes active components , often
called “business logic”. (e.g., the BCS ranking system)

• A Database Management System (DBMS) is a software

system designed to store, manage, and facilitate access
to databases.

7
Database Design
 Database Systems: Then

8
Database Design
 Database Systems: Today

9
From Friendster.com on-line tour
Database Design
 Other databases you may use

10
Database Design
 = Is the WWW a DBMS?
• Fairly sophisticated search available
– crawler indexes pages on the web
– Keyword-based search for pages
• But, currently
– data is mostly unstructured and untyped
– search only:
• can’t modify the data
• can’t get summaries, complex combinations of data
– few guarantees provided for freshness of data, consistency
across data items, fault tolerance, …
– Web sites typically have a DBMS in the background to provide
these functions.
• The picture is changing
– New standards e.g., XML, Semantic Web can help data
modeling
– Research groups (e.g., at Berkeley) are working on providing
some of this functionality across multiple web sites. 11
Database Design
 Current Commercial Outlook
• A major part of the software industry:
– Oracle, IBM, Microsoft, Sybase
– also Informix (now IBM), Teradata
– smaller players: java-based dbms, devices, OO, …
• Well-known benchmarks (esp. TPC)
• Lots of related industries
– data warehouse, document management, storage, backup,
reporting, business intelligence, app integration
• Relational products dominant and evolving
– adapting for extensibility (user-defined types), adding native
XML support.
• Open Source coming on strong
– MySQL, PostgreSQL, BerkeleyDB
12
Database Design
 ?
Why Study Databases??
• Shift from computation to information
– always true for corporate computing
– Web made this point for personal computing
– more and more true for scientific computing
• Need for DBMS has exploded in the last years
– Corporate: retail swipe/clickstreams, “customer relationship
mgmt”, “supply chain mgmt”, “data warehouses”, etc.
– Scientific: digital libraries, Human Genome project, NASA
Mission to Planet Earth, physical sensors, grid physics
network
• DBMS encompasses much of CS in a practical discipline
– OS, languages, theory, AI, multimedia, logic
– Yet traditional focus on real-world apps

13
Database Design
 Describing Data: Data Models
• A data model is a collection of concepts for
describing data.

• A schema is a description of a particular

collection of data, using a given data model.

• The relational model of data is the most widely

used model today.
– Main concept: relation, basically a table with rows
and columns.
– Every relation has a schema, which describes the
columns, or fields.

14
Database Design
 Levels of Abstraction
Users

• Views describe how users

see the data.

• Conceptual schema View 1 View 2 View 3

defines logical structure
Conceptual Schema

Physical Schema
• Physical schema describes
the files and indexes used.
DB
• (sometimes called the
ANSI/SPARC model)
15
Database Design
 Example: University Database
View 1 View 2 View 3
• Conceptual schema:
– Students(sid: string, name: string,
Conceptual Schema
login: string, age: integer, gpa:real)
– Courses(cid: string, cname:string, Physical Schema
credits:integer)
– Enrolled(sid:string, cid:string,
grade:string) DB
• External Schema (View):
– Course_info(cid:string,enrollment:integer)
• Physical schema:
– Relations stored as unordered files.
– Index on first column of Students.
16
Database Design
Databases make these folks happy ...
• DBMS vendors, programmers
– Oracle, IBM, MS, Sybase, …
• End users in many fields
– Business, education, science, …
• DB application programmers
– Build enterprise applications on top of DBMSs
– Build web services that run off DBMSs
• Database administrators (DBAs)
– Design logical/physical schemas
– Handle security and authorization
– Data availability, crash recovery
– Database tuning as needs evolve

17
Database Design