CSE 3241: Database Systems I
Course Introduction
Agenda
⚫
Administrative Tasks
⚫
Course Outline
⚫
Course Requirements
2
Instructor
⚫ Bettina Bair ([email protected])
⚫ Office: Dreese Labs 493
⚫ Office Hours: Tu/Th 1:15, We/Fr 2:10
◦ or by appointment
⚫ Phone: 292-2565 msgs only
CSE 3241 Introduction to Database Systems
Database systems use, logical design,
entity-relationship model, normalization,
query languages and SQL, relational algebra and calculus, object relational databases,
XML, active databases; database design project.
A basic knowledge of programming languages, files, and the application development process is assumed.
Prerequisites
Prereq: 2133 or 2231 or 2233 (314) or 321, and 2321 or Math 2366 (366).
Intro. To DBMS 4
Where are you?
⚫
CSE 3241/5241 Introduction to DBMS
◦
Relational Data Base Systems
⚫
CSE 5242 Advanced DBMS
◦
Transactions, query processing
◦
Performance, advanced indexing
◦
Hierarchical DB
◦
Network DB
Course Goals
⚫
You should leave this class with:
◦
An understanding of relational databases
● Writing queries in relational languages (SQL, relational algebra)
◦
An understanding of database design
● Conceptual and logical design
● A brief bit of physical design
◦
An understanding of what data independence means and how to accomplish it
6
Course Resources
Carmen
⚫ WS
Answers
⚫ Grades
⚫ Dropbox Course Site
⚫ Announcements
⚫ Schedule
⚫ Course material
◦ Including slides,
assignments, reading
sites.google.com/site/cse3241sp17
Textbook
⚫
Fundamentals of Database Systems,
◦
Sixth Edition, by R. Elmasri and S.B. Navathe.
◦
ISBN-10: 0136086209, ISBN-13:
9780136086208
⚫
Reading assigned daily
◦
Study questions from book may be used as the basis for exam questions.
8
downloadable as a PDF
Topics Covered
⚫ Intro /Databases - ch 1, 2
⚫ ER Model - ch 7, 8
⚫ EER Model - ch 9
⚫ Relational Model - ch 3
⚫ Relational Algebra - ch 6
⚫ Relational Calculus
⚫ Basic SQL - ch 4
⚫ Advanced SQL - ch 5
⚫ Func Dependency -ch 15
⚫ Normalization - ch 16
⚫ Indexing - ch 18
⚫ Embedded SQL - ch 13
⚫ Transactions – ch 21
⚫ OODB - ch 11
⚫ XML - ch 12
Grading Basis
Intro. To DBMS 10
with partner In-class activities (15* @ 2 pts) 30
Project** 20
on your own Midterm 20
Final *** 30
* 15 highest scores out of many ** Peer evaluation will weight each member’s contribution *** Must pass final to pass course
Group Work
⚫ In Class Activities
◦ Groups of 2+
◦ Practice and understand lecture topics
◦ Handed out and collected each class
⚫ Project
◦ Groups of 4
◦ Application of concepts
◦ Submitted at end of quarter
◦ Periodic checkpoints for feedback.
Schedule of Topics
12
Midterm1:
Feb 7th or 8th
Missing Work
⚫
No makeups for in-class activities.
◦
Best 15 are used.
◦
Answers will be posted on Carmen
⚫
If you are going to miss an exam then you must inform me ASAP.
⚫
In the case of an emergency, contact me (or the CSE department), and collect
verifiable documentation.
Academic Misconduct
⚫
In accordance with Faculty Rule
3335-5-487, all instances of alleged
academic misconduct will be reported to the department chairperson and the
Committee on Academic Misconduct.
⚫
Instructors and graders will not make an evaluation of the facts of the case, either towards guilt or innocence.
⚫
For more information see:
http://oaa.osu.edu/coam.html.
Intro. To DBMS 14
Accommodations for Disabilities
⚫
Students with disabilities are encouraged to visit and register with the Office for Disability Services (ODS) as soon as possible
⚫
(see http://www.ods.ohio-state.edu).
Attendance & Waitlist
⚫ We are trying to accommodate as many students as resources permit, and we apologize that not everyone will be able to enroll in all classes they would like to take this term.
⚫ Anyone who is thinking of dropping the course should do so before the end of the day on Friday in order to avoid possible Late Drop charges.
⚫ Anyone who is on the waiting list should continue to attend class until decisions are made early in the second week.
◦ No Late Add charges will be assessed for courses added by the end of the second week.
Intro. To DBMS 16
DATABASE
MANAGEMENT SYSTEMS (DBMS)
Introduction
Outline
⚫
What is a database?
⚫
The database approach
◦
Advantages
◦
Disadvantages
⚫
Database users
⚫
Database concepts and System architecture
18
What is a database?
⚫
A collection of related data
◦
..but not just a random collection of data
● The “My Documents” folder on your hard drive isn’t a database, even though the data in it might all be “related”
● Though you could turn your “My Documents”
folder into a database with a bit of work
What is a database?
⚫
A collection of logically coherent data
with some inherent meaning designed and built for some specific purpose
⚫ Logically coherent
◦ Pieces of data are connected to each other in a rational, logical manner
⚫ Inherent meaning
◦ The data as a whole can be described (e.g. “a collection of customers”)
⚫ Specific purpose
◦ The data was organized with a particular audience in mind
20
For Example…
⚫
Simple : A telephone book
◦
A collection of names and associated telephone numbers
⚫
Complex : Library card catalog
◦
A collection of information on the various books that the library owns
⚫
Complex : Netflix catalog
◦
A collection of information on the movie
selection offered by Netflix, including
Databases don’t have to be electronic
⚫
Clay tablets recording customer transactions
⚫
“Old-school” library card catalogs (on actual cards)
⚫
Grandma’s recipe box
⚫
Sales Contact Rolodex
⚫
… all of these are databases
22
Databases are everywhere!
⚫
Traditional database applications
◦
Store textual or numeric information
⚫
Multimedia databases
◦
Store images, audio clips, and video streams digitally
⚫
Geographic information systems (GIS)
◦
Store and analyze maps, weather data, and
satellite images
Database software
⚫
Computerized databases have come to dominate the idea of databases
◦
Computers allow:
● Better organization
● Better ability to search
● Ability to minimize redundancies in your data
● Ability to put your database on the web
● … and many other benefits
24
Database software
⚫
When we talk about database software, we mean Database Management
Systems (DBMS)
◦
DMBS theory and practice will be the focus of this course
◦
More specifically Relational DBMS (RDBMS)
Terms
◦
Data Base Management System (DBMS)
◦ Collection of programs
◦ Enables users to create and maintain a database
◦ Example of a large commercial database,
Amazon.com
From the book: “a general purpose software system that enables the defining, constructing,
manipulating and sharing of databases”
DBMS History
⚫ Early1960s
⚫ Integrated Data Store, first general-purpose DBMS, Charles Bachman, General Electrics
⚫ Late 1960s
⚫ Information Management System (IMS), IBM
⚫ 1970
⚫ Relational database model proposed, Edgar Codd, IBM’s San Jose Laboratory
⚫ Late 1980s
⚫ Structured Query Language (SQL), standardized
⚫ Late 1980s, early 1990s
⚫ More powerful query languages, complex analysis of data, support for new data types (e.g., images)
⚫ Packages which come with powerful customizable application layers, i.e. Access, FoxPro, DBII
1984 – Criminal Intelligence DB
⚫
Data input
◦
Arrest records from AZ, NV, NM, CO
⚫
Data and reports output
◦
Organized crime groups
◦
Suspects according to descriptions
Intro. To DBMS 28
Design a Criminal Database
⚫
Who are the users?
◦
Skilled?
◦
Executive? First level?
⚫
How do they use the system?
◦
At the same time?
◦
Reports? Same reports?
◦
Inserting and changing records?
◦
Are all new records complete?
● Does every arrest report contain the same info?
What are the steps to design?
⚫
Collect requirements
◦
Who are the users?
◦
How do they use the data?
◦
Constraints?
◦
Tasks?
⚫
Model requirements and validate
⚫
Design from Model
⚫
Implement from Design
⚫
Optimize / additional features
Intro. To DBMS 30
1984 – Criminal Intelligence DB
ID, name
ID, address
ID, eye color
ID, tattoos
ID, crime ID,
assoc
ID, height
ID,
weight ID, hair ID, notes
⚫
Advantages to this design?
⚫
Disadvantages?
⚫
What would you do?
DATABASE APPROACH
Intro. To DBMS 32
The Database Approach
⚫
Traditional file processing
◦
Each user defines and implements the files needed for a specific software application
⚫
Database approach
◦
Single repository maintains data that is
defined once and then accessed by various
users
Database Management System (DBMS)
⚫
Defining
◦ Specifying what should be in the database
◦ Meta-data: description of what the data in the database means
⚫
Constructing
◦ Storing data in the database
◦ Handling file creation and organization on the disk
34
Database Management System (DBMS)
⚫
Manipulating
◦ Querying and updating the database
● Query: retrieving specific data from the database
● Update: change the contents of the database
⚫
Sharing
◦ Allowing users to access the database
Simplified Data Base System
Environment
DBMS Languages
⚫
Data definition language (DDL)
•
Defines internal and conceptual schemas
⚫
Storage definition language (SDL)
• Specifies the internal schema
⚫
View definition language (VDL)
• Specifies user views/mappings to conceptual schema
⚫
Data manipulation language (DML)
•
Allows retrieval, insertion, deletion,
modification
Characteristics of the Database Approach
A. Self-describing nature of a database system
B. Insulation between programs and data, and data abstraction
C. Support of multiple views of the data D. Sharing of data and multiuser
transaction processing
Advantages of the database approach with DBMS
⚫
Controlling redundancy
◦
Design databases to store each logical item only once
⚫
Controlling access
⚫
Efficient query and update processing
◦
Everything in one place, rather than
scattered around
Advantages of the database approach with DBMS
⚫
Backups!
⚫
Enforcing integrity constraints
◦
Data types
◦
Referential integrity
● Integrity across records in different tables
40
Advantages of the database approach with DBMS
⚫
Enforcing standards
⚫
Reduced application development time
◦
Most of your development work is already done once you’ve built the database
⚫
Flexibility
◦
Can change the database without changing every single application
⚫
Instant updates to information
When Not to Use a DBMS
⚫
Overhead!
◦
High initial monetary investment in hardware, software
◦
Security, referential integrity, concurrency control and other features don’t come for free
● Higher hardware costs than flat files
42
When Not to Use a DBMS
⚫
single-user database?
⚫
Limited data manipulation, cross referencing?
⚫
Speed is important
⚫
Requirements not expected to change at all
⚫
Limited storage capacity
Any other examples?
What to do before next class
⚫
Find the class website
◦ https://sites.google.com/site/cse3241sp17/
◦
Find project, slides, worksheets, etc
⚫
Find textbook (read ch 1, 2)
⚫
Pick a group for the project
⚫
Next few weeks…
◦
Find / install / explore SQLite extension Firefox
Intro. To DBMS 44
