Database Design P.Uruthiran puruthiran@gmail ICT-UNIVOTEC

Database Design P.Uruthiran [email protected] ICT-UNIVOTEC

Chapter 1 Information in the organization and DBMS terminology and concepts

Data Storage. UNIVOTEC Data storage is considered by some to be the heart of an Information System. The general objectives of the design of data storage organization are shown in the following diagram. 3 Database Design - B.Tech 04/09/2023

Conventional Files and Databases UNIVOTEC There are two approaches to the storage of data in a computer-based system. 1) Store data in individual files each unique to a particular application. 2) Storage of data in a computer-based system involves building a database 4 Database Design - B.Tech 04/09/2023

Example UNIVOTEC Organization with a number of information systems using conventional, separate files. There are three individual files: SALES-FILE, Which contains historical sales information; CURRENT-ACTIVITY, Which is updated often; and PERSONNEL-FILE, Which contains address, titles, and so on; 5 Database Design - B.Tech 04/09/2023

UNIVOTEC 6 Database Design - B.Tech 04/09/2023

UNIVOTEC Notice that NUM and NAME exists on each file, besides the extra effort needed to input the NAME three times, a name change would require updating in three separate files. But, conventional files will remain a practical way to store data for some applications. 7 Database Design - B.Tech 04/09/2023

A modern database has a number of different interpretations UNIVOTEC 1) A database is a collection of stored operational data used by the application systems of some particular organization. 2) Database is not only a collection of files; instead, database is a central source of data meant to be shared by many users for a variety of applications. Note that data in the database need to integrated in the sense that they must not just represent isolated facts, but should also represent naturally occurring relationships between them. For example if the database contains data about orders & customers, it must show which orders were placed by which customers. 3) Database is a large, integrated, shared central source or pool of data meant to be shared by many users for a variety of applications 8 Database Design - B.Tech 04/09/2023

Integration: UNIVOTEC A common pool (Central source) of data was required rather than a series of departmental files, that is, the database has to be integrated. This is clear enough in plain English, but the technical implications are less easily defined. In fact a good deal more about the structure of data must be discussed before the technical implications of database integration can be fully understood 9 Database Design - B.Tech 04/09/2023

Data Sharing UNIVOTEC Data stored in a database is not usually held only for the use of one person. A database is normally expected to be accessible by more than one person, perhaps at the same time. Hence students’ database might be accessible by members of not only academic but also administrative staff. In our supermarket, sales data is accessible by the stock control and management information systems. 10 Database Design - B.Tech 04/09/2023

Data integration UNIVOTEC Shared data brings numerous advantages to the organization. Such advantages, however, only result if the database is treated responsibly. One major responsibility of database usage is to ensure that the data is integrated. This implies that a database should be a collection of data which, at least, has no redundant data. Redundant data is unnecessarily duplicated data. A data value is redundant when an attribute has two or more identical values. A data value is redundant if you can delete it without information being lost 11 Database Design - B.Tech 04/09/2023

Data integration. UNIVOTEC In the past, for instance separate files of student information might have been maintained by different academic and administrative departments of a university with many fields in common. The aim of a database system would be to store one logical item of data in one place only. Hence, one student record would be accessible to a range of information systems. 12 Database Design - B.Tech 04/09/2023

Data integrity UNIVOTEC Another responsibility arising as a consequence of shared data is that a database should display integrity. In other words, that the database should accurately reflect the universe of discourse that it is attempting to model. This means that if relationship exists in the real world between objects represented by data in a database then changes made to one partner in such a relationship should be accurately reflected in changes made to other partners in the relationship. Hence, if changes are made to the information stored on a particular university degree course, for instance, then relevant changes should be made to the information stored on modules and perhaps students of that course. 13 Database Design - B.Tech 04/09/2023

Data security. UNIVOTEC One of the major ways of ensuring the security of a database is by restricting access; in other words, securing the database. The main way that is done in contemporary database systems is by defining in some detail a set of authorized users of the whole, or more usually parts of the database. For instance, a secure system would be one where the finance department has access to information used for the collection of student fees but is prohibited from changing the fee levels of given students. 14 Database Design - B.Tech 04/09/2023

Technical Requirements of a Database UNIVOTEC In addition to being integrated & shareable, the database must have certain additional technical properties if it is to be acceptable to its users. 1) It must be reliable, secure and recoverable 2) It must have privacy 3) It must be available 4) It must be flexible to change 5) It must present consistent picture of the organization's operations to all its users. 15 Database Design - B.Tech 04/09/2023

