Drawing Conventions for Readability

• ·         Space on a page or poster not covered by print or graphic matter. – white space
• ·         An entity that will have a large number of instances. – High volume entities

1. Create an ERD based on the following Summit Sporting Goods scenario. Be sure to follow drawing conventions for readability and clarity.

“I’m a manager of a sporting-goods wholesale company that operates worldwide to fill orders from retail sporting-goods stores. The stores are our customers (some of our people prefer to call them our clients). Right now we have 15 customers worldwide, but we’re trying to expand our customer base by about 10% each year starting this year. Our two biggest customers are Big John’s Sports Emporium in San Francisco and Womansports in Seattle. For each customer, we must track an ID and a name.

We may track an address (including the city, state, zip code, and country) and phone number. We maintain warehouses in different regions to best fill the orders of our customers. For each order, we must track an ID. We may track the date ordered, date shipped, and payment type when the information is available.”

2. Add on to the Summit Sporting Goods ERD.

“Right now we have the world divided into five regions: North America, South America, Africa/Middle East, Asia, and Europe. That’s all we track; just the ID and name. We try to assign each customer to a region so we’ll generally know the best location from which to fill each order. Each warehouse must have an ID. We may track an address (including the city, state, zip code, and country) and phone number. We currently have only one warehouse per region, but we’re hoping to have more soon.

I manage the order-entry functions for our wholesale sporting-goods business. My department is responsible for placing and tracking the orders when our customers call. For each department, we must track the ID and name. Sometimes, our customers just mail us the orders when they are not in a rush, but most often they call us or fax us an order. We are hoping to expand our business by providing immediate turnaround of order information to our clients. Do you think we can put this application on the Web?”

Answer: If the order is received on fax or by phone, it must have to put inside system using web application, and customer may also be given credentials on web application being created.

3. (Optional) Add on to the Summit Sporting Goods ERD.

“We can promise to ship by the next day as long as the goods are in stock (or inventory) at one of our warehouse locations. When the information is available, we track the amount in stock, the reorder point, maximum stock, a reason as to why we are out of stock, and the date we restocked the item. When the goods are shipped, we fax the shipping information automatically through our shipping system. No, I don’t manage that area. My department just ensures that our customers have the correct billing information and verifies that their accounts are in good credit standing. We also record general comments about a customer.

We do make sure that all the items they have requested are in stock. For each item, we track an ID. We may also track the item price, quantity, and quantity shipped if the information is available. If they are in stock, we want to process the order and tell our clients what the order ID is and how much their order total is. If the goods are not in stock, the customer tells us whether we should hold the order for a full shipment or process the partial order.”

4. (Optional) Add on to the Summit Sporting Goods ERD.

“The accounting department is responsible for maintaining the customer information, especially for assigning new customer IDs. My department is allowed to update the customer information only when an order is placed and the billing or ship-to address has changed. No, we are not responsible for collections. That’s all handled by accounts receivable. I also think that the sales reps get involved because their commission depends on customers who pay! For each sales rep, or employee, we must know the ID and last name. Occasionally we need to know the first name, user ID, start date, title, and salary. We may also track the employee’s commission percent and any comments about the individual.

Our order-entry personnel are well versed in our product line. We hold frequent meetings with marketing so they can inform us of new products. This results in greater customer satisfaction because our order-entry operators can answer a lot of questions. This is possible because we deal with a few select customers and maintain a specialty product line. For each product, we must know the ID and name. Occasionally we must also know the description, suggested price, and unit of sale. We would also like the ability to track very long descriptions of our products and pictures of our products, when it is necessary.”

Assumption: Often I will have a mix of conventions, depending on amount of space  I have and my preference. Readability and clarity is more important.

Modeling Change: Price

• ·         Keeping an on-going record of transactions. – Journaling and/or Logging
• ·         A rise in value or price, especially over time - Appreciation
• ·         A decrease or loss in value, because of age, wear, or market conditions. - Depreciation

1. Comic-book collectors need to know the price history of different types of comics. This helps them decide what to purchase/collect and how much to sell their collection for.

Create an ERD (two or three entities) to model this.

There are high chances that Enddate is known here, since price fluctuation is not very drastic on time scale and we are talking of new buys rather than old stuff buy where price vary of items with same condition.

2. When a student’s grade is changed, we need to record information on the teacher who changed the grade and the reason for the change.

Start with the ENROLLMENT entity, which is the resolution of the M:M between STUDENT and CLASS.

Enrollment is there, but quite possible not graded yet. If a grade is changed, Grade in GRADELOG with most recent DateTimeGiven is honored for transcripts. Reason for change is saved in comments. It may be used to say very good in first attempt of grading also.

3. When vintage-car aficionados shop for additions to their collections, it helps to know the price history of the cars they are interested in. It may also make them consider selling one of their cars if it has greatly increased in value since they bought it.

