(1) For example:
Users(UserID, SSN, FirstName, LastName,
OfficePhone, UnitID)
Other Candidate Keys: {SSN}.
Foreign Keys: UnitID on Units(UnitId),
not nullable.
Administrator(AdministratorID,UserID)
Foreign Keys: UserID on Users(UserID),
not nullable.
Technicians(TechnicianID,UserID)
Foreign Keys: UserID on Users(UserID),
not nullable.
RegularUsers(RegularUserID,UserID)
Foreign Keys: UserID on Users(UserID),
not nullable.
Units(UnitID, UnitName, Phone, ParentUnitID,
HeadOfUnitsRegularUsersID,
SecretaryOfUnitsRegularUsersID)
Foreign Keys: HeadOfUnitsRegularUsersID
on RegularUsers(RegularUserID), nullable.
SecretaryOfUnitsRegularUsersID ion RegularUsers(RegularUserID), nullable.
ParentUnitID on Units(ParentUnitID), nullable.
Account(AccountName, Password, UserID)
Foreign Keys: AccountUserID
on Users(UserID).
Requests(RequestID, RequestDate, WorkerTechnicianID,
AssigningAdministratorID,
RequesterRegularUserID, HomogeneousComputerGroupID)
Foreign Keys: WorkerTechnicianID
on Technicians(TechniciansID), not nullable
RequestRegularUserID on RegularUsers(RegularUserID), not nullable
AssigningAdministratorID on Administrator(AdministratorID), nullable
HomogeneousComputerGroupID on HomogeneousComputerGroups
(HomogeneousComputerGroupID), not nullable.
ProgressReports(ReportID, ReportDate,
ReportComments, RequestID)
Foreign Keys: RequestID on Request(RequestID),
not nullable.
RequestItems(RequestItemID, Description,
Comments, RequestID, InstalledSoftwareID)
Foreign Keys: RequestID on Request(RequestID),
not nullable.
InstalledSoftwareID on InstalledSoftware(installedSoftwareID), nullable.
ComputerGroups(ComputerGroupID, ComputerGroupName,
ComputerGroupDescription, HeterogeneousComputerGroupID)
Foreign Keys: HeterogeneousComputerGroupID
on
HeterogeneousComputerGroup(HeterogeneousComputerGroupID), nullable.
HeterogeneousComputerGroups(HeterogeneousComputerGroupID,
ComputerGroupID)
Foreign Keys: ComputerGroupID
on ComputerGroups(ComputerGroupID), not nullable.
HomogeneousComputerGroups(HomogeneousComputerGroupID,
HDSize, RAMSize, ComputerGroupID)
Foreign Keys: ComputerGroupID
on ComputerGroups(ComputerGroupID), not nullable.
Computers(ComputerID, IPAddress, HomogeneousComputerGroupID)
Other Candidate keys: (IPAddress).
Foreign Keys: HomogeneousComputerGroupID
on HomogeneousComputerGroups
(HomogeneousComputerGroupID), not nullable.
Operating Systems(OperatingSystemsID,
OperatingSystemName)
Foreign Keys: None
HomogeneousComputerGroupOperatingSystems(HomogeneousComputerGroupsID,
OperatingSystemID)
Foreign Keys: HomogeneousComputerGroupID
on HomogeneousComputerGroups
(HomogeneousComputerGroupID), not nullable.
OperatingSystemsID on OperatingSystems(OperatingSystemID), not nullable.
Software(SoftwareID, SoftwareName)
Foreign Keys: None
TechnicianSoftwareExpertise(TechniciansID,
SoftwareID)
Foreign Keys: TechniciansID
on Technicians(TechnicianID), not nullable.
SoftwareID on Software(SoftwareID), not nullable.
SupportedSoftware(SupportedSoftwareID,
SoftwareID, OperatingSystemID)
Other Candidate Keys: (SoftwareId,
OperatingSystemID)
Foreign Keys: SoftwareID on
Software(SoftwareID), not nullable.
OperatingSystemID on OperatingSystems(OperatingSystemID), not nullable.
InstalledSoftware(InstalledSoftwareID,
SupportedSoftwareID, HomogeneousComputerGroupID)
Foreign Keys: SupporedSoftwareID
on SupportedSoftware(SupportedSoftwareID), not nullable.
HomogeneousComputerGroupID on HomogeneousComputerGroups
(HomogeneousComputerGroupID), not nullable.
(2) No major changes from the class diagram. Note:
Subclass to superclass
cardinality is 0..1 to 1..1.
For the two composition
assocation:
ComputerGroups (1..*) to HeterogeneousComputerGroups (0..1)
Computers (1..*) to HomogeneousComputerGroups (1..1)..