CS 307 - Database Design and Management Lecture 1 - PDF

CS 307 – Database Design and Management Week 1: Course overview and Introduction to Databases Dr Tanko Ishaya University of Jos  Aim and Objectives § To learn and explore essential principles...

CS 307 – Database Design and Management Week 1: Course overview and Introduction to Databases Dr Tanko Ishaya University of Jos  Aim and Objectives § To learn and explore essential principles and concepts of databases, database design and database management systems § To gain practical experience in designing and implementing a simple database according to the relational principles § Enable you to design and use a database to support an application § To understand the implications of your design § Develop an understanding of current and likely future developments in the database ! technology 2 !  Learning Outcomes § Intellectual skills § Analyse and develop databases using relational modelling techniques such as Entity Relationship (E-R modelling) and Normalisation § Compare relational data models and relational algebra § Practical subject specific skills § Create and query relational database applications using SQL and other technologies for interfacing databases to the Internet § Transferable skills § Recognise the need for future developments in ! database technologies 3 !  Course Outline § Database systems § Data models § The relational database model § Entity relationship (ER) modeling § Normalization of database tables § Advanced data modeling § Introduction to structured query language (SQL) § Advanced SQL § Database design § Transaction management and concurrency control § Database performance tuning and query optimization § Distributed database management systems § Database connectivity and web technologies § Database administration and security. ! 4 !  Why Study Databases? § Databases are useful § Many computing applications deal with large amounts of information § Database systems give a set of tools for storing, searching and managing this information § Databases in CS § Databases are a ‘core topic’ in computer science § Basic concepts and skills with database systems are part of the skill set you will be assumed to have as a CS graduate ! 5 !  Course Organisation § Three parts § lectures § Labs § self study § Lecturers § Tanko Ishaya § Lab Officer(s) § Online course documentation at Moodle ! 6 !  Module Assessments § Assessed Course work: 30% § Examination mark: 70% § Important Note To pass the module, you must pass both assessed course work and examination ! 7 !  Module References § Essential § Rob, P. & Coronel, C., Database Systems: Design, Implementation, and Management, 8th edition, 2009 § Elmasri R,E., Navathe, S.B., Fundamentals of Database Systems, Longman Higher Education, 5th edition, 2007 § Recommended § Jones, J. and Monk, S., Databases in Theory and Practice, ITP, 1997 § Connolly, T. & Begg, C., Database Systems: A Practical Approach to Design, Implementation and Management,. 2nd Edition, 1999, ISBN: 0-201-34287-1 § Further Reading § MYSQL User Guide, http://www.mysql.org § PostgreSQL User Guide, http://www.postgresql.org/ ! 8 !  The Data, the People and the Organisation All the value of this company is in its people. If you burned down all our plants, and we just kept our people and our information files, we should soon be as strong as ever. Thomas Watson, Jr. Former chairman of IBM ! 9 !  Traditional File Systems § In the the past as new applications were written they used existing files, or created a new file for their use. § Sometimes several existing files need to be sorted and merged to obtain the new file.Thus, it is probable that several files will contain the same information stored in different ways. In other words, there will be redundant and possibly inconsistent data. § Consider the files for an insurance company POLICY# POLICYHOLDER PREMIUMS data ADDRESS PREMIUM-PA PREMIUM-TOTAL POLICY# POLICYHOLDER AGENCY data ADDRESS AGENT-CODE RENEWAL-DATE ! RENEWAL-AMT 10 !  Traditional File Systems § Basic concepts of a file system § Traditionally composed of a collection of file folders, each properly tagged and kept in a filling cabinet § Contents of each file folder were logically related § Applications were often considered in relative isolation § Data that should have been together was not § The potential for flexible enquiry and reporting was limited § Procedures were required for backup and recovery § All programmers had access to all records § There was limited concurrent access ! 11 !  What is Data? § A series of observations, measurements or facts § The information operated on by a computer § “Data” means information recorded in a form in which it can be processed by equipment operating automatically in response to instructions given for that purpose - Data Protection Act 1984 ! 12 !  What is a Database? § A database is a collection of “related” data (facts that can be recorded and that have implicit meaning) § A database represents some aspects of the real world called mini-word or Universe of Discourse (UoD) § A database is designed, build and populated with data for a specific purpose. It has an intended group of users and some preconceived applications in which these users are intended ! 13 !  Database Systems § A data base management system (DBMS) is a general -purpose software that facilitate the processes of defining, constructing, and manipulating databases for various applications Database + DBMS = Database System § Database Management Systems are used for § gathering of data (INSERT) § storage of data (SAVE) § retrieval of data (SELECT) § manipulation of data (DELETE & UPDATE) ! 14 !  An Example of Database Systems BOOK INSERT INTO BOOK Title Author Publisher Year VALUES('Fund of...','..') Intro to DB Systems Date Addison-Wesley 1986 Fund. of DB Systems Elmasri Addison-Wesley 1989 DELETE FROM BOOK WHERE TITLE='London London Fields Amis Penguin 1989 Fields' 100 years of solitude Marquez Picador 1982 The history man Bradbury Arrow Books 1977 UPDATE BOOK SET YEAR='1975' WHERE TITLE=The history man' Title Author Fund. of DB Systems Elmasri SELECT TITLE, AUTHOR FROM BOOK London Fields Amis WHERE YEAR='1989' ! 15 !  Requirements of a Database System Efficiency for access and modification of very large amounts of data in the ability of data surviving hardware crashes Resilience and software errors without sustaining loss or becoming inconsistent in the ability to permit simultaneous access of Access Control data by multiple users in a consistent manner and assuring only authorised access the maintenance of data over long periods Persistence of time, independent of any programs that access the data ! 16 !  Developing a Database Application Processing Requirements 1 Conceptual Schema Design DBMS Characteristics 2 Database Schema Design Hardware 3 &Operating Physical Design System characteristics Physical Database Structure ! 17 !  Classification of Database Systems § Recent past mainstream technologies § Hierarchical § Network § Current mainstream technologies § Relational § Future mainstream technologies § Object-Oriented § Object-Relational § Deductive ! 18 ! Next?