Relation with the Multi-Valued Attribute
Resulting Relation with the Multi-Valued Attribute Excised.
With no key, the mapping rule remains the same except that, instead of "together with the key, …" we would say "together with the atomic attributes…" In relational databases, every row of a table contains atomic attributes. Also, every row is unique. Therefore, a candidate key in any table is always all of the attributes. Usually, a subset of "all of the attributes" can be found to be a key; but because no two rows are ever the same, we would say that one candidate key is the collection of all attributes.
If the name or address attributes were not considered unique, then the resulting relation would be:
Name–School name school Smith St. Helens Smith Mountain Smith Volcano Jones Manatee U Jones Everglades High Sudip PCA Sudip Pensacola High Sudip UWF Pradeep Cuttington Pradeep UT STUDENT name address Smith 123 4th St Jones 222 2nd St Sudip 887 Mirabelle Pradeep 248 Shillingford STUDENT
Note that rule M1c is an application of the non-1NF to 1NF transformation discussed in Chapter 1.
Checkpoint 2.3
1. How do you map multi-valued attributes?
2. How do you map composite attributes?
3. What is a unique identifier? Is it a candidate key? Is it "the" primary key? Discuss.
[4]These mapping rules are adapted from Elmasri and Navathe (2000).
Smith 123 4th Street St. Helens
Smith 123 4th Street Mountain Smith 123 4th Street Volcano Jones 222 2nd St Manatee U
Jones 222 2nd St Everglades High Sudip 887 Mirabelle PCA Sudip 887 Mirabelle Pensacola High Sudip 887 Mirabelle UWF Pradeep 248 Shillingford Cuttington Pradeep 248 Shillingford UT
Chapter Summary
The main focus in this chapter was on developing the concept of the entity and developing a one-entity diagram (using the Chen-like model). The concept of attributes was also discussed, and the final section focused on how a one-entity diagram could be mapped to a relational database. The grammar for a one-entity diagram and its attributes was also developed. This grammar will be further developed in subsequent chapters. Chapter 3
discusses developing a second entity, and the relationship between this second entity and the "primary entity."
Chapter 2 Exercises
[Note: You should filter out and clarify the assumptions you made when you report your work.]
Exercise 2.1
You want to create a database about businesses. Each business will have a name, an address, the business phone number, the owner's phone number, and the first names of the employees who work at the business. Draw the ER diagram using the Chen-like model, and then write the English
description for your diagrams. Compare the English to your diagrams, and state any assumptions you made when drawing the diagrams. Map your diagrams to a relational database.
Which attributes would you consider composite attributes in this database? Which attributes would you consider multi-valued attributes in this database? Could there be any derived attributes? What would be good keys?
Exercise 2.2
You want to create a database about the books on your shelf. Each book has authors (assume last name only is needed), title, publisher, courses used in (course number only). Draw the ER diagram using the Chen-like model, and then write out the English description for your diagrams.
Compare the English to your diagrams and state any assumptions you made when drawing the diagrams.
Which attributes would you consider composite attributes in this database? Which attributes would you consider multi-valued attributes in this database? Could there be any derived attributes? What would be good keys? Map your diagram to a relational database.
References
Batini, C., Ceri, S., and Navathe, S.B. Conceptual Database Design, Benjamin Cummings, Redwood City, CA, 1992.
Chen, P.P. "The Entity Relationship Model — Toward a Unified View of Data,"ACM Transactions on Database Systems, 1(1), 9–37, March 1976.
Chen, P.P. "The Entity-Relationship Model: A Basis for the Enterprise View of Data,"Proceedings IFIPS NCC 46, No. 46, 76–84, 1977. Codd, E. Relational Model for Data Management – Version 2, Addison- Wesley, Reading, MA, 1990.
Date, C.J. An Introduction to Database Systems, 5th ed., Addison- Wesley, Reading, MA, 1995.
Earp, R. and Bagui, S. "Building an Entity Relationship Diagram: A Software Engineering Approach,"Database Management, Auerbach Publications, Boca Raton, FL, 22-10-41, 1–16, December 2000.
Elmasri, R. and Navathe, S.B. Fundamentals of Database Systems, 3rd ed., Addison-Wesley, Reading, MA, 2000.
McFadden, F.R. and Hoffer, J.A. Modern Database Management, 4th ed., Benjamin Cummings, Menlo Park, CA, 1994.
Navathe, S. and Cheng, A. "A Methodology for Database Schema Mapping from Extended Entity Relationship Models into the Hierarchical Model,"The Entity-Relationship Approach to Software Engineering, G.C. Davis et al., Eds., Elsevier, North-Holland, Amsterdam, 1983.
Scheuermann, P., Scheffner, G., and Weber, H. "Abstraction Capabilities and Invariant Properties Modeling within the Entity- Relationship Approach,"Entity-Relationship Approach to System
Analysis and Design, P. Chen, Ed., Elsevier, North-Holland, Amsterdam, 121–140, 1980.
Teorey, T.J., Yang, D., and Fry, J.P. "A Logical Design Methodology for Relational Databases Using the Extended Entity-Relationship Model," ACM Computing Surveys, 18(2), 197–222, June 1986.
Valacich, J.S., George, J.F., and Hoffer, J.A., Essentials of Systems Analysis and Design, Prentice Hall, Upper Saddle River, NJ, 2001.