Assignment : Relational Data Model for Transaction Processing (OLTP)

One of the most important transactions for a business is a sale. Receiving payment in exchange for a good or service is the ultimate goal for almost every organization.

Consequently, businesses typically structure their transaction processing systems to efficiently track sales and the business assets and events that support them.

Let's take a look at an important business document for movie theaters that is created when a sale is made (a movie ticket) and see what business entities (events and assets) this ticket hints at by listing some of their attributes.

Directions:

Follow the instructions below, recording your answers in the provided Excel template file (on Blackboard).

Part A - Entities and Attributes

Extract as many attributes as you can from the example receipt pictured above and organize them into entities that would be stored in the movie theater chain's OLTP database.

1. Indicate the entities and their attributes in the provided Excel template with the entity name in the top (blue) row of each block, and attributes on subsequent (grey) rows of the block.

a. Entities should be named after the noun they describe (e.g. Theater) and include the following:

i. a primary key (e.g. TheaterKey)

ii. attributes shown on the receipt (e.g. Theater Name)

iii. other attributes not necessarily shown (e.g. Theater Address)

b. Record these entities and attributes in the Excel template provided

i. DO write down the generic entity or attribute

ii. Do NOT write down the example data shown on the receipt

iii. Example: DO list "Name" as an attribute of the "Theater" entity. Do NOT write down "Village Cinemas" as part of your answer

c. Housekeeping:

i. copy/paste blocks to create any additional entities needed

ii. Add extra grey rows to blocks if necessary to capture all the entities brainstormed

iii. Remove any left-over generic entities or attributes (including the . . . ) once you're done

2. Indicate the Primary Keys (PK) for each entity with the letters "PK" to the left of the attribute that will be acting as the key.

Part B - Relationships

3. List the relationships that exist between the entities. This can be done in the indicated "Part B" portion of the template spreadsheet.

a. Be sure to note their cardinality (one-to-one, one-to-many, many-to-many).

b. If you find a many-to-many relationship, try to think of another entity that can have a one- to-many relationship with each of the other two tables.

c. Example: Many movies are shown in many theaters. To relate the two, we create a ‘showing' entity, which relates to only one theater and one movie.

4. Indicate the Foreign Keys (FK) necessary to create the relationships between tables

Part C - Additional Amenities

Other ticket types and additional amenities are available to purchase as "Upcharges" that were not shown on this ticket (because the person who purchased it chose not to buy those amenities).

5. After reviewing the different ticket prices, upcharges, and specials above, fill out the table in the Excel template to map each item to an attribute, and indicate what entity that attribute best belongs to.

When choosing the appropriate entity, make sure the attribute is functionally dependent on that entity's primary key (you may find that more than one of the additional amenities can be assigned as a value to the same attribute).

BONUS for updating your entities with the new attributes (and relationships if necessary)

Attachment:- Relational Data Model .rar