Mẹo Which of the following is not true about representing subtypes in a relational database design?
Kinh Nghiệm Hướng dẫn Which of the following is not true about representing subtypes in a relational database design? Mới Nhất
Bùi Thảo Ngọc đang tìm kiếm từ khóa Which of the following is not true about representing subtypes in a relational database design? được Update vào lúc : 2022-09-29 02:56:11 . Với phương châm chia sẻ Bí quyết về trong nội dung bài viết một cách Chi Tiết Mới Nhất. Nếu sau khi Read tài liệu vẫn ko hiểu thì hoàn toàn có thể lại Comments ở cuối bài để Ad lý giải và hướng dẫn lại nha.Last updated Save as PDF Page ID15569
In chapter 8 we covered rules to be used when an ERD is mapped to a relational database. In this section we add rules for mapping supertypes and subtypes to relations. There are three basic options a designer considers when mapping these structures to a database:
Nội dung chính- Relations for All Entity TypesRelations for Bottom-Most Entity TypesOne Relation Representing the Whole HierarchyWhich of the following is the correct technique for representing an M N relationship using the relational model?Which of the following is true when implementing a oneWhich is not a step of the database design process?Which of the following is the correct technique for representing a 1 N relationship in the relational model quizlet?
We use
two examples to exhibit the mapping options; one where total participation is specified for the supertype and the other where participation is optional.
The previous library model is modified to show that an item can be out on loan to a thành viên, and that one of the subtypes, video, is produced by a producer:
Figure
(PageIndex1): Previous library model.A university model where a person may be a student and/or an employee, and where students declare a major subject area:
Figure (PageIndex2): University model.Regardless of the option selected for hierarchies, the rules for mapping an ERD to a relational database discussed previously (Chapter 8) still apply. We must apply rules regarding relationships and attributes consistently. For example, if any entity type in a hierarchy is involved in a one-to-many relationship we must ensure the proper use of foreign keys.
Relations for All Entity Types
With this option each entity type in a hierarchy is represented by its own relation. Important points here are that
- All relations representing entity types in the same hierarchy have the
same primary key.The primary key of a subtype relation will also be a foreign key that references its supertype relation.Attributes of a supertype (except for the primary key) appear only in the relation that represents the supertype.
Example (PageIndex1)
The library model maps to the following relational design:
Figure (PageIndex3): Library model mapping.Note the foreign keys:
- Item has a foreign key referencing Member Video has a foreign key referencing Producer Each of Video, Book, and Magazine has a foreign key referencing Item. If a row exists in Video, Book, or Magazine then there must be a
corresponding row in Item.
A sample database is presented on the next page.
The tables are shown here with sample data. Note that
- each row of Video, Book, and Magazine has a related row in Item some items are out on loan to a thành viên each video has a producer
Figure
(PageIndex4): Tables with sample data.In the relationships diagram note the one-to-one relationships between the supertype relation and each of its subtype relations:
Figure (PageIndex5): Relationships diagram.Example (PageIndex2)
Now consider the university model. The relational design for this mapping option:
Figure (PageIndex6): University model mapping.Since subtyping is optional in the university model there can be a row in Person with no corresponding row in Employee or Student. A person does not have to exist as one of the subtypes.
Note the foreign keys:
- Student has a foreign key referencing SubjectArea Employee and Student have foreign keys referencing Person. If a row exists in Employee or Student then a corresponding row must exist in Person.
On the following page we show tables with some sample data and the relationships diagram.
A sample database is presented below. Note that person 2 is both a student and an employee, and that person 4 is neither a student nor an employee.
Figure (PageIndex7): Sample database.In the relationships diagram note the relationships are one-to-one between the supertype relation and each of its subtype relations:
Figure (PageIndex8): Relationships diagram.
Relations for Bottom-Most Entity Types
In this case relations are created for only entity types that are the “bottom” of the hierarchy. There are no relations created for a supertype. Important points here are that
- All relations derived from entity types in the same hierarchy will have the same primary key.No primary key value can be repeated (We have not seen how to handle this in MS Access. Further study of relational systems can include techniques that
automate the checking for this kind of integrity constraint.)Attributes of a supertype must be included in each of its subtype relations.
Example (PageIndex3)
For the library model, since there is total participation in subtyping this option works well. Every item will be stored in a relation, and each item is stored exactly once. The resulting design:
Figure (PageIndex9): Library model design.Note the foreign keys:
- Because there is no Item relation, each of Video, Book, and Magazine have foreign keys referencing Member.Video is the only relation with a foreign key referencing Producer.
An issue the designer should be aware of is that callNumbers across the three relations must be unique (call number is the primary key of Item). Further study of database systems is needed to know how this rule can be enforced.
It is left as an exercise for the student to create a database with sample data.
Example (PageIndex4)
Consider the university model. This approach (creating relations for bottom-most entity types) is not suitable for the university model because of the overlapping subtypes and because the participation in subtyping is not total. Applying the option we have:
Figure (PageIndex10): University model mapping.If an entity exists in more than one subtype then such an entity will have data stored redundantly in the database. In the design above if a person is both an employee and a student then that person’s first and last names would be stored twice (in two different relations).
The Employee and Student relations are not sufficient to store Person data. The participation is optional and so a person may exist who is neither an employee nor a student; in such a case the data for the person cannot be stored!
It is left as an exercise for the student to create a database with sample data.
One Relation Representing the Whole Hierarchy
When this option is applied one relation is created for a complete hierarchy. All attributes appearing in the hierarchy are placed in one relation. Note that the value of a discriminator attribute will enable the user to know easily the subtype of a particular entity. For our example models, when we map a hierarchy to a single relation we obtain very simple relational designs.
It is left as an exercise for the student to create databases with sample data.
Example (PageIndex5)
The library model maps to the following design
Figure (PageIndex11): Library model mapping.In the Item relation the itemType attribute indicates if the row represents a video, a magazine, or a book. The memberId may have a value if the item is out on loan. producerId can only have a value if itemType is “video”.
Example (PageIndex6)
When mapping a hierarchy to a single relation for the university model the designer should include discriminator attributes that are boolean-valued with one discriminator attribute per subtype. Applying this option to the university model we have:
Figure (PageIndex12): University model
mapping with discriminator attributes.With this database each person is stored most once in the database; there is no duplicated data as with the previous mapping option.
If a person is neither an employee nor a student then the only attributes that can have values are: personId, employeeFlag, studentFlag, first and last – the others must be null. The values of employeeFlag and studentFlag would be false.
Exercises
Exercise (PageIndex1)
Consider the database designs illustrated in this appendix. Implement one or more of these and populate with data.
Exercise (PageIndex2)
Consider the two designs used in the examples of this appendix. Combine these two designs by replacing Member with the Person hierarchy. Illustrate the relational structures when the model is mapped to a database. Choose mapping options for the hierarchies.
Exercise (PageIndex3)
Consider the design you created in exercise 2 but modify the one-to-many borrows relationship to be a many-to-many with attributes dateBorrowed and dateReturned where dateBorrowed is a discriminator for the relationship. Recall this discriminator is not the same as the discriminators suggested for mapping supertypes and subtypes.
Note that this modification to the library example will allow history to be recorded for the borrowing of items.
Exercise (PageIndex4)
For exercise (PageIndex3) create the database and populate the database with sample data.
Exercise (PageIndex5)
Create an ERD for a service station business that provides goods and services to its customers. Typically a customer comes in with their vehicle and requests certain work to be performed. For example a customer may request an oil change and for a new set of four tires to be provided and installed.
The work items that can be performed or supplied can be of two types: a service (such as the oil change) and actual physical items (such as litres of oil). There will be several services that can be performed such as tire installation, changing oil, or fixing a flat tire. Each of these will have some cost to be charged to a customer. There are many concrete items that are supplied and charged to a customer such as fan belts, litres of oil, or tires – these are things that are kept in inventory. Consider creating a hierarchy for products (goods / services); make up reasonable attributes.
This service station has customers that fall into two groups: some are private individuals and others are businesses. Individuals will have a first name, last name, address and phone number. A business will have a business name, address, phone number and a contact person who has a first name and last name. Consider creating a hierarchy for customers.
The service station needs to keep track of all the goods and services it provides to its customers so that it has a historical record and knows what it has charged to each customer. Each visit to the service station by a customer will generate a work order that keeps track of the work that was done for the customer’s vehicle. Vehicles have license plate numbers, and other attributes to describe them (make, model, colour, ...). For each visit of a customer to the station the system needs to know the date the visit occurred, the details of the work performed and goods provided, and the total charge to the customer.
Which of the following is the correct technique for representing an M N relationship using the relational model?
Which of the following is the correct technique for representing a M:N relationship using the relational model? An intersection relation is created, and the keys of both parent entities are placed as a composite key in the intersection relation.Which of the following is true when implementing a one
17) Which of the following is true when implementing a one-to-many relationship? The file on the many end has a foreign key consisting of the primary key on the one end.Which is not a step of the database design process?
Hence, Add the Data is not a step of the database design process.Which of the following is the correct technique for representing a 1 N relationship in the relational model quizlet?
Which of the following is the correct technique for representing a 1:N relationship in the relational model? The key of the entity on the one side is placed into the relation for the entity on the many side. Tải thêm tài liệu liên quan đến nội dung bài viết Which of the following is not true about representing subtypes in a relational database design?
Post a Comment