For each of the descriptions below, perform the following tasks: i. Identify the degree...
For each of the descriptions below, perform the following tasks:
i. Identify the degree and cardinalities of the relationship.
ii. Express the relationships in each description graphically with an E-R diagram.
a. A book is identified by its ISBN number, and it has a title, a price, and a date of publication. It is published by a publisher, which has its own ID number and a name. Each book has exactly one publisher, but one publisher typically publishes multiple books over time.
b. A book (see 2a) is written by one or multiple authors. Each author is identified by an author number and has a name and date of birth. Each author has either one or multiple books; in addition, occasionally data are needed regarding prospective authors who have not yet published any books.
c. In the context specified in 2a and 2b, better information is needed regarding the relationship between a book and its authors. Specifically, it is important to record the percentage of the royalties that belongs to a specific author, whether or not a specific author is a lead author of the book, and each author’s position in the sequence of the book’s authors.
d. A book (see 2a) can be part of a series, which is also identified as a book and has its own ISBN number. One book can belong to several sets, and a set consists of at least one but potentially many books.
e. A piano manufacturer wants to keep track of all the pianos it makes individually. Each piano has an identifying serial number and a manufacturing completion date. Each instrument represents exactly one piano model, all of which have an identification number and a name. In addition, the company wants to maintain information about the designer of the model. Over time, the company often manufactures thousands of pianos of a certain model, and the model design is specified before any single piano exists.
f. A piano manufacturer (see 2e) employs piano technicians who are responsible for inspecting the instruments before they are shipped to the customers. Each piano is inspected by at least two technicians (identified by their employee number). For each separate inspection, the company needs to record its date and a quality evaluation grade.
g. The piano technicians (see 2f) have a hierarchy of reporting relationships: Some of them have supervisory responsibilities in addition to their inspection role and have multiple other technicians report to them. The supervisors themselves report to the chief technician of the company.
h. A vendor builds multiple types of tablet computers. Each has a type identification number and a name. The key specifications for each type include amount of storage space and display type. The company uses multiple processor types, exactly one of which is used for a specific tablet computer type; obviously, the same processor can be used in multiple types of tablets. Each processor has a manufacturer and a manufacturer’s unique code that identifies it.
i. Each individual tablet computer manufactured by the vendor (see 2h) is identified by the type identification number and a serial number that is unique within the type identification. The vendor wants to maintain information about when each tablet is shipped to a customer.
j. Each of the tablet computer types (see 2h) has a specific operating system. Each technician the company employs is certified to assemble a specific tablet type–operating system combination. The validity of a certification starts on the day the employee passes a certification examination for the combination, and the certification is valid for a specific period of time that varies depending on tablet type–operating system combination.
Step-by-Step Solution
Degree of relationship:
The number of entity types that took part in the entity relationship is known as degree of relationships.
• There are three types of degree of relationships.
o Unary relationship
o Binary relationship
o Ternary relationship
Cardinality constraints:
Cardinality constraints will specify the number of entity instances that associates with each instance as another entity.
• It is an important data modeling notation.
• There are four types of cardinality constraints:
o Mandatory one
o Mandatory many
o Optional one
o Optional many
Entity-Relationship diagram:
The entity-relationship diagram is a graphical demonstration of entity-relationship model.
• A logical representation of business data for a business area is the entity-relationship model (E-R model).
o In the business environment, the E-R model is expressed in terms of entities and relationships between the entities.
o The properties of the entities and relationships are expressed in E-R model.
a. Degree, Cardinality and Entity-Relationship diagram for BOOK and PUBLISHER:
• The entity BOOK is having the attributes ISBN, Title, Price, and Publish Date.
• The entity PUBLISHER is having the attributes Publisher ID and Name.
• Each book is having exactly one publisher only, but one publisher may publish multiple books over time.
i. Degree, Cardinality of the entities BOOK and PUBLISHER:
• The entities BOOK and PUBLISHER are having a degree of two; this is a binary relationship.
• The cardinality of the entities PUBLISHER to BOOK is having the relationship One-to-Many.
ii. Entity-Relationship diagram for BOOK and PUBLISHER:
• The above ER-Diagram represents,
o The degree of the relationship between the entities BOOK and PUBLISHER is two.
o One-to-Many cardinality between the entities PUBLISHER and BOOK.
b. Degree, Cardinality and Entity-Relationship diagram for BOOK and AUTHOR:
• The entity BOOK is having the attributes ISBN, Title, Price, and Publish Date.
• The entity AUTHOR is having the attributes Author Number, Name and Date of Birth.
• Each author is having one or multiple books, and the data are also maintained if the authors have not published any books.
i. Degree, Cardinality of the entities BOOK and AUTHOR:
• The entities BOOK and AUTHOR is having a degree of two; this is a binary relationship.
• The cardinality of the entities AUTHOR to BOOK is having the relationship Many-to-Many.
ii.
• The above ER-Diagram represents,
o The degree of the relationship between the entities BOOK and AUTHOR is two.
o Many-to-Many cardinality is available between the entities BOOK and AUTHOR.
c. Degree, Cardinality and Entity-Relationship diagram for BOOK and AUTHOR:
• The entity BOOK is having the attributes ISBN, Title, Price, and Publish Date.
• The entity AUTHOR is having the attributes Author Number, Name and Date of Birth.
• The better information about the royalty is maintained with the attributes Royalty Pct, Lead Author, and Sequence Number.
i. Degree, Cardinality of the entities BOOK and AUTHOR:
• The entities BOOK and AUTHOR is having a degree of two; this is a binary relationship.
• The cardinality of the entities AUTHOR to BOOK is having the relationship Many-to-Many.
ii. Entity-Relationship diagram for BOOK and AUTHOR:
• The above ER-Diagram represents,
o The degree of the relationship between the entities BOOK and AUTHOR is two.
o Many-to-Many cardinality is available between the entities BOOK and AUTHOR.
d. Degree, Cardinality and Entity-Relationship diagram for BOOK:
• The entity BOOK is part of a series having the attributes ISBN, Title, Price, and Publish Date.
• The entity BOOK may belong to several sets and a set may consist a minimum of one but potentially many books.
i. Degree, Cardinality of the entities BOOK:
• The entities BOOK are having a degree of one; this is a unary relationship.
• The cardinality of the entity BOOK is having the relationship Many-to-Many.
ii. Entity-Relationship diagram for BOOK:
• The above ER-Diagram represents:
o The degree of the relationship for the entity BOOK is one.
o Many-to-Many cardinality is available in the entity BOOK.
e.
• The entity PIANO is having the attributes Serial Number, and Mfg Completion Date.
• The entity MODEL is having the attributes ID Number and Name.
• The entity DESIGNER is having the attributes Designer ID and Designer Name.
• The company wants to maintain the designer of a model in thousands of pianos.
i. Degree, Cardinality of the entities between PIANO, MODEL, DESIGNER:
• The entities PIANO, MODEL, and DESIGNER is having a degree of two; this is a binary relationship.
• The cardinality of the entities PIANO, MODEL, and DESIGNER is having the relationship One-to-Many.
ii. Entity-Relationship diagram for PIANO, MODEL, DESIGNER:
• The above ER-Diagram represents
o The degree of the relationship for the entities PIANO, MODEL, and DESIGNER is two.
o One-to-Many cardinality is available in the entities PIANO, MODEL, and DESIGNER.
f. Degree, Cardinality and Entity-Relationship diagram for PIANO, TECHNICIAN:
• The entity PIANO is having the attributes Serial Number, and Mfg Completion Date.
• The entity TECHNICIAN is having the attributes Employee Number.
• For the evaluation purpose the attributes Inspection Date and Quality Grade is maintained.
i. Degree, Cardinality of the entities between PIANO, TECHNICIAN:
• The entities PIANO, and TECHNICIAN is having a degree of two; this is a binary relationship.
• The cardinality of the entities PIANO, and TECHNICIAN is having the relationship Many-to-Many.
ii. Entity-Relationship diagram for PIANO, TECHNICIAN:
• The above ER-Diagram represents,
o The degree of the relationship for the entities PIANO, and TECHNICIAN is two.
o Many-to-Many cardinality is available between the entities PIANO, and TECHNICIAN.
g. Degree, Cardinality and Entity-Relationship diagram for TECHNICIAN:
• The entity TECHNICIAN is having the attributes Employee Number.
• Some of the situations technicians have supervisory responsibility and other technicians report to the supervisor.
i. Degree, Cardinality of the entity TECHNICIAN:
• The entity TECHNICIAN is having a degree one, this is a unary relationship.
• The cardinality of the entity TECHNICIAN is having the relationship One-to-Many.
ii.
• The above ER-Diagram represents,
o The degree of the relationship for the entity TECHNICIAN is one.
o One-to-Many cardinality is available in the entity TECHNICIAN.
h. Degree, Cardinality and Entity-Relationship diagram for TABLET TYPE, PROCESSOR TYPE:
• The entity TABLET TYPE is having the attributes Tablet ID Number, Tablet Name, Storage and Display.
• The entity Processor Type is having the attributes Mfg ID Number and Mfg Name.
• The processor used in one type tablet may be used in many other type tablets.
i. Degree, Cardinality of the entities between TABLET TYPE, PROCESSOR TYPE:
• The entities TABLET TYPE and PROCESSOR TYPE are having a degree of two; this is a binary relationship.
• The cardinality of the entities TABLET TYPE, and PROCESSOR TYPE is having the relationship Many-to-Many.
ii. Entity-Relationship diagram for TABLET TYPE, PROCESSOR TYPE:
• The above ER-Diagram represents,
o The degree of the relationship for the entities TABLET TYPE, and PROCESSOR TYPE is two.
o Many-to-Many cardinality is available between the entities TABLET TYPE, and PROCESSOR TYPE.
i. Degree, Cardinality and Entity-Relationship diagram for TABLET TYPE, TABLET COMPUTER, CUSTOMER:
• The entity TABLET TYPE is having the attributes Tablet ID Number, Tablet Name, Storage and Display.
• The entity TABLET COMPUTER is having the attributes Tablet Type, Serial Number and Shipping Date.
• The entity CUSTOMER information is maintained by the vendor.
i. Degree, Cardinality of the entities between TABLET TYPE, TABLET COMPUTER, CUSTOMER:
• The entities TABLET TYPE, TABLET COMPUTER, and CUSTOMER are having a degree of two; this is a binary relationship.
• The cardinality of the entities TABLET TYPE, TABLET COMPUTER, and CUSTOMER is having the relationship One-to-Many.
• In the same time the shipment to the customer is multiple then the relationship is Many-to-Many.
• The attribute Shipping Date becomes an attribute of that M: M relationship.
ii.
• The above ER-Diagram represents
o The degree of the relationship for the entities TABLET TYPE, TABLET COMPUTER, and CUSTOMER is two.
o One-to-Many cardinality is available between the entities TABLET TYPE, TABLET COMPUTER, and CUSTOMER.
j. Degree, Cardinality and Entity-Relationship diagram for TABLET TYPE, TECHNICIAN:
• The entity TABLET TYPE is having the attributes Tablet ID Number, Tablet Name, Storage and Display.
• The entity TECHNICIAN is validating the operating system and the tablet, based on the validation the certificate validity is determined.
o The certificate has the attributes Cert Pass Date, Cert End Date.
i. Degree, Cardinality of the entities between TABLET TYPE, TECHNICIAN:
• The entities TABLET TYPE, and TECHNICIAN is having a degree of two; this is a binary relationship.
• The cardinality of the entities TABLET TYPE, and TECHNICIAN is having the relationship Many-to-Many.
ii. Entity-Relationship diagram TABLET TYPE, TECHNICIAN:
• The above ER-Diagram represents:
o The degree of the relationship for the entities TABLET TYPE, and TECHNICIAN is two.
o Many-to-Many cardinality is available between the entities TABLET TYPE, and TECHNICIAN.
Most questions answered within 3 hours.
-
Calculating the space time for parallel reactions. m-Xylene is reacted over a ZSM-5 zeolit...
-
Determine Vo and ID for the networks of Fig. 2.160.FIG. 2.160
-
The truck travels along a circular road that has a radius of 50 m at a speed of 4 m/s. F...
-
A state legislature enacted a statute that required any motorcycle operator or passenger...
-
A 1024 × 1024 8-bit image with 5.3 bits/pixel entropy [computed from its histogram using E...
-
In Problem 3.3, we estimated the equationwhere we now report standard errors along with th...
-
In each of the following cases, deduce the nature of the light that is consistent with the...
-
Solve Example 20.5 such that the x, y, z axes move with curvilinear translation, Ω = 0 in...
-
In Fig. 6.43, if i = cos 4t and v = sin 4t, the element is:(a)a resistor(b) a capacitor(c)...
-
Sketch vo for each network of Fig. 2.181 for the input shown.FIG. 2.181
-
(Supplement B) Computing and Reporting Cash Flow Effectsof Sale of Plant and EquipmentDuri...
-
A 350-mL spherical flask contains 0.075 mol of an ideal gas at a temperature of 293 K. Wha...