Supplemental reading: Bagui and Earp, Ch. 2, 42-46, Ch. 6 pp. 145-152, Ch. 7 pp. 165-181, Ch. 8 pp. 187-195,
Bagui and Earp, 2nd Edition:
Ch. 4 pp. 89-94 “Mapping the Entity Diagram to a Relational Database”
Ch. 8 pp. 208-216 “Recursive Relationships” – “The Derived or Redundant Relationship”
Ch. 9 pp. 229 – 244 “Binary or Ternary Relationships” – “n-ary Relationships may be Resolved into Binary Relationships”
Ch. 10 pp. 253 – 263 “What is...
Assignment 3
Supplemental reading: Bagui and Earp, Ch. 2, 42-46, Ch. 6 pp. 145-152, Ch. 7 pp. 165-181, Ch. 8 pp. 187-195,
Bagui and Earp, 2nd Edition:
Ch. 4 pp. 89-94 “Mapping the Entity Diagram to a Relational Database”
Ch. 8 pp. 208-216 “Recursive Relationships” – “The Derived or Redundant Relationship”
Ch. 9 pp. 229 – 244 “Binary or Ternary Relationships” – “n-ary Relationships may be Resolved into Binary Relationships”
Ch. 10 pp. 253 – 263 “What is Generalization or Specialization” – “Methodology and Grammar for Generalization/Specialization Relationships”
Part 1:
Give an E-R Model for the following narrative given by a manager of a fictional real estate company:
“Our database keeps track of houses and sellers. Each house has a unique home ID, a location (consisting of a street address and a state name), and a number of square feet. A seller has a Social Security number to identify him/her, a name, a phone number, and an optional spouse name. A seller may own one or more houses, but each house has just one seller that owns it.”
“Our agents list houses for the sellers – a seller may use the same agent to list many of their houses if they have several houses to sell, or they may list different houses with different agents. An agent can list many houses, of course, but each house is only listed by one agent at a time. Each agent has a unique AgentID, a name, one or more phone numbers, and a name for the office out of which he/she works. When a seller lists a house with an agent, an asking price and a commission percentage are determined.”
“We keep track of potential buyers too. A buyer has a Social Security number to identify him/her, a name, a phone number, and a price range (consisting of lower and upper limits). An agent can represent many buyers, but a buyer is represented by only one agent – in fact, we don’t even keep track of the information on a potential buyer unless he/she is represented by one of our agents. ”
The same as it was for Assignment 2, your task is to draw an E-R diagram with containing the entities discussed above, their attributes and keys, and the relationships among them.
Also provide a formal written description of your E-R Model, with all of the necessary information for each entity, attribute, and relationship. After your formal written description, include a list of “Additional Assumptions” that you had to make beyond what was stated in the narrative to determine the properties of the entities, attributes, and relationships
Part 2:
In this assignment, your task is to model a snack distributer database.
The database contains information about a number of different snack types: Skittles, Animal crackers and Trail mix. All snacks must have a unique identifier (Universal Product Code or UPC), a name and a number of calories in a package – these attributes are non-optional and must be specified for every snack in the database.
The particular snacks also have some additional information that only applies to them. Skittles information always includes a flavor name, and a value entry for each color that may appear in the package. Animal cracker listing has to include an expiration (i.e., “best-before”) date. Trail mix description has to include the estimated average packaged volume and a listing of (possibly several) allergy codes that may be applicable. Note that we also allow selling snacks that do not currently belong to any category (temporarily, until they are assigned their own category).
All snacks are sold to us by different distributers. There are no exclusive contracts of any sort – any distributer can provide any number of snacks and vice-versa. Each snack distributer has a unique id (DID) that can be used to look them up. Moreover, the distributers provide a 1-800 number for all customer service issues. In some cases, snack distributers do not offer retail services – in that case they bulk-sell only to other distributers rather than directly to us (in that case we do not know which snacks are being sold, just which distributers deal exclusively with other distributers). We keep track of this information so that we can escalate any health alerts to the original distributer(s).
Hint: You can copy superclass/subclass diagram and connectors from a Visio example (Accounts_Example.vsd) posted in the document section.
Similarly to Part1, draw an E-R Model representing the above database and give a formal written description of your E-R Model, with all of the necessary information for each entity, attribute, and relationship. Please include any assumptions that you have made to supplement the text description.
You can group Visio diagrams together in one file – Please submit 2 files for each part. Be sure that your name and “Assignment 3” appear at the top of each submitted file.
Assignment 3
Supplemental reading: Bagui and Earp, Ch. 2, 42-46, Ch. 6 pp. 145-152, Ch. 7 pp. 165-181, Ch. 8 pp. 187-195,
Bagui and Earp, 2nd Edition:
Ch. 4 pp. 89-94 “Mapping the Entity Diagram to a Relational Database”
Ch. 8 pp. 208-216 “Recursive Relationships” – “The Derived or Redundant Relationship”
Ch. 9 pp. 229 – 244 “Binary or Ternary Relationships” – “n-ary Relationships may be Resolved into Binary Relationships”
Ch. 10 pp. 253 – 263 “What is Generalization or Specialization” – “Methodology and Grammar for Generalization/Specialization Relationships”
Part 1:
Give an E-R Model for the following narrative given by a manager of a fictional real estate company:
“Our database keeps track of houses and sellers. Each house has a unique home ID, a location (consisting of a street address and a state name), and a number of square feet. A seller has a Social Security number to identify him/her, a name, a phone number, and an optional spouse name. A seller may own one or more houses, but each house has just one seller that owns it.”
“Our agents list houses for the sellers – a seller may use the same agent to list many of their houses if they have several houses to sell, or they may list different houses with different agents. An agent can list many houses, of course, but each house is only listed by one agent at a time. Each agent has a unique AgentID, a name, one or more phone numbers, and a name for the office out of which he/she works. When a seller lists a house with an agent, an asking price and a commission percentage are determined.”
“We keep track of potential buyers too. A buyer has a Social Security number to identify him/her, a name, a phone number, and a price range (consisting of lower and upper limits). An agent can represent many buyers, but a buyer is represented by only one agent – in fact, we don’t even keep track of the information on a potential buyer unless he/she is represented by one of our agents. ”
The same as it was for Assignment 2, your task is to draw an E-R diagram with containing the entities discussed above, their attributes and keys, and the relationships among them.
Also provide a formal written description of your E-R Model, with all of the necessary information for each entity, attribute, and relationship. After your formal written description, include a list of “Additional Assumptions” that you had to make beyond what was stated in the narrative to determine the properties of the entities, attributes, and relationships
Part 2:
In this assignment, your task is to model a snack distributer database.
The database contains information about a number of different snack types: Skittles, Animal crackers and Trail mix. All snacks must have a unique identifier (Universal Product Code or UPC), a name and a number of calories in a package – these attributes are non-optional and must be specified for every snack in the database.
The particular snacks also have some additional information that only applies to them. Skittles information always includes a flavor name, and a value entry for each color that may appear in the package. Animal cracker listing has to include an expiration (i.e., “best-before”) date. Trail mix description has to include the estimated average packaged volume and a listing of (possibly several) allergy codes that may be applicable. Note that we also allow selling snacks that do not currently belong to any category (temporarily, until they are assigned their own category).
All snacks are sold to us by different distributers. There are no exclusive contracts of any sort – any distributer can provide any number of snacks and vice-versa. Each snack distributer has a unique id (DID) that can be used to look them up. Moreover, the distributers provide a 1-800 number for all customer service issues. In some cases, snack distributers do not offer retail services – in that case they bulk-sell only to other distributers rather than directly to us (in that case we do not know which snacks are being sold, just which distributers deal exclusively with other distributers). We keep track of this information so that we can escalate any health alerts to the original distributer(s).
Hint: You can copy superclass/subclass diagram and connectors from a Visio example (Accounts_Example.vsd) posted in the document section.
Similarly to Part1, draw an E-R Model representing the above database and give a formal written description of your E-R Model, with all of the necessary information for each entity, attribute, and relationship. Please include any assumptions that you have made to supplement the text description.
You can group Visio diagrams together in one file – Please submit 2 files for each part. Be sure that your name and “Assignment 3” appear at the top of each submitted file.
