I need help with a database past paper, our professors did not provide us with s

Question

I need help with a database past paper, our professors did not provide us with solutions, so I don't know whether my answers are correct or not.

I would appreciate if an expert could provide me with some example answers.

1. (a) What is normalisation in the context of relational database design? Explain whether it is always desirable to normalise relations into Third Normal Form. 13] (b) The following is an example of an order placed by a customer with a self-explanatory order form: 102369 17/03/2010 103423 No Order Date Customer ID Customer Name John Smith Delivery Address 123 Main Road, Cardiff, CF12 3AB Item No 1023-01 2137-12 7387-15 Total Amount£51.20 Description Item Price(£) Quantity Cost(£) A4 Paper DVD-R Disk Batteries 1.20 0.70 2.30 6 30 10 7.20 21.00 23.00 Using this form i. derive a single First Normal Form relation (only the schema is required and the primary key should be underlined); 2 ii. identify the functional dependencies that you woulkd expect to hold among the attributes in your First Normal Form relation and express them in a functional dependency diagram (state say assumptions you make);: your First Normal Form relation into a set of Third Normal Form tables (primary keys should be ii. nomalise underlined) (c) Assume that we have a relation with three attributes: R(A, B,C), and AB and B C (functional dependen- cies) hold in R. Use Armstrong rules to show that A BC also holds. [3

Explanation / Answer

1)a)Answer:

Normalization:

Normalization is usually thought of as a process of applying a set of rules to your database design, mostly to achieve minimum redundancy in the data. Most textbooks present this as a three-step process, with correspondingly labeled “normal forms,” which could be done in an almost algorthmic sequence.

• In theory, you could start with a single relation scheme (sometimes called the universal scheme, or U) that contains all of the attributes in the database—then apply these rules recursively to develop a set of increasingly-normalized sub-relation schemes. When all of the schemes are in third normal form, then the whole database is properly normalized. In practice, you will more likely apply the rules gradually, refining each relation scheme as you develop it from the UML class diagram or ER model diagram. The final table structures should be the same no matter which method (or combination of methods) you’ve used.

• Since most developers will use traditional terms, you should know how the design patterns that you have learned will lead to the same (normalized) results, as shown in the following table:

2)a)Answer:

A deadlock occurs when two different users or transactions require access to data that is being locked by the other user. It can be avoided in 2 ways, 1 is to set measures which prevent deadlocks from happening, and 2 is to set ways in which to break the deadlock after it happens. One way to prevent or to avoid deadlocks is to require the user to request all necessary locks atone time, ensuring they gain access to everything they need or nothing. Secondly, sometimes they can be avoided by setting resource access order, meaning resources must be locked in a certain order to prevent such instances. Essentially once a deadlock does occur the DBMS must have a method for detecting the deadlock, and then to resolve it, the DBMS must select a transaction to cancel and revert the entire transaction until the resources required become available, allowing one transaction to complete while the other has to be reprocessed at a later time

4)a)Answer:

A database trigger is stored code that is executed immediately after a predefined event. It is used to ensure the coordinated performance of related actions. Although implementation varies, all major relational databases support trigger

For example, a human resources (HR) application requires that every employee manager receive an informational e-mail immediately after an employee’s leave request is submitted. When a record is written into a table that stores employee leave requests, a created trigger fires and invokes the e-mail-sending procedure to the manager.

Another common trigger use is to save important original data, in its unchanged state, to maintain an audit trail or ensure that the original data remains accessible in the event of accidental changes. For example, the same HR application may contain a trigger that is executed when an employee's bank details are changed. The trigger first saves the original information to another table; this allows data modification.

Normal forms Normal form Traditional definition As presented here First normal form (1NF) • All attributes must be atomic, and
• No repeating groups • Eliminate multi-valued attributes, and
• Eliminate repeated attributes Second normal form (2NF) • First normal form, and
• No partial functional dependencies • Eliminate subkeys (where the subkey is part of a composite primary key) Third normal form (3NF) • Second normal form, and
• No transitive functional dependencies • Eliminate subkeys (where the subkey is not part of the primary key)

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.