DBS Theory Week 1 including relationships and relational database

tyy

1. Dr. Maria Kanwal Assistant Professor, CS NUTECH, Islamabad Database Systems Lecture 01

2. What are Data?

3. What are Data? Data are often viewed as the lowest level of abstraction from which information and knowledge are derived. Data can exist in a variety of forms -- as numbers or text on pieces of paper, as bits and bytes stored in electronic memory , or as facts stored in a person's mind. Raw data refers to a collection of numbers, characters, images or other outputs from devices that collect information to convert physical quantities into symbols, that are unprocessed.

4. What is Database?

5. What is a Database? A database is a shared collection of logically related data that is stored to meet the requirements of different users of an organization Or A database consists of an organized collection of data for one or more multiple uses. Or An organized body of related information. Computer databases typically contain aggregations of data records or files.

6. What is a Database? Databases play a critical role in almost all areas Banking: all transactions Airline: reservation, schedules Universities: registration, grades Sales: customers, products, purchases Manufacturing: production, inventory, orders, supply chain Human resources: employee records, salaries, tax deductions

7. What is a Database? A database can be of any size and of varying complexity . A software system that facilitates the creation and maintenance and use of an electronic database For example, the list of names and addresses of friends The book catalog of a large library may contain half a million records A database of much greater size and complexity is maintained by the government to keep track of the tax information filed by taxpayers.

8. What is a Database? A schema is the definition of a database. It defines the meaning of data. An instance of a database is the collection of data in the database at a particular point of time. For example, in the above snap-shot, the schema is “ Student Name, ID, Age, Gender, Entrance Year, Grade ” . The remaining rows in the table make up an instance of the database. Student Name ID Age Gender Entrance Year Grade Sadaf Jamal A34455 20 F 1998 A Ali Imtiaz C23444 19 M 1999 B Wasif Ali C73334 19 M 2000 C

9. Definitions Database: organized collection of logically related data Database stores data Data: referred to facts concerning objects and events that could be recorded and stored on computer media Types of data Structured: numbers, text, dates Example Students regd No, name, contact No Unstructured: images, video, documents Information : data that have been processed to increase knowledge of the person who uses the data. Example Consider the following facts 9

10. 10 Metadata: data that describes the properties or characteristics of end user data and context of that data Data about data Example By adding a few additional data items and providing some structure, we recognize a class roster for a particular course.

11. 11 Descriptions of the properties or characteristics of the data, including data types, field sizes, allowable values, and data context

12. What is Database Management System (DBMS)? A DBMS is a collection of software programs to enable users to create, maintain and utilize a database. In database management systems, data files are the files that store the database information, whereas other files, such as index files and data dictionaries, store administrative information, known as metadata . Functions of DBMS Insert records Delete records Update records Query records

13. What is a DBMSs? Commercial DBMSs Company Product Oracle Oracle 8i, 9i, 10g IBM DB2, Universal Server (from System R, System R*, Starburst) & Informix Microsoft Access, SQL Server Sybase Adaptive Server Informix Dynamic Server NCR Teradata UC Brekeley’s INGRES,M PostgreSQL

14. Database Users End users Use the database system to achieve some goal Application developers Write software to allow end users to interface with the database system Database Administrator (DBA) Designs & manages the database system Database systems programmer Writes the database software itself

15. Why do we need a DBMS? To reduce application development time Suppose we are given a collection of raw files which occupy 500GB What are the constraints and drawbacks? In case if we are using file systems rather than DBMS

16. What is File System? A file system (often also written as filesystem ) is a method of storing and organizing computer files and their data . Essentially, it organizes these files into a database for the storage, organization, manipulation, and retrieval by the computer's operating system . File systems are used on data storage devices such as a hard disks or CD-ROMs to maintain the physical location of the files.

17. System Architecture Applications Operating System (e.g. Windows, Linux) Device Driver Storage Media (Hard Disk, Flash Memory) File System (e.g. DOS, FAT)

