21. Explain the relationship between entity, entity class, and entity instance.
An entity is something that can be identified in the users’ work environment, something that the users want to track. Entities of a given type are grouped into entity classes. An entity instance is the representation of a particular entity.
22. Explain the difference between attributes and identifiers.
Entities have attributes. Attributes are properties that describe the entity’s characteristics. Entity instances have identifiers. Identifiers are attributes that name, or identify, entity instances.
23. Name and describe three types of binary relationships.
1:1 – a single entity instance of one type is related to a single-entity instance of another type.
1:N – a single entity instance of one type is related to many-entity instances of another type.
M:N – many-entity instances of one type relate to many-entity instances of another type.
24. What is the archtetype/instance pattern?
The archetype/instance pattern occurs when one entity tracks occurrences of another entity. A common example is the relationship between CLASS and SECTION, where the actual occurrence of a class happens when students register for a SECTION of that CLASS. The archetype/instance pattern is implemented as an ID-dependent relationship.
25. What is a recursive relationship? Give an example not used in the text.
A recursive relationship is a relationship between an entity and itself. For example, given the entity PERSON, a recursive relationship could be used to show a PERSON and his or her SIBLINGs (brothers and sisters).
26. What are the steps for transforming an entity into a table?
The steps are: (1) specify the primary key, (2) specify candidate keys, (3) specify column properties including null status, data type, default value (if any), and data constraints (if any), and (4) verifying normalization.
27. Define a surrogate key, describe the ideal primary key and explain how surrogate keys meet this ideal.
The ideal primary key is short, numeric and fixed. A surrogate key is a unique, DBMS-supplied identifier intended to be used as the primary key of a table. Further, the DBMS will not allow the value of a surrogate key to be changed. The values of a surrogate key have no meaning to the users and are usually hidden on forms and reports. By design, they are short, numeric and fixed and thus meet the definition of the ideal primary key
28. Define and discuss data constraints.
Data constraints on a column are the limits put on the values the data can have. There are four types of data constraints: (1) domain constraints, which define a limited set of values for the column, (2) range constraints, which specify that the values must fall within a certain range, (3) intrarelation constraints, which define what values the column can have based on values of other columns in the same table, and (4) interrelation constraints, which define values the column can have based on values of columns in other tables.
29. In general, how are recursive relationships handled in a database design?
A recursive relationship is a relationship among entities of the same class, and is represented in the same way as other relationships are. The rows of the tables can take two different roles, however. Some are parent rows, and others are child rows. Further, the table will contain both its own primary key and the foreign key that links back to the table itself. If a row has no parent, then the value of the foreign key column in that row will be null. If the row has a parent, then there must be a foreign key value in that row that corresponds to the primary key value of another row in the table.
30. What is a cascading update?
Referential integrity constraints require that foreign key values in one table correspond to primary key values in another. If the value of the primary key is changed — that is, updated — the value of the foreign key must immediately be changed to match it. Cascading updates will set this change to be done automatically by the DBMS whenever necessary.
