7.1 The Information system

(1)

Chapter 7. Database Planning, Design and Administration

• Last few decades have seen proliferation of software applications, many requiring constant maintenance involving:

– correcting faults,

– implementing new user requirements, – modifying software to run on new or upgraded

platforms.

• As a result, many major software projects were

– late,

– over budget, – unreliable, – difficult to maintain, – performed poorly.

Chapter 7. Database Planning, Design and Administration

• In late 1960s, led to ‘software crisis’, now refer to as the ‘software depression’. There are several major reasons for failure of software projects including:

– lack of a complete requirements specification;

– lack of appropriate development methodology;

– poor decomposition of design into manageable components.

• As a solution, a structured approach to development was proposed called information systems lifecycle or software development lifecycle.

NOTES

Principle and Technology of Database

Chapter 7. Database Planning, Design and Administration

• In this chapter, we present an overview of the database application lifecycle, and describe each stage of the database application in more detail.

• 7.1 The Information System Lifecycle

• 7.2 The Database application Lifecycle

• 7.3 Database Planning

• 7.4 System Definition

• 7.5 Requirements Collection and Analysis

• 7.6 Database Design

NOTES

Chapter 7. Database Planning, Design and Administration

• 7.7 DBMS Selection

• 7.8 Application Design

• 7.9 Prototyping

• 7.10 Implementation

• 7.11 Data Conversion and Loading

• 7.12 Testing

• 7.13 Operational Maintenance

• 7.14 Case Tools

• 7.15 Data Administration and Database Administration

NOTES

Chapter 7. Database Planning, Design and Administration

• Main components of an information system.

• Main stages of database application lifecycle.

• Main phases of database design: conceptual, logical, and physical design.

• Benefits of CASE tools.

• How to evaluate and select a DBMS.

• Distinction between data administration and database administration.

• Purpose and tasks associated with data administration and database administration.

NOTES

7.1 The Information system

• Information System: The resources that enable collection, management, control, and dissemination of information throughout an organization.

• The stages in the lifecycle of an I.S. include:

planning, requirements collection and analysis, design, prototyping, implementation, testing, conversion, and operational maintenance.

• The Database is fundamental component of I.S.,

and its development and usage should be

viewed from perspective of the wider

requirements of the organization.

(2)

7.2 The Database Application Lifecycle

• The database application lifecycle is inherently associated with the lifecycle of the information system.

• The stages of the database application lifecycle are shown in Figure in next page.

– Database planning – System definition

– Requirements collection and analysis – Database design

– DBMS selection (optional)

7.2 The Database Application Lifecycle – Application design

– Prototyping (optional) – Implementation

– Data conversion and loading – Testing

– Operational maintenance.

• It is important to recognised that the stages of the database application lifecycle are not strictly sequential, but involve some amount of repetition of previous stages through feedback loops.

NOTES

7.2 The Database Application Lifecycle

Database Planning System Definition

Requirement Collection and Analysis

Conceptual database design

Logical database design Physical database design

DBMS Selection Application Design

Prototyping Implemtntation

Data Conversion & Loading Testing Operational Maintenance Database Design

NOTES

7.3 Database Planning

• Database Planning: The management activities that allow the stages of the database application to be realized as efficiently and effectively as possible.

• Database planning must be integrated with the overall IS strategy of the organization. There are three main issues involved in formulating an IS strategy, which are:

– Identification of enterprise plans and goals with subsequent determination of information systems needs;

– Evaluation of current information systems to determine existing strengths and weakness;

– Appraisal of IT opportunities that might yield competitive advantage.

NOTES

7.3 Database Planning

• Mission statement for the database project defines major aims of database application.

– Those driving database project within the organization (such as Director or Owner) normally define the mission statement.

– Mission statement helps clarify purpose of the database project and provides clearer path towards the efficient and effective creation of required database application.

– Once mission statement is defined, mission objectives are defined.

NOTES

7.3 Database Planning

– Each objective should identify a particular task that the database must support.

– May be accompanied by some additional information that specifies the work to be done, the resources with which to do it, and the money to pay for it all.

• Database planning should also include development of standards that govern:

– how data will be collected, – how the format should be specified,

– what necessary documentation will be needed,

– how design and implementation should proceed.

(3)

7.3 Database Planning

• Example:

• A mission statement for DreamHome

– We begin the conducting interviews with the Director and any other appropriate staff, as indicated by the Director. Examples of typical questions we might ask include:

