ASSIGNMENT 1 Database Design: ER/EER Diagrams & Relational Mapping
Course: Database Systems | Semester: 4th
Submission Deadlines: Part (i) - ER/EER Diagrams: 24-Mar-2026 Part (ii) -
Relational Mapping: 27-Mar-2026
Case Study 1: International Coding Competition (ICC)
Part (i): ER/EER Diagram Description
Entities and Attributes
TEAM (Superclass) - TeamID (PK) - Name - Country - TeamLeadID (FK →
[Link])
HOST_TEAM (Subclass) - TeamID (PK/FK) - IsHostTeam
INTERNATIONAL_TEAM (Subclass) - TeamID (PK/FK) - Continent
EUROPEAN_TEAM (Subclass of INTERNATIONAL_TEAM) - TeamID
(PK/FK) - TravellingAllowanceRate
CODER - CoderID (PK) - Name - SeniorityLevel - SkillLevel - TeamID (FK) -
IsTeamLead
PROFESSIONAL_HISTORY (Multi-valued) - HistoryID (PK) - CoderID (FK) -
Organisation - Role - StartDate - EndDate
SUCCESS_STORY (Multi-valued) - StoryID (PK) - CoderID (FK) - Title -
Description - Year
ALLOWANCE_CLAIM - ClaimID (PK) - CoderID (FK) - AllowanceType -
Amount - ClaimDate
COMPETITION (Superclass) - CompetitionID (PK) - DateTime -
VirtualPlatform - Result
PRACTICE_COMPETITION - CompetitionID (PK/FK) - PracticeNotes
OFFICIAL_COMPETITION - CompetitionID (PK/FK) - PointRate -
MaxPointsPerCategory - MinPointsPerCategory - IndividualPointGain -
TeamPointGain
Relationships - TEAM participates_in COMPETITION (M:N →
TEAM_COMPETITION) - CODER belongs_to TEAM (M:1) - CODER submits
ALLOWANCE_CLAIM (1:N) - CODER has PROFESSIONAL_HISTORY (1:N) -
CODER has SUCCESS_STORY (1:N) - TEAM has TEAM_LEAD → FK TeamLeadID
- EER Specialisation constraints enforced as disjoint/partial/total as per
assumptions
Part (ii): Relational Model Mapping
Strong Entities: - TEAM(TeamID PK, Name, Country, TeamLeadID FK) -
CODER(CoderID PK, Name, SeniorityLevel, SkillLevel, TeamID FK,
IsTeamLead) - COMPETITION(CompetitionID PK, DateTime, VirtualPlatform,
Result)
Multi-valued Attributes: - PROFESSIONAL_HISTORY(HistoryID PK, CoderID
FK, Organisation, Role, StartDate, EndDate) - SUCCESS_STORY(StoryID PK,
CoderID FK, Title, Description, Year)
M:N Relationships: - TEAM_COMPETITION(TeamID FK, CompetitionID FK,
PK(TeamID, CompetitionID))
Specialisations: - HOST_TEAM(TeamID PK/FK, IsHostTeam) -
INTERNATIONAL_TEAM(TeamID PK/FK, Continent) - EUROPEAN_TEAM(TeamID
PK/FK, TravellingAllowanceRate) - PRACTICE_COMPETITION(CompetitionID
PK/FK, PracticeNotes) - OFFICIAL_COMPETITION(CompetitionID PK/FK,
PointRate, MaxPointsPerCategory, MinPointsPerCategory,
IndividualPointGain, TeamPointGain)
ALLOWANCE_CLAIM: - ALLOWANCE_CLAIM(ClaimID PK, CoderID FK,
AllowanceType, Amount, ClaimDate)
Case Study 2: Auto Spare Parts Business
Part (i): ER/EER Diagram Description
Entities: SHOP, SALE_BOY, CUSTOMER, ORDER, PART, ENGINEER, SUPPLIER
Relationships: SHOP employs SALE_BOY, SALE_BOY supervises SALE_BOY,
CUSTOMER places ORDER, ORDER contains PART, PART assembled_from
PART, ENGINEER involved_in ASSEMBLY, SUPPLIER supplies PART
Part (ii): Relational Model Mapping
Entities: - SHOP(ShopID PK, Location, ContactNumber) -
SALE_BOY(SaleBoyID PK, Name, ShopID FK, SupervisorID FK) -
CUSTOMER(CustomerID PK, Name, ContactInfo, AgentSaleBoyID FK) -
ORDER(OrderID PK, CustomerID FK, OrderDate, TotalAmount) - PART(PartID
PK, PartName, PartType, UnitPrice) - ENGINEER(EngineerID PK, Name,
Specialisation) - SUPPLIER(SupplierID PK, Name, ContactInfo)
M:N Relationships: - ORDER_PART(OrderID FK, PartID FK, Quantity,
UnitPriceAtTime, PK(OrderID, PartID)) - PART_ASSEMBLY(AssembledPartID FK,
ComponentPartID FK, EngineerID FK, AssemblyDate, PK(AssembledPartID,
ComponentPartID, EngineerID)) - SUPPLIER_PART(SupplierID FK, PartID FK,
SupplyPrice, LeadTimeDays, PK(SupplierID, PartID))
Case Study 3: LGU Workshop Management
Part (i): ER/EER Diagram Description
Entities: VENUE, MEETING_ROOM, WORKSHOP, WORKSHOP_DATE,
PARTICIPANT Relationships: VENUE contains MEETING_ROOM, WORKSHOP
assigned_to MEETING_ROOM, WORKSHOP has_dates WORKSHOP_DATE,
PARTICIPANT registers_for WORKSHOP
Part (ii): Relational Model Mapping
Entities: - VENUE(VenueID PK, VenueName, Address) -
MEETING_ROOM(VenueID FK, Floor, RoomNumber, SeatingCapacity,
PK(VenueID, Floor, RoomNumber)) - WORKSHOP(WorkshopID PK, Name,
ReferenceID UNIQUE, VenueID FK, Floor FK, RoomNumber FK) -
PARTICIPANT(ParticipantID PK, Name, Email UNIQUE) -
WORKSHOP_DATE(WorkshopID FK, SessionDate, PK(WorkshopID,
SessionDate)) - REGISTRATION(ParticipantID FK, WorkshopID FK,
RegistrationDate, PK(ParticipantID, WorkshopID))
Case Study 4: Company Database (COMPANY Schema)
Part (i): ER/EER Diagram Description
Entities: DEPARTMENT, EMPLOYEE, PROJECT, DEPT_LOCATION,
PROJECT_LOCATION, DEPENDENT
Relationships: - DEPARTMENT managed_by EMPLOYEE (1:1) - EMPLOYEE
works_in DEPARTMENT (M:1) - EMPLOYEE supervises EMPLOYEE (1:N) -
DEPARTMENT has_locations (1:N) - DEPARTMENT controls PROJECT (1:N) -
PROJECT has_locations (1:N) - EMPLOYEE works_on PROJECT (M:N) -
EMPLOYEE has DEPENDENT (1:N weak entity)
Part (ii): Relational Model Mapping
Strong Entities: - DEPARTMENT(DeptNumber PK, DeptName UNIQUE,
ManagerCNIC FK, ManagerStartDate) - EMPLOYEE(CNIC PK, FirstName,
LastName, Address, Salary, Gender, BirthDate, DeptNumber FK,
SupervisorCNIC FK) - PROJECT(ProjNumber PK, ProjName UNIQUE, ProjType,
DeptNumber FK)
Weak Entity: - DEPENDENT(EmpCNIC FK, DependentName PK, Gender,
BirthDate, Relationship, PK(EmpCNIC, DependentName))
Multi-valued Attributes: - DEPT_LOCATION(DeptNumber FK, Location,
PK(DeptNumber, Location)) - PROJECT_LOCATION(ProjNumber FK, Location,
PK(ProjNumber, Location))
M:N Relationships: - WORKS_ON(EmpCNIC FK, ProjNumber FK,
HoursPerWeek, PK(EmpCNIC, ProjNumber))
Business Rules: Circular FK handled via staged insertions.
Case Study 5: Computer Repair Shop
Part (i): ER/EER Diagram Description
Entities: CUSTOMER, REPAIR_JOB, REPAIR_TYPE, REPAIRMAN, ITEM,
PAYMENT Relationships: CUSTOMER submits REPAIR_JOB, REPAIR_JOB is_of
REPAIR_TYPE, REPAIRMAN specializes_in REPAIR_TYPE, REPAIRMAN performs
REPAIR_JOB, REPAIR_JOB uses ITEM, REPAIR_JOB has PAYMENT
Part (ii): Relational Model Mapping
Strong Entities: - CUSTOMER(CustomerID PK, Name, ContactNumber,
Email) - REPAIR_TYPE(RepairTypeID PK, TypeName, Description) -
REPAIR_JOB(JobID PK, CustomerID FK, JobType FK, JobDetail, AdvanceDeposit,
TotalCost, RemainingAmount, ReturnDate, Status) -
REPAIRMAN(RepairmanID PK, Name, ContactNumber) - ITEM(ItemID PK,
ItemName, UnitCost, StockQuantity) - PAYMENT(PaymentID PK, JobID FK,
PaymentType, Amount, PaymentDate, ReceiptNumber UNIQUE)
M:N Relationships: - REPAIRMAN_SPECIALISATION(RepairmanID FK,
RepairTypeID FK, PK(RepairmanID, RepairTypeID)) - JOB_REPAIRMAN(JobID
FK, RepairmanID FK, LabourCost, PK(JobID, RepairmanID)) - JOB_ITEM(JobID
FK, ItemID FK, QuantityUsed, CostAtTime, PK(JobID, ItemID))
Business Rules: RemainingAmount = TotalCost - AdvanceDeposit; Job
cannot be collected until Status = Completed and Final payment recorded.
Summary Table of Relations
Case Study Domain Relations Produced
CS 1 ICC TEAM, HOST_TEAM,
Competition INTERNATIONAL_TEAM,
EUROPEAN_TEAM, CODER,
PROFESSIONAL_HISTORY,
SUCCESS_STORY, COMPETITION,
PRACTICE_COMPETITION,
Case Study Domain Relations Produced
OFFICIAL_COMPETITION,
TEAM_COMPETITION,
ALLOWANCE_CLAIM
CS 2 Auto Spare SHOP, SALE_BOY, CUSTOMER,
Parts ORDER, PART, ENGINEER,
SUPPLIER, ORDER_PART,
PART_ASSEMBLY, SUPPLIER_PART
CS 3 LGU VENUE, MEETING_ROOM,
Workshops WORKSHOP, WORKSHOP_DATE,
PARTICIPANT, REGISTRATION
CS 4 Company HR DEPARTMENT, EMPLOYEE,
PROJECT, DEPENDENT,
DEPT_LOCATION,
PROJECT_LOCATION, WORKS_ON
CS 5 Repair Shop CUSTOMER, REPAIR_JOB,
REPAIR_TYPE, REPAIRMAN, ITEM,
PAYMENT,
REPAIRMAN_SPECIALISATION,
JOB_REPAIRMAN, JOB_ITEM