0

I have a list of attribute and I am trying to find out 1NF,2NF,3NF,BCNF, 4NF The list of attributes are:

 - Pat_Name    
 - Pat_Address
 - Pat_Num
 - Pat_Dob
 - Pat_Gender
 - Pat_MotherName  
 - Pat_FatherName
 - Doc_Name  
 - Doc_Address
 - Doc_HomeTel
 - Doc_MobTel
 - Doc_Qualification
 - Doc_Dob
 - App_Date
 - App_Time
 - App_Doc
 - Visit_Date
 - Visit_Time
 - Visit_Reason   
 - Diagnosis
 - Prescription
 - DateOfBill
 - TotalBillCharge

In First Normal Form I got:

 Patient (Table)     -> Pat_ID,Pat_Name,Pat_Address,Pat_Num,Pat_Dob,Pat_Gender,Pat_MotherName,Pat_FatherName
 Doctor  (Table)     -> Doc_ID,Doc_Name,Doc_Address,Doc_HomeTel, Doc_MobTel,Doc_Qualification, Doc_Dob
 Appointment(Table)  -> App_ID,Pat_ID,Doc_ID,App_Date,APP_Time,Visit_Reason,Diagnosis,Prescription,DateOfBill,TotalBillCharge

In 1NF :

I have been confused with Visit_Time and Visit_Date (Is it similar with Appointment_Time & Appointment_Date).Looks to me similar so I removed Both of that.Is it I am doing correct or should i make new table for visit (Really need help on this)? I have been created new attribute for identify primary key.

2NF:

Table Patient Do not have any partial Dependency (Am I correct ?) Table Doctor Do not Have any partial dependency (Am I correct ?) Table I got Partial Dependency, So, I separate another table called visit

   Appointment -> App_ID,Pat_ID,Doc_ID,App_Date,APP_Time
   Visit       -> App_ID,Visit_Reason,Diagnosis,Prescription,DateOfBill,TotalBillCharge

In 3NF: Table Patient and Doctor I did not found any transitive dependency In Table Visit Have transitive dependency so I separate it to new table bills 

Visit -> **App_ID**,Visit_Reason,Diagnosis,Prescription_ID     //(I created new Attributes here, is this correct ?)
Bills -> Prescription_ID, DateOfBill, TotalBillCharge

Any help is appreciated. :)

Chandrahas Aroori
  • 955
  • 2
  • 14
  • 27
Jahidul Islam
  • 29
  • 2
  • 7
  • 1
    1) Normalization starts with functional dependencies. What are the functional dependencies? 2) Normalization never introduces *new* attributes like pat_id, doc_id, and app_id. – Mike Sherrill 'Cat Recall' Dec 12 '14 at 05:54
  • I introduce new attributes because, i want to introduce primary key for the table, because the data given above do not have any primary key – Jahidul Islam Dec 12 '14 at 06:10
  • 1
    *Normalization never introduces new attributes like pat_id, doc_id, and app_id.* Is your question about normalization, or is it about something else? – Mike Sherrill 'Cat Recall' Dec 12 '14 at 06:13
  • Actually I got two following question ->Start converting the table into first, second, third and higher normal form if there are any. Use a dependency diagram to show each conversion. 3. State all the assumptions and constraints if any. You may create new attributes to improve the design – Jahidul Islam Dec 12 '14 at 06:39
  • 1
    *"Start converting the table into first, second, third and higher normal form if there are any."* Normalization starts with functional dependencies. What are the functional dependencies? – Mike Sherrill 'Cat Recall' Dec 13 '14 at 12:07
  • Functional dependency start with only from 2nf to bcnf – Jahidul Islam Dec 13 '14 at 14:48
  • 1
    All relations are in 1nf as far as normalization is concerned. You can define *other* notions of "1nf" than "is a relation" but they are irrelevant to normalization. Normalization starts with FDs. This determines candidate keys. Primary keys per se are irrelevant to normalization. (You can pick a CK as PK.) The MVDs/JDs that violate 4nf are the ones that do not correspond to FDs. For somebody who admits they "really need help" you are pretty resistant. – philipxy Dec 13 '14 at 15:02
  • Give or quote your textbook telling you what to do. Then *show your work following your textbook*. Google 'stackexchange homework'. ["1NF" has no standard meaning](https://stackoverflow.com/a/40640962/3404097) so you/we need to know what your textbook says it is & [how to get to it](https://stackoverflow.com/a/37483508/3404097). And any notation for non-relations will be non-standard so you/we need to know what your textbook says it is & what converting to a relation is. Explain your relation or non-relation start structure & its field/attribute types & what FDs do & don't hold. – philipxy Mar 24 '18 at 22:01
  • PS There likely *are* partial & transitive FDs. But on the the other only *certain* partial & transitive FDs are *problems*. You need to read definitions of FDs, superkeys, CKs, NFs, etc & use them properly. There are tons of steps & that is why you must *reference & follow your textbook* or you are just asking us to write another one for you. Eg learn how to find *all* the FDs that hold when you know that certain ones do. Eg *yet again: what are the FDs & why?* Also if you are given attributes what right do you have to drop them? Find out from your instructors what they mean. – philipxy Mar 24 '18 at 22:16

0 Answers0