“What is the purpose of your Company?”

“Why do you feel that you need a database?”

“How do you know that a database will solve your problem?”

– For example, the database developer may start the interview by asking the Director of DreamHome the following questions:

Database Developer “What is the purpose of your Company?”

Director “We offer a wide range of high quality properties for rent to clients registered at our branches throughout the UK. Our ability to offer quality properties, of course, depends upon the services we provide to property owners. We provide a highly professional service to property owners to ensure that properties are rented out for maximum return”.

7.3 Database Planning

• A mission statement for DreamHome

Database Developer “Why do you feel that you need a database?”

Director “ To be honest we can’t cope with our own success.

Over the past few years, we’ve opened several branches in most of the main cities of the UK, and at each branch we now offer a larger selection of properties to a growing number of clients. However, this success has been accompanied with increasing data management problems, which means that the level of service we provide is falling.

Also, there’s a lack of cooperation and sharing of information between branches, which is a very worrying development.”

Database Developer “How do you know that a database will solve your problem?”

Director “All I know is that we are drowning in paperwork. We need something that will speed up the way we work by automating a lot of the day-to-day tasks that seem to take forever these days. Also, I want the branches to start working together. Databases will help to achieve this, won’t they?”

NOTES

7.3 Database Planning

• A mission statement for DreamHome

– Responses to these types of questions should help to formulate the mission statement. An example mission statement for the DreamHome database application is shown in Figure 10.8. When we have a clear and unambiguous mission statement that the staff of DreamHome agree with, we move on to define the mission objectives.

• Please everyone see additional document

“Dreamhome_case” to know about the details.

NOTES

7.3 Database Planning

NOTES

7.3 Database Planning

NOTES

7.3 Database Planning

(4)

7.3 Database Planning

NOTES

7.3 Database Planning

NOTES

7.3 Database Planning

NOTES

7.4 System Definition

• System Definition: Describes the scope and boundaries of the database application and the major user views.

• User view defines what is required of a database application from perspective of:

– a particular job role (such as Manager or Supervisor) or

– enterprise application area (such as marketing, personnel, or stock control).

• Database application may have one or more user views.

• Identifying user views helps ensure that no major users of the database are forgotten when developing requirements for new application.

NOTES

7.4 System Definition

• User views also help in development of complex database application allowing requirements to be broken down into manageable pieces.

• We present a diagram that represents the scope

and boundaries of the DreamHome database

application in additional documents.

(5)

7.4 System Definition

7.5 Requirements Collection and Analysis

• Requirements collection and analysis: The process of collecting and analyzing

information about the part of organization to be supported by the database application, and using this information to identify users’

requirements of new system.

• Information is gathered for each major user view including:

– a description of data used or generated;

– details of how data is to be used/generated;

– any additional requirements for new database application.

• Information is analyzed to identify requirements to be included in new database application.

NOTES

7.5 Requirements Collection and Analysis

• Another important activity is deciding how to manage database application with multiple user views. Three main approaches:

– centralized approach;

– view integration approach;

– combination of both approaches.

• We present the DreamHome – Requirements Collection and Analysis in additional documents.

• Centralized approach

– Requirements for each user view are merged into a single set of requirements.

– A global data model is created based on the merged requirements (which represents all user views).

NOTES

7.5 Requirements Collection and Analysis

• The centralized approach to managing multi user views 1 to 3.

NOTES

7.5 Requirements Collection and Analysis

• View integration approach

– Requirements for each user view are used to build a separate data model to represent that user view.

– The view integration approach involves leaving the requirements for each user view as separate lists of requirements.

– Data model representing single user view is called a

local data model, and is composed of diagrams and

documentation describing requirements of a particular user view of database.

– Local data models are then merged to produce a

global data model, which represents all user views

for the database.

NOTES

7.5 Requirements Collection and Analysis

• The view integration approach to managing

multiple user views 1 to 3

(6)

7.5 Requirements Collection and Analysis

Director Manager Supervisor Assistant

branch X X

staff X X X

property for rent X X X X

owner X X X X

client X X X X

property viewing X X

lease X X X X

newspaper X X

7.6 Database Design

• Database Design: Process of creating a design for a database that will support the enterprise’s operations and objectives.

• Major aims:

– Represent data and relationships between data required by all major application areas and user groups.

– Provide data model that supports any transactions required on the data.

– Specify a minimal design that is appropriately structured to achieve stated performance

requirements for the system (such as response times).

NOTES

