Database Design Assignment (ER/EER
and Relational Mapping)
CASE STUDY 1
Entities:
- Team (TeamID, Name, Country)
- Coder (CoderID, Name, SeniorityLevel, SkillLevel, ProfessionalHistory)
- Competition (CompetitionID, DateTime, Platform, Result)
- SuccessStory (StoryID, Description)
Relationships:
- Team has many Coders (1:M)
- Team Lead is a Coder (1:1)
- Coder has SuccessStories (1:M)
- Team participates in Competition (M:N)
Relational Mapping:
Team(TeamID PK, Name, Country, TeamLeadID FK)
Coder(CoderID PK, Name, SeniorityLevel, SkillLevel, ProfessionalHistory, TeamID FK)
SuccessStory(StoryID PK, Description, CoderID FK)
Competition(CompetitionID PK, DateTime, Platform, Result)
Participation(TeamID FK, CompetitionID FK)
--------------------------------------------------
CASE STUDY 2
Entities:
- Shop (ShopID, Location)
- SaleBoy (SaleBoyID, Name)
- Customer (CustomerID, Name)
- Order (OrderID, Date)
- AutoPart (PartID, Name)
- Supplier (SupplierID, Name)
- Engineer (EngineerID, Name)
Relationships:
- Shop employs SaleBoys (1:M)
- SaleBoy supervises SaleBoy (1:M recursive)
- SaleBoy manages Customer (1:M)
- Customer places Order (1:M)
- Order contains AutoParts (M:N)
- AutoPart composed of AutoParts (M:N recursive)
- Supplier supplies AutoPart (M:N)
- Engineer assembles AutoPart (M:N)
Relational Mapping:
Shop(ShopID PK, Location)
SaleBoy(SaleBoyID PK, Name, ShopID FK, SupervisorID FK)
Customer(CustomerID PK, Name, SaleBoyID FK)
OrderTable(OrderID PK, Date, CustomerID FK)
AutoPart(PartID PK, Name)
OrderPart(OrderID FK, PartID FK)
PartAssembly(PartID FK, SubPartID FK)
Supplier(SupplierID PK, Name)
Supply(SupplierID FK, PartID FK)
Engineer(EngineerID PK, Name)
AssemblyEngineer(EngineerID FK, PartID FK)
--------------------------------------------------
CASE STUDY 3
Entities:
- Workshop (WorkshopID, Name, Venue)
- Participant (ParticipantID, Name, Email)
- Room (RoomID, Floor, Capacity)
Relationships:
- Workshop occurs in Room (1:1)
- Workshop has multiple Dates
- Participant registers for Workshop (M:N)
Relational Mapping:
Workshop(WorkshopID PK, Name, Venue, RoomID FK)
Participant(ParticipantID PK, Name, Email)
Room(RoomID PK, Floor, Capacity)
WorkshopDate(WorkshopID FK, Date)
Registration(ParticipantID FK, WorkshopID FK)
--------------------------------------------------
CASE STUDY 4
Entities:
- Department (DeptID, Name)
- Employee (EmpID, Name, CNIC, Address, Salary, Gender, BirthDate)
- Project (ProjectID, Name, Type)
- Dependent (DependentID, Name, Gender, BirthDate, Relationship)
Relationships:
- Department managed by Employee (1:1)
- Department has Projects (1:M)
- Employee works on Project (M:N)
- Employee supervises Employee (1:M recursive)
- Employee has Dependents (1:M)
Relational Mapping:
Department(DeptID PK, Name, ManagerID FK, StartDate)
Employee(EmpID PK, Name, CNIC, Address, Salary, Gender, BirthDate, DeptID FK,
SupervisorID FK)
Project(ProjectID PK, Name, Type)
WorksOn(EmpID FK, ProjectID FK, Hours)
Dependent(DependentID PK, Name, Gender, BirthDate, Relationship, EmpID FK)
--------------------------------------------------
CASE STUDY 5
Entities:
- Customer (CustomerID, Name)
- RepairJob (JobID, Type, TotalCost, AdvanceDeposit, ReturnDate)
- Repairman (RepairmanID, Name)
- Item (ItemID, Name)
Relationships:
- Customer requests RepairJob (1:M)
- RepairJob uses Items (M:N)
- Repairman performs RepairJob (M:N)
- Repairman specializes in RepairType (M:N)
Relational Mapping:
Customer(CustomerID PK, Name)
RepairJob(JobID PK, Type, TotalCost, AdvanceDeposit, ReturnDate, CustomerID FK)
Item(ItemID PK, Name)
JobItem(JobID FK, ItemID FK)
Repairman(RepairmanID PK, Name)
JobRepairman(JobID FK, RepairmanID FK)
Specialization(RepairmanID FK, Type)