ER Modeling Exercises Transformation Solutions

1
ER Modeling Exercises Transformation Solutions
2
ER Modeling I handout - Q1

• DIVISION (DivisionID,ManagerID)
• DEPARTMENT (DeptID,DivisionID)
• EMPLOYEE (EmpID, DeptID)
• PROJECT (ProjectID,)
• EMPLOYEE_PROJECT (EmpID, ProjectID,)

Case 4
Case 6
Case 7
Case 10
Case 3
null allowed - all others not null
3
ER Modeling I - Q2

• INSTRUCTOR (InstructorID, HighestDegree, )
• COURSE (CourseID, ClassTitle, )
• CLASS (ClassID, CourseID, InstructorID, Term)
• TRAINEE (TraineeID, )
• ENROLL (TraineeID, ClassID, Term)

Case 6
Case 6
Case 3
Optionally, create an EnrollmentID attribute to
use as primary key.
All foreign keys not null.
4
ER Modeling I - Q3
Case 4

• CUSTOMER (CustomerID, )
• INVOICE (InvoiceID, CustomerID, SalesRepID,)
• LINE (InvoiceID, LineID, ProdID,)
• PRODUCT (ProductID, )
• SALESREP (SalesRepID, )
• VENDOR (VendorID,)
• SHIP (ShipID, ProductID, VendorID,)

Case 1
Case 4
Case 1
All foreign keys not null.
5
ER Modeling I - Q4

• AGENT (AgentID, LName, Region)
• CLIENT (ClientID, LName,)
• MUSICIAN (MusicianID, AgentID, Name,
  DaysAvailable,)
• EVENT (EventID, ClientID, MusicianID, Date, Time,
  Location)
• INSTRUMENT (InsturmentID, )
• MUSICIAN_INSTRUMENT (MusicianID, InstrumentID,
  YearsExperience)

All foreign keys not null.
6
ER Modeling I - Q5

• CITY (CityID, )
• TEAM (TeamID, CoachID, CityID, )
• PLAYER (PlayerID, TeamID,)
• COACH (CoachID, TeamID,)
• GAME (GameID, HomeTeamID, VisitorTeamID,)

All foreign keys not null.
7
ER Modeling II - Q1

• COMPANY (CompanyID, )
• DEPARTMENT (DepartmentID, CompanyID)
• EMPLOYEE (EmployeeID, DepartmentID, )
• DEPENDENT (EmployeeID, DependentID, )
• EMPLOYEE_HISTORY (EmployeeID, HistoryID, )

All foreign keys are not null
8
ER Modeling II - Q2

• MEMBER (MemberID, )
• WORKOUT (WorkoutID, MemberID, Date)
• EXERCISE (ExerciseID)
• WORKOUT_EXERCISE (WorkoutID, ExerciseID,
  NumberSets, NumberReps,)

9
ER Modeling II - Q3

• EMPLOYEE (EmployeeID, NamePositionID)
• PART_TIME_EMPLOYEE (EmployeeID, HourlyRate)
• FULL_TIME_EMPLOYEE (EmployeeID, Salary,
  OfficeRoom, )
• POSITION (PositionID, Title, Job_Description)

All foreign keys not null.
10
ER Modeling II - Q4

• USER (UserID, Name, Department,)
• PROBLEM (ProblemID, TimeSpent, UserID,
  ResolverID,)
• HARDWARE (ProblemID, Description, Solution)
• SOFTWARE (ProblemID, SoftwareVersion, )
• RESOLVER (ResolverID, Name, Phone, Level, )

All foreign keys not null.
11
ER Modeling II - Q5

• EMPLOYEES (EmployeeID, SupervisorID, )
• SKILLS (SkillID, SkillName, )
• EMPLOYEE_SKILL (EmployeeID, SkillID,
  DateAcquired, Certification,)
• PROJECTS (ProjectID, ProjectName, ManagerID,
  StartDate)
• EMPLOYEE_PROJECT (EmployeeID, ProjectID, Role)
• PROJECT_SKILL (ProjectID, SkillID,
  SkillLevelRequired, NumberStaff,)
• DEPENDENTS (EmployeeID, DependentID,
  DateOfBirth)
• WORK_HISTORY(EmployeeID, HistoryID,)
• BENEFITS (BenefitID, BenefitType, Company,
  Contact,)
• EMPLOYEE_BENEFIT (EmployeeID, BenefitID,)

All foreign keys are not null.
Optionally, create a ProjectSkill_ID attribute
to use as primary key.
12
ER Modeling II - Q6

• ORCHARD (OrchardID, Location, )
• SPECIES (SpeciesID, Name, OrchardID)
• DISEASE (DiseaseID, Symptoms, Treatment,)
• SPECIES_DISEASE (SpeciesDiseaseID, SpeciesID,
  DiseaseID, Date,)
• CUSTOMER (CustomerID, )
• ORDER (OrderID, CustomerID, )
• ORDERDETAILS (OrderID, DetailID, SpeciesID,)

Optionally, use the combination of SpeciesID,
DiseaseID and Date as primary key and remove
SpeciesDiseaseID entirely.
All foreign keys not null.