Normalization Assignment Help | Project | Homework Problem Solution

Adjusting values in data base is easy when you acquire our normalization assignment help. DatabaseHomeworkHelp is ending your wait for perfect normalization homework help. We emphasize to show our talent in help with normalization assignment as our dedication is to solve difficulties of data base students. Help with normalization project can ease the process of learning DBMS.

Without the DatabaseHomeworkHelp normalization problem solution is not easy. We not just try to make it easy to get normalization project help but we also put energies to get actual good marks for students. Every satisfied student is result of our dedication in normalization assignment help. Our teaching segment is also exceptional for exerting knowledge by facility of normalization online expert tutor.

Now from bad marks to good marks the distance is getting our help with normalization assignment. We give you the chance to get help with normalization project and make your project faultless in terms of DBMS. If you sense the bad results you are more than welcome for our normalization problem solution. We return the efforts in our normalization project help for monetary cost. Nothing can be greater for learning date base other than our normalization online expert tutor skill.

Normalization

Normalization of tables is done in order to minimize the data redundancy and dependency on relational database. It helps to maintain consistency of data and help to save storage space. It helps to remove anomalies by splitting single tables into multiple tables. It can be further divided into,

  1. First normalization
  2. Second Normalization
  3. Third Normalization
  4. BCNF

Note:- The below normalization sample assignment is for your reference and is not been taken from our previous solved homework that we provided to our students. The sample has been prepared by our tutors to get you the flavor of our solutions. We maintain quality, privacy check and confidentiality. This has helped us to establish us better in the world of Assignment help providers.

Task: What is normalization? Explain with examples.
Normalization

A database is to be designed for a dental practice. Consider the information system with the following characteristics

“When a patient registers with the practice a patient number is allocated and details are recorded including the patient’s name, address, and next of kin. The patient is also allocated to a dentist. Details are kept of patients’ appointments. A patient is given an appointment at a specific time and date with his/her dentist. (Assume all appointments and procedures are undertaken by the patient’s own dentist.) A nurse must accompany the dentist during every appointment. One nurse only is required for each appointment.

Various procedures are offered by the dental practice such as fillings, extractions, dental crowns and root canals. Sometimes a procedure will span more than one appointment and the dates of the start and end of treatment must be recorded. The Heath Service provides a list of Standard Procedures together with standard estimates of the cost of treating a patient who needs such treatment.

Staff, who work at the practice, include three dentists and four dental nurses. For all employees, details kept include employee number, name, address, and grade. The number of years of service is also held for each dentist ”

Task: Below are the entities along with their attributes (Appendix A and B). Establish the relationship between them using all normalization form and show the different relationship. Also show the logical relationship between all the details with procedure.

normal

Appendix B

Dentistry table

DenNo DenNa PatNo PatNa AppNo AppDate AppTime NurNo NurNa StProNo Desc Cost
D01 Smith P01 Jones A01 01/02/09 12:30 N01 Green ST01 Filling £30
A02 01/03/09 11:30 N01 Green ST01 Filling £30
A03 01/04/09 10:30 N01 Green ST02 Canal £20
P02 Patel A04 01/02/09 10:30 N02 Brown ST01 Filling £30
A05 01/03/09 12:30 N02 Brown ST01 Filling £30
A06 01/04/09 13:30 N02 Brown ST02 Canal £20
D02 Bakry P03 Quigly A07 03/06/09 14:30 N03 Cann ST01 Filling £30
A08 12/07/09 14:00 N03 Cann ST03 Crown £100
P04 Thom A09 14/12/09 13:00 N04 Bavan ST03 Crown £100

Solution:-

normal1

ERD after breaking the many to many table,

normal2

Logical details, Tables after ERD:

 

Table Name: Dentist

Column Name Data Type Required Keys
Dent_eno Integer yes PK
grade Varchar(20) yes
dname Varchar(20) yes
Address Varchar(50)
yrsOfService date

 

 

Table Name: Patient

Column Name Data Type Required Keys
Pno Integer yes PK
pname Varchar(20) yes
address Varchar(20) yes
Next-kin Varchar(50)

 

 

Table Name: Nurse

Column Name Data Type Required Keys
nurse_eno Integer yes PK
grade Varchar(20) yes
Nname Varchar(20) yes
Address Varchar(50)

 


Table Name: Procedure

Column Name Data Type Required Keys
Stand_procno Integer yes PK
Description Varchar(20) yes
cost Interger yes

 

 

Table name:- Appointment

Column Name Data Type Required Keys
ano Integer yes PK
date date yes
atime DateTime yes
DateOfStartTreatment date yes
DateOfEndTreatment date yes
NurseID Integer yes FK(Nurse)
patientID Integer yes FK(Patient)
DentistID Integer yes FK(Dentist)

 

 

DenNo DenNa PatNo PatNa AppNo AppDate AppTime NurNo NurNa StProNo Desc Cost
D01 Smith P01 Jones A01 01/02/09 12:30 N01 Green ST01 Filling £30
A02 01/03/09 11:30 N01 Green ST01 Filling £30
A03 01/04/09 10:30 N01 Green ST02 Canal £20
P02 Patel A04 01/02/09 10:30 N02 Brown ST01 Filling £30
A05 01/03/09 12:30 N02 Brown ST01 Filling £30
A06 01/04/09 13:30 N02 Brown ST02 Canal £20
D02 Bakry P03 Quigly A07 03/06/09 14:30 N03 Cann ST01 Filling £30
A08 12/07/09 14:00 N03 Cann ST03 Crown £100
P04 Thom A09 14/12/09 13:00 N04 Bavan ST03 Crown £100

 

Steps:-

a)    The above table is not in Normalized form. Because there is no primary key in this table which uniquely identified each row of the table.

And if you know the NurNo you can find out the name of the nurse Cost id only depended upon the stproNo. There are many other attributes which are not fully dependent upon the primary key.

b)    Converting the table into First Normal form

DenNo DenNa PatNo PatNa AppNo AppDate AppTime NurNo NurNa StProNo Desc Cost
D01 Smith P01 Jones A01 01/02/09 12:30 N01 Green ST01 Filling £30
D01 Smith P01 Jones A02 01/03/09 11:30 N01 Green ST01 Filling £30
D01 Smith P01 Jones A03 01/04/09 10:30 N01 Green ST02 Canal £20
D01 Smith P02 Patel A04 01/02/09 10:30 N02 Brown ST01 Filling £30
D01 Smith P02 Patel A05 01/03/09 12:30 N02 Brown ST01 Filling £30
D01 Smith P02 Patel A06 01/04/09 13:30 N02 Brown ST02 Canal £20
D02 Bakry P03 Quigly A07 03/06/09 14:30 N03 Cann ST01 Filling £30
D02 Bakry P03 Quigly A08 12/07/09 14:00 N03 Cann ST03 Crown £100
D02 Bakry P04 Thom A09 14/12/09 13:00 N04 Bavan ST03 Crown £100

 

c)    The primary key for the first normal form is DenNo,patNo,AppNo,NurNo

d)

normal3

e) Decompose the table into dentist

normal4

f)    Decompose the table patient

normal5

g) To decompose Nurse table:

normal6

h)    To get the Procedure detail,

normal7

i)    To get the 3rd Normalized form for appointment entity.

normal8

Procedure

normal9

Nurse table:

normal10

G) Explain 3rd Normal form.

Boyce-Codd normal form (or BCNF) is a normal form used in database normalization. It is a slightly stronger version of the third normal form (3NF). A table is in Boyce-Codd normal form if and only if for every one of its non-trivial functional dependencies X → Y, X is a super key—that is, X is either a candidate key or a superset thereof.