LIS569 – Database Systems Dr. Jianqiang Wang Introduction

1. LIS569 – Database SystemsDr. Jianqiang WangIntroduction Week 1, Jan. 14, 2013

2. Agenda • Course overview • Topics, requirements, evaluation, etc. • Database environment • Databse/IS development methodologies

3. Course Goals: What You Will Learn • Understand the theoretical basis of relational databases • Understand how design decisions affect data management and retrieval • Learn the principles and methodologies for database design • Learn the basics of placing user-accessible databases on the Web • Become skillful in using some of the popular database management tools (MySQL and PHP)

4. Related LIS Program Objectives 1.1 Understand the nature of information and its role. 1.6 Understand the role of library and information services in a rapidly changing technological society and are equipped to respond to the evolving needs. 2.1 Are able to analyze information needs and to design, promote, and assess information services. 2.3 Understand and are able to apply principles of knowledge organization for a wide range of applications. 2.4 Have technological knowledge and skills needed for carrying out information functions.

5. Related LIS Program Objectives 3.2 Have skills in managing their own work, such as priority setting and time management. 3.3 Are able to communicate effectively in multiple media, as receivers of communications, as producers of communications. and as collaborators. 3.5 Understand and are able to apply a variety of research methods / methods of inquiry, exhibit critical thinking, and are able to assess and apply research findings. 5 Understand the importance of personal qualities conducive to professional success such as integrity, creativity, initiative, intellectual curiosity, and interpersonal skills.

6. Time, Location, and Office Hours • Class/lab sessions • Where: 14A Baldy PC Lab • When: 7:00-9:40PM, Mon. • Instructor: Dr. Jianqiang Wang • Office: 526 Baldy Hall • Email: jw254@buffalo.edu (finds me anywhere) • Office hours: 3-4PM, Mon.; 4-5PM, Wed.

7. Approach • Readings • Provide background and detail • Lecture/discussion sessions • Provide conceptual structure • Exercises, homework, and project • Provide hands-on experience • Quizzes • Measure learning progress

8. Software Tools • 14A Baldy Lab PCs • Diagramming: SmartDraw, MS Visio, or their likes • Navicat Lite (to access MySQL server) • GSE Servers • Web server • PHP • MySQL • Your personal computer • XAMPP installation recommended

9. Grading • Homework assignments: 30% • Individual work (group discussion is fine) • Consolidate database skills and understandings • Project: 35% • Group work • Design and implement a Web-based database system • Quizzes: 20% • Individual work • In-class, open-book, ~60 minutes each • Class participation: 15% • Class attendance and discussion • Lab exercises for most weeks

10. Homework Assignments • Six assignments • Conceptual data modeling (ERDs) • Logical database design (including normalization) • SQL and advanced SQL • PHP programming • Creating MySQL databases • Web-database integration (PHP+MySQL+HTML) • Logistics • (roughly) two weeks per HW assignment • Complete and submit electronically

11. Project • Goal • Develop a Web-based relational database system • Project topic • Decided by teams or assigned by the instructor • Format • Group of three • Timeline • Week 3: project proposal • Week 5, 8, 11, 14, and 16: informal project updates • Week 17: working system, project demo, project report

12. Example Project Topics • Library circulation control • Library acquisition information • Inter-library loan management • Course registration system • Course review and outcome assessment system • Plan of study and advising system • Department calendar • Membership management system • Airline scheduling • Movie reservation

13. Main Project Tasks • Structuring systems requirements • Collecting, analyzing, prioritizing, … • Designing the database • Entities, relationships, attributes, business rules, ... • Designing the interface • Forms, reports, views, … • Implementing the system • Database, sample data, interface, … • Presenting the project • Demonstrating the working system • Documenting and Reporting • Rationale, problems, solutions, collaboration, future work…

14. The Grand Plan Physical Design Introduction PHP II Web-DB I SQL ERD Web-DB II Enhanced ERD MySQL I DBA Logical Design I MySQL II Project Demo Logical Design II PHP I

15. Important Dates • HW assignments • Assigned in Week 3, 5, 7, 10, 12, and 14 • Due in Week 5, 7, 10, 12, 14, and 16 • Quizzes in • Week 7, 12, 16 • Project • Proposal due in Week 3 • Updates in Week 5, 8, 11, 14, and 16 • System and report due in Week 17

16. The Fine Print • Group work is encouraged on HWs • But you must personally create what you turn in • Deadlines are firm and sharp • But late submission due to extreme circumstances (severe illness, car accident, etc.) can be accepted • Academic integrity is a serious matter • UB’s Academic Integrity Policy and Procedure

17. LIS Technology Courses • LIS506: lays the background of IT • LIS559: covers system and network administration • LIS561: covers the methodologies and skills of planning, analyzing, and designing IS • LIS562: focuses on networking technology • LIS563: focuses on digital library technology and related issues • LIS569: focuses on database technology

18. Week 1’s Objectives • Define terms • Name limitations of conventional file processing • Explain advantages of databases • Identify costs and risks of databases • List components of database environment • Identify categories of database applications • Describe database system development life cycle • Explain prototyping approaches • Explain roles of individuals • Explain the three-schema architecture for databases

19. Definitions • Database: organized collection of logically related data • Data: stored representations of meaningful objects and events • Structured: numbers, dates, … • Unstructured: images, video, documents, … • Information: data processed to increase knowledge in the person using the data • Metadata: data that describes the properties and context of user data

