Pages: 10

This chapter discusses the ideas that are fundamental to our presentation of the design of a database. The term software engineering refers to a process of specifying, designing, writing, delivering, maintaining, and finally retiring software. Software engineers often refer to the “life cycle” of software; software has a beginning and an ending. There are many excellent references on the topic of SE. A basic idea in SE is to build software correctly; a series of steps or phases is required to progress through a “life cycle.” These steps ensure that a process of thinking precedes action—thinking through “what is needed” precedes “what software is written.” The “players” in the software development life cycle may be placed into two camps, often referred to as the user and the analyst. Software is designed by the analyst for the user according to the user’s specification.

Pages: 22

This chapter discusses the evolution of databases and examines how the relational database model for designing and using databases. Data must be stored in an orderly fashion in a file of some kind to be useful. A database is most simply a repository of data about some specific entity. As data was placed on a computer, software was developed to handle the data and filing techniques evolved. In the very early days of databases, the files kept on computers basically replicated the 3 × 5 cards. In hierarchical database models, all data are arranged in a top-down fashion in which some records have one or more “dependent” or “child” records, and each child record is tied to one and only one “parent.” Relationships in all database models have what are called structural constraints. A structural constraint consists of two notions: cardinality and optionality.

Pages: 37

The data cell containing the two phone numbers is said to contain a repeating group. If a two-dimensional arrangement of data cells contains only atomic data, it is said to be a table. In mathematics, we define sets as an unordered collection of unique objects. Codd viewed and defined a relational database as: All tables contain sets of rows, and all data are atomic in two-dimensional tables. A functional dependency (FD) is a relationship of one attribute in a table to another. In a database, we often have the case where one attribute defines. In a database, the designer makes choices defining data with FDs. It is the designer’s responsibility to elicit FD information from the user. The user tells us whether a project has one location or multiple locations. The user tells us whether a person can have one and only one phone number.

Pages: 35

This chapter discusses the data modeling approach and then introduces entity-relationship (ER) diagrams. The concepts of entities, attributes, relationships, and keys are introduced. The ER diagram is a graphic tool to facilitate data modeling. ER diagrams are a subset of “semantic models” in database parlance. Semantic models refer to models intended to elicit meaning from arrangements of data. ER diagrams are not the only semantic modeling tools, but they are common and popular. Mapping is the process of choosing a logical model and then moving to a physical database file system from a conceptual model. A physical file loaded with data is necessary to actually obtain data from a database. The ER diagram is a semantic data modeling tool used to accomplish the goal of abstractly describing or portraying data. Abstractly described data is called a conceptual model. A relationship is a link or association between entities. Relationships are usually denoted by verb phrases.

Pages: 22

An E R diagram shows the relationship between two entities, School and Student. The E R diagram for the entity, School, comprises one composite attribute, location, one unique identifier, school name, and one simple attribute, type of school. A relationship in an ER diagram is a connection between two or more entities or between one entity and itself. The latter kind of relationship, between one entity and itself, is known as a recursive relationship. The user may be the ultimate judge of the appropriateness of the expression we use, but we will add to this grammar soon. The user may prefer a different tense for the verb and may choose a verb they think more appropriately assesses the situation. Relationships in ER diagrams are usually given names depicting how the entities are related. Sometimes, a relationship is difficult to describe; in this case, a two-letter code for the relationship is used.

Pages: 50

A diagram illustrates the one-to-one relationship two entities, Student and Automobile. Each entity comprises multiple elements. The one-to-one relationship between the entities is represented by a solid line connecting one element of Student to exactly one element of Automobile. In many-to-many relationships, many occurrences of one entity are associated with many occurrences of the other entity. In the Chen-like model, we will depict the one-to-oneness of this relationship by adding the cardinality numbers to the lines on the ER diagram connecting the relationships and the entities. To show every automobile is driven by a student, but not every student drives an automobile, we enhance our Chen-like models of ER diagrams by putting a double line between the relationship diamond and the AUTOMOBILE entity to indicate every automobile is driven by a student. Put another way, every automobile in the database participates in the relationship.

Pages: 22