Database. UNIVOTEC A database is a collection of occurrence of multiple record types containing the relationship between records, data aggregate and data items. A database may be defined as A database is a collection of interrelated data store together without harmful and unnecessary redundancy (duplicate data) to serve multiple applications. 16 Database Design - B.Tech 04/09/2023

Types of Databases. UNIVOTEC Continuing developments in information technology and its business applications have resulted in the evolution of several major types of databases. Operational Databases The databases store detailed data needed to support the operations of the entire organization. They are also called subject area databases (SADB), transaction databases, and production databases: Examples are customer databases, personnel databases, inventory databases, and other databases containing data generated by business operations 17 Database Design - B.Tech 04/09/2023

Types of Databases. UNIVOTEC Distributed Databases Many organizations replicate and distribute copies or parts of databases to network severs at a variety of sites. These distributed databases can reside on network servers on the World Wide Web, on corporate Intranets or extranets, or on other company networks. Distributed databases may be copies of operational or analytical. 18 Database Design - B.Tech 04/09/2023

Types of Databases. UNIVOTEC External Databases Access to external, privately owned online databases or data banks is available for a fee to end users and organizations from commercial online services, and with or without charge from many sources on the Internet, especially the Web. 19 Database Design - B.Tech 04/09/2023

Types of Databases. UNIVOTEC Hypermedia Databases It consists of hyperlinked pages of multimedia (text, graphics, and photographic images, video clips, audio segments, etc.). From a database management point of view, the set of interconnected multimedia pages at a website is a database of interrelated hypermedia page elements, rather than interrelated data records. 20 Database Design - B.Tech 04/09/2023

Database System. UNIVOTEC Database System is an integrated collection of related files along with the detail about their definition, interpretation, manipulation and maintenance. It is a system, which satisfied the data need for various applications in an organization without unnecessary redundancy. A database system is based on the data. Also a database system can be run or executed by using software called DBMS (Database Management System). A database system controls the data from unauthorized access. 21 Database Design - B.Tech 04/09/2023

Database Management System UNIVOTEC A DBMS is best described as a collection of programs that manage the database structure and that control shared access to the data in the database. Current DBMSes also store the relationships between the database components; they also take care of defining the required access paths to those components. A database management system (DBMS) is the combination of data, hardware, software and users to help an enterprise manage its operational data. The main function of a DBMS is to provide efficient and reliable methods of data retrieval to many users. Efficient data retrieval is an essential function of databaseDatabase systems. 22 Design - B.Tech 04/09/2023

Database management System (DBMS) 23 Database Design - B.Tech UNIVOTEC 04/09/2023

Database management System (DBMS) UNIVOTEC Data maintenance. Adding new data structures to the database, removing data structures from the database, modifying the structure of existing data, inserting new data into existing data structures, upgrading data in existing data structures, deleting data from existing data structures. 24 Database Design - B.Tech 04/09/2023

Database management System (DBMS). Data retrieval. Querying existing data by extracting data for use programs. Data control. Creating and monitoring databases, restricting access database and monitoring the database. 25 UNIVOTEC end-users and by application users of the to data in the performance of Database Design - B.Tech 04/09/2023

Various components of DBMS UNIVOTEC Basic Components: A database system has four components. These four components are important for understanding and designing the database system. These are: 1. Data 2. Hardware 3. Software 4. Users 26 Database Design - B.Tech 04/09/2023

Data. UNIVOTEC Data is raw hand information collected by us. Data is made up of data item or data aggregate. A Data item is the smallest unit of named data: It may consist of bits or bytes. A Data item is often referred to as field or data element. A Data aggregate is the collection of data items within the record, which is given a name and referred as a whole. Data can be collected orally or written. 27 Database Design - B.Tech 04/09/2023

Hardware. UNIVOTEC Hardware is also a major and primary part of the database. Without hardware nothing can be done. The definition of Hardware is “which we can touch and see”, i.e. it has physical existences. All physical quantity or items are in this category. For example, all the hardware input/output and storage devices like keyboard, mouse, scanner, monitor, storage devices (hard disk, floppy disk, magnetic disk, and magnetic drum) etc. are commonly used with a computer system. 28 Database Design - B.Tech 04/09/2023

Software. UNIVOTEC Software is another major part of the database system. It is the other side of hardware. Hardware and software are two sides of a coin. They go side by side. Software is a system. Software are further subdivided into two categories, First type is system software (like all the operating systems, all the languages and system packages etc.) Second one is an application software (payroll, electricity billing, hospital management and hostel administration etc.). We can define software as which we cannot touch and see. Software only can execute. By using software, data can be manipulated, organized and stored. 29 Database Design - B.Tech 04/09/2023

