Scribd
Upload
9 views3 pages

Final Database Assignment

The document outlines database design assignments focused on creating ER/EER diagrams and relational models for five distinct case studies: ICC Competition, Auto Spare Parts, Workshop Management, Company Database, and Repair Shop. Each case study includes entity descriptions, relationships, and corresponding relational models detailing primary and foreign keys. The assignments emphasize the importance of understanding entity relationships and mapping them into a structured database format.

Uploaded by

birabia007
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
9 views3 pages

Final Database Assignment

The document outlines database design assignments focused on creating ER/EER diagrams and relational models for five distinct case studies: ICC Competition, Auto Spare Parts, Workshop Management, Company Database, and Repair Shop. Each case study includes entity descriptions, relationships, and corresponding relational models detailing primary and foreign keys. The assignments emphasize the importance of understanding entity relationships and mapping them into a structured database format.

Uploaded by

birabia007
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

ASSIGNMENT 1

Database Design: ER/EER Diagrams &


Relational Mapping
Name: NABEEL ARIF
Class: BSSE (B)
Roll No: FA-24/059

Case Study 1: ICC Competition

Part (i): ER/EER Description


Entities: TEAM, CODER, COMPETITION, ALLOWANCE_CLAIM

Relationships: TEAM participates in COMPETITION (M:N)

Specialization: TEAM -> HOST_TEAM, INTERNATIONAL_TEAM

Coder belongs to one TEAM

Part (ii): Relational Model


TEAM(TeamID PK, Name, Country, TeamLeadID FK)

CODER(CoderID PK, Name, SeniorityLevel, SkillLevel, TeamID FK)

COMPETITION(CompetitionID PK, DateTime, Platform, Result)

TEAM_COMPETITION(TeamID FK, CompetitionID FK)

Case Study 2: Auto Spare Parts

Part (i): ER/EER Description


Entities: SHOP, SALE_BOY, CUSTOMER, ORDER, PART

SALE_BOY supervises SALE_BOY (1:N)

CUSTOMER places ORDER (1:N)

ORDER contains PART (M:N)


Part (ii): Relational Model
SHOP(ShopID PK, Location)

SALE_BOY(SaleBoyID PK, ShopID FK, SupervisorID FK)

CUSTOMER(CustomerID PK, AgentSaleBoyID FK)

ORDER(OrderID PK, CustomerID FK)

ORDER_PART(OrderID FK, PartID FK)

Case Study 3: Workshop Management

Part (i): ER/EER Description


Entities: WORKSHOP, PARTICIPANT, ROOM

WORKSHOP assigned to ROOM

PARTICIPANT registers for WORKSHOP (M:N)

Part (ii): Relational Model


WORKSHOP(WorkshopID PK, Name, RoomID FK)

PARTICIPANT(ParticipantID PK, Name, Email)

REGISTRATION(ParticipantID FK, WorkshopID FK)

Case Study 4: Company Database

Part (i): ER/EER Description


Entities: EMPLOYEE, DEPARTMENT, PROJECT, DEPENDENT

EMPLOYEE works on PROJECT (M:N)

EMPLOYEE supervises EMPLOYEE

Part (ii): Relational Model


EMPLOYEE(CNIC PK, Name, DeptNumber FK, SupervisorCNIC FK)

DEPARTMENT(DeptNumber PK, ManagerCNIC FK)

PROJECT(ProjNumber PK, DeptNumber FK)

WORKS_ON(CNIC FK, ProjNumber FK)


Case Study 5: Repair Shop

Part (i): ER/EER Description


Entities: CUSTOMER, REPAIR_JOB, REPAIRMAN, ITEM

REPAIR_JOB uses ITEM (M:N)

REPAIRMAN performs JOB (M:N)

Part (ii): Relational Model


REPAIR_JOB(JobID PK, CustomerID FK, TotalCost)

REPAIRMAN(RepairmanID PK)

JOB_ITEM(JobID FK, ItemID FK)

JOB_REPAIRMAN(JobID FK, RepairmanID FK)

You might also like