Relationship Mapping

• ·         A relationship in a database where the foreign key column in the database table cannot be updated –non-transferable relationship
• ·         A series of relationships implying that the unique identifier of each entity in the chain is carried down to the entity on the next level – cascade barred relationship
• ·         The product of the resolution of a many to many relationship. – Intersection entity

The following entities were mapped to tables in a previous lesson: SHIFT, REGULAR

MENU, PROMOTIONAL MENU, FREQUENT DINER CARD.

1. Transform relationships into foreign-key columns.

Use as many rows as necessary in a table similar to the one in the diagram.

Key Type (pk, uk, fk)	Optionality (“*” or “o”)	Column Name

Refer to the completed Global Fast Foods model and map the following entities:

FOOD ITEM:

FOOD_ITEMS (FIM)
Key Type	Optionality	Column Name
pk	*	number
	*	description
	*	price
fk	○	reg_code
fk	○	promo_code

ORDER:

ORDERS (ODR)
Key Type	Optionality	Column Name
pk	*	number
	*	date
	*	time
fk	*	staff_id
fk	○	din_card_id

ORDER LINE:

ORDER_LINES (OLE)
Key Type	Optionality	Column Name
pk, fk	*	ord_num
pk, fk	*	fd_num
	*	qty

2. Indicate if a check constraint or additional programming is needed to enforce the relationship in the database. Under each table diagram, write the conditions that the check constraint or program has to ensure.

For example:

“To enforce exclusive relationships, a check constraint is needed to make sure that (column A is not null and column B is null) OR (column A is null and column B is not null).”

“To enforce a nontransferable relationship, additional programming is needed to make sure that the <foreign key column> cannot be updated.”

• ·         PROMOTIONAL_MENUS: A check constraint is needed here to ensure ( (end_date is not null and end_date is greater than start_date) or (end_date is null)) [ taking back strikethrough, seems to be out of scope for problem mentioned]
• ·         REGULAR_MENUS: When a food item is ordered, programming is required to not accept order based on hrs_served [ this seems to be out of scope so taking this observation back]
• ·         FOOD_ITEMS:A check constraint for exclusive or relationship

 (reg_code is not null and promo_code is null) or (reg_code is null and promo_code is not  null)

• ·         ORDERS: additional programming to ensure staff_id cannot be changed after the row is created once. This is ‘non-transferability’. [ Also, once served order row cannot be deleted, out of scope of this practice, this seems to be out of scope, so taking strikethrough part back.]
• ·         SHIFT_ASSIGNMENTS: additional programming to ensure, only one day before the date in this table occurs, the assignment can be modified. Once the date started, no change allowed to satisfy ‘conditional non-transferability’. 

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.