• 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 2 : Logical Design Part 2 Step 1: Apply the Rules of Relationship Mapping to Transform Relationship Exercise 1 : Create Table Mapping Document Data modeling allows you to understand and apply the Oracle naming conventions of a Conceptual Data Model and apply them in the creation of a Physical Data Model. The Physical model will represent the information stored in the ERD in a way that a relational database can be easily produced. Using the ERD that you have produced throughout this project (or the suggested start point in this document) create a table mapping document that will represent the transformation of the Conceptual Data Model terminology to that of the Physical Data Model. Table Mapping instructions 1. The first row of the table diagram contains the table name and the short name. a. The table name is the plural of the entity name. b. The table short name is what will be used to name the foreign key relationships and should be created following the appropriate strategy for the name of the entity. 2. The Key Type column should contain values of "pk" for the primary key, "uk" for the unique key, or "fk" for the foreign key column. The cell is blank if the column is not a part of a key. When mapping foreign key attributes you should remember the following: a. You create the foreign key on the side of the relationship that has a crows foot notation. b. You take the primary key from the related table and add it using the table short name_attribute name as the identifier. c. There is no specific order for the foreign keys to be identified by, but we number their type to show that there is more than one foreign key. d. On a 1:1 relationship the foreign key can be added to the most appropriate table. 3. The Optionality column must contain an asterisk (*) if the column is mandatory and a lowercase "o" if it is optional. 4. The third column is for the column name this should match the singular attribute name but with spaces replaced with underscores. The name of the column should not include the entity/table name because columns are qualified with the table name 5. Remember that you have to follow the naming conventions at all stages and you have to be consistent in your naming approach. 6. Be careful not to try to assign keywords as table/column names as this will cause you problems when you try to create the Physical model.

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 The first table (customers) has been completed for you as an example. A single table implementation has been used to represent the super/subtypes shown in the CUSTOMER entity. Table Name customers Key Type pk uk

uk fk1 fk2

Table Short Name ctr Optionality * * * * * * o o o

Column Name ctr_number email first_name last_name phone_number current_balance loyalty_card_number tem_id sre_id

Using the given example and the following template to complete a separate table for every entity in your Conceptual Data Model. Table Name

Table Short Name

Key Type

Optionality

Column Name

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

Suggested starting point

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

5

Exercise 2 : Modifying Table Mapping Document Data modeling allows you to understand and apply the Oracle naming conventions of a Conceptual Data Model and apply them in the creation of a Physical Data Model. The Physical model will represent the information stored in the ERD in a way that a relational database can be easily produced. Using the table mapping document that you created in part 1 of this exercise complete the tables to also show appropriate data types and sizes. Table Mapping instructions 1. The provided suggested starting point in this document shows you the relationships between the tables with sample data for each field. 2. The colour coding shows you where the primary key/foreign key relationships occur. 3. The columns that hold the foreign key values need to be of the same data type (and preferably size) in order to hold that data that will forge the relationship. 4. The data provided is only a sample and the content shouldn’t be taken as a maximum size. 5. For each field calculate the maximum size that you think is the most appropriate for the type of information that will be stored there. The first table (customers) has been completed for you as an example. A single table implementation has been used to represent the super/subtypes shown in the CUSTOMER entity. Table Name

Table Short Name

customers

ctr

Key Type

Optionality

Column Name

pk

*

ctr_number

VARCHAR2

6

uk

*

email

VARCHAR2

50

*

first_name

VARCHAR2

20

*

last_name

VARCHAR2

30

*

phone_number

VARCHAR2

11

*

current_balance

NUMBER

6,2

uk

o

loyalty_card_number

VARCHAR2

6

fk1

o

tem_id

VARCHAR2

4

fk2

o

sre_id

VARCHAR2

4

Data type

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

Using the given example and the following template complete a separate table for every entity in your Conceptual Data Model that will show the data type and size (if appropriate) for each attribute.. Table Name

Table Short Name

Key Type

Optionality

Column Name

Data type

size

Suggested starting point: Sample Table Data - The following tables represent the first row of data in each table you should place the data types and sizes in the appropriate table that you created in Task 1. customers ctr_number c00001

ctr first_ name

email bob.thornbe rry@heatma il.com

last_ name

Robert

Phone_ number

Thornberry

current_ balance

01234567 898

loyalty_c ard_ number

150.00

tem_i d