Main vendors/products Commercial Oracle IBM/DB2 MS SQL-server Sybase Informix MS Access, 30 UNIVOTEC Open source Postgres (UCB) mySQL, mSQL miniBase (Wisc) Predator (Cornell) (www.acm.org/ sigmod) Database Design - B.Tech 04/09/2023

Users. UNIVOTEC Without user all of the above said components (data, hardware & software) are meaning less. User can collect the data, operate and handle the hardware. Also operator feeds the data and arranges the data in order by executing the software. Other components 1. People - Database administrator; system developer; end user. 2.CASE tools: Computer-aided Software Engineering (CASE) tools. 3. User interface - Microsoft Access; PowerBuilder. 4. Application Programs - PowerBuilder script language; Visual Basic; C ; COBOL. 5. Repository - Store definitions of data called METADATA, screen and report formats, menu definitions, etc. 6. Database - Store actual occurrences data. 7. DBMS - Provide tools to manage all of this - create data, maintain data, control security access to data and to the repository. 31 Database Design - B.Tech 04/09/2023

Functions of DBMS. UNIVOTEC Data definition: The DBMS must be able to accept data definitions (external schemas, the conceptual schema, the internal schema, and all associated mappings) in source form and convert them to the appropriate object form. Data manipulation: The DBMS must be able to handle requests from the users to retrieve, update, or delete existing data the database, or to add new data to the database. In other words, the DBMS must include a data manipulation language (DML) processor component. 32 Database Design - B.Tech 04/09/2023

Functions of DBMS. UNIVOTEC Data security and integrity: The DBMS must monitor user requests and reject any attempt to violate the security and integrity rules defined by the DBA. Data recovery and concurrency: The DBMS - or else some other related software component, usually called the transaction manager - must enforce certain recovery and concurrency controls. 33 Database Design - B.Tech 04/09/2023

Functions of DBMS. UNIVOTEC Data Dictionary: The DBMS must provide a data dictionary function. The data dictionary can be regarded as a database in its own right (but a system database, rather than a user database). The dictionary contains “data about the data” (sometimes called metadata) - that is, definitions of other objects in the system - rather than just”raw data.” In particular, all the various schemas and mapping (external, conceptual, etc.) will physically be stored, in both source and object form, in the dictionary. A comprehensive dictionary will also include crossreference information, showing, for instance, which programs use which pieces of the database, which users require which reports, which terminals are connected to the system, and so on. 34 Database Design - B.Tech 04/09/2023

A multi-level view of data 35 Database Design - B.Tech UNIVOTEC 04/09/2023

(a) User is interested in some aspects of the business, example: In business banking database customer interested in his UNIVOTEC credit status; and financial transaction; status of his loan etc. (b) The analyst, of course is interested in exactly the same real world of information as the users, but needs to analyze the users information requirements in a methodical and structured way and, as a results of this analysis, produce some structured representation of the requirement in the form of a document. The term INFORMATION MODEL or DATA MODEL is currently used to describe such a document. Information model represent information requirement of an organization. Analyst use terms entity, attributes, and relationships to describe information model. (c) The analyst can organize information in a systemic way into entity types, their attributes and their relationships and this information can be accessed by the use of identifiers. In moving from the analyst's view of information into that of the manner in which it is stored in a computer, the information has to be mapped into computer's data storage mechanisms. Types of entities, attributes and relationships are represented by the Data Structure. 36 Database Design - B.Tech 04/09/2023

Data Structure 37 UNIVOTEC Database Design - B.Tech 04/09/2023

Data Structure. UNIVOTEC Since many files are integrated into a database that stores data reflecting interrelationships in the actual world, the structuring data is one of the most important elements of a database. 38 Database Design - B.Tech 04/09/2023

Designing a DBMS for a Organization UNIVOTEC In designing a Database Management System for any organization, as the first step or stage is to get to know something about the organization. This is called business analysis and business analysis is necessary so that the analysis can gain background knowledge of the organization, such as the goal of the organization, management hierarchy, and other various requirements that information systems may need to fulfill. 39 Database Design - B.Tech 04/09/2023

Organizations : Structure and IT Support UNIVOTEC Organizations The nature of organizations determines their activities, the information support they need, and the type of information systems they use Profit-making business versus not-for-profit organizations exist Manufacture goods versus services are delivered Can be located in one place or in several places, some are global or multinational organizations 40 Database Design - B.Tech 04/09/2023

