Basic Mapping: The Transformation Process

Cheatsheet

Common for table and column:

• ·         start with letter,
• ·         up to 30 alphanumeric
• ·         no space or special char except $ # and _
• ·         table name unique in one user acc
• ·         column name unique in table
• ·         don't use reserved words e.g. TABLE, NUMBER, SEQUENCE, ORDER, VALUES, LEVEL, TYPE : check more at docs.oracle.com/cd/B19306_01/em.102/b40103/app_oracle_reserved_words.htm

For table:

• ·         to plural from entity name
• ·         for short name of more than one word: First char of first word + First char of sec word + last char of last word
• ·         for short name of one word but more than one syllable:  First char. of first slb. +  First char. of sec slb. +  last char. of last slb. [how to recognize syllable - search it in thefreedictionary.com]
• ·         short name for one word containing one syllable: first char + second char + last char
• ·         In case of duplicate short name append with integer like SDT1

For column name (from attribute name):

• ·         special character and space : replace with underscore e.g. first name to first_name
• ·         often abbreviated e.g. first name to fname

Now lets start

• ·         To change the elements of an ERD into database elements-Transform
• ·         To associate the elements of an ERD with database elements-Map

1. Transform the following entities into table definitions using suitable naming conventions.

Table Name (short name)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name

STUDENTS (SDT)
Key Type	Optionality	Column Name
pk	*	id
	*	fname
	*	lname
	*	haddress
	○	pnumber

TEACHERS (TER)
Key Type	Optionality		Column Name
pk	*		id
	*		first_name
	*		last_name
	*		ssn
	*		ph_number
	○		email_address
BANK_ACCOUNTS (BAT)
Key Type		Optionality		Column Name
pk		*		bnk_id
pk		*		acc_id
		*		cus_name
		*		cus_bal
		○		cus_h_addr

2. Match the ERD elements to their corresponding database elements.

Analysis	Design
Attribute	Column
Entity	Table
ER Model	Physical design
Instance	Row
Primary UID	Primary key
Relationship	Foreign key
Secondary UID	Unique key

3. Identify the table diagram notations listed below.

         “pk” –primary key
         “fk” –foreign key
         “uk”-unique key
         “*”-mandatory
        “o”-optional

4. Create short names for the terms below based on the naming conventions rules.

•          Academic major - AMR
•          Undergraduate (un·der·grad·u·ate) - UDE
•          Class - CLS

5. Identify what is wrong with each of the table names below.

•          1997classes – start with numeric, letters are  in lower [table name is of great importance]
•          Schedule_of_classes_spring_2003 – 31 alphanumeric, letters are in lower [table name is of great importance]
•          Financial Aid deadlines! – has spaces and “!”, letters are in lower [table name is of great importance]

Introduction to Relational Database Concepts

• ·         A constraint which ensures that the column contains no null values and uniquely identifies each row of the table. –Primary Key
• ·         A column or set of columns that refer to a primary key in the same table or another table. –Foreign key
• ·         An entry in a table, consisting of values for each appropriate column -row
• ·         An implementation of an attribute or relationship in a table -column
• ·         An integrity constraint that requires every value in a column or set of columns be unique. - Unique key
• ·         Collections of objects or relations, set of operators to act on those relations and data integrity for accuracy and consistency. –relational database
• ·         More than one column or combination of columns that could serve as the table’s primary key –candidate key

1. Identify the integrity rules below.

• ·         A column must contain only values consistent with the defined data format of the column –column integrity
• ·         Define the relationally correct state for a database; they ensure that users can perform only those operations that leave the database in a correct, consistent state –data integrity
• ·         A primary key must be unique and no part of the primary key can be null –entity integrity
• ·         A foreign key must match an existing primary key value (or else be null) –referential integrity
• ·         The data stored in a database must comply with the rules of the database –user defined integrity

2. Data Integrity in the DJs Database

Examine the sample data for the tables in the DJs on Demand database (next page). Check for entity, referential, and column integrity. Identify any data-integrity violations.

Assume that all date columns should have a date format and all amount (cost) columns should have a number format.

Below mentioned all are data integrity (entity, referential, column, user-defined) violations.

1.      Problem designer removed 77 from id column of client table. This created Entity Integrity issue in clients table and referential integrity issue in events table.

2.      Same in theme _code of events table for row with id 15 - and referential integrity issue

3.      Problem designer instructs all amount columns to be number format and gives cost in row with id 17 as 2gs (events table) to demonstrate column integrity violation.

4.      Same in date of row with id 25 in events table - column integrity violation

5.      To identify user-defined integrity we some need business rules here which must be followed – insufficient information provided to identify this kind of violation.

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]