Normalization | you mentioned normalization to Cassia
Added on -2019-09-25
| 29 pages
| 2239 words
| 865 views
Trusted by 2+ million users,
1000+ happy students everyday
Showing pages 1 to 6 of 29 pages
PART-AQ.1)
Q.2) Use case Diagram for the SystemANS:
PART-BQ1) In your discussion of the systems design phase, you mentioned normalization to Cassia. She would like you to explain the basics of normalization in plain English to help her understand the data design tasks.ANS: Initially we have one big table that includes all the attributesManagerLocationCustomerPersonal(ManagerID, ManagerName, ManagerAddress, ManagerContact, LocationID, LocationName, CustomerID, CustomerName, CustomerAddress, CustomerContact) Primary Key: ManagerID, CustomerIDbut ManagerID determines ManagerName, ManagerAddress, ManagerContact which violates the2NF rule i.e. the Proper subset of candidate key should not determine the Nonprime attribute andbecause of this reason it is not even 3NF CustomerMembership(TransactionID, CustomerID,Membership, MonthlyPay, MonthlyPayDate)CustomerExtraFaclity(TransactionID, CustomerID,CustomerExtra, ExtraService, Discount_in_per, ExtraPay, ExtraPayDate)This Normalization is 1NF because of that reason it contains some anomalies • Insert• Delete• Update
Let us consider first table ManagerLocationCustomerPersonal Here if we want to insert some location, and if there is no manager no customer then we can't do thatNow lets consider the case of Deletion suppose that if there is one customer at some particular location and that customer is also going to leave then along with customer location and manager details will be deletedNow lets consider the case of Update suppose Address of some manager changes, but there may be data mismatch from one tuple to another tuple that creates data ambiguityNot only this but it is taking much more space again to again to feed the same data this problem called Data Redundancy like one manager details will add whenever ever any customer join that center.To avoid this all situation we need to convert it into 3NF Manager(ManagerID, LocationID, ManagerName, ManagerAddress, ManagerContact)ManagerID--> LocationID, ManagerName, ManagerAddress, ManagerContactLocation(LocationID, LocationName)LocationID-->LocationName
CustomerPersonal(ManagerID, LocationID, CustomerID, CustomerName, CustomerAddress, CustomerContact)CustomerID--> ManagerID, LocationID, CustomerName, CustomerAddress, CustomerContactCustomerMembership(TransactionID, CustomerID,Membership, MonthlyPay, MonthlyPayDate)TransactionID--> CustomerID, Membership, MonthlyPay, MonthlyPayDateCustomerExtraFaclity(TransactionID, CustomerID,CustomerExtra, ExtraService, Discount_in_per, ExtraPay, ExtraPayDate)TransactionID-->CustomerID, CustomerExtra, CustomerService, Discount_in_per, ExtraPay, ExtraPayDateQ2) Review of Personal TrainerANS: A fitness center has around 300-500 member capacity. It compose of two membership full and limited for full membership people they can access all facilities available in the fitness centrewhile limited member has limited facility but they can available some other facility also by paying some more amount for that specific facility, currently they are using Bumblebee software to store all information about the customer but manager and fitness centre trainer want more analytical feature like it should provide some discount to the customer and also promote their fitness centre, and they want to see the particular activity where they are finding more profit, some manager want computerized activity to the centre based on these all review we have
created an Entity- Relationship Diagram (ERD).
Found this document preview useful?
You are reading a preview
Upload your documents to download
or
Become a Desklib member to get accesss
Paid Plans
Free Plan
Single Unlock
Monthly Plan
Yearly Plan