Database Systems – Practical Assignment

Poznan University of Technology, AI Specialization
Instructor: Dr. Serhii Baraban
Course: Database Systems
Topic: Entity and Referential Integrity, ERD, and Relational Diagrams

---

🎯 Objective

This practical assignment develops your ability to:

• Identify primary and foreign keys in relational tables
• Verify entity and referential integrity
• Recognize types of relationships between entities
• Create ER diagrams (conceptual level)
• Create relational diagrams (logical level)
• Explain integrity constraints and key dependencies

---

📘 Dataset Description

You will use three example databases shown in the textbook figures:

• Figure P3.1 – Ch03_StoreCo Ch03_StoreCo Tables
• Figure P3.10 – Ch03_BeneCo Ch03_BeneCo Tables
• Figure P3.17 – Ch03_TransCo Ch03_TransCo Tables

Each database consists of multiple related tables. The figures are provided in the /docs/ folder:

• fig_3_1.png – StoreCo Database
• fig_3_10.png – BeneCo Database
• fig_3_17.png – TransCo Database

---

🧩 Tasks

🧮 Part 1 – Using Figure P3.1 (Ch03_StoreCo)

Use the database shown in Figure P3.1 to answer Problems 1–9.

1. For each table, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None.
2. Do the tables exhibit entity integrity? Answer Yes or No, and explain your reasoning.
3. Do the tables exhibit referential integrity? Answer Yes or No, and explain. Write N/A if the table does not have a foreign key.
4. Describe the type(s) of relationship(s) between STORE and REGION.
5. Create the ERD showing the relationship between STORE and REGION.
6. Create the relational diagram showing the relationship between STORE and REGION.
7. Describe the type(s) of relationship(s) between EMPLOYEE and STORE.
   Hint: Each store employs many employees, one of whom manages the store.
8. Create the ERD for the relationship between EMPLOYEE and STORE.
9. Create the relational diagram for the same relationship.

---

🧮 Part 2 – Using Figure P3.10 (Ch03_BeneCo)

Use the database shown in Figure P3.10 to answer Problems 10–16.

The database contains four tables that reflect these relationships:

• An EMPLOYEE has only one JOB_CODE, but a JOB_CODE can be held by many employees.
• An EMPLOYEE can participate in many PLANs, and any PLAN can be assigned to many employees.
• The M:N relationship between EMPLOYEE and PLAN is resolved by the BENEFIT table, which acts as a bridge (composite) entity.

10. For each table, identify the primary key and the foreign key(s). If none, write None.
11. Create the ERD showing the relationship between EMPLOYEE and JOB.
12. Create the relational diagram for the same relationship.
13. Do the tables exhibit entity integrity? Answer and justify.
14. Do the tables exhibit referential integrity? Answer and justify. Write N/A if not applicable.
15. Create the ERD showing the relationships among EMPLOYEE, BENEFIT, JOB, and PLAN.
16. Create the relational diagram for the same four entities.

---

🧮 Part 3 – Using Figure P3.17 (Ch03_TransCo)

Use the database shown in Figure P3.17 to answer Problems 17–23.

17. For each table, identify the primary key and the foreign key(s). If none, write None.
18. Do the tables exhibit entity integrity? Explain.
19. Do the tables exhibit referential integrity? Explain. Write N/A if no foreign key exists.
20. Identify the TRUCK table’s candidate key(s).
21. For each table, identify a superkey and a secondary key.
22. Create the ERD for the TransCo database.
23. Create the relational diagram for the TransCo database.

---

🧱 Deliverables

File	Description
README.md	Contains written answers for all questions
/diagrams/StoreCo_ERD.png	ERD for Figure P3.1
/diagrams/BeneCo_ERD.png	ERD for Figure P3.10
/diagrams/TransCo_ERD.png	ERD for Figure P3.17
/diagrams/*.png	Corresponding relational diagrams
/report/explanation.pdf	Optional structured report with detailed reasoning

---

---

💻 Recommended Tools

• pgAdmin 4 or DBeaver for schema visualization
• draw.io, Lucidchart, or Visual Paradigm for ERD diagrams
• Markdown for answers (README.md)
• Export diagrams as .png for inclusion in the /diagrams/ folder

---

🧮 Evaluation Criteria

Aspect	Weight	Description
Key & Integrity Analysis	25%	Correctly identifies PK/FK and explains integrity
ERDs	25%	Clear, consistent Crow’s Foot or UML notation
Relational Diagrams	25%	Logical accuracy and correct relationships
Explanations / Reasoning	15%	Completeness and clarity
Repository Structure	10%	Organization, clarity, and GitHub commit quality

---

🏁 Submission Instructions

1. Complete your assignment and verify diagrams are readable.
2. Commit and push all changes to your GitHub Classroom repository before the deadline.
3. Ensure all required files are present and correctly named.

Example commit:

git add .
git commit -m "Final submission – Database Integrity and ERD Assignment"
git push origin main