DBMS Fundamentals

1. DBMS Fundamentals

2. Module Outline 4. Structured Query Language 1. What is a Database System 2. Types of Database Systems 3. Creating a Database Environment 5. Internal Management 6. Database Trends

3. What is a Database System • A Database System is essentially a computerized record-keeping system. • A database-management system (DBMS) consists of a collection of interrelated data and a set of programs to access those data. • Database systems are designed to manage large volume of information

4. File Organization : Terms and Concepts • Database: Group of related files • File: Group of records of same type • Record: Group of related fields • Field: Group of words or a complete number • Byte: Group of bits that represents a single character • Bit: Smallest unit of data; binary digit (0,1) Data Hierarchy in a Computer System

5. File Organization : Terms and Concepts • Entity: Person, place, thing, event about which information is maintained • Attribute: Description of a particular entity • Key Field: Identifier field used to retrieve, update, sort a record

6. File Organization : Terms and Concepts Problems with the Traditional File Environment • Data redundancy • Program-Data dependence • Lack of flexibility • Poor security • Lack of data-sharing and availability • No concurrency control Traditional File Processing

7. DBMS and its Advantages • A Database Management System is a collection of programs that enables users to create and maintain a database. It is a general purpose software system that facilitates processes of defining, constructing and manipulating databases for various applications. • Advantages of Database approach: • Controlling Redundancy • Restricting Unauthorized access • Providing persistent storage for program objects and data structures • Permitting inference and actions using deduction rules • Providing multiple user interface • Representing complex relationships among data • Enforcing integrity constraints • Providing backup and recovery

8. Figure 7-4 Database Management System (DBMS) • Acts as an interface between application programs and physical data files. • Separates logical and physical views of data • Eliminates redundancy of data • Creates and maintains databases • Enforces security of data

9. DBMS Architecture • Internal Schema : Describes physical storage structure of database • Conceptual Schema : Describes structure of whole database for a community of users. • External Schema : Each view describes that part of database that a particular user requires, and hides the rest.

10. DBMS Architecture • Data Independence • Logical data independence : capacity to change conceptual schema without having to change external schema. • Physical data independence : capacity to change internal schema without changing conceptual schema.

11. Functions of DBMS • Data definition : • Specifies contentand structure of database and defines each data element • Data manipulation : • Manipulates data in a database • Data security and integrity : • Monitors user requests and rejects any unauthorized attempts • Data recovery and concurrency : • Enforces certain controls for recovery and concurrency • Data dictionary: • Stores definitions of data elements, and data characteristics • Performance : • Functions should be performed efficiently

12. Requirements of a DBMS • Key elements in a database environment: • Data Administration • Data Planning and Modeling Methodology • Database Technology and Management • Users

13. 2.0 Types of Databases • Learning Objective: At the end of this Topic you will be able to – • Explain briefly the various types of Database Systems • Relational DBMS • Hierarchical DBMS • Network DBMS • Object-Oriented Databases

14. Relational Database Model • Represents data as two-dimensional tables called relations • Relates data across tables based on common data element Examples: DB2, Oracle, MS SQL Server

15. Three Basic Operations in a Relational Database • Select: Creates subset of rows that meet specific criteria • Join: Combines relational tables to provide users with information • Project: Enables users to create new tables containing only relevant information

16. Three Basic Operations in a Relational Database JOIN SELECT PROJECT

17. Hierarchical Database Model • It is a pointer based model • Organizes data in a tree-like structure • Stores data in tables and views relationships as links • Supports one-to-many parent-child relationships • Prevalent in large legacy systems

18. Network DBMS • Depicts data logically as many-to-many relationships • Organizes data in tables and views relationships as links • It is also a pointer based model • Organizes data in arbitrary graphs

19. Hierarchical and Network DBMS Some of the Disadvantages • Outdated • Complex pointer based organization • Less flexible compared to RDBMS • Lack support for ad-hoc and English language-like queries

20. Object-Oriented Databases • Object-oriented DBMS: Stores data and procedures as objects that can be retrieved and shared automatically • Object-relational DBMS: Provides capabilities of both object-oriented and relational DBMS

21. Types of Databases : Summary • In a relational database the data is perceived as tables (and nothing but tables) by the user • The relational operators available are used to manipulate the data in the tables

22. 3.0 Creating a DB environment • Learning Objective: • At the end of this Topic you will – • Have the ability to model an application system based on the E-R Modeling approach. • Understand the Relational Database concepts like Normalization, Data Integrity, Relational Operations like Union, Intersection etc. • Be able to Design Relational Databases based on E-R Models or System Requirements for an application.