sre_i d

t001

sr00 1

customers_addresses id

address_line_1 ca0101

83 Barrhill Drive

address_line_2

city Liverpool

postal_code

ctr_number

Lp79HJK

c00001

teams id

name t001

number_of_players Rockets

25

discount 10

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

sales_representatives id

email

sr01

first_name

last_name

phone_number

commission_rate

supervisor_id

Charles

Raymond

134598761

5

sr01

[email protected]

sales_representatives_address id

address_line_1 sr01

address_line_2

12 Cherry Lane

orders

Denton

odr date

id

17-Jun-2017

quantity_ordered

DT48211

number_of_units ctr_number 08:32:30

oim quantity_shipped

5

postal_code Detroit

time

or0101250 ordered_items

city

10

c00001

odr_id

5

itm_number or0101250

Im01101025

Items

itm Itm_number name Im01101025

description gloves

Inventory_list

category

catcher mitt

clothing

color brown

ilt unit_cost

id

size

ilt_id m

il010230124

units_on_hand

il010230124

2.50

100

price_history start_date 17-Jun-2017

start_time 09:00:00

price

end_date 4.99

end_time

itm_number Im01101025

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

Part 2 Step 2: Oracle SQL Developer Data Modeler Creating a Logical Data Model Oracle SQL Developer Data Modeler allows you to create Entities, attributes, and UIDs with correct optionality and cardinality, Supertype and subtype entities as well as Arcs, hierarchical, barred, and recursive relationships. 1. The first step in building a logical model (ERD) in Oracle SQL Developer Data Modeler is to click the Logical tab. a. If you do not see the Logical tab, then perform the following steps: b. Right-click the Logical Model in the browser. c. Select Show. 2. Create entities. 3. Create UIDs for each entity. a. Click the entity for which you want to define the UID. b. With Attributes selected in the left navigator of the Entity Properties window, select the attribute that you want to assign as the UID. c. Select the Primary UID check box. 4. Create mandatory or optional attributes for each entity (at this stage don’t apply any data types to the attributes). Add comments to explain the attribute where necessary. Do not include any foreign key attributes as these are added through the relationships. 5. Create the relationship between the entities. You can create M:N Relation (many-to-many), 1:N Relation (one-to-many), 1:N Relation identifying (one-to-many barred) or 1:1 Relation (one-to-one). a. Set the source entity and target entity for the relationship. b. Name the relationship following the convention of naming the entity on the one side followed by a colon and naming the second entity in the relationship (customer : customer address). c. Use name on source and name on target to add the labels to the relationships. These won’t be displayed on the logical model but will be shown on the physical model. 6. A subtype entity inherits the properties of the supertype. a. To define an entity as a subtype in Oracle SQL Developer Data Modeler you need to ensure that the supertype exists. b. When you create the entity choose the super drop down and identify the supertype entity. 7. Save your work.

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 Suggested task starting point

Recreate your ERD in Oracle SQL Developer Data Modeler. 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

10

Part 2 Step 3: Oracle SQL Developer Data Modeler Engineer the Relational Model from the Logical Data Model Exercise 1: In this step you will take your completed logical data model that was created using Oracle SQL Data Modeler and engineer it to a Relational Model. The relational model forms the basis for the physical database design. Create a relational model in Oracle SQL Data Modeler by following these steps: 8. Update the attributes in your entities to include their data type and size. a. Double click the entity to access the properties. b. Choose attributes from the left menu c. Select each attribute and choose logical from the properties window to activate the data type drop down. d. Choose the appropriate data type (VARCHAR to represent text, there is no VARCHAR2 option). e. Place the size in the size box. f. Any attributes that have been brought in as foreign keys can be renamed in the relational model by double clicking them and setting their preferred abbreviation. g. Any attributes are names using keywords should have a preferred abbreviation assigned. h. You cannot assign unique keys at this stage. i. For any supertype entities set the subtree generation to single table in the subtype property. 9. Click the Engineer to Relational Model icon. 10. Ensure that all values are as expected by comparing the logical and relational information in tree view. Correct the values as necessary. 11. In the general option tab click engineer co-ordinates to lock the diagram in place and avoid overlap. 12. When you are happy click Engineer. 13. Expand the Relational Model node in the object browser to view the objects that you created. 14. Move the node objects to ensure that all information is visible on your diagram. 15. Check the result matches your table map and Save your work.

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

