1

I am currently working on a project to create an IT Asset/Inventory Management system. My approach was to take their mess of spreadsheets and convert it to a relational database. It will be used to track specific system information and eventually relate financial information to the specific systems and hardware.

I have gone through a few iterations now and believe that this is in 2NF. I would love to get this into 3NF, but am not sure where to make the changes at this point. Any guidance would be greatly appreciated!

Side question: I believe that my schema resembles a Star Schema, is this a correct assumption, and can/should a star schema be normalized?

Description of entities/relationships:

  • The System entity is used to define distinct systems. A System can be either physical (Server) or virtual (VM), and is uniquely identified by a systemKey.
  • Systems are composed of the related hardware components/Network components
  • Systems exist in both a physical (Site) location and a virtual (Lab) location.
  • Location is unqiely identified by the locationKey and each entry requires both physical and virtual attributes to exist.
  • Systems may have related Software.
  • Systems may have related Tickets.
  • Systems may have one or more Warranties.
  • Hardware is related to vendors and is a specific type of hardware, identified with a number/name.
  • CPU/DIMM/Drives are all related to their respective types for definition. They are mandatory on all systems (both virtual and physical).

ERD

4
  • Everything has a sysid - even a ticket
    – paparazzo
    Commented Aug 12, 2016 at 16:00
  • @Paparazzi Yeah it does, I know that this is not correct for normalization. I would like to reduce the overlap as much as possible. Commented Aug 12, 2016 at 16:01
  • 2
    Then start with removing that needless reference to sysid. And you will need to define what the tables do. We cannot if the relationship with system and application is backwards.
    – paparazzo
    Commented Aug 12, 2016 at 17:09
  • @Paparazzi please see my updated schema. I have attempted to correct the relationships by viewing them inversely; systems are composed of the various entities in the schema (not the other way around). I have introduced foreign keys to the system entity, but I believe I have reduced the amount of redundant data overall. Commented Aug 12, 2016 at 19:39

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.