7.6 Database Design

• In this section we present an overview of the main approaches to database design.

• We also discuss the purpose and use of data modeling in database design.

• We then describe the three phases of database design, namely conceptual logical, and physical design.

• 7.6.1 approaches to Database Design

• 7.6.2 Data Modeling

• 7.6.3 Phases of Database Design

NOTES

7.6.1 Approaches to Database Design

• Approaches include:

– Bottom-up: beginning at fundamental level of

attributes, which are grouped into relations.

– Top-down: starting with the development of data

models that contain a few high-level entities and relationships and then identifying lower-level entities, relationships, and the associated attributes.

– Inside-out: related to bottom-up approach but

differing by first identifying a set of major entities and then spreading out to consider other entities, relationships, and attribute with those first identified.

– Mixed: using both bottom-up and top-down approach.

NOTES

7.6.2 Data Modeling

• Main purposes of data modeling include:

– to assist in understanding the meaning (semantics) of the data;

– to facilitate communication about the information requirements.

• Building data model requires answering questions about entities, relationships, and attributes.

• A data model ensures we understand:

– each user’s perspective of the data;

– nature of the data itself, independent of its physical representations;

– use of data across user views.

NOTES

7.6.2 Data Modeling

• Criteria for data models

(7)

7.6.3 Phases of Database Design

• Three phases of database design:

– Conceptual database design – Logical database design – Physical database design.

• Conceptual database design: The process of constructing a model of the information used in an enterprise, independent of all physical considerations.

– Data model is built using the information in users’

requirements specification.

– Source of information for next logical design phase.

7.6.3 Phases of Database Design

• Step: Conceptual database design

– Step 1 Identify entity types – Step 2 Identify relationship types

– Step 3 Identify and associate attributes with entity or relationship types

– Step 4 Determine attribute domains

– Step 5 Determine candidate and primary key attributes – Step 6 Consider use of enhanced modeling concepts (optional step) – Step 7 Check model for redundancy

– Step 8 Validate local conceptual model against user transactions – Step 9 Review local conceptual data model with user

• Example for conceptual design: see P327

NOTES

7.6.3 Phases of Database Design

• Logical database design: the process of constructing a model of the information used in an enterprise based on a specific data model (e.g. relational), but independent of a particular DBMS and other physical considerations.

– Conceptual data model is refined and mapped on to a logical data model.

– The logical data model is based on the target data model for the database (such as relational data model).

– The logical model also serves an important role during the operational maintenance stage to application lifecycle.

NOTES

7.6.3 Phases of Database Design

• Step: Logical database design for the relational model

– 1. Build and validate local logical data model for each view

Step 1.1 Remove features not compatible with the relational model (optional step)

Step 1.2 Derive relations for local logical data model

Step 1.3 Validate relations using normalization

Step 1.4 Validate relations against user transactions

Step 1.5 Define integrity constraints

Step 1.6 Review local logical data model with user – 2. Build and validate global logical data model

Step 2.1 Merge local logical data models into global model

Step 2.2 Validate global logical data model

Step 2.3 Check for future growth

Step 2.4 Review global logical data model with users

• Example for logical design: see P342

NOTES

7.6.3 Phases of Database Design

• Physical database design: The process of producing a description of the database implementation on secondary storage. It describes the base relations, file

organizations, and indexes used to achieve efficient access to the data, any associated integrity constraints and security measures.

– Physical design is tailored to a specific DBMS system.

– The main aim of physical design is to describe how we intend to physically implement the logical design.

– There is feedback between physical and logical design.

Because decisions are taken during physical design for improving performance that may affect the structure of the logical data model.

NOTES

7.6.3 Phases of Database Design

• Step: Physical database design for the relational model

– Step 1 Translate global logical data model for target DBMS

Step 1.1 Design base relations

Step 1.2 Design representation of derived data

Step 1.3 Design enterprise constraints – Step 2 Design physical representation

Step 2.1 Analyze transactions

Step 2.2 Choose file organization

Step 2.3 Choose indexes

Step 2.4 Estimate disk space requirements – Step 3 Design user views

– Step 4 Design security mechanisms

– Step 5 Consider the introduction of controlled redundancy – Step 6 Monitor and tune the operational system

• Example for physical design: see P371

(8)

7.6.3 Phases of Database Design

• The correspondence between the three-level ANSI-SPARC architecture for a database system and conceptual, logical, and physical design

.

7.7 DBMS selection

