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 Promotion 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 (5pts). 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. In two sentences, please describe the relationships in the image below. Make sure to
describe the two directions in the image. (5pts).
7. In two sentences, please describe the relationships in the image below. Make sure to
describe the two directions in the image. (5pts).
8. 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 (10pts).
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.
Page | 5
i. Provide a short description of your actions
ii. Provide a Screenshot5 showing E–R model. (See instructions)
EnrollID StudentID FirstName LastName EnrollDate CourseID CourseName1 CourseID CourseName2
1011 91231 Jay Smith 2022/02/20 1123 BCIS4660 1156 BCIS 4570
1012 89091 Leslie Ogundele 2022/02/20 1124 BCIS 4630 1123 BCIS4660
1013 23347 Izee Ram 2022/02/20 1156 BCIS 4570 1123 BCIS4660
1014 84354 Lola Lin 2022/02/20 1124 BCIS 4630 1123 BCIS4660
1015 34536 Pau Lilipot 2022/02/20 1124 BCIS 4630 1123 BCIS4660
1016 45456 John Carrie 2022/02/20 1156 BCIS 4570 1123 BCIS4660
1017 33435 Mark Luad 2022/02/20 1124 BCIS 4630 1123 BCIS4660
1018 35345 Red Darlion 2022/02/20 1156 BCIS 4570 1123 BCIS4660
1019 35378 Sri Rino 2022/02/20 1124 BCIS 4630 1123 BCIS4660
1020 98065 kalyani Cruz 2022/02/20 1156 BCIS 4570 1123 BCIS4660
9. Database reverse engineering: (5pts)
a. Pick one of the following websites and perform reverse engineering to identify its major
entities and their attributes (4 or more entities). List the entities and their attributes.
i. Entity1 (attrib1, attrib2, attrib3…)
ii. Entity2 (attrib1, attrib2, attrib3…)
iii. Entity3 (attrib1, attrib2, attrib3…)
iv. Entity4 (attrib1, attrib2, attrib3…)
v. ……
b. Use ERDPlus to draw an ERD that represents the entities and attributes, as well as the
relationships between the entities. (Screenshot)
c. Describe the entities and attributes and what you accomplished.
