BIT 4514 Database Technology for Business Fall 2019

BIT 4514: Database Technology for Business Fall 2019 Lecture 1: Intro to Databases

Developer’s conceptual model of a DSS Knowledge management (KBMS) Model management (MBMS) Data management (DBMS) Dialog management (GUI) Data: External and Internal Database component System user 2

DSS model: managing data • Database: "a collection of interrelated data organized in such a way that it corresponds to the needs and structure of an organization and can be used by more than one person for more than one application. " – i. e. : interrelated files with shared data. • DBMS (database management system): enables storage of data in the database, retrieval of data from the database, and control of the database. 3

Data vs. information Data - raw facts that can be recorded and preserved - Data management focuses on the proper generation, storage, and retrieval of data and is a core activity of any organization. Information - result of processing raw data to reveal its meaning in a given context - Accurate, relevant, and timely information is the key to good decision making Supports analytic techniques / can provide a competitive advantage 4

Data vs. information ID Name Address City 1 Andrew David SW 16 th Ave. Orlando FL 35691 2 Robert Brown 23 rd Terrace Boston MA 08902 3 Bob Thomas 52 nd Avenue Bronx NY 03261 4 Lee Hung 2 nd Street Albany NY 03562 5 Richard Gray 999 Palm Bay Detroit MI 56986 6 Tony Stevens 15 th Avenue Austin TX 68271 (a) Data State Zip Pie chart by State (b) Summarized Data (Information) Data can often be processed in a variety of ways to generate information 5

History: traditional file-based systems • Computerized manual filing systems – Individual files for each purpose – Each business unit had its own file system – Organized to facilitate the expected use of the data – Data processing specialist required to retrieve data and run reports 6

Example: file-based systems • Suppose, as a VT student, that you need to do the following things: 1. Register for courses 2. Pay tuition 3. Work part-time on campus 7

Example: file-based systems Tuition payment entry and reports File handling routines File definition Tuition payment application programs Course registration entry and reports Payment file(s) File handling routines File definition Course registration application programs Work-study data entry and reports Tuition. Rec (ID, name, address, amt. Paid, balance, …) Reg. Rec (ID, name, address, courses, …) Registration file(s) File handling routines File definition Work-study application programs Emp. Rec (ID, name, address, wage, …) Work-study file(s) 8

Issues with file-based systems • Data redundancy – Different files contain same information (ID, name, address, etc…) • Isolation of data in separate systems • Data inconsistency (e. g. , address only changed in one file) – Lack of data integrity • Data anomalies – All changes may not be made successfully • Update / Insertion / Deletion anomalies • Program-Data dependence – Definition of data is coded into application programs 9

Better approach: database systems Defined around: • DBMS (database management system): – Collection of programs that manages the database structure and controls access to the data (security) – Provides access to the data via a query language (e. g. , SQL) – Possible to share data among multiple applications or users – Makes data management more efficient and effective – End users have access to more, and better-managed, data • Possible to produce quick answers to ad hoc queries • Probability of data inconsistency is greatly reduced – Promotes integrated view of an organization’s operations 10

Example: database system Tuition payment entry and reports Tuition payment application programs Course registration entry and reports Course registration application programs DBMS Database Work-study data entry and reports Work-study application programs 11

Advantages of database systems • • Minimal data redundancy Data consistency Integration of data Improved data sharing Enforcement of standards Ease of application development Uniform security, privacy, and integrity Data independence from applications – "self-describing" data stored in a data dictionary (metadata) 12

The database system environment Components of a database system: • • • Hardware Software People Procedures Data** 13

Database system: software • Operating system software • Application programs and utility software • DBMS software – Examples: • Microsoft Access • Microsoft SQL Server • Oracle • My. SQL – Systems that are easier to learn are often more limited 14

Database system: people • System administrators – Oversee general operations of the entire system • Database administrators (DBAs) – Manage the DBMS • Database designers – Design the database structure and perform data modeling • System analysts and programmers – Design and implement application programs to use the data • End users 15

Types of databases • Can be classified according to: – Number of users – Database location(s) – Type of use • Number of users: – Single-user database – Multiuser database • Workgroup database • Enterprise database 16

Types of databases (cont. ) • Database location(s): – Centralized database – Distributed database • Type of use: – Operational database (Transactional or Production DB) – Data warehouse 17

Improving data management • File-based systems often imply "flat" data files: (think about managing data in Excel) 18

Improving data management: Types of database models • Hierarchical database model – Tree-based approach developed in 1960's • Based on parent-child relationships (1: M) – Each record is linked to all its child records – Ex: customer → order(s) → item(s) • Network database model – Created to improve on hierarchical model • Allows records to have more than one parent • Can access data from multiple points • Relational database model 19

Relational database model • Edgar F. Codd (Turing Award Winner)’s landmark paper – "A Relational Model of Data for Large Shared Data Banks" (1970) laid out a new way to organize and access data: the Relational Model. Customer(Customer. ID, Name, … Order(Order. ID, Customer. ID, Order. Date, … Items. Ordered(Order. ID, Item. ID, Quantity, … Items(Item. ID, Description, Price, … 20

Relational database model • Basis for most approaches used today for storing data • Basic Terminology: • Table = "relation" = matrix of intersecting rows and columns • Entity = object/concept about which data is collected and stored • Entity instance (or occurrence) = row in a relational table • Attributes = characteristics of an entity (columns) • Relationships = connections among data • Used to connect tables • Questions / queries do not need to be known at design time • Structured Query Language (SQL) used for "what" without "how" 21

Example: relational database Customer table Order. Item table 22

Features of a relational database application • The end-user interface – Allows user to interact with the DB, typically without writing code • The collection of tables stored in the database • The SQL engine – Executes all queries or data requests – SQL is a declarative language 23

Summary • How do we best manage data to enable it to be turned into information? – Traditional file systems are improved by database systems • Many advantages… • We will be working in the database system environment – Focus on software component – Further focus on relational databases • Design • Usage • Management 24

Next Steps • Short Term – EER diagrams & normalizing database tables – SQL queries to access the data in an effort to answer business questions • Long Term – Input forms and output reports – Integrity and Transactions – Data mining / Data warehousing 25