• DBMS Selection: The Selection of an appropriate DBMS to support the database application.

• Selection can be done at any time prior to logical design provided sufficient information is available regarding system requirements such as

performance, ease of restructuring, security, and integrity constraints.

• Main steps to selecting a DBMS:

– define Terms of Reference of study;

– shortlist two or three products;

– evaluate products;

– recommend selection and produce report.

NOTES

7.7 DBMS selection

• Features for DBMS evaluation .

NOTES

7.7 DBMS selection

• Features for DBMS evaluation .

NOTES

7.8 Application Design

• Application design: The design of user interface and application programs that use and process the database.

• Database and application design are parallel activities.

Application includes two important activities:

– transaction design;

– user interface design.

• Transaction: An action, or series of actions, carried out by a single user or application program, which accesses or changes content of the database.

– Transactions refer ‘real world’ events such as:

– the registering of a property for rent, – the addition of a new member of staff, – the registration of a new client, – the renting out of a property.

NOTES

7.8 Application Design

• The purpose of transaction design is to define and document the high-level characteristics of the transactions required, including:

– data to be used by the transaction;

– functional characteristics of the transaction;

– output of the transaction;

– importance to the users;

– expected rate of usage.

• Three main types of transactions:

– Retrieval transaction: to retrieve data for display on the screen or in the production of a report.

– Uupdate transaction: to insert new records, delete old records, or modify existing records in the database.

– Mixed transaction: involve both the retrieval and updating of data.

(9)

7.8 Application Design

• User interface design guidelines

– Meaningful title

– Comprehensible instructions

– Logical grouping and sequencing of fields – Visually appealing layout of the form/report – Familiar field labels

– Consistent terminology and abbreviations – Consistent use of color

– Visible space and boundaries for data-entry fields – Convenient cursor movement

– Error correction for individual characters and entire fields – Error messages for unacceptable values

– Optional fields marked clearly – Explanatory messages for fields – Completion signal

7.9 Prototyping

• Prototyping: Building a working model of a database application.

– to identify features of a system that work well, or are inadequate;

– to suggest improvements or even new features;

– to clarify the users’ requirements;

– to evaluate feasibility of a particular system design.

• There are two prototyping strategies in common use today:

– Requirements prototyping: once the requirements are complete the prototype is discarded.

– Evolutionary prototyping: the prototype is not discarded but with further development becomes the working database application.

NOTES

7.10 Implementation

• Implementation: the physical realization of the database and application designs.

– The database implementation is achieved using DDL or a graphical user interface (GUI), Use DDL to create database schemas and empty database files, using DDL to create any specified user views.

– The application programs are implemented using 3GL or 4GL. This will include the database transactions implemented using the DML, possibly embedded in a host programming language.

– We also implement the other components of the application design such as menu screens, data entry

forms, and reports.

NOTES

7.11 Data Conversion and Loading

• Data conversion and loading: Transferring any existing data into new database and converting any existing applications to run on new database.

– Only required when new database system is replacing an old system.

– DBMS normally has utility that loads existing files into new database. The utility requires the specification of the source file and the target database, and then automatically converts.

– May be possible for the developer to convert and use application programs from old system for use by new system.

NOTES

7.12 Testing

• Testing: the process of executing application programs with intent of finding errors.

– Use carefully planned test strategies and realistic data so that the entire testing process is methodically and rigorously carried out.

– Demonstrates that database and application programs

appear to be working according to requirements.

– Testing cannot show absence of faults; it can show only that software faults are present.

– If real data is to be used, it is essential to have backups taken in case of error.

NOTES

7.13 Operational Maintenance

• Operational Maintenance: the process of monitoring and maintaining system following installation.

– Maintenance involves the following activities:

– Monitoring the performance of the system: if performance falls below an acceptable level, may require tuning or reorganization of the database.

– Maintaining and upgrading database

application (when required): new requirements

are Incorporated into database application

through the preceding stages of the lifecycle.

(10)

7.14 CASE Tools

• Computer-Aided Software Engineering (CASE) can be applied to any tool that supports software engineering.

• CASE Support may include:

– data dictionary to store information about database application’s data;

– design tools to support data analysis;

– tools to permit development of corporate data model, and conceptual and logical data models;

– tools to enable prototyping of applications.

• CASE tools may be divided into three categories – Upper-CASE: support initial stages of lifecycle.

– Lower-CASE: support latter stage of lifecycle.

– integrated-CASE: support all stages of lifecycle.

