CSE 3241: Database Systems I Course Introduction

(1)

1

CSE 3241: Database Systems I

Course Introduction

(2)

Agenda

 Administrative Tasks

 Course Outline

 Course Requirements

2

(3)

Instructor

3

 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

Facebook/Bettina.Bair:

Do you play Candy Crush?

Send me “lives” and extra moves!

(4)

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

(5)

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

Intro. To DBMS 5

(6)

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

(7)

Course Resources

Carmen

 WS

Answer s

 Grades

 Dropbo x

Course Site

 Announcements

 Schedule

 Course material

◦ Including slides, assignments,

reading

Intro. To DBMS 7

sites.google.com/site/cse3241sp17

(8)

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

(9)

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

9

(10)

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

(11)

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.

Intro. To DBMS 11

(12)

Schedule of Topics

12

Midterm1:

Feb 7

^th

or 8

^th

(13)

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.

Intro. To DBMS 13

(14)

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

(15)

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).

Intro. To DBMS 15

(16)

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

(17)

DATABASE

MANAGEMENT SYSTEMS

(DBMS)

Introduction

Intro. To DBMS 17

(18)

Outline

 What is a database?

 The database approach

◦ Advantages

◦ Disadvantages

 Database users

 Database concepts and System architecture

18

(19)

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

19

(20)

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

²⁰

(21)

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 customer reviews

21

(22)

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

(23)

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

(24)

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

(25)

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)

25

(26)

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”

(27)

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

 Late 1990s, Internet and distributed DBs

Intro. To DBMS 27

(28)

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

(29)

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?

 Do you have more questions?

Intro. To DBMS 29

(30)

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

(31)

1984 – Criminal Intelligence DB

ID, name

ID, addres

s

ID, eye color

ID, tattoo

s

ID, crime ID,

assoc

ID, height

ID, weight

ID, hair

ID, notes

 Advantages to this design?

 Disadvantages?

 What would you do?

(32)

DATABASE APPROACH

Intro. To DBMS 32

(33)

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

(34)

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

(35)

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

35

(36)

Simplified Data Base System

Environment

(37)

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

(38)

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

(39)

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

39

(40)

Advantages of the database approach with DBMS

 Backups!

 Enforcing integrity constraints

◦ Data types

◦ Referential integrity

 Integrity across records in different tables

40

(41)

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

41

(42)