Dbm S2018 L1 PDF

Preview

Summary

Dbm S2018 L1...

Description

Agenda Overview of the class – Content of the course

Introduction to Database Management 95-703

– Class Material – Assignments – Grading

Brief Introduction to Database Systems

Spring 2018 Janusz Szczypula

95-703, DBM

Adelaide campus

Overview This is an introductory class – Core requirement for MISM/MSIT, and “Data

Course Content Introduction to Relational Database Model Entity Relationship Modeling and Diagramming

Analytics” students

Normalization Other “Database” classes offered by Heinz College

Mapping ER models into Relational Tables

– Advanced Relational Database Management – NoSQL Database Management

Structured Query Language (SQL)

– Data Warehousing 95-703, DBM

95-703, DBM

Course Content – SQL Database Tables and Data Integrity Data Manipulation (Insert, Update, Delete) Single and multiple table queries (Select) Single row functions Multiple row functions & Grouping data Subqueries Regular Expressions Functions Analytic SQL Using Metadata

Class Material Textbook: – Casteel, J., “Oracle 11g: SQL,” Course Technology, 2014

Class lectures/notes: – Available on class website at www.cmu.edu/canvas

Occasional handouts

95-703, DBM

95-703, DBM

Recommended books

Grading

 Connolly, T. and C. Begg, “Database Systems: A Practical Approach to Design, Implementation, and Management,” 6th edition, Addison-Wesley, 2015

Homework assignments SQL assignments

 Coronel, C., S. Morris, and P. Rob, “Database Systems: Design, Implementation, & Management,” 10th edition, Course Technologies, 2013

Database Implementation Project

 Hoffer, J. A., R. Venkataraman, and Heikki Topi, “Modern Database Management,” 11th edition, Prentice Hall, 2012

Closed books/notes Exam

 Price, J., “Oracle Database 11g: SQL,” Mc Graw Hill, 2008 95-703, DBM

95-703, DBM

Grading (cont.)

Grading (cont.)

All assignments are due at the beginning of class on the day specified (before lecture starts) – No late assignments will be accepted unless permission is granted before deadline – Each assignment must be typed and diagrams created using appropriate tool

Project is due at the beginning of the last class Assignments must reflect individual effort: – No collaboration in any form on assignments is allowed – Sharing your assignment with any other student in any form is not permitted

Example Project Exam

Maxpoints:

35

82

49

36

202

Maxpoints:

150

154

Pointsreceived:

32

53

34

30

149

Pointsreceived:

137

136

73.8

PercentPoints:

91.3

88.3

SQL1

SQL2

SQL3

50

43

57

Pointsreceived:

44

37

54

Total 150 135 90.0

20% 15% 15% 50%

 The letter grade structure: 97% – 100%: A+ 93% – 96.9%: A 89% – 92.9%: A – 85% – 88.9%: B+ 81% – 84.9%: B 77% – 80.9%: B – 73% – 76.9%: C+ 69% – 72.9%: C 65% – 68.9%: C – Below 65%: R

Electronic devises in-class

HW1 HW2 HW3 HW4 Total

Maxpoints:

 Homework assignments  SQL assignments  Project  Exam

HW: 73.8 * 0.20 = SQL: 90.0 * 0.15 = Project: 91.3 * 0.15 = Exam: 88.3 * 0.50 = Overall total:

14.76% 13.50% 13.70% 44.15% 86.11%

Final Grade is: B+

All laptops, cell phones or smart phones, or any other electronic devises must be turned off and put away during class time No student may record or tape any classroom activity without the express written consent of the instructor Only one time you need to use a laptop in class – the Oracle 11g Express Lab 95-703, DBM

Outline Basic Concepts: – Data – Metadata – Information

Introduction to Database Systems

File Systems Database Systems Relational Data Model 95-703, DBM

Data Data – representation of facts as symbols, text, numbers, graphs, images, sound, or video – Captured and stored without context or relation to other data – Facts that we consider worth collecting, summarizing, analyzing, and interpreting

What does it represent? 27

JAN

010110

Examples: – 27, JAN, 010110 95-703, DBM

Without additional details, we know nothing about any of these pieces of data.

Metadata Metadata describes data in terms of type, length, format, timeframe, and other useful characteristics Metadata answers not only the question what but also should address other questions for users of the data: the who, when, where, why, and how questions

Information is data in a context The lack of context does not make the data anything more than “facts” Metadata provides the context to the data and turns data into information – it adds meaning to the data

95-703, DBM

95-703, DBM

Understanding Information

File Systems

Unit of Analysis – a person, a thing, a transaction, or an event that can be uniquely identified and about which we want to collect data – – – –

Information

Person (student, customer, …) Transaction (sale, monthly rent, …) Thing (education, …) Event (election, …)

Level of measurement or granularity (degree of aggregation or the level of details): – Location (city, state, region, country, …) – Time period (week, month, quarter, year, ...) – Category (internet store vs. physical store), etc.

Manual filing systems Computer file systems – a pre-database system that worked with individual and separate files – Easily sorted – Generating various reports was possible and easy

Terminology: – Data Elements: raw facts – Field: named datum – Record: logically connected fields that describe an entity (person, place, event, or a thing) – File: collection of related records 95-703, DBM

File System Example