7.14 CASE Tools

• Application of CASE tools

NOTES

7.14 CASE Tools

• CASE tools provide following benefits:

– Standards: help to enforce standards on software project or across the organization.

– Integration: store all the definition generated in a repository, or data dictionary. The data then can be linked together to ensure that all parts of the system are integrated.

– support for standard methods: simply result in documentation that is correct and more current.

– Consistency: check all information consistency.

– automation: can automatically transform parts of a design specification into executable code.

NOTES

7.15 DA and DBA

• Data Administrator (DA) and Database Administrator (DBA) are responsible for managing and controlling activities associated with corporate data and corporate database, respectively.

• DA is more concerned with early stages of lifecycle and DBA is more concerned with later stages.

NOTES

7.15 DA and DBA

• DA: Management of data resource including:

– database planning,

– development and maintenance of standards, policies and procedures, and conceptual and logical database design.

• DBA: Management of physical realization of a database application including:

– physical database design and implementation, – setting security and integrity controls,

– monitoring system performance, and reorganizing the database.

NOTES

Question and Exercises?

1. Describe the major components of an information system.

2. Describe the main purpose(s) and activities associated with each stage of the database application lifecycle.

3. Compare and contrast the three phases of database design.

4. Identify the stage(s) where it is appropriate to select a DBMS and describe an approach to selecting the ‘best’ DBMS.

5. Application design involves transaction design and user interface design. Describe the purpose and main activities associated with each.

6. Describe the main advantages of using the prototyping approach when building a database application.

7. Define the purpose and tasks associated with data administration and database administration.

(11)

Chapter 8. Entity-Relationship Modeling

• To ensure that we get a precise understanding of the nature of the data and how it is used by the enterprise, we need to have a model for communication that is non-technical and free of ambiguities.

• The Entity-Relationship (ER) model is one such example.

• ER modeling is an important technique for any database designer to master and forms the basis of the methodology.

• In this chapter we introduce the basic concepts of the ER model.

Chapter 8. Entity-Relationship Modeling

• We have chosen a diagrammatic notation that uses an increasingly popular object-oriented modeling language called Unified Modeling Language (UML).

• 8.1 Entity Types

• 8.2 Relationship Types

• 8.3 Attributes

• 8.4 String and Weak Entity Types

• 8.5 Attributes on relationship

• 8.6 Structural Constraints

• 8.7 Transform ER into relationship

NOTES

Chapter 8. Entity-Relationship Modeling

• How to use Entity–Relationship (ER) modeling in database design.

• Basic concepts associated with ER model.

• Diagrammatic technique for displaying ER model using Unified Modeling Language (UML).

• How to build an ER model from a requirements specification.

• How to derive a set of relations from a conceptual data model.

NOTES

Chapter 8. Entity-Relationship Modeling

• Concepts of the ER Model – Entity types

– Relationship types – Attributes

Oxford

Bill, Cain, Tim, Kitty,…

University

Student

Has

NOTES

Chapter 8. Entity-Relationship Modeling

ER Diagram of Branch View of DreamHome

NOTES

8.1 Entity Types

• Entity type: A group of objects with same properties, which are identified by the enterprise as having an independent existence.

– The basic concept of the ER model is entity type.

– Different designer may identify different entities.

• Entity occurrence: A Uniquely identifiable object of an entity type.

– We identify each entity type by a name and a list of properties.

• We use the more general term ‘entity’ where

meaning is ‘entity type’ or ‘entity occurrence’.

(12)

8.1 Entity Types

• A database normally contains many different entities.

• Examples of entities with a physical or conceptual existence.

8.1 Entity Types

• Diagrammatic representation of entity types – Each entity type is shown as a rectangle labeled with

the name of the entity.

– Normally a entity is named using singular noun.

– In UML, the first letter of each word in the entity name is upper case.

– Example for diagrammatic of the Staff and Branch entity types

NOTES

8.2 Relationships Types

• Relationship type: A set of meaningful associations among entity types.

– A relationship type is a set of associations between one or more participating entity types.

– Each relationship type is given a name that describes its function.

• Relationship occurrence: A uniquely identifiable association, which includes one occurrence from each participating entity type.

– Relationship occurrence indicates the particular entity occurrences that are related.

NOTES

8.2 Relationships Types

• Diagrammatic representation of relationship types

– Each relationship type is shown as a line connecting the associated entity types, labeled with the name of the relationship.

