MIS 3150 Data and Info Management Lecture 1 - Database fundamentals

1. MIS 3150 Data and Info Management Lecture 1 - Database fundamentals Arijit Sengupta

2. Structure of this quarter MIS3150 1. Design 2. Querying 4. Advanced Topics 0. Intro 3. Applications Database Fundamentals Conceptual Modeling Query Languages Java DB Applications – JDBC Transaction Management Relational Model Advanced SQL Data Mining Normalization Newbie Users Designers Developers Professionals

3. Today’s buzzwords • Organizational data cycle • Database • Database Management Systems • Data Independence • Centralized Database Systems • Client-Server Database Systems • Distributed Database Systems

4. Getting used to Pilot • Content – lecture notes, etc. • Discussion boards • Dropboxes – for assignments • Groups – form the project groups • Quizzes and exams • Grades • Finally… Elluminate!

5. Before we begin – Elluminate! • In the Elluminate tab, find the Elluminate link for today’s lecture • When you click the link, a small jws application will download and start (might take a few minutes) • Remember – do not click the microphone button if you are using the lab computers.

6. Elluminate practices • Asking e-questions • Providing e-feedback • Using chat • Using polls and poll results • Using the whiteboard

7. Poll Question 1 How would you evaluate your current knowledge of databases? • Very little or no knowledge • Some knowledge (e.g., Access) • Good knowledge (used an enterprise DBMS before) • Expert (work/worked in database industry)

8. Poll Question 2 How would you evaluate your knowledge of SQL? • Little of no knowledge • Have written some very basic SQL with only Select-from-where • Have written mid-level SQL with joins and aggregate functions • Expert – have used nested subqueries and other advanced features

9. Using the whiteboard Write one thing that you hope you will learn in this course.

10. Objectives of today’s lecture • Know common database terminology • Understand requirements and uses of data • Know the differences between databases and flat files • Realize the importance and need for databases in problem-solving • Understand the different types of databases and their differences

11. Data: A Resource • The Success of an organization depends on efficient use of its resources: • Buildings, factories, equipment • Technical know-how • Human resources • Data • Data: An important organizational resource

12. Electronic Data • Why? • Large volume in a small space • Ease of sharing • Ease of use • Data analysis • How? • File-based system versus databases

13. Legacy (File-based) Systems • Uncontrolled data redundancy, • Data inconsistency • Poor data sharing • Difficult to keep up with changes • Record format Vs. user requirements • Programs Vs. record format • Low productivity • High maintenance cost

14. So what is a database? • According to Oxford English Dictionary: “A structured collection of data held in computer storage; esp. one that incorporates software to make it accessible in a variety of ways” • So does it make pretty much every collection of data a “database”?

15. The Database • The data itself PLUS The data definitions (metadata), applications, queries and visualizations

16. Database Approach • Non-redundant collection of logically related facts • representing some aspect of the real world • the data itself plus the data definitions • Permits sharing • Consistent representation for each piece of data • Avoids (minimizes) redundancy • Allows different user views • Users are isolated from most changes

17. Why Databases? • Independence from representation formats • Control redundancy and consistency • Ensure integrity/security • Better scalability • Allow ad hoc access • Better maintenance • Better concurrency

18. View Conceptual Physical Data Independence • Does data have to be part of programs? • Do we need to change one if the other changes? • Three-tier architecture of databases What the users see How we model data How data is stored

19. Access Flexibility • Easy to ask ad-hoc questions • No need for separate codes • User-friendly interface • Command-based (e.g., SQL) • Graphical (e.g., QBE)

20. Data Integrity • Ensures that the stored data are consistent and correct • Easy to define global rules • customer_age > 21 years • number_of_credits < 18 • Can allow multiple users to access data without compromising on data integrity

21. Data Security • Access definition • Global • Local • Uniform access authorization

22. Data Redundancy • Data need not be replicated • Less wastage of storage space • Less data anomaly • Reduced and controlled redundancy • Tighter control of replicated data

23. Standardization • Everybody talks the same talk. • Less chance of misunderstanding • Easier to interpret other’s data • Easier to merge • Useful when several organizations combine to form one.

24. Productivity and Maintenance • Increase in productivity • User-friendly interface • Independence from specific data structure • Easier maintenance • Less code to maintain • The DBMS is the bulk of the code. • Ad-hoc queries make it possible to make do with much less code. • The vendor makes revisions of the DBMS. • Economy of scale

25. Productivity vs. Maintenance

26. Disadvantages of Databases • Software complexity • Processing inefficiency • Need for co-ordination • Organizational impact • Risk

27. DBMS • A Specialized piece of software that sits between the data and its users. DatabaseManagement System Intension + Extension Data

28. DBMS Functions and Users • Four major uses of a DBMS package • Database Development, Interrogation, Maintenance, and • Application Development • Automated tools for design, query, and application development • Database users • Database administrators ( DBAs ) • Database designer • End Users

29. Summary • Data is essential for an organization • A Database is usually the most effective way of storing and organizing data • File-based Vs. database systems • Database system properties • Types of database systems