We have covered the concepts of relational databases in "Introduction to Databases," how to access such databases in "Accessing Databases with SQL," creation of web pages with forms in "Creating Web Pages" and "Web Forms for Database Queries," and CGI programming to interface between web pages and databases and to process data in "CGI Programs and Web Forms" and "CGI Programs in C++ Using the MySQL C API," "In Genomic Data," "Genomic Sequence Comparison," and "Searching Genomic Databases," we studied some of the algorithms to process genomic data and how to use these algorithms in conjunction with the above tasks. Until now, however, we have employed existing databases. The current module, "Relational Database Development," and "Creating and Changing Databases with SQL" discuss how we can design and produce databases. The ability to do so is important for development of databases for our own use or for larger computational science applications. Throughout this discussion, we consider the "College Physics Example" of the module "Computational Science and Web-Accessed Databases" as well as other applications.
|Definition||The external level of a database has the users' views of the database. The conceptual level describes the logical structure of the entire database, including descriptions of the data and relationships among the data. The internal level gives the details of the physical storage of the database on the computer.|
|Doctor's View||Billing Office View|
|Definition||The Entity-Relationship (ER) model, a high-level data model that is useful in developing a conceptual design for a database.|
|Definition||An entity is a real-world item or concept that exists on its own. The set of all possible values for an entity is the entity type.|
Figure 2. ER diagram notation for entity student
|Definition||An attribute of an entity is a particular property that describes the entity. The set of all possible values of an attribute is the attribute domain.|
Figure 3. ER diagram notation for an attribute domain (StudentGrade) of an entity type (student)
|Definition||Null is the special attribute value that indicates an unknown or missing value.|
|Definition||A simple attribute is one component that is atomic. A composite attribute has multiple components, each of which is atomic or composite.|
Figure 4. ER diagram notation for composite attribute domain, name
Another way to classify attributes is either as single-valued or multi-valued. For an entity an attribute, such as StudentGrade, usually holds exactly one value, such as 93, and thus is a single-valued attribute. However, two lab assistants might assist in a laboratory section. Consequently, the LabAssistant attribute for the entity LabSection is multi-valued. A multi-valued attribute has more than one value for a particular entity. We illustrate this situation with a double oval around the lab assistant type, LabAssistant (see Figure 5).
|Definition||For a particular entity, an entity attribute that holds exactly one value is a single-valued attribute. A multi-valued attribute has more than one value for a particular entity.|
Figure 5. ER diagram notation for multi-valued attribute domain, LabAssistant
A derived attribute can be obtained from other attributes or related entities. For example, the radius of a sphere can be determined from the circumference. We request the derived attribute with a dotted oval and line, such as in Figure 6.
Figure 6. ER diagram notation for derived attribute, radius
An attribute or set of attributes that uniquely identifies a particular entity is a key. For example, Emanuel Vagas' Student Identification Number uniquely identifies him. However, to determine the class we need a composite key that consists of several attributes, such as catalogue number, section, semester, and year. In the ER diagram of Figure 7, we underline the composite key, class. (The figure shows another attribute (DragExpWeek) of LabSection that stores the week of the semester (1-15) in which the drag experiment occurs.)
|Definition||An attribute or set of attributes that uniquely identifies a particular entity is a key. A composite key is a key that is a composite of several attributes.|
Figure 7. ER diagram notation for key StudentId for student and composite key (class, consistent of (CatalogNumber, section, year, and semester) for LabSection
Figure 8. Relationships (Emanuel Vagas, Phys201F2005A04), (Ignatios Trekas, Phys201F2005A04), and (Ignatios Trekas, Phys201S2006B03)
We use a diamond to illustrate the relationship type in an ER diagram, such as in Figure 9. We arrange the diagram so that the relationship reads from left to right, "a student is a member of a team." Alternatively, we can arrange the components from top to bottom.
|Definition||A relationship type is a set of associations among entity types. A relationship or relationship instance is an ordered pair consisting of particular related entities.|
Figure 9. ER diagram notation for relationship type, MemberOf
|Definition||The degree of a relationship type is the number of entity types that participate. If two entity types participate, the relationship type is binary. A role name indicates the purpose of an entity in a relationship.|
Figure 10. ER diagram notation with roles member, leader, and lab team
As Figure 11 illustrates, a relationship type can also have attributes. The relationship type order connects entities chemical and supplier. The relationship is many-to-many because each chemical can be from several suppliers and each supplier has a number of chemicals. An order has a purchase date, amount, and total cost as well as the chemical and supplier information. Thus, order has attributes PurchaseDate, amount, and TotalCost that we cannot appropriately associate with chemical or supplier.
Relationship type with attributes
|Definition||A recursive relationship is one in which the same entity participates more than once in the relationship.|
Figure 12. ER diagram notation for recursive relationship type, supervision
|Definition||An entity type is strong if its existence does not depend on some other entity type. Otherwise, the entity type is weak.|
Figure 13. ER diagram notation for weak entity type, team, and identifying relationship, in
Figure 14. ER diagram notation for one-to-one relationship
Figure 15. ER diagram notation for many-to-one relationship
Because a student can retake a course, a student could be a member of more than one team. Thus, we would need different variables, say N and M, for the numbers of student and team entities, respectively; and the ratio would be N:M in this many-to-many relationship. As Figure 16 diagrams, a team has any number of students, and a student can participate in several teams during his or her college career. Ratios, such as 1:1, 1:N, N:1, and N:M, give a cardinality constraint or numeric restriction on the possible relationships.
|Definition||A 1:1 or one-to-one relationship from entity type S to entity type T is one in which an entity from S is related to at most one entity from T and vice versa. An N:1 or many-to-one relationship from entity type S to entity type T is one in which an entity from T can be related to two or more entities from S. A 1:N or one-to-many relationship from entity type S to entity type T is one in which an entity from S can be related to two or more entities from T. An N:M or many-to-one relationship from entity type S to entity type T is one in which an entity from S can be related to two or more entities from T, and an entity from T can be related to two or more entities from S. A ratio, such as 1:1, 1:N, N:1, and N:M, gives a cardinality constraint or numeric restriction on the possible relationships.|
Figure 16. ER diagram notation for many-to-many relationship
|Quick Review Question 3 Give
the cardinality constraint on the relationship runs, where each team
may have many drag experiment runs:
Figure 17. ER diagram notation for total participation constraint
Using the above components, we have the ER model in Figure 18.
Figure 18. ER diagram notation for total participation constraint
Table 1. Summary of the ER diagram notation
|Weak entity type with identifying relationship|
a. Draw an ER diagram. Make sure each entity type has at least one key attribute. Document any assumptions you make. Avoid diagrams with a single entity.1. A chemistry department wants to have a database of all chemicals in the stockroom. The information includes the name, molecular formula, amount on hand, date purchased, supplier, and supplier contact information.
b. For each attribute, determine its domain of values, whether null is an acceptable value, and, if acceptable, what null indicates.
c. If you think the description is incomplete, list other data the database should store.
d. Adjust your design to incorporate these additions.
Copyright � 2002, Dr. Angela B.
All rights reserved