– Normally, a relationship is named using a verb or a short phrase including a verb. An arrow symbol is placed beside the name indicating the correct directon.

– The first letter of each word in the relationship name is shown in upper case.

– Whenever possible, a relationship name should be unique for given ER model.

NOTES

8.2 Relationships Types

• A diagrammatic representation of Branch Has Staff relationship type.

– Relationship is only labeled in one direction.

NOTES

8.2 Relationships Types

• Degree of a Relationship Type: The number of participating entities in relationship.

– The degree of a relationship indicates the number of entity types involved in a relationship.

– Relationship of degree:

two is called binary;

three is called ternary;

four is called quaternary.

(13)

8.2 Relationships Types

• Diagrammatic representation of complex relationships

– The UML notation uses a diamond to represent relationships with degrees higher than binary.

– The name of the relationship is displayed inside the diamond and in this case the directional arrow normally associated with the name is omitted.

• An example of a binary relationship called POwns.

8.2 Relationships Types

• An example of a ternary relationship called Registers.

• An example of a quaternary relationship called Arranges.

NOTES

8.2 Relationships Types

• Recursive Relationship: A relationship type where same entity type participates more than once in different roles.

– Relationships may be given role names to indicate purpose that each participating entity type plays in a relationship.

– Role names can be important for recursive relationship to determine the function of each participant.

– Role names may also be used when two entities are associated through more than one relationship.

– Role names are usually not required if the function of the participating entities in a relationship is unambiguous.

NOTES

8.2 Relationships Types

• An example of a recursive relationship called Supervises with role names Supervisor and Supervisee.

NOTES

8.2 Relationships Types

• An example of entities associated through two distinct relationships called Manages and Has with Role Names.

NOTES

8.3 Attributes

• Attribute: A property of an entity or a relationship type.

– The attributes hold values that describe each entity occurrence and represent the main part of the data stored in the database.

• Attribute Domain: The Set of allowable values for one or more attributes.

– The domain defines the potential values that an attribute may hold and is similar to the domain concept in the relationship model.

– Attribute may share a domain.

– A fully developed data model includes the domains of

each attribute in the ER model.

(14)

8.3 Attributes

• Attributes can be classified as being:

– Simple or Composite – Single-valued or multi-valued – Derived

• Simple Attribute: An attribute composed of a single component with an independent existence.

– Simple attributes can not be further subdivided into smaller components.

– Simple attributes are sometimes called atomic attributes.

• Composite Attribute: An attribute composed of multiple components, each with an independent existence.

8.3 Attributes

• Single-valued Attribute: An attribute that holds a single value for each occurrence of an entity type.

– The majority of attributes are single-valued.

– For example: branchNo is referred to as being single- valued.

• Multi-valued Attribute: An attribute that holds multiple values for each occurrence of an entity type.

– A multi-valued attribute may have a set of numbers with upper and lower limits.

– Foe example: telNo has between one and three values.

NOTES

8.3 Attributes

• Derived Attribute: An attribute that represents a value that is derivable from value of a related attribute or set of attributes, not necessarily in the same entity type.

– For example: duration can be calculated from the rentStart and rentFinish, which come from same entity type Lease.

– totalStaff can be calculated by counting the total number of Staff entity occurrences.

– deposit of the Lease entity can be calculated by twice the monthly rent of the PropertyForRent entity type.

NOTES

8.3 Attributes

• Candidate Key: The minimal set of attributes that uniquely identifies each occurrence of an entity type.

– A candidate key cannot contain a null.

• Primary Key: The candidate key that is selected to uniquely identify each occurrence of an entity type.

– The choice of primary key for an entity is based on attribute length, minimal number of attributes required.

• Composite Key: A candidate key that consists of two or more attributes.

– In some case, the key of an entity type is composed of several attributes, whose values together are unique for each entity occurrence but not separately.

NOTES

8.3 Attributes

• Diagrammatic representation of attributes – If an entity type is to be displayed with its attribute, we

divide the rectangle representing the entity in two. The upper part of the rectangle display the name of the entity and the lower part lists the names of the attributes.

NOTES

8.4 Strong and Weak Entity Types

• Strong Entity Type: An entity type that is not existence-dependent on some other entity type.

– A characteristic of a strong entity types is that each entity occurrence is uniquely identifiable using the primary key attribute(s).

• Weak Entity Type: An entity type that is existence-dependent on some other entity type.

– Each weak entity occurrence cannot be uniquely identified using only the attributes associated with that entity type. No primary key.

