List All Pages
The concepts in the previous two sections allow you to model many business situations. The following concepts are needed to round out your repetoire so that you will be ready for almost any...
This is the part-of relationship. Consider again the relationship among car, body, and engine.
This looks like the subtype relationship but it is interpreted differently. Attributes are not...
Answer P.1
1NF
3NF
Answer P.2
1NF
3NF
Answer P.3
A, C, [A, B], F
[[A, B]]
Answers:
There are three separate transitive dependencies: 1) A —> C —> D, 2) A...
customer, sales person, model, car, used, new
yes
yes
0
no
yes
0
yes
0
10: One for each entity type, one for each ternary (3-way) relationship, one for talks, and one for test drive.
10: One for...
1) Here's the diagram:
The primary key of this relation is [part #, vendor name].
2) There is one primary key in this relation.
3) There are two fields (attributes) in the primary key.
4) Here's...
1) Here's the diagram:
2) 1. There's always just one primary key for a table in 1NF. It is composed of three fields (or attributes), but it is still just one primary key.
3) 3
4) There are three...
The cardinality and existence are shown in the ER diagrams below. You did not have to draw these diagrams. You could have just used words to describe the cardinality and existence.
1
Only current...
I don't have any answers here. Sorry.
Return to ER exercises.
In each of the below I drew the ER diagram and assume that you can read the cardinality and existence off of the diagram.
1
The optional on the left could be left out. It is not stated in the...
1
2
Return to ER exercises.
1
2
3
4
5
Return to ER exercises.
Return to ER exercises.
Return to ER exercises.
The minimum number of Cs that can be associated with one A is 1. I know this because the existence of the C to A relationship is mandatory. The maximum number of Cs that can be associated with one...
Diagrams 1, 2, 3, and 4 are consistent with the facts. Diagram 2 is consistent because, although Jennifer leads more than one project, the diagram is ambiguous as regards to cardinality — it...
1
2
3
4
Return to ER exercises.
It seems here that the situation is concerned with divisions, departments, and employees or managers. It gives some details about which contains which, how they are related to each other, and...
The municipal bond is an entity; bond is a possible entity type. Ford is an entity; manufacturer is a possible entity type. Clothes could be either: 1) a type if the entities are pants, shirts,...
I would name the first relationship something like “home division” and the second one something like “manages”.
Return to simple modeling exercise.
The relationship between department and a managing employee is different than the one between division and department. It doesn't say so but we can assume that a department has only one manager.
An...
Here's my assumptions for this situation:
It would seem that at any particular time a patient can only have one primary care physician and that any physician can have many patients (M:1).
One...
I like to work in a different order. First determine what the candidate identifiers are. I say that the set of candidate identifiers is [[A]] (that is, one candidate identifier and that one...
Write the 1NF relation.
The attributes are as follows:
Receipt, Date, PID, PName, Phone, City, State, VID, VName, Type, Days, Date Due, Cost, Total
Derived data
Date Due can be calculated from...
The determinants of the Employee relation are [id] and [ssn]. The candidate identifiers are [[id], [ssn]]. Either [id] or [ssn] can be the primary identifier (but not both). You simply have to...
It is not entirely clear from the situation description which of the above are true. I make the relatively standard assumptions that a department must have at least one manager and that an employee...
There are no derived data attributes and no repeating groups.
Remove the multi-valued attribute (the parts attribute) from the table. Put this in the new Component table, described next. This...
It's also the case that Dividend is dependent on Stock, Name on Cust ID, and Addr on Cust ID. These changes, combined with the changes discussed just above, give us the following two new relational...
In this database you should define a company entity type with two subtypes: AR_co and AP_co. The company entity type stores all facts that are common attributes — in this case, the address and...
As for 1NF:
All the attributes are single valued.
There are no derived data attributes.
There are no repeating groups.
We have defined primary identifiers for all relations (tables).
The...
Boeing 747 is a type of plane and a specific Boeing 747 that flies through the air with passengers in it is an instance of this type. Boeing 747 is a specific instance of the entity type plane type...
Yes. Notice, you can't even think about an answer until you know the identifier of the table. Clearly, it is [Bookstore, Location]. It appears (to me) that the only functional dependency...
No. The problem comes from functional dependencies that look like
part-of-identifier —> some attribute
And, since a single field identifier can't be broken up into parts, you can't have...
SSN is not a good identifier unless the set of all people in the database consists of employed people in the United States. People not in this set might not have a SSN. This means that SSN could...
ISBN is generally good if you are dealing with books published relatively recently
VIN is generally good if you are dealing with recently manufactured cars
Student ID is good. Uniqname is probably...
Step 1
The Invoice table as shown [#tab:invoice-example above] is not in 1NF because of the attributes containing derived values. Ext Price is calculated by multiplying Price by # used. Tax is...
The table is in 1NF since it has no repeating groups, all single-valued attributes, and no derived data. The primary identifier is [Journal title, Volume]. It is not in 2NF since
Journal title...
Consider the two choices:
Adding a field to an existing table
The new relation will have the attributes [vin, model, type]. Before we added the type attribute to this relation, there seemed to be...
Find any pair of rows that have the same value of [car] (i.e., X). To start, look at rows 1 and 6.
Exchange the values of [engine] in these two rows. Now you have [mustang, red, F4.5L] and...
In situation #1 you can see from the first line that Don sold Sam the Cobra. In situation #2, looking at buys you can see that Sam did actually buy a Cobra (second line). Looking at buys from you...
To determine the cardinality for this relationship, I had to make several assumptions. Other assumptions are possible but I thought these seemed reasonable.
An employee can use the same skill on...
This table is in 1NF. The primary identifier is [course, teacher, text]. There are two partial key dependencies, teacher —> hire date and text —> copyright, which are violations of 2NF....
I would think that these would be optional, overlapping entity subtypes. But if I were not going to represent it this way, I may consider having a M:M relationship between student and major.
Return...
To put this in 4NF, the first step is to put it in 3NF. Since manufacturer depends solely on car, we should put this information in a separate relation. So, now we have
car —>...
Optional if the information is entered before an order is delivered
Mandatory
Mandatory
Mandatory
Return to advanced concepts in ER modeling.
The original relation is
student id —> [ssn, name]
There is a conditional dependency student id - -> ssn. Create another relation with these two attributes and drop ssn from the original...
author, paper, reaction, issue, journal
writes, refer to, discuss, in, has
many
1
0
No. The existence of that relationship is mandatory.
Yes. 1.
1.
Yes. 1.
1.
1.
No. The existence of that...
This is not strictly a part of the normalization process but can be considered an extension of it that helps improve storage efficiency for the database. Consider a table that stores information...
Please change this page according to your needs
insertion anomalies
You may want to add information about a person with whom you want to do business. The above table only allows information for customers that own a share of stock. If the person...
In this previous section I used an example to present an overview of how and why ER modeling is performed. In this section I present more detail on some of the basic...
Reasons for normalization
As database designers we are interested in designing a database that can both accurately reflect important facts about parts of the world we are interested in and stay...
Data bases, and the data base management systems that lord over them, are the core information systems technology. They are used — and will be used — to store corporate data, web pages, on-line...
Below is an ER model for a car dealership.
Questions you need to be able to answer:
List the entity types.
Does a car have to be classified as either new or used?
For a used car, can you tell...
Below is an ER model for a research group in chemistry that we're going to interpret. It is mainly concerned with keeping track of papers and the reactions they discuss.
Questions you should...
Exercise ER-1
What is the cardinality and existence of each of the following relationships in just the direction given? State any assumptions you have to make.
Husband to wife
Student to...
Situation 1
Sales people can sell cars and try to meet their own annual quota. Administrative employees cannot sell cars. Administrative employees have an annual salary.
Situation 2
Add the...
Above is an ER model for a university. There are some additional assumptions of which you should be aware:
Data is maintained for all past and current classes.
Need to know student's name and...
Here is a simplified description of a university:
Professors can teach many sections of a course. A specific course can have multiple sections. Courses have many prerequisites. A course can be...
Here's the schedule for the database portion of the Financial Engineering Boot Camp 2008:
Begin
End
Date
Time
Time
Location
Break
8/21/08
8:30am
12noon
Duderstadt Center 3rd Floor Training...
Here's the schedule for the database portion of the Financial Engineering Boot Camp 2009:
Begin
End
Date
Time
Time
Location
Break
8/25/08
8:30am
12noon
3358 Duderstadt...
Modern relational database management systems (DBMSs) allow you to have only one value per attribute for each entity occurrence. There are a couple ways to violate the single value restriction....
Fourth normal form (4NF)
Violations of this normal form are much less frequent than of the others. It occurs when there are repetitions of groups of data. Before you can learn how to put a table...
The purpose of this set of pages is to come up with a set of rules for translating from ER diagrams to table definitions to SQL create statements. The first set of links below analyze a series of...
Basics of using functional notation
First normal form
Second normal form
Third normal form
Fourth normal form
Conditional dependencies
Two extensions using functional notation
In this section I introduce two extensions to the normalization process that are not usually covered: fourth normal form and conditional dependencies.
If you are allowed to edit pages in this Site, simply click on edit button at the bottom of the page. This will open an editor.
To create a link to a new page, use syntax: [[[new page name]]] or...
At the end of the discussion of each relation I list the SQL create statements needed to create the tables necessary to represent the relation. The statement looks something like the...
After each of the possible relations I list a table structure that looks something like the following:
B(__b_id__, b_name, fk_b_a*, <b_other>)
The interpretation of this is
The table is...
Again, consider the [[[functionalbasics#tab-inventory-example Inventory table above]. Here is what we know. The relation R is Inventory. The set of attributes A is [Part #, Part Name, Supplier,...
Who can join?
Anyone can see this site, but only people who have the “secret password” can edit pages.
Join!
If you already know a "secret password", go for it!
Lecture 1 (Tues morning I)
We're going to introduce the topic (i.e., databases), and then move on to normalization. We'll also do some exercises.
Normalization slides
Normalization Exercise: Class...
Diagram
How to read
For any A there are possibly many Bs. For any B there are possibly many As.
Relevant relations
Either side of this relationship can be optional or mandatory.
Consider...
Diagram
How to read
One employee may manage many employees. Each employee may be managed by many managers.
Relevant relations
Both many sides of this relation can be either optional or...
Questions that might be asked
You have now gone through the whole normalization process. There are several concepts you must understand and many different ways of testing you on these concepts....
Consider the table below which contains sample data for parts and for vendors who supply those parts. In discussing these data with users, we find that part numbers (but not descriptions) uniquely...
Here we are presented with the following form. Note that one section can have only one professor, one professor can teach more than one section, a student can only have one major, several courses...
The answers to the first six of these exercises are on this page.
Question P.1
Using the graphical method, draw the 1NF relation given the receipt shown in [functional-basics.php#ex-form-videos...
Get to first normal form.
List all the fields.
Get rid of synonym problems; that is, don't use different names for the same attribute.
Get rid of homonym problems; that is, don't use the same...
Diagram
How to read
For any A there are possibly many Bs. For any B there only one A.
Relevant relations
The many side of this relation can be either optional or mandatory.
The two...
Diagram
How to read
One employee may manage many employees. Each employee is managed by exactly one manager.
Relevant relations
The many side of this relation can be either optional or...
Diagram
How to read
For any A there must be one B. For any B there must be one A.
Relevant relations
none.
Separate entities
Consider the alternatives of embedding the primary key of one...
Diagram
How to read
One employee must manage exactly one other employee. Each employee must be managed by exactly one other employee.
Relevant relations
None.
The table structure is as...
Diagram
How to read
For any A there are many Bs. For any B there is no more than one A. (Equivalently, there may be one A).
Relevant relations
The many side of this relation can be either...
Diagram
How to read
One employee may manage many employees. Each employee may be managed by a manager.
Relevant relations
The many side of this relation can be either optional or...
Diagram
How to read
For any A there is one and only one B. For any B there may be one A.
Relevant relations
Of course, if A were mandatory and B optional, the table names in this discussion...
Diagram
How to read
One employee may manage one other employee, but each employee is managed by exactly one employee.
Relevant relations
None.
The table structure is as...
Diagram
How to read
For any A there may be one B. For any B there may be one A.
Relevant relations
none.
Consider the alternatives of embedding a column from one table in the other table:...
Diagram
How to read
One employee may manage one other employee, and each employee may be managed by one employee.
Relevant relations
None.
The table structures are as...
mandatory
If an attribute is mandatory, add the words not null after the attribute's type in the table create statement.
optional
Do not add not null—-i.e., leave it as is.
default value
If an...
Here are my rules for database design and implementation. Again, these are not absolute rules but are guidelines that should be followed in most circumstances.
Can't have multiple values in a...
A big problem with the Stock table is redundancy. What we really have is the single fact that IBM's price is 100 but we have to express that fact for each customer that owns IBM. Second and third...
Welcome page
What is a Wiki Site?
How to edit pages?
How to join this site?
Site members
Recent changes
List all pages
Page Tags
Site Manager
Page tags
Add a new page
edit this panel
The whole purpose of ER modeling is to create an accurate reflection of the real world in a database. The ER model doesn't actually give us a database description. It gives us an intermediate step...
Members:
Moderators
Admins
Overview
It's my belief that the best way to learn SQL is to use SQL. So that's what you're going to do. There are some very high quality interactive tutorial sites already on the Web. You should...
The following sites were the main background that I referred to.
Delicious sql+tutorial sites
Simple SQL Reference
SQL Reference (PDF version)
Another very good online SQL tutorial at W3...
It's my belief that the best way to learn SQL is to use SQL. So that's what you're going to do. There are some very high quality interactive tutorial sites already on the Web. You should complete...
Overview
This site has been created for University of Michigan students by Scott Moore, a professor at the Ross School of Business. It has been assembled in order to provide some background related...
Without this relation, you can't tell which videos were actually rented on that particular receipt.
In this section I present the basics of how to translate subtypes into relations. I then describe how the optional versus mandatory and disjoint versus overlapping distinctions change these...
It would be impossible to list how to translate all the higher order relations into SQL. In this section I will present an example and provide instructions on how similar relations would be...
This normal form further guards against redundancy. It also helps make sure that our data does not accidentally go away. A nice feature, wouldn't you agree?
Before discussing 3NF, we must first...
Introduction
Normalization
Discussion
1st normal form
2nd normal form
3rd normal form
4th normal form
Conditional dependencies
Using graphs
Conclusion
Exercises
Answers
Normalization process
ER...
holds: no. chair: no. advisor: yes. has dept: no.
advisor, teach, qual, position, dept
advisor: 2 (prof, student). position: 2 (prof, dept). enroll: 2 (student, section).
Two foreign keys, both...
Print: Course.name. Tables: Dept, offered, Course. Constraints: Dept.id=25.
Print: Student.name. Tables: Student, Enroll, Section, Course. Constraints: Course.name=“E-commerce”.
Print:...
Go back to the university problem.
Each table is represented as TableName(field1, field2, field3, … fieldN), where the underlined fields are the fields that make up the primary key for that table.
Bldg(name)
Room(bldg, room...
Introduction to using graphs
In this section I am going to spell out, in as simple terms as possible, a method of normalizing that uses graphs. These graphs are called determinancy diagrams. The...
These entities exist only when another entity exists. The example used here is the one discussed in this section concerning employees and their salary history.
Suppose that the employee table...
According to Wikipedia, the world largest wiki site:
A Wiki ([ˈwiː.kiː] <wee-kee> or [ˈwɪ.kiː] <wick-ey>) is a type of website that allows users to add, remove, or otherwise edit...