20. Information: Data in Context Context helps users understand data

21. Information: Summarized Data Graphical displays turn data into useful information that managers can use for decision making and interpretation

22. Metadata: Data about Data Descriptions of the properties or characteristics of the data, including data types, field sizes, allowable values, and data context

23. Data Retrieval vs. Information Retrieval Source: C. J. van Rijsbergen: Information Retrieval. Butterworth 1979

24. Duplicate Data

25. Disadvantages of File Processing • Data redundancy • Different systems/programs have separate copies • Data inconsistency • Different files contain different information of the same object • Limited data sharing • No centralized control of data • Program-data dependence • All programs maintain metadata for each file they use • High demands for development/maintenance • Programmers must design and maintain their own file formats and programs

26. Solution: Database Approach • Central repository of shared data • Data is managed by a controlling agent • Stored in a standardized, convenient form Requires a Database Management System (DBMS)

27. Database Management System • A software system that is used to create, maintain, and provide controlled access to user databases Order Filing System Central database Contains employee, order, inventory, pricing, and customer data Invoicing System DBMS Payroll System DBMS manages data resources like an operating system manages hardware resources

28. Advantages of the Database Approach • Program-data independence • Planned data redundancy • Improved data consistency • Improved data sharing • Increased application development productivity • Enforcement of standards • Improved data quality • Improved data accessibility and responsiveness • Reduced program maintenance • Improved decision support

29. Costs and Risks of the DB Approach • New, specialized personnel • Installation and management cost and complexity • Conversion costs • Need for explicit backup and recovery • Organizational conflict

30. Elements of the Database Approach • Data models • Graphical system capturing nature and relationship of data • Enterprise Data Model: high-level entities and relationships for the organization • Project Data Model: more detailed view, matching data structure in database or data warehouse • Entities • Noun form describing a person, place, object, event, or concept • Composed of attributes • Relationships • Between entities • Usually one-to-many (1:M) or many-to-many (M:N) • Relational Databases • Database technology involving tables (relations) representing entities and primary/foreign keys representing relationships

31. Data Models Depicted as Diagrams Enterprise-level data model Project-level data model

32. Components of the DB Environment

33. Range of Database Applications

34. Two-tier Client/Server Architecture

35. Three-tier Client/Server Architecture

36. Evolution of DB Technologies

37. Approaches to DB/IS Development • SDLC • System Development Life Cycle • Detailed, well-planned development process • Time-consuming, but comprehensive • Long development cycle • Prototyping • Rapid application development (RAD) • Cursory attempt at conceptual data modeling • Define database during development of initial prototype • Repeat implementation and maintenance activities with new prototype versions

38. Planning Analysis Logical Design Physical Design Implementation Maintenance Systems Development Life Cycle

39. Planning Analysis Logical Design Physical Design Implementation Maintenance Systems Development Life Cycle (cont.) Purpose–preliminary understanding Deliverable–request for study Planning Database activity– enterprise modeling and early conceptual data modeling

40. Planning Analysis Logical Design Physical Design Implementation Maintenance Systems Development Life Cycle (cont.) Purpose–thorough requirements analysis and structuring Deliverable–functional system specifications Analysis Database activity–thorough and integrated conceptual data modeling

41. Planning Analysis Logical Design Physical Design Implementation Maintenance Systems Development Life Cycle (cont.) Purpose–information requirements elicitation and structure Deliverable–detailed design specifications Logical Design Database activity– logical database design (transactions, forms, displays, views, data integrity and security)

42. Planning Analysis Logical Design Physical Design Implementation Maintenance Systems Development Life Cycle (cont.) Purpose–develop technology and organizational specifications Deliverable–program/data structures, technology purchases, organization redesigns Physical Design Database activity– physical database design (define database to DBMS, physical data organization, database processing programs)

43. Planning Analysis Logical Design Physical Design Implementation Maintenance Systems Development Life Cycle (cont.) Purpose–programming, testing, training, installation, documenting Deliverable–operational programs, documentation, training materials Database activity– database implementation, including coded programs, documentation, installation and conversion Implementation

44. Planning Analysis Logical Design Physical Design Implementation Maintenance Systems Development Life Cycle (cont.) Purpose–monitor, repair, enhance Deliverable–periodic audits Database activity– database maintenance, performance analysis and tuning, error corrections Maintenance

45. Prototyping Database Methodology

46. Database Schema • External Schema • User Views • Subsets of Conceptual Schema • Can be determined from business-function/data entity matrices • DBA determines schema for different users • Conceptual Schema • E-R models–covered in Chapters 2 and 3 • Internal Schema • Logical structures–covered in Chapter 4 • Physical structures–covered in Chapter 5

47. Three-Schema Architecture Different people have different views of the database…these are the external schema The internal schema is the underlying design and implementation

48. Managing Projects • Project: a planned undertaking of related activities to reach an objective that has a beginning and an end • Involves use of review points for: • Validation of satisfactory progress • Step back from detail to overall view • Renew commitment of stakeholders • Incremental commitment: review of systems development project after each development phase with re-justification after each phase

49. Managing Projects: People Involved • Business analysts • Systems analysts • Database analysts and data modelers • Users • Programmers • Database architects • Data administrators • Project managers • Other technical experts

50. Computer System for PVF Company