1. Circle the integrity constraint(s) that is violated in the following. Domain
ID: 3564481 • Letter: 1
Question
1. Circle the integrity constraint(s) that is violated in the following.
Domain constraint Entity constraint Referential constraint
2. Discuss any problem with transforming the following E-R model into a relational data model shown in the following table:
STUDENT
SSN
Name
DEPARTMENT
DeptName
Address
Phone
SSN
JoiningDate
3. Transform the following E-R model into a relational data model.
4. Transform the following E-R model into a relational data model. The attributes of ITEM are ID, Name, Description.
5. Transform the following E-R model into a relational data model. The attributes of PERSON are SSN, Name, Address.
SSN
Name
Explanation / Answer
Q&A: 5936391 AID: 5897 | 09/10/2014
1)
The important integrity constraints are Domain integrity, Entity integrity and Referential integrity. Domain integrity is all about defining a valid set of values for an attribute. Entity integrity constraint states that there must be a proper value in the primary key field. That is, the primary key cannot be null. The referential integrity constraint is specified always between two tables which maintain the consistency among the rows of two tables.
Consider the STUDENT table as primary table and DEPARTMENT table as secondary or related table of STUDENT.
The Domain integrity constraint is not violated because all the attributes are defined is true to their correctness. Entity integrity constraints are also not violated in STUDENT and DEPARTMENT tables, as all the primary keys of both primary table and related table are filled.
The integrity constraint which is being violated is referential constraint. Referential integrity constraint states that when a table is related to a primary table, the related table should contain all the entries in its primary key from the primary table. As per the rule DeptName is the primary key in DEPARTMENT table which is from STUDENT table and it does not contain all the entries from the primary table. That is, BME field is missing. Hence Referential constraint is violated.
2)
Consider STUDENT as primary table and DEPARTMENT as related table. When the ER diagram is transformed into a relational model, the Referential integrity constraints should not be violated. The referential constraints should be maintained by having any field form the primary table as the primary key in related table. As per this rule, the STUDENT table does not contain the primary key of DEPARTMENT table in it. Hence the foreign key relationship is not maintained and referential integrity constraints are violated.
3)
SHOW
ShowID
ShowDay
ShowTime
NumberOfTickets
TICKET
TicketNumber
Price
SELL
ShowID
TicketNumber
4)
ITEM
ID
Name
Description
CONTAINS
ID
Quantity
5)
PERSON
SSN
Name
Address
ISMARRIED
SSN
Date
ShowID
ShowDay
ShowTime
NumberOfTickets
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.