4660

Page | 1

BCIS 4660 Introduction to Data Warehousing

Assignment 2

Objectives

• Application of database design process o Conceptual, logical, and physical database design o Application of normalization

• Apply E-R modeling

Instructions

1. Create a new Word document. Do not use the current document.

2. Submit a Word document with your name on the cover page

o To create a cover page, click Insert, Cover Page, and

select a cover page)

3. Clearly identify your name and assignment name on the cover page

4. The Word document should contain the requested screenshots and

answers to any questions

5. Each screenshot image must include a caption (e.g., Screenshot1:

ERD1 Shipment)

6. Your screenshots should be legible

7. A professional quality work is expected. Points will be deducted for

unprofessional work.

Environment

• VM BCIS 4660: SQL Server

• ERDPlus https://erdplus.com/standalone

Exercise: ERD & Database Design This assignment is designed to begin familiarizing you with database conceptual design using Entity

Relationship Diagrams.

1. Observe the ER diagram for the GreenShop and answer the following questions about the diagram (5pts)

Page | 2

a) How many tables will the relational schema mapped from the GreenShop ER diagram have? a. ________

b) What is the primary key column in the Product table? a. ________

c) How many columns will the following entities have in the relational schema mapped from the GreenShop ER diagram?

a. Promotion ____ b. Product _____ c. Company _______

2. In GreenShop, each employee reports to one department and a department must have at least one employee reporting to it. However, each department but could have many

employees reporting to it. In the ER Diagram below, what symbols should represent the

relationships (e.g., Zero or more, One and only one, One or more). Please justify the cardinality assignment. (5pts)

a. Symbol 1 should be ___ b. Symbol 2 should be ___

Page | 3

3. In the ER Diagram for the BCIS4660 Course, what symbols should represent the relationships (e.g., Zero or more, One and only one, One or more). Please justify the cardinality assignment. (5pts)

a. Symbol 1 should be ___ b. Symbol 2 should be ___ c. Symbol 3 should be___ d. Symbol 4 should be___

4. In the ER Diagram for the Music, what symbols should represent the relationships (e.g., Zero or more, One and only one, One or more). Please justify the cardinality assignment. (5pts)

c. Symbol 1 should be ___ d. Symbol 2 should be ___ e. Symbol 3 should be___ f. Symbol 4 should be___

5. In this exercise, please put your learned concepts into action by converting the following scenario

into an ERD.

A manager wants to store information on their employees. The employees are identified by an

Employee ID. In additional to storing the employees’ last name, first name, phone number, and

zipcode, each employee is assigned to a department which is identified by the department ID. Each

department has a budget amount, and department name.

Please create an ERD that captures this information about this organization. Be certain to indicate

identifiers and cardinality. Please justify the cardinality assignment. (Screenshot1)

Grading (7pts). Make sure that in the ERD, the:

• Entities are correctly identified:

• Attributes are correctly identified:

Page | 4

• Primary keys are correctly identified:

• Relationships and cardinality are correctly identified:

6. Shipack is a packaging and shipping company with an enterprise database system that tracks its

shipments. Shipped items are characterized by unique item number, weight, dimensions,

destination address, and delivery date. Shipped items are received into Shipack’s system at a single

retail center. Each retail center is characterized by its type, a unique retail identification number,

and address. Shipped items arrive at their destination through one or more standard Shipack

transportation events (i.e., flights, truck deliveries). These transportation events are characterized

by a unique schedule identification number, and a transportation type (e.g., flight, truck).

Create an Entity Relationship Diagram that captures this information about the Shipack system.

Be certain to indicate identifiers and cardinality constraints. Please justify the cardinality

assignment. (Screenshot2)

Grading (7pts). Make sure that in the ERD, the:

• Entities are correctly identified:

• Attributes are correctly identified:

• Primary keys are correctly identified:

• Relationships and cardinality are correctly identified:

7. You have just started working as a Junior Data Analyst at GreenShop. Your manager hands you

an Excel spreadsheet with the following columns (see table below) and asks you to design a

database to store this data (16pts).

a. Describe the steps to normalize this dataset (table) up to 2NF?

b. After this dataset is normalized, how many entities should this dataset have? (Hint: start by identifying the different entities present in this dataset)

c. Use ERDPlus to design the ERD for entities and attributes. (Screenshot3 - make sure to show the 4 required elements in your screenshot)

d. Write and execute the T-SQL to create the tables. Please use elegant and rerunnable scripts

Meaning, there should not be any errors in your script. (HINT: USE “databasename”;

DROP “object type” IF EXISTS). (Screenshot4 - make sure to show the 4 required

elements in your screenshot, including the expanded objects in the object explorer).

e. Using the SQL Server database diagramming tool, create and view the E-R-Model of the

physical implementation.

i. Provide a short description of your actions

ii. Provide a Screenshot5 showing E-R model. (See instructions)

Page | 5

EnrollID StudentID FirstName LastName EnrollDate CourseID CourseName1 CourseID CourseName2

1011 91231 Ken Smith 2021/02/2021 1123 BCIS4660 1156 BCIS 4570

1012 89091 Ada Ogundele 2021/02/2021 1124 BCIS 4630 1123 BCIS4660

1013 23347 Sri Ram 2021/02/2021 1156 BCIS 4570 1123 BCIS4660

1014 84354 Julie Lin 2021/02/2021 1124 BCIS 4630 1123 BCIS4660

1015 34536 Barbara Lilipot 2021/02/2021 1124 BCIS 4630 1123 BCIS4660

1016 45456 John Gerry 2021/02/2021 1156 BCIS 4570 1123 BCIS4660

1017 33435 Mark Goode 2021/02/2021 1124 BCIS 4630 1123 BCIS4660

1018 35345 Red Darlion 2021/02/2021 1156 BCIS 4570 1123 BCIS4660

1019 35378 Janet Rino 2021/02/2021 1124 BCIS 4630 1123 BCIS4660

1020 98065 Porter Cruz 2021/02/2021 1156 BCIS 4570 1123 BCIS4660