SQL and Entity Relationships Homework Sample

SQL and Entity Relationships Homework Sample

Where is a null value appropriate? What are the differences between an entity, an entity type, and an entity set? What is a recursive relationship type? Given the entity relationship diagram for an airline reservations system, what are the constraints that should be used. What are the cardinality ratios between the 2 entities listed. For more SQL assignment help contact us for details.

Solution:

3.2. List the various cases where use of a NULL value would be appropriate.

There are cases in which an entity is not applicable for an attribute. For example, the “apartment number” attribute in the Employee entity only makes sense when the employee lives in an apartment building.
In other cases, the value for an attribute may be unknown, like a “phone number” attribute of a given employee or mail address.

3.4. What is an entity type? What is an entity set? Explain the differences among
an entity, an entity type, and an entity set.

An entity type defines a collection of entities that have the same attributes. For example, a database could have an entity for holding employees’ data called Employee determining how the employee type is defined. The collection of all entities of a particular entity type in the database at any point in time is called an entity set or entity collection. They both are different concepts.

3.5. Explain the difference between an attribute and a value set.

An attribute is a property of an entity. Value set is the domain of that attribute. In other words, is the set of valid values an attribute can hold.

3.9. Under what conditions can an attribute of a binary relationship type be
migrated to become an attribute of one of the participating entity types?

Only when the binary relationship is of type 1:1 or 1:N.

3.11. What is meant by a recursive relationship type? Give some examples of
recursive relationship types.

Recursive relationships are those in which the same entity participates more than once in different roles. For example, an Employee entity can have a recursive relationship called “supervises” indicating that all employees have a supervisor. Therefore, the entity itself is playing both sides of the binary relationship.

3.13. Can an identifying relationship of a weak entity type be of a degree greater
than two? Give examples to illustrate your answer.
Yes, it can if the weak entity has more than one owner entity type. For example, suppose that an employment agency database keeps track of candidates interviewing for jobs at various companies. A candidate can have multiple interviews with the same company with different departments, and a job offer is made based on one of the interviews. The INTERVIEW entity is represented as a weak entity with two owners CANDIDATE and COMPANY.

Part B: Exercise

3.16. Which combinations of attributes have to be unique for each individual
SECTION entity in the UNIVERSITY database shown in Figure 3.20 to enforce
each of the following miniworld constraints:
a. During a particular semester and year, only one section can use a particular
classroom at a particular DaysTime value.

(SecID,Sem,Year,DaysTime,CRoom)

b. During a particular semester and year, an instructor can teach only one
section at a particular DaysTime value.

(SecID,Sem,Year,DaysTime)

c. During a particular semester and year, the section numbers for sections
offered for the same course must all be different.
Can you think of any other similar constraints?

(SecID,Sem,Year)

3.19. Consider the ER diagram in Figure 3.21, which shows a simplified schema
for an airline reservations system. Extract from the ER diagram the requirements
and constraints that produced this schema. Try to be as precise as
possible in your requirements and constraints specification.

⦁ Airport can land several types of airplanes
⦁ Every airplane belongs to one airplane type only
⦁ Every leg instance is assigned to only one plane
⦁ A leg instance can have several seats. Every seats is associated with a leg instance
⦁ A flight has several legs
⦁ Fares are associated to only one flight.

3.23. Consider the ER diagram shown in Figure 3.22 for part of a BANK database.
Each bank can have multiple branches, and each branch can have multiple
accounts and loans.
⦁ List the strong (nonweak) entity types in the ER diagram.
BANK, ACCOUNT, CUSTOMER, LOAN.
b. Is there a weak entity type? If so, give its name, partial key, and identifying
relationship.
Yes, the BANK_BRANCH entity. The partial key is Branch_no and the identifying relationships is the “Branches” relationship with the BANK entity.

c. What constraints do the partial key and the identifying relationship of the
weak entity type specify in this diagram?

That a bank branch must only be part of one bank and cannot exist without the BANK entity.

d. List the names of all relationship types, and specify the (min, max)
constraint on each participation of an entity type in a relationship type.
Justify your choices.

“Branches” Relationship between BANK and BANK_BRANCH – (1,N)
“ACCTS” Relationship between ACCOUNT and BANK_BRANCH – (N,1)
“LOANS” Relationship between LOAN and BANK_BRANCH – (N,1)
“L_C” Relationship between CUSTOMER and LOAN – (N,M)
“A_C” Relationship between CUSTOMER and ACCOUNT – (N,M)

3.27. Cardinality ratios often dictate the detailed design of a database. The cardinality
ratio depends on the real-world meaning of the entity types involved
and is defined by the specific application. For the following binary relationships,
suggest cardinality ratios based on the common-sense meaning of the
entity types. Clearly state any assumptions you make.

1 – 1:1
2 – 1:N
3 – N:M (A wall can be share between classrooms)
4 – 1:1
5 – N:M (It is assumed that a textbook can be used in many courses and a course uses many textbook).
6 – N:M
7 – N:M
8 – N:1 (It is assumed that only one instructor per class is needed)
9– 1:N (It is assumed that an instructor belongs to only one office, but an office may have several instructors)
10 – N:1