Scribd
Upload
3 views3 pages

Modified Assignment

The document outlines several case studies for database design, including the International Coding Contest System, Spare Parts Management, Workshop Scheduling System, Company Structure Database, and Repair Shop System. Each case study includes rewritten assumptions and a relational schema in modified notation, detailing entities and their relationships. The schemas cover various aspects such as team management, sales operations, workshop participation, employee structure, and repair job handling.

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
3 views3 pages

Modified Assignment

The document outlines several case studies for database design, including the International Coding Contest System, Spare Parts Management, Workshop Scheduling System, Company Structure Database, and Repair Shop System. Each case study includes rewritten assumptions and a relational schema in modified notation, detailing entities and their relationships. The schemas cover various aspects such as team management, sales operations, workshop participation, employee structure, and repair job handling.

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 Models &


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

Case Study 1: International Coding Contest System

Assumptions (Rewritten)
- Each team and coder is uniquely identified by an ID.

- A team leader is also treated as a normal coder with an additional role.

- Teams may be categorized as host or international.

- European teams are considered a subset of international teams.

- Competitions are divided into practice and official categories.

Relational Schema (Modified Notation)


T(TeamID, Name, Country, LeadID)

C(CoderID, Name, Level, Skill, TeamID)

COMP(CompID, DateTime, Platform, Result)

T_COMP(TeamID, CompID)

Case Study 2: Spare Parts Management

Assumptions (Rewritten)
- Each shop operates independently and has multiple sales staff.

- A hierarchical supervision exists among sales staff.

- Customers are handled by a single sales agent.

- Orders can contain multiple parts.


Relational Schema (Modified Notation)
SHOP(ShopID, Location)

SB(SaleBoyID, ShopID, SupervisorID)

CUST(CustomerID, AgentID)

ORD(OrderID, CustomerID)

ORD_PART(OrderID, PartID)

Case Study 3: Workshop Scheduling System

Assumptions (Rewritten)
- Workshops can span multiple days.

- Each workshop is assigned a fixed room.

- Participants can attend multiple workshops.

Relational Schema (Modified Notation)


W(WorkshopID, Name, RoomID)

P(ParticipantID, Name, Email)

REG(PID, WID)

Case Study 4: Company Structure Database

Assumptions (Rewritten)
- Departments have unique identifiers and managers.

- Employees belong to one department but may work on many projects.

- Dependents are linked to employees.

- Employees can supervise other employees.

Relational Schema (Modified Notation)


EMP(CNIC, Name, DeptID, Supervisor)

DEPT(DeptID, ManagerID)

PROJ(ProjID, DeptID)

WORKS(CNIC, ProjID)
Case Study 5: Repair Shop System

Assumptions (Rewritten)
- Each repair job is linked to a customer.

- Repairmen may specialize in multiple repair types.

- Multiple items can be used in a repair job.

- Payments are split into advance and final.

Relational Schema (Modified Notation)


JOB(JobID, CustomerID, Cost)

RM(RepairmanID)

JOB_ITEM(JobID, ItemID)

JOB_RM(JobID, RepairmanID)

You might also like