Sports League Step 1 Teacher
academy.oracle.com Database Design Project Oracle Baseball League Store Database Project Scenario: You are a small cons
Views 1,523 Downloads 163 File size 641KB
Recommend stories
- Author / Uploaded
- ivory2011
Citation preview
academy.oracle.com
Database Design Project Oracle Baseball League Store Database Project Scenario: You are a small consulting company specializing in database development. You have just been awarded the contract to develop a data model for a database application system for a small retail store called Oracle Baseball League (OBL). The Oracle Baseball League store serves the entire surrounding community selling baseball kit. The OBL has two types of customer, there are individuals who purchase items like balls, cleats, gloves, shirts, screen printed t-shirts, and shorts. Additionally customers can represent a team when they purchase uniforms and equipment on behalf of the team. Teams and individual customers are free to purchase any item from the inventory list, but teams get a discount on the list price depending on the number of players. When a customer places an order we record the order items for that order in our database. OBL has a team of three sales representatives that officially only call on teams but have been known to handle individual customer complaints.
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
2
Part 1 : Conceptual Design Part 1 Step 1 : Business Requirements Using the text provided in the scenario above identify the business requirements that will allow you to understand the business processes involved in running this type of organization. Use the following categories to help you with this: • • •
Business rule: It is used to understand business processes and the nature, role, and scope of the data. Assumption: It can be defined as a fact or a statement that has been taken for granted. Problem: It can be defined as a situation or scenario that requires attention and a possible solution to alleviate the situation.
Build a list of business needs, rules and assumptions based on your scenario, research, and objectives. (Answers may vary)
Solution • • • • •
Customers can be either individual or represent a team Customers can purchase items from the inventory list Teams get discounts based on their number of players Customers purchases are stored as orders in the database Sales representatives mainly work with team customers but can also deal with individual customers.
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
3
Part 1 Step 2: Identifying Entities Using text analysis on the given scenario identify any potential entities that will have to be represented in a relational database system. Entities are usually the nouns in the scenario description however not every noun becomes an entity so think carefully but remember you are identifying potential entities not creating a definitive list.
Solution: The Oracle Baseball League store serves the entire surrounding community selling baseball kit. The OBL has two types of customer, there are individuals who purchase items like balls, cleats, gloves, shirts, screen printed t-shirts, and shorts. Additionally customers can represent a team when they purchase uniforms and equipment on behalf of the team. Teams and individual customers are free to purchase any item from the inventory list, but teams get a discount on the list price depending on the number of players. When a customer places an order we record the order items for that order in our database. OBL has a team of three sales representatives that officially only call on teams but have been known to handle individual customer complaints.
Potential Entities: •
Customer
•
Team
•
Sales Representative
•
Order
•
Item
•
Inventory List
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
4
Part 1 Step 3: Identifying Attributes To gain a better understanding of the system a meeting was held with the manager of the Oracle Baseball league store. The following transcript details the conversation that took place: Meeting Transcript Interviewer: In the information you provided you state that you have two types of customer, individual and team; what information do you store about customers and how do you distinguish between the different types? Manager: For all customers the store tracks their full name, address, phone number, email and what, if any team they belong to. The current balance owed by the customer is also tracked in our system Interviewer: You say that customers can order any item from the inventory list. What types of items can they purchase? Manager: Individual customers can purchase items like balls, cleats, gloves, shirts, screen printed t-shirts, and shorts. Additionally, teams can order an entire set of uniforms as well as balls, warm-up and team t-shirts and may get a discount on the list price depending on the number of players within that team. When a team purchases items from the store, we require that the registered customer for that team places the order for the named team. Interviewer: Do you have any specific information about the items that you sell that you want recorded on the system? Manager: Customers never purchase items sight-unseen, so there is always a description and price available. The tracking of inventory items is a very important part of the business, as well as the description and price we currently track the item name, color (if applicable), size (if applicable) and item category. There are three item categories that we use: clothing, equipment, and miscellaneous. For our inventory we also track the wholesale cost of the unit as well as the number of units on hand; when no units are on hand a zero is recorded in the system. Interviewer: How do you record what items have been ordered by your customers? Manager: When a customer places an order, we record the following purchase details, the date, items purchased, item size, color, number of units, and the price for each unit. We would also like to store the total order price for all of the items ordered. Interviewer: You have three sales representatives in the company, what is their role? Manager: Every team customer is assigned their own sales representative as the salespeople work on commission, two salespersons would never be allowed to call on the same customer. Although sales reps normally only call on teams, they have been known to handle individual customer complaints. Interviewer: How do you record the details of the sales reps on your system? Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
5 Manager: For each of the three sales reps the store keeps track of their name, address, phone, email, total commission and commission rate. Using text analysis on the given scenario identify any potential attributes that will be used to store information about the previously identified entities. Attributes are normally found by identifying nouns that describe other nouns (our entities).
Solution : Potential Entities and Attributes •
Customer name address phone number email team they belong to current balance
•
Team discount number of players name team customer
•
Sales Representative name address phone number email total commission commission rate
•
Order date items purchased item size color number of units price total price
•
Item description price name color size
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
6 category •
Inventory List cost of the unit units on hand
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
7
Part 1 Step 4 : Identifying Mandatory/Optional Attributes Using the interview transcript and the already identified entities and attributes from the previous lesson mark which ones you think will be mandatory (*) or optional (o). Remember optional attributes can be blank unlike mandatory ones which must have a value. Check if any of the attributes could be described as volatile, if they can then change them to a non-volatile equivalent.
Solution : Potential Entities and Attributes including Optionality •
Customer * name * address * phone number * email o team they belong to * current balance
•
Team o discount * number of players * name * team customer
•
Sales Representative * name * address * phone number * email * total commission * commission rate
•
Order * date * items purchased * item size * color * number of units * price * total order price
•
Item * description * price * name o color o size
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
8 * category •
Inventory List o cost of the unit o units on hand
No volatile attributes are present with the suggested solution.
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
9
Part 1 Step 5: Using Barker Notation Use Barker notation to display the information represented by the entities and attributes identified. To help make each softbox clearer to understand place the attributes in their logical order as well as placing all of the mandatory attributes before the optional ones.
Solution : CUSTOMER * Name * Address * Phone number * Email * Current balance o Team they belong to
•
Customer * name * address * phone number * email o team they belong to * current balance
•
Team o discount * number of players * name
TEAM * Name * Number of players o Discount
•
Sales Representative * name * address * phone number * email * total commission * commission rate
SALES REPRESENTATIVE * Name * Address * Phone number * Email * Total commission * Commission rate
•
Order * date * items purchased * item size * color * number of units * price * total order price
ORDER * Date * Items purchased * Item size * Color * Price * Number of units * Total order price
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
10 •
Item * description * price * name o color o size * category
•
Inventory List * cost of the unit * units on hand
ITEM * Name * Description * Price * Category o Color o Size
INVENTORY LIST * Cost of the unit * Units on hand
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
11
Part 1 Step 6: Identifying Unique Identifiers (UIDs) Using the Barker notation softboxes, created in the previous exercise, identify any potential candidate keys (attributes that can be used to uniquely identify an entity) that exist within the entities. • •
If you can identify any information bearing (natural) UID’s then place a # sign as that attribute’s optionality and move it to the top of the attribute list. If there is more than one attribute (composite) that combined makes the entity unique then place the # sign before each of the attribute names and move all of them to the top of the list.
Solution : CUSTOMER # Email * Name * Address * Phone number * Current balance o Team they belong to TEAM * Name * Number of players o Discount SALES REPRESENTATIVE # Email * Name * Address * Phone number * Commission rate
ORDER * Date * Items purchased * Item size * Color * Price * Number of units * Total Order Price ITEM * Name * Description * Price * Category o Color o Size
INVENTORY LIST * Cost of the unit * Units on hand
There are only two potential candidate UID’s in this example(CUSTOMER, SALES REPRESENTATIVE) as no combination of any of the other attributes could guarantee a unique value.
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
12
Part 1 Step 7: Identifying Artificial Unique Identifiers (UIDs) Using the Barker notation softboxes created previously identify and assign the unique identifiers that will be used to uniquely identify the given entities. • •
If no information bearing identifier has been assigned then assign an artificial one. Normally we use id or number as the name for the artificial identifier. If an information bearing identifier has been identified then you need to choose if you will use that as the primary unique identifier or as a secondary one. If you are going to use it as a secondary identifier then create an artificial identifier and enclose the information bearing identifier # sign in brackets.
Solution : CUSTOMER # Number (#) Email * Name * Address * Phone number * Current balance o Team they belong to
TEAM # Id * Name * Number of players o Discount
SALES REPRESENTATIVE # Id (#) Email * Name * Address * Phone number * Commission rate
ORDER # Id * Date * Items purchased * Item size * Color * Price * Number of units * Total Order Price
ITEM # Number * Name * Description * Price * Category o Color o Size
INVENTORY LIST # Id * Cost of the unit * Units on hand
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
13
Part 1 Step 8: Recognize Examples of Relationships Relationships represent an association between two or more entities. Using the business rules, identified previously in this project and listed below, identify potential associations between your entities. Business Rules • Customers can be either individual or represent a team • Customers can purchase items from the inventory list • Teams get discounts based on their number of players • Customers purchases are stored as orders in the database • Sales representatives mainly work with team customers but can also deal with individual customers.
CUSTOMER # Number (#) Email * Name * Address * Phone number * Current balance o Team they belong to
TEAM # Id * Name * Number of players o Discount SALES REPRESENTATIVE # Id (#) Email * Name * Address * Phone number * Commission rate
ORDER # Id * Date * Items purchased * Item size * Color * Price * Number of units * Total Order Price ITEM # Number * Name * Description * Price * Category o Color o Size
INVENTORY LIST # Id * Cost of the unit * Units on hand
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
14
Solution :
CUSTOMER # Number (#) Email * Name * Address * Phone number * Current balance o Team they belong to
TEAM #Id * Name * Number of players o Discount
CUSTOMER # Number (#) Email * Name * Address * Phone number * Current balance o Team they belong to
SALES REPRESENTATIVE # Id (#) Email * Name * Address * Phone number * Commission rate
CUSTOMER # Number (#) Email * Name * Address * Phone number * Current balance o Team they belong to
ORDER # Id * Date * Items purchased * Item size * Color * Price * Number of units * Total Order Price
ORDER # Id * Date * Items purchased * Item size * Color * Price * Number of units * Total Order Price
ITEM # Number * Name * Description * Price * Category o Color o Size
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
15
ITEM # Number * Name * Description * Price * Category o Color o Size
INVENTORY LIST #Id * Cost of the unit * Units on hand
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
16
Part 1 Step 9: Identify the Optionality of Relationships Relationships are bi directional and must either be mandatory or optional.
Exercise 1 : For the relationships identified in the previous exercise write the optionality of the relationship between both entities (remember the relationship exists in both directions), the first one has been completed for you.
CUSTOMER # Number (#) Email * Name * Address * Phone number * Current balance o Team they belong to
Left to Right • Right to Left •
TEAM #Id * Name * Number of players o Discount
Each CUSTOMER may represent a TEAM Each TEAM must be represented by a CUSTOMER
Solution : CUSTOMER # Number (#) Email * Name * Address * Phone number * Current balance o Team they belong to Left to Right • Right to Left •
SALES REPRESENTATIVE # Id (#) Email * Name * Address * Phone number * Commission rate
Each CUSTOMER may be assigned a SALES REPRESENTATIVE Each SALES REPRESENTATIVE must be assigned to one or more CUSTOMERs
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
17
CUSTOMER # Number (#) Email * Name * Address * Phone number * Current balance o Team they belong to
Left to Right • Right to Left •
Each CUSTOMER may place one or more ORDERs Each ORDER must be placed by a CUSTOMER
ORDER # Id * Date * Items purchased * Item size * Color * Price * Number of units * Total Order Price
Left to Right • Right to Left • ITEM # Number * Name * Description * Price * Category o Color o Size
ORDER # Id * Date * Items purchased * Item size * Color * Price * Number of units * Total Order Price
ITEM # Number * Name * Description * Price * Category o Color o Size
Each ORDER must include one or more ITEMs Each ITEM may be part of one or more ORDERs INVENTORY LIST #Id * Cost of the unit * Units on hand
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
18 Left to Right • Right to Left •
Each ITEM must be on an INVENTORY LIST Each INVENTORY LIST may include one or more ITEMs
Exercise 2 : Relationship lines represent a bi-directional relationship between two entities therefore you should think of it as two separate lines that originate from their entity and meet in the middle. Using the descriptions that you wrote in part 1 complete the relationship lines to represent the optionality of each relationship, the first one has been completed for you. CUSTOMER # Number (#) Email * Name * Address * Phone number * Current balance o Team they belong to
TEAM #Id * Name * Number of players o Discount
Solution : CUSTOMER # Number (#) Email * Name * Address * Phone number * Current balance o Team they belong to
CUSTOMER # Number (#) Email * Name * Address * Phone number * Current balance o Team they belong to
SALES REPRESENTATIVE # Id (#) Email * Name * Address * Phone number * Commission rate
ORDER # Id * Date * Items purchased * Item size * Color * Price * Number of units * Total Order Price
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
19 ORDER # Id * Date * Items purchased * Item size * Color * Price * Number of units * Total Order Price
ITEM # Number * Name * Description * Price * Category o Color o Size
ITEM # Number * Name * Description * Price * Category o Color o Size
INVENTORY LIST #Id * Cost of the unit * Units on hand
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
20
Part 1 Step 10: Identify the Cardinality of Relationships For the relationships identified in the previous exercise determine the cardinality by analyzing the description given for each relationship. If the description uses “a” or “an” then it should be a 1 relation (single toe) however if it says “many” or “one or more” then it should be a M relation (crow’s foot). Add the cardinality to the following entities by adding any required crow’s foot notation.
Solution : CUSTOMER # Number (#) Email * Name * Address * Phone number * Current balance o Team they belong to
• •
Each CUSTOMER may represent a TEAM Each TEAM must be represented by a CUSTOMER
CUSTOMER # Number (#) Email * Name * Address * Phone number * Current balance o Team they belong to
• •
TEAM #Id * Name * Number of players o Discount
SALES REPRESENTATIVE # Id (#) Email * Name * Address * Phone number * Commission rate
Each CUSTOMER may be assigned a SALES REPRESENTATIVE Each SALES REPRESENTATIVE must be assigned to one or more CUSTOMERs
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
21
CUSTOMER # Number (#) Email * Name * Address * Phone number * Current balance o Team they belong to
ORDER # Id * Date * Items purchased * Item size * Color * Price * Number of units * Total Order Price
Each CUSTOMER may place one or more ORDERs Each ORDER must be placed by a CUSTOMER
• •
ORDER # Id * Date * Items purchased * Item size * Color * Price * Number of units * Total Order Price
ITEM # Number * Name * Description * Price * Category o Color o Size
Each ORDER must include one or more ITEMs Each ITEM may be part of one or more ORDERs
• •
ITEM # Number * Name * Description * Price * Category o Color o Size
• •
INVENTORY LIST #Id * Cost of the unit * Units on hand
Each ITEM must be on an INVENTORY LIST Each INVENTORY LIST may include one or more ITEMs
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
22
Part 1 Step 11: Using a Relationship Matrix A relationship matrix shows if and how each row entity on the left side of the matrix is related to each column entity shown across the top of the matrix. • If a row entity is related to a column entity, the name of that relationship is shown in the intersection box. • If a row entity is not related to a column entity, the intersection box is empty. • Recursive relationships can be represented by placing the name of that relationship on the diagonal.
Exercise 1: Create a relationship matrix to validate the relationships that you have already identified throughout this project. Use the descriptions of your entities to create the names of the relationship in the intersection box.
Solution : CUSTOMER CUSTOMER
TEAM represent
TEAM
represented by
SALES REPRESENTA TIVE ORDER
assigned to
ITEM
part of
INVENTORY LIST
include
placed by
SALES REPRESENTATIVE assigned
ORDER
ITEM
place
INVENTORY LIST
include on
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
23
Exercise 2: Use the names that you added to the intersection boxes of the relationship matrix add the correct labels to the appropriate relationships of your ERDs.
Solution :
CUSTOMER # Number (#) Email * Name * Address * Phone number * Current balance o Team they belong to
CUSTOMER # Number (#) Email * Name * Address * Phone number * Current balance o Team they belong to
CUSTOMER # Number (#) Email * Name * Address * Phone number * Current balance o Team they belong to
represent represented by
assigned assigned to
place placed by
TEAM #Id * Name * Number of players o Discount
SALES REPRESENTATIVE # Id (#) Email * Name * Address * Phone number * Commission rate
ORDER # Id * Date * Items purchased * Item size * Color * Price * Number of units * Total Order Price
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
24
ORDER # Id * Date * Items purchased * Item size * Color * Price * Number of units * Total Order Price
ITEM # Number * Name * Description * Price * Category o Color o Size
include part of
on include
ITEM # Number * Name * Description * Price * Category o Color o Size
INVENTORY LIST #Id * Cost of the unit * Units on hand
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
25
Part 1 Step 12: Entity Relationship Modeling An Entity Relationship Diagram (ERD) allows you to graphical represent the system information and has the following four goals: • Capture all required information. • Ensure that information appears only once. • Model no information that is derivable from other information that is already modeled. • Locate information in a predictable, logical place. As you have already Identified the entities, their attributes and the relationships between the entities you can now begin to construct the finalised ERD that will show how the system is linked together. Using the information you have already gathered throughout this project construct an ERD that adheres to the four goals specified above. Construct your ERD following diagramming convention.
(See solution next page)
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
CUSTOMER # Number (#) Email * Name * Address * Phone number * Current balance o Team they belong to
represent represented by
assigned assigned to
TEAM #Id * Name * Number of players o Discount SALES REPRESENTATIVE # Id (#) Email * Name * Address * Phone number * Commission rate
place
placed by ORDER # Id * Date * Items purchased * Item size * Color * Price * Number of units * Total Order Price
include part of
ITEM # Number * Name * Description * Price * Category o Color o Size
on include
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
INVENTORY LIST #Id * Cost of the unit * Units on hand
Part 1 Step 13: Resolving Many to Many Relationships After completing the initial ERD it was shown to the client to confirm that the business was being correctly reflected in the design. The following is a transcript from that meeting: Meeting Transcript Interviewer: Do you see any major problems with the design of your new system? Manager: It looks like you have covered everything does that mean that it is nearly finished? Interviewer: Not quite we still have quite a lot to do before it is a working database system. I have a few more questions for you just to clear some things up before we finalize the design. Manager: No problem. Interviewer: A customer can represent a team; does each team have a single named representative or can many people place orders on the team’s behalf? Manager: There can only be one person that is the official representative of the team. Interviewer: Can the same person represent multiple teams? Manager: No they can only be assigned to a single team at a time. Interviewer: At one of our earlier meetings you said that each team representative is assigned a single sales representative. How are the sales representatives assigned to their teams? Manager: One of the sales representatives is assigned the role of supervisor and they are responsible for assigning individual teams to all the sales representatives including themselves. Interviewer: You also said that sales representatives sometime handle individual customer complaints? Manager: Yes if a customer has an issue with their order a sales representative will help them out. Interviewer: When a customer places an order can that order be transferred to someone else? Manager: No the order is between the company and the person who placed the order; that can never change. Interviewer: Can a customer only register a single address as a delivery destination or can they have multiples? Manager: We allow our customers to register multiple addresses with us so that they can have their orders delivered to the location that suits them best. An address can only be allocated to a single customer. Interviewer: If you store multiple addresses for the customers do you also store multiple addresses for the sales representatives?
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
28 Manager: No we only store the current address information of our staff members, if they move address we update the information there is no need to track their address history. Interviewer: Going back to customers, can a customer be both a team representative as well as an individual customer? Manager: Absolutely, but they will have two separate accounts with purchases on one not affecting purchases on the other. Interviewer: Teams get a discount based on the number of players, how does that work? Manager: A team needs a minimum of 9 players to receive a discount. If a team has a minimum nine players on their roster they get a 5% discount, if they have a minimum of 25 players they get a 10% discount and for 40 players or more they get a 20% discount. The appropriate discount is allocated to the team. Interviewer: Is there anything that you are thinking about introducing that might affect the system either now or in the future? Manager: We are looking at introducing a loyalty card scheme for the individual customers. This would be an optional scheme that would allow loyalty card members to attend special sale evenings where they can purchase items at a reduced price. Interviewer: Would you want to record what each loyalty card scheme member purchases at these events. Manager: No the restriction will be that only customers who have loyalty cards will be allowed in the store. We don’t need to know what items they purchased. Interviewer: So you would like us to track the price of items over time so that you can change them? Manager: Yes that would make life so much easier for us. In relational databases you can have the following three types of relationship that represent the information requirements and the rules of the business.: •
Many-to-one (M:1) or one-to-many (1:M)
•
Many-to-many (M:M)
•
One-to-one (1:1)
If you have any many to many (M:M) relationships in your ERD then you will need to resolve those before going any further. Remember you resolve a M:M relationship by: •
Delete the M:M relationship.
•
Create a new entity (intersection).
•
Create two identifying relationships with the many side on the new intersection entity.
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
29 •
Identify and create additional attributes in the intersection entity if required. Think about how you would store how many of each item and how many items were actually shipped to the customer.
•
Evaluate whether the two existing identifying relationships constitute a UID for the intersection entity or whether an artificial UID has to be created for the new entity.
•
Add relationship labels to the new identifying relationships.
Suggested starting point:
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
30
Solution explanation The M:M relationship between ORDER and ITEM has to be resolved by the use of an intersection entity. The intersection entity has been named ORDERED ITEM and holds the information about the quantity of items ordered and how many of them have been shipped. This gives us the option to ship the items in different deliveries. The UID for ORDERED ITEM uses a barred relationship to use the UID from both ORDER and ITEM as the UID.
Solution:
ORDER # Id * Date * Time * Number of units
include
ORDERED ITEM * Quantity ordered * Quantity Shipped part of
represent
represented by
ITEM # Number * Name * Description * Price * Category o Color o Size
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
31
Part 1 Step 14: Identify and Illustrate Non-Transferable Relationships This step uses the additional scenario and meeting transcript that was provided in the previous exercise. A non-transferable relationship cannot be moved between instances of the entities it connects and has to be mandatory. Read through the meeting transcript and identify any non-transferable relationships that might exist. Draw any non-transferable relationships on the ERD for the system.
Suggested starting point:
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
32
Solution explanation: In the scenario the following information was conveyed: Interviewer: When a customer places an order can that order be transferred to someone else? Manager: No the order is between the company and the person who placed the order; that can never change. This tells us that relationship between CUSTOMER and ORDER is non-transferable and this is shown on the ERD by use of the diamond notation.
Solution: CUSTOMER # Number (#) Email * Name * Address * Phone number * Current balance o Team they belong to
place
placed by ORDER # Id * Date * Time * Items purchased * Price * Number of units * Total Order Price o Item size o Color Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
33
Part 1 Step 15: Identify and Draw Supertype and Subtype Entities This step uses the additional scenario and meeting transcript that was provided in previous exercises. Subtype/supertypes allow you to represent entities that have common attributes as a group. • Each subtype is a specialization of a supertype and therefore must be enclosed within an entity. • The common attributes and relationships for all subtypes must be listed only in the supertype, but they are inherited in every subtype. • A subtype can and would generally have attributes and relationships of its own. • There can never be just one subtype; another subtype should be created to contain the rest. Using the given scenario draw any supertype and subtype groups that have been identified on your ERD.
Suggested starting point:
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
34
Solution explanation: In the scenario the following information was conveyed: Interviewer: Going back to customers, can a customer be both a team representative as well as an individual customer? Manager: Absolutely, but they will have two separate accounts with purchases on one not affecting purchases on the other. Interviewer: Is there anything that you are thinking about introducing that might affect the system either now or in the future? Manager: We are looking at introducing a loyalty card scheme for the individual customers. This would be an optional scheme that would allow loyalty card members to attend special sale evenings where they can purchase items at a reduced price. This information reinforces that a customer can be one of two unique types. Because only individual customers receive a loyalty card and team customers represent a team that gives us a unique set of attributes for each. We can then store common attributes at the supertype level and unique attributes at the subtype level.
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
CUSTOMER # Number TEAM REPRESENTATIVE (#) Email * Team they belong to * Name * Address * Phone number * Current balance
represent represented by
assigned
INDIVIDUAL (o) Loyalty card number
assigned to
TEAM # Id * Name * Number of players o Discount SALES REPRESENTATIVE # Id (#) Email * Name * Address * Phone number * Commission rate
place
INVENTORY LIST # Id * Cost of the unit * Units on hand include
on
placed by ORDER # Id * Date * Time * Items purchased * Price * Number of units * Total Order Price o Item size o Color
include part of
ORDERED ITEM * Quantity ordered * Quantity Shipped
represent represented by
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
ITEM # Number * Name * Description * Price * Category o Color o Size
Part 1 Step 16: Identify Hierarchical, Recursive and Arc Relationships This step uses the additional scenario and meeting transcript that was provided in the previous lessons.
Exercise 1 - Recursive relationships A recursive relationship allows you to represent a relationship that an entity has with itself. A recursive relationship requires that one attribute in the entity be dependent on another attribute in the entity. Ensure that both attributes are present in the entity before you draw the recursive relationship. Using the given scenario identify and draw any recursive relationships that you identified on your ERD.
Solution explanation: One of the sales representatives supervises the allocation of teams to the others so a recursive relationship is used here.
Solution:
SALES REPRESENTATIVE # Id (#) Email * Name * Address * Phone number * Commission rate
supervise
supervised by
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
37
Exercise 2 - Arc relationships An arc is an exclusive relationship group, which is defined such that only one of the relationships can exist for any instance of an entity. All relations included in an arc should belong to the same entity and should have the same cardinality. In the previous exercise you created a supertype/subtype for customer that showed how a customer can be either an individual or team representative. One other way of showing this would have been using an arc. As a separate ERD that only details the customer information show how customer could have been represented using an arc.
Solution explanation: A supertype/subtype relationship shows an exclusive relationship as the subtypes have to be mutually exclusive of each other. This can be represented by using an arc and it really comes down to personal choice which way you want to show them on the ERD.
Solution: CUSTOMER # Number (#) Email * Name * Address * Phone number * Current balance
TEAM REPRESENTATIVE * Team they belong to
INDIVIDUAL o Loyalty card number
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
38
Part 1 Step 17: Keep Track of Data that Changes over Time This step uses the additional scenario and meeting transcript that was provided in previous exercises. Information that exists in a single entity can only ever store a single (or current) value. If we were to change that value all historical information would be lost. To store both current and historical or alternative data we may have to add entities and relationships to the model to accommodate this extra information. These are some questions that you can ask that may help you identify the need for historical data: •
Is an audit trail required?
•
Can attribute values change over time?
•
Can relationships change over time?
•
Do you need to query older data?
Using the given scenario add any additional entities and their corresponding relationships that are required to store any data that can change over time or where alternatives are required.
Solution explanation: In the scenario the following information was conveyed: Interviewer: Can a customer only register a single address as a delivery destination or can they have multiples? Manager: We allow our customers to register multiple addresses with us so that they can have their orders delivered to the location that suits them best. An address can only be allocated to a single customer. Interviewer: If you store multiple addresses for the customers do you also store multiple addresses for the sales representatives? Manager: No we only store the current address information of our staff members, if they move address we update the information there is no need to track their address history. This tells us that we have to take address out of the CUSTOMER entity and create a 1:M relationship allowing the customer to receive deliveries to multiple addresses. The field for address line 2 is marked as optional as that is not always required when storing an address. The manager also states that although an address field is stored for the SLAES REPRESENTATIVE there is no business need as this stage to do anything with that attribute. The scenario also gives us this information: Interviewer: Is there anything that you are thinking about introducing that might affect the system either now or in the future? Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
39 Manager: We are looking at introducing a loyalty card scheme for the individual customers. This would be an optional scheme that would allow loyalty card members to attend special sale evenings where they can purchase items at a reduced price. Interviewer: Would you want to record what each loyalty card scheme member purchases at these events. Manager: No the restriction will be that only customers who have loyalty cards will be allowed in the store. We don’t need to know what items they purchased. Interviewer: So you would like us to track the price of items over time so that you can change them? Manager: Yes that would make life so much easier for us.
As the price of items can change we need to remove price from ITEM and place it in its own entity called PRICE HISTORY. The entity named PRICE HISTORY records the starting date and time when the price was changed. The end attributes are optional as they would need to store the current price which has not ended yet. It has a composite UID of both start date and start time that combined with the barred relationship to ITEM creates a unique value. It uses the barred relationship to take the UID of ITEM to create a relationship between ITEM and the PRICE HISTORY.
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Solution:
CUSTOMER # Number (#) Email * Name * Phone number * Current balance
TEAM REPRESENTATIVE * Team they belong to
represent represented by
get deliveries to
placed by ORDER # Id * Date * Time * Items purchased * Price * Number of units * Total Order Price o Item size o Color
INVENTORY LIST # Id * Cost of the unit * Units on hand include
assigned
INDIVIDUAL (o) Loyalty card number
place
TEAM # Id * Name * Number of players o Discount
assigned to
assigned to
CUSTOMER ADDRESS # Id * Address
include part of
SALES REPRESENTATIVE # Id (#) Email * Name * Address * Phone number * Commission rate
supervise
Supervised by
ORDERED ITEM * Quantity ordered * Quantity Shipped
PRICE HISTORY # Start date # Start time * Price o End date o End time represent
on
represent represented by
ITEM # Number * Name * Description * Category o Color o Size
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Priced using
Part 1 Step 18: Using Normalization to Validate Data - Convert the Data into Unnormalized Form Before starting the process of normalization you need to convert your data into Unnormalized Form (UNF). •
Unnormalized Form (UNF) -
Remove any calculated fields that can be derived from other attributes.
-
Ensure that every entity has a unique identifier.
-
Remove duplicate data where information is being stored in multiple entities.
Take the data that is currently represented in the ERD and convert the data into Unnormalized Form so that the data can be said to be in UNF.
Solution explanation: The calculated field Total order price was removed from the ORDER entity as it can be calculated through the item price. The duplicate attributes of Color and Size were removed from ORDER and left in ITEM. The duplicate Items purchased (ORDER) and Quantity ordered (ORDERED ITEM) attribute was removed from ORDER and left in ORDERED ITEM. The duplicate attribute of Price was removed from ORDER and left in PRICE HISTORY.
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Solution: CUSTOMER # Number (#) Email * Name * Phone number * Current balance
TEAM REPRESENTATIVE * Team they belong to
represent represented by
INVENTORY LIST # Id * Cost of the unit * Units on hand include
assigned
INDIVIDUAL (o) Loyalty card number
get deliveries to
place
TEAM # Id * Name * Number of players o Discount
assigned to
assigned to
CUSTOMER ADDRESS # Id * Address
SALES REPRESENTATIVE # Id (#) Email * Name * Address * Phone number * Commission rate
supervise
Supervised by
include part of
ORDERED ITEM * Quantity ordered * Quantity Shipped
represent
on
placed by ORDER # Id * Date * Time * Number of units
PRICE HISTORY # Start date # Start time * Price o End date o End time
represent represented by
ITEM # Number * Name * Description * Category o Color o Size
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Priced using
Part 1 Step 19: Using Normalization to Validate Data - First Normal Form Normalization is a relational database concept, but its principles apply to data modeling. •
First Normal Form (1NF) -
The data is atomic (All attributes must be single-valued).
-
The entries in a column are of the same type.
-
There can be no duplicated rows in the table meaning that the table has a group of columns that uniquely identifies the row.
Take the un-normalized data that is currently represented in the ERD and apply the principles of First Normal Form so that the data can be said to be in 1NF.
Solution explanation: All attributes that can contain more than a single value have been split so that they are all single valued. These were the name and address attributes across the entities.
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Solution:
CUSTOMER # Number (#) Email * First name * Last name * Phone number * Current balance
TEAM REPRESENTATIVE * Team they belong to
represent represented by
assigned
INDIVIDUAL (o) Loyalty card number
get deliveries to
place
assigned to placed by ORDER # Id * Date * Time * Number of units
assigned to
CUSTOMER ADDRESS # Id * Address Line 1 o Address Line 2 * City * Postal code
include part of
TEAM # Id * Name * Number of players o Discount
INVENTORY LIST # Id * Cost of the unit * Units on hand
SALES REPRESENTATIVE # Id (#) Email * First name * Last name * Address Line 1 o Address Line 2 * City * Postal code * Phone number * Commission rate
include
supervise
represent
on
Supervised by
ORDERED ITEM * Quantity ordered * Quantity Shipped
PRICE HISTORY # Start date # Start time * Price o End date o End time
represent represented by
ITEM # Number * Name * Description * Category o Color o Size
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Priced using
Part 1 Step 20: Using Normalization to Validate Data - Second Normal Form Normalization is a relational database concept, but its principles apply to data modeling. •
Second Normal Form (2NF) -
The data meets the requirements for 1NF.
-
Requires that any non-UID attribute be dependent on the entire UID
-
If the data is not directly dependent on the entire UID, then it needs to be moved to another table.
Take the data that is currently represented in the ERD in 1NF and apply the principles of Second Normal Form so that the data can be said to be in 2NF.
Solution explanation This scenario has no composite UIDs that require being resolved to 2NF therefore no changes are required to the ERD.
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
46
Part 1 Step 21: Using Normalization to Validate Data - Third Normal Form Normalization is a relational database concept, but its principles apply to data modeling. •
Third Normal Form (3NF) -
It met all database requirements for both 1NF and 2NF.
-
No non-UID attribute can be dependent on another non-UID attribute.
-
Each column must depend directly on the UID. All attributes that are not dependent on the UID must be removed. For example, attributes that can be derived from data contained in other fields and tables must be eliminated. (All transitive dependencies are eliminated).
Take the data that is currently represented in the ERD in 2NF and apply the principles of Third Normal Form so that the data can be said to be in 3NF.
Solution explanation 1 (pictured below): If you accept the attributes Address Line 1, Address Line 2 and City are dependent on Postal code and are not dependent on the UID for Sales Representative they need to be removed and placed in their own entity. The Postal code field is not unique for each address but represents a group of addresses so a barred relation is created with the Sales Representative. This allows only one address to be stored for each Sales Representative.
Solution explanation 2: If you do not accept that the attributes Address Line 1, Address Line 2 and City are dependent on Postal code but are indeed dependent on the UID for Sales Representative then no changes have to be made to the diagram and it can be said to already be in third normal form.
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Solution:
CUSTOMER # Number (#) Email * First name * Last name * Phone number * Current balance
TEAM REPRESENTATIVE * Team they belong to
represent represented by
get deliveries to assigned to
assigned to
reside at CUSTOMER ADDRESS # Id * Address line 1 o Address line 2 * City * Postal code
INVENTORY LIST # Id * Cost of the unit * Units on hand include
SALES REPRESENTATIVE # Id (#) Email * First name * Last name * Phone number * Commission rate
assigned
INDIVIDUAL (o) Loyalty card number
place
TEAM # Id * Name * Number of players o Discount
to
supervise
Supervised by
assigned
SALES REP ADDRESS * Address line 1 o Address line 2 * City * Postal code
placed by ORDER # Id * Date * Time * Number of units
include part of
ORDERED ITEM * Quantity ordered * Quantity Shipped
PRICE HISTORY # Start date # Start time * Price o End date o End time
represent represented by
represent
on ITEM # Number * Name * Description * Category o Color o Size
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Priced using