File System Example (cont.) Customer file:

Personnel dept.

Sales dept.

File Management Programs

File Report Programs

File Management Programs

EMPLOYEES file

CUSTOMER file

File Management Programs

File Report Programs

File Report Programs

C_Name Sally Adams Ann Samuels Don Charles Tom Daniels Al Williams Sally Adams Mary Nelson Tran Dinh Mara Galvez Dan Martin

C_Phone C_Address S_Name S_Phone 346-7985 481 Oak; Lansing, MI, 49224 Mary Jones 123-5687 292-3465 215 Pete; Grant, MI, 49219 William Smith 459-1523 398-5455 48 College; Ira, MI, 49034 Miguel Diaz 612-4578 446-7235 914 Cherry; Kent, MI, 48391 William Smith 459-1523 634-1285 519 Watson; Grant, MI, 49219 Miguel Diaz 612-4578 123-7345 16 Elm; Lansing, MI, 49224 Mary Jones 123-5687 324-1785 108 Pine; Ada, MI, 49441 Miguel Diaz 612-4578 746-1265 808 Ridge; Harper, MI, 48421 William Smith 459-1523 124-7491 512 Pine; Ada, MI, 49441 William Smith 459-1523 986-3457 419 Chip; Grant, MI, 49219 Mary Jones 123-5687

Balance 818.75 21.50 825.75 770.75 402.75 1817.50 98.75 402.40 114.60 1045.75

Sales_Rep file: S_Name Mary Jones William Smith Miguel Diaz

SALES file

S_Phone 123-5687 459-1532 612-4578

S_Address Hire_Date 123 Main; Grant, MI, 49219 03/15/96 102 Raymond; Ada, MI, 49441 12/01/97 419 Harper; Lansing, MI, 49224 11/21/95

95-703, DBM

File Systems (cont.) Issues: – Data redundancy – repetition of data found in multiple files. It leads to data anomalies (i.e., actions that lead to inconsistent data) – Structural dependence – a change in any file’s structure requires the modification of all programs using that file – Data dependence – a change in any file’s data characteristics requires changes of all data access programs

Solution: integrated data management – the storage of all data in a single database 95-703, DBM

Database Systems Database is a shared and integrated computer structure housing: – End user data – Metadata

Database Management System (DBMS) – software that enables users to define, create, and maintain a database and provides controlled access to the database 95-703, DBM

Database Systems (cont.)

93-703, DBM Based on: Coronel & Morris “Database Systems”

File System vs. Database Systems

25

93-703, DBM Based on: Coronel & Morris “Database Systems”

Database Approach Makes data management more efficient Provides better access to better-managed data Promotes an integrated view of organization’s operations Improves data consistency & data sharing Query language allows quick answers to ad hoc queries 95-703, DBM

Data Models A data model is a collection of logical constructs used to represent data structure and relationships within the database – Conceptual models: represent logical nature of data – Implementation models: emphasis on how the data are represented in the database

95-703, DBM

26

Data Models (cont.) Relationships in Data Models – One-to-one (1:1) – One-to-many (1:M) – Many-to-many (M:N)

Entity Relationship Model Based on entities, attributes, and relationships Represented by an entity relationship diagram (ERD)

Implementation Models – – – –

PROFESSOR

1

teaches

M

CLASS

Hierarchical Network Relational Other… 95-703, DBM

95-703, DBM

Entity Relationship Model (cont.) Advantages – Conceptual simplicity – Visual representation – Effective communication tool

Disadvantages – Limited constraint representation – No data manipulation language 95-703, DBM

Relational Model Perceived by users as a collection of tables Tables are related by sharing common attribute(s) Customer: Cus_No 124 256 311 315 405 412 522 567 587 622

Last Adams Samuels Charles Daniels Williams Adams Nelson Dinh Galvez Martin

First Sally Ann Don Tom Al Sally Mary Tran Mara Dan

Street 481 Oak 215 Pete 48 College 914 Cherry 519 Watson 16 Elm 108 Pine 808 Ridge 512 Pine 419 Chip

City State Zip_Code Phone Balance Slsrep_No Lansing MI 49224 818.75 03 346-7985 Grant MI 49219 21.50 06 292-3465 Ira MI 49034 825.75 12 398-5455 Kent MI 48391 770.75 06 446-7235 Grant MI 49219 402.75 12 634-1285 Lansing MI 49224 03 123-7345 1817.50 Ada MI 49441 98.75 12 324-1785 Harper MI 48421 402.40 06 746-1265 Ada MI 49441 114.60 06 124-7491 Grant MI 49219 986-3457 1045.75

Sales_Rep: Slsrep_No 03 06 12

Last

First

Jones Mary Smith William Diaz Miguel

Street

City

123 Main 102 Raymond 419 Harper

Grant Ada Lansing

State Zip_Code MI MI MI

49219 49441 49224

Phone 123-5687 459-1532 612-4578

Hire_Date 03/15/96 12/01/97 11/21/95

Relational Model (cont.) Advantages – Easier database design, implementation, management, and use – Data & Structural Independence – Powerful ad hoc query capability (SQL) – Multiple platforms and vendors exist

Disadvantages – Substantial hardware and system software overhead – Poor design and implementation is possible and easy – May promote “islands of information” problems 95-703, DBM...