Task Instructions Use the completed table mapping document produced in a previous exercise to assign the data types and sizes. Table Name

Table Short Name

customers

ctr

Key Type

Optionality

Column Name

pk

*

ctr_number

VARCHAR2

6

uk

*

email

VARCHAR2

50

*

first_name

VARCHAR2

20

*

last_name

VARCHAR2

30

*

phone_number

VARCHAR2

11

*

current_balance

NUMBER

6,2

o

loyalty_card_number

VARCHAR2

6

fk1

o

tem_id

VARCHAR2

4

fk2

o

sre_id

VARCHAR2

4

Table Name

Data Type

Size

Table Short Name

customers_addresses cas Data Type

Size

Key Type

Optionality

Column Name

pk

*

id

VARCHAR2

8

*

address_line_1

VARCHAR2

30

o

address_line_2

VARCHAR2

30

*

city

VARCHAR2

15

*

postal_code

VARCHAR2

7

*

ctr_number

VARCHAR2

6

fk

Table Name

Table Short Name

teams

tem

Key Type

Optionality

Column Name

pk

*

id

VARCHAR2

4

*

name

VARCHAR2

20

*

number_of_players

NUMBER

2

o

discount

NUMBER

2

Data Type

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.

12

Table Name

Table Short Name

sales_representatives

sre

Key Type

Optionality

Column Name

pk

*

id

VARCHAR2

4

uk

*

email

VARCHAR2

50

*

first_name

VARCHAR2

20

*

last_name

VARCHAR2

30

*

phone_number

*

commission_rate

VARCHAR2 NUMBER

11 2

*

supervisor_id

VARCHAR2

4

fk

Data Type

Size

Table Name

Table Short Name

sales_representatives_address

sas

Key Type

Optionality

Column Name

Pk, fk

*

id

VARCHAR2

4

*

address_line_1

VARCHAR2

30

o

address_line_2

VARCHAR2

30

*

city

VARCHAR2

15

*

postal_code

VARCHAR2

7

Data Type

Size

Table Name

Table Short Name

orders

odr

Key Type

Optionality

Column Name

pk

*

id

*

odr_date

DATE

*

odr_time

TIMESTAMP

0

*

number_of_units

NUMBER

2

*

ctr_number

VARCHAR2

6

fk

Data Type VARCHAR2

Size 9

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

Table Name

Table Short Name

ordered_items

oim

Key Type

Optionality

Column Name

*

quantity_ordered

NUMBER

3

*

quantity_shipped

NUMBER

3

pk, fk1

*

odr_id

VARCHAR2

9

pk, fk2

*

itm_number

VARCHAR2

10

Table Name

Table Short Name

items

itm

Key Type

Optionality

Column Name

pk

*

itm_number

VARCHAR2

10

*

name

VARCHAR2

20

*

description

VARCHAR2

50

*

category

VARCHAR2

25

o

color

VARCHAR2

15

o

size

CHAR

1

fk

*

ilt_id

VARCHAR2

11

Table Name

Table Short Name

inventory_list

ilt

Key Type

Optionality

Column Name

pk

*

id

* *

Data Type

Data Type

Data Type

Size

Size

Size

VARCHAR2

11

cost

NUMBER

7,2

units

NUMBER

4

Table Name

Table Short Name

price_history

phy

Key Type

Optionality

Column Name

pk

*

start_date

DATE

pk

*

start_time

TIMESTAMP

0

*

price

NUMBER

7,2

o

end_date

DATE

o

end_time

TIMESTAMP

0

*

itm_number

VARCHAR2

10

pk, fk1

Data Type

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.

14

Engineer the Relational Model from the Logical Data Model Exercise 2: In this exercise you will take your completed relational model and create the remaining constraints. 1. Identify the tables that have unique columns that have still to be created. a. Right click the table and choose properties. b. Select Unique Constraints from the menu. c. Click the green cross to add a new unique constraint. d. Use the table short name_column_name_constraint type format to name your constraints. e. Select the column that is to be set as unique and click the arrow to add it to the selected column. f.

Click OK.

2. Identify any tables that have a recursive relationship a. Right click the table and choose properties. b. Select Foreign Keys from the menu. c. Click the green cross to add a new foreign key. d. Select the referenced column (pk) and the column that is to be used to create the link. e. Click OK. 3. Check that all names satisfy naming conventions. a. Go to each table and choose the Foreign Keys option in the properties dialog. b. Click on the name and choose the edit icon to shorten the name c. Click ok. 4. Save your work.

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

