This assignment is to practice reading and creating entity-relationship (ER) dia

Question

This assignment is to practice reading and creating entity-relationship (ER) diagrams. Make sure the ER diagrams are legible and that relationships are properly documented, including cardinalities (1:1/ 1:n/ n:m), participation (total/partial) and primary keys.

Use the ER diagram provided to answer questions about a data base for Belmont Landscapes.

1. List two-non key attributes in the Customer Entity.

2. What is the cardinality of the Places relationship?

3. What is the participation of invoices in the Generates relationship?

4. Do you think the primary key for the Customer entity is a "natural" or "surrogate" key? Why?

5. Map the ER diagram to a relational schema to obtain the columns for the tables

6. What is the foreign key that creates a relationship between the Customer and Contract entities? Which table contains the foreign key, Customer or Contract?

7. What is the foreign key that creates a relationship between the Contract and Invoice entities? Which table contains the foreign key, Customer or Contract?

8. Explain the term "referential integrity" as applied to the relationship between the Contract and Invoice entities. Use sample data to explain how referential integrity could protect the integrity of the data.

firstName lastName phone date comparmy item custID Customer PlacesContractGenerates Invoice itemNumb address type city date state email

Explanation / Answer

1)All the non key attributes in the Customer Entity is

firstName,lastName,phone,company,address,city,state,zip,email

2)The places is the one to many relationship which involves total participation.

3)The participation of invoices is one to many which involves total participation.

4)The primary key of the customer relationship is natural because the custID is unique for every customer.

5)E-R to Relational

1)Every entities is a table in relational and write down all primary keys and entities with thier attributes

Customer(custID,firstName,lastName,phone,company,address,city,state,zip,email)

Contract(contractName,date,amount,type)

Invoice(invoiceNum,amount,date,item,itemNumer)

2)Identify the relationships between entities

here all are one to many relationship .In that case we include the primary key of one side as foreign key on many side .

On including the Final tables are

Customer(custID,firstName,lastName,phone,company,address,city,state,zip,email) Pk:custID

Contract(contractName,custID,date,amount,type)  Pk:contractName, FK:custID

Invoice(invoiceNum,amount,date,item,itemNumer)  Pk:invoiceNum, FK:contractName

6)The foreign key that creates a relationship between the Customer and Contract entities is custID and Contract table contains the foreign key.

7)The foreign key that creates a relationship between the Contract and Invoice entities is contractName and Invoice table contains the foreign key.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.