2
SQL Unit 14, ER Modeling, Assignment
Some of the following questions make use of a simple method for making schemas that represent table designs. You may need to be able to read a schema as part of a question or provide a schema as an answer. These schemas encapsulate the information used in an SQL create table command, but with simplified syntax and without types for fields. These points summarize the method:
A. A table is represented by a meaningful name, and the name is followed by a set of parentheses.
B. Inside the parentheses the attributes of the table are listed. The attributes should have meaningful names and they should be in an agreeable order.
C. Primary and foreign key fields are indicated by putting the abbreviations pk and fk after the names of those attributes, respectively. A primary key may also be indicated by underlining.
Some of the following questions require an ER diagram as an answer. Not all of the tables or fields needed in a complete design are necessarily given. If they're not given, you need to supply them. When drawing ER diagrams, follow these conventions:
A. Entities or tables should be represented as rectangles. Their names should appear above them. If an arc is in the way, their names should appear below.
B. Base tables are usually specified in the questions. Tables in the middle may not be. In any case, if another table is needed in the design, give it a descriptive name and include it.
C. For questions where fields are given or needed, they should be shown inside the rectangle representing the table.
D. 1-m relationships should be indicated by an arc with a crow's foot. No label is needed on the arc.
E. 1-1 relationships should be indicated by an arc without a crow's foot. The arc should be labeled descriptively.
F. For questions where fields are given, include them in the diagram. Primary and foreign key fields in the diagram should be explicitly marked with pk or fk.
G. For questions where some or all field names are not given, if necessary, make up descriptive names for the primary and foreign key fields which capture the relationships between the tables and explicitly mark them with pk and fk.
H. It is not necessary to use plus signs, vertical bars, or "O's" in the notation to indicate embedding of a key or whether an entity is mandatory or optional.
1. Consider a situation where you are trying to keep track of owners and houses where a person can own more than one house but a house can only be owned by one person. (No fields in diagram)
2. Suppose you are considering buildings rather than houses, where owners of the buildings may be business partners. In other words, a person can own more than one building in whole or in part, and a building can be owned by more than one owner in whole or in part. You do not have to include it, but note that a field like “percent ownership” might exist somewhere in such a design. (No fields in diagram.)
3. Now consider a case where apartment buildings, for example, are owned by a single owner and each building has a different resident manager. You want to keep track of the owners-managers in one table and the buildings in another. You also want to keep track of a relationship showing that each owner can own more than one building, but each building has only one resident manager and each resident manager can only manage one building. (No fields in diagram.)
4. Consider a scenario similar to the previous one, except throw out the building table. You want to model the relationship between owners-managers in a single table. (No fields in diagram.)
5. Consider a situation where you want to keep track of apartment buildings. You also want to keep track of numbered units within buildings. You also want to keep track of the current renters of units. A unit can’t be rented by more than one renter at a time but a renter can rent more than one unit at a time. You also want to keep track of historical payment-receipts unit by unit. Among other things the payment-receipts would include an indication of the period of time the receipt covered, the amount paid, and the date of payment. (No fields in diagram.)
6. Now consider a more elaborate setup, which might be more accurate or useful. (In some states, an apartment rental firm has to keep historical track of who renters were, how much they paid in rent, and the portion of their rent that went towards property taxes.) You want to keep track of owners. You also want to keep track of single owner buildings. You also want to keep track of the units in the buildings. You keep a historical, tax year record of unit payment-receipts. The payment-receipts have to be connected to a listing of renters, who may be current, or past. (No fields in diagram.)
7. Give a design showing that a student can enroll in many courses and a course can be taken by many students. Use these base table primary key names: studid, courseid. (Key fields in diagram.)
8. Give a design showing that at a particular moment in time a woman can only be “united in marriage” with one man. There are two possible options for the solution, gynocentric and androcentric. Choose one. Either will be accepted. Use these base table primary key names: womanid, manid. (Key fields in diagram.)
9. Give a design showing that a state can have more than one county and a county can have more than one town. Treat both county and town, successively, as weak entities. Use these primary key field names: stateid, countyid, townid. (Key fields in diagram.)
10. Give a design showing the following relationships based on the kind of information that is collected when you have a Carr’s card, for example. Customers can go on many shopping trips, but every trip is uniquely associated with the single customer whose card is swiped for a particular set of items. Items here are not identified down to individual unique cans and packages, but types of items. In other words, two different customers can buy “a can of corn”. They obviously can’t both buy the same can of corn, but they can both pick out this item. On any shopping trip a customer can buy more than one item, and any (kind of) item can be bought by more than one customer. Use these base table primary key names: custid, tripid, itemid. (Key fields in diagram.)
11. Suppose you record all faculty members at a university in a single table. Give a design showing the relationship between faculty members and chairs of departments, who are also faculty members. Use this base table primary key name: facid. (Key fields in diagram.)
12. Give a design showing the relationships between manufacturing companies (companies, for short) and products. The idea is the following: companies can manufacture more than one product. Products are identified down to a specificity that includes the manufacturer. In other words, if Acme makes a flange and Universal makes a flange, and the technical specifications for these flanges is the same, they are still treated as distinct, unique products. Because these are manufacturing companies, they also buy products for use in producing their products. A company can buy many different kinds of products, and a product can be bought by many different companies. Use these base table primary key names: compid, prodid. (Key fields in diagram.)
13. Give a design showing the following relationships. You want to record information on students who are doing internship work in a particular area of expertise (database, accounting, psychology, etc.). Assume that students, areas of expertise, and internship providers are entities. A student can work with more than one provider in more than one area of expertise. A provider can have more than one student in more than one area of expertise. It is not the case that, if a student is known to work in a particular area of expertise, and it’s known that a provider is currently hosting interns in that area, that that student must work for that provider in that area. Use these base table primary key names: studid, areaid, provid. (Key fields in diagram.)
14. Give a design showing that a person can have many dogs and a dog can have many people. (This is the “human family as pack of dogs” model.) Use base table primary key fields pid and did. (Key fields in diagram.)
15. Give a design showing that a person can only have one dog and a dog can only have one person. (This is the “strict yet loving parents” model, where a child is allowed just one dog and is responsible for it.) Use base table primary key fields pid and did. (Key fields in diagram.)
16. Give a design showing that a musher has a single kennel, each kennel has a single musher, each kennel consists of many dogs, and any given dog is only in one kennel. The number of dogs per kennel should not be limited in any way. Use base table primary key fields mid, kid, and did. (Key fields in diagram.)
17. Give a design showing that a kennel can have more than one dog team, but each team belongs to only one kennel. Each team can have more than one dog, but each dog belongs to only one team. Use the base table primary key fields kid, tid, and did. (Key fields in diagram.)
18. Give a design showing that a kennel can have more than one team. Each dog can only be a member of one team. Each musher can only have one team and each team can only have one musher. Use the base table primary key fields mid, kid, tid, and did. (Key fields in diagram.)
19. Give a design showing that each dog is born into a single litter and that each litter has a single mother and father, but each dog is potentially a parent in more than one litter. Use the base table primary key fields did and lid. If necessary in distinguishing foreign keys, use mid and fid for mothers and fathers. (Key fields in diagram.)
20. A woman has an id, a name, and a dob. She can have 0 or more children. A child is a separate entity having an id, a name, a sex, and at most one woman who is its mother. (All fields in diagram.)
21. Draw an ER diagram for this scenario: A course has a uniquely identifying CRN, a dept, a courseno (different from the CRN), and a name. A student has a studentid, a name, and a major. A course can have many students enrolled in it and a student can be enrolled in more than one course. An enrollment is identified by a combination of course and student since no student can be enrolled in the same course more than once. An enrollment also has a grade associated with it. (All fields in diagram.)
22. A department has an id, a name, and a phone. It can have many offices assigned to it. An office belongs to only one department. An office is identified by a buildingid and a roomno. It also has a size. An employee has an id, and a name. A department may have many employees assigned to it. An office may have many employees assigned to it. An employee is assigned to one department. An employee is assigned to one office. (It is true that an employee could be assigned to an office that belongs to a different department. Nothing in the model should prevent this.) (All fields in diagram.)
23. The next group of questions has something to do with shoes. The viewpoint for the designs is that of a shoe wholesaler which does business with chains of shoe stores. Let one of the entities in the model be shoe manufacturer: shoemfr(mrfid, name, address, phone). The fields in the shoe table are given below. Draw a diagram which will show the relationship between shoemfr and shoe. (All fields in diagram.)
shoeid = unique id for a particular kind of shoe
(not an individual instance of a particular shoe)
mfrid = the unique id of the shoe’s manufacturer,
modelname = the manufacturer’s name for that shoe
style = pump, loafer, etc.
use = dress, casual, etc.
M/W = men’s or women’s shoe
24. Let there be a separate table, color, consisting of two fields, colorid and colorname, including all of the colors any shoes might have. Draw a diagram showing the relationship between shoe and color. (All fields in diagram.)
25. Shoes can come in more than one size and more than one width. Let there be separate tables size and width. For the time being assume that they only have one field each, their primary keys respectively, sizeid and widthid. The important thing to realize is that for any given kind of shoe, not all sizes necessarily come in all widths. Draw a diagram that correctly captures the information about which shoes come in which sizes and which widths. (All fields in diagram except that it’s not necessary to show all fields in the shoe table, just the primary key.)
26. You should not include the tables for shoechain, shoe, size, width, or color in your answer to this question. Do include the tables for shoeorder and shoeorderline. Shoechains place shoeorders. Shoeorders have shoeorderid’s and dates. There are shoeorderlines that correspond to them. Let the shoeorderline be a weak entity, with a shoeorderlineno field. The shoeorderlines specify shoe, size, width, color, and quantity. Draw a diagram showing the relationship between shoeorders and shoeorderlines. (All fields in diagram.)
27. As a general rule, is it considered a good idea to encode non-key attribute values in a pk? (This is just a yes or no question. No diagram is required.)
This descriptive scenario applies to the next two questions: A snow removal company keeps track of its employees. They are identified by an empid. A lastname, firstname, and payrate are also recorded for them. The company also has many clients. Clients are identified by a clientid field. Clients also have a lastname field, a firstname field, a field named BorR, which tells whether the account is business or residential, and an address. The company does not try to handle all types of addresses. For the purposes of this question, the address field is a single field without any additional tables.