Create an ERD (at least two entities) to model this.

We store here datetime evaluated, without end time, because even similar used condition car may be sold for two different prices. So it’s a kind of crude feed which I am storing here.

Modeling Change: Time

• ·         A constraint or data restriction that results from the time dimension. - Time-related Constraint
• ·         Refers to a relationship that may or may not be transferable, depending on time.- Conditional Non-transferability
• ·         Property of a relationship where an instance of A is related to an instance of B, and the association cannot be moved to another instance of B non- transferability

1.  Speeding Tickets

Each police officer may issue speeding tickets to motorists in an assigned area. Originally, the attribute date was modeled as part of the SPEEDING TICKET entity. However, the city police department wants to see if there is a relationship between weather and the frequency of speeding tickets -- do people drive faster on nice sunny days? Are there more tickets in hot weather or cool weather?

Modify the ERD to address this new requirement.

2. Examine the ERD that represents classroom assignments for different exams.

a. Why is start time part of the UID of ASSIGNMENT?

Each assignment identified by StartTime, amongst instances with same exam, date of exam, and class.

Say, no of students in class are more than no of computers available on which exam will be presented. So I have timeslots mentioned in assignment instances.

b. Name at least three time-related constraints. For example: End time must be later than start time. Indicate if the constraint represents conditional non-transferability.

• ·         An exam cannot be unassigned b/w starttime and end time. This one is a conditional non-transferability.

StartTime field is not dateTime, its only time here e.g. 20:30, not 21September 2016 20:30. Same is true for rnd time.  [Why didn’t we store it as a datetime rather than date and time separately in two attributes?]

• ·         Start time and end time are times occurring in date of exam.
• ·         Also, Below mentioned ERD restrict exam to finish on the same day. Since if end time is on the next day, it is not recorded anywhere. [ better is store end datetime if I want to allow exam to continue to next day.]
• ·         There must be at least one day and start time in that same day when an exam instance must be assigned to any class.

Modeling Historical Data

1. In the scenario below, what words or phrases indicate to you the need to track data that changes over time?

Actors at the local theater sign contracts that specify their pay and working conditions. These contracts are renewed annually. Each year the contracts may change through new language and pay rates. In order to resolve labor disputes, the theater must sometimes go back several years in order to see the contract provisions in place on a particular date.

2. Given the scenario below, construct a conceptual model.

The Seville, Spain soccer association is renovating their soccer arena. They are adding luxury boxes that will be offered to companies. These boxes contain varying numbers of seats. Each box is assigned a section number and level. The boxes are leased by the year, and the prices are set individually according to the location and number of seats in the box. The soccer association needs to keep track of the company name, address, phone, and contact person. Sometimes the companies also provide a logo. Companies that lease a box have the right to renew their leases each year, but they often do not. When a lease for a box opens up, the association contacts companies that previously held leases to see if they want to lease one again.

Assumptions:

• ·         If a box is booked say in Nov, it doesn’t mean company gets it only for Nov & Dec, they should get it till next Nov.  “The boxes are leased by the year” only helps here, I need not store end date, just start date of lease is sufficient. End date is automatically same date next year minus 1day.
• ·         Since corporate discounts are also given based on negotiations, price should be stored in lease instance. Some may suggest, keep base price in box and discount % in company instance, but problem here is that sometimes returning customers get extra discounts on lease renewals. So, better keep price decided for next year in Lease instance.
• ·         So each company may have multiple lease instances. Lease is identified by box, company and dateofbooking.
• ·         A lease must be undertaken by a company and must be for a box.
• ·         A box may be booked multiple times in many years.
• ·         Section is identified by level and sectionnumber [with same number every level has a section]
• ·         Box is identified by boxNumber [ with same number every section has a box]

Arcs

• ·         A curved line used to represent an exclusive relationship in an entity-relationship diagram - Arc
• ·         A logical operator that returns a true value if one, but not both, of its operands is true. - Exclusive OR
• ·         A relationship between one entity and two (or more) other entities when only one of the relationships can exist at a time. - Exclusive OR Relationship/ mutually exclusive relationship
• ·         A restriction that applies to data, which is often dictated by the business rules. - constraint

1. A show ticket is purchased from an agent, the box office, or the Internet. A ticket has a description, an event, a date and a price. An agent has a name and a phone number. The box office has an address and a phone number. The Internet has a URL address.

Draw the entities and represent the mutually exclusive relationship.

Assume that all type of sellers it for same price.  One Ticket Instance must be booked by one and only of (one Agent Instance, one box office instance or one website instance)

