1.In your words, describe the purpose of normalization in database design 2.For
ID: 3812306 • Letter: 1
Question
1.In your words, describe the purpose of normalization in database design
2.For the relations listed below, identify if the relations are normalized to BCNF and if not, normalize the relationships. Please ensure to identify the steps we worked on in class for normalizing a relation and to identify the primary and foreign keys and referential integrity rules for each of three questions.
A.TRANSACTION (AccountNumber, AccountType, AccountBalance, TranscationNumber, TranscationType, TransactionAmount, CustomerID)
BusinessRules: The transaction number is unique for every transaction that happens and involves an account number and customer; A customer can make many transactions.
B.CUSTOMER (CustomerID, CustomerFirstName, CustomerLastName, CustomerPhone, CouponID, CouponType, CouponDiscountPercent)
Business Rules: Every customer has a unique customer ID; Different customers can use the same couponID. Each customer can use a couponID only one time but a customer can use many different types of coupons. The coupon type and coupon discount percent is associated with the couponID.
C.ORDER (CustomerID, CustomerPhone, OrderID, ItemID, ItemDescription, ItemQuantity, ItemUnitPrice)
BusinessRules: A customer can make many orders; An order is associated with one customer. OrderID can repeat if the same customer buys different types of items. An order includes a row for only one type of item. Item quantity refers to how many items of that particular type was ordered for a specific orderID.
Explanation / Answer
Normalization: Normalization in a databse is a way of organizing the data to avoid data redundancy, insertion,update & deletion anomalies.
Update anomalies If data items that are stored in tables that are not linked to each other properly, then it could become worst to perform relational operations on that data.
Deletion anomalies Deletion anomalies causes situations like,if we tried to delete a record, but some parts of data was left undeleted.
Insert anomalies Insert anomalies causes situations like, we tried to insert data in a record but does not exist at all.
Given relationships are:
TRANSACTION (AccountNumber, AccountType, AccountBalance, TranscationNumber, TranscationType, TransactionAmount, CustomerID).
To make above relation BCNF it should follow 1st NF, 2nd NF and 3rd NF.
1st NF: No two Rows of data must contain repeating group of information.
2nd NF: Every non-prime attribute should be fully functionally dependent on prime key attribute.
3rd NF: There should not be situations that a non-prime attribute is determined by another non-prime attribute.
BCNF: For each and every non-trivial functional dependency, X A, X must be a super-key.
Given relationships are:
A)TRANSACTION (AccountNumber, AccountType, AccountBalance, TranscationNumber, TranscationType, TransactionAmount, CustomerID).
By appllying all normalizations on above table,
TRANSACTION(TranscationNumber, TranscationType, TransactionAmount, CustomerID,AccountNumber)
ACCOUNT(AccountNumber, AccountType, AccountBalance)
B)CUSTOMER (CustomerID, CustomerFirstName, CustomerLastName, CustomerPhone, CouponID, CouponType, CouponDiscountPercent)
By appllying all normalizations on above table,
CUSTOMER (CustomerID, CustomerFirstName, CustomerLastName, CustomerPhone, CouponID)
COUPON(CouponID, CouponType, CouponDiscountPercent);
C)ORDER (CustomerID, CustomerPhone, OrderID, ItemID, ItemDescription, ItemQuantity, ItemUnitPrice)
By appllying all normalizations on above table,
ORDER (CustomerID, CustomerPhone, OrderID,ItemID)
ITEM(ItemID, ItemDescription, ItemQuantity, ItemUnitPrice)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.