-
Am I Properly Normalizing These Tables
I am currently a student studying Database Design & Programming. Currently I am working on some normalization exercises found from the web, and I want to make sure that I am properly normalizing the data. If I am making mistakes or there are better practices that I am neglecting, please point them out. Any type of feedback is appreciated.
1. The following displays data stored about patients and drugs prescribed to them:
{Patient-Id + Name + Addr + {Rx# + Trademark-Drug + Generic-Drug + Date}}
Assume the following:
• Rx# are unique - never a duplicate for any reason.
• Trademark-Drug is unique.
• A refill of a Rx has a new Rx#.
• If you know Trademark-Drug you can determine Generic-Drug.
My solution: https://imgur.com/a/kjTaa
2. Here we are presented with the following form. Note that one section can have only one professor, one professor can teach more than one section, a student can only have one major, several courses can have the same course title, and professors can share an office. Form: https://imgur.com/a/qJgFI
My Solution: https://imgur.com/a/FzyHW
(NOTE: I didn’t make a separate entity for majors, because no attributes depend on Major_Code and a single student may not have more than one major at a time)
3. The following data stores information on construction projects and the employees and equipment associated with them:
{Proj-No + Proj-Desc + Mgr-No + Mag-Name + {Emp-No + Emp-Name + Union-Code + Union-Desc} + {Equip-No + Equip-Desc + Time-Period}}
Assume the following:
• An employee can be assigned to more than one project at any one time. A piece of equipment may be assigned to the same project more than once but not on the same time period (defined by the "Time-Period" attribute).
• A piece of equipment can be assigned to different projects but not during the same time period.
• Mgr-No, Emp-No, Union-Code, and Equip-No are all unique.
• Employees belong to a single union no matter which projects they are assignment to.
My Solution: https://imgur.com/a/l50WS
NOTE: Many assumptions had to be made about each of these tables such as what the functional dependencies are in these tables. If there are any better resources for database normalization practice exercises, please provide this if possible. I apologize if any of my wording is confusing. I am a beginner looking to improve their normalization understanding.
-
Drugs
No, you are not normalized. You need at least the following 5 tables (see note).
Patient: Id (PK, indexed), Name, Address (data is non atomic. define elements)
PatientPrescription: PatientId (FK), PrescriptionId (FK), Date
Prescription: Id (PK, indexed), Rx# (indexed), Date
PrescriptionDrug: PrescriptionId, DrugId, Date
Drug: Id (PK, indexed), TrademarkName, GenericName
Note: Drug violates 3rd normal -- GenericDrug has many TrademarkNames
Think of the world as many-to-many.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|