2. Draw the MENU entity as a supertype of the PROMOTIONAL, REGULAR, and OTHER entities. The UID of MENU is code. MENU is related to FOOD ITEM through this relationship: each MENU may contain one or more FOOD ITEMs, and each FOOD ITEM must be listed on one and only one MENU. The UID of FOOD ITEM is a barred UID using its attribute “number”. Add appropriate attributes to the other entities and be ready to defend your choices.

Second Normal Form

• The outcome of the second step of database normalization – Second Normal Form (2NF)

1. The UID of a library book includes its BOOK number and SHELF number. Does this ERD follow the rules of Second Normal Form? If you spot a violation, correct it.

Sugession: 2NF on shelf. [Assume that a book won’t have multiple author, even if it has we only track major one so, 1NF is not violated their, problem specifically targets 2NF here.]

2. .      Class Enrollment is the intersection entity that resolves the M:M between STUDENT and CLASS. Does the ERD follow the rules of Second Normal Form? If you spot a violation, correct it.

Suggestion:

3. A store can be located in several shopping malls, and a shopping mall may house several stores. To locate a particular store in a specific neighborhood, you will need to know the name and address of the nearby shopping mall, plus the name of the store. Does the ERD follow the rules of Second Normal Form? If you spot a violation, correct it.

Answer:

To locate a store, name and address of mall + store name is needed, then why location is present in store listing entity. Ok, let’s be realistic here, inside mall, where is store? In shop no 100. This is what here location means. I say no harm in storing a location value. Some may argue why location is not in store entity, what if this store has another branch in another mall and it is located in shop no 200 there?

 Now we come to store specialty, this may be the reason why I have this problem here. Store franchises follow exactly same recipes’ and if there is a specialty, then it’s of the whole chain. Since specialty is of store not only the one located in specific mall, I want to move it to Store Entity. 2NF wants specialty to be dependent on Store UID.

Third Normal Form

• ·         The form of database normalization where all non-key fields are dependent on the key, the whole key, and nothing but the key. - Third Normal Form (3NF)
• ·         A condition that exists when any attribute in an entity is dependent upon any other non-UID attribute in that entity.- Transitive Dependency

1.      Identify the transitive dependency in the model below. State which attributes violate Third Normal Form.

Store address being dependent on store name, is a transitive dependency and violates 3NF.

2. Define the rule of Third Normal Form.

• ·         1NF is satisfied by resolving multi-valued attributes.
• ·         2NF is satisfied by - 1NF  + Each non UID attribute is dependent on the whole UID,
• ·         #NF is satisfied by - 2NF + there should be no transitive dependency present.

3. A color scheme for a car includes specifications for paint color for the body and the interior colors and materials. For example: The “Desert” color scheme includes silver paint and gray leather interior; the “Sunburst” color scheme includes gold paint and cream leather interior. Does the model below follow the rules of Third Normal Form? If you spot a violation, correct it.

Suggestion:

• ·         Model I choose depend on manufacturer (make) – not all manufacturer make every model.
• ·         Color scheme available really depends on model I choose to buy.
• ·         Paint color and interior color depends on color scheme I choose.

Assumptions:

• 1.      A model must have at least one color scheme designed for it
• 2.      A Color scheme must be created with at least one model in mind.
• 3.      Once car is manufactured, I can’t change scheme and model of car.

4. Assume the following business rules:

• An athlete employs one agent.

• An agent may work for one or more athletes.

• An athlete may play for one team.

• A team may have one or more players

Does the model below contain one or more transitive dependencies? Identify the attributes involved in the transitive dependencies.

The transitive dependencies identified:

• ·         Team coach is dependent on team. And if player changes team, coach changes for player, he won't drag his coach with him to new team J
• Agent commission is dependent on what agent player chooses, if he changes agent, commission may also change (unless agents have a labor union who decides there rates J ). Or if agent revises his commission, 'all the players using him' instances will have to be updated. 

5. Now that you have an idea of what makes a good UID, you need to be aware of the controversy, benefits, and difficulty of uniquely identifying someone. Consider the idea of a national ID card. What kinds of problems would the card create and what kinds of problems would it solve? If your country already uses a national ID card, what are the benefits and issues associated with this? In your opinion, could DNA mapping become the national ID card? Why or why not?

I am more satisfied with the concept of single identity of every person than afraid of. 

Why satisfied:

• ·         In all government transactions I just need to give SSN and done, say no paper work while getting a new credit card.
• ·         Say I update my name at one place, ideally it should get propagated everywhere ( in reality it’s not so simple, since theoretically possible in future, so putting here under why satisfied)

Why afraid of:

• ·         If someone has my SSN, he can even get a credit card that is another story that all credit cards have fraud protections but still…
• ·         It’s bad for me in a way that, only by knowing SSN they know how many payments of credit card I missed ( I didn’t do it intentionally J )

DNA thing is a good idea but we are far way behind as of today. It won’t be an artificial UID like SSN but costly to collect this data as of today.