Organizations : Structure UNIVOTEC Organizational Structure departmental functional structure » specialize in the delivery of a certain function » typical departments in an organization: » Accounting » Finance » Marketing and Sales » Production or Operations Management (POM) » Human Resources Management (HRM) » Information Systems and Technology 41 Database Design - B.Tech 04/09/2023

Organizations : Structure UNIVOTEC Hierarchical Structure Headquarters Division A Plant C Division B Plant D Plant E Overseas Division Plant F Administration Services (legal, etc.) POM 42 Accounting Marketing Database Design - B.Tech Finance HRM 04/09/2023

Organizations : IT Support UNIVOTEC The Corresponding Information Systems 43 Departmental Information Systems Plant Information Systems Divisional Information Systems Enterprisewide Information Systems Interorganizational Information Systems (IOS) Global Information System for an International or Multinational Corporation Database Design - B.Tech 04/09/2023

Types of Information Systems UNIVOTEC Transaction Processing Systems Organizations perform routine, repetitive tasks A TPS supports the monitoring, collection, storage, processing, and dissemination of the organization’s basic business transactions Frequently, several transaction processing systems exist in one company Today’s transaction processing systems are much more sophisticated and complex 44 Database Design - B.Tech 04/09/2023

Types of Information Systems (continued ) UNIVOTEC Management Information Systems (MIS) Provides periodic reports Generates weekly and monthly summaries by product, customer, or salesperson Initially, MIS had an historical orientation today, MIS reports may include summary reports, for the current period or for any number of previous periods - used for monitoring, planning, and control functional management information systems (MIS) » access, organize, summarize, and display information for supporting routine decision making in the functional areas » geared toward middle managers 45 Database Design - B.Tech 04/09/2023

Types of Information Systems (continued ) UNIVOTEC Major Outputs of a Functional Management Information System OUTPUT Statistical summaries DESCRIPTION Summaries of raw data such as daily production, and weekly and monthly usage of electricity. Exception reports Highlights of data items that are larger or smaller than designated levels. Periodic reports Statistical summaries and exception reports provided at scheduled, regular periods. Ad hoc reports Special, unscheduled reports provided on demand. Comparative analysis Projections 46 Performance comparison to that of competitors, past performance, or industry standards. Advance estimates of trends in future sales, cash flows, market share, etc. Database Design - B.Tech 04/09/2023

Types of Information Systems (continued ) UNIVOTEC Support Systems Office automation systems (OAS) » word processing systems Computer-Aided Design and Manufacturing (CAD/CAM) Decision Support Systems (DSS) End-user computing Executive Information Systems (EIS) Group Support Systems (GSS) Intelligent Systems Expert Systems (ES) 47 Artificial Neural Network Database Design - B.Tech (ANN) 04/09/2023

Types of Information Systems UNIVOTEC (continued ) Integrating Systems Various computerized systems are being integrated to increase their functionalities One popular form of integrated system is Enterprise Resources Planning (ERP) ERP plans and manages all of an organization’s resources and their use, including contacts with business partners 48 Database Design - B.Tech 04/09/2023

The Evolution UNIVOTEC Computer Based Information Systems 1940 Scientific, military applications 1950 Routine business applications, TPS 1960 MIS, office automation 1970 DSS, LANs Client/server executive information syste 1980 PC’s, AI, Groupware Integration, intelligent systems 1990 the Web, intranets, extranets, ERP softwa 49 Database Design - B.Tech 04/09/2023 2000 Internet, Electronic commerce, Smart syst

IT Support at Different Organizational Levels Strategic Systems UNIVOTEC Top Managers Staff Support Knowledge Workers, Professionals Managerial Systems Operation Systems Middle Managers Line Managers, Operators Office Automation and Communication Systems Clerical Staff Information Infrastructure and TPS 50 Database Design - B.Tech 04/09/2023 The information systems support of people in organizations

Knowledge Workers UNIVOTEC People who create information and knowledge and integrate it into the business Engineers, financial and marketing analysts, production planners, lawyers, and accountants Responsible for finding or developing new knowledge for the organization and integrating it with existing knowledge Act as advisors and consultants to the members of the organization Act as change agents by introducing new procedures, technologies, or processes 60 to 80 percent of all workers are knowledge workers Supported by a large variety of information systems from Internet search engines to expert systems, to computer-aided design, and by knowledge bases 51 Database Design - B.Tech 04/09/2023

