Create Normalized Database Schema in 3rd Normal Form

Question

I am working on a small assignment for my second year university model, system analysis and design. Im battling with a question that should be fairly simple to answer but never the less I have some doubts about my answer.

I will post the question and my answer. I will greatly appreciate it if someone could spare a minute to look it over and push me in the right direction if my answer is not correct.

Question follows:

Refer to the ERD for the Reliable Pharmaceutical Service provided on the next page and develop a normalised database schema in 3NF. Clearly indicate all keys. All tables should be in 3NF. Underline all primary keys, and use the letters FK to indicate foreign key, for example …, Product-code (FK),….

Note in the given ERD

• Not all fields are given
• Some fields may not be neccessary for some tables
• Primary keys and foreign keys are not shown

MY ANSWER:

Client Health-Care Organization (Organization-code, Name, Address)
Client Unit (Client-unit-code, Name, Address, Floor, Wing)
Patient (Number, Name, DateOfBirth, Sex, Roomnr)
Prescription (prescription-number, StartDate, StartTime, EndDate, EndTime, Quantity, Frequency, Instructions)
DrugItem (UnitType, UnitDosage, QuantityOnHand, RecorderPoint, RecorderQuantity, Price)
Drug (Chemical-Number, ChemicalName)
DrugItemPackage (UPC, PackageType, PackageQuantity, BrandName, Price)
Manufacturer (Manufacturer-ID, Name, Address)
OrderItem(Order-Nr, Quantity, Date, StartTime, Endtime)
Pharmacist(LicenseNumber, Name)

Note: In my answer primary keys are at the start of the table

In the OrderItem table I left out PatientName, RoomNumber (Since it is allready contained it the patient table am I correct in doing this?)

Also in the OrderItem table I left out ChemicalName, Manufactorer, UnitType, Dosage and Special instructions as I feel this is unnecessary fields for the OrderItem table and is already contained within other tables am I correct in doing this?

Thank you so much for taking the time to read my question, also thank you to the creators and contributors of this wonderful website

Cheerz

Answer 1

Your proposed schema doesn't have 'Order' in it.

Also, OrderItem doesn't appear to link to Patient. Most commonly Order would be per- single Patient, but from the diagram it looks like this system is for a "bulk order" covering many patients.. so the link should be from OrderItem.

Neither does OrderItem link to whichever Drug entity it should.

Keep working on this.

See:

• http://en.wikipedia.org/wiki/Third_normal_form