This chapter revisits and redefines steps 3 and 4 of the entity-relationship (ER) design methodology to include the concept of the weak entity. Grammar and mapping rules for the weak entity are also developed. An E R diagram illustrates the relationship between two entities, Employee and Dependent. The entity, Employee, comprises two attributes: one composite attribute, name, and one unique identifier, employee id. An E R diagram shows the relationship between two entities, Employee and Dependent. The entity, Employee, comprises two attributes: a composite attribute, name, and a unique identifier, employee id. An E R diagram illustrates the relationship between two entities, Employee and Automobile. The entity, Employee, comprises two attributes: one composite attribute, name, and one unique identifier, employee id. The primary key of the weak relation will be the partial key of the weak entity concatenated to the primary key of the owner entity.

Pages: 38

This chapter discusses the derived and redundant relationships, and the ER design methodology is again refined; step 6b is included to deal with these structures. The grammar and structured English for the intersection entity are presented, and the concept of recursive relationships is introduced. Relationship attributes may occur with an ER diagram containing any cardinality, but one will most often find relationship attributes in the binary, M:N model. However, one could realistically argue things we have described as attributes could themselves be entities. Both the instructor and the book should be candidates for being diagrammed as entities if the envisioned database called for it. An E R diagram illustrates the relationship between two entities: Course and Instructor. The entity, Course, comprises four attributes: one unique identifier, course number, and three simple attributes: credits, course name, and book.

Pages: 25

This chapter discusses the intersection attributes create ternary relationships and then look at structural constraints. The development of ternary relationships from relationships of relationships is also introduced. Ternary relationships are required when binary relationships are not sufficient to accurately describe the semantics of an association among three entities. The usual case where n-ary relationships arise is where there exists data that must be related to more than two entities. Just because there is a ternary relationship does not mean binary relationships among the entities may not exist. Using the example of CUSTOMERS, VENDORS, and PRODUCTS, suppose retail vendors and suppliers of products have a special relationship not involving customers, such as wholesaling with an entirely different price structure. In the informal version of the statement from the diagram, little information is gained by repetition. Select one primary entity from the database requirements description and show the attributes to be recorded for that entity.

Pages: 34

This chapter discusses the generalizations and specializations in the Enhanced Entity-Relationship model and develop a methodology and grammar for this extension. One way programmers in the past solved the problem of specializations was to create variants. Variants are record pieces varying according to other parts of the record. The info attribute has different values for different sports. These different values are called variants. While the introduction of variants in records seems to solve a problem of representing data, it causes database problems with storage and retrieval. A solution to the problem of variants in records and varying attributes in entities in the ER diagrams is to excise the variant and reference it back to the primary key of the “parent” information piece. Generalizations and specializations are categorizations of entities for which the specialization entity may result from generalizations containing variants.

Pages: 26

This chapter presents the mapping rules and then discuss reverse engineering—starting with the database and working backward to an ER/EER diagram. Form a separate table for the multivalued attribute. Record a row for each value of the multivalued attribute together with the key from the original table. The key of the new table will be the concatenation of the multivalued attribute plus the key of the owner entity. Remove the multivalued attribute from the original table. The reversed engineered diagram provides us with a grammatical and diagrammatic description of the database. Often people use databases but do understand them because there is no “big picture.” The rules are provided as a guideline to move from no ER diagram to a more complete, documented database. The following rules are to be considered rigid but rather a starting point toward “negotiating” a documented database. It is likely the ER diagram will evolve, the database may change, users will govern the final product.

Pages: 17

This chapter discusses the conventions used in the Barker/Oracle-like model as it applies to our ER Design Methodology. The point of models like these is to elicit how users view their data and then map whatever model is used to a relational database. An analyst may want to present several models to make clear what the user wants and needs. Therefore, this chapter is looking at the same problem from a slightly different angle to shed light on what is needed. All attributes in a Barker/Oracle-like model are considered simple or atomic as in relational databases. The Barker/Oracle-like model does have the concept of composite attributes. A mandatory attribute could be a key, but it is not necessarily a key. Mandatory and optional attributes are usually not indicated explicitly in the Chen-like model. The relationship phrase for each end of a relationship is placed near the appropriate entity-end in lowercase.