– Weak entity are sometimes referred to as child,

dependent, or subordinate, and strong entity as parent,

owner, or dominant.

(15)

8.4 Strong and Weak Entity Types

• For example: Strong Entity Type called Client and Weak Entity Type called Preference

8.5 Attribute on Relationships

• We use the same symbol, rectangle to represent attribute on a relationship, but the rectangle using a dashed line.

• Relationship called Advertises with Attributes.

NOTES

8.6 Structural Constraints

• All appropriate enterprise constraints are identified and represented is an important part of modeling an enterprise. Main type of constraint on relationships is called multiplicity.

• Multiplicity: The 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.

– Multiplicity constrains the way that entities are related.

– Represents policies (called business rules) established by user or company.

NOTES

8.6 Structural Constraints

• The most common degree for relationships is binary.

• Binary relationships are generally referred to as being:

– one-to-one (1:1): a member of staff manages a branch;

**– one-to-many (1:*): a member of staff oversees** properties for rent;

**– many-to-many (:): newspapers advertise properties** for rent.

• It is important to note that not all enterprise constraints can be easily represented in an ER model.

NOTES

8.6 Structural Constraints

• How to determine the multiplicity for each of these constraints and how to represent each in an ER diagram, and how to examine multiplicity for relationships of degrees higher than binary.

• 8.6.1 One-to-One (1:1) Relationships

• 8.6.2 One-to-Many (1:*) Relationships

• 8.6.3 Many-to-Many (:) Relationships

• 8.6.4 Multiplicity for Complex Relationships

• 8.6.5 Cardinality and Participation Constraints

NOTES

8.6.1 One-to-One (1:1) Relationships

• Semantic Net of Staff Manages Branch

Relationship Type.

(16)

8.6.1 One-to-One (1:1) Relationships

• Multiplicity of Staff Manages Branch (1:1) Relationship Type.

**8.6.2 One-to-Many(1:*) Relationships**

• Semantic Net of Staff Oversees PropertyForRent Relationship Type.

NOTES

**8.6.2 One-to-Many(1:*) Relationships**

• Multiplicity of Staff Oversees PropertyForRent (1:*) Relationship Type.

NOTES

**8.6.3 Many-to-Many(:) Relationships**

• Semantic Net of Newspaper Advertises PropertyForRent Relationship Type.

NOTES

**8.6.3 Many-to-Many(:) Relationships**

• Multiplicity of Newspaper Advertises PropertyForRent (:) Relationship.

NOTES

8.6.4 Multiplicity for Complex Relationships

• Multiplicity for Complex Relationships: The number (or range) of possible occurrences of an entity type in an n-ary relationship when other (n-1) values are fixed.

– The multiplicity for a ternary relationship represents the potential range of entity occurrences of a particular entity in the relationship when the other two values representing the other two entities are fixed.

– For example, the ternary registers relationship

between Staff, Branch, Client. We examine the

registers relationship when the values for the Staff and

Branch entities are fixed.

(17)

8.6.4 Multiplicity for Complex Relationships

• Semantic Net of Ternary Registers Relationship with Values for Staff and Branch Entities Fixed.

When staffNo and branchNO are fixed the corresponding clienNo are zero or more.

8.6.4 Multiplicity for Complex Relationships

• Multiplicity of Ternary Registers Relationship.

When staffNo and clienNo are fixed the corresponding branchNo are 1 and 1.

When branchNo and clienNo are fixed the corresponding staffNo are 1 and 1.

NOTES

8.6.4 Multiplicity for Complex Relationships

• Summary of Multiplicity Constraints.

NOTES

8.6.5 Cardinality and Participation Constraint

– Multiplicity is made up of two types of restrictions on relationships: cardinality and participation.

• Cardinality: Describes maximum number of possible relationship occurrences for an entity participating in a given relationship type.

– The cardinality of a binary relationship is what we previously referred to as a one-to-one, one-to-many, many-to-many.

• Participation: Determines whether all or only some entity occurrences participate in a relationship.

– All refer to as mandatory participation.

– Only some refer to as optional participation.

NOTES

8.6.5 Cardinality and Participation Constraint

• Multiplicity as Cardinality and Participation Constraints.

NOTES

8.6.5 Cardinality and Participation Constraint

• Multiplicity as Cardinality and Participation

Constraints.

(18)

8.7 Transform ER into relationship

• Local Conceptual Data Model for Staff View Showing all Attributes.

