CSCI 5931A
Advanced Database Development
Summer 1999
Suugested Solution to Homework #2

(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)..