23. Introduction to Data Modeling • What is Data Modeling? A technique for analyzing requirements and for identifying the information needs of an organization • Why Data Modeling is important? Cannot build a good system without knowing what data needs to be captured and how it needs to be organized

24. Introduction to Data Modeling • An Overview : • Conceptual representation of the data structures required by a database • Data structures include the data objects, the associations between data objects, and the rules which govern operations on the objects • Focuses on what data is required and how it should be organized • Independent of hardware or software constraints • Data Model And Database Design: • Data Model is to a Database what a Building plan or a blueprint is to a Building • A Database Design translates a data model into a database • A Data Model is the conceptual design of a database

25. E-R Modeling • Originally proposed by Peter Chen (1976) • Views the real world as entities and relationships • Key component is the E-R Diagram • Most common model used for designing relational databases • Entity- An identifiable object or concept of significance • Attribute- Property of an entity or relationship • Relationship- An association between entities • Identifier- one or more attributes identifying an instance • (occurrence) of an entity

26. Entity relationship diagram

27. E-R Modeling Entity EMPLOYEE DEPARTMENT has works for • Name • Emp Id. • Dept No. • Name Attributes Relationship Identifier

28. E-R Modeling • Entity • Any object or thing of significance about which data needs to be collected and maintained • Could be • Concrete or tangible like a person or a building • Abstract like a concept or activity • Analogous to a table in a relational database Examples: EMPLOYEES, PROJECTS, INVOICES

29. E-R Modeling • Entity Rules • Any thing or object may only be represented by one entity. Entities are mutually exclusive in all cases. • Each entity must be uniquely identifiable. Each instance (occurrence) of an entity must be separate and distinctly identifiable from all other instances of that type of entity. • Entity Classification and Types • Classified as dependent and independent • An independententity is one that does not rely on another for identification • A dependent entity is one that relies on another for identification • In some, methodologies, the terms used are strong and weak, respectively

30. E-R Modeling • Entity Classification and Types • Fundamental entity - An entity that exists and is of interest in its own right. Generally, most entities in the data model are fundamental entities. Example :Department and Employee are both fundamental entities • Special Entity Types • Associative Entity -Used to associate two entities in order to reconcile a many-many relationship • Sub-type/super-type- Used in generalization hierarchies to represent a subset of instances of their of parent entity

31. E-R Modeling Example of Associative entity: ITEM ORDER for a has ORDER LINE appears on belongs to

32. E-R Modeling • Generalization Hierarchies • Generalization occurs when two or more entities represent categories of the same real-world object. Example: CAR and TRUCK represent categories of the same entity, VEHICLE is the super-type; CAR and TRUCK would be the subtypes

33. E-R Modeling • Generalization Hierarchies • Form of abstraction that specifies that two or more entities that share common attributes can be generalized into a higher level entity type called a super-type or generic entity. • The lower-level of entities become the sub-type, or categories, to the super-type. Sub-types are dependent entities.

34. PERSON FACULTY STAFF STUDENT E-R Modeling • Generalization Hierarchies • Sub-types can be either mutually exclusive (disjoint) or overlapping (inclusive) • In an overlapping hierarchy an entity instance can be part of multiple subtypes Example: Entity PERSON represents people at a university. It has three subtypes, FACULTY, STAFF, and STUDENT. A STAFF member could also be registered as a STUDENT

35. E-R Modeling • Generalization Hierarchies • In a disjoint hierarchy, an entity instance can be in only one subtype. Example:Entity EMPLOYEE, may have two subtypes, CLASSIFIED and WAGES. An employee may be one type or the other but not both

36. PERSON FACULTY STUDENT UNDERGRAD GRADUATE Generalization Hierarchies - Nested E-R Modeling

37. FName MI Student Name DOB LName Simple Composite E-R Modeling • Attribute • Attributes describe a property or a characteristic of an entity • A particular instance of an attribute is a value. For example “John Doe” is one value of the attribute Name. • Simple attribute • Contains only atomic values • Composite attribute • Has component attributes

38. Math Multi-valued Module Single-valued Id Student Physics E-R Modeling • Attribute Classification • Single-valued attribute • Has exactly one value per instance of an entity • Multi-valued attribute • Contains repeating values per instance of an entity