18. File Processing System file processing system refers to the first computer-based approach of handling the commercial or business applications.  This approach met the data processing needs of individual department’s needs rather than overall information needs of the organization. it is also called a replacement of the manual file system. 18

19. File Processing System In this system data is stored in permanent system files. Different application programs are written to extract data from these files and to add record to these files. No overall map, plan or model guided application growth 19

20. File-Based Systems A decentralized approach was taken, where each department, with the assistance of Data Processing (DP) staff, stored and controlled its own data. Each department access their own files through application programs written specially for them. Each set of departmental application programs handles data entry, file maintenance, and the generation of a fixed set of specific reports. The physical structure and storage of the data files and records are defined in the application code.

21. File-based Processing

22. Disadvantages of File Processing Data redundancy: In File Processing Systems, the same data is often stored in multiple files, which can lead to data redundancy. Data redundancy means that the same data is stored in more than one place, leading to the wastage of storage space and making data maintenance more difficult. Data inconsistency: Due to data redundancy, inconsistencies can occur in the data. If changes are made to one file, these changes must be manually made to all other files containing the same data. If this is not done properly, data inconsistencies can arise. Limited data sharing : File Processing Systems are not designed to share data between applications or users. Each application has its own set of files, and sharing data between applications is difficult and often requires custom coding. Security : File Processing Systems do not have built-in security features to protect data from unauthorized access. Access to files is typically controlled through file permissions, which can be difficult to manage and can lead to security breaches. Lack of flexibility : File Processing Systems are not flexible and cannot easily adapt to changes in the data structure or new requirements. Adding new fields or modifying existing fields requires significant effort and can disrupt existing applications that rely on the data. Limited scalability: File Processing Systems are not scalable and can handle only a limited amount of data. As the amount of data grows, File Processing Systems can become slow and unreliable. Difficult to maintain : File Processing Systems require significant effort to maintain. As the number of files and applications grows, it can become difficult to keep track of which files are being used by which applications and to ensure data consistency.

23. Database Approach Arose because: Definition of data was embedded in application programs, rather than being stored separately and independently. No control over access and manipulation of data beyond that imposed by application programs. Result: the database and Database Management System (DBMS).

24. Database Shared collection of logically related data (and a description of this data), designed to meet the information needs of an organization. System catalog (metadata) provides description of data to enable program–data independence. Logically related data comprises entities, attributes, and relationships of an organization’ s information.

25. Database Processing

26. OS File System Storage Based Approach Files of records –used for data storage Data redundancy –wastage of space Maintaining consistency becomes difficult Record structures –hard coded into the programs Structure modifications –hard to perform Each different data access request (a query) Performed by a separate program difficult to anticipate all such requests Creating the system- requires a lot of effort Managing concurrent access and failure recovery are difficult

27. DBMS Approach DBMS Separation of data and metadata Flexibility of changing metadata Program-data independence Data access language standardized –SQL ad-hoc query formulation –easy

28. DBMS Approach System development less effort required concentration on logical level design is enough components to organize data storage process queries, manage concurrent access, recovery from failures, manage access control are all available

29. The DATABASE Approach Data models: capture the nature of and relationships among data and are used at different levels of abstraction as a database is conceptualized and designed. The effectiveness and efficiency of a database is directly associated with the structure of the database. Various graphical systems exist that convey this structure and are used to produce data models. 29

30. A data model is made up of entities and relationships. Entities Entity: a “thing” or “object” in the enterprise that is distinguishable from other objects Described by a set of attributes Example CUSTOMER, ORDER Attribute: Customer Name, Address, Order ID 30

31. Relationships relationship between entities that exists in organizational data. Different types of relationships exist. One to many(1:M) Many to many (M:N) 31

32. The DATABASE Approach Relational model Entity-Relationship data model (mainly for database design) Object-based data models (Object-oriented and Object-relational) Semi structured data model (XML) Other older models: Network model Hierarchical model 32