8.7 Transform ER into relationship

• Transform ER into relational data model: To create

relations for the local logical data model to represent the entities, relationships, and attributes that have been identified.

• (1)

Strong entity types

– Create a relation that includes all simple attributes of that entity.

For composite attributes, include only constituent simple attributes.

Staff (staffNo, fName, lName, position, sex, DOB) Primary Key staffNo

• (2) Weak entity types

– Create a relation that includes all simple attributes of that entity.

Primary key is partially or fully derived from each owner entity.

Preference (prefType, maxRent) Primary Key None (at present)

NOTES

8.7 Transform ER into reationship

• (3) 1:* Binary relationship types

– Entity on ‘one side’ is designated the parent entity and entity on

‘many side’ is the child entity.

– Post copy of the primary key attribute(s) of parent entity into relation representing child entity, to act as a foreign key.

NOTES

8.7 Transform ER into reationship

• (4) 1:1 Binary relationship types

– More complex as cardinality cannot be used to identify parent and child entities in a relationship.

– Instead, participation used to decide whether to combine entities into one relation or to create two relations and post copy of primary key from one relation to the other. Consider following:

 (a) mandatory participation on both sides of 1:1 relationship;

 (b) mandatory participation on one side of 1:1 relationship;

 (c) optional participation on both sides of 1:1 relationship.

– (a) Mandatory participation on both sides of 1:1 relationship

Combine entities involved into one relation and choose one of the primary keys of original entities to be primary key of new relation, while other (if one exists) is used as an alternate key.

The Client States Preference relationship is an example of a 1:1 relationship with mandatory participation on both sides

Client (clientNo, fName, lName, telNo, prefType, maxRent, staffNo) Primary Key clientNo

Foreign Key staffNo references Staff(staffNo)

NOTES

8.7 Transform ER into reationship

– (b) Mandatory participation on one side of a 1:1 relationship

Identify parent and child entities using participation constraints.

Entity with optional participation is designated parent entity, and other entity designated child entity.

Copy of primary key of parent placed in relation representing child entity.

If relationship has one or more attributes, these attributes should follow the posting of the primary key to the child relation.

Example

NOTES

8.7 Transform ER into reationship

– (c) Optional participation on both sides of a 1:1 relationship

Designation of the parent and child entities is arbitrary unless can find out more about the relationship.

 Consider 1:1 Staff Uses Car relationship with optional participation on both sides.

If there is no additional information to help select the parent and child entities, the choice is arbitrary. Designate car as parent, or vice versa.

Assume majority of cars, but not all, are used by staff and only minority of staff use cars. The Car entity, although optional, is closer to being mandatory than Staff entity. Therefore designate Staff as parent entity and Car as child entity.

(19)

8.7 Transform ER into reationship

• (5) 1:1 Recursive relationships - follow rules for

participation for a 1:1 relationship.

– mandatory participation on both sides, represents the recursive relationship as a single relation with two copies of the primary key. As before, one copy of the primary key represents a foreign key and should be renamed to indicate the relationship it represents.

– mandatory participation on only one side: option to create a single relation with two copies of the primary key as described above, or create a new relation to represent the relationship. The new relation would only have two attributes, both copies of the primary key. As before, the copies of the primary keys act as foreign keys and have to be renamed to indicate the purpose of each in the relation.

– optional participation on both sides, again create a new relation as described above.

8.7 Transform ER into reationship

• (6) : Binary relationship types

– Create relation to represent relationship and include any attributes that are part of relationship.

– Post a copy of the primary key attribute(s) of the entities that participate in relationship into new relation, to act as foreign keys.

– These foreign keys will also form primary key of new relation, possibly in combination with some of the attributes of the relationship.

NOTES

8.7 Transform ER into reationship

• (7) Complex relationship types

– Create relation to represent relationship and include any attributes that are part of the relationship.

– Post copy of primary key attribute(s) of entities that participate in the complex relationship into new relation, to act as foreign keys.

– Any foreign keys that represent a ‘many’ relationship (for example, 1..*, 0..*) generally will also form the primary key of new relation, possibly in combination with some of the attributes of the relationship.

NOTES

8.7 Transform ER into reationship

• (8)

Multi-valued attributes

– Create new relation to represent multi-valued attribute and include primary key of entity in new relation, to act as a foreign key.

– Unless the multi-valued attribute is itself an alternate key of the entity, primary key of new relation is combination of the multi- valued attribute and the primary key of the entity.