39. E-R Modeling • Identifiers and Descriptors • Attributes can be classified as identifiers or descriptors • Identifiers, more commonly called keys, uniquely identify an instance of an entity. • A descriptor describes a non-unique characteristic of an entity instance. • An Example : • Entity: Employee • Unique Identifier: Employee No. • Descriptor: Name, DOJ, DOB

40. E-R Modeling • Relationship • Represents an association between two or more entities • Examples • - Employees work for Departments • - Departments manage one or more projects • - Employees are assigned to projects • - Projects have sub-tasks • - Orders have line items • Defined in terms of: • - Degree • - Connectivity • - Cardinality • - Direction • - Type • - Existence

41. E-R Modeling • Degree • Number of entities associated with the relationship • Binary relationships, the association between two entities is the • most common type in the real world. N-ary is the general form for • degree n • Connectivity • Mapping of associated entity instances in the relationship. • The values of connectivity are "one" or "many”. • Cardinality • Actual number of related occurrences for each of the two entities. • The basic types of connectivity for relations are: one-to-one, one-to-many, and many-to-many.

42. E-R Modeling • Connectivity and Cardinality • A one-to-one (1:1) relationship is when at most one instance of a entity A Is associated with one instance of entity B. • For example: • Employees in the company are each assigned their own office. For each • Employee there exists a unique office and for each office there exists a • unique employee. • A one-to-many (1:N) relationships is when for one instance of entity A, there are zero, one, or many instances of entity B, but for one instance of entity B, there is only one instance of entity A. • An example : • A department has many employees each employee is assigned to one department

43. E-R Modeling • Connectivity and Cardinality • A many-to-many relationship, is when for one instance of entity A, there are zero, one, or many instances of entity B and for one instance of entity B there are zero, one, or many instances of entity A. • An example is: • employees can be assigned to no more than two projects at the • same time; Project must have assigned at least three employees

44. E-R Modeling • Direction • Indicates the originating entity of a binary relationship. The entity from which a relationship originates is the parent entity; the entity where the relationship terminates is the child entity. • Type • The direction of a relationship is determined by its connectivity. • Identifying and Non-identifying • An identifying relationship is one in which one of the child entities is also dependent entity. • A non-identifying relationship is one in which both entities are independent.

45. E-R Modeling • Existence • Denotes whether the existence of an entity instance is dependent • upon the existence of another, related, entity instance. • Defined as either mandatory or optional. • Mandatory and optional relationship • If an instance of an entity must always occur for an entity to be included in a • relationship, then it is mandatory. If the instance of the entity is not required, it • is optional. Example: Mandatory : Every project must be managed by a single department Optional : Employees may be assigned to work on projects

46. E-R Modeling • E-R Notation • No standard notation • Original notation by Chen • Common notations are: Bachman, crow's foot, and IDEFIX • All styles represent entities as rectangular boxes and relationships as lines connecting boxes • Each style uses a special set of symbols to represent the cardinality of a connection

47. E-R Modeling • Entities • Represented by labeled rectangles • The label is the name of the entity • Entity names should be singular nouns. • Relationships • Represented by a solid line connecting two entities. • Name written above the line • Relationship names should be verbs Employee Works for Department

48. E-R Modeling • Employee • EmpID • EmpName • Attributes • Listed inside the entity rectangle • Underlined • Names should be singular nouns • Cardinality • Many is represented by a line ending in a crow's foot. If omitted, cardinality is one • Existence • Represented by placing a circle or a perpendicular bar on the line • Mandatory existence is shown by the bar next to the entity for an instance that is required • Optional existence is shown by placing a circle next to the entity that is optional

49. E-R Modeling : Assignment How to create an E-R Model from Requirements ? Step 1: Identify Entities • Entities are things people talk about, record information about and do work on – by definition • Any keyword (noun) is a candidate • Identify generic object from reference to instances or occurrences • Combine synonyms to represent a single entity An Example : Purchase Order - System Requirements A buyer creates a purchase order (PO) as and when the need arises. A PO is for a Specific vendor. A PO has one or more line items. A buyer cannot create a PO of Total value more than his approval limit. A PO can be sent to the vendor by mail, fax, EDI. A PO can be canceled before it is submitted. A PO can be linked to a sales order…

50. E-R Modeling Step 1: Identify Entities • Entities Purchase Order (PO) Buyer? Vendor Line Items Sales Order Approval Limit? • Buyercharacterizes a PO • Approval Limit characterizes a Buyer What does it tell us? • Approval Limit is not an entity • Buyer is an entity • Approval Limit is an attribute of the entity Buyer