Q1: Design an ER diagram for the following Dental Clinic database. Your diagram
ID: 3671663 • Letter: Q
Question
Q1: Design an ER diagram for the following Dental Clinic database. Your diagram should have all the needed details. You may make any reasonable assumptions but you have to state them clearly.
The clinic has several dentists. Each dentist has a unique Number, name, nationality, multiple room-number, salary, birth-date and home-address (Box, City, Zip).
Each client (patient) has a unique Code, name, home-phone, work-phone, address, and birth-date. Each client is assigned to one dentist. All future visits will be to the same dentist. A client can be insured or self-paying. An insured client should have an insurance company-name, and company-Phone, while a self-paying client must have a bank-name and a bank account.
Each visit of a client is described by a date, type, action, fee, and date-of-next-appointment
1.Design an Entity Relationship (ER) Model to represent the above requirements. (2 Point)
2.Convert an ER Diagram that presented in part (A) to Rational Schema. (1 Point)
Q2.
Explain the distinction between condition-defined and user-defined constraints. Which of these constraints can the system check automatically? (0.75 Point)
Explain the distinction between disjoint and overlapping constraints. (0.75 Point)
Explain the distinction between total and partial constraints. (0.5 Point)
Explanation / Answer
2)
In a generalization – specialization hierarchy, it must be possible to decide which entities are members of which lower level entity sets. In a condition defined design constraint, membership in the lower level entity-sets is evaluated on the basis of whether or not an entity satisfies an explicit condition or predicate.User-defined lower-level entity sets are not constrained by a membership condition; rather, entities are assigned to a given entity set by the database user.
Condition-defined constraints alone can be automatically handled by the system. Whenever any tuple is inserted into the database, its membership in the various lower level entity-sets can be automatically decided by evaluating the respective membership predicates. Similarly when a tuple is updated, its membership in the various entity sets can be re-evaluated automatically.
In a disjointness design constraint, an entity can belong to not more than one lower-level entity set. In overlapping generalizations, the same entity may belong to more than one lower-level entity sets. For example, in the employee-workteam example of the book, a manager may participate in more than one work-team.
In a total design constraint, each higher-level entity must belong to a lower-level entity set. The same need not be true in a partial design constraint. For instance, some employees may belong to no work-team.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.