Often when normalizing a table design you don’t yet have data in your table.

Often when normalizing a table design you don’t yet have data in your table. For instance, in cases where you are designing a new data structure, not analyzing an existing table. So, the small amount of data provided in the above examples cannot always be counted on. However, sometimes you might have to determine whether a table is in 2NF or 3NF based solely on the knowledge of what attributes functionally determine other attributes. Presented here is a new type of notation that enables you to do that type of analysis without being able to see data. A -> B, C This means that column A is the key for the table and it functionally determines columns B and C. In other words, this means that for every unique value in column A, there can be only one row and thus only one distinct value for the data in columns B and C. Determining if a column’s data functionally determines another column’s data can usually be accomplished by asking the question “Can X have only one Y or can it have more than one Y?” An example might be “Can a doctor have only one patient or more than one patient?” If the answer is only one, then doctor functionally determines patient, otherwise if a doctor can have more than one patient, the doctor does not functionally determine the patient. In other words, if doctor functionally determines patient, then if you know the doctor, you can know the one and only patient he/she has. If doctor does not functionally determine patient (which of course it doesn’t), then you can’t just talk about the doctor and his/her patient. You have to specify the patient. This means that the key would have to include both doctor and patient.

5.) Is the following true? Why or why not? Doctor, Patient -> Prescription

6.) Is the following true? Why or why not? Doctor, Patient, Prescription -> Refills

Remaining Sometimes a key that is made up of more than one attribute (like Doctor, Patient) can technically functionally determine another attribute (like the Patient’s birth date). But it is also true that you don’t need Doctor in order to functionally determine a Patient’s birth date. This situation is what 2NF is all about. Making sure that all functionally determined attributes need all of the key columns to functionally determine them. In cases where you don’t, you need to decompose the related data into two or more tables. One for the full key (and any attributes you need the full key to determine), and one for each attribute that is only determined by a part of they key (along with that part of the key). For example, if you had: Doctor, Patient -> Last Appointment Date and Time, Patient Birth Date You would want to create two tables. One for: Doctor, Patient -> Last Appointment Date and Time And one for: Patient -> Patient Birth Date The problem this solves is that if a patient is seeing more than one doctor (perhaps a general practitioner and a neurologist), you don’t have to duplicate the patient’s birth date for each doctor he/she visits. This is redundant, and additionally makes it possible for the “two” birth dates to differ in the database when clearly a person doesn’t have two birth dates.

7.) Should the following design be decomposed into two or more tables? Why or why not? And if decomposing, what columns belong in each resultant table? Patient, Hospital Room Number -> Hospital Floor

8.) Should the following design be decomposed into two or more tables? Why or why not? And if decomposing, what columns belong in each resultant table? Doctor, Patient -> Hospital Admit Date and Time, Doctor’s Pager #

Sometimes a key can technically functionally determine two attributes but does so transitively. An example would be: Employee -> Employee’s Boss, Employee’s Boss’s Email Address The problem is that a boss likely has several people that report to them. So, if everywhere we record an employee’s boss, we also record their boss’s email address, it’s possible that the same boss might appear to have different email addresses when looked up by one employee vs. another. Solving this problem is what 3NF is all about. Again, this is not just about reducing redundancy but also reducing the chance for inconsistent data. To solve this problem, we’d want to decompose the design into two tables: Employee -> Employee’s Boss Boss -> Boss’s Email Address Aside: This decomposition is sufficient to meet the requirements of 3NF. However, an even better design would involve realizing that a boss is also an employee and store all employees in one table and have the relationship between employee and boss recursively refer to other rows in the same table: Employee -> Employee Email Address, Boss The Boss attribute would refer to the Employee column of another row in that same table.

9.) Should the following design be decomposed into two or more tables? Why or why not? And if decomposing, what columns belong in each resultant table? Doctor -> Department, Department Office Location (Note: a department can have only one office location)

10.) Should the following design be decomposed into two or more tables? Why or why not? And if decomposing, what columns belong in each resultant table?

Doctor, Patient, Date and Time -> Diagnosis, Typical Prognosis (By Typical Prognosis, assume of the diagnosis in general, not the particular doctor diagnosing the particular patient.)

For this assignment we want to take what we’ve learned and start applying them to the healthcare industry.

Then, I want you to provide a total of at least 32 attributes distributed among the 8 identities. Each identity doesn’t have to have exactly four attributes, some might have more or less.

Then I want you to identify the primary key for each entity. Ideally try to identify what the natural primary key for the entity would be. If one of your identities is a hospital room, the natural primary key for that would be its room number. However, some things might not have an obvious natural primary key, so you may add a surrogate key. An example of that might be if you have an employee entity. You might want to create an employee ID surrogate key for that entity.

Then identify the relationships between your entities. Every entity should participate in at least one relationship. Every relationship will result in a foreign key between the two entities. Remember a foreign key is a shared attribute between two entities. So, if you have a relationship between an employee and some other entity, either that other entity will also have an employee ID or your employee entity will have the key of the other entity as one or more of its attributes. When identifying the relationships, avoid many-to-many relationships as these are more difficult. You want either 1-to-many or many-to-1 relationships. Like, for example, a hospital can have many employees, but an employee is only an employee of that one hospital (which may not always be true, but we can assume it is for our purposes). For each relationship, identify its cardinality (i.e. what the 1 side is and what the many side is).

You do not need to represent these entities, attributes, keys and relationships diagrammatically for this lesson. You can simply type them up like the following example. However, you may find it helpful to first sketch these out on paper with arrows between the entities to help you visualize the relationships.

Entity: Car

Attributes: VIN, Make Name, Model Name, Color

Primary Key: VIN

Foreign Key: Make Name in the Make entity

Relationships: Car participates in a many-to-one relationship with the Make entity (the same Make Name can be in the Car entity multiple times but is unique in the Make entity).

Entity: Make

Attributes: Make Name, Stock Ticker, CEO Name

Primary Key: Make Name

Foreign Key: None

Relationships: Make participates in a one-to-many relationship with the Car entity (the Make Name is unique in the Make entity but can appear in the Car entity multiple times).