IST 220 – Intro to Databases

1. IST 220 – Intro to Databases Lecture 1 An Overview

2. Course Objectives • In this course, we will learn • Fundamentals of (relational) databases • Analyzing data needs and coming up with good DB design • Developing simple DB applications using Access • Basics of SQL using Access and Oracle

3. Textbook • Succeeding in Business with Microsoft Office Access 2003 • Bast et al • Course Technology • Features • Emphasize on solving real problems • Demonstrating database design and implementation in a step-by-step setting • Introducing how to build interactive DB applications using macros and VBA modules • Providing the SAM software for building basic skills

4. The Approach • Learn relational database through practices • Getting started with data modeling and design • Building simple DB incrementally using MS Access • Convenient templates for DB design and implementation • Easy-to-use tools • Extended functionalities • Widely used in various applications • Getting exposed to SQL with Access and Oracle • universal standard query language • Industry strength RDBMS

5. Major Topics • Introductions to RDBMS and MS Access • Data modeling & RDB design (processes and rules) • DB building blocks • Tables – data storage • Queries – data retrieval and manipulation (insert, delete, update) • Forms and reports – user interfaces for displaying data/info • Application development using switchboards, as well as macros & VBA modules • SQL (using Access and Oracle)

6. Class Plan & Grade Breakdown Quizzes (3 at 40 points each) 120 points Exams (3 at 100 points each) 300 points Labs and course portfolio 280 points Team Project 300 points 8 increments 240 points Docs & Demo 60 points

7. What Is a Database? • To answer this question, we will discuss the following related concepts • Data • Information • Database • Relational database • Database management system • Database applications

8. Data • Definition by Merriam-Webster’s • factual information (as measurements or statistics) used as a basis for reasoning, discussion, or calculation • Raw data • Raw data (pieces of information) may not be so helpful without proper manipulation Data is the set of original value you store in the tables of the database (You store data.)

9. Information • Information is • Derived from raw data • Specially prepared for a certain audience • Before useful information can be obtained • Data needs to be defined and stored in a highly organized manner • Data should be easy to add into and retrieve from a database Information is data that is retrieved and organized in a way that is meaningful to the person viewing it. (You retrieve & present information.)

10. Database • A database is a collection of related raw data that is well-defined and organized. A 100-Year-Old Example Banked for Hara Talbot Aug 13 Adah Talbot ½ day in the afternoon $0.50

11. Relational Database • Backed by sound mathematical theories, RDBs provide a better way to organize user data • Usually with data for several subjects (entities) within a certain application (domain) • Teams • Players • Games • Scores and technical statistics • In a relational database, data are stored in tables each of which is designated for a specific subject • The mathematical term “relation” defines a data structure for which a table is the exact incarnation

12. Example of a Table – Hawks Roster Row (or record) Column (or field)

13. Database Management System • A DBMS is a software system which takes care of • Data definition • Data storage and retrieval • Data manipulation • Insertion • Modification • Deletion • Other functions, such as security, transaction control, archiving, backup and recovery • By using a DBMS, applications programmer can focus on business logic

14. File-Processing Systems X

15. File-Processing Systems • Records are stored in separate files • Drawbacks • Separated and isolated data • Data duplication • Application program dependency • Read from the product file for 20 bytes starting from location 1234, from the customer file ... • The difficulty of synchronize data changes • The difficulty of representing data in the users’ perspectives

16. Why DBMS?

17. Database Systems • DB technology was developed largely to overcome the limitations of file systems • Advantages • Integrated data • Reduced data duplication • Program/data independence, through the use of SQL • SELECT ProductName FROM Product WHERE ProductId = ‘P010’ • Easier to update • Easier representation of the users’ views

18. Relational Databases • The term relational is used because • In RDB, data are stored in tables. • Each table is a mathematical relation on a number of sets • Related tables are linked with shared columns Aug 13 Adah Talbot ½ day in the afternoon $0.50 Month {Jan, …, Dec} Day {1, 2, …, 31} Worker {…} Time worked {…} Wage {…}

19. RDBMS Capabilities • Data definition • Attributes – columns of your tables • Data types – date, text, numeric, money, etc • Relationships – among data in different tables • Data manipulation • Data control – permission to use data • Fans can read statistics on website • The Hawks manager can add, remove, and modify player records

20. Data Definition • A database is self-describing • Metadata – data used to define user data • or data about data Aug 13 Adah Talbot ½ day in the afternoon $0.50 Month Day Worker Time worked Wage Text Number Text Text Money {Jan, …, Dec} {1, 2, …, 31} {…} {…} {…} Field name Data type Valid range User data Metadata, or schema

21. Example– Data Definition in Access

22. Example – Relationship in Access

23. Components of DB Systems

24. DB Users • Types of Users • End users (naïve users) • Limited access to data, through forms or reports • Application developers • Use programming languages w/ DB accessibility • Sophisticated users • Use query languages and tools • Specialized users • Write special applications, such as CAD, GIS, etc

25. DB Administrators • DBA is the central control over the system • DBA functions include • Schema definition and modification • Storage structure definition • Granting authorization for data access • Read only • Change (add and/or modify) • Create tables and modify schema • Routine operation and maintenance

26. When to Use a DBMS? • A significant amount of related data • Data about different subjects • Multiple uses of these data • Data used by multiple users • Changes to data should be synchronized • Accesses to data must be controlled

27. Sample DB Applications • In a local area network

28. Sample DB Applications • In a wide area network or over the Internet

29. Characteristics of Types of DBs