DBS Chapter Four Part One PDF

Chapter Four Database Application Development 1 Database Management Systems  Database Development Life Cycle(DDLC) System Development Life Cycle  Information System (IS): Resources that enable c...

Chapter Four Database Application Development 1 Database Management Systems  Database Development Life Cycle(DDLC) System Development Life Cycle  Information System (IS): Resources that enable collection, management, control, and dissemination of information throughout an organization. Database is fundamental component of IS, and its development and usage should be viewed from the perspective of the wider requirements of the organization.  System development life cycle (SDLC) is a structured project management model that outlines the phases(stages) required to build an IS , from inception to the final result. 2 Database Management Systems   Systems Development Life Cycle (SDLC) gives structure to the challenges of transitioning from the beginning to the end of a project without forgetting a step.  The aim of a system development life cycle is to enable effective production of a high-quality solution that would meet or exceed a client’s expectations throughout all its stages, within the given budget and timelines. Stages of SDLC  SDLC phases include planning, system analysis, system design, development/ implementation, integration and testing, operation and maintenance. Database Development Life Cycle The Database development Life Cycle (DBLC) contains six phases which align with SDLC database Planning , Initial Study(System Analysis), Database Design(System Design), Implementation and Loading(Development and Implementation) , Testing and Evaluation(Integration and Testing), Operation and Maintenance 3 Database Management Systems 4 Database Management Systems  Database Initial Study  Initial study starts by considering the statement of requirements and finishes by producing a system specification. A specification is a formal representation of what a system should do, expressed in terms that are independent of how it may be realized.  The current system’s operation within the company must be examined and the questions how and why the current system fails answered.  The overall purpose of the database initial study is to: Analyze the company situation and collect requirements ,Define problems ,Define objectives ,Define scope and boundaries of the project.  Methods used during the analysis phase for information/requirement gathering: Interviewing end users individually and in a group Questionnaire survey Direct observation Examining different documents like forms, reports, receipts, invoices etc 5 Database Management Systems  Analyze the Company Situation:  The company situation describes the general conditions in which a company operates, its organizational structure, and its mission.  To analyze the company situation, the database designer must discover what the company’s operational components are, how they function, and how they interact.  In this stage functional and non functional Requirements of the database system needs to be identified.(What needs to be accomplished by the system)  Functional requirement describe the desired functionality of the database, including data entry, storage, retrieval, and reporting.  Non-functional Requirements on the other hands specify any constraints or performance expectations, such as security, scalability, and response time 6 Database Management Systems  Define Problems  A problem can be regarded as a difference between the actual situation and the desired situation.  A problem definition usually contains some sort of summarized problem statement followed by a series of issues, or major, independent pieces of the problem.  A problem statement is usually explain the problem that the project will address. In general, a problem statement will outline the negative points of the current situation and explain why this matters  One of the most important goals of any problem statement is to define the problem being addressed in a way that's clear and precise. Define Objectives:  A proposed database system must be designed to help solve at least the major problems identified during the problem discovery process.  the following questions must be addressed : What is the proposed system’s initial objective? Will the system interface / share data with other existing or future systems in the company? 7 Database Management Systems  Define Scope and Boundaries:  The extent of the area or subject matter that something deals with or to which it is relevant is called Scope.  The system’s scope defines the extent of the design according to operational requirements.  The question ,will the database design encompass the entire organization, one or more departments within the organization, or one or more functions of a single department? ,must be addressed  Knowing the scope helps in defining the required data structures, the type and number of entities, the physical size of the database, and so on.  The system boundary is a conceptual line that divides the system under study from 'everything else'. 8 Database Management Systems  Database Design Three main phases for Database Design  Conceptual database design The process of constructing a model of the data used in an enterprise, independent of all Technical/physical considerations.  Logical database design The process of constructing a model of the data used in an enterprise based on a specific data model (e.g. relational), but independent of a particular DBMS and other physical considerations. 9 Database Management Systems   Physical database design The process of producing a description of the implementation of the database on secondary storage; it describes the base relations, file organizations, and indexes design used to achieve efficient access to the data, and any associated integrity constraints and security measures. Conceptual Design Logical Design Design Physical Design 10 Database Management Systems  Implementation and Loading The output of the database design phase is a series of instructions detailing the creation of tables, attributes, domains, views, indexes, security constraints, and storage and performance guidelines. In this phase, all these design specifications are implemented.  Select and Install the DBMS  Create the Database(s)  Load or Convert the Data After the database has been created, the data must be loaded into the database tables. 11 Database Management Systems  Testing and Evaluation  In the design phase, decisions were made to ensure integrity, security, performance, and recoverability of the database.  During implementation and loading, these plans were put into place.  In testing and evaluation, the DBA tests and fine-tunes the database to ensure that it performs as expected. Integrity and security of the data are tested. Physical security , Password security, Access rights, Data encryption etc are tested.  To ensure that the data contained in the database are protected against loss, backup and recovery plans are tested. 12 Database Management Systems  Operation  Once the database has passed the evaluation stage, it is considered to be operational.  At that point, the database, its management, its users, and its application programs constitute a complete information system. Maintenance  The database administrator must be prepared to perform routine maintenance activities within the database.  Some of the required periodic maintenance activities include: Preventive maintenance (backup). Corrective maintenance (recovery). Adaptive maintenance (enhancing performance, adding entities and attributes, and so on). Assignment of access permissions and their maintenance for new and old users. 13 Database Management Systems  Conceptual Database Design with E-R Modeling  Conceptual design revolves around discovering and analyzing organizational and user data requirements  The entity-relationship (E-R) data model perceives the real world as consisting of basic objects, called entities described by their attributes , and relationships among these objects.  It was developed to facilitate database design by allowing specification of an enterprise schema, which represents the overall conceptual structure of a database. The important activities are  Identify Entities , Attributes ,Relationships and Constraints  And based on these components develop the E-R model composed of ER diagrams and description about the Diagram 14 Database Management Systems  Developing an E-R Diagram  Identify Entities (Nouns), Attributes( descriptive words /Adjectives ) , Relationships (Verbs) and constraints (Commanding words )  Develop the basic E-R Model  Validate an E-R Model against requirement of the user.  Present the basic E-R model for review.  Collecting Feedback from the users Note: The process is repeated until the end users and designers agree that the E-R diagram is a representation of the organization’s activities and functions. (Iterative) 15 Database Management Systems  Entity Type(Entity )  An entity is an “object” or “concept” in the real world that is distinguishable from all other objects about which we want to store information.  An entity may be concrete, such as a person or a book, or it may be abstract, such as a course , a loan, or a holiday. Example : For a bank System Employee , Customer , Loan etc… can be taken as entities  An entity type has a set of properties(attributes), and the values for some set of properties may uniquely identify an entity occurrence.  Entity occurrence is a uniquely identifiable member of an entity type. Strong vs Weak Entities Entities can either exist on their own or they can only exist when associated with some other entity type.  16 Database Management Systems   Strong entities can be uniquely identified by its own attributes. the entity’s existence does not depend on any other entity Example: a Dormitory can be uniquely identified by its name and location.  Weak entities cannot be uniquely identified by its own attributes. Needs additional attribute from parent entity to be uniquely identified. Example : a Room in a Dormitory needs the Dormitory information as part of its identity.  Strong Entities are represented by rectangles and a weak entity with a double rectangles in ER Diagrams. Example Strong Weak Entity Entity Name Name Dormitory Dormitory Room 17 Database Management Systems  Attributes  Attributes are descriptive properties possessed by each member / entity occurrence of an entity  The name of an attribute for an entity expresses that the database stores similar information concerning each entity occurrence. however, each entity occurrence may have its own value for each attribute. Example : Possible attributes of the customer are customer_id, customer_name, customer_gender ,customer_ address , customer_ phonenumber, customer _dateofbirth,customer_age etc Possible attributes of the loan entity set are loan-number loan_type and loan_amount. Note Database objects are referenced by their names, and needs to be consistent throughout Database models.Having consistent naming conventions across data models means that developers will need to spend less time looking up the names of tables, views, and columns. 18 Database Management Systems   Each entity occurence has a value for each of its attributes.  For each attribute, there is a set of permitted values, called the domain, or value set, of that attribute. Example : The domain of attribute customer-name might be the set of all text strings of a certain length. Similarly, the domain of attribute loan-number might be the set of all strings of the form “L-n” where n is a positive integer. Attributes are represented by ovals in ER diagrams Attribute Name Example Customer_ gender 19 Database Management Systems  Types of attributes Simple Vs composite attributes.  Simple attributes can not be divided into subparts.  Composite attributes, on the other hand, can be divided into subparts (that is, other attributes).  For example, an attribute customer_address could be structured as a composite attribute consisting of region, zone, and woreda.  Using composite attributes in a design schema is a good choice if a user will wish to refer to an entire attribute on some occasions, and to only a component of the attribute on other occasions. zone woreda region customer_ address 20 Database Management Systems  Single-valued Vs multivalued attributes  single valued attribute is an attribute which can take only one value for a particular occurrence of an entity, Example : customer_gender for a specific customer can be only one value. Such attributes are said to be single valued customer _gender  Multivalued attribute is an attribute that can take more than one value for a specific occurrence of an entity. Example : customer_ phonenumber is a multivalued attribute. A customer may have zero, one, or several phone numbers Multivalued attributes are represented by double oval phone_ number 21 Database Management Systems  Derived attribute  Derived attribute is an attributes whose value can be derived (calculated) from other related attribute  The value of a derived attribute is not stored, but is computed when required Example customer_age can be calculated from customer_date_of_birth and current date In this case date_of_birth is a base attribute, or a stored attribute. Whereas age is derived  Derived attributes are represented by oval with broken outline date_of_ customer birth _age 22 Database Management Systems  Keys  In a database every occurrence of an entity should be uniquely identified.Therefore, No two entities in an entity set are allowed to have exactly the same value for all attributes  A key allows us to identify a set of attributes that suffice to distinguish entities from each other.  Super key is a set of one or more attributes taken collectively to identify uniquely an entity occurrence in the entity set.  Candidate key is a minimal set of attributes that uniquely identifies each occurrence of an entity.  Composite Key is a candidate key that consists of two or more attributes. Example :{customer_id },{customer_id, customer_name, customer_gender }can be taken as super keys and {customer_id }is a candidate key 23 Database Management Systems   Primary key is a candidate key that is chosen by the database designer as the principal means of identifying entities within an entity set.  A key (primary, candidate, and super) is a property of the entity set, rather than of the individual entity occurrences.  Any two entity occurrences are prohibited from having the same value on the primary key attributes at the same time.  The primary key should be chosen such that its attributes always have a value and are never, or very rarely, changed.  Primary key attributes are represented in ER diagrams by underlying their name Note : In case of composite attributes underline all. customer_id 24 Database Management Systems  Relationships  A relationship is a meaningful association among entities Example customer and loan entity types are related , a costumer borrows a loan. Relationships are represented by diamonds in ER Diagrams borrows  Relationships can be Binary(between two entity types ) , Ternary( between three entity types ) and n-ary(between n entity types)  Most of the relationship sets in a database system are binary. Occasionally, however, relationship sets involve more than two entity sets. 25 Database Management Systems  Example A supplier can supply many parts in a particular project Supplier Supplies Project Part  Entity sets of a relationship need not be distinct. There are relationships within an entity(recursive entity) The number of entity sets that participate in a relationship is called degree of the relationship. The degree of a binary relationship is 2; and for a ternary relationship it is 3 26 Database Management Systems  Structural Constraints(Cardinalities and Participation) Main type of constraint on relationships is called Multiplicity Multiplicity is made up of two types of restrictions on relationships: cardinality and participation. Mapping cardinality( cardinality ratios ) express maximum number (or range) of possible occurrences of an entity type that may relate to a single occurrence of an associated entity type through a particular relationship.  For a binary relationship set R between entity types A and B, the mapping cardinality will be one of the following: One to one(1:1) : An entity occurrence in A is associated with at most one entity occurrence in B, and an entity occurrence in B is associated with at most one entity occurrence in A. One to many (1:*) : An entity occurrence in A is associated with any number (zero or more) of entity occurrences in B. An entity occurrence in B, however, can be associated with at most one entity occurrence in A. Many to one (*:1): An entity occurrence in A is associated with at most one entity occurrence in B. An entity occurrence in B, however, can be associated with any number (zero or more) of entity occurrences in A. Many to many (*:*): An entity occurrence in A is associated with any number (zero or more) of entity occurrences in B, and an entity occurrence in B is associated with any number (zero or more) of entity occurrences in A. 27 Database Management Systems  Example (1:1) cardinality Staff Manages Branch relationship type 28 Database Management Systems 7/3/2024  Example (1:* ) cardinality Staff Oversees PropertyForRent relationship type 29 Database Management Systems 7/3/2024  Example (*:*) cardinality Newspaper Advertises PropertyForRent relationship type 30 Database Management Systems 7/3/2024   Participation : Determines whether all or only some entity occurrences participate in a relationship.  The participation of an entity set E in a relationship set R is said to be total if every entity in E participates in at least one relationship in R.Participation is mandatory.  If only some entities in E participate in relationships in R, the participation of entity set E in relationship R is said to be partial. Participation is optional  Example 1 : Every loan entity should be related to at least one customer through the borrows relationship. Therefore the participation of loan in the borrows relationship is total. In contrast, an individual can be a bank customer whether or not he/she has a loan with the bank. Hence, it is possible that only some of the customer entities are related to the loan entity set through the borrows relationship and the participation of customer in the borrower relationship set is therefore partial  Total participation is indicated with a double lines or Bold line Customer borrows Loan 31 Database Management Systems  Cardinality describes maximum number of possible relationship occurrences for an entity participating in a given relationship type. Whereas Participation ddetermines whether all or only some entity occurrences participate in a relationship. 32 Database Management Systems 7/3/2024 33 Database Management Systems 34 Database Management Systems 35 Database Management Systems  One-to-One 36 Database Management Systems  Weak relationship  Weak Relationships are connections between a weak entity and its owner.  A relationship where child entity existence is dependent on parent, and PK of Child Entity contains PK component of Parent Entity.  Identifying relationships exist when the primary key of the parent entity is included in the primary key of the child entity. On the other hand, a non-identifying relationship exists when the primary key of the parent entity is included in the child entity but not as part of the child entity’s primary key WeakR elation Weak relationship is represented by a double diamond. ship 37 Database Management Systems 21. An associative entity  is a term used in relational and entity–relationship theory.  A relational database requires the implementation of a base relation to resolve many- to-many relationships. A base relation representing this kind of entity is called, an associative entity  An associative entity is an entity that associates two other entities in a many to many relationship 38 Database Management Systems  Problems with E-R models  A lot of data semantics can (and should) be captured. But some constraints cannot be captured in ER diagrams.  ER design is subjective It can not express certain constraints; so it need refinement Analyzing alternatives can be tricky, especially for a large enterprise. There are often several ways to model the reality to be described, it is not possible to say that one way is right or wrong; some of these decisions are simply design matter  Sometimes it is hard to decide whether something should be represented as an entity, an attribute or a relationship.  Problems may also arise when designing a conceptual data model called connection traps.  Two main types of connection traps are called fan traps and chasm traps. Often due to a misinterpretation of the meaning of certain relationships. 39 Database Management Systems   Fan Trap : Where a model represents a relationship between entity types, but pathway between certain entity occurrences is ambiguous.  Occurs when we have two or more 1..* relationships fanning out from same entity type Example : At which branch office does staff number SG37 work? 40 Database Management Systems 7/3/2024  Restructuring ER model to remove Fan Trap Database Management Systems SG37 works at branch B003. 41 7/3/2024  Chasm Trap :Where a model suggests the existence of a relationship between entity types, but pathway does not exist between certain entity occurrences that creates information gap. Occurs when we have one or more entities with partial participation forming the path way between related entities. Example At which branch office is property PA14 available? 42 Database Management Systems 7/3/2024  ER Model restructured to remove Chasm Trap 43 Database Management Systems 7/3/2024  Restructured ER Model with Chasm Trap Removed 44 Database Management Systems 7/3/2024  Enhanced E-R model features Although most properties of entities and relationships can be expressed using the basic modeling constructs, some of the properties are difficult to express and understand. That’s why there are some extensions to the ER model. Basic concepts of ER modelling are not sufficient to represent requirements more complex applications.  Semantic concepts are incorporated into the original ER model and called the Enhanced Entity-Relationship (EER) model.  Examples of additional concept of EER model is called specialization / generalization. 45 Database Management Systems  Specialization  An entity set may include subgroupings of entities that are distinct in some way from other entities in the set. For instance, a subset of entities within an entity set may have attributes that are not shared by all the entities in the entity set. Example : Consider an entity set person, with attributes name, gender, address. A person may be further classified as one of the following: customer employee  Each of these person types is described by a set of attributes that includes all the attributes of person entity plus possibly additional attributes 46 Database Management Systems   The process of designating subgroupings within an entity set is called specialization. The specialization of person allows us to distinguish among persons according to whether they are employees or customers.  In terms of an E-R diagram, specialization is depicted by a triangle component labeled ISA.  The label ISA stands for “is a” and represents, example, a customer “is a” person.  The ISA relationship may also be referred to as a superclass-subclass relationship. Higher- and lower-level entity sets are depicted as regular entity sets—that is, as rectangles containing the name of the entity set. Note :  Superclass : An entity type that includes one or more distinct subgroupings of its occurrences.  Subclass : A distinct subgrouping of occurrences of an entity type. 47 Database Management Systems 48 Database Management Systems  Generalization  The refinement from an initial entity set into successive levels of entity subgroupings represents a top-down design process in which distinctions are made explicit.  The design process may also proceed in a bottom-up manner, in which multiple entity sets are synthesized into a higher-level entity set on the basis of common features. The database designer may have first identified a customer entity with the attributes name, street, city, and customer-id, and an employee entity with the attributes name, street, city, employee-id, and salary. There are similarities between the customer entity set and the employee entity set in the sense that they have several attributes in common.  This commonality can be expressed by generalization, which is a containment relationship that exists between a higher-level entity set and one or more lower-level entity sets. 49 Database Management Systems   For all practical purposes, generalization is a simple inversion of specialization.  Whether a given portion of an E-R model was arrived at by specialization or generalization, the outcome is basically the same.  Specialization stems from a single entity ; it emphasizes differences among entity occurrences within the set by creating distinct lower-level entities. These lower-level entities may have attributes, or may participate in relationships, that do not apply to all the entity occurrences in the higher-level entity set. Indeed, the reason a designer applies specialization is to represent such distinctive features. If customer and employee neither have attributes that person entities do not have nor participate in different relationships than those in which person entities participate, there would be no need to specialize the person entity. Generalization proceeds from the recognition that a number of entities share some common features (namely, they are described by the same attributes and participate in the same relationship sets). On the basis of their commonalities, generalization synthesizes these entities into a single, higher-level entity. 50 Database Management Systems   Generalization is used to emphasize the similarities among lower-level entity sets and to hide the differences; it also permits an economy of representation in that shared attributes are not repeated. Inheritance  A crucial property of the higher- and lower-level entities created by specialization and generalization is inheritance.  Attribute Inheritance :The attributes of the higher-level entity(superclass) are said to be inherited by the lower-level entities (subclass). For example, customer and employee inherit the attributes of person  Relationship Inheritance : A lower-level entity set (or subclass) also inherits participation in the relationship in which its higher-level entity (or superclass) participates.  In general Specialization is a process of maximizing differences between members of an entity by identifying their distinguishing characteristics. Whereas Generalization is a process of minimizing differences between entities by identifying their common characteristics. 51 Database Management Systems  Constraints on Generalizations/ Specialization To model an enterprise more accurately, the database designer may choose to place certain constraints on a particular generalization.  Membership constraint :One type of constraint involves determining which entities can be members of a given lower-level entity set, Such membership may be one of the following: Condition-defined. In condition-defined lower-level entities , membership is evaluated on the basis of whether or not an entity satisfies an explicit condition or predicate. Since all the lower-level entities are evaluated on the basis of the same attribute, this type of generalization is said to be attribute-defined. User-defined. User-defined lower-level entity sets are not constrained by a membership condition; rather, the database user assigns entities to a given entity set. 52 Database Management Systems   Disjoint constraints : The lower-level entity sets may be one of the following: Disjoint: A disjointness constraint requires that an entity occurrence belong to no more than one lower-level entity. example, an occurrence of a Student entity can satisfy only one condition for the student-type attribute; it can be either a Postgraduate or undergraduate student, but cannot be both. 53 Database Management Systems  Overlapping: In overlapping generalizations, the same entity may belong to more than one lower-level entity set within a single generalization. Example university member entity occurrence can be a university Staff and university Student 54 Database Management Systems   Participation (Completeness constraints): specifies whether or not an entity occurrence in the higher-level entity set must belong to at least one of the lower-level entity sets within the specialization/generalization.  Determines whether every member in superclass must participate as a member of a subclass. May be mandatory or optional.  This constraint may be one of the following: Total generalization or specialization. Each higher-level entity occurrence must belong to a lower-level entity. Example : a student must be postgrad or undergrad. 55 Database Management Systems   Partial generalization or specialization. Some higher-level entities may not belong to any lower-level entity set. Example some board members of the university are neither student nor staff 56 Database Management Systems   Total generalization can be specified in an E-R diagram by using a double line to connect the box representing the higher-level entity set to the triangle symbol. (This notation is similar to the notation for total participation in a relationship.)  The completeness constraint for a generalized higher-level entity set is usually total. When the generalization is partial, a higher-level entity occurrence is not constrained to appear in a lower-level entity set. 57 Database Management Systems  Aggregation  Aggregation is an abstraction through which we can represent relationships as higher level entity sets.  An ER diagram is not capable of representing relationship between an entity and a relationship which may be required in some scenarios. In those cases, a relationship with its corresponding entities is aggregated into a higher level entity.  Aggregation allows us to treat a relationship set as an entity set for purposes of participation in (other) relationships.  It is possible to use aggregated entity set as one single unit without having to pay attention to its internal structure. 58 Database Management Systems  Design choices  In ER Conceptual Design the notions of entities and their relationships are not precise, and it is possible to define a set of entities and the relationships among them in a number of different ways.  Should a concept be modelled as an entity or an attribute? Example Should address be an attribute of customer or an entity (connected to customer by a relationship)? Depends upon the use we want to make of address information, and the semantics of the data : If we have several addresses per customer, address must be an entity If the structure (Region, Zone, etc.) is important, e.g., we want to retrieve customer in a given Zone, address must be modelled as an entity 59 Database Management Systems   Should a concept be modelled as an entity or a relationship? Example In this case a product can not be offered with different prices but taking price as an entity it is possible to offer with different prices  Identifying relationships: Binary or ternary? Some relationships that appear to be non binary could actually be better represented by several binary relationships In some cases two binary relationships are better than one ternary relationship. 60 Database Management Systems  ER design decisions  Should I use an attribute or an entity to represent an object?  Should a concept from the real world be represented as an entity or as a relationship?  Should I use a ternary (3) or a set of binary relationships?  Should I use a strong or a weak entity?  When is it worth the while to use specialization and generalization?  When should I use aggregation? 61 Database Management Systems