33. Relational Model Example of tabular data in the relational model Columns Rows

34. 34 What happened to dept_name of instructor and student? Represented diagrammatically by an entity-relationship diagram: Relational Model

35. Database Management System 35 DBMS manages data resources like an operating system manages hardware resources A software system that is used to create, maintain, and provide controlled access to user databases

36. User interface 36

37. User Interface 37

38. Database Application Program 38

39. DBMS 39

40. ER Model of DBMS 40

41. Advantages of the Database approach Program Data Independence Metadata is stored separately from the application program. Data descriptions are stored in repository. This allows database to change and evolve without changing application program. 41

42. Advantages of the Database approach contd.. Planned Data Redundancy Database design attempts to integrate redundant files into a single logical structure. Each primary fact is recorded in only one place in database. At times it may be desirable to include some limited redundancy to improve database performance. 42

43. 43 Relationships established in special columns that provide links between tables

44. Advantages of the Database approach contd.. Improved data consistency By eliminating or controlling redundancy, reduces inconsistencies. 44

45. Advantages of the Database approach contd.. Improved data sharing Database is designed as a shared corporate resource. External/internal users are granted permission to use database.(user views) A user view is a logical description of some portion of the database that is required by a user to perform some task. 45

46. Advantages of the Database approach contd.. Increased productivity of application development Database reduces the cost and time for developing new business applications. There are three reasons for it: Assume database is already implemented, the application developer can concentrate on the specific functions required, without worrying about low level details. DBMs provides productivity tools such as forms and report generators. Use of Web services, based on the use of standard Internet Protocols. 46

47. Advantages of the Database approach contd.. Enforcement of standards Database administration should be granted single point authority and responsibility for enforcing standards. The standards include naming conventions, data quality standards, and uniform procedures for accessing, updating and protecting data.(Repository) 47

48. Advantages of the Database approach contd.. Improved data quality is a perception or an assessment of  data's  fitness to serve its purpose in a given context. According to report of Data Warehousing Institute (DWI) Data quality problem costs $600 billion to U.S business annually. The database approach provides a number of tools and processes to improve data quality. Integrity constraints: rule that cannot be violated by database users 48

49. Advantages of the Database approach contd.. Data warehouse : a large store of data accumulated from a wide range of sources within a company and used to guide management decisions. Data warehouse cleans up operational data before they are placed in data warehouse. 49

50. Advantages of the Database approach contd.. Improved data accessibility Structured Query Language is used to retrieve and process databases. It is an easy language and end user can easily use this language to interpret databases. 50

51. Advantages of the Database approach contd.. Reduced program maintenance Stored data changes due to many reasons Changes such as adding new data items, changing data formats, and so on.. Data are more independent on application program, so it can accommodate changes easily. 51

52. Advantages of the Database approach contd.. Improved decision support Purpose of databases is to serve for decision support applications. For example Databases to support customer relationship management, Financial analysis Supply chain management. 52

53. Components of database environment

54. Components of database environment contd.. Data modeling and design tools Data modeling and design tools are automated tools used to design databases and application programs. These tools help with creation of data models and in some cases can also help automatically generate the “code” needed to create the database . Repository A centralized knowledge base for all data definitions, data relationships, screen, and report formats, and other system components. DBMS Software that is used to create, maintain, and provide controlled access to user databases. OR A software that handles all access to the database Database Organized collection of logically related data, usually designed to meet the information needs of multiple users. Application programs Used to create and maintain the database and provide information to users . 54

55. Components of database environment contd.. User interface Includes languages, menus and other facilities by which users interact with various components of database . Data and database administrators Data administrators are persons who are responsible for overall management of data resources in an organization Database administrators are responsible for physical database design and for managing technical issues in database environment. System developers Persons such as systems analyst and programmers who design new application programs. End Users Persons throughout the organization who add, delete, and modify data 55