BTM 382 Database Management Chapter 1 Database systems

BTM 382 Database Management Chapter 1: Database systems Chitu Okoli Associate Professor in Business Technology Management John Molson School of Business, Concordia University, Montréal

Structure of BTM 382 Database Management § § § Week 1: Introduction and overview § ch 1: Introduction Weeks 2 -6: Database design § ch 3: Relational model § ch 4: ER modeling § ch 6: Normalization § ERD modeling exercise § ch 5: Advanced data modeling Week 7: Midterm exam Weeks 8 -10: Database programming § ch 7: Intro to SQL § ch 8: Advanced SQL § SQL exercises Weeks 11 -13: Database management § ch 2, 14: Data models § ch 13: Business intelligence and data warehousing § ch 9, 15, 16: Selected managerial topics

Review of Chapter 1: Database systems § Why do we need databases? § What are the different kinds of database management systems? That is, how could different DBMSs be grouped or classified? § What are the major aspects of database management?

Why do we need databases?

Structural and Data dependence and independence § Structural dependence: Access to a database file is dependent on the precise database structure § E. g. if you add a new field (attribute), you have to modify all application programs that access the database § Structural independence: File structure can be changed without affecting applications’ ability to access the data § Data dependence and independence § Data dependence: Access to a database file is dependent on the precise format of the data § E. g. if you change the number of decimal places for a numeric field, you have to modify all application programs that access that field § Data independence: Data format characteristics can be changed without affecting applications’ ability to access the data

Data redundancy § Unnecessarily repeating the same data at different places in the database § Problems: § Poor data security § Data inconsistency § Increased likelihood of data-entry errors when complex entries are made in different files § Data anomaly: when not all of the required changes in the redundant data are made successfully

Database and DBMS § A database is a shared, integrated computer structure that stores a collection of: § End-user data: Raw facts of interest to end users § Metadata: Data about data, which is used to integrate and manage the end-user data § Describe data characteristics and relationships § Database management system (DBMS) § Collection of programs § Manages the database structure § Controls access to data stored in the database

The DBMS manages the interaction between the end user and the database

Databases versus file systems

Types of databases

Types of databases: Number of users § Single-user database: Supports one user at a time § Desktop database: Runs on a PC § Multiuser database: Supports multiple users at the same time § Workgroup databases: Supports a small number of users or a specific department § Enterprise database: Supports many users across many departments

Types of databases: Physical location § Centralized database: Data is located at a single site § Distributed database: Data is distributed across different sites § Cloud database: A type of distributed database where the database developers never need to think about where the database is physically located, because someone else (the cloud service provider) takes care of all of that

Types of databases: Operational versus analytical § Operational/transactional database: Designed to support a company’s day-to-day operations § Analytical database: Stores past historical data and business metrics used exclusively for tactical or strategic decision making § Optimized for read-only operations; very slow for updating § Data warehouse: Stores data in a format optimized for decision support

Types of databases (correction/clarification): Information provision databases § All the other kinds of database described are fully functional databases where the users can read, write and edit data § Unlike the other kinds of database described, General-purpose databases and disciplinespecific databases refer to prepopulated readonly databases provided for information purposes by specific vendors

Database management

The database system environment 18

Structure of BTM 382 Database Management § § § Week 1: Introduction and overview Weeks 2 -6: Database design Week 7: Midterm exam Weeks 8 -10: Database programming Weeks 11 -13: Database management

Conclusion

Summary of Chapter 1: Database systems § We need databases to efficiently organize data without redundancies in a way independent from application programs. § DBMSs can be classified according to the number of users, physical location, and operational versus analytical purpose. § Database management mainly consists of design, programming and administration.

Sources § Most of the slides are adapted from Database Systems: Design, Implementation and Management by Carlos Coronel and Steven Morris. 11 th edition (2015) published by Cengage Learning. ISBN 13: 978 -1 -285 -19614 -5 § Other sources are noted on the slides themselves 22