Clerical Staff Support managers at all levels Data workers - use, manipulate, disseminate information UNIVOTEC or bookkeepers, secretaries who work with word processors, electronic file clerks, and insurance claim processors Supported by office automation and groupware, including document management, workflow, e-mail, and other personal productivity software 52 Database Design - B.Tech 04/09/2023

Designing a DBMS for a Organization UNIVOTEC With background knowledge of the organization established, it is possible to develop a conceptual model. This is a formal model of the organization, which is achieved by using data analysis techniques. 53 Database Design - B.Tech 04/09/2023

Designing a DBMS for a Organization. UNIVOTEC The next stage is to map this model onto a computer database. This process is usually referred to as developing the Logical Model. The form of this model will vary according to the database management system used; finally the DBMS maps these data structure onto computer storage media. This is the physical model, which is usually held on magnetic disks and will be accessed by the various applications that require data from database. This access will be made using the DBMS. 54 Database Design - B.Tech 04/09/2023

Business Modeling: Business modeling involved the business analysis and maps the business system into information flow diagrams. UNIVOTEC Conceptual Modeling: Conceptual modeling involved building a model of the real world in terms of data requirements of the system. The initial set of information and processing requirements are gathered from the users. The process involving developing a conceptual model is called as an entity relationship modeling. The output of this process is developing Entity Relationship Diagrams (ERD) 55 Database Design - B.Tech 04/09/2023

Logical Modeling UNIVOTEC This involve building a model of the real world, express in terms of a particular architecture. This involves determining the contents of the database using the conceptual model as data input and transforming it into architectural data model and we carried out the normalization as a process which used to eliminate certain anomalies and data duplications. If your data model is a Relational Data Model (RDM) then the output become a set of normalize relations. Physical Modeling This involves building a model of the real world express in terms of data structures and access mechanism available in the selected DBMS. The output of this stage is the implementation plan express in Data Definition Language (DDL). 56 Database Design - B.Tech 04/09/2023

UNIVOTEC 57 Database Design - B.Tech 04/09/2023

Data Concepts: UNIVOTEC Entity Any object or event about which someone chooses to collect data or wish to store information is an Entity. An entity may be a person, place, an object or thing- for example, a sales person, a course, a customer, machine or a product. Entities has to be labeled with a singular noun and can be identified as Person : Examples: Employee, Student, Doctor etc. A Place : Examples: Region, Country etc. An Object: Examples: Building, Machine etc. Event: Examples: Registration, Sales, etc. Concepts: Exemples: Course, Account etc. Notation 58 Database Design - B.Tech 04/09/2023

Attribute. UNIVOTEC An attribute is some property or characteristic of an entity. There can be many attributes for each entity. For example, a patient (entity) can have many attributes such as last name, first name, address, Contact Number, City, and so on. PATIENT ( Last Name, First Name, Address, Contact Number, City) 59 Database Design - B.Tech 04/09/2023

Attribute UNIVOTEC Notations Key Attribute Non –Key Attribute Multi value Attribute 60 Database Design - B.Tech 04/09/2023

Example 61 UNIVOTEC Database Design - B.Tech 04/09/2023

Relationship. UNIVOTEC An association or link between two entities depending on the number of entities involve in the relationship Notation: 62 or Database Design - B.Tech 04/09/2023

Data elements or Data Items: UNIVOTEC In a DBMS, data is arranged from the smallest to the largest. These are referred to as data elements or data items; individual piece of data that are joined to produce information. There are two levels of data elements used in the DBMS: physical data representation and logical data representation. 63 Database Design - B.Tech 04/09/2023

Physical data representation: UNIVOTEC Physical data representation refers to how data is stored and retrieved on the computer system's secondary storage. Physical data elements include the bit, byte, and word. These elements are stored in files on some form of magnetic media, such as disk. 64 Database Design - B.Tech 04/09/2023

Logical data representation: UNIVOTEC Logical data representation is an organized method for storing data about an entity in a database. In DBMS logical data elements are characters, fields, records, files and databases. 65 Database Design - B.Tech 04/09/2023

Character: A character is a single symbol, letter, number, or punctuation mark defined in the database. Field: A field is a group of characters that represent an attribute, or characteristic of an entity. Records: A record is a collection of related data items or fields that a DBMS treats as a unit. File: Within the DBMS, file is a collection of related records that serves as a unit of storage. The file holds all of the company's customer's records. UNIVOTEC 66 Database Design - B.Tech 04/09/2023

UNIVOTEC 67 Database Design - B.Tech 04/09/2023