1 / 45

Database Design & Management

Explore the fundamentals of database management, design principles, SQL operations, and Oracle usage. Learn about data types, information processing, data vs. information, and the benefits of transitioning to a centralized database system.

lwu
Download Presentation

Database Design & Management

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Design & Management

  2. Contents (Summary) • Concepts of Database Management • Database Designing and Modelling • Client-Server Architecture, Data Dictionaries • SQL • DDL,DML,Views ,Constraints • Oracle SQL • Distributed Database • Object oriented database • Database Administration

  3. Method of Delivery • Text Books • Reference Book/s • Lecture Slides • Lab work (SQL) • Helping notes or Reference website (email)

  4. Key to success • Punctuality • Hard work • Practice • Devotion • Dedication

  5. Text Book (1) Modern Database Management By: Fred McFadden, Jeffrey Hoffer, Mary Prescott 5th Edition, Addison Wesley (2) Database Systems Concepts By: Silberschatz., Korth., Sudarshan 4th Edition (3) Database Systems By: Thomas Connolly, Carolyn Begg 3rd Edition (4) An Introduction to Database Systems By: C.J.Date

  6. What is Data ?

  7. Data consists of Facts, text, graphics, images, sound and video segments that have meaning to user’s environment. • Data can exist in a variety of forms: • - As numbers or text on pieces of paper - As bits and bytes stored in electronic memory • - As facts stored in a person's mind. Definition of Data

  8. Data is what we measure • Data type refers to the classification of a particular type of information • integer • floating point • character, string • date • student record • blob (binary large object) Data Type

  9. Data in Context Large volume of facts, difficult to interpret or make decisions based on

  10. INFORMATION • Data that has been processed in such a way that it can increase the knowledge of person who uses it

  11. Useful information that managers can use for decision making and interpretation

  12. DatavsInformation • Data into information • Summarise data • Data In context (table) • Decision support • We use Database term without distinguish data or information

  13. ACTIVITY • Please write down the data for cricket match bowling • Do you get any information??

  14. “ Database is a shared collection of logically related data, designed to meet the information needs of multiple users in an organization ” Database is designed, built, and populated with data for a specific purpose. It has an intended group of users and preconceived applications in which these users are interested. Definition of Database

  15. Alternative Terminology • Formal terms are mostly used for technical users. • Data processing terms are more natural for non technical users.

  16. Computerized File Based Processing System

  17. Computerized File-based Processing File handling routines & File definition Sales files Sales File handling routines & File definition Contracts files Contracts

  18. File-based Systems • A collection of application programs that perform services for the end users (e.g. reports). • Each program defines and manages its own data.

  19. Duplicate Data Three file processing systems

  20. Separation and Isolation of data • Each application maintains its own set of data. • Users of one application may be unaware of potentially useful data held by other applications. • Duplication of data (data redundancy) • Same data is held by different files. Limitation of Computerized File-based Approach • Limited Data Sharing • No centralized control of data • Lengthy Development Times • Programmers must design their own file formats • Excessive Program Maintenance • 80% of information systems budget

  21. Accessibility • Data is stored using programs that are written in different languages and format • Cannot easily access other files due to incompatible file format. • No control over access and manipulation of data beyond that imposed by application programs. • Hard Coded Queries (Data dependence) • File structure and all definition of data are defined in the application program code. • Programs are written to satisfy particular functions / requirements. • Any new requirement needs a new program. Limitation of File-based Approach

  22. Problems with Data Dependency • Each application programmer must maintain their own data • Each application program needs to include code for the metadata of each file • Each application program must have its own processing routines for reading, inserting, updating and deleting data • Lack of coordination and central control • Non-standard file formats

  23. Problems with Data Redundancy • Waste of space to have duplicate data • Causes more maintenance headaches • The biggest Problem: • When data changes in one file, could cause inconsistencies • Compromisesdata integrity

  24. SOLUTION: Database Processing System

  25. Database Processing Sales Application Sales DBMS Database Contracts Application Contracts

  26. The 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. Application #1 Application #2 Application #3 DBMS Database containing centralized shared data Database Management System DBMS manages data resources like an operating system manages hardware resources

  28. A collection of programs that enables users to define,create and maintain and control access to the database. General-purpose software system that facilitates the processes of defining, constructing and manipulating databases for various applications. Defining - specifying data types, structures, constraints. Constructing - storing of data Manipulation - querying, updating and reporting. Database Management System (DBMS)

  29. Popular DBMS In The Market Sybase SQL Anywhere Informix Dynamic Server Borland Interbase

  30. Database Management System • A DBMS is a data storage and retrieval system which permits data to be stored non-redundantly while making it appear to the user as if the data is well-integrated.

  31. Advantages of Database Approach • Program-Data Independence • Metadata stored in DBMS, so applications don’t need to worry about data formats • Data queries/updates managed by DBMS so programs don’t need to process data access routines Results in: increased application development and maintenance productivity • Minimal Data Redundancy • Leads to increased data integrity/consistency

  32. Advantages of Database Approach • Improved Data Sharing • Different users get different views of the data • Enforcement of Standards • All data access is done in the same way • Improved Data Quality • Constraints, data validation rules • Better Data Accessibility/ Responsiveness • Use of standard data query language (SQL) • Security, Backup/Recovery, Concurrency • Disaster recovery is easier

  33. Costs and Risks of the Database Approach • Up-front costs: • Installation Management Cost and Complexity • Conversion Costs • Cost of DBMS • Additional hardware costs. Ongoing Costs: • Requires New, Specialized Personnel • Need for Explicit Backup and Recovery • Higher impact of a failure • Organizational Conflict: • Old habits die hard

  34. The Range ofDatabase Applications • Personal Database – standalone desktop database • Workgroup Database – local area network (<25 users) • Department Database – local area network (25-100 users) • Enterprise Database – wide-area network (hundreds or thousands of users)

  35. Personal Computer Database

  36. Workgroup database with local area network

  37. An enterprise data warehouse

  38. 1. Hardware Can range from a PC to a network of computers. 2. Software DBMS itself and operating system, including any network software if network is being used. 3. Data Includes actual data used by the organization, and description of data (schema). Also a system catalog contains data such as the names of authorized users. Components of DBMS Environment

  39. 4. Procedures Instructions and rules that should be applied to the design and use of the database, and the use of the DBMS. 5. People Data & Database Administrator Database Designer :- Logical & Physical Application Programmers End Users Components of DBMS Environment

  40. Bridge DBMS Environment Data Software Procedures Hardware People Machine Human

  41. Users/Programs Database System Environment Application Programs/Queries Software to Process Queries/Programs Software to Access Stored Data Stored Database Definition Stored Database

  42. Evolution of DB Systems • Flat files - 1960s - 1980s • Hierarchical – 1970s - 1990s • Network – 1970s - 1990s • Relational – 1980s - present • Object-oriented – 1990s - present • Object-relational – 1990s - present • Data warehousing – 1980s - present • Web-enabled – 1990s - present

  43. Summary • Course Contents • Data • Information • Data Vs Information • Database Terms • DBMS • Advantages • Disadvantages of DBMS • Database Terms • Comparison of Computerized File Based Processing System & Database System

More Related