Foundations of Database Systems

1. Foundations of Database Systems Class Introduction

2. Agenda • Introductions • Seating Chart • Course Overview • Syllabus • Case • Database Development Overview

3. Foundations of Database Systems • Objectives • Understand data-related activities of SDLC • Implement data modeling, database design, and database implementation techniques • CASE (Visio) • Database (SQL Server) • Course Contents • Lectures, Examples, In-Class Exercises • Assignments (2) • Labs (2) • Exam (1)

4. Learning • Participate : • Prepare --read & reread-- for each class • Attend, listen, be attentive, engaged • Ask and answer questions, & add to discussion • Do each assignment completely& in a timely and professional manner • Take PLENTY of notes in class: • Do NOT just rely on powerpoint • Explore : • Go beyond classroom material

5. Class Format1 • 9:00 am Devotional • 9:10 am Announcements, Q&A • 9:20 am DB Topic • 10:30 am - Break - • 10:45 am DB Topic • 12:30 noon Lunch • 14:00 pm DB Topic, Group Exercises • 15:30 pm - Break - • 15:40 pm DB Topic, Group Exercises and/or Labs • 16:30 pm Class Ends 1All times are approximate

6. Class Resources, Syllabus • Class Homepage: • http://blogs.baylor.edu/gina_green/northrise/ • Syllabus & Schedule: • http://business.baylor.edu/gina_green/teaching/northrise/summ14/summ14_northrise_syllabus.html all class materials accessed here NOTE: VLS is NOT used for posting class materials. It is used for (1) posting of grades and (2) posting a link to the class syllabus. The class syllabus contains additional links to class materials.

7. Assignments… • Two assignments: • ERD due Thursday, 24 Jul, 17:00 • SQL due Friday, 25 Jul, 17:00 • Based on real-world organization • Team-based • *** Team Preferences due by 9:00am tomorrow (Tuesday) *** • Case-based • Case is vague in some areas, and very detailed in other areas • Case available on class schedule webpage (Due column, 7/24 & 7/25) -OR – Click HERE to view case • Begin reviewing early • Ask me questions to clarify information, expectations

8. Research • Teaching Techniques in Database Courses • Periodic Assessments • Anonymous • NOT graded; will NOT impact your grade • First one today!

9. Introduction to Databases

10. 1960’s 1970’s 1980’s 1990’s 2000+ Federated MDDB XML NoSQL ……. Hierarchical Object Traditional Files Relational Network Object-Relational Evolution of Database Technologies

11. The Database Environment

12. Advantages of Databases • Program-data independence • Improved data sharing • Minimal data redundancy • Improved data accessibility/responsiveness • Improved data consistency • Faster application development • Enforcement of standards • Improved data quality • Reduced program maintenance

13. Database Development Process

14. Systems Development Life Cycle SDLC for this class DB Activities in SDLC Planning Enterprise Modeling* DB Scope, Requirements (Conceptual Data Model) Analysis DB Design (Logical DB Design) Design DB Design (Physical DB Design) DB Implementation (Load, Test, Eval, Op) Implementation DB Maintenance*

15. Enterprise Data Modeling • Determine organizational data requirements • Build enterprise data model • outcome is a very high-level Entity-Relationship Diagram • see : • http://da.ks.gov/kito/ITPlans/data_maps06.ppt • http://www.tdan.com/view-articles/5205

16. Source: http://www.tdan.com/view-articles/5205

17. Conceptual Data Modeling • Determine user data requirements • Determine business rules • Build conceptual data model • outcome is an Entity-Relationship Diagram (conceptual schema)

18. Logical Database Design • Select database model • e.g., the Relational Model • Transform conceptual (ERD) into logical (relational) data model • Normalize data structures • Outcome is normalized, relational tables

19. Physical Database Design • Select database product (e.g., SQL Server) • Select storage device(s) • Design fields, records, files (physical schema) • outcomes are detailed, physical definitions for: • fields (data dictionary) • records (space requirements for physical structures)* • files (access methods) *Will not do in this class

20. Database Implementation • Create database file/table structures • Create views (external schema) • Establish access rights • Load test data • Write/test programs that process data • Install database (with production data) into production operations • outcomes are secured database tables loaded with data

21. Database Maintenance • Maintain database structures • Storage/space management • Performance, tuning • I/O Contention • CPU Usage • Application Tuning • Data availability • DBMS upgrades, "fixes" • Backup, recovery …….

22. Database Maintenance, cont… • Backup • Full • Incremental • Differential • Business Continuity • Data Replication ("fallback")

23. Traditional Administration Definitions • Data Administration: A high-level function that is responsible for the overall management of data resources in an organization, including maintaining corporate-wide definitions and standards • Database Administration: A technical function that is responsible for physical database design and for dealing with technical issues such as security enforcement, database performance, and backup and recovery

24. Data People Involved in SDLC • Data Administrators • Data Architects • Data Stewards • Data(base) Analysts/Designers • Business (Intelligence) Analyst • Data Mining Engineer; Big Data Engineer; Data Scientist; Business Analytics Engineer; … • (System/Traditional) DBAs • Application DBAs • Procedural DBAs • e-DBAs • Data Warehouse Administrators

25. Summary • Evolution of Data Management • Disadvantages of file processing • Database Concepts • Components of a DBMS Environment • Database Advantages • Database Development: • Overall SDLC • Database Activities in the SDLC • People Involved in SDLC (esp. DB) • Traditional job divisions and responsibilities • Newer job titles