Task Instructions Use the completed table mapping document produced in a previous exercise to assign the constraints. Table Name

Table Short Name

customers

ctr

Key Type

Optionality

Column Name

pk

*

ctr_number

VARCHAR2

6

uk

*

email

VARCHAR2

50

*

first_name

VARCHAR2

20

*

last_name

VARCHAR2

30

*

phone_number

VARCHAR2

11

*

current_balance

NUMBER

6,2

uk

o

loyalty_card_number

VARCHAR2

6

fk1

o

tem_id

VARCHAR2

4

fk2

o

sre_id

VARCHAR2

4

Table Name

Data Type

Size

Table Short Name

customers_addresses cas Data Type

Size

Key Type

Optionality

Column Name

pk

*

id

VARCHAR2

8

*

address_line_1

VARCHAR2

30

o

address_line_2

VARCHAR2

30

*

city

VARCHAR2

15

*

postal_code

VARCHAR2

7

*

ctr_number

VARCHAR2

6

fk

Table Name

Table Short Name

teams

tem

Key Type

Optionality

Column Name

pk

*

id

VARCHAR2

4

*

name

VARCHAR2

20

*

number_of_players

NUMBER

2

o

discount

NUMBER

2

Data Type

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.

16

Table Name

Table Short Name

sales_representatives

sre

Key Type

Optionality

Column Name

pk

*

id

VARCHAR2

4

uk

*

email

VARCHAR2

50

*

first_name

VARCHAR2

20

*

last_name

VARCHAR2

30

*

phone_number

*

commission_rate

VARCHAR2 NUMBER

11 2

*

supervisor_id

VARCHAR2

4

fk

Data Type

Size

Table Name

Table Short Name

sales_representatives_address

sas

Key Type

Optionality

Column Name

Pk, fk

*

id

VARCHAR2

4

*

address_line_1

VARCHAR2

30

o

address_line_2

VARCHAR2

30

*

city

VARCHAR2

15

*

postal_code

VARCHAR2

7

Data Type

Size

Table Name

Table Short Name

orders

odr

Key Type

Optionality

Column Name

pk

*

id

*

odr_date

DATE

*

odr_time

TIMESTAMP

0

*

number_of_units

NUMBER

2

*

ctr_number

VARCHAR2

6

fk

Data Type VARCHAR2

Size 9

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

Table Name

Table Short Name

ordered_items

oim

Key Type

Optionality

Column Name

*

quantity_ordered

NUMBER

3

*

quantity_shipped

NUMBER

3

pk, fk1

*

odr_id

VARCHAR2

9

pk, fk2

*

itm_number

VARCHAR2

10

Table Name

Table Short Name

items

itm

Key Type

Optionality

Column Name

pk

*

itm_number

VARCHAR2

10

*

name

VARCHAR2

20

*

description

VARCHAR2

50

*

category

VARCHAR2

25

o

color

VARCHAR2

15

o

size

CHAR

1

fk

*

ilt_id

VARCHAR2

11

Table Name

Table Short Name

inventory_list

ilt

Key Type

Optionality

Column Name

pk

*

id

* *

Data Type

Data Type

Data Type

Size

Size

Size

VARCHAR2

11

cost

NUMBER

7,2

units

NUMBER

4

Table Name

Table Short Name

price_history

phy

Key Type

Optionality

Column Name

pk

*

start_date

DATE

pk

*

start_time

TIMESTAMP

0

*

price

NUMBER

7,2

o

end_date

DATE

o

end_time

TIMESTAMP

0

*

itm_number

VARCHAR2

10

pk, fk1

Data Type

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.

18

Engineer the Relational Model from the Logical Data Model Exercise 3 : Create a SQL script file In this exercise you will take your completed relational model that you created in the previous exercise and create the SQL script that will generate your tables in APEX. Task - Creating DDL scripts 1. Ensure that you are in the relational view. a. Click on the Generate DDL button on the toolbar. b. Choose the environment you want to create the script for (11g is fine). c. Click Generate. d. In the Generation Options window double check that everything is as it should be. e. Click OK. f.

Save your file locally as “Sports Script.ddl”.

g. If any errors are identified then you will have to go back to your model to resolve them before regenerating the script.

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.