Databases Normalize the following relation into relations in 4NF. Indicate the f
ID: 3599884 • Letter: D
Question
Databases
Normalize the following relation into relations in 4NF. Indicate the functional dependencies, multivalued dependencies, primary keys (underlined), foreign keys (dashed underline), candidate keys, and specify any referential integrity constraints. Show the resulting ERD. Do not leave any N:M relationships.
A hospital serves many patients. Each patient can have many charges, including their daily room charge, therapy, IV, radiology, etc. Each of these charges may be incurred by several patients, and the price is always the same. For example, the IV charge is $150 per day. A hospital also has many physicians on staff. A physician is on staff of only one hospital. A physician may admit patients, but a patient is admitted by only one physician
Explanation / Answer
Illogically or inconsistently stored data can cause a number of problems. In a relational database, a logical and efficient design is just as critical. A poorly designed database may provide erroneous information, may be difficult to use, or may even fail to work properly.
Most of these problems are the result of two bad design features called: redundant data and anomalies. Redundant data is unnecessary reoccurring data (repeating groups of data). Anomalies are any occurrence that weakens the integrity of your data due to irregular or inconsistent storage (delete, insert and update irregularity, that generates the inconsistent data).
Basically, normalisation is the process of efficiently organising data in a database. There are two main objectives of the normalization process: eliminate redundant data (storing the same data in more than one table) and ensure data dependencies make sense (only storing related data in a table). Both of these are valuable goals as they reduce the amount of space a database consumes and ensure that data is logically stored.
The process of designing a relational database includes making sure that a table contains only data directly related to the primary key, that each data field contains only one item of data, and that redundant (duplicated and unnecessary) data is eliminated. The task of a database designer is to structure the data in a way that eliminates unnecessary duplication(s) and provides a rapid search path to all necessary information. This process of specifying and defining tables, keys, columns, and relationships in